[转帖]深入理解mysql-第十一章 mysql查询优化-Explain 详解（中）

深入,理解,mysql,第十一章,查询,优化,explain,详解 · 浏览次数 : 0

小编点评

**执行计划-filtered属性   Extra属性   额外信息** **执行计划-filtered属性   Extra属性   额外信息** **执行计划-filtered属性   Extra属性   额外信息** **执行计划-filtered属性   Extra属性   额外信息** **执行计划-filtered属性   Extra属性   额外信息** **执行计划-filtered属性   Extra属性   额外信息**

正文

一、执行计划-type属性 

    执行计划的一条记录就代表着MySQL对某个表的执行查询时的访问方法，其中的type列就表明了这个访问这个单表的方法具体是什么，比方说下边这个查询： 

  1. mysql> EXPLAIN SELECT * FROM s1 WHERE key1 = 'a';
  2. +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
  3. | id | select_type | table | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra |
  4. +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
  5. |  1 | SIMPLE      | s1    | NULL       | ref  | idx_key1      | idx_key1 | 303     | const |    8 |   100.00 | NULL  |
  6. +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
  7. 1 row in set, 1 warning (0.04 sec)

    可以看到type列的值是ref，表明MySQL即将使用ref访问方法来执行对s1表的查询。我们针对使用InnoDB存储引擎的表进行单表访问的方法如下：

  • const，当我们根据主键或者唯一二级索引列与常数进行等值匹配时，对单表的访问方法就是const（注意对于唯一二级索引来说查询null值是不适用该访问方法的）。
  •  ref，当通过普通二级索引进行等值匹配时来查询某个表，那么对该表的访问方法就可能是ref。
  • ref_or_null，当对普通二级索引进行等值匹配查询，该索引列的值也可以是NULL值时，那么对该表的访问方法就可能是ref_or_null。  
    1. mysql> EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' OR key1 IS NULL;
    2. +----+-------------+-------+------------+-------------+---------------+----------+---------+-------+------+----------+-----------------------+
    3. | id | select_type | table | partitions | type        | possible_keys | key      | key_len | ref   | rows | filtered | Extra                 |
    4. +----+-------------+-------+------------+-------------+---------------+----------+---------+-------+------+----------+-----------------------+
    5. |  1 | SIMPLE      | s1    | NULL       | ref_or_null | idx_key1      | idx_key1 | 303     | const |    9 |   100.00 | Using index condition |
    6. +----+-------------+-------+------------+-------------+---------------+----------+---------+-------+------+----------+-----------------------+
    7. 1 row in set, 1 warning (0.01 sec)

  •  range,如果使用普通二级索引获取某些范围区间的记录（包含IN、>和<等），那么就可能使用到range访问方法。

    1. mysql> EXPLAIN SELECT * FROM s1 WHERE key1 IN ('a', 'b', 'c');
    2. +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
    3. | id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra                 |
    4. +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
    5. |  1 | SIMPLE      | s1    | NULL       | range | idx_key1      | idx_key1 | 303     | NULL |   27 |   100.00 | Using index condition |
    6. +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
    7. 1 row in set, 1 warning (0.01 sec)

  • index,当我们可以使用索引覆盖要查询的列，但需要扫描全部的索引记录时，该表的访问方法就是index。下面查询中的搜索列表中只有key_part2一个列，而且搜索条件中也只有key_part3一个列，这两个列又恰好包含在idx_key_part这个索引中，可是搜索条件key_part3不能直接使用该索引进行ref或者range方式的访问，只能扫描整个idx_key_part索引的记录，所以查询计划的type列的值就是index。

    1. mysql> EXPLAIN SELECT key_part2 FROM s1 WHERE key_part3 = 'a';
    2. +----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+
    3. | id | select_type | table | partitions | type  | possible_keys | key          | key_len | ref  | rows | filtered | Extra                    |
    4. +----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+
    5. |  1 | SIMPLE      | s1    | NULL       | index | NULL          | idx_key_part | 909     | NULL | 9688 |    10.00 | Using where; Using index |
    6. +----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+
    7. 1 row in set, 1 warning (0.00 sec)
  •  all,全表扫描。 

