MySQL の高度な学習: Explain コマンドの深い理解

この記事は MySQL の高度な学習です。各フィールドの意味を深く理解することができます。お役に立てば幸いです。

Explain の用途: SQL ステートメントの実行を最適化する方法を知るには、SQL ステートメントの特定の実行プロセスを確認して、処理を高速化する必要があります。 SQL ステートメントの実行効率。

Explain SQL ステートメントを使用して、オプティマイザによる SQL クエリ ステートメントの実行をシミュレートし、mysql が SQL ステートメントをどのように処理するかを知ることができます。実行計画を見て、エグゼキュータが想定どおりに SQL を処理しているかどうかを理解します。

explain実行計画に含まれる情報は次のとおりです:

id: クエリ シーケンス番号

select_type: クエリ タイプ

table: テーブル名前またはエイリアス

パーティション: 一致するパーティション

タイプ: アクセス タイプ

possible_keys: 可能なインデックス

キー: 実際に使用されるインデックス

key_len: インデックスの長さ

ref: インデックスと比較した列

rows: 推定行数

filtered: テーブル条件によってフィルターされた行の割合

補足: 追加情報

各列の意味と対応する SQL について説明します。

このテストでは、mysql バージョン 5.7 を使用します。使用される 3 つのテーブル構造は次のとおりです

