文档章节

MySQL SQL优化之覆盖索引

Mr船长
 Mr船长
发布于 2017/09/03 18:04
字数 1071
阅读 2138
收藏 89
点赞 11
评论 23

前些天,有个同事跟我说:“我写了个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船长
粉丝 9
博文 18
码字总数 13816
作品 0
苏州
程序员
加载中

评论(23)

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
请问兄台,执行时间截图使用的是什么工具
springlin
springlin
请教一下,分析执行计划任务的工具叫什么,谢谢。
shuaia
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;
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
一次非常有意思的sql优化经历

场景 我用的数据库是mysql5.6,下面简单的介绍下场景 课程表 create table Course( c_id int PRIMARY KEY, name varchar(10) ) 数据100条 学生表: create table Student( id int PRIMARY KE...

rewiner22
06/26
0
0
这份MySQL索引实践清单,已超300人在学习,推荐你看看

特邀嘉宾 叶 金 荣 知数堂联合创始人 & 3306π社区负责人 MySQL优化课程讲师 资深MySQL专家,Oracle MySQL ACE,曾任职搜狐畅游DBA主管,精通MySQL数据库,10年以上MySQL相关工作经验,擅长M...

n88lpo
05/25
0
0
SQL-SQL优化-索引

图文并茂详解 SQL JOIN Join 是关系型数据库系统的重要操作之一,一般关系型数据库中包含的常用 Join:内联接、外联接和交叉联接等。如果我们想在两个或以上的表获取其中从一个表中的行与另一...

掘金官方
2017/12/25
0
0
解开发者之痛:中国移动MySQL数据库优化最佳实践

本文根据DBAplus社群第69期线上分享整理而成。 开源数据库MySQL比较容易碰到性能瓶颈,为此经常需要对MySQL数据库进行优化,而MySQL数据库优化需要运维DBA与相关开发共同参与,其中MySQL参数...

章颖
2016/08/12
0
0
不懂MySQL索引优化?甩你这份清单别嫌多

周四见 公开课系列 We,知数堂 习惯用实力介绍自己—我们只分享干货 重磅福利来袭 2018年5月24日,20:30-21:30 周四见 不见不散! 主讲嘉宾:叶金荣 知数堂联合创始人 & 3306π社区负责人 资深...

n88lpo
05/24
0
0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

CDH的坑之Sqoop导出数据到MySQL

CDH的坑之Sqoop导出数据到MySQL 最近使用Sqoop从Hive导出数据到MySQL中,出现了一系列的问题,下面将这个问题记录一下,避免再度踩坑! 导出语句 sqoop export --connect jdbc:mysql://192....

星汉
6分钟前
0
0
Hyperledger Fabric 客户端开发三

前面两篇文章介绍了Hyperledger Fabric SDK并使用一个实例介绍如何通过SDK和Hyperledger Fabric Blockchain交互, 现在详细分析相关的过程。 首先看 enroll (登录) admin 过程。 'use stric...

十一月不远
7分钟前
0
0
PowerDesigner连接MySQL和逆向工程图

最近想梳理公司项目的表间关系,从项目后台管理系统的操作入手,以及代码的hibernate注解入手,都不算特别尽人意,于是最后还是鼓捣了一下PowerDesigner的逆向工程图,这样更直观一些。 想着...

Oo若离oO
7分钟前
0
0
威胁web应用安全的错误

一般绝大部分的web应用攻击都是没特定目标的大范围漏洞扫描,只有少数攻击确实是为入侵特定目标而进行的针对性尝试。这两种攻击都非常频繁,难以准确检测出来,许多网站的web应用防火墙都无法...

上树的熊
9分钟前
2
0
pypy2 install crypto error

install pycryptodome instead pip install pycryptodome

coord
13分钟前
0
0
Service Mesh所应对的8项挑战

Lori Macvittie 微服务架构是把双刃剑,我们享受它带来的开发速度(development velocity),却也不得不面对服务间通讯带来的复杂性问题。 目前大多数扩展容器化微服务的架构多是基于proxy-b...

好雨云帮
22分钟前
0
0
时间复杂度

1. 维基上的定义 在计算机科学中,算法的时间复杂度是一个函数,它定性描述该算法的运行时间。这是一个代表算法输入值的字符串的长度的函数。时间复杂度常用大O符号表述,不包括这个函数的低...

liuyan_lc
28分钟前
0
0
js中的~符

~是js里的按位取反操作符,~~就是执行两次按位取反,其实就是保持原值,但是注意虽然是原值,但是对布尔型变量执行这个操作,会转化成相应的数值型变量,也就是 ~~true === 1,~~false === 0...

JamesView
29分钟前
0
0
webpack安装

npm install --save-dev webpack-cli

Vincent-Duan
31分钟前
0
0
实时监听EditText内容变化

主要是addTextChangedListener方法的使用 aswerEdittext.addTextChangedListener(new TextWatcher() { //编辑框的内容发生改变之前的回调方法 @Override public void before...

王先森oO
35分钟前
0
0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

返回顶部
顶部