Mysql에서 explain의 역할에 대한 자세한 설명-MySQL 튜토리얼-php.cn

이 글은 주로 인덱싱에 대한 지식이 포함된 Mysql의 explain 관련 내용을 소개하는데, 이 내용이 필요한 친구들이 모두 배울 수 있기를 바랍니다.

1. MYSQL 인덱스

인덱스(Index) : MySQL이 데이터를 효율적으로 얻을 수 있도록 도와주는 데이터 구조. 검색 효율성을 높이기 위해 사용되며 사전과 비교할 수 있습니다. 간단히 말해서 정렬되고 빠른 검색 데이터 구조로 이해될 수 있습니다.

색인의 역할: 질의 및 정렬을 용이하게 합니다(따라서 색인을 추가하면 where 문과 정렬 문 순서에 영향을 미칩니다).

데이터 외에도 데이터베이스는 특정 검색 알고리즘을 만족하는 데이터 구조를 유지하며 이러한 데이터 구조는 어떤 방식으로든 데이터를 참조합니다. 이를 통해 이러한 데이터 구조에 고급 검색 알고리즘을 구현할 수 있습니다. 이러한 데이터 구조는 인덱스입니다.

인덱스 자체도 용량이 매우 크고 메모리에 완전히 저장할 수 없기 때문에 인덱스 파일 형태로 디스크에 저장되는 경우가 많습니다.

우리가 일반적으로 지칭하는 인덱스는 특별한 언급이 없는 한 일반적으로 B-트리 인덱스입니다. (클러스터형 인덱스, 복합 인덱스, 접두사 인덱스, 고유 인덱스는 모두 기본적으로 B+ 트리 인덱스입니다.) B-트리 인덱스 외에 해시 인덱스도 있습니다.

이점:

A. 데이터 검색 효율성을 높이고 데이터베이스 IO 비용을 줄입니다.
B. 인덱스 열을 통해 데이터를 정렬하면 데이터 정렬 비용과 CPU 소비가 줄어듭니다.

단점:

A. 인덱스도 테이블입니다. 이 테이블은 기본 키와 인덱스 필드를 저장하고 엔터티 테이블의 레코드를 가리키므로 인덱스도 공간을 차지합니다.
B. 테이블에 대해 INSERT, UPDATE, DELETE 작업을 수행하면 MYSQL은 데이터를 업데이트할 뿐만 아니라 업데이트될 때마다 인덱스 파일에 추가된 인덱스 열 필드의 해당 정보를 저장합니다.

실제 제작 환경에서는 점진적으로 최적의 인덱스를 분석, 최적화, 구축하고 쿼리 조건을 최적화해야 합니다.

인덱스 분류:

1. 단일 값 인덱스 인덱스는 하나의 필드만 포함하며 테이블은 여러 개의 단일 열 인덱스를 가질 수 있습니다.
2. 고유 인덱스 인덱스 열의 값은 고유해야 하지만 null 값은 허용됩니다.
3. 복합 인덱스에는 여러 열이 포함됩니다.

한 테이블에 5개 미만의 인덱스를 생성하는 것이 좋습니다.

구문:

1.CREATE [UNIQUE] INDEX indexName (columnName(length))
2. ALTER myTable 추가 [UNIQUE] INDEX [indexName] ON (열 이름(길이));

Delete: DROP INDEX [indexName] ON myTable;

View: SHOW INDEX FROM table_nameG;

2, EXPLAIN

EXPLAIN의 역할: Mysql 최적화 프로그램이 SQL 쿼리 문을 실행하는 방법을 시뮬레이션하여 Mysql이 SQL 문을 처리하는 방법을 알 수 있습니다. 쿼리 문이나 테이블 구조의 성능 병목 현상을 분석합니다.

mysql> explain select * from tb_user;
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
| id | select_type | table  | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE   | tb_user | ALL | NULL     | NULL | NULL  | NULL |  1 | NULL |
+----+-------------+---------+------+---------------+------+---------+------+------+-------+

로그인 후 복사

(1) id 열:

(1), id는 위에서 아래로 동일한 순서로 실행됩니다

