SELECT t1.*
FROM t1
LEFT JOIN t2 ON t2.capital_unique_code = t1.unique_code
WHERE t1.trade_date >= '2020-12-24'
AND t1.trade_date <= '2020-12-24'
AND t1.capital_account IN ('95200078801700000859', 'dev@souche.com', 'sousou@souche.com')
ORDER BY t1.trade_date, t1.trade_time, t1.id
LIMIT 50
图上sql t1. unique_code utf8mb4 字符集
t2. capital_unique_code是utf8支付集
因为字符集不一致 导致了索引失效
+----+-------------+-------+------------+-------+--------------------------------+--------------------------------+---------+------+---------+----------+-----------------------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+--------------------------------+--------------------------------+---------+------+---------+----------+-----------------------------------------------------------------+
| 1 | SIMPLE | t1 | NULL | range | idx_trade_date_capital_account | idx_trade_date_capital_account | 257 | NULL | 34 | 100.00 | Using index condition; Using temporary; Using filesort |
| 1 | SIMPLE | t2 | NULL | index | NULL | uniq_key | 303 | NULL | 2348088 | 100.00 | Using where; Using index; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+-------+--------------------------------+--------------------------------+---------+------+---------+----------+-----------------------------------------------------------------+
修改字符集为一致之后
+----+-------------+-------+------------+-------+--------------------------------+--------------------------------+---------+---------------------------+------+----------+---------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+--------------------------------+--------------------------------+---------+---------------------------+------+----------+---------------------------------------+
| 1 | SIMPLE | t1 | NULL | range | idx_trade_date_capital_account | idx_trade_date_capital_account | 257 | NULL | 34 | 100.00 | Using index condition; Using filesort |
| 1 | SIMPLE | t2 | NULL | ref | uniq_key | uniq_key | 403 | sfs_server.t1.unique_code | 1 | 100.00 | Using where; Using index |
+----+-------------+-------+------------+-------+--------------------------------+--------------------------------+---------+---------------------------+------+----------+---------------------------------------+
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/30127122/viewspace-2745138/ ，如需转载，请注明出处，否则将追究法律责任。
