共计 6034 个字符,预计需要花费 16 分钟才能阅读完成。
前言
对于互联网公司来说,随着用户量和数据量的一直减少,慢查问是无奈防止的问题。个别状况下如果呈现慢查问,意味着接口响应慢、接口超时等问题。如果是高并发的场景,可能会呈现数据库连贯被占满的状况,间接导致服务不可用。
慢查问确实会导致很多问题,咱们要如何优化慢查问呢?
次要解决办法有:
- 监控 sql 执行状况,发邮件、短信报警,便于疾速辨认慢查问 sql
- 关上数据库慢查问日志性能
- 简化业务逻辑
- 代码重构、优化
- 异步解决
- sql 优化
- 索引优化
其余的方法先不说,前面有机会再独自介绍。明天我重点说说索引优化,因为它是解决慢查问 sql 问题最无效的伎俩。
如何查看某条 sql 的索引执行状况呢?
没错,在 sql 后面加上 explain
关键字,就可能看到它的执行打算,通过执行打算,咱们能够分明的看到表和索引执行的状况,索引有没有执行、索引执行程序和索引的类型等。
索引优化的步骤是:
- 应用
explain
查看 sql 执行打算 - 判断哪些索引使用不当
- 优化 sql,sql 可能须要屡次优化能力达到索引应用的最优值
既然索引优化的第一步是应用explain
,咱们先全面的理解一下它。
explain 介绍
先看看 mysql 的官网文档是怎么形容 explain 的:
- EXPLAIN 能够应用于 SELECT,DELETE,INSERT,REPLACE,和 UPDATE 语句。
- 当 EXPLAIN 与可解释的语句一起应用时,MySQL 将显示来自优化器的无关语句执行打算的信息。也就是说,MySQL 解释了它将如何解决该语句,包含无关如何连贯表以及以何种程序连贯表的信息。
- 当 EXPLAIN 与非可解释的语句一起应用时,它将显示在命名连贯中执行的语句的执行打算。
- 对于 SELECT 语句,EXPLAIN 能够显示的其余执行打算的正告信息。
explain 详解
explain 的语法:
`{EXPLAIN | DESCRIBE | DESC}`
`tbl_name [col_name | wild]`
`{EXPLAIN | DESCRIBE | DESC}`
`[explain_type]`
`{explainable_stmt | FORCONNECTION connection_id}`
`explain_type: {`
`EXTENDED`
`| PARTITIONS`
`| FORMAT = format_name`
`}`
`format_name: {`
`TRADITIONAL`
`| JSON`
`}`
`explainable_stmt: {`
`SELECTstatement`
`| DELETEstatement`
`| INSERTstatement`
`| REPLACEstatement`
`| UPDATEstatement`
`}`
用一条简略的 sql 看看应用 explain
关键字的成果:
explain select * from test1;
执行后果:
从上图中看到执行后果中会显示 12 列信息,每列具体信息如下:
说白了,咱们要搞懂这些列的具体含意能力失常判断索引的应用状况。
话不多说,间接开始介绍吧。
id 列
该列的值是 select 查问中的序号,比方:1、2、3、4 等,它决定了表的执行程序。
某条 sql 的执行打算中个别会呈现三种状况:
- id 雷同
- id 不同
- id 雷同和不同都有
那么这三种情况表的执行程序是怎么样的呢?
1.id 雷同
执行 sql 如下:
explain select * from test1 t1 inner join test1 t2 on t1.id=t2.id
后果:
咱们看到执行后果中的两条数据 id 都是 1,是雷同的。
这种情况表的执行程序是怎么样的呢?
答案:从上到下执行,先执行表 t1,再执行表 t2。
执行的表要怎么看呢?
答案:看 table 字段,这个字段前面会具体解释。
2.id 不同
执行 sql 如下:
explain select * from test1 t1 where t1.id = (select id from test1 t2 where t2.id=2);
后果:
咱们看到执行后果中两条数据的 id 不同,第一条数据是 1,第二条数据是 2。
这种情况表的执行程序是怎么样的呢?
答案:序号大的先执行,这里会从下到上执行,先执行表 t2,再执行表 t1。
3.id 雷同和不同都有
执行 sql 如下:
explain
select t1.* from test1 t1
inner join (select max(id) mid from test1 group by id) t2
on t1.id=t2.mid
后果:
咱们看到执行后果中三条数据,后面两条数据的的 id 雷同,第三条数据的 id 跟后面的不同。
这种情况表的执行程序又是怎么样的呢?
答案:先执行序号大的,先从下而上执行。遇到序号雷同时,再从上而下执行。所以这个列子中表的程序程序是:test1、t1、
兴许你会在这里心生疑难:<
`derived2>` 是什么鬼?
它示意派生表,别急前面会讲的。
还有一个问题:id 列的值容许为空吗?
答案在前面揭晓。
select_type 列
该列示意 select 的类型。具体蕴含了如下 11 种类型:
然而罕用的其实就是上面几个:
上面看看这些 SELECT 类型具体是怎么呈现的:
1.SIMPLE
执行 sql 如下:```
explain select * from test1;
```
后果:
它只在简略 SELECT 查问中呈现,不蕴含子查问和 UNION,这种类型比拟直观就不多说了。
2.PRIMARY 和 SUBQUERY
执行 sql 如下:```
explain select * from test1 t1 where t1.id = (select id from test1 t2 where t2.id=2);
```
后果:
咱们看到这条嵌套查问的 sql 中,最外层的 t1 表是 PRIMARY 类型,而最外面的子查问 t2 表是 SUBQUERY 类型。
3.DERIVED
执行 sql 如下:```
explain
select t1.* from test1 t1
inner join (select max(id) mid from test1 group by id) t2
on t1.id=t2.mid
```
后果:
最初一条记录就是衍生表,它个别是 FROM 列表中蕴含的子查问,这里是 sql 中的分组子查问。
4.UNION 和 UNION RESULT
执行 sql 如下:
```
explain
select * from test1
union
select* from test2
```
后果:
test2 表是 UNION 关键字之后的查问,所以被标记为 UNION,test1 是最次要的表,被标记为 PRIMARY。而 <union1,2> 示意 id= 1 和 id= 2 的表 union,其后果被标记为 UNION RESULT。
UNION 和 UNION RESULT 个别会成对呈现。
此外,答复下面的问题:id 列的值容许为空吗?
如果认真看下面那张图,会发现 id 列是能够容许为空的,并且是在 SELECT 类型为:UNION RESULT 的时候。
table 列
该列的值示意输入行所援用的表的名称,比方后面的:test1、test2 等。
但也能够是以下值之一:
<unionM,N>
:具备和 id 值的行的 M 并集 N。<derivedN>
:用于与该行的派生表后果 id 的值 N。派生表可能来自(例如)FROM 子句中的子查问。<subqueryN>
:子查问的后果,其 id 值为 N
partitions 列
该列的值示意查问将从中匹配记录的分区
type 列
该列的值示意连贯类型,是查看索引执行状况的一个重要指标。蕴含如下类型:
执行后果从最好到最坏的的程序是从上到下。
咱们须要重点把握的是上面几种类型:
system > const > eq_ref > ref > range > index > ALL
在演示之前,先阐明一下 test2 表中只有一条数据:
并且 code 字段下面建了一个一般索引:
上面逐个看看常见的几个连贯类型是怎么呈现的:
- system
这种类型要求数据库表中只有一条数据,是 const 类型的一个特例,个别状况下是不会呈现的。
-
const
通过一次索引就能找到数据,个别用于主键或惟一索引作为条件的查问 sql 中,执行 sql 如下:
explain select * from test2 where id=1;
后果:
3.eq_ref
罕用于主键或惟一索引扫描。执行 sql 如下:
explain select * from test2 t1 inner join test2 t2 on t1.id=t2.id;
后果:
此时,有人可能感到不解,const 和 eq\_ref 都是对主键或惟一索引的扫描,有什么区别?
答:const 只索引一次,而 eq_ref 主键和主键匹配,因为表中有多条数据,个别状况下要索引屡次,能力全副匹配上。
4.ref
罕用于非主键和惟一索引扫描。执行 sql 如下:
explain select * from test2 where code = '001';
后果:
5.range
罕用于范畴查问,比方:between … and 或 In 等操作,执行 sql 如下:
explain select * from test2 where id between 1 and 2;
后果:
6.index
全索引扫描。执行 sql 如下:
explain select code from test2
后果:
7.ALL
全表扫描。执行 sql 如下:
explain select * from test2;
后果:
possible_keys 列
该列示意可能的索引抉择。
请留神,此列齐全独立于表的程序,这就意味着 possible_keys 在实践中,某些键可能无奈与生成的表程序一起应用。
如果此列是 NULL,则没有相干的索引。在这种状况下,您能够通过查看该 WHERE 子句以查看它是否援用了某些适宜索引的列,从而进步查问性能。
key 列
该列示意理论用到的索引。
可能会呈现 possible_keys 列为 NULL,然而 key 不为 NULL 的状况。
演示之前,先看看 test1 表构造:
test1 表中数据:
应用的索引:
code 和 name 字段应用了联结索引。
执行 sql 如下:
`explain select code from test1;`
后果:
这条 sql 预计没有应用索引,然而实际上应用了全索引扫描形式的索引。
key_len 列
该列示意应用索引的长度。下面的 key 列能够看出有没有应用索引,key_len 列则能够更进一步看出索引应用是否充沛。不出意外的话,它是最重要的列。
有个要害的问题浮出水面:key\_len 是如何计算的?
决定 key_len 值的三个因素:
1. 字符集
2. 长度
3. 是否为空
罕用的字符编码占用字节数量如下:
目前我的数据库字符编码格局用的:UTF8 占 3 个字节。
mysql 常用字段占用字节数:
此外,如果字段类型容许为空则加 1 个字节。
上图中的 184 是怎么算的?
184 = 30 3 + 2 + 30 3 + 2
再把 test1 表的 code 字段类型改成 char,并且改成容许为空:
执行 sql 如下:
explain select code from test1;
后果:
怎么算的?
183 = 30 3 + 1 + 30 3 + 2
** 还有一个问题:为什么这列示意索引应用是否充沛呢,还有应用不充沛的状况?
**
执行 sql 如下:
explain select code from test1 where code='001';
后果:
上图中应用了联结索引:idx_code_name,如果索引全匹配 key_len 应该是 183,但实际上却是 92,这就阐明没有应用所有的索引,索引应用不充沛。
ref 列
该列示意索引命中的列或者常量。
执行 sql 如下:
explain select * from test1 t1 inner join test1 t2 on t1.id=t2.id where t1.code='001';
后果:
咱们看到表 t1 命中的索引是 const(常量),而 t2 命中的索引是列 sue 库的 t1 表的 id 字段。
rows 列
该列示意 MySQL 认为执行查问必须查看的行数。
对于 InnoDB 表,此数字是估计值,可能并不总是精确的。
filtered 列
该列示意按表条件过滤的表行的预计百分比。最大值为 100,这示意未过滤行。值从 100 减小示意过滤量减少。
rows 显示了查看的预计行数,rows× filtered 显示了与下表连贯的行数。例如,如果 rows 为 1000 且 filtered 为 50.00(50%),则与下表连贯的行数为 1000×50%= 500。
Extra 列
该字段蕴含无关 MySQL 如何解析查问的其余信息,这列还是挺重要的,然而外面蕴含的值太多,就不一一介绍了,只列举几个常见的。
1.Impossible WHERE
示意 WHERE 前面的条件始终都是 false,
执行 sql 如下:
explain select code from test1 where 'a' = 'b';
后果:
2.Using filesort
示意按文件排序,个别是在指定的排序和索引排序不统一的状况才会呈现。
执行 sql 如下:
explain select code from test1 order by name desc;
后果:
这里建设的是 code 和 name 的联结索引,程序是 code 在前,name 在后,这里间接按 name 降序,跟之前联结索引的程序不一样。
3.Using index
示意是否用了笼罩索引,说白了它示意是否所有获取的列都走了索引。
下面那个例子中其实就用到了:Using index,因为只返回一列 code,它字段走了索引。
4.Using temporary
示意是否应用了长期表,个别多见于 order by 和 group by 语句。
执行 sql 如下:
explain select name from test1 group by name;
后果:
- Using where
示意应用了 where 条件过滤。
- Using join buffer
示意是否应用连贯缓冲。来自较早联接的表被局部读取到联接缓冲区中,而后从缓冲区中应用它们的行来与以后表执行联接。
索引优化的过程
1. 先用慢查问日志定位具体须要优化的 sql
2. 应用 explain 执行打算查看索引应用状况
3. 重点关注:
key(查看有没有应用索引)
key_len(查看索引应用是否充沛)
type(查看索引类型)
Extra(查看附加信息:排序、长期表、where 条件为 false 等)
个别状况下依据这 4 列就能找到索引问题。
4. 依据上 1 步找出的索引问题优化 sql
5. 再回到第 2 步
最初说一句(求关注,别白嫖我)
如果这篇文章对您有所帮忙,或者有所启发的话,帮忙扫描下发二维码关注一下,您的反对是我保持写作最大的能源。
求一键三连:点赞、转发、在看。
在公众号中回复:面试、代码神器、开发手册、工夫治理有超赞的粉丝福利,另外回复:加群,能够跟很多 BAT 大厂的前辈交换和学习。image.png
举荐浏览:
让人头痛的大事务问题到底要如何解决?
mybatis 日志性能是如何设计的?
zuul 如果两个 filter 的 order 一样,是如何排序的?
mysql 的这几个坑你踩过没?真是防不胜防
线程池最佳线程数量到底要如何配置?
这 8 种保障线程平安的技术你都晓得吗?
springboot 面试杀手锏 - 主动配置原理