mysql> explain 
  -> SELECT*FROM tb_order tb1
  -> LEFT JOIN tb_product tb2 ON tb1.tb_product_id = tb2.id
  -> LEFT JOIN tb_user tb3 ON tb1.tb_user_id = tb3.id;
+----+-------------+-------+--------+---------------+---------+---------+---------------------------+------+-------+
| id | select_type | table | type  | possible_keys | key   | key_len | ref            | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+---------------------------+------+-------+
| 1 | SIMPLE   | tb1  | ALL  | NULL     | NULL  | NULL  | NULL           |  1 | NULL |
| 1 | SIMPLE   | tb2  | eq_ref | PRIMARY    | PRIMARY | 4    | product.tb1.tb_product_id |  1 | NULL |
| 1 | SIMPLE   | tb3  | eq_ref | PRIMARY    | PRIMARY | 4    | product.tb1.tb_user_id  |  1 | NULL |
+----+-------------+-------+--------+---------------+---------+---------+---------------------------+------+-------+

로그인 후 복사

(2), 하위 쿼리인 경우 id 일련번호는 자동으로 증가하며, id 값이 클수록 우선순위가 높아지고 더 일찍 실행됩니다.

mysql> EXPLAIN
  -> select * from tb_product tb1 where tb1.id = (select tb_product_id from tb_order tb2 where id = tb2.id =1);
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key   | key_len | ref  | rows | Extra    |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
| 1 | PRIMARY   | tb1  | const | PRIMARY    | PRIMARY | 4    | const |  1 | NULL    |
| 2 | SUBQUERY  | tb2  | ALL  | NULL     | NULL  | NULL  | NULL |  1 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+

로그인 후 복사

(3), ID는 같으면서도 다르며 동시에 존재합니다

mysql> EXPLAIN 
  -> select * from(select * from tb_order tb1 where tb1.id =1) s1,tb_user tb2 where s1.tb_user_id = tb2.id;
+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+
| id | select_type | table   | type  | possible_keys | key   | key_len | ref  | rows | Extra |
+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+
| 1 | PRIMARY   | <derived2> | system | NULL     | NULL  | NULL  | NULL |  1 | NULL |
| 1 | PRIMARY   | tb2    | const | PRIMARY    | PRIMARY | 4    | const |  1 | NULL |
| 2 | DERIVED   | tb1    | const | PRIMARY    | PRIMARY | 4    | const |  1 | NULL |
+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+

로그인 후 복사

derived2: 파생 테이블 2는 id=2

인 파생 테이블 tb1이 있음을 의미합니다(2 ) select_type 열: 데이터 읽기 작업의 작업 유형

　　1. SIMPLE: 단순 선택 쿼리, SQL에는 하위 쿼리 또는 UNION이 포함되지 않습니다.
　2. PRIMARY: 복잡한 하위 쿼리 부분을 포함하는 쿼리로, 가장 바깥쪽 쿼리는 PRIMARY
　3로 표시됩니다. SUBQUERY: 하위 쿼리가 select 또는 WHERE 목록에 포함됩니다
　4. DERIVED: FROM 목록에 포함된 하위 쿼리가 표시됩니다. DERIVED(파생 테이블)로서 MYSQL은 이러한 하위 쿼리를 재귀적으로 실행하고 결과 집합을 제로 타임 테이블에 넣습니다.
　5. UNION: UNION 다음에 두 번째 SELECT가 나타나면 UNION으로 표시되고, FROM 절의 하위 쿼리에 UNION이 포함되면 외부 SELECT는 DERIVED로 표시됩니다
　6. UNION RESULT: UNION Select에서

테이블 획득 결과 (3) 테이블 열: 데이터 행이 어느 테이블인지

(4) 유형 열: 최고 시스템에서 최악 시스템까지의 액세스 유형 > 범위 > 색인 > 모두

