Chinaunix首页 | 论坛 | 博客
  • 博客访问: 2276354
  • 博文数量: 297
  • 博客积分: 0
  • 博客等级: 民兵
  • 技术积分: 2200
  • 用 户 组: 普通用户
  • 注册时间: 2014-03-31 14:30
个人简介

自己慢慢积累。

文章分类

全部博文(297)

分类: Mysql/postgreSQL

2015-10-22 10:47:56

看表结构:
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) |
给主人留下些什么吧!~~