MySQL 고급 학습: explain 명령에 대한 심층적인 이해

이 글은 MySQL에 대한 고급 연구로서 각 분야의 의미를 심도 깊게 이해하는 데 도움이 되기를 바랍니다.

Explain의 용도: SQL 문의 실행을 최적화하는 방법을 알기 위해서는 SQL 문의 구체적인 실행 과정을 살펴 SQL 문의 실행 효율성을 높여야 합니다.

explain + SQL 문을 사용하여 SQL 쿼리 문을 실행하는 최적화 프로그램을 시뮬레이션하여 mysql이 SQL 문을 처리하는 방법을 알 수 있습니다. 실행 계획을 보고 Executor가 우리가 생각한 대로 SQL을 처리하고 있는지 파악합니다.

explain실행 계획에 포함된 정보는 다음과 같습니다. ​ id: 쿼리 시퀀스 번호

select_type: 쿼리 유형

​ table: 테이블 이름 또는 별칭

partitions: 일치하는 파티션

​ type: 액세스 유형

possible_keys : 가능한 인덱스 e key: 실제 인덱스

key_len: 인덱스 길이

Ref: 컬럼

ROWS: 추정 행: 추정 Extra: 추가 정보

각 컬럼의 의미와 해당 SQL에 대해 이야기해보겠습니다.

테스트에서는 MySQL 버전 5.7을 사용했습니다. 사용된 세 가지 테이블 구조는 다음과 같습니다

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 열

선택 쿼리의 시퀀스 번호(숫자 집합)는 선택 순서를 나타냅니다. 절 또는 테이블 작업이 쿼리에서 실행됩니다.

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 값이 클수록 우선 순위가 높아지고 더 일찍 실행됩니다. 컬럼

은 주로 쿼리의 종류를 일반 쿼리인지, 조인트 쿼리인지, 서브 쿼리인지 구분하는 데 사용됩니다. . Primary: 쿼리에 복잡한 하위 쿼리가 포함된 경우 가장 바깥쪽 쿼리는 Primary

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');

로 표시됩니다. 3. Union : Union의 두 번째 및 후속 선택, Union All 및 하위 쿼리는 Union

mysql> explain select * from emp;

로 표시됩니다.

4. 종속 Union: UNION 또는 UNION ALL이 포함된 대규모 쿼리에서 각 소규모 쿼리가 다음에 종속되는 경우 외부 쿼리의 경우 가장 왼쪽의 소규모 쿼리를 제외하고 나머지 소규모 쿼리의 select_type 값은 DEPENDENT UNION입니다.

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

5. Union 결과: Union 테이블에서 결과를 선택합니다.

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

6. 하위 쿼리: select 또는 where 목록에 하위 쿼리 포함(from 절 아님)

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)

7. 종속 하위 쿼리: 먼저 하위 쿼리에서 선택(from 절 아님)), 외부 쿼리.

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

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 중국어 웹사이트의 기타 관련 기사를 참조하세요!