首页 > 代码库 > MySQL order by后对其他索引的干扰，导致优化器走错索引
MySQL order by后对其他索引的干扰，导致优化器走错索引
MySQL version：5.5.36
xxx@172.17.62.39 5.5.36-log xxx 10:19:54>show index from FD_FINANCE_ACC_HIS;
+--------------------+------------+------------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------------------+------------+------------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| FD_FINANCE_ACC_HIS | 0 | PRIMARY | 1 | ID | A | 62268963 | NULL | NULL | | BTREE | | |
| FD_FINANCE_ACC_HIS | 0 | accno_confirmdate_uniq | 1 | CUST_ACC_NO | A | 1037816 | NULL | NULL | | BTREE | | |
| FD_FINANCE_ACC_HIS | 0 | accno_confirmdate_uniq | 2 | CONFIRM_DATE | A | 62268963 | NULL | NULL | YES | BTREE | | |
| FD_FINANCE_ACC_HIS | 1 | CONFIRM_DATE_idx | 1 | CONFIRM_DATE | A | 20 | NULL | NULL | YES | BTREE | | |
| FD_FINANCE_ACC_HIS | 1 | CREATETIME_idx | 1 | CREATE_TIME | A | 20 | NULL | NULL | YES | BTREE | | |
+--------------------+------------+------------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
sql语句：
xxx@172.17.62.39 5.5.36-log xxx 10:19:34>desc SELECT facc.ID, facc.CREATE_TIME, facc.CONFIRM_DATE, facc.CUST_ACC_NO, facc.TOTAL_VOL, facc.TOTAL_DSB_INCOME, facc.CURDAY_DSB_INCOME, iden.CUST_NAME, iden.JD_PIN, iden.WALLET_ID, facc.SEVEN_PROFIT, facc.MILLION_PROFIT, facc.MHT_CODE FROM FD_FINANCE_ACC_HIS facc left join FD_FINANCE_IDEN as iden on facc.CUST_ACC_NO = iden.CUST_ACC_NO where 1=1 and facc.CONFIRM_DATE <= ‘2014-11-10‘ and iden.WALLET_ID = ‘1000001849776‘ order by facc.CREATE_TIME desc limit 100;
+----+-------------+-------+-------+----------------------------------------------+----------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+----------------------------------------------+----------------+---------+-------+------+-------------+
| 1 | SIMPLE | iden | const | PRIMARY,walletid_uniq,CUSTACCNO_IDENTYPE_idx | walletid_uniq | 99 | const | 1 | |
| 1 | SIMPLE | facc | index | accno_confirmdate_uniq,CONFIRM_DATE_idx | CREATETIME_idx | 9 | NULL | 200 | Using where |
+----+-------------+-------+-------+----------------------------------------------+----------------+---------+-------+------+-------------+
2 rows in set (0.00 sec)
sql执行时间：
60.011
从执行计划看，优化器明显走了order by 后的索引，但是CREATETIME_idx 选择性不好啊，坏了，
liuqirong@172.17.62.39 5.5.36-log funddb1 10:21:13>select count(distinct CREATE_TIME) from FD_FINANCE_ACC_HIS;
+-----------------------------+
| count(distinct CREATE_TIME) |
+-----------------------------+
| 252 |
+-----------------------------+
1 row in set (0.01 sec)
xxx@172.17.62.39 5.5.36-log xxx 10:21:50>select count(*) from FD_FINANCE_ACC_HIS;
+----------+
| count(*) |
+----------+
| 62276038 |
+----------+
1 row in set (14.48 sec)
1.第一种优化方式：force index(accno_confirmdate_uniq )，但是这种方式，一旦后期有索引变更（如把force index索引删掉），sql语句将报错。
liuqirong@172.17.62.39 5.5.36-log funddb1 10:19:45>desc SELECT facc.ID, facc.CREATE_TIME, facc.CONFIRM_DATE, facc.CUST_ACC_NO, facc.TOTAL_VOL, facc.TOTAL_DSB_INCOME, facc.CURDAY_DSB_INCOME, iden.CUST_NAME, iden.JD_PIN, iden.WALLET_ID, facc.SEVEN_PROFIT, facc.MILLION_PROFIT, facc.MHT_CODE FROM FD_FINANCE_ACC_HIS facc force index(`accno_confirmdate_uniq`) left join FD_FINANCE_IDEN as iden on facc.CUST_ACC_NO = iden.CUST_ACC_NO where 1=1 and facc.CONFIRM_DATE <= ‘2014-11-10‘ and iden.WALLET_ID = ‘1000001849776‘ order by facc.CREATE_TIME desc limit 100;
+----+-------------+-------+-------+----------------------------------------------+------------------------+---------+-------+------+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+----------------------------------------------+------------------------+---------+-------+------+----------------+
| 1 | SIMPLE | iden | const | PRIMARY,walletid_uniq,CUSTACCNO_IDENTYPE_idx | walletid_uniq | 99 | const | 1 | Using filesort |
| 1 | SIMPLE | facc | ref | accno_confirmdate_uniq | accno_confirmdate_uniq | 194 | const | 60 | Using where |
+----+-------------+-------+-------+----------------------------------------------+------------------------+---------+-------+------+----------------+
这种方式不推荐，一旦force index的索引发生变更，此sql优化会报错，除非是实在没办法的情况下。
2.第二种优化方式：drop index CREATETIME_idx ，排除此索引对的干扰
alter table FD_FINANCE_ACC_HIS drop key `CREATETIME_idx `;
3.第三种优化方式：add 一新的索引
alter table FD_FINANCE_ACC_HIS add key `accno_confirmdate_createtime_idx`(CUST_ACC_NO,CONFIRM_DATE,CREATE_TIME );
案例二：
mysql> explain select subscribeId, pin,projectId,projectTag,redoundId,subscribeStatus,freight,supportAmount,payAmount,isRedound,orderId,createTime,updateTime,expressCompany ,expressNum,remarks,consignee,deliveryAddress,contactPhone,receiptTime,payedTime,transactionId,payStatus,email,ordFrom,csRemarks,invoiceFlag,invoiceTitle,status,canceltag
-> from projectsubscribe
-> where projectId=194 and cancelTag=0 and payStatus in ( 3 , 5 , 4 , 6 )
-> order by subscribeId desc
-> limit 0,10;
+----+-------------+------------------+-------+----------------------------------------------------------------------------------+---------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------------+-------+----------------------------------------------------------------------------------+---------+---------+------+------+-------------+
| 1 | SIMPLE | projectsubscribe | index | ind_paystatus,projectid_paystatus,idx_faster,projectId_cancelTag_subscribeId_idx | PRIMARY| 8 | NULL | 2052 | Using where |
+----+-------------+------------------+-------+----------------------------------------------------------------------------------+---------+---------+------+------+-------------+
1 row in set (0.39 sec)
发现是一个MySQL 5.6 优化器改进的一个bug：
mysql> show variables like ‘eq_range_index_dive_limit‘;
+---------------------------+-------+
| Variable_name | Value |
+---------------------------+-------+
| eq_range_index_dive_limit | 10 |
col_name IN(val1, ..., valN) col_name = val1 OR ... OR col_name = valN
eq_range_index_dive_limit：不同的值控制值优化器选择index dives、index statistics。
eq_range_index_dive_limit=0 总是使用index dives（[v1,v1]、[v2,v2]...[vn,vn]）
eq_range_index_dive_limit=N+1 总是使用index dives（[v1,v1]、[v2,v2]...[vn,vn]）
eq_range_index_dive_limit=其他 总是使用index statistics）。
MySQL order by后对其他索引的干扰，导致优化器走错索引