二、执行计划-possible_keys和key属性

    在EXPLAIN语句输出的执行计划中，possible_keys列表示在某个查询语句中，对某个表执行单表查询时可能用到的索引有哪些，key列表示实际用到的索引有哪些，比方说下边这个查询：

  1. mysql> EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND key3 = 'a';
  2. +----+-------------+-------+------------+------+-------------------+----------+---------+-------+------+----------+-------------+
  3. | id | select_type | table | partitions | type | possible_keys     | key      | key_len | ref   | rows | filtered | Extra       |
  4. +----+-------------+-------+------------+------+-------------------+----------+---------+-------+------+----------+-------------+
  5. |  1 | SIMPLE      | s1    | NULL       | ref  | idx_key1,idx_key3 | idx_key3 | 303     | const |    6 |     2.75 | Using where |
  6. +----+-------------+-------+------------+------+-------------------+----------+---------+-------+------+----------+-------------+
  7. 1 row in set, 1 warning (0.01 sec)

    上述执行计划的possible_keys列的值是idx_key1,idx_key3，表示该查询可能使用到idx_key1,idx_key3两个索引，然后key列的值是idx_key3，表示经过查询优化器计算使用不同索引的成本后，最后决定使用idx_key3来执行查询比较划算。所以possible_keys列中的值并不是越多越好，可能使用的索引越多，查询优化器计算查询成本时就得花费更长时间，所以如果可以的话，尽量删除那些用不到的索引。

    注意：有时候，使用index访问方法来查询某个表时，possible_keys列是空的，而key列展示的是实际使用到的索引。

三、执行计划-key_len属性

  key_len列表示当优化器决定使用某个索引执行查询时，该索引记录的最大长度。它是由这三个部分构成的：

  • 对于使用固定长度类型的索引列来说，它实际占用的存储空间的最大长度就是该固定值，对于指定字符集的变长类型的索引列来说，比如某个索引列的类型是VARCHAR(100)，使用的字符集是utf8（每个字符三个字节），那么该列实际占用的最大存储空间就是100 × 3 = 300个字节。

  • 如果该索引列可以存储NULL值，则key_len比不可以存储NULL值时多1个字节。

  • 对于变长字段来说，都会有2个字节的空间来存储该变长列的实际长度。

比如下边这个查询： 

  1. mysql> EXPLAIN SELECT * FROM s1 WHERE id = 5;
  2. +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
  3. | id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
  4. +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
  5. |  1 | SIMPLE      | s1    | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
  6. +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
  7. 1 row in set, 1 warning (0.01 sec)

    由于id列的类型是INT，并且不可以存储NULL值，所以在使用该列的索引时key_len大小就是4。当索引列可以存储NULL值时，key_len列就变成了5，比使用id列的索引时多了1。

    对于可变长度的索引列来说，比如key1列的类型是VARCHAR(100)，所以该列实际最多占用的存储空间就是300字节，又因为该列允许存储NULL值，所以key_len需要加1，又因为该列是可变长度列，所以key_len需要加2，所以最后ken_len的值就是303。

  key_len列主要是为了让我们区分某个使用联合索引的查询具体用了几个索引列，比如idx_key_part 这个联合索引如果只用到了联合索引前两个索引列，长度显示就是606。

