注意使用 BTREE 复合索引各字段的 ASC/DESC 以优化 order by 查询效率
来源:互联网 发布:少女前线 知乎 编辑:程序博客网 时间:2024/06/06 02:14
tbl_direct_pos_201506 表有 190 万数据,DDL:
关于该表的一个慢查询日志如下:
# Time: 150701 15:45:28
# User@Host: test[test] @ localhost [127.0.0.1] Id: 1
# Query_time: 2.478195 Lock_time: 0.010007 Rows_sent: 20 Rows_examined: 450612
SET timestamp=1435736728;
select substr(t.acq_ins_code, 3) merAcqInsCode, t.mer_code, t.term_id, substr(t.mer_addr_name, 1, 12) merName,
tt.trans_desc, t.rev_flag, t.trans_amt, concat(substr(t.sys_date, 1, 4), t.trans_datetime) transTime,t.before_trans_code,
t.acct_num, t.retrivl_ref_num, t.resp_code, t.resp_auth_code, r.recv_ins_name,t.acq_ins_code,t.trace_num,t.trans_datetime,
case substr(t.sa_sav2,259,1) when 1 then '借记卡' when 2 then '贷记卡'
when 3 then '准贷记卡' when 4 then '私有预付卡' else '' end cardType,
case
when locate('VIS',t.sa_sav2) > 0 then 'VISA'
when locate('JCB',t.sa_sav2) > 0 then 'JCB'
when locate('DNC',t.sa_sav2) > 0 then '大莱卡'
when locate('CUP',t.sa_sav2) > 0 then '银联境内卡'
when locate('UPI',t.sa_sav2) > 0 then '银联境外卡'
else '' end cardBrand
from tbl_direct_pos_201506 t
left join trans_recv_ins r on r.recv_ins_code = t.recv_ins_code
left join tbl_trans_type tt on tt.trans_code = t.before_trans_code
where t.sys_date between '20150622' and '20150628'
order by
t.sys_date desc, t.trans_datetime desc, t.acq_ins_code, t.trace_num
limit 0, 20;
日志中可以看出该 sql 的执行时间是 2.478 s。
我们来查看一下该 sql 的执行计划:
id select_type table type possible_keys key key_len ref rows Extra 1SIMPLEtrangePRIMARYPRIMARY24 299392Using index condition; Using filesort1SIMPLEreq_refPRIMARYPRIMARY41msp.t.recv_ins_code1Using where1SIMPLEtteq_refPRIMARYPRIMARY14msp.t.before_trans_code1Using where
执行计划分析:
Using filesort。是的,看到它,说明我们的查询需要优化了:文件排序是通过相应的排序算法,将取得的数据在内存中进行排序。
MyISAM 存储引擎的主键索引和非主键索引差别很小,只不过是主键索引的索引键是一个唯一且非空的键而已。MyISAM 的索引默认为 B-TREE。也就是说,主键在这里相当于一个普通的 B-TREE。
该 sql 一个 where 字段,四个 order by 字段,都在主键里边呀,而且 order by 的顺序完全符合最左前缀原则,为什么还要 filesort?
MySql 索引创建手册里如是说:
索引列的定义可以跟随 ASC 或者 DESC。这些关键字允许为未来扩展用于指定升序或降序索引值存储。这个语法会被解析但却被忽略。索引列总是以升序排列。——也就是说你写了不会报错,但写了白写。
这样看来,我们的主键没起排序作用,原因就在于我们的主键是各主键字段 asc 存储, order by 里 desc 和 asc(默认是 asc) 混用。为了验证这个说法,我们把该 order by 换为和主键一致的 asc:
执行时间:0.023 s。
结果差强人意。查看其执行计划:
id select_type table type possible_keys key key_len ref rows Extra 1SIMPLEtrangePRIMARYPRIMARY24 299392Using index condition1SIMPLEreq_refPRIMARYPRIMARY41msp.t.recv_ins_code1Using where1SIMPLEtteq_refPRIMARYPRIMARY14msp.t.before_trans_code1Using where
果然,我们利用到了主键索引,Using filesort 没有了。
既然找的了问题的症兆所在,接下来的事情似乎只是走流程了。
问了下业务,分页结果里 sys_date 和 trans_datetime 两个字段必须降序排列,其余两个字段倒不是很在意。
既然我们无法更改索引每一列的降序、升序(默认为升序),那么我们可以在写 order by 的时候让索引各字段降序/升序一致。最终的 sql 改写为:
CREATE TABLE `tbl_direct_pos_201506` ( `acq_ins_code` char(13) NOT NULL DEFAULT '' COMMENT '机构代码', `trace_num` char(6) NOT NULL DEFAULT '' COMMENT '跟踪号', `trans_datetime` char(10) NOT NULL DEFAULT '' COMMENT '交易时间', `process_flag` char(1) DEFAULT NULL COMMENT '处理标识', `rev_flag` char(1) DEFAULT NULL COMMENT '接收标识', `before_trans_code` char(3) DEFAULT NULL COMMENT '交易类型', `trans_amt` decimal(15,3) DEFAULT NULL COMMENT '交易金额', `acct_num` char(21) DEFAULT NULL COMMENT '卡号', `mer_type` char(4) DEFAULT NULL COMMENT '商户类型', `recv_ins_code` char(13) DEFAULT NULL COMMENT '发卡行代码', `retrivl_ref_num` char(12) DEFAULT NULL COMMENT '检索参考号', `resp_auth_code` char(6) DEFAULT NULL COMMENT '授权码', `resp_code` char(2) DEFAULT NULL COMMENT '应答码', `term_id` char(8) DEFAULT NULL COMMENT '终端代码', `mer_code` char(15) DEFAULT NULL COMMENT '商户代码', `mer_addr_name` char(40) DEFAULT NULL COMMENT '商户名称和地址,前 25 字节是名称,后面是地址', `self_define` varchar(300) DEFAULT NULL COMMENT '第 259 字节是卡片类型', `sys_date` char(8) NOT NULL DEFAULT '' COMMENT '交易日期', `sa_sav2` varchar(300) DEFAULT NULL COMMENT '第 243 字节是 DCC 标识', `rec_create_time` datetime DEFAULT NULL COMMENT '联机入库时间', `rec_update_time` datetime DEFAULT NULL COMMENT '最后修改时间', PRIMARY KEY (`sys_date`,`trans_datetime`,`acq_ins_code`,`trace_num`), KEY `idx_direct_pos_create_time` (`rec_create_time`)) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='交易月表模板';
关于该表的一个慢查询日志如下:
# Time: 150701 15:45:28
# User@Host: test[test] @ localhost [127.0.0.1] Id: 1
# Query_time: 2.478195 Lock_time: 0.010007 Rows_sent: 20 Rows_examined: 450612
SET timestamp=1435736728;
select substr(t.acq_ins_code, 3) merAcqInsCode, t.mer_code, t.term_id, substr(t.mer_addr_name, 1, 12) merName,
tt.trans_desc, t.rev_flag, t.trans_amt, concat(substr(t.sys_date, 1, 4), t.trans_datetime) transTime,t.before_trans_code,
t.acct_num, t.retrivl_ref_num, t.resp_code, t.resp_auth_code, r.recv_ins_name,t.acq_ins_code,t.trace_num,t.trans_datetime,
case substr(t.sa_sav2,259,1) when 1 then '借记卡' when 2 then '贷记卡'
when 3 then '准贷记卡' when 4 then '私有预付卡' else '' end cardType,
case
when locate('VIS',t.sa_sav2) > 0 then 'VISA'
when locate('JCB',t.sa_sav2) > 0 then 'JCB'
when locate('DNC',t.sa_sav2) > 0 then '大莱卡'
when locate('CUP',t.sa_sav2) > 0 then '银联境内卡'
when locate('UPI',t.sa_sav2) > 0 then '银联境外卡'
else '' end cardBrand
from tbl_direct_pos_201506 t
left join trans_recv_ins r on r.recv_ins_code = t.recv_ins_code
left join tbl_trans_type tt on tt.trans_code = t.before_trans_code
where t.sys_date between '20150622' and '20150628'
order by
t.sys_date desc, t.trans_datetime desc, t.acq_ins_code, t.trace_num
limit 0, 20;
日志中可以看出该 sql 的执行时间是 2.478 s。
我们来查看一下该 sql 的执行计划:
执行计划分析:
Using filesort。是的,看到它,说明我们的查询需要优化了:文件排序是通过相应的排序算法,将取得的数据在内存中进行排序。
MyISAM 存储引擎的主键索引和非主键索引差别很小,只不过是主键索引的索引键是一个唯一且非空的键而已。MyISAM 的索引默认为 B-TREE。也就是说,主键在这里相当于一个普通的 B-TREE。
该 sql 一个 where 字段,四个 order by 字段,都在主键里边呀,而且 order by 的顺序完全符合最左前缀原则,为什么还要 filesort?
MySql 索引创建手册里如是说:
索引列的定义可以跟随 ASC 或者 DESC。这些关键字允许为未来扩展用于指定升序或降序索引值存储。这个语法会被解析但却被忽略。索引列总是以升序排列。——也就是说你写了不会报错,但写了白写。
这样看来,我们的主键没起排序作用,原因就在于我们的主键是各主键字段 asc 存储, order by 里 desc 和 asc(默认是 asc) 混用。为了验证这个说法,我们把该 order by 换为和主键一致的 asc:
select substr(t.acq_ins_code, 3) merAcqInsCode, t.mer_code, t.term_id, substr(t.mer_addr_name, 1, 12) merName,tt.trans_desc, t.rev_flag, t.trans_amt, concat(substr(t.sys_date, 1, 4), t.trans_datetime) transTime,t.before_trans_code,t.acct_num, t.retrivl_ref_num, t.resp_code, t.resp_auth_code, r.recv_ins_name,t.acq_ins_code,t.trace_num,t.trans_datetime,case substr(t.sa_sav2,259,1) when 1 then '借记卡' when 2 then '贷记卡'when 3 then '准贷记卡' when 4 then '私有预付卡' else '' end cardType,case when locate('VIS',t.sa_sav2) > 0 then 'VISA' when locate('JCB',t.sa_sav2) > 0 then 'JCB' when locate('DNC',t.sa_sav2) > 0 then '大莱卡' when locate('CUP',t.sa_sav2) > 0 then '银联境内卡' when locate('UPI',t.sa_sav2) > 0 then '银联境外卡'else '' end cardBrand from tbl_direct_pos_201506 tleft join trans_recv_ins r on r.recv_ins_code = t.recv_ins_codeleft join tbl_trans_type tt on tt.trans_code = t.before_trans_codewhere t.sys_date between '20150622' and '20150628' order by t.sys_date, t.trans_datetime, t.acq_ins_code, t.trace_numlimit 0, 20;
执行时间:0.023 s。
结果差强人意。查看其执行计划:
果然,我们利用到了主键索引,Using filesort 没有了。
既然找的了问题的症兆所在,接下来的事情似乎只是走流程了。
问了下业务,分页结果里 sys_date 和 trans_datetime 两个字段必须降序排列,其余两个字段倒不是很在意。
既然我们无法更改索引每一列的降序、升序(默认为升序),那么我们可以在写 order by 的时候让索引各字段降序/升序一致。最终的 sql 改写为:
select substr(t.acq_ins_code, 3) merAcqInsCode, t.mer_code, t.term_id, substr(t.mer_addr_name, 1, 12) merName,tt.trans_desc, t.rev_flag, t.trans_amt, concat(substr(t.sys_date, 1, 4), t.trans_datetime) transTime,t.before_trans_code,t.acct_num, t.retrivl_ref_num, t.resp_code, t.resp_auth_code, r.recv_ins_name,t.acq_ins_code,t.trace_num,t.trans_datetime,case substr(t.sa_sav2,259,1) when 1 then '借记卡' when 2 then '贷记卡'when 3 then '准贷记卡' when 4 then '私有预付卡' else '' end cardType,case when locate('VIS',t.sa_sav2) > 0 then 'VISA' when locate('JCB',t.sa_sav2) > 0 then 'JCB' when locate('DNC',t.sa_sav2) > 0 then '大莱卡' when locate('CUP',t.sa_sav2) > 0 then '银联境内卡' when locate('UPI',t.sa_sav2) > 0 then '银联境外卡'else '' end cardBrand from tbl_direct_pos_201506 tleft join trans_recv_ins r on r.recv_ins_code = t.recv_ins_codeleft join tbl_trans_type tt on tt.trans_code = t.before_trans_codewhere t.sys_date between '20150622' and '20150628' order by t.sys_date desc, t.trans_datetime desc, t.acq_ins_code desc, t.trace_num desc limit 0, 20;
执行之,0.029 s,搞定。
参考资料
- http://dev.mysql.com/doc/refman/5.5/en/create-index.html
- http://stackoverflow.com/questions/10109108/how-do-i-create-a-desc-index-in-mysql
- http://stackoverflow.com/questions/2341576/updating-mysql-primary-key
0 0
- 注意使用 BTREE 复合索引各字段的 ASC/DESC 以优化 order by 查询效率
- 注意使用 BTREE 复合索引各字段的 ASC/DESC 以优化 order by 查询效率
- ORDER BY xxx DESC/ASC
- oracle查询排序asc/desc 多列 order by
- oracle查询排序asc/desc 多列 order by
- oracle查询排序asc/desc 多列 order by
- oracle查询排序asc/desc 多列 order by
- oracle 查询结果的排序,ASC/DESC,升序,降序,多列排序,ORDER BY,一些练习题
- oracle 查询结果的排序,ASC/DESC,升序,降序,多列排序,ORDER BY
- 排序用法 order by asc desc
- 分组查询 group by having 排序 order by asc(升序)或desc(降序)
- 使用倒序索引提升ORDER BY DESC性能
- 使用倒序索引提升ORDER BY DESC性能
- order by 降序desc,升序asc,排名函数 rank()over()
- Mysql| order by 排序检索数据(ASC,DESC)
- sql2008 order by desc 查询速度慢
- mysql:sql order by */* desc (查询)
- oracle order by 不指定 asc或者desc,默认是升序,即asc
- docker安装升级linux内核(2.6.32->3.10.81),安装成功!
- Swift 2.0初探
- HDU 1171(Big Event in HDU)0-1背包问题
- 连载七 奖学金文章更新 张文军 北京IOS第38期学员
- 为虚拟机添加新的硬盘并创建LVM
- 注意使用 BTREE 复合索引各字段的 ASC/DESC 以优化 order by 查询效率
- 浅谈java中的return
- 自定义View 滚动条
- Android多任务断点下载
- ci database报错
- ConcurrentHashMap类的理解
- 定时任务框架搭建
- 面试题37:两个链表的第一个公共结点
- 【Linux】 定时任务 每隔时间执行任务 脚本