Explication détaillée de l'optimisation MySQL 5.7 : Expliquer le plan d'exécution

Tutoriel vidéo MySQLIntroduction à la colonneExpliquez le plan d'exécution

Recommandé (gratuit) : Tutoriel vidéo MySQL

Table des matières

• 1 Introduction
• 2. colonne de résultat Explication détaillée
• • 2.1 id
  • 2.2 select_type
  • 2.3 table
  • 2.4 partitions
  • 2.5 type (très important)
  • 2,6 possible_keys
  • 2,7 key
  • 2,8 key_len
  • 2,9 ref
  • 3,10 lignes
  • 2.11 filtré
  • 2.12 Extra

[Note]

• Environnement système actuel : MySQL 5.7, les autres versions sont légèrement différentes , et sera mis à jour ultérieurement. Prenez le temps d'expliquer individuellement.
• Seuls les scénarios courants sont introduits. D'autres scénarios rares ne seront pas étudiés pour le moment. Le cas échéant, vous pourrez les retrouver dans les documents officiels.
• Pas pour les débutants, vous devez avoir une certaine compréhension de la structure de données sous-jacente MySQL de l'arborescence B+.

Référence du document :
Document Explain officiel de MySQL

Introduction

UtilisationEXPLAIN Le mot-clé peut simuler l'exécution de l'optimiseur de l'instruction SQL et analyser le goulot d'étranglement des performances de l'instruction de requête.

2. Expliquez l'explication détaillée de la colonne de résultat

2.1 id

• id Le numéro de la colonne est select Le numéro de série a généralement autant de select qu'il y a de id (la requête de table commune aura répété id), et l'ordre de id augmente dans l'ordre dans lequel select apparaît. Plus le
• id est grand, plus la priorité d'exécution est élevée. Le id est le même (apparaissant généralement dans les requêtes de table conjointe) et est exécuté de haut en bas. dernière exécution de id. NULL

2.2 select_type

Indique si la ligne correspondante est une requête simple ou complexe. Les valeurs communes sont : select_type

• : requête simple, la requête ne contient pas de sous-requêtes ni d'unions. simple

•  : La sélection la plus externe dans une requête complexe. primary

•  : sous-requête incluse dans la sélection (pas dans la clause from) subquery

•  : incluse dans la sous-requête du formulaire dans l'instruction , MySQL placera les résultats dans une table temporaire, également appelée table dérivée. derived

•  : La deuxième sélection ou les suivantes dans le syndicat. union

[Note] Dans

, la table dérivée sera fusionnée et optimisée. Si vous souhaitez visualiser intuitivement la valeur de MySQL 5.7, vous devez désactiver temporairement cette fonction. (la valeur par défaut est activée), select_typeTout ce qui implique des tables dérivées dans l'introduction suivante nécessite cette opération.

# 关闭衍生表的合并优化（只对该会话有效）set session optimizer_switch='derived_merge=off'; # 打开衍生表的合并优化（只对该会话有效）set session optimizer_switch='derived_merge=on';

Table 2.3

La table correspondant à la requête de ligne.

[Note]

Lorsqu'il y a une sous-requête dans
• , la colonne du tableau est au format from 子句, ce qui signifie que l'exécution de cette ligne est la <derivenN> requête de ligne. id = N
Quand il y a
• , les données du tableau sont au format union, et <union M,N> représente la M 和 N ligne union qui participe à select. id

Partions 2.4

A suivre. . .

Type 2.5 (très important)

• Indique le type d'association (type d'accès, ou type de requête) de cette ligne de requêtes Cette valeur. peut comprendre la plage approximative des enregistrements de données de requête pour cette ligne. type
Les valeurs communes du meilleur au pire sont :
• De manière générale, si nous voulons garantir l'efficacité, nous devons optimiser nos déclarations pour au moins atteindre le niveau system > const > eq_ref > ref > range > index > ALL, et optimiser si possible To range; ref est généralement utilisé pour la recherche par plage, donc en d'autres termes, en plus de la recherche par plage, nous ferions mieux d'optimiser d'autres instructions de requête au niveau range. Valeurs communes ref Pas besoin d'accéder aux tables et index.

 : MySQL peut optimiser une certaine partie de la requête et la convertir en constante (vous pouvez visualiser les résultats de l'optimisation via

), principalement la clé primaire de la requête (

) ou index de clé unique (
• ), car il n'y a pas de doublons, un seul enregistrement peut être interrogé au maximum, la vitesse est donc relativement rapide.

  est un cas particulier de NULL, c'est-à-dire MySQL lorsqu'il n'y a qu'un seul enregistrement dans la table temporaire.

  # 表里有一个主键id为1的记录 - constexplain select * from student where id = 1# 派生表里面只有一条记录 - systemexplain select * from (select * from student where id = 1) tmp# 注： 如果查询的列中有 text 类型，那么在这里 type 会变为 ALL ，# 因为无法使用内存临时表，只能在磁盘上创建临时表，所以性能上会有所损耗，效果等同于全表查询 ALL。

