MySQL order by後對其他索引的幹擾，導致最佳化器走錯索引

標籤：mysql order by後對其他索引的幹擾   導致最佳化器走錯索引   

MySQL version：5.5.36

[email protected] 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語句：

[email protected] 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 選擇性不好啊，壞了，

[email protected] 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)

[email protected] 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語句將報錯。

[email protected] 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後對其他索引的幹擾，導致最佳化器走錯索引