explain | 索引优化的这把绝世好剑,你真的会用吗?(上)

简介: explain | 索引优化的这把绝世好剑,你真的会用吗?(上)

前言


对于互联网公司来说,随着用户量和数据量的不断增加,慢查询是无法避免的问题。一般情况下如果出现慢查询,意味着接口响应慢、接口超时等问题。如果是高并发的场景,可能会出现数据库连接被占满的情况,直接导致服务不可用。

慢查询的确会导致很多问题,我们要如何优化慢查询呢?

主要解决办法有:

  • 监控sql执行情况,发邮件、短信报警,便于快速识别慢查询sql
  • 打开数据库慢查询日志功能
  • 简化业务逻辑
  • 代码重构、优化
  • 异步处理
  • sql优化
  • 索引优化

其他的办法先不说,后面有机会再单独介绍。今天我重点说说索引优化,因为它是解决慢查询sql问题最有效的手段。

如何查看某条sql的索引执行情况呢?

没错,在sql前面加上explain关键字,就能够看到它的执行计划,通过执行计划,我们可以清楚的看到表和索引执行的情况,索引有没有执行、索引执行顺序和索引的类型等。

索引优化的步骤是:

  1. 使用explain查看sql执行计划
  2. 判断哪些索引使用不当
  3. 优化sql,sql可能需要多次优化才能达到索引使用的最优值

既然索引优化的第一步是使用explain,我们先全面的了解一下它。


正文


explain介绍


先看看mysql的官方文档是怎么描述explain的:

26.png

  • 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;

执行结果:

27.png

从上图中看到执行结果中会显示12列信息,每列具体信息如下:

28.png

说白了,我们要搞懂这些列的具体含义才能正常判断索引的使用情况。

话不多说,直接开始介绍吧。


id列


该列的值是select查询中的序号,比如:1、2、3、4等,它决定了表的执行顺序。

某条sql的执行计划中一般会出现三种情况:

  1. id相同
  2. id不同
  3. id相同和不同都有

那么这三种情况表的执行顺序是怎么样的呢?

1.id相同

执行sql如下:

explain select * from test1 t1 inner join test1 t2 on t1.id=t2.id

结果:

31.png

我们看到执行结果中的两条数据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);

结果:

32.png

我们看到执行结果中两条数据的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

结果:

33.png

我们看到执行结果中三条数据,前面两条数据的的id相同,第三条数据的id跟前面的不同。

这种情况表的执行顺序又是怎么样的呢?

答案:先执行序号大的,先从下而上执行。遇到序号相同时,再从上而下执行。所以这个列子中表的顺序顺序是:test1、t1、

也许你会在这里心生疑问:<derived2> 是什么鬼?

它表示派生表,别急后面会讲的。

还有一个问题:id列的值允许为空吗?

答案在后面揭晓。


select_type列


该列表示select的类型。具体包含了如下11种类型:

34.png

但是常用的其实就是下面几个:

类型 含义
SIMPLE 简单SELECT查询,不包含子查询和UNION
PRIMARY 复杂查询中的最外层查询,表示主要的查询
SUBQUERY SELECT或WHERE列表中包含了子查询
DERIVED FROM列表中包含的子查询,即衍生
UNION UNION关键字之后的查询
UNION RESULT 从UNION的表获取结果集


下面看看这些SELECT类型具体是怎么出现的:


  1. SIMPLE


    执行sql如下:
explain select * from test1;
  1. 结果:
    35.png
    它只在简单SELECT查询中出现,不包含子查询和UNION,这种类型比较直观就不多说了。


  1. PRIMARYSUBQUERY
    执行sql如下:
explain select * from test1 t1 where t1.id = (select id from  test1 t2 where  t2.id=2);


结果:
36.png
我们看到这条嵌套查询的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

结果:
37.png最后一条记录就是衍生表,它一般是FROM列表中包含的子查询这里是sql中的分组子查询。

4.UNION 和 UNION RESULT
执行sql如下:

explain
select * from test1
union
select* from test2

结果:
38.png

    test2表是UNION关键字之后的查询,所以被标记为UNION,test1是最主要的表,被标记为PRIMARY。而表示id=1和id=2的表union,其结果被标记为UNION RESULT。


UNION 和 UNION RESULT一般会成对出现。


此外,回答上面的问题:id列的值允许为空吗?


如果仔细看上面那张图,会发现id列是可以允许为空的,并且是在SELECT类型为: UNION RESULT的时候。


table列


该列的值表示输出行所引用的表的名称,比如前面的:test1、test2等。

但也可以是以下值之一:

  • :具有和id值的行的M并集N。
  • :用于与该行的派生表结果id的值N。派生表可能来自(例如)FROM子句中的子查询 。
  • :子查询的结果,其id值为N


partitions列


该列的值表示查询将从中匹配记录的分区


type列


该列的值表示连接类型,是查看索引执行情况的一个重要指标。包含如下类型:

51.png

执行结果从最好到最坏的的顺序是从上到下。

我们需要重点掌握的是下面几种类型:

