SQL 排序查询优化

优化前

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改写，让多个字段的排序升序一致。