CREATE TABLE `demo`.`emp`  (  `emp_id` bigint(20) NOT NULL,  `name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '姓名',  `empno` int(20) NOT NULL COMMENT '工号',  `deptno` int(20) NOT NULL COMMENT '部门编号',  `sal` int(11) NOT NULL DEFAULT 0 COMMENT '销售量',  PRIMARY KEY (`emp_id`) USING BTREE,  INDEX `u1`(`deptno`) USING BTREE,  UNIQUE INDEX `u2`(`empno`) USING BTREE) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;

CREATE TABLE `demo`.`dept`  (  `id` bigint(20) NOT NULL,  `deptno` int(20) NOT NULL COMMENT '部门编码',  `dname` varchar(20) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '部门名称',  PRIMARY KEY (`id`) USING BTREE,  UNIQUE INDEX `dept_u1`(`deptno`) USING BTREE) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;

CREATE TABLE `demo`.`salgrade`  (  `id` bigint(20) NOT NULL,  `losal` int(20) NULL DEFAULT NULL,  `hisal` int(20) NULL DEFAULT NULL,  `emp_id` bigint(20) NULL DEFAULT NULL,  PRIMARY KEY (`id`) USING BTREE) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;

id ​​column

select queryシーケンス番号 (数値のグループ)。クエリ内で選択句または操作テーブルが実行される順序を示します。

id ​​列は 3 つの状況に分かれています。

1. ID が同じ場合、実行順序は上から下です

mysql> explain select * from emp e join dept d on e.deptno = d.deptno join salgrade sg on e.sal between sg.losal and sg.hisal;

## 2 . idが異なる場合、サブクエリの場合はidのシーケンス番号がインクリメントされ、idの値が大きいほど優先度が高く、より早く実行されます

mysql> explain select * from emp e where e.deptno = (select d.deptno from dept d where d.dname = 'SALES');

## 3. 同じ ID と異なる ID が同時に存在する場合、同じ ID を 1 つのグループとみなし、上から下へ順番に実行されます。 ID 値が大きいほど優先順位が高く、より早く実行されます

mysql> explain select * from emp e join dept d on e.deptno = d.deptno join salgrade sg on e.sal between sg.losal and sg.hisal wheree.deptno = (select d.deptno from dept d where d.dname = 'SALES');

select_type column は主に、クエリの種類 (通常のクエリ、結合クエリ、サブクエリ) を区別するために使用されます

1.

sample

: サブクエリを除く単純なクエリおよび Union

mysql> explain select * from emp;

## 2. プライマリ: クエリに複雑なサブクエリが含まれる場合、最も外側のクエリはプライマリ

mysql> explain select * from emp e where e.deptno = (select d.deptno from dept d where d.dname = 'SALES');

としてマークされます。

3. Union: Union では、union all とサブクエリの 2 番目以降の select は、union としてマークされます

mysql> explain select * from emp where deptno = 10 union select * from emp where sal >2000;

## 4. 依存ユニオン: を含む大規模なクエリ内UNION または UNION ALL。各小さなクエリが外側のクエリに依存する場合、左側の小さなクエリを除き、残りの小さなクエリの select_type 値は DEPENDENT UNION になります。

mysql> explain select * from emp e where e.empno  in ( select empno from emp where deptno = 10 union select empno from emp where sal >2000)

5. 結合結果: 結合テーブルから結果を選択します。

mysql> explain select * from emp where deptno = 10 union select * from emp where sal >2000;

6. サブクエリ: select または where リストにサブクエリが含まれます (from 句には含まれません)

mysql> explain select * from emp where sal > (select avg(sal) from emp) ;

7 . 依存サブクエリ: サブクエリ内の最初の選択 (from 句内ではない) であり、外部クエリに依存します。

mysql> explain select e1.* from emp e1 WHERE e1.deptno = (SELECT deptno FROM emp e2 WHERE e1.empno = e2.empno);

8. 派生: FROM リストに含まれるサブクエリは DERIVED としてマークされ、派生クラス

とも呼ばれます。

mysql> explain select * from ( select emp_id,count(*) from emp group by emp_id ) e;

9. UNCACHEABLE SUBQUERY：一个子查询的结果不能被缓存，必须重新评估外链接的第一行对于外层的主表，子查询不可被物化，每次都需要计算（耗时操作）

mysql> explain select * from emp where empno = (select empno from emp where deptno=@@sort_buffer_size);

10. uncacheable union: 表示union的查询结果不能被缓存：没找到具体的sql语句验证.

table列

对应行正在访问哪一个表，表名或者别名，可能是临时表或者union合并结果集.

1、如果是具体的表名，则表明从实际的物理表中获取数据，当然也可以是表的别名.

2、表名是derivedN的形式，表示使用了id为N的查询产生的衍生表.

3、当有union result的时候，表名是union n1,n2等的形式，n1,n2表示参与union的id.

type列

type显示的是访问类型，访问类型表示我是以何种方式去访问我们的数据，最容易想的是全表扫描，直接暴力的遍历一张表去寻找需要的数据，效率非常低下。

访问的类型有很多，效率从最好到最坏依次是：

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

一般情况下，要保证查询至少达到range级别，最好能达到ref

1. all: 全表扫描，需要扫描整张表，从头到尾找到需要的数据行。一般情况下出现这样的sql语句而且数据量比较大的话那么就需要进行优化。

mysql> explain select * from emp;

2. index：全索引扫描这个比all的效率要好，主要有两种情况，一种是当前的查询时覆盖索引，即我们需要的数据在索引中就可以索取，或者是使用了索引进行排序，这样就避免数据的重排序

mysql> explain  select empno from emp;

3. range：表示利用索引查询的时候限制了范围，在指定范围内进行查询，这样避免了index的全索引扫描，适用的操作符：=, <>, >, >=, <, <=, IS NULL, BETWEEN, LIKE, or IN()

mysql> explain select * from emp where empno between 100 and 200;

4. index_subquery：利用索引来关联子查询，不再扫描全表

mysql> explain select * from emp where deptno not in (select deptno from emp)

但是大多数情况下使用SELECT子查询时，MySQL查询优化器会自动将子查询优化为联表查询，因此 type 不会显示为 index_subquery,而是ref

5. unique_subquery: 该连接类型类似于index_subquery,使用的是唯一索引

mysql> explain SELECT * from emp where emp_id not in (select emp.emp_id from emp );

大多数情况下使用SELECT子查询时，MySQL查询优化器会自动将子查询优化为联表查询，因此 type 不会显示为 index_subquery,而是eq_ref

6. index_merge：在查询过程中需要多个索引组合使用.

mysql> 没有模拟出来

7. ref_or_null：对于某个字段即需要关联条件，也需要null值的情况下，查询优化器会选择这种访问方式.

mysql> 没模拟出来

8. ref：使用了非唯一性索引进行数据的查找

mysql> explain select * from emp where deptno=10;

9. eq_ref ：当进行等值联表查询使用主键索引或者唯一性非空索引进行数据查找(实际上唯一索引等值查询type不是eq_ref而是const)

mysql> explain select * from salgrade s LEFT JOIN emp e on s.emp_id = e.emp_id;

10. const：最多只能匹配到一条数据，通常使用主键或唯一索引进行等值条件查询

mysql> explain select * from emp where empno = 10;

11. system：表只有一行记录（等于系统表），这是const类型的特例，平时不会出现,不需要进行磁盘io

mysql> explain SELECT * FROM `mysql`.`proxies_priv`;

possible_keys列

显示可能应用在这张表中的索引，一个或多个，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用。

key列

实际使用的索引，如果为null，则没有使用索引，查询中若使用了覆盖索引，则该索引和查询的select字段重叠。

key_len列

表示索引中使用的字节数，可以通过key_len计算查询中使用的索引长度，在不损失精度的情况下长度越短越好。

索引越大占用存储空间越大，这样io的次数和量就会增加，影响执行效率

ref列

显示之前的表在key列记录的索引中查找值所用的列或者常量

rows列

根据表的统计信息及索引使用情况，大致估算出找出所需记录需要读取的行数，此参数很重要，直接反应的sql找了多少数据，在完成目的的情况下越少越好。

filtered列

针对表中符合某个条件(where子句或者联接条件)的记录数的百分比所做的一个悲观估算。

extra列

包含额外的信息。

1. using filesort: 说明mysql无法利用索引进行排序，只能利用排序算法进行排序，会消耗额外的位置

mysql> explain select * from emp order by sal;

2. using temporary: 建立临时表来保存中间结果，查询完成之后把临时表删除

mysql> explain select name,count(*) from emp where deptno = 10 group by name;

3. using index: 这个表示当前的查询是覆盖索引的，直接从索引中读取数据，而不用访问数据表。如果同时出现using where 表名索引被用来执行索引键值的查找，如果没有，表面索引被用来读取数据，而不是真的查找

mysql> explain select deptno,count(*) from emp group by deptno limit 10;

4. using where: 使用where进行条件过滤

mysql> explain select * from emp where name = 1;

5. using join buffer: 使用连接缓存

mysql> explain select * from emp e left join dept d on e.deptno = d.deptno;

6. impossible where：where语句的结果总是false

mysql> explain select * from emp where 1=0;

更多编程相关知识，请访问：编程视频！！

以上がMySQL の高度な学習: Explain コマンドの深い理解の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。