일반적으로 느린 쿼리 SQL
语句时会使用EXPLAIN
命令来查看SQL
语句的执行计划,通过返回的信息,可以了解到Mysql
优化器是如何执行SQL
문을 쿼리하고 분석하면 최적화 아이디어를 제공하는 데 도움이 됩니다.
explain 명령은 주로 SQL 문의 실행 계획을 보는 데 사용됩니다. 이 명령은 옵티마이저의 SQL 쿼리 문 실행을 시뮬레이션할 수 있으며 SQL을 작성하고 최적화하는 데 도움이 될 수 있습니다. 그렇다면 SQL을 최적화하는 데 도움이 되는 구체적인 정보는 무엇입니까?
테이블 읽기 순서
데이터 읽기 작업의 동작 종류
어떤 인덱스를 사용할 수 있는지
어떤 인덱스가 실제로 사용되는지
테이블 간 참조
행 수 각 테이블은 옵티마이저에 의해 쿼리됩니다
사용법: explain + sql to be running
explain은 실행할 SQL에 대한 실행 계획 목록을 반환합니다. , 목록에는 12개의 필드가 포함되어 있으며 각 필드는 실행 계획에서 SQL이 실행되는 방식을 공동으로 설명합니다. 다음 목록에서는 실행 계획 테이블의 필드 의미를 자세히 설명합니다.
Field name | Description |
---|---|
id | 읽기 순서를 결정하는 select 문 쿼리의 시퀀스 번호 of the table |
select_type | 쿼리의 종류, 즉 데이터 읽기 연산의 연산 종류 |
table | 쿼리의 테이블 이름 |
partitions | Table partitions |
type | 액세스 유형 |
possible_keys | 사용 가능한 인덱스. 쿼리에 포함된 필드에 인덱스가 있는 경우 해당 인덱스가 나열되지만 실제로 쿼리에서 사용되지 않을 수도 있습니다. 이 필드가 null이지만 필드 키가 null이 아닌 경우 이 상황은 검색 시 사용할 수 있는 보조 인덱스 트리가 없지만 보조 인덱스에 쿼리해야 하는 필드가 포함되어 있으므로 클러스터형 인덱스(클러스터형 인덱스) )는 더 이상 검색되지 않고, 대신 보조 인덱스 트리가 스캔되며(보조 인덱스 트리는 상대적으로 작음) 이때 일반 액세스 유형은 인덱스이며 전체 인덱스 트리가 스캔됩니다. . |
key | 실제 스캔에 사용되는 인덱스입니다. null인 경우 인덱스는 사용되지 않습니다. 쿼리에 포함 인덱스가 사용되는 경우 인덱스는 키 목록에만 나타납니다. |
인덱스에 사용되는 바이트 수입니다. 이 열은 쿼리에 사용된 인덱스의 길이를 계산하는 데 사용할 수 있습니다. 길이가 짧을수록 key_len에 표시되는 값은 사용된 실제 길이가 아닌 인덱스 필드의 최대 길이입니다. 즉, key_len은 테이블을 기반으로 합니다. 정의는 테이블에서 검색되지 않고 계산됩니다. | |
는 사용되는 인덱스 열을 보여줍니다. 가능하다면 인덱스 열에서 값을 찾는 데 사용되는 열 또는 상수입니다. | |
테이블 통계 및 인덱스 선택을 기반으로 필요한 레코드를 찾는 데 필요한 읽기를 대략적으로 추정합니다. 행 수; | |
검색 조건으로 필터링한 후 남은 데이터의 비율입니다. | |
다른 열에 표시하기에는 적합하지 않지만 매우 중요한 추가 정보가 포함되어 있습니다. |
Description | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
실행 순서 위에서 아래로 다음 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
subquery인 경우 id의 일련번호가 증가하며, id 값이 클수록 우선 순위가 높아져 더 일찍 실행됩니다. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
id가 같으면 그룹으로 간주하여 위에서 아래로 순차적으로 실행됩니다. id 값이 높을수록 우선순위가 높을수록 빨리 실행됩니다 |
쿼리에 복잡한 하위 쿼리가 포함된 경우 가장 바깥쪽 쿼리가 표시됩니다. | |
---|---|
하위 쿼리의 첫 번째 SELECT는 외부 쿼리에 따라 다릅니다. 즉, 하위 쿼리는 외부 쿼리의 결과에 따라 달라집니다. | |
from 목록에 포함된 하위 쿼리는 DERIVED(파생 테이블)로 표시됩니다. MySQL은 이러한 하위 쿼리를 재귀적으로 실행하고 결과를 임시 테이블에 저장합니다. Union 다음에 나타나면 Union으로 표시됩니다. from 절의 하위 쿼리에 Union이 포함되면 외부 선택은 Union 테이블에서 | |
로 표시됩니다. Union merging 집합에서 선택 쿼리의 결과를 가져옵니다) | |
구체화된 테이블, 하위 쿼리가 쿼리와 연결되면 하위 쿼리 결과가 구체화된 임시 테이블에 저장되고 메인 테이블이 됩니다. 임시 테이블의 데이터를 기반으로 일치합니다. | |
UNION UNION의 두 번째 이후 쿼리문은 외부 쿼리에 따라 달라집니다 | |
(3) 쿼리에서 별칭을 사용하는 경우 table | 에 표시되는 쿼리 테이블 이름은 여기에 표시된 것은 별칭입니다. 데이터 테이블에 작업이 없으면 null로 표시되거나 다음 중 하나가 될 수 있습니다. |
Type name | |
는 이것이 임시 테이블임을 나타내고, 다음 N은 실행 계획의 ID로 이 쿼리에서 결과가 생성됨을 나타냅니다. |
(4)partitions 쿼리는 레코드의 파티션과 일치합니다. 파티션을 나누지 않은 테이블에는 (5)type依次从好到差:
除了 我们自己创建一系列表来实验下: SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for goods -- ---------------------------- DROP TABLE IF EXISTS `goods`; CREATE TABLE `goods` ( `id` int(11) NOT NULL, `sn` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL, `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of goods -- ---------------------------- INSERT INTO `goods` VALUES (1, 'sn123456', '衣服'); -- ---------------------------- -- Table structure for sku -- ---------------------------- DROP TABLE IF EXISTS `sku`; CREATE TABLE `sku` ( `id` int(11) NOT NULL, `goods_id` int(11) NOT NULL, `status` int(11) NOT NULL, `deleted` int(11) NOT NULL, `barcode` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL, `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE, UNIQUE INDEX `index_2`(`name`) USING BTREE, INDEX `index_1`(`goods_id`, `status`, `deleted`, `barcode`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of sku -- ---------------------------- INSERT INTO `sku` VALUES (1, 1, 1, 0, 'kt123456', '黑色'); SET FOREIGN_KEY_CHECKS = 1; system表只有一行记录(等于系统表),这是 const 类型的特例,平时不会出现,这个也可忽略不计; const表示通过索引一次就找到了,const 用于比较 primary key 或者 unique 索引。因为只匹配一行记录,所以很快。 如果将主键置于 where 列表中,mysql 就能将该查询转换成一个常量; EXPLAIN SELECT * FROM sku WHERE id=1;复制代码 eq_ref唯一性索引扫描,对于每一个索引键,表中只有一条记录与之匹配,常用于主键或唯一索引扫描;此类型通常出现在多表的 join 等值查询,表示对于前表的每一个结果,都只能匹配到后表的一行结果,查询效率较高。 EXPLAIN SELECT * FROM sku,goods WHERE sku.goods_id=goods.id; ref非唯一性索引扫描,返回匹配某个单独值得所有行,本质上也是一种索引访问,它返回所有匹配某个单独值的行,然而,它可能会找到多个符合条件的行,所以它应该属于查找和扫描的混合体; EXPLAIN SELECT * FROM sku WHERE goods_id=1; ref_or_null二级索引等值比较同时限定 is null 。 EXPLAIN SELECT * FROM sku WHERE name='123456' or name IS NULL; range只检索给定范围的行,使用一个索引来选择行,key列显示使用哪个索引,一般就是在你的 where 语句中出现了 between、、in 等的查询;这种范围索引扫描比全表扫描要好,因为它只需要开始于索引的某一个点,结束于另一个点,不用扫描全部索引; EXPLAIN SELECT * FROM sku WHERE id BETWEEN 1 and 10; indexindex 和 all 区别为 index 类型只遍历索引树,这通常比 all 快,因为索引文件通常比数据文件小;也就是说虽然 all 和 index 都是读写表,但 index 是从索引中读取的,而 all 是从硬盘中读的; EXPLAIN SELECT barcode FROM sku WHERE deleted=0; all也就是全表扫描; EXPLAIN SELECT * FROM sku WHERE deleted=0; (6)possible_keys查询可能使用到的索引都会在这里列出来。 (7)key查询真正使用到的索引, (8)key_lenkey_len 表示该列计算查询中使用的索引的长度。例如: (5) 유형🎜🎜🎜최상에서 최악까지: 🎜
system >const > ;eq_ref >ref >ref_or_null >범위 >색인 > ;ALL 🎜🎜(7)key🎜🎜🎜실제로 사용된 인덱스를 쿼리합니다.
4 + 3 * 20 + 2 + 1 = 67 입니다. 구체적인 계산 규칙은 아래 표와 같습니다. 🎜 |
---|
값 유형 | 값 이름 | Description |
---|---|---|
String | CHAR(n) | n 바이트 길이 |
VARCHAR(n) | utf8 인코딩인 경우, 3n + 2바이트이고, utf8mb4 인코딩인 경우 4n + 2바이트입니다. | |
숫자 유형 | TINYINT | 1바이트 |
SMALLINT | 2바이트 | |
MEDIUMINT | 3바이트 | |
INT | 4바이트 | |
BIGINT | 8바이트 | |
시간 유형 | DATE | 3바이트 |
TIMESTAMP | 4바이트 | |
DATETIME | 8바이트 | |
Field 속성 | NULL 속성은 1바이트를 차지합니다. 필드가 NULL이 아닌 경우 해당 필드는 채워지지 않습니다. |
상수 동등 쿼리인 경우 여기에 const
가 표시됩니다. 연결 쿼리인 경우 구동 테이블의 실행 계획에 의 관련 필드가 표시됩니다. 조건이 표현식이나 함수를 사용하거나 조건 열이 내부 암시적 변환을 수행하는 경우 func
로 표시될 수 있습니다. const
,如果是连接查询,被驱动表的执行计划这里会显示驱动表的关联字段,如果是条件使用了表达式或者函数,或者条件列发生了内部隐式转换,这里可能显示为func
。
这里是执行计划中估算的扫描行数,不是精确值。
使用explain extended
时会出现这个列,5.7
之后的版本默认就有这个字段,不需要使用explain extended
了。这个字段表示存储引擎返回的数据在server层过滤后,剩下多少满足查询的记录数量的比例,注意是百分比,不是具体记录数。
这个列可以显示的信息非常多,有几十种,常用的有:
1、distinct:在select
部分使用了distinct
关键字
2、no tables used:不带from
字句的查询或者From dual
查询。使用not in()
形式子查询或not exists()
运算符的连接查询,这种叫做反连接。即,一般连接查询是先查询内表,再查询外表,反连接就是先查询外表,再查询内表。
3、using filesort:说明mysql
会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取。mysql
中无法利用索引完成的排序操作称为“文件排序”。排序时无法使用到索引时,就会出现这个。常见于order by
语句中,需要尽快优化
4、using index:查询时不需要回表查询,直接通过索引就可以获取查询的数据。
5、using join buffer(block nested loop),using join buffer(batched key accss) :5.6.x
之后的版本优化关联查询的BNL
,BKA
特性。主要是减少内表的循环数量以及比较顺序地扫描查询。
6、using sort_union,using_union,using intersect,using sort_intersection:
and
的各个索引的条件时,该信息表示是从处理结果获取交集or
连接各个使用索引的条件时,该信息表示从处理结果获取并集and
和or
查询信息量大时,先查询主键,然后进行排序合并后,才能读取记录并返回。7、using temporary:表示使用了临时表存储中间结果。临时表可以是内存临时表和磁盘临时表,执行计划中看不出来,需要查看status
变量,used_tmp_table
,used_tmp_disk_table
才能看出来。常见于order by
和分组查询group by
。group by
一定要遵循所建索引的顺序与个数。需要尽快优化
8、using where:表示存储引擎返回的记录并不是所有的都满足查询条件,需要在server
层进行过滤。查询条件中分为限制条件和检查条件,5.6
之前,存储引擎只能根据限制条件扫描数据并返回,然后server
层根据检查条件进行过滤再返回真正符合查询的数据。5.6.x
之后支持ICP
特性(index condition pushdown,索引下推),可以把检查条件也下推到存储引擎层,不符合检查条件和限制条件的数据,直接不读取,这样就大大减少了存储引擎扫描的记录数量。extra
列显示using index condition
9、firstmatch(tb_name) :5.6.x
开始引入的优化子查询的新特性之一,常见于where
字句含有in()
类型的子查询。如果内表的数据量比较大,就可能出现这个
10、loosescan(m..n) :5.6.x
之后引入的优化子查询的新特性之一,在in()
(11)filtered
🎜🎜이 열은explain Extended
를 사용할 때 표시되며, 5.7
이후 버전은 기본적으로 이 필드를 사용하면 확장 설명
을 사용할 필요가 없습니다. 이 필드는 스토리지 엔진에서 반환된 데이터가 서버 계층에서 필터링된 후 쿼리를 충족하는 나머지 레코드의 비율을 나타냅니다. 이는 특정 레코드 수가 아닌 백분율입니다. 🎜에서 <code>distinct
키워드는 >select 부분🎜🎜🎜2에서 사용됩니다. 테이블이 사용되지 않습니다🎜: from
또는 가 없는 쿼리 이중 code>쿼리에서. <code>not in()
형식 하위 쿼리나 안티 조인이라고 하는 not presents()
연산자의 조인 쿼리를 사용하세요. 즉, 일반 조인 쿼리는 내부 테이블을 먼저 쿼리한 후 외부 테이블을 쿼리하는 반면, 안티 조인 쿼리는 외부 테이블을 먼저 쿼리한 후 내부 테이블을 쿼리합니다. 🎜🎜🎜3. using filesort🎜: 설명 mysql
은 테이블의 인덱스 순서대로 읽는 대신 외부 인덱스를 사용하여 데이터를 정렬합니다. mysql
에서 인덱스를 사용하여 완료할 수 없는 정렬 작업을 "파일 정렬"이라고 합니다. 이는 정렬 중에 인덱스를 사용할 수 없을 때 발생합니다. order by
문에서 흔히 볼 수 있으며 최대한 빨리 최적화해야 합니다🎜🎜🎜4. index 사용🎜: 쿼리할 때 테이블로 돌아갈 필요가 없으며 쿼리 데이터를 인덱스를 통해 직접 얻습니다. 🎜🎜🎜5. 조인 버퍼(블록 중첩 루프) 사용, 조인 버퍼(일괄 키 accss) 사용🎜: 5.6.x
이상 버전에서는 관련 쿼리에 대해 BNL
을 최적화합니다. BKA
기능. 주요 목적은 내부 테이블의 루프 수를 줄이고 쿼리를 순차적으로 스캔하는 것입니다. 🎜🎜🎜6. using sort_union, using_union, using intersect, using sort_intersection: 🎜🎜and
를 사용하여 각 인덱스의 조건을 나타낼 때 이 정보는 결과 획득 교차점에서 처리or
를 사용할 때 처리 결과에서 Union을 얻었음을 나타내는 정보입니다.and
및 or
를 사용하여 많은 양의 정보를 쿼리할 때 나타나는 점을 제외하면 이전 두 가지와 유사합니다. 먼저 쿼리한 다음 정렬하고 병합한 후에만 레코드를 읽고 반환할 수 있습니다. status
변수, used_tmp_table
, used_tmp_disk_table을 확인해야 합니다.
나오시면 됩니다. 일반적으로 order by
및 그룹 쿼리 group by
에 사용됩니다. 그룹화 기준
은 생성된 색인의 순서와 개수를 따라야 합니다. 가능한 한 빨리 최적화해야 합니다🎜🎜🎜8. using where🎜: 스토리지 엔진에서 반환된 모든 레코드가 쿼리 조건을 충족하지 않으며 서버
계층에서 필터링해야 함을 나타냅니다. 쿼리 조건은 제한 조건과 검사 조건으로 구분됩니다. 5.6
이전에는 스토리지 엔진이 제한 조건에 따라 데이터를 스캔하고 반환할 수만 있었으며, 이후에는 서버
계층이 검사 조건에 따라 실제로 쿼리와 일치하는 데이터를 필터링하여 반환합니다. 5.6.x
는 5.6.x
이후 ICP
기능(인덱스 조건 푸시다운, 인덱스 푸시다운)을 지원합니다. 검사 조건 및 제한 사항을 충족하지 않는 스토리지 엔진 계층은 데이터를 직접 읽지 않으므로 스토리지 엔진에서 검색하는 레코드 수가 크게 줄어듭니다. extra
열에는 🎜using index Condition🎜🎜🎜🎜9, firstmatch(tb_name)🎜이 표시됩니다. 이는 일반적으로 볼 수 있는 5.6.x
에 도입된 최적화 하위 쿼리의 새로운 기능 중 하나입니다. in where
절에는 in()
유형의 하위 쿼리가 포함되어 있습니다. 내부 테이블의 데이터 양이 상대적으로 많으면 이런 일이 발생할 수 있습니다🎜🎜🎜10.loosescan(m..n)🎜: 5.6.x
이후 도입된 최적화 하위 쿼리의 새로운 기능 중 하나 , in in()
유형 하위 쿼리에서 하위 쿼리가 중복 레코드를 반환하는 경우 이러한 현상이 발생할 수 있습니다🎜🎜🎜4. 주요 초점을 설명하세요🎜🎜🎜일반적으로 몇 개의 열에만 주의하면 됩니다. 결과: 🎜열 이름 | Remarks |
---|---|
type | 이 쿼리 테이블 연결 유형, 여기에서 이 쿼리의 대략적인 효율성을 확인할 수 있습니다. |
key | 최종 선택된 인덱스, 그렇지 않은 경우 인덱싱 측면에서 볼 때 이 쿼리의 효율성은 일반적으로 매우 낮습니다. |
key_len | 이 쿼리에서 결과 필터링에 사용된 인덱스의 실제 길이 |
rows | 스캔해야 할 예상 레코드 수 , 스캔해야 할 예상 레코드 수more 작을수록 좋음 |
Extra | 추가 추가 정보는 주로 filesort 사용 및 임시 사용의 두 가지 상황인지 확인합니다. 발생Using filesort 、Using temporary 这两种情况 |
再来看下Extra
列中需要注意出现的几种情况:
关键字 | 备注 |
---|---|
Using filesort | 将用外部排序而不是按照索引顺序排列结果,数据较少时从内存排序,否则需要在磁盘完成排序,代价非常高,需要添加合适的索引 |
Using temporary | 需要创建一个临时表来存储结果,这通常发生在对没有索引的列进行GROUP BY 时,或者ORDER BY 里的列不都在索引里,需要添加合适的索引
|
Using index | 表示MySQL 使用覆盖索引避免全表扫描,不需要再到表中进行二次查找数据,这是比较好的结果之一。注意不要和type 中的index 类型混淆 |
Using where | 通常是进行了全表/全索引扫描后再用WHERE 子句完成结果过滤,需要添加合适的索引
|
Impossible WHERE | 对Where 子句判断的结果总是false而不能选择任何数据,例如where 1=0 ,无需过多关注 |
Select tables optimized away | 使用某些聚合函数来访问存在索引的某个字段时,优化器会通过索引直接一次定位到所需要的数据行完成整个查询,例如MIN()MAX()
|
Extra
열에 주의해야 할 몇 가지 상황이 있습니다: 🎜Keywords🎜🎜Remarks🎜🎜🎜🎜🎜 🎜filesort🎜🎜를 사용하면 결과를 인덱스 순서로 정렬하는 대신 외부 정렬을 사용하게 됩니다. 데이터가 적을 경우 메모리에서 정렬해야 하며, 그렇지 않으면 디스크에서 정렬을 완료해야 하므로 비용이 많이 듭니다. 🎜적절한 인덱스를 추가해야 합니다🎜 🎜🎜🎜🎜임시 사용하기🎜🎜결과를 저장하기 위해 임시 테이블을 만들어야 하는데, 이는 일반적으로 인덱스가 없을 때 발생합니다. GROUP BY
의 열을 수행할 때 또는 ORDER BY
가 인덱스에 모두 포함되어 있지 않으므로 적절한 인덱스를 추가해야 합니다🎜🎜🎜🎜🎜인덱스🎜🎜를 사용한다는 것은 MySQL
가 전체 테이블 스캔을 피하기 위해 커버링 인덱스를 사용한다는 것을 의미합니다. 테이블에서 데이터를 두 번 검색할 필요가 없습니다. 이는 더 나은 결과 중 하나입니다. type
🎜🎜🎜🎜where 사용🎜🎜일반적으로 WHERE 절은 결과 필터링을 완료합니다 🎜적절한 인덱스를 추가해야 합니다🎜🎜🎜🎜🎜Impossible WHERE🎜🎜<code>Where
절의 판단 결과는 항상 false이며 데이터를 선택할 수 없습니다. , 예를 들어 where 1=0
, 너무 많은 주의를 기울일 필요가 없습니다🎜🎜🎜🎜최적화되지 않은 테이블을 선택하세요🎜🎜특정 집계 함수를 사용하여 인덱스가 있는 필드에 액세스하면 최적화 프로그램이 직접 위치를 찾습니다. 인덱스를 통해 필수 필드를 한 번에 데이터 행이 MIN()MAX()
와 같은 전체 쿼리를 완성하는데, 이 역시 더 나은 결과 중 하나입니다🎜🎜🎜🎜🎜[관련 추천: 🎜 mysql 동영상 튜토리얼🎜]🎜
위 내용은 이 기사는 MySQL 실행 계획을 빠르게 이해하는 데 도움이 될 것입니다.의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!