MySQL についての私の理解 パート 3: 実行計画

TodayMySQL Database 列では、関連する実行プランを紹介します。

MySQL シリーズの 3 番目のブログ、主な内容は MySQL での Explain 実行プランの分析です。実行プランの分析方法をすでに知っている場合は、であれば、SQL のチューニングは簡単です。

一流、準大手メーカーの採用要件を見ると、データベース設計をする人には必ずSQLチューニングの経験が求められており、これはほぼSpringと同等の「8部作エッセイ」の面接質問になっています。 . .

SQL チューニングを行うには、まず SQL の実行状況を知る必要があります。最も直感的に感じられるのは、もちろん SQL 文の実行時間ですが、これに加えて、SQL 文を分析することもできます。実行計画を実行してチューニングを実行します。

1. 簡単に説明します

Explain ステートメントでは、インデックスの使用、スキャンされた行の数など、MySQL がこの SQL ステートメントをどのように実行するかを表示できます。この情報は SQL チューニングに非常に役立ちます。 . 重要なので、まず実行計画を理解する必要があります。

mysql> explain select * from user where name='one';
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+|  1 | SIMPLE      | user  | NULL       | ref  | a             | a    | 13      | const |    1 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+1 row in set, 1 warning (0.00 sec)复制代码

上記は、単純なクエリ ステートメントの実行プランです。このテーブルには合計 12 個のフィールドがあり、それぞれが異なる意味を表します。以下で 1 つずつ説明します。

• id: SQLの実行順序を示し、値が大きいほど優先度が高くなります。値が同じ場合、実行順序はオプティマイザによって決定されます。
• select_type: 選択クエリ ステートメントのタイプを示します。
• table: SQL によってクエリされるテーブル名 (またはテーブルの別名)一時テーブルなどの存在しないテーブルです
• partitions: クエリ文に含まれるパーティション情報
• type : アソシエーション タイプ (アクセス タイプ)、決定 MySQL がテーブル内の行を検索する方法について説明します。最悪から最高のパフォーマンスは、ALL、index、range、index_merge、ref、 です。 eq_ref、const、system、NULL
• possible_keys: クエリ ステートメントが使用できることを示します。すべてのインデックス
• key: オプティマイザによって決定されたインデックス名を表示します。
• key_len: MySQL がインデックスの長さに使用するバイト数を表示します。
• ref: key 列レコード
• rows のインデックス内の値を検索するために使用される列または定数:行数のスキャン推定
• filtered: ストレージ エンジンによって返された合計行数に対する、最終的にクエリ ステートメントを満たす行数の割合
• 番外: その他の実行情報

上記は実行計画テーブルの各フィールドの名詞の説明ですが、次に皆さん(私自身)のために実際の例を用いて説明します。 select_type、type、key_len、rows、Extra これらの重要なフィールドをよく理解してください。

2. 詳細の説明

2.1 サンプル テーブル構造

最初に、この記事で使用するサンプル テーブル構造とデータ行を紹介します:

CREATE TABLE `user`  (  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',  `name` varchar(36) DEFAULT NULL COMMENT '姓名',  `age` int(11) NULL DEFAULT NULL COMMENT '年龄',  `email` varchar(36) DEFAULT NULL COMMENT '邮箱',
  PRIMARY KEY (`id`) USING BTREE,  INDEX `idx_age_name`(`age`, `name`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1;复制代码

関数を使用して、1,000,000 個のテスト データをテーブルに挿入します。

CREATE DEFINER=`root`@`localhost` PROCEDURE `idata`()begin 
  declare i int; 
  set i=1; 
  while(i<h3 id="Explain-の-select-type">2.2 Explain の select_type</h3><p>実行プランの <code>select_type</code> フィールドは、選択クエリ ステートメントのタイプを示します。一般的なタイプは次のとおりです: </p>

• SIMPLE: 次のようなサブクエリと関連付けを除く単純なクエリ ステートメント:

mysql> explain select * from user where id=1;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+|  1 | SIMPLE      | user  | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+1 row in set, 1 warning (0.00 sec)复制代码

2.2.1 PRIMARY

クエリ ステートメントに複雑なサブパートが含まれている場合の場合、最も外側の部分は次のように PRIMARY としてマークされます。

mysql> explain select * from user where id=(select id from user where id=1);
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+|  1 | PRIMARY     | user  | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL        |
|  2 | SUBQUERY    | user  | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+2 rows in set, 1 warning (0.00 sec)复制代码

この SQL ステートメントの実行プランでは、最初に実行される SQL は select * from yser where id = (...) は PRIMARY

としてマークされます2.2.2 SUBQUERY

選択に含まれるサブクエリ、またはコンテンツが ## としてマークされる場所#SUBQUERY。たとえば、前の SQL 例の実行プランの 2 番目のステートメント、つまり select id from user where id=1 の select_type がマークされます。 サブクエリとして。##サブクエリのタグが付けられました。

2.2.3 DERIVED

FROM キーワードの後に​​含まれるサブクエリ (つまり、サブクエリの結果は「テーブル」とみなされます)、「テーブル」とみなされるサブクエリ" は DERIVED とマークされ、結果は次のような一時テーブルに保存されます。

mysql> explain select * from (select id,name,count(*) from user where id=1) as user_1 where id=1;
+----+-------------+------------+------------+--------+---------------+---------+---------+-------+------+----------+-------+| id | select_type | table      | partitions | type   | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+------------+------------+--------+---------------+---------+---------+-------+------+----------+-------+|  1 | PRIMARY     | <derived2> | NULL       | system | NULL          | NULL    | NULL    | NULL  |    1 |   100.00 | NULL  |
|  2 | DERIVED     | user       | NULL       | const  | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+------------+------------+--------+---------------+---------+---------+-------+------+----------+-------+2 rows in set, 1 warning (0.00 sec)复制代码</derived2>

実行プランからわかるように、2 番目に実行された SQL は ## です。 #select id、id=1 が DERIVED であるユーザーからの name,count(*) のクエリ タイプ。

select_type クエリ タイプは全部で 12 種類あります。具体的な説明については、Explain_select_type

2.3 type in Explain## の公式ドキュメントを参照してください。

#type

フィールドは、実行プランで SQL を測定するための非常に重要な基礎であり、SQL ステートメントの関連付けタイプ (アクセス タイプ) を示し、MySQL がテーブル内の行を検索する方法を決定します。 <p><code>type 字段的值性能从最差到最优依次是 ALL, index, range, index_merge, ref, eq_ref, const, system。

为了能更好地理解各个类型的含义，我对上述每一种类型都举出了相应的示例。

并未全部列出，完整的解释可以看官方文档-EXPLAIN Join Types

2.3.1 ALL

ALL 表示全表扫描，意味着存储引擎查找记录时未走索引，所以它是性能最差的一种访问类型，如

mysql> explain select * from user where age+2=20;
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+|  1 | SIMPLE      | user  | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1002301 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+1 row in set, 1 warning (0.00 sec)复制代码

可以看到 rows 行的值为1002301，即扫描了全表的所有数据（扫描行数的值实际为估算），如果在生产环境有这样的 SQL，绝对是要优化的。

我们知道在 where 查询条件中，不应该对查询字段使用函数或表达式（应该写在等号不等号右侧），不了解此内容的可以看看我的上一篇博客 —— 我所理解的MySQL(二)索引。

这条查询语句在优化后应该是: select * from user where age=18，去掉等号左侧的表达式，优化后的执行计划如下:

mysql> explain select * from user where age=18;
+----+-------------+-------+------------+------+---------------+--------------+---------+-------+-------+----------+-------+| id | select_type | table | partitions | type | possible_keys | key          | key_len | ref   | rows  | filtered | Extra |
+----+-------------+-------+------------+------+---------------+--------------+---------+-------+-------+----------+-------+|  1 | SIMPLE      | user  | NULL       | ref  | idx_age_name  | idx_age_name | 5       | const | 39360 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+--------------+---------+-------+-------+----------+-------+1 row in set, 1 warning (0.00 sec)复制代码

2.3.2 index

index 表示全索引树扫描，由于扫描的是索引树，所以比 ALL 形式的全表扫描性能要好。

同时，由于索引树本身就是有序的，可以避免排序。

mysql> explain select id,age from user where name='name1';
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+---------+----------+--------------------------+| id | select_type | table | partitions | type  | possible_keys | key          | key_len | ref  | rows    | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+---------+----------+--------------------------+|  1 | SIMPLE      | user  | NULL       | index | NULL          | idx_age_name | 116     | NULL | 1002301 |    10.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+---------+----------+--------------------------+1 row in set, 1 warning (0.00 sec)复制代码

示例查询语句如上所述，当查询条件存在于联合索引 idx_age_name 中，但又无法直接使用该索引（由于最左前缀原则），同时查询列 id,age 也存在于联合索引中，无须通过回表来获取时，执行计划中的访问类型 type 列就会是 index。

2.3.3 range

range 表示范围扫描，准确的说是基于索引树的范围扫描，扫描的是部分索引树，所以性能比 index 稍好。

需要注意的是，若使用 in 或者 or 时，也可以使用范围扫描。

mysql> explain select * from user where age>18 and age explain select * from user where age=18 or age=20;
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+-------+----------+-----------------------+| id | select_type | table | partitions | type  | possible_keys | key          | key_len | ref  | rows  | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+-------+----------+-----------------------+|  1 | SIMPLE      | user  | NULL       | range | idx_age_name  | idx_age_name | 5       | NULL | 78720 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+-------+----------+-----------------------+1 row in set, 1 warning (0.00 sec)复制代码

2.3.4 index_merge

index_merge 即索引合并，它表示在查询时 MySQL 会使用多个索引。

MySQL 在 where 语句中存在多个查询条件，并且其中存在多个字段可以分别使用到多个不同的索引，在这种情况下 MySQL 可以对多个索引树同时进行扫描，最后将它们的结果进行合并，如：

mysql> explain select * from user where id=1 or age=18;
+----+-------------+-------+------------+-------------+----------------------+----------------------+---------+------+-------+----------+-----------------------------------------------------+| id | select_type | table | partitions | type        | possible_keys        | key                  | key_len | ref  | rows  | filtered | Extra                                               |
+----+-------------+-------+------------+-------------+----------------------+----------------------+---------+------+-------+----------+-----------------------------------------------------+|  1 | SIMPLE      | user  | NULL       | index_merge | PRIMARY,idx_age_name | idx_age_name,PRIMARY | 5,4     | NULL | 39361 |   100.00 | Using sort_union(idx_age_name,PRIMARY); Using where |
+----+-------------+-------+------------+-------------+----------------------+----------------------+---------+------+-------+----------+-----------------------------------------------------+1 row in set, 1 warning (0.00 sec)复制代码

上面这条查询语句中的 id=1 和 age=18 分别使用到了 PRIMARY 主键索引和 idx_age_name 联合索引，最后再将满足这两个条件的记录进行合并。

2.3.5 ref

ref 表示索引访问（索引查找），这种访问类型会出现在查询条件中以非聚簇索引列的常量值进行查询的情况。

比如在介绍全表扫描中优化后 SQL 的访问类型就是 ref。

2.3.6 eq_ref

eq_ref 这种访问类型会出现在连接查询时，通过聚簇索引进行连接的情况，此类型最多只返回一条符合条件的记录。若表的聚簇索引为联合索引，所有的索引列必须是等值查询，如：

mysql> explain select * from user user1 inner join user user2 where user1.id=user2.id limit 10;
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------------+---------+----------+-------+| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref                 | rows    | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------------+---------+----------+-------+|  1 | SIMPLE      | user1 | NULL       | ALL    | PRIMARY       | NULL    | NULL    | NULL                | 1002301 |   100.00 | NULL  |
|  1 | SIMPLE      | user2 | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | all_in_one.user1.id |       1 |   100.00 | NULL  |
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------------+---------+----------+-------+2 rows in set, 1 warning (0.00 sec)复制代码

2.3.7 const

const 这种访问类型会出现在通过聚簇索引进行常量等值查询的情况，如：

mysql> explain select * from user where id=1;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+|  1 | SIMPLE      | user  | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+1 row in set, 1 warning (0.00 sec)复制代码

2.4 key_len in Explain

在上一篇博客 —— 我所理解的MySQL(二)索引 中 5.2 部分字段匹配 中已经提到过关于索引长度的计算方式，这里再来总结一下。

2.4.1 字符类型

字符类型的字段若作为索引列，它的索引长度 = 字段定义长度 字符长度 + 是否默认NULL + 是否是变长字段*，其中：

• 字段定义长度 就是定义表结构时跟在字段类型后括号中的数字
• 字符长度 是常数，utf8=3, gbk=2, latin1=1
• 是否默认NULL 也是常数，若字段默认值为 NULL，该值为1，因为 NULL 需要额外的一个字节来表示；否则该值为0
• 是否是变长字段 也是常数，若该字段为变长字段，该值为2；否则该值为0

所谓的变长字段就是 varchar，它所占用的就是字段实际内容的长度而非定义字段时的长度。而定长字段，也就是 char 类型，它所占用的空间就是自定字段时的长度，若超过会被截取。

举个例子，为上述实例表中添加一个字符类型字段的索引。

alter table user add index idx_name(`name`);复制代码

然后通过 name 字段去做查询，查看执行计划。

mysql> explain select * from user where name='name1';
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+| id | select_type | table | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+|  1 | SIMPLE      | user  | NULL       | ref  | idx_name      | idx_name | 111     | const |    2 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+1 row in set, 1 warning (0.01 sec)复制代码

可以看到，执行计划中 key_len 一列的值为 111。

根据上述索引长度的计算公式，name 列字段定义长度为36，字符集类型为默认的 utf8，该字段默认允许 NULL，同时该字段是可变长字段 varchar。

所以 idx_name 索引的索引长度=36*3+1+2=111，恰如执行计划中显示的值。

2.4.2 其他定长类型

对于定长类型的字段，其索引长度与它的数据类型长度是一致的。

数据类型	长度
int	4
bigint	8
date	3
datetime	8
timestamp	4
float	4
double	8

需要注意的是，若该字段允许默认值为 NULL，与字符类型一样，其索引长度也需要加上1。

mysql> explain select * from user where age=1;
+----+-------------+-------+------------+------+---------------+--------------+---------+-------+-------+----------+-------+| id | select_type | table | partitions | type | possible_keys | key          | key_len | ref   | rows  | filtered | Extra |
+----+-------------+-------+------------+------+---------------+--------------+---------+-------+-------+----------+-------+|  1 | SIMPLE      | user  | NULL       | ref  | idx_age_name  | idx_age_name | 5       | const | 39366 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+--------------+---------+-------+-------+----------+-------+1 row in set, 1 warning (0.00 sec)复制代码

如上面这个示例（本示例中索引只用到了 age 字段），age 字段为 int 类型，其索引长度本应为 4，但由于 age 字段默认允许为 NULL，所以它的索引长度就变成了5。

2.5 rows in Explain

扫描行数在执行计划中其实是一个估值，MySQL 会选择 N 个不同的索引数据页，计算平均值得到单页索引基数，然后再乘以索引页面数，就得到了扫描行数的估值。

扫描行数就是优化器考量索引执行效率的因素之一，一般而言扫描行数越少，执行效率越高。

2.6 Extra in Explain

执行计划中 Extra 字段的常见类型有：

• Using index: 使用了覆盖索引，以避免回表
• Using index condition: 使用了索引下推，具体可以看我的上一篇博客 —— 我所理解的MySQL(二)索引
• Using where: 表示MySQL 会通过 where 条件过滤记录
  • 全表扫描：where 中有该表字段作为搜索条件
  • 扫描索引树：where 中包含索引字段之外的其他字段作为搜索条件
• Using temporary: MySQL 在对查询结果排序时会使用临时表
• Using filesort: 对结果进行外部索引排序（文件排序），排序不走索引
  • 数据较少时在内存中排序，数据较多时在磁盘中排序
  • 尽量避免该信息出现在执行计划中

相关免费学习推荐：mysql数据库(视频)

以上がMySQL についての私の理解 パート 3: 実行計画の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。