• req_ref：当主键或唯一键索引的相关列并联接使用时（联表查询），最多匹配一条符合条件的记录。这是除了 const之外的最好的联接类型，简单的 select 查询不会出现 req_ref，更多出现在联表查询。

  # 虽然返回结果中有多条记录，但是在查询中一个学生id只对应一个班级，所以查询班级的时候为 req_ref，# 但是查询 student 的时候是 ALL，全表查询explain select * from student left join banji on student.id = banji.student_id

【注】在查询的过程中的返回结果如下：

当联接表查询时候会看作是一条查询 SQL，所以它们对应的 id 是一样的，当 id 都是一样的时候，按照从上到下的顺序依次执行，这里是先查询班级所有的学生（全表查询 ALL），然后根据学生id查找出学生对应的班级信息（req_ref）。

• ref：当使用普通索引（Normal）或者是联合索引的部分前缀时，索引要和某个值进行比较，可能会找到多个符合条件的记录行，从辅助索引的根节点开始对比并找到相应的记录。

  # 简单的 select 查询，name 是普通索引（Normal Index）explain select * from student where name = '张三';# 简单 select 查询，banji_id (第一个) 和 student_id (第二个) 的联合索引EXPLAIN SELECT * FROM banji_student WHERE banji_student.banji_id = 3# 关联表查询# 包含 banji 表，banji_student 是班级与学生的关系表# 关系表中有 banji_id (第一个) 和 student_id (第二个) 的联合索引 idx_banji_stu_id 索引，# 以下查询只用到了联合索引的 banji_id (第一个)explain select * from banji_id from banji left join banji_student 
  	on banji.id = banji_student.banji_id

• range：范围扫描，通常出现在 in，between，>，<，>= 等操作中，使用一个索引来检索给定范围的行。

  # 查询 id 大于 1 的学生信息explain select * from student where id > 2;

• index：

  • 扫描全索引就能拿到结果，一般是扫描某个二级索引（辅助索引，除了主键之外的索引）。这种索引不会从主键索引树根节点开始查找，而是直接对二级索引的叶子节点遍历和扫描，从而查找出相应的记录行，速度比较慢；
  • 这种查询方式一般为使用覆盖索引，查询所需的所有结果集在二级索引与主键索引中都有的情况下，由于二级索引一般比较小（因为二级索引是非聚集的，其叶子节点是存放的主键索引相应的地址，而主键索引是聚集的，其叶子节点存放的是完整的数据集），所以优先走二级索引，这种情况通常比 ALL 快一些。
  • 在某些情况下，如果表的列数特别多，这个时候通过辅助索引查询的性能就不如直接使用主键索引效率高（如果查询了辅助索引的话，还会返回到主键索引中进行查找更多的字段，也就是回表查询，当然在某些情况下使用回表查询的性能也会比只使用主键索引的性能高），这个时候会走主键索引，这种情况也比 ALL 快。

  # student 表只有id主键，name 普通索引select * from student;# 这个时候会走 name 索引# 因为 name 是普通索引，所以如果加 where 的话可以达到 ref 级别select * from student where name = 'Ana'

  覆盖索引定义：覆盖索引一般针对于辅助索引，并不是真正的索引，只是索引查找的一种方式。如果 select 查询的字段都在辅助索引树中全部拿到，这种情况一般是使用了覆盖索引，不需要通过辅助索引树找到主键，再通过主键去主键索引树里获取其它字段值。

• ALL：全表扫描，扫描主键（聚簇、聚集）索引树的所有叶子节点，通常这种情况下要根据业务场景来增加其他索引进行优化。

  # id 为主键的 student 表，没有其他索引，该查询为 ALL.select * from student

2.6 possible_keys

possible_keys 主要显示查询可能用到哪些索引来查找，只是可能会使用，并不代表一定会使用。

常见值说明：

• NULL: 没有相关索引，如果是 NULL 的话，可以考虑在 where 子句中创建一个适当的索引来提高查询性能，然后继续用 explain 查看其效果；也有可能出现 possible_keys 为 NULL，但是 key 有值，实际走了索引。
• 有列值：如果显示表中的某列，则表示可能会走这一列对应列值的索引；如果 possible_keys 有值，但是 key 显示 NULL，这种情况一般存在于表中数据量不大的情况，因为 MySQL 语句优化器认为索引对此查询的帮助不大，从而选择了全表查询。

2.7 key

• key 表示 MySQL 实际采用哪个索引来优化对该表的查询。
• 如果没有使用索引，则该列为 NULL，如果想强制 MySQL 使用或忽略 possible_keys 列中的索引，可以在查询中使用 force index 或 ignore index.

