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