　　1、system:表只有一条记录(等于系统表),这是const类型的特例，平时业务中不会出现。
　　2、const:通过索引一次查到数据，该类型主要用于比较primary key 或者unique 索引，因为只匹配一行数据，所以很快;如果将主键置于WHERE语句后面，Mysql就能将该查询转换为一个常量。
　　3、eq_ref:唯一索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或者唯一索引扫描。
　　4、ref:非唯一索引扫描，返回匹配某个单独值得所有行，本质上是一种索引访问，它返回所有匹配某个单独值的行，就是说它可能会找到多条符合条件的数据，所以他是查找与扫描的混合体。
　　5、range：只检索给定范围的行，使用一个索引来选着行。key列显示使用了哪个索引。一般在你的WHERE 语句中出现between 、< 、> 、in 等查询，这种给定范围扫描比全表扫描要好。因为他只需要开始于索引的某一点，而结束于另一点，不用扫描全部索引。
　　6、index：FUll Index Scan 扫描遍历索引树(扫描全表的索引，从索引中获取数据)。
　　7、ALL 全表扫描从磁盘中获取数据百万级别的数据ALL类型的数据尽量优化。

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

（六）keys列:实际使用到的索引。如果为NULL，则没有使用索引。查询中如果使用了覆盖索引，则该索引仅出现在key列表中。覆盖索引：select 后的字段与我们建立索引的字段个数一致。

（七）ken_len列:表示索引中使用的字节数，可通过该列计算查询中使用的索引长度。在不损失精确性的情况下，长度越短越好。key_len 显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出来的。

（八）ref列:显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值。

（九）rows列(每张表有多少行被优化器查询):根据表统计信息及索引选用的情况，大致估算找到所需记录需要读取的行数。

（十）Extra列：扩展属性，但是很重要的信息。

1、 Using filesort(文件排序)：mysql无法按照表内既定的索引顺序进行读取。

 mysql> explain select order_number from tb_order order by order_money;
+----+-------------+----------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table  | type | possible_keys | key | key_len | ref | rows | Extra     |
+----+-------------+----------+------+---------------+------+---------+------+------+----------------+
| 1 | SIMPLE   | tb_order | ALL | NULL     | NULL | NULL  | NULL |  1 | Using filesort |
+----+-------------+----------+------+---------------+------+---------+------+------+----------------+
1 row in set (0.00 sec)

로그인 후 복사

说明：order_number是表内的一个唯一索引列，但是order by 没有使用该索引列排序，所以mysql使用不得不另起一列进行排序。

2、Using temporary:Mysql使用了临时表保存中间结果，常见于排序order by 和分组查询 group by。

mysql> explain select order_number from tb_order group by order_money;
+----+-------------+----------+------+---------------+------+---------+------+------+---------------------------------+
| id | select_type | table  | type | possible_keys | key | key_len | ref | rows | Extra              |
+----+-------------+----------+------+---------------+------+---------+------+------+---------------------------------+
| 1 | SIMPLE   | tb_order | ALL | NULL     | NULL | NULL  | NULL |  1 | Using temporary; Using filesort |
+----+-------------+----------+------+---------------+------+---------+------+------+---------------------------------+
1 row in set (0.00 sec)

로그인 후 복사

3、Using index 表示相应的select 操作使用了覆盖索引，避免访问了表的数据行，效率不错。

如果同时出现Using where ，表明索引被用来执行索引键值的查找。

如果没有同时出现using where 表明索引用来读取数据而非执行查找动作。

mysql> explain select order_number from tb_order group by order_number;
+----+-------------+----------+-------+--------------------+--------------------+---------+------+------+-------------+
| id | select_type | table  | type | possible_keys   | key        | key_len | ref | rows | Extra    |
+----+-------------+----------+-------+--------------------+--------------------+---------+------+------+-------------+
| 1 | SIMPLE   | tb_order | index | index_order_number | index_order_number | 99   | NULL |  1 | Using index |
+----+-------------+----------+-------+--------------------+--------------------+---------+------+------+-------------+
1 row in set (0.00 sec)

로그인 후 복사

4、Using where 查找

5、Using join buffer ：表示当前sql使用了连接缓存。

6、impossible where ：where 字句总是false ，mysql 无法获取数据行。

7、select tables optimized away：

8、distinct：