四、执行计划-ref属性

    当使用索引列等值匹配的条件去执行查询时，也就是在访问方法(type)是const、ref、ref_or_null其中之一时，ref列展示的就是与索引列作等值匹配的值得类型，比如只是一个常数或者是某个列。const代表常数，某一列名（数据库名称.s1.id）代表匹配的是一列，func代表匹配的是函数。

  1. mysql> EXPLAIN SELECT * FROM s1 WHERE key1 = 'a';
  2. +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
  3. | id | select_type | table | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra |
  4. +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
  5. |  1 | SIMPLE      | s1    | NULL       | ref  | idx_key1      | idx_key1 | 303     | const |    8 |   100.00 | NULL  |
  6. +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
  7. 1 row in set, 1 warning (0.01 sec)

    可以看到ref列的值是const，表明在使用idx_key1索引执行查询时，与key1列作等值匹配的对象是一个常数。

  1. mysql> EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.id = s2.id;
  2. +----+-------------+-------+------------+--------+---------------+---------+---------+-----------------+------+----------+-------+
  3. | id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref             | rows | filtered | Extra |
  4. +----+-------------+-------+------------+--------+---------------+---------+---------+-----------------+------+----------+-------+
  5. |  1 | SIMPLE      | s1    | NULL       | ALL    | PRIMARY       | NULL    | NULL    | NULL            | 9688 |   100.00 | NULL  |
  6. |  1 | SIMPLE      | s2    | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | mytest.s1.id |    1 |   100.00 | NULL  |
  7. +----+-------------+-------+------------+--------+---------------+---------+---------+-----------------+------+----------+-------+
  8. 2 rows in set, 1 warning (0.00 sec)

  可以看到对被驱动表s2的访问方法是eq_ref，而对应的ref列的值是mytest.s1.id，这说明在对被驱动表进行访问时会用到PRIMARY索引，也就是聚簇索引与一个列进行等值匹配的条件，于s2表的id作等值匹配的对象就是mytest.s1.id列（注意这里把数据库名也写出来了）。

五、执行计划-rows属性

   如果查询优化器决定使用全表扫描的方式对某个表执行查询时，执行计划的rows列就代表预计需要扫描的行数，如果使用索引来执行查询时，执行计划的rows列就代表预计扫描的索引记录行数。比如下边这个查询：

  1. mysql> EXPLAIN SELECT * FROM s1 WHERE key1 > 'z';
  2. +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
  3. | id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra                 |
  4. +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
  5. |  1 | SIMPLE      | s1    | NULL       | range | idx_key1      | idx_key1 | 303     | NULL |  266 |   100.00 | Using index condition |
  6. +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
  7. 1 row in set, 1 warning (0.00 sec)

  我们看到执行计划的rows列的值是266，这意味着查询优化器在经过分析使用idx_key1进行查询的成本之后，觉得满足key1 > 'z'这个条件的记录只有266条。

六、执行计划-filtered属性

    之前在分析连接查询的成本时，知道了MySQL在计算驱动表扇出时采用的一个策略：

  • 如果使用的是全表扫描的方式执行的单表查询，那么计算驱动表扇出时需要估计出满足搜索条件的记录到底有多少条。

  • 如果使用的是索引执行的单表扫描，那么计算驱动表扇出的时候需要估计出满足除使用到对应索引的搜索条件外的其他搜索条件的记录有多少条。

比方说下边这个查询：

  1. mysql> EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND common_field = 'a';
  2. +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+------------------------------------+
  3. | id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra                              |
  4. +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+------------------------------------+
  5. |  1 | SIMPLE      | s1    | NULL       | range | idx_key1      | idx_key1 | 303     | NULL |  266 |    10.00 | Using index condition; Using where |
  6. +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+------------------------------------+
  7. 1 row in set, 1 warning (0.00 sec)

    从执行计划的key列中可以看出来，该查询使用idx_key1索引来执行查询，从rows列可以看出满足key1 > 'z'的记录有266条。执行计划的filtered列就代表查询优化器预测在这266条记录中，有多少条记录满足其余的搜索条件，也就是common_field = 'a'这个条件的百分比。此处filtered列的值是10.00，说明查询优化器预测在266条记录中有10.00%的记录满足common_field = 'a'这个条件。对于单表查询来说，这个filtered列的值没什么意义，我们更关注在连接查询中驱动表对应的执行计划记录的filtered值。比如以上SQL内连接S2表，那么S1作为驱动表，这个10%就很有意义，就代表了要对s2表进行多少次查询。

  1. mysql> EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.key1 = s2.key1 WHERE s1.common_field = 'a';
  2. +----+-------------+-------+------------+------+---------------+----------+---------+-------------------+------+----------+-------------+
  3. | id | select_type | table | partitions | type | possible_keys | key      | key_len | ref               | rows | filtered | Extra       |
  4. +----+-------------+-------+------------+------+---------------+----------+---------+-------------------+------+----------+-------------+
  5. |  1 | SIMPLE      | s1    | NULL       | ALL  | idx_key1      | NULL     | NULL    | NULL              | 9688 |    10.00 | Using where |
  6. |  1 | SIMPLE      | s2    | NULL       | ref  | idx_key1      | idx_key1 | 303     | xiaohaizi.s1.key1 |    1 |   100.00 | NULL        |
  7. +----+-------------+-------+------------+------+---------------+----------+---------+-------------------+------+----------+-------------+
  8. 2 rows in set, 1 warning (0.00 sec)

    从执行计划中可以看出来，查询优化器打算把s1当作驱动表，s2当作被驱动表。我们可以看到驱动表s1表的执行计划的rows列为9688， filtered列为10.00，这意味着驱动表s1的扇出值就是9688 × 10.00% = 968.8，这说明还要对被驱动表执行大约968次查询。

