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';
로그인 후 복사
이 UPDATE 문은 다음과 같이 SELECT 문으로 다시 작성할 수 있습니다.
SELECT col1, col2
FROM table1
WHERE id1 = 9
AND dt >= '2010-01-01';
로그인 후 복사
버전 5.6.10에서는 dml 문에 대한 explain 분석 작업을 직접 수행할 수 있습니다.MySQL 최적화 프로그램 비용을 기반으로 작동하며 QEP 위치를 제공하지 않습니다. 이는 각 SQL 문이 실행될 때 QEP가 동적으로 계산된다는 의미입니다. MySQL 저장 프로시저의 SQL 문도 실행될 때마다 QEP를 계산합니다. 저장 프로시저 캐시는 쿼리 트리만 구문 분석합니다.
2 각 열에 대한 자세한 설명
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
로그인 후 복사
이 QEP는 인덱스를 사용하지 않고(즉, 전체 테이블 스캔) 쿼리를 만족시키기 위해 많은 수의 행을 처리함을 보여줍니다. 동일한 SELECT 문에 대해 최적화된 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 실행 중(예: 하위 쿼리 또는 병합 작업에서) 생성된 물리적 스키마 테이블이나 내부 임시 테이블과 연결될 수 있습니다.
자세한 내용은 MySQL 참조 매뉴얼(http://www.php.cn/)을 참조하세요.
2.1 키 키 열은 최적화 프로그램이 선택한 인덱스를 나타냅니다. 일반적으로 SQL 쿼리에서는 테이블당 하나의 인덱스만 사용됩니다. 주어진 테이블에 두 개 이상의 인덱스가 사용되는 경우와 같이 인덱스 병합에는 몇 가지 예외가 있습니다. 다음은 QEP의 키 열의 예입니다. key: item_id key: NULL key: first, last SHOW CREATE TABLE
테이블을 확인하고 열 세부정보를 색인화하는 방법을 확인하세요. 키 열과 관련된 열에는 available_keys, 행 및 key_len도 포함됩니다.
2.2 ROWS
행 열은 누적 결과 집합에 있는 모든 행에 대해 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개의 행을 처리했습니다.
此列的一些示例值如下所示： 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';
mysql> EXPLAIN SELECT ID, post_title
-> FROM wp_posts
-> WHERE post_type='post'
-> AND post_status='publish'
-> AND post_date > '2010-06-01';
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의 값이 , 인 것을 자주 볼 수 있습니다. 이는 일치하는 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;
2.7 partitions
partitions 열은 해당 테이블에서 사용되는 파티션을 나타냅니다. 이 열은 EXPLAIN PARTITIONS 문에만 나타납니다.
2.8 Extra
Extra 열은 다양한 종류의 MySQL 최적화 경로에 대한 다양한 추가 정보를 제공합니다. 추가 열은 여러 값을 포함할 수 있고 다양한 값을 가질 수 있으며 이러한 값은 새 버전의 MySQL이 출시됨에 따라 계속 증가하고 있습니다. 다음은 에 일반적으로 사용되는 값 목록입니다. http://www.php.cn/에서 보다 포괄적인 값 목록을 확인할 수 있습니다.
1. where 사용
이 값은 쿼리가 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(...) 사용
![[웹 프런트엔드] Node.js 빠른 시작](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
