看表结构:
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时,如果有多个字段需要排序,一定要尽量是使排序方向相同
设计索引时,如果磁盘空间运行,覆盖索引的效率要高得多
阅读(943) | 评论(0) | 转发(0) |