system > const > eq_ref > ref > range > index > ALL

在演示之前,先说明一下test2表中只有一条数据:

52.png

并且code字段上面建了一个普通索引:

53.png


下面逐一看看常见的几个连接类型是怎么出现的:

  1. system
    这种类型要求数据库表中只有一条数据,是const类型的一个特例,一般情况下是不会出现的。

  2. const
    通过一次索引就能找到数据,一般用于主键或唯一索引作为条件的查询sql中,执行sql如下:
explain select * from test2 where id=1;

结果:
54.png

3.eq_ref
常用于主键或唯一索引扫描。执行sql如下:

explain select * from test2 t1 inner join test2 t2 on t1.id=t2.id;

结果:
55.png

此时,有人可能感到不解,const和eq_ref都是对主键或唯一索引的扫描,有什么区别?

答:const只索引一次,而eq_ref主键和主键匹配,由于表中有多条数据,一般情况下要索引多次,才能全部匹配上。

4.ref
常用于非主键和唯一索引扫描。执行sql如下:

explain select * from test2 where code = '001';

结果:
55.png

5.range
常用于范围查询,比如:between ... and 或 In 等操作,执行sql如下:

explain select * from test2 where id between 1 and 2;

结果:

56.png

6.index
全索引扫描。执行sql如下

explain select code from test2;

结果:
57.png

7.ALL
全表扫描。执行sql如下

explain select *  from test2;

结果:

59.png

 

possible_keys列


该列表示可能的索引选择。


请注意,此列完全独立于表的顺序,这就意味着possible_keys在实践中,某些键可能无法与生成的表顺序一起使用。

60.png

如果此列是NULL,则没有相关的索引。在这种情况下,您可以通过检查该WHERE 子句以检查它是否引用了某些适合索引的列,从而提高查询性能。

相关文章
|
25天前
|
SQL 存储 关系型数据库
如何巧用索引优化SQL语句性能?
本文从索引角度探讨了如何优化MySQL中的SQL语句性能。首先介绍了如何通过查看执行时间和执行计划定位慢SQL,并详细解析了EXPLAIN命令的各个字段含义。接着讲解了索引优化的关键点,包括聚簇索引、索引覆盖、联合索引及最左前缀原则等。最后,通过具体示例展示了索引如何提升查询速度,并提供了三层B+树的存储容量计算方法。通过这些技巧,可以帮助开发者有效提升数据库查询效率。
38 2
|
13天前
|
SQL Oracle 关系型数据库
SQL优化-使用联合索引和函数索引
在一次例行巡检中,发现一条使用 `to_char` 函数将日期转换为字符串的 SQL 语句 CPU 利用率很高。为了优化该语句,首先分析了 where 条件中各列的选择性,并创建了不同类型的索引,包括普通索引、函数索引和虚拟列索引。通过对比不同索引的执行计划,最终确定了使用复合索引(包含函数表达式)能够显著降低查询成本,提高执行效率。
|
19天前
|
SQL 机器学习/深度学习 关系型数据库
最完整的Explain总结,SQL优化不再困难!
最完整的Explain总结,SQL优化不再困难!
|
6月前
|
SQL 关系型数据库 MySQL
explain是什么?explain优缺点及如何使用explain优化SQL
explain是什么?explain优缺点及如何使用explain优化SQL
139 1
|
6月前
|
存储 SQL 关系型数据库
explain详解和索引最佳实践
explain详解和索引最佳实践
|
存储 关系型数据库 MySQL
MySQL基础应用拓展、索引及执行计划
MySQL基础应用拓展、索引及执行计划
80 0
MySQL基础应用拓展、索引及执行计划
|
SQL 存储 缓存
Mysql优化之explain你真的会吗?
Mysql优化之explain你真的会吗?
71 0
|
NoSQL MongoDB 开发者
索引的使用 执行计划 | 学习笔记
快速学习 索引的使用 执行计划
索引的使用 执行计划 | 学习笔记
|
存储 SQL 缓存
MySql索引分析及查询优化
MySql索引分析及查询优化
205 0
MySql索引分析及查询优化
|
SQL 关系型数据库 MySQL
MySQL索引详解(优缺点,何时需要/不需要创建索引,索引及sql语句的优化)
目录 MySQL索引详解(优缺点,何时需要/不需要创建索引,索引及sql语句的优化) 一、什么是索引? 二、索引的作用? 三、优点: 四、缺点 五、什么时候需要创建索引 六、什么时候不需要创建索引 七、索引的分类: 八、索引和sql语句的优化 1、前导模糊查询不能使用索引, 2、Union、in、or可以命中索引,建议使用in 3、负条件查询不能使用索引,可以优化为in查询, 4、联合索引最左前缀原则,又叫最左侧查询, 5、建立联合查询时,区分度最高的字段在最左边 6、如果建立了(a,b)联合索引,就不必再单独建立a索引。 7、存在非等号和等号混合判断条件时,在建索引时,要把等号条件的列前置
384 0
MySQL索引详解(优缺点,何时需要/不需要创建索引,索引及sql语句的优化)