MySQL 走错索引导致驱动表选错

regw333

原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。