MySQL 5.7最適化の詳細説明: 実行計画の説明

mysql ビデオ チュートリアルコラムの紹介実行計画の説明

##推奨 (無料): mysql ビデオ チュートリアル

目次

1. はじめに
• 2. 結果の説明列 詳細説明
• • 2.1 id
  • 2.2 select_type
  • 2.3 table
  • 2.4 パーティション
  • 2.5 type (非常に重要)
  • 2.6 possible_keys
  • 2.7 key
  • 2.8 key_len
  • 2.9 ref
  • 3.10 rows
  • 2.11 filtered
  • 2.12 Extra

現在のシステム環境:
• MySQL 5.7、その他のバージョンは省略 相違点がある場合は、後ほど別途説明させていただきます。
一般的なシナリオのみを紹介します。他のまれなシナリオについては当面検討しません。必要に応じて公式ドキュメントを参照してください。
• 初心者向けではありません。
• MySQL の基礎となるデータ構造 B ツリーをある程度理解する必要があります。

MySQL 公式の Explain ドキュメント

使用方法

キーワードは、オプティマイザによる SQL ステートメントの実行をシミュレートし、クエリ ステートメントのパフォーマンスのボトルネックを分析できます。

id
• 列 シリアル番号は select です。通常、select は id と同数存在します (結合テーブル クエリには重複した id# が含まれます) ## )、select が出現する順に id の順序が増加します。 id
値が大きいほど、実行の優先順位が高くなります。
• id が同じ場合 (通常、結合テーブル クエリに表示されます)、実行は上から下へ。 id は NULL で、最後に実行されます。
2.2 select_type

select_type

simple
• : 単純なクエリ。クエリにはサブクエリとユニオンは含まれません。

primary
• : 複雑なクエリの最も外側の選択。

subquery
• : select に含まれるサブクエリ (from 句には含まれていません)

derived
• : form 句に含まれるサブクエリの場合、MySQL は結果を一時テーブル (派生テーブルとも呼ばれます) に配置します。

union
• : ユニオン内の 2 番目以降の選択。
  [注意]
MySQL 5.7

select_typeの値を視覚的に確認したい場合は、この機能を一時的にオフにする必要があります (デフォルトではオンになっています)。この操作は、次の導入部のすべての派生テーブルに必要です。

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

行クエリに対応するテーブル。

にサブクエリがある場合、テーブルの列は
• #の形式となります。 ## は、この行が id ​​= N 行のクエリを実行することを示します。 union がある場合、テーブルデータは
の形式になり、
• M と N は参加を表します。 ##select 行 id (#union)。 2.4 パーティション
続きます。 。 。

2.5 type (非常に重要)

type この行の関連付けタイプ (アクセス タイプまたはクエリ タイプ) を示します。クエリ。この値を使用して、この行のクエリ データ レコードのおおよその範囲を把握できます。

system > const > eq_ref > ref > range > Index > ALL
• ; 一般に、効率を確保する必要があります。その場合、ステートメントを少なくとも range レベルに達するように最適化し、可能であれば
ref
• まで最適化する必要があります。range は通常、範囲検索に使用されます。つまり、範囲検索に加えて、他のクエリ ステートメントも ref レベルまで最適化する必要があります。 共通値の説明:

NULL:

が可能であることを示します。最適化 クエリ ステートメントは段階的に分解され、実行段階ではテーブルとインデックスにアクセスする必要がありません。

• system / const: MySQL は特定のクエリ部分を最適化し、それを定数に変換できます (

  show warnings
で表示できます) 最適化結果)、主に主キー (
• Primary Key

  ) または一意のキー インデックス (Unique Key) に対応するレコードをクエリします。重複がないため、クエリできるレコードは 1 つだけです。せいぜい. なので、速度は比較的速いです。 system は const の特殊なケースで、一時テーブルにレコードが 1 つだけある場合の system です。 <div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;"># 表里有一个主键id为1的记录 - constexplain select * from student where id = 1# 派生表里面只有一条记录 - systemexplain select * from (select * from student where id = 1) tmp# 注： 如果查询的列中有 text 类型，那么在这里 type 会变为 ALL ，# 因为无法使用内存临时表，只能在磁盘上创建临时表，所以性能上会有所损耗，效果等同于全表查询 ALL。</pre><div class="contentsignin">ログイン後にコピー</div></div></li><li><p><code>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

  ログイン後にコピー

  以上がMySQL 5.7最適化の詳細説明: 実行計画の説明の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。