七、执行计划-Extra属性

  Extra列是用来说明一些额外信息的，我们可以通过这些额外信息来更准确的理解MySQL到底将如何执行给定的查询语句。MySQL提供的额外信息有好几十个，我们举例一部分说明。

  • No tables used，当查询语句的没有FROM子句时将会提示该额外信息。
  • Impossible WHERE，查询语句的WHERE子句永远为FALSE时将会提示该额外信息。
  • Using index，当我们的查询列表以及搜索条件中只包含属于某个索引的列，也就是在可以使用索引覆盖的情况下不需要进行回表操作，在Extra列将会提示该额外信息。
  • Using index condition，有些搜索条件中虽然出现了索引列，但却不能使用到索引，比如左模糊查询。
  • Using where，当我们使用全表扫描来执行对某个表的查询，并且该语句的WHERE子句中有针对该表的搜索条件。
  • Using join buffer (Block Nested Loop)，在连接查询执行过程中，当被驱动表不能有效的利用索引加快访问速度，MySQL一般会为其分配一块名叫join buffer的内存块来加快查询速度。
  • Using temporary，在许多查询的执行过程中，MySQL可能会借助临时表来完成一些功能，比如去重、排序之类的，比如我们在执行许多包含DISTINCT、GROUP BY、UNION等子句的查询过程中，如果不能有效利用索引来完成查询，MySQL很有可能寻求通过建立内部的临时表来执行查询。如果查询中使用到了内部的临时表，在执行计划的Extra列将会显示Using temporary提示。
文章知识点与官方知识档案匹配，可进一步学习相关知识
MySQL入门技能树查询优化 SHOW STATUS58201 人正在系统学习中
深入 理解 mysql 第十一章 查询 优化 explain 详解

与[转帖]深入理解mysql-第十一章 mysql查询优化-Explain 详解（中）相似的内容：

[转帖]深入理解mysql-第十一章 mysql查询优化-Explain 详解（中）

一、执行计划-type属性 执行计划的一条记录就代表着MySQL对某个表的执行查询时的访问方法，其中的type列就表明了这个访问这个单表的方法具体是什么，比方说下边这个查询： mysql> EXPLAIN SELECT * FROM s1 WHERE key1 = 'a';+ + + + + + +

深入 理解 mysql 第十一章
0
[转帖]深入理解mysql-第十章 mysql查询优化-Explain 详解（上）

目录 一、初识Explain 二、执行计划-table属性 三、执行计划-id属性 四、执行计划-select_type属性 一条查询语句在经过MySQL查询优化器的各种基于成本和规则的优化会后生成一个所谓的执行计划，这个执行计划展示了接下来具体执行查询的方式，比如多表连接的顺序是什么，对于每个表采

深入 理解 mysql 第十章
0
[转帖]深入理解mysql-第十二章 mysql查询优化-Explain 详解（下）