2.8 key_len

显示了 MySQL 索引所使用的字节数，通过这个数值可以计算具体使用了索引中的哪些列（主要用于联合索引的优化）。

【注】索引最大长度是 768 字节，当字符串过长时，MySQL 会做一个类似左前缀索引的处理，将前半部分的字符提取出来做索引。

示例：一个学生与班级的关系表：banji_student，存在使用 banji_id 与 student_id 两个列组合的联合索引，并且每个索引 int 都是 4 字节，通过 key_len 值为 4 可以知道只使用了联合索引的第一列：banji_id 来执行索引查找。

# 只使用了联合索引的第一列select * from banji_student where banji_id = 2

key_len 的计算规则如下：

• 字符串：常见的是 char(n) 和 varchar(n)，从 MySQL 5.0.3 之后，n 均表示字符数，而不是字节数，如果是 UTF-8，一个数字或字母占1个字节，一个汉字占3个字节。

  	描述
  char(n)	非汉字长度为 n，如果存放汉字长度为 3n 字节
  varchar(n)	非汉字长度为 n+2，如果存放汉字长度为 3n+2 字节；因为 varchar 是可变长字符串，需要 2 字节来存储字符串长度

• 数值类型：

  	描述
  tinyint	长度为 1 字节
  smallint	长度为 2 字节
  int	长度为 4 字节
  bigint	长度为 8 字节

• 时间类型：

  	描述
  date	长度为 3 字节
  timestamp	长度为 4 字节
  datetime	长度为 8 字节

• NULL

  如果字段允许设置为 NULL，则需要 1 字节来记录是否为 NULL； Not NULL 的列则不需要。

2.9 ref

显示了在使用 key 列中实际的索引时，表查找时所用到的列名和常量；常见的为 const 常量或索引关联查询的字段（列）名。

# 使用了常量 2，所以在查询的时候 ref 为 constselect * from student where id = 2# 关联表查询# 包含 banji 表，banji_student 是班级与学生的关系表# 关系表中有 banji_id (第一个) 和 student_id (第二个) 的联合索引 idx_banji_stu_id 索引# 这里的 ref 为 test.id ，也就是指的是 banji.idexplain select * from banji_id from banji left join banji_student 		on banji.id = banji_student.banji_id

3.10 rows

显示预计查询的结果数，并不是真正的结果集中的记录（行）数，仅供参考。

2.11 filtered

未完待续。。。

2.12 Extra

这一列展示的是额外的信息，存在很多值，且在不同的场景下以及不同版本的 MySQL 所表示的意思也不同，只能是表示大概的意思并且仅做优化参考，这里只介绍常见的值。

• Using index：使用覆盖索引，在 type 相同的情况下， Extra 的值为 Using index 要比为 NULL 性能高。

  比如 banji 表，存在 id，name，create_time 列，存在 id 主键与 name 普通索引。

  # 覆盖索引，直接查询 name 对应的索引树就可以满足 select 后面的查询列select id,name from banji# 非覆盖索引，虽然也走了索引，但是进行了回表查询，以查询出 create_time 字段。select * from banji where name = '二年级'

• Using where：使用 where 关键字来查询，并且对应的列没有设置索引，对应的 key 为 NULL。

  这种情况一般要对查询的列添加相对应的索引来进行优化。

• Using index condition：非覆盖索引查询并进行了回表，并且辅助索引使用了条件查询语句（where 或其他）。

  比如 banji_student 关系表，存在 id，banji_id，student_id，create_time 列，存在 id 主键 和 banji_id 与 student_id 的组合（联合）索引。

  # 进行了回表查询，以查询出 create_time 列，并且组合索引进行了范围查找select * from banji_student where banji_id > 3

• Using temporary：MySQL 需要创建创建一个临时表来处理查询，出现这种情况一般要添加索引进行优化处理。

  # 如果 name 没有添加普通索引的话，则需要创建一个临时表来进行去重，Extra 值为 Using temporary# 如果添加了索引，则会走 name 对应的索引树，并且是覆盖索引，Extra 值为 Using indexexplain select distinct name from student

• Using filesort：使用外部排序而不是索引排序，当数据较小的时候采用的是内存排序，当数据量较大的时候会频繁的访问磁盘，并将排序后的数据写入磁盘。

  # 如果 name 没有添加普通索引的话，则需要创建一个临时表来进行去重，Extra 值为 Using filesort# 如果添加了索引，则会走 name 对应的索引树，并且是覆盖索引，Extra 值为 Using indexexplain select name from student order by name

• Select tables optimized away：使用聚合函数（例如 max、min等）来访问存在索引的字段时，只访问索引树中已排好序的叶子，节点性能很高。

  # 比如使用聚合函数 min 查询最小的学生 id(主键)explain select min(id) from student

  Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!