MySQL EXPLAIN 명령에 대한 자세한 연구
- 黄舟원래의
- 2017-02-17 13:26:191473검색
MySQL EXPLAIN 명령어 상세 설명
MySQL의 EXPLAIN 명령어는 SQL 문의 쿼리 실행 계획(QEP)에 사용된다. 이 명령의 출력을 통해 MySQL 최적화 프로그램이
SQL 문을 실행하는 방법을 이해할 수 있습니다. 이 명령은 조정 제안을 제공하지 않지만 조정 결정을 내리는 데 도움이 되는 중요한 정보를 제공할 수 있습니다.
1 구문
MySQL의 EXPLAIN 구문은 SELECT 문이나 특정 테이블에서 실행할 수 있습니다. 테이블에 대해 작업하는 경우 이 명령은 DESC 테이블 명령과 동일합니다. UPDATE
및 DELETE 명령도 테이블의 기본 키에서 직접 실행되지 않는 경우 최적의 인덱스 사용을 보장하기 위해 SELECT 문으로 변경해야 합니다. ). 다음 예를 참조하세요.
UPDATE table1 SET col1 = X, col2 = Y WHERE id1 = 9 AND dt >= '2010-01-01';
SELECT col1, col2 FROM table1 WHERE id1 = 9 AND dt >= '2010-01-01';
MySQL EXPLAIN 명령은 SQL 문의 각 테이블에 대해 다음 정보를 생성할 수 있습니다.
mysql> EXPLAIN SELECT * FROM inventory WHERE item_id = 16102176\G; ********************* 1. row *********************** id: 1 select_type: SIMPLE table: inventory type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 787338 Extra: Using where
********************* 1. row *********************** id: 1 select_type: SIMPLE table: inventory type: ref possible_keys: item_id key: item_id key_len: 4 ref: const rows: 1 Extra:
이 QEP에서는 인덱스가 사용되는 것을 볼 수 있으며, 데이터가 얻어지게 됩니다.
z id
z select_type z table
z partitions(이 열은 EXPLAIN PARTITIONS 구문에만 나타납니다.)
z available_keys
z key
z key_len
z ref
z 행
z Filtered(이 열만 나타남) EXPLAINED EXTENDED 구문에서만)
l Extra
이 열에는 각 테이블에 대한 SELECT 문의 QEP가 표시됩니다. 테이블은 SQL 실행 중(예: 하위 쿼리 또는 병합 작업에서) 생성된 물리적 스키마 테이블이나 내부 임시 테이블과 연결될 수 있습니다.
2.1 키
키 열은 최적화 프로그램이 선택한 인덱스를 나타냅니다. 일반적으로 SQL 쿼리에서는 테이블당 하나의 인덱스만 사용됩니다. 주어진 테이블에 두 개 이상의 인덱스가 사용되는 경우와 같이 인덱스 병합에는 몇 가지 예외가 있습니다. 다음은 QEP의 키 열의 예입니다.
key: item_id
key: NULL
key: first, last
SHOW CREATE TABLE f5d188ed2c074f8b944552db028f98a1 테이블을 확인하고 열 세부정보를 색인화하는 방법을 확인하세요. 키 열과 관련된 열에는 available_keys, 행 및 key_len도 포함됩니다.
행 열은 누적 결과 집합에 있는 모든 행에 대해 MySQL 최적화 프로그램이 분석하려고 시도한 행 수의 추정치를 제공합니다. QEP를 사용하면 이 어려운 통계를 쉽게 설명할 수 있습니다. 쿼리의 총 읽기 작업 횟수는 행을 병합하기 전 각 행의 행 값이 지속적으로 누적된 결과를 기반으로 합니다. 이것은 중첩 행 알고리즘입니다.
두 테이블을 연결하는 QEP를 예로 들어보겠습니다. id=1 조건을 통해 찾은 첫 번째 행의 행 값은 1이며, 이는 첫 번째 테이블에 대한 읽기 작업과 동일합니다. 두 번째 행은 id=2인
에 의해 발견되며 행의 값은 5입니다. 이는 현재 누적 1과 일치하는 5개의 읽기와 같습니다. 두 테이블을 모두 참조하면 총 읽기 작업 수는 6입니다. 또 다른 QEP
에서는 첫 번째 행의 값이 5이고 두 번째 행의 값이 1입니다. 이는 첫 번째 테이블에 대한 5개의 읽기와 동일하며, 5개의 누적 각각에 대해 하나씩입니다. 따라서 두 테이블
에 대한 총 읽기 작업 횟수는 10(5+5)회입니다.
가장 좋은 추정치는 1입니다. 일반적으로 이는 찾고 있는 행을 기본 키나 고유 키로 테이블에서 찾을 수 있는 경우에 발생합니다.
아래 QEP에서 외부 중첩 루프는 id=1로 찾을 수 있으며 추정된 물리적 행 번호는 1입니다. 두 번째 루프에서는 10개의 행을 처리했습니다.
********************* 1. row *********************** id: 1 select_type: SIMPLE table: p type: const possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: const rows: 1 Extra: ********************* 2. row *********************** id: 1 select_type: SIMPLE table: c type: ref possible_keys: parent_id key: parent_id key_len: 4 ref: const rows: 10 Extra:
SHOW STATUS 명령을 사용하여 실제 행 작업을 볼 수 있습니다. 이 명령은 물리적 행 작업을 확인하는 가장 좋은 방법을 제공합니다. 다음 예를 참조하세요.
mysql> SHOW SESSION STATUS LIKE 'Handler_read%'; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | Handler_read_first | 0 | | Handler_read_key | 0 | | Handler_read_last | 0 | | Handler_read_next | 0 | | Handler_read_prev | 0 | | Handler_read_rnd | 0 | | Handler_read_rnd_next | 11 | +-----------------------+-------+ 7 rows in set (0.00 sec)
다음 QEP에서 id=1로 발견된 외부 중첩 루프는 160줄로 추정됩니다. 두 번째 루프는 1행으로 추정됩니다.
********************* 1. row *********************** id: 1 select_type: SIMPLE table: p type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 160 Extra: ********************* 2. row *********************** id: 1 select type: SIMPLE table: c type: ref possible_keys: PRIMARY,parent_id key: parent_id key_len: 4 ref: test.p.parent_id rows: 1 Extra: Using where
실제 행 작업은 SHOW STATUS 명령을 통해 확인할 수 있으며, 이는 물리적 읽기 작업 횟수가 크게 증가했음을 보여줍니다. 아래 예를 참조하세요.
mysql> SHOW SESSION STATUS LIKE 'Handler_read%'; +--------------------------------------+---------+ | Variable_name | Value | +--------------------------------------+---------+ | Handler_read_first | 1 | | Handler_read_key | 164 | | Handler_read_last | 0 | | Handler_read_next | 107 | | Handler_read_prev | 0 | | Handler_read_rnd | 0 | | Handler_read_rnd_next | 161 | +--------------------------------------+---------+ 相关的QEP 列还包括key列。
2.3 possible_keys
possible_keys 列指出优化器为查询选定的索引。
一个会列出大量可能的索引(例如多于3 个)的QEP 意味着备选索引数量太多了，同时也可能提示存在一个无效的单列索引。
可以用第2 章详细介绍过的SHOW INDEXES 命令来检查索引是否有效且是否具有合适的基数。
为查询确定QEP 的速度也会影响到查询的性能。如果发现有大量的可能的索引，则意味着这些索引没有被使用到。
相关的QEP 列还包括key 列。
2.4 key_len
key_len 列定义了用于SQL 语句的连接条件的键的长度。此列值对于确认索引的有效性以及多列索引中用到的列的数目很重要。
此列的一些示例值如下所示：
此列的一些示例值如下所示：
key_len: 4 // INT NOT NULL
key_len: 5 // INT NULL
key_len: 30 // CHAR(30) NOT NULL
key_len: 32 // VARCHAR(30) NOT NULL
key_len: 92 // VARCHAR(30) NULL CHARSET=utf8
从这些示例中可以看出，是否可以为空、可变长度的列以及key_len 列的值只和用在连接和WHERE 条件中的索引的列
有关。索引中的其他列会在ORDER BY 或者GROUP BY 语句中被用到。下面这个来自于著名的开源博客软件WordPress 的表展示了
如何以最佳方式使用带有定义好的表索引的SQL 语句：
CREATE TABLE `wp_posts` ( `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `post_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', `post_status` varchar(20) NOT NULL DEFAULT 'publish' , `post_type` varchar(20) NOT NULL DEFAULT 'post', PRIMARY KEY (`ID`), KEY `type_status_date`(`post_type`,`post_status`,`post_date`,`ID`) ) DEFAULT CHARSET=utf8 CREATE TABLE `wp_posts` ( `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `post_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', `post_status` varchar(20) NOT NULL DEFAULT 'publish' , `post_type` varchar(20) NOT NULL DEFAULT 'post', PRIMARY KEY (`ID`), KEY `type_status_date`(`post_type`,`post_status`,`post_date`,`ID`) ) DEFAULT CHARSET=utf8
这个表的索引包括post_type、post_status、post_date 以及ID列。下面是一个演示索引列用法的SQL 查询：
EXPLAIN SELECT ID, post_title FROM wp_posts WHERE post_type='post' AND post_date > '2010-06-01';
这个查询的QEP 返回的key_len 是62。这说明只有post_type列上的索引用到了(因为(20×3)+2=62)。尽管查询在WHERE 语句
中使用了post_type 和post_date 列，但只有post_type 部分被用到了。其他索引没有被使用的原因是MySQL 只能使用定义索引的
最左边部分。为了更好地利用这个索引，可以修改这个查询来调整索引的列。请看下面的示例：
mysql> EXPLAIN SELECT ID, post_title -> FROM wp_posts -> WHERE post_type='post' -> AND post_status='publish' -> AND post_date > '2010-06-01';
在SELECT查询的添加一个post_status 列的限制条件后，QEP显示key_len 的值为132，这意味着post_type、post_status、post_date
三列(62+62+8，(20×3)+2，(20×3)+2，8)都被用到了。此外，这个索引的主码列ID 的定义是使用MyISAM 存储索
引的遗留痕迹。当使用InnoDB 存储引擎时，在非主码索引中包含主码列是多余的，这可以从key_len 的用法看出来。
相关的QEP 列还包括带有Using index 值的Extra 列。
2.5 table
table 列是EXPLAIN 命令输出结果中的一个单独行的唯一标识符。这个值可能是表名、表的别名或者一个为查询产生临时表
的标识符，如派生表、子查询或集合。下面是QEP 中table 列的一些示例：
table: item
table: 6db98b0ebf978836f3b742968b1383c4
table: 3d79052a4b39814111818a40e6c7009c
表中N 和M 的值参考了另一个符合id 列值的table 行。相关的QEP 列还有select_type
2.6 select_type
select_type 列提供了各种表示table 列引用的使用方式的类型。最常见的值包括SIMPLE、PRIMARY、DERIVED 和UNION。其他可能
的值还有UNION RESULT、DEPENDENT SUBQUERY、DEPENDENT UNION、UNCACHEABLE UNION 以及UNCACHEABLE QUERY。
1. SIMPLE
对于不包含子查询和其他复杂语法的简单查询，这是一个常 见的类型。
2. PRIMARY
这是为更复杂的查询而创建的首要表(也就是最外层的表)。这个类型通常可以在DERIVED 和UNION 类型混合使用时见到。
3. DERIVED
当一个表不是一个物理表时，那么就被叫做DERIVED。下面的SQL 语句给出了一个QEP 中DERIVED select-type 类型的
示例：
mysql> EXPLAIN SELECT MAX(id)
-> FROM (SELECT id FROM users WHERE first = 'west') c;
4. DEPENDENT SUBQUERY
이 선택 유형 값은 하위 쿼리를 사용하기 위해 정의됩니다. 다음 SQL 문은 이 값을 제공합니다:
mysql> EXPLAIN SELECT p.*
-> FROM parent p
-> WHERE p.id NOT IN (SELECT c.parent_id FROM child c); 🎜>
5. UNION UNION 문의 SQL 요소입니다.
6. UNION RESULT
UNION 문에 정의된 일련의 테이블에 대한 반환 결과입니다. select_type이 이 값인 경우 table의 값이 3d79052a4b39814111818a40e6c7009c,
인 것을 자주 볼 수 있습니다. 이는 일치하는 id 행이 이 세트의 일부임을 의미합니다. 다음 SQL은 UNION 및 UNION RESULT 선택 유형을 생성합니다.
mysql> EXPLAIN SELECT p.* FROM parent p WHERE p.val
LIKE 'a%'
-> ; SELECT p.* FROM parent p WHERE p.id > 5;
partitions 열은 해당 테이블에서 사용되는 파티션을 나타냅니다. 이 열은 EXPLAIN PARTITIONS 문에만 나타납니다.
Extra 열은 다양한 종류의 MySQL 최적화 경로에 대한 다양한 추가 정보를 제공합니다. 추가 열은 여러 값을 포함할 수 있고 다양한 값을 가질 수 있으며
이러한 값은 새 버전의 MySQL이 출시됨에 따라 계속 증가하고 있습니다. 다음은
에 일반적으로 사용되는 값 목록입니다.
http://www.php.cn/에서 보다 포괄적인 값 목록을 확인할 수 있습니다.
이 값은 쿼리가 where 문을 사용하여 결과를 처리함을 나타냅니다. 예를 들어 전체 테이블 검색을 수행합니다. 인덱스도 사용되는 경우 필요한 데이터를 얻은 다음 읽기 버퍼를 처리하여 행 제약 조건을 달성합니다.
2. 임시 사용
이 값은 내부 임시(메모리 기반) 테이블을 사용함을 나타냅니다. 쿼리는 여러 임시 테이블을 사용할 수 있습니다. MySQL 이 쿼리 실행 중에 임시 테이블을 생성하는 데는 여러 가지 이유가 있습니다. 두 가지 일반적인 이유는 다른 테이블의 열에
DISTINCT를 사용하거나 다른 ORDER BY 및 GROUP BY 열을 사용하기 때문입니다.
자세한 내용은 http://www.php.cn/
of_query_execution_and_use_of_temp_tables를 참조하세요.
임시 테이블이 디스크 기반 MyISAM 스토리지 엔진을 사용하도록 강제할 수 있습니다
. 그 이유는 크게 두 가지입니다.
내부 임시 테이블이 차지하는 공간이 min(tmp_table_size, max_
heap_table_size) 시스템 변수의 한도를 초과했습니다.
TEXT/BLOB 컬럼이 사용되었습니다.
3. filesort 사용
ORDER BY 문의 결과입니다. 이는 CPU를 많이 사용하는 프로세스일 수 있습니다. 적절한 인덱스를 선택하고 인덱스를 사용하여 쿼리 결과를 정렬하면 성능을 향상시킬 수 있습니다. 자세한 절차는 4장을 참조하세요.
4. 인덱스 사용
이 값은 인덱스만 사용하면 쿼리 테이블의 요구 사항을 충족할 수 있으며, 테이블 데이터에 직접 접근할 필요가 없음을 강조합니다. 이 값을 이해하려면 5장의 자세한 예제를 참조하세요.
5. 조인 버퍼 사용
이 값은 조인 조건을 얻을 때 인덱스를 사용하지 않으며, 중간 결과를 저장하기 위해 조인 버퍼가 필요하다는 점을 강조합니다. 이 값이 나타나면 쿼리의 특정 조건에 따라 성능 향상을 위해 인덱스를 추가해야 할 수도 있다는 점에 유의해야 합니다.
6. 불가능 where
이 값은 where 문으로 인해 조건을 충족하는 행이 없음을 강조합니다. 다음 예를 참조하세요. mysql> EXPLAIN SELECT * FROM user WHERE 1=2;
7. 최적화된 테이블 선택
이 값은 인덱스를 사용하는 경우에만 의미됩니다. , 최적화 프로그램은 집계 함수 결과에서 하나의 행만 반환할 수 있습니다. 다음 예를 참조하십시오.
8. Distinct
이 값은 MySQL이 일치하는 첫 번째 행을 찾은 후 다른 행 검색을 중지함을 의미합니다.
9. 인덱스 병합
MySQL이 주어진 테이블에서 둘 이상의 인덱스를 사용하기로 결정하면 다음 형식 중 하나가 나타나 인덱스 사용 및 병합 유형을 자세히 설명합니다. sort_union(...) 사용 Union(...) 사용
intersect(...) 사용
2.9 id
id 열은 QEP에 표시된 테이블에 대한 연속 참조입니다.
2.10 ref
ref 열은 인덱스 비교에 사용되는 열이나 상수를 식별하는 데 사용할 수 있습니다. 2.11 filtered 2.12 type 3 解释EXPLAIN 输出结果 以上就是MySQL EXPLAIN 命令详解学习的内容，更多相关内容请关注PHP中文网（www.php.cn）！
filtered 列给出了一个百分比的值，这个百分比值和rows 列的值一起使用，可以估计出那些将要和QEP 中的前一个表进行连
接的行的数目。前一个表就是指id 列的值比当前表的id 小的表。这一列只有在EXPLAIN EXTENDED 语句中才会出现。
type 列代表QEP 中指定的表使用的连接方式。下面是最常用的几种连接方式：
const 当这个表最多只有一行匹配的行时出现system 这是const 的特例，当表只有一个row 时会出现
eq_ref 这个值表示有一行是为了每个之前确定的表而读取的
ref 这个值表示所有具有匹配的索引值的行都被用到
range 这个值表示所有符合一个给定范围值的索引行都被用到
ALL 这个值表示需要一次全表扫描其他类型的值还有fulltext 、ref_or_null 、index_merge 、unique_subquery、index_subquery 以及index。
想了解更多信息可以访问http://www.php.cn/。
理解你的应用程序(包括技术和实现可能性)和优化SQL 语句同等重要。下面给出一个从父子关系中获取孤立的父辈记录的商
业需求的例子。这个查询可以用三种不同的方式构造。尽管会产生相同的结果，但QEP 会显示三种不同的路径。 mysql> EXPLAIN SELECT p.*
-> FROM parent p
-> WHERE p.id NOT IN (SELECT c.parent_id FROM child
c)\G
********************* 1. row ***********************
id: 1
select type: PRIMARY
table: p
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 160
Extra: Using where
********************* 2. row ***********************
id: 2
select_type: DEPENDENT SUBQUERY
table: c
type: index_subquery
possible_keys: parent_id
key: parent_id
key_len: 4
ref: func
rows: 1
Extra: Using index
2 rows in set (0.00 sec)
EXPLAIN SELECT p.* FROM parent p LEFT JOIN child c ON p.id = c.parent_id WHERE c.child_id IS NULL\G
********************* 1. row ***********************
id: 1
select_type: SIMPLE
table: p
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 160
Extra:
********************* 2. row ***********************
id: 1
select_type: SIMPLE
table: c
type: ref
possible_keys: parent_id
key: parent_id
key_len: 4
ref: test.p.id
rows: 1
Extra: Using where; Using index; Not exists
2 rows in set (0.00 sec)