我们前面两章详解了Explain的各个属性，我们看到的都是mysql已经生成的执行计划，那这个执行计划的是如何生成的？我们能看到一些过程指标数据吗？实际mysql贴心为我们提供了执行计划的各项成本评估指标的以及优化器生成执行计划的整个过程的方法。 一、查看执行计划计算的成本数据 我们上边介绍的EXP

深入 理解 mysql 第十二章
0
[转帖]深入理解mysql-第六章 mysql存储引擎InnoDB的索引-B+树索引

一、引入索引 在没有索引的情况下，不论是根据主键列或者其他列的值进行查找，由于我们并不能快速的定位到记录所在的页，所以只能从第一个页沿着双向链表一直往下找，因为要遍历所有的数据页，时间复杂度就是O(n),所以这种方式显然是超级耗时的。所以我们需要采取一定的数据结构来存储数据，方便我们进行数据的增删改

深入 理解 mysql 第六章
0
[转帖]深入理解mysql-第五章 InnoDB记录存储结构-页结构

前言： 页是InnoDB管理存储空间的基本单位，上一章我们主要分析了页中的主要的构成行的存储结构-行格式，其中简单提了一下页的概念。这章我们详细讲解一下页的存储结构。 一、数据页结构 前边我们简单提了一下页的概念，它是InnoDB管理存储空间的基本单位，一个页的大小一般是16KB。和存储一条条数据的

深入 理解 mysql 第五章
0
[转帖]Redis 运维实战 第01期：Redis 复制

https://cloud.tencent.com/developer/article/1986816 作者简介 马听，多年 DBA 实战经验，对 MySQL、 Redis、ClickHouse 等数据库有一定了解，专栏《一线数据库工程师带你深入理解 MySQL》作者。 从这篇文章开始，将出几期 R

redis 实战 复制
0
[转帖]MySQL 慢查询日志深入理解

https://www.jb51.net/article/210312.htm + 目录 什么是慢查询日志 MySQL的慢查询日志是 MySQL提供的一种日志记录，它用来记录在 MySQL 中响应时间超过阀值的语句，具体指运行时间超过long_query_time 值的 SQL，则会被记录到慢查询日

mysql 查询 日志 深入
0
[转帖]Intel PAUSE指令变化如何影响MySQL的性能

https://zhuanlan.zhihu.com/p/581200704 导读 x86、arm指令都很多，无论是应用程序员还是数据库内核研发大多时候都不需要对这些指令深入理解，但是 Pause 指令和数据库操作太紧密了，本文通过一次非常有趣的性能优化来引入对 Pause 指令的理解，期望可以事半

intel pause 指令 变化
0
【转帖】mysql一个索引块有多少指针_深刻理解MySQL系列之索引

索引 查找一条数据的过程 先看下InnoDB的逻辑存储结构：node 表空间：能够看作是InnoDB存储引擎逻辑结构的最高层，全部的数据都存放在表空间中。默认有个共享表空间ibdata1。若是启用innodb_file_per_table参数，须要注意每张表的表空间内存放的只是数据、索引和插入缓冲B

mysql 一个 索引 多少
0
[转帖]深入理解同步机制---内核自旋锁

https://switch-router.gitee.io/blog/spinlock/ 进程(线程)间的同步机制是面试时的常见问题，所以准备用一个系列来好好整理下用户态与内核态的各种同步机制。本文就以内核空间的一种基础同步机制—自旋锁开始好了 自旋锁是什么 自旋锁就是一个二状态的原子(atomi

深入 理解 同步 机制
0
# 热门排行
微软 New Bing AI 申请与使用保姆级教程（免魔法） ChatGPT API使用介绍 ChatGPT开发实战 一篇带你了解如何使用纯前端类Excel表格构建现金流量表 手把手教你玩转 Excel 数据透视表 为什么 C# 可能是最好的第一编程语言 .NET 入门到高级路线 提高工作效率的神器：基于前端表格实现Chrome Excel扩展插件 React + Springboot + Quartz，从0实现Excel报表自动化 用Echarts实现前端表格引用从属关系可视化
© PmDaddy. 2023 PmDaddy教程网 All rights reserved.