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 mpayOrderinner 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 tablempay_orderadd index `create_time_idx`(create_time);

通过对mpay_order加索引后，优化器选择小的驱动表mpayOrder1484，减少Nest Loop的代价。

索引已经添加，需要改写sql。