MySQL 走錯索引導致驅動表選錯

標籤：mysql 走錯索引導致驅動表選錯

原sql：

select count(*) from mpay_order mpayOrder inner join mrecharge_order_info orderinfo on mpayOrder.order_num = orderinfo.order_num inner join mpay_trade mpayTrade on mpayOrder.order_num = mpayTrade.order_num where TRUE and left(mpayTrade.trade_num,2) = ‘10‘ and TIMESTAMPDIFF(SECOND,mpayOrder.create_time,‘2014-12-19 00:00:00‘) <= 0 and TIMESTAMPDIFF(SECOND,mpayOrder.create_time,‘2014-12-19 12:40:32‘) >= 0; 

執行計畫：

+----+-------------+-----------+-------+---------------+---------------+---------+--------------------------+--------+-------------+ 
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | 
+----+-------------+-----------+-------+---------------+---------------+---------+--------------------------+--------+-------------+ 
| 1 | SIMPLE | orderinfo | index | order_num_idx | order_num_idx | 93 | NULL | 184192 | Using index | 
| 1 | SIMPLE | mpayTrade | ref | order_num_idx | order_num_idx | 93 | mpay.orderinfo.order_num | 1 | Using where | 
| 1 | SIMPLE | mpayOrder | ref | order_num_idx | order_num_idx | 93 | mpay.mpayTrade.order_num | 1 | Using where | 
+----+-------------+-----------+-------+---------------+---------------+---------+--------------------------+--------+-------------+

執行時間：1 row in set (2.69 sec)

===>建議最佳化為，改成成如下：

select count(*) from mpay_order mpayOrder inner join mrecharge_order_info orderinfo on mpayOrder.order_num = orderinfo.order_num inner join mpay_trade mpayTrade on mpayOrder.order_num = mpayTrade.order_num where TRUE and left(mpayTrade.trade_num,2) = ‘10‘ and mpayOrder.create_time>=‘2014-12-19 00:00:00‘ and mpayOrder.create_time<=‘2014-12-19 12:40:32‘; 

執行計畫：

+----+-------------+-----------+-------+-------------------------------+-----------------+---------+--------------------------+------+--------------------------+ 
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | 
+----+-------------+-----------+-------+-------------------------------+-----------------+---------+--------------------------+------+--------------------------+ 
| 1 | SIMPLE | mpayOrder | range | order_num_idx,create_time_idx | create_time_idx | 9 | NULL | 1484 | Using where | 
| 1 | SIMPLE | orderinfo | ref | order_num_idx | order_num_idx | 93 | mpay.mpayOrder.order_num | 1 | Using where; Using index | 
| 1 | SIMPLE | mpayTrade | ref | order_num_idx | order_num_idx | 93 | mpay.mpayOrder.order_num | 1 | Using where | 
+----+-------------+-----------+-------+-------------------------------+-----------------+---------+--------------------------+------+--------------------------+

執行時間:1 row in set (0.03 sec)

最佳化思路：去掉函數TIMESTAMPDIFF，建立索引alter table mpay_order add index `create_time_idx`(create_time);  

          通過對mpay_order加索引後，最佳化器選擇小的驅動表mpayOrder 1484 ，減少Nest Loop的代價。

索引已經添加，需要改寫sql。

本文出自 “My DBA life” 部落格，請務必保留此出處http://huanghualiang.blog.51cto.com/6782683/1596179

MySQL 走錯索引導致驅動表選錯