优化前
mysql> explain SELECT ROADMONITORID_,TRAFFICID_,PLATENUMBER_,PASSTIME_,SPEED_,PLATECOLOR_ FROM hadoop_firstinto
-> WHERE PASSTIME_>='2015-01-23 13:59:16' AND PASSTIME_<='2015-10-23 13:59:16' AND backType_='6'
-> ORDER BY PASSTIME_ DESC,PLATENUMBER_ ASC LIMIT 0,100;
+----+-------------+------------------+------+-----------------+---------+---------+-------+----------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------------+------+-----------------+---------+---------+-------+----------+-----------------------------+
| 1 | SIMPLE | hadoop_firstinto | ref | index01,index02 | index01 | 5 | const | 12300119 | Using where; Using filesort |
+----+-------------+------------------+------+-----------------+---------+---------+-------+----------+-----------------------------+
SELECT ROADMONITORID_,TRAFFICID_,PLATENUMBER_,PASSTIME_,SPEED_,PLATECOLOR_ FROM hadoop_firstinto
WHERE PASSTIME_>='2015-01-23 13:59:16' AND PASSTIME_<='2015-10-23 13:59:16' AND backType_='6'
ORDER BY PASSTIME_ DESC,PLATENUMBER_ ASC LIMIT 0,100;
....
100 rows in set (50.19 sec)
优化后
mysql> EXPLAIN SELECT ROADMONITORID_,TRAFFICID_,PLATENUMBER_,PASSTIME_,SPEED_,PLATECOLOR_ FROM hadoop_firstinto
-> WHERE PASSTIME_>='2015-01-23 13:59:16' AND PASSTIME_<='2015-10-23 13:59:16' AND backType_='6'
-> ORDER BY PASSTIME_ DESC,PLATENUMBER_ DESC LIMIT 0,100;
+----+-------------+------------------+------+-----------------+---------+---------+-------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------------+------+-----------------+---------+---------+-------+----------+-------------+
| 1 | SIMPLE | hadoop_firstinto | ref | index01,index02 | index01 | 5 | const | 12300119 | Using where |
+----+-------------+------------------+------+-----------------+---------+---------+-------+----------+-------------+
--优化后查询性能测试
SELECT ROADMONITORID_,TRAFFICID_,PLATENUMBER_,PASSTIME_,SPEED_,PLATECOLOR_ FROM hadoop_firstinto
WHERE PASSTIME_>='2015-01-23 13:59:16' AND PASSTIME_<='2015-10-23 13:59:16' AND backType_='6'
ORDER BY PASSTIME_ DESC,PLATENUMBER_ DESC LIMIT 0,100;
....
100 rows in set (0.01 sec)
总结:因为同时多个字段不同方向的排序,无法完全利用索引避免排序,所以这里进行了SQL改写,让多个字段的排序升序一致。