文档章节

MySQL SQL优化之覆盖索引

Mr船长
 Mr船长
发布于 2017/09/03 18:04
字数 1071
阅读 2764
收藏 89

前些天,有个同事跟我说:“我写了个SQL,SQL很简单,但是查询速度很慢,并且针对查询条件创建了索引,然而索引却不起作用,你帮我看看有没有办法优化?”。

我对他提供的case进行了优化,并将优化过程整理了下来。

 

我们先来看看优化前的表结构、数据量、SQL、执行计划、执行时间等。

1. 表结构:

CREATE TABLE `t_order` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `order_code` char(12) NOT NULL,
  `order_amount` decimal(12,2) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uni_order_code` (`order_code`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

隐藏了部分不相关字段后,可以看到表足够简单, 并且在order_code上创建了唯一性索引uni_order_code。

2. 数据量:316977

这个数据量还是比较小的,不过如果SQL足够差,一样会查询很慢。

3. SQL:

select order_code, order_amount from t_order order by order_code limit 1000;

哇,SQL足够简单,不过有时候越简单也越难优化。

4. 执行计划:

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t_order ALL NULL NULL NULL NULL 316350 Using filesort

全表扫描、文件排序,注定查询慢!

那为什么MySQL没有利用索引(uni_order_code)扫描完成查询呢?因为MySQL认为这个场景利用索引扫描并非最优的结果。我们先来看下执行时间,然后再来分析为什么没有利用索引扫描。

 5. 执行时间:260ms

原SQL执行过程

的确,执行时间太长了,如果表数据量继续增长下去,性能会越来越差。

 

我们来分析下MySQL为什么使用全表扫描、文件排序,而没有使用索引扫描、利用索引顺序:

1. 全表扫描、文件排序:

虽然是全表扫描,但是扫描是顺序的(不管机械硬盘还是SSD顺序读写性能都是高的),并且数据量不是特别大,所以这部分消耗的时间应该不是特别大,主要的消耗应该是在排序上。

2. 利用索引扫描、利用索引顺序:

uni_order_code是二级索引,索引上保存了(order_code,id),每扫描一条索引需要根据索引上的id定位(随机IO)到数据行上读取order_amount,需要1000次随机IO才能完成查询,而机械硬盘随机IO的效率是极低的(机械硬盘每秒寻址几百次)。

根据我们自己的分析选择全表扫描相对更优。如果把limit 1000改成limit 10,则执行计划会完全不一样。

 

既然我们已经知道是因为随机IO导致无法利用索引,那么有没有办法消除随机IO呢?

有,覆盖索引。

 

我们来看看利用覆盖索引优化后的索引、执行计划、执行时间。

1. 创建索引:

ALTER TABLE `t_order` 
ADD INDEX `idx_ordercode_orderamount` USING BTREE (`order_code` ASC, `order_amount` ASC);

创建了复合索引idx_ordercode_orderamount(order_code,order_amount),将select的列order_amount也放到索引中。

2. 执行计划:

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t_order index NULL idx_ordercode_
orderamount
42 NULL 1000 Using index

执行计划显示查询会利用覆盖索引,并且只扫描了1000行数据,查询的性能应该是非常好的。

3. 执行时间:13ms

优化后执行结果

从执行时间来看,SQL的执行时间提升到原来的1/20,已经达到我们的预期。

 

总结:

覆盖索引是select的数据列只用从索引中就能够取得,不必读取数据行,换句话说查询列要被所建的索引覆盖。索引的字段不只包含查询列,还包含查询条件、排序等。

要写出性能很好的SQL不仅需要学习SQL,还要能看懂数据库执行计划,了解数据库执行过程、索引的数据结构等。

 

© 著作权归作者所有

共有 人打赏支持
Mr船长
粉丝 10
博文 15
码字总数 20778
作品 0
苏州
程序员
私信 提问
加载中

评论(25)

voov
voov

引用来自“voov”的评论

请教
分析执行计划任务 是Navicat for MySQL工具特有的吗?还是命令行下使用其他命令也能达到这个查看执行计划效果。
谢谢
@Mr船长 已找到方法:

SET profiling = 1;
select xx; #查询语句
show profiles;
show profile all for query 2; # 2为上一步得到的id
voov
voov
请教
分析执行计划任务 是Navicat for MySQL工具特有的吗?还是命令行下使用其他命令也能达到这个查看执行计划效果。
谢谢
h
huihrt
试试这样写呢
SELECT
  t1.order_code,
  t1.order_amount
FROM
  t_order t1
INNER JOIN (
  SELECT id from t_order ORDER BY order_code LIMIT 1000;
)temp_t on temp_t.id=t1.id

glamey
glamey
select a.id,a.order_code, a.order_amount from t_order a inner join (select id,order_code from t_order order by order_code desc) b on a.id = b.id limit 1000

如果这样写会如何呢?
shuaia
shuaia

引用来自“Mr船长”的评论

引用来自“shuaia”的评论

这个语句应该也能搞定
select t2.order_code,t2.order_amount from t_order t1
join t_order t2 on t1.id = t2.id
order by t1.order_code desc
limit 1000;
这样还是没消灭随机IO或排序操作

@Mr船长 你有测试过吗?从执行计划来看,这个语句和最原始的语句是有差别的。
Mr船长
Mr船长

引用来自“shuaia”的评论

这个语句应该也能搞定
select t2.order_code,t2.order_amount from t_order t1
join t_order t2 on t1.id = t2.id
order by t1.order_code desc
limit 1000;
这样还是没消灭随机IO或排序操作
Mr船长
Mr船长

引用来自“shuaia”的评论

上ddl的成本太大了。对一个大表做onlineddl基本上每次都是几十个小时,如果有唯一索引还经常思索。真是让人头大!这里我觉得可以这样改。分两步查!第一步select order_code from t_order order by order_code limit 1000;第二步ordercode查就好了
多大表要几十个小时?分两步查也行,不过where in数量不能太大,否则也有性能问题。
Mr船长
Mr船长

引用来自“chdy”的评论

请问兄台,执行时间截图使用的是什么工具
Navicat for MySQL
Mr船长
Mr船长

引用来自“springlin”的评论

请教一下,分析执行计划任务的工具叫什么,谢谢。
Navicat for MySQL
c
chdy
请问兄台,执行时间截图使用的是什么工具
MySQL 索引选择原则

目的 MySQL查询优化器是基于代价(cost-based)的查询方式。因此,在查询过程中,最重要的一部分是根据查询的SQL语句,依据多种索引,计算查询需要的代价,从而选择最优的索引方式生成查询计...

tara_qri
2015/09/24
62
0
MySQL 索引选择原则

目的 MySQL查询优化器是基于代价(cost-based)的查询方式。因此,在查询过程中,最重要的一部分是根据查询的SQL语句,依据多种索引,计算查询需要的代价,从而选择最优的索引方式生成查询计...

真爱2015
2015/12/07
75
0
SQL 常用优化手段总结 - 小技巧

中国有句古话叫做:欲速则不达。在一口气学完了分析 SQL 语句的一般步骤与索引的正确运用方式后小憩片刻。搭配上红茶与白兰地轻松享用下面这些小技巧吧! 系列文章回顾 SQL 常用优化手段总结...

给你添麻烦了
01/10
0
0
mysql sql优化和sql执行计划

SQL优化 禁用SELECT 使用SELECT COUNT() 统计行数 尽量少运算 尽量避免全表扫描,如果可以,在过滤列建立索引 尽量避免在WHERE子句对字段进行NULL判断 尽量避免在WHERE子句使用!= 或者<> 尽量...

qq5805bc784f826
06/28
0
0
写SQL要学会使用"执行计划"

能写SQL是程序员的基本功,而能写出性能优异的SQL是优秀程序员的必备技能。 可那些性能好的SQL语句是怎么写出来的?难道他们了解数据库底层的东西吗? 其实了解数据库原理是一方面,更快捷的...

雷神雨石
2013/11/26
0
0

没有更多内容

加载失败,请刷新页面

加载更多

降压变换器 Buck

特点 输入输出极性相同。 工作过程 在 MOS 导通时,输入电源通过 L 和 C 滤波后向负载端提供电流;当 MOS 断开后,L 通过二极管续流,保持负载电流连续。输出电压因为占空比的作用,不会超过...

colinux
36分钟前
0
0
Apache日志不记录访问静态文件,访问日志切割,静态元素过期时间设置

Apache配置不记录访问静态文件的日志 网站大多元素为静态文件,如图片、css、js等,这些元素可以不用记录 vhost原始配置 <VirtualHost *:80> ServerAdmin test@163.com DocumentRoo...

野雪球
今天
3
0
聊聊storm的ICommitterTridentSpout

序 本文主要研究一下storm的ICommitterTridentSpout ICommitterTridentSpout storm-core-1.2.2-sources.jar!/org/apache/storm/trident/spout/ICommitterTridentSpout.java public interface......

go4it
今天
4
0
Ubuntu常用操作

查看端口号 netstat -anp |grep 端口号 查看已使用端口情况 netstat -nultp(此处不用加端口号) netstat -anp |grep 82查看82端口的使用情况 查找被占用的端口: netstat -tln netstat -tl...

hc321
昨天
3
0
网站cdn的静态资源突然访问变的缓慢,问题排查流程

1.首先我查看了一下是否自己的网络问题,通过对比其他资源的访问速度和下载速度,确认不是 2.通过ping 和 tracert 判断cdn域名能否正常访问,(最后回想感觉这一步可以省略,因为每次最终能访...

小海bug
昨天
3
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部