简测覆盖索引、多个排序方向的效率对比

看表结构：
CREATE TABLE `hadoop_firstinto` (
  `ID_` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `TRAFFICID_` varchar(32) NOT NULL COMMENT '通行ID',
  `PASSTIME_` datetime DEFAULT NULL COMMENT '过车时间',
  `SPEED_` int(11) DEFAULT NULL COMMENT '过车速度',
  `ORGID_` bigint(20) DEFAULT NULL COMMENT '机构ID',
  `ROADMONITORID_` bigint(20) DEFAULT NULL COMMENT '卡口ID',
  `CHANNELID_` bigint(20) DEFAULT NULL COMMENT '车道ID',
  `PLATENUMBER_` varchar(15) NOT NULL COMMENT '车牌号码',
  `PLATECOLOR_` varchar(2) NOT NULL COMMENT '车牌颜色',
  `PLATETYPE_` varchar(2) DEFAULT NULL COMMENT '车牌类型',
  `VEHICLECOLOR_` varchar(2) DEFAULT NULL COMMENT '车辆颜色',
  `VEHICLETYPE_` varchar(4) DEFAULT NULL COMMENT '车辆类型',
  `VEHICLEBRAND_` varchar(4) DEFAULT NULL COMMENT '车辆品牌',
  `VEHICLESUBBRAND_` varchar(16) DEFAULT NULL COMMENT '车辆子品牌',
  `VEHICLEMODELYEAR_` varchar(16) DEFAULT NULL COMMENT '车辆年款',
  `backType_` varchar(1) NOT NULL,
  PRIMARY KEY (`ID_`),
  alter table hadoop_firstinto add index index01(backType_,PASSTIME_,PLATENUMBER_);
  alter table hadoop_firstinto add index   index02(backType_,VEHICLETYPE_,VEHICLEBRAND_,VEHICLECOLOR_,PLATETYPE_,PLATECOLOR_,PLATENUMBER_,PASSTIME_);
) ENGINE=innoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

查询语句：
SELECT ROADMONITORID_,TRAFFICID_,PLATENUMBER_,PASSTIME_,SPEED_,PLATECOLOR_ FROM hadoop_firstinto WHERE PASSTIME_>='2014-10-17 17:35:33' AND PASSTIME_<='2015-10-17 17:35:33' AND backType_='1' ORDER BY PASSTIME_ DESC,PLATENUMBER_ asc LIMIT 0,1000;

数据量： 2000W

1、无覆盖索引，两个排序方向不同（desc asc）
查询SQL: SELECT ROADMONITORID_,TRAFFICID_,PLATENUMBER_,PASSTIME_,SPEED_,PLATECOLOR_ FROM hadoop_firstinto WHERE PASSTIME_>='2014-10-17 17:35:33' AND PASSTIME_<='2015-10-17 17:35:33' AND backType_='1' ORDER BY PASSTIME_ DESC,PLATENUMBER_ asc LIMIT 0,1000;
查询时长：138s

2、加上覆盖索引，两个排序方向不同（desc asc）
索引增加： alter table add KEY `index03` (`backType_`,`PASSTIME_`,`PLATENUMBER_`,ROADMONITORID_,TRAFFICID_,PLATENUMBER_,PASSTIME_,SPEED_,PLATECOLOR_),

查询SQL: SELECT ROADMONITORID_,TRAFFICID_,PLATENUMBER_,PASSTIME_,SPEED_,PLATECOLOR_ FROM hadoop_firstinto WHERE PASSTIME_>='2014-10-17 17:35:33' AND PASSTIME_<='2015-10-17 17:35:33' AND backType_='1' ORDER BY PASSTIME_ DESC,PLATENUMBER_ asc LIMIT 0,1000;
查询时长：9.6s

3、加上覆盖索引，两个排序方向相同（desc desc）

查询SQL: SELECT ROADMONITORID_,TRAFFICID_,PLATENUMBER_,PASSTIME_,SPEED_,PLATECOLOR_ FROM hadoop_firstinto WHERE PASSTIME_>='2014-10-17 17:35:33' AND PASSTIME_<='2015-10-17 17:35:33' AND backType_='1' ORDER BY PASSTIME_ DESC,PLATENUMBER_ desc LIMIT 0,1000;
查询时长：0.015s


结论：
设计查询SQL时，如果有多个字段需要排序，一定要尽量是使排序方向相同
设计索引时，如果磁盘空间运行，覆盖索引的效率要高得多