文档章节

MySQL SQL优化之覆盖索引

Mr船长
 Mr船长
发布于 2017/09/03 18:04
字数 1071
阅读 2469
收藏 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
博文 18
码字总数 13788
作品 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

没有更多内容

加载失败,请刷新页面

加载更多

37. Sudoku Solver

Description tags: backtrack,hash table difficulty: hard Write a program to solve a Sudoku puzzle by filling the empty cells.A sudoku solution must satisfy all of the following......

52iSilence7
23分钟前
0
0
磁盘格式化、磁盘挂载和手动增加swap空间

9月26日任务 4.5/4.6 磁盘格式化 4.7/4.8 磁盘挂载 4.9 手动增加swap空间 4.5/4.6 磁盘格式化 #查看Linux所支持的文件格式 [root@zgxlinux-01 ~]# cat /etc/filesystems xfs...

zgxlinux
34分钟前
0
0
intellij idea中,鼠标拖动选择的是一块矩形区域

点“编辑”-->“列选择模式” 或者 按 Shift+Alt+Insert

hengbao5
36分钟前
0
0
中文地址

火力全開
51分钟前
0
0
71:循环之for、while、break、continue、exit

1、for循环语法: for 变量名 in 条件;do......;done 1:案例1:求1加到100的和: [root@localhost_02 for]# vim for1.sh #!/bin/bashsum=0for i in `seq 1 100`do sum=$[$sum...

芬野de博客
54分钟前
0
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部