> 데이터 베이스 > MySQL 튜토리얼 > mysql explain 유형 연결 유형 예

mysql explain 유형 연결 유형 예

黄舟
풀어 주다: 2017-02-13 10:57:51
원래의
1592명이 탐색했습니다.


MySQL 실행 계획을 얻으려면 explain 메소드를 통해 볼 수 있습니다. explain 메소드는 간단해 보이지만 실제로는 많은 내용이 포함되어 있습니다. 특히 출력 결과를 입력합니다. 이러한 다양한 유형을 이해하는 것은 SQL 최적화에 매우 중요합니다. 이 기사에서는 explian 출력 결과의 유형 열만 설명하고 이에 대한 데모를 제공합니다.

explian 출력에 대한 전체 설명은 MySQL EXPLAIN SQL 출력 정보 설명을 참조하세요.

1 EXPLAIN 문의 유형 열 값

type:
    连接类型
    system          表只有一行    const           表最多只有一行匹配,通用用于主键或者唯一索引比较时
    eq_ref          每次与之前的表合并行都只在该表读取一行,这是除了system,const之外最好的一种,
                    特点是使用=,而且索引的所有部分都参与join且索引是主键或非空唯一键的索引
    ref             如果每次只匹配少数行,那就是比较好的一种,使用=或<=>,可以是左覆盖索引或非主键或非唯一键
    fulltext        全文搜索
    ref_or_null     与ref类似,但包括NULL
    index_merge     表示出现了索引合并优化(包括交集,并集以及交集之间的并集),但不包括跨表和全文索引。
                    这个比较复杂,目前的理解是合并单表的范围索引扫描(如果成本估算比普通的range要更优的话)
    unique_subquery 在in子查询中,就是value in (select...)把形如“select unique_key_column”的子查询替换。
                    PS:所以不一定in子句中使用子查询就是低效的!
    index_subquery  同上,但把形如”select non_unique_key_column“的子查询替换
    range           常数值的范围    index           a.当查询是索引覆盖的,即所有数据均可从索引树获取的时候(Extra中有Using Index);
                    b.以索引顺序从索引中查找数据行的全表扫描(无 Using Index);
                    c.如果Extra中Using Index与Using Where同时出现的话,则是利用索引查找键值的意思;
                    d.如单独出现,则是用读索引来代替读行,但不用于查找
    all             全表扫描
로그인 후 복사
로그인 후 복사

2. 연결 유형 부분 예시

1、all-- 环境描述
(root@localhost) [sakila]> show variables like &#39;version&#39;;
+---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| version       | 5.6.26 |
+---------------+--------+
MySQL采取全表遍历的方式来返回数据行,等同于Oracle的full table scan
(root@localhost) [sakila]> explain select count(description) from film;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | film  | ALL  | NULL          | NULL | NULL    | NULL | 1000 | NULL  |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
2、index
MySQL采取索引全扫描的方式来返回数据行,等同于Oracle的full index scan
(root@localhost) [sakila]> explain select title from film \G
*************************** 1. row ***************************           
id: 1  
select_type: SIMPLE        
table: film         
type: indexpossible_keys: NULL
          key: idx_title      
          key_len: 767          
          ref: NULL         
          rows: 1000        
          Extra: Using index1 row in set (0.00 sec)

3、  range
索引范围扫描,对索引的扫描开始于某一点,返回匹配值域的行,常见于between、<、>等的查询
等同于Oracle的index range scan
(root@localhost) [sakila]> explain select * from payment where customer_id>300 and customer_id<400\G
*************************** 1. row ***************************           
id: 1  
select_type: SIMPLE        
table: payment         
type: rangepossible_keys: idx_fk_customer_id          
key: idx_fk_customer_id      
key_len: 2          
ref: NULL         
rows: 2637        
Extra: Using where1 row in set (0.00 sec)

(root@localhost) [sakila]> explain select * from payment where customer_id in (200,300,400)\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE        
  table: payment         
  type: rangepossible_keys: idx_fk_customer_id          
  key: idx_fk_customer_id      
  key_len: 2          
  ref: NULL         
  rows: 86        
  Extra: Using index condition1 row in set (0.00 sec)

4、ref
非唯一性索引扫描或者,返回匹配某个单独值的所有行。常见于使用非唯一索引即唯一索引的非唯一前缀进行的查找
(root@localhost) [sakila]> explain select * from payment where customer_id=305\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE        
  table: payment         
  type: refpossible_keys: idx_fk_customer_id          
  key: idx_fk_customer_id      
  key_len: 2          
  ref: const         
  rows: 25        
  Extra: 1 row in set (0.00 sec)

idx_fk_customer_id为表payment上的外键索引,且存在多个不不唯一的值,如下查询
(root@localhost) [sakila]> select customer_id,count(*) from payment group by customer_id
    -> limit 2;
+-------------+----------+
| customer_id | count(*) |+-------------+----------+
|           1 |       32 ||           2 |       27 |
+-------------+----------+-- 下面是非唯一前缀索引使用ref的示例
(root@localhost) [sakila]> create index idx_fisrt_last_name on customer(first_name,last_name);
Query OK, 599 rows affected (0.09 sec)
Records: 599  Duplicates: 0  Warnings: 0(root@localhost) [sakila]> select first_name,count(*) from customer group by first_name 
    -> having count(*)>1 limit 2;
+------------+----------+| first_name | count(*) |
+------------+----------+| JAMIE      |        2 || JESSIE     |        2 |
+------------+----------+2 rows in set (0.00 sec)

(root@localhost) [sakila]> explain select first_name from customer where first_name=&#39;JESSIE&#39;\G
*************************** 1. row ***************************           
id: 1  select_type: SIMPLE        
table: customer         
type: refpossible_keys: idx_fisrt_last_name          
key: idx_fisrt_last_name      
key_len: 137          
ref: const         
rows: 2        
Extra: Using where; Using index1 row in set (0.00 sec)

(root@localhost) [sakila]> alter table customer drop index idx_fisrt_last_name;
Query OK, 599 rows affected (0.03 sec)
Records: 599  Duplicates: 0  Warnings: 0--下面演示出现在join是ref的示例
(root@localhost) [sakila]> explain select b.*,a.* from payment a inner join    -> customer b on a.customer_id=b.customer_id\G
*************************** 1. row ***************************           
id: 1  
select_type: SIMPLE        
table: b         
type: ALLpossible_keys: PRIMARY
          key: NULL
      key_len: NULL          
      ref: NULL         
      rows: 599        
      Extra: NULL
      *************************** 2. row ***************************           
      id: 1  
      select_type: SIMPLE        
      table: a         
      type: refpossible_keys: idx_fk_customer_id          
      key: idx_fk_customer_id      
      key_len: 2          
      ref: sakila.b.customer_id         
      rows: 13        
      Extra: NULL2 rows in set (0.01 sec)

5、eq_ref
类似于ref,其差别在于使用的索引为唯一索引,对于每个索引键值,表中只有一条记录与之匹配。
多见于主键扫描或者索引唯一扫描。
(root@localhost) [sakila]> explain select * from film a join film_text b 
    -> on a.film_id=b.film_id;
+----+-------------+-------+--------+---------------+---------+---------+------------------+------+-------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref         | rows | Extra       |
+----+-------------+-------+--------+---------------+---------+---------+------------------+------+-------------+
|  1 | SIMPLE      | b     | ALL    | PRIMARY       | NULL    | NULL    | NULL    | 1000 | NULL    |
|  1 | SIMPLE      | a     | eq_ref | PRIMARY       | PRIMARY | 2       | sakila.b.film_id |    1 | Using where |
+----+-------------+-------+--------+---------------+---------+---------+------------------+------+-------------+
(root@localhost) [sakila]> explain select title from film where film_id=5;
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+|  1 | SIMPLE      
| film  | const | PRIMAR   | PRIMARY | 2       | const |    1 | NULL  |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+6、const、system:
当MySQL对查询某部分进行优化,这个匹配的行的其他列值可以转换为一个常量来处理。
如将主键或者唯一索引置于where列表中,MySQL就能将该查询转换为一个常量
(root@localhost) [sakila]> create table t1(id int,ename varchar(20) unique);
Query OK, 0 rows affected (0.05 sec)

(root@localhost) [sakila]> insert into t1 values(1,&#39;robin&#39;),(2,&#39;jack&#39;),(3,&#39;henry&#39;);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

(root@localhost) [sakila]> explain select * from (select * from t1 where ename=&#39;robin&#39;)x;
+----+-------------+------------+--------+---------------+-------+---------+-------+------+-------+
| id | select_type | table      | type   | possible_keys | key   | key_len | ref   | rows | Extra |
+----+-------------+------------+--------+---------------+-------+---------+-------+------+-------+
|  1 | PRIMARY     | <derived2> | system | NULL          | NULL  | NULL    | NULL  |    1 | NULL  |
|  2 | DERIVED     | t1         | const  | ename         | ename | 23      | const |    1 | NULL  |
+----+-------------+------------+--------+---------------+-------+---------+-------+------+-------+
2 rows in set (0.00 sec)

7、type=NULL
MySQL不用访问表或者索引就可以直接得到结果
(root@localhost) [sakila]> explain select sysdate();+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra          |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | No tables used |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
1 row in set (0.00 sec)
로그인 후 복사


MySQL 실행 계획을 얻으려면 explain 메소드를 통해 볼 수 있지만, 설명 방법은 간단합니다. 특히 출력 결과의 유형 열에는 실제로 많은 내용이 포함되어 있습니다. 이러한 다양한 유형을 이해하는 것은 SQL 최적화에 매우 중요합니다. 이 기사에서는 explian 출력 결과의 유형 열만 설명하고 이에 대한 데모를 제공합니다.

explian 출력에 대한 전체 설명은 MySQL EXPLAIN SQL 출력 정보 설명을 참조하세요.

1 EXPLAIN 문의 유형 열 값

type:
    连接类型
    system          表只有一行    const           表最多只有一行匹配,通用用于主键或者唯一索引比较时
    eq_ref          每次与之前的表合并行都只在该表读取一行,这是除了system,const之外最好的一种,
                    特点是使用=,而且索引的所有部分都参与join且索引是主键或非空唯一键的索引
    ref             如果每次只匹配少数行,那就是比较好的一种,使用=或<=>,可以是左覆盖索引或非主键或非唯一键
    fulltext        全文搜索
    ref_or_null     与ref类似,但包括NULL
    index_merge     表示出现了索引合并优化(包括交集,并集以及交集之间的并集),但不包括跨表和全文索引。
                    这个比较复杂,目前的理解是合并单表的范围索引扫描(如果成本估算比普通的range要更优的话)
    unique_subquery 在in子查询中,就是value in (select...)把形如“select unique_key_column”的子查询替换。
                    PS:所以不一定in子句中使用子查询就是低效的!
    index_subquery  同上,但把形如”select non_unique_key_column“的子查询替换
    range           常数值的范围    index           a.当查询是索引覆盖的,即所有数据均可从索引树获取的时候(Extra中有Using Index);
                    b.以索引顺序从索引中查找数据行的全表扫描(无 Using Index);
                    c.如果Extra中Using Index与Using Where同时出现的话,则是利用索引查找键值的意思;
                    d.如单独出现,则是用读索引来代替读行,但不用于查找
    all             全表扫描
로그인 후 복사
로그인 후 복사

2. 연결 유형 부분 예시

1、all-- 环境描述
(root@localhost) [sakila]> show variables like &#39;version&#39;;
+---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| version       | 5.6.26 |
+---------------+--------+MySQL采取全表遍历的方式来返回数据行,等同于Oracle的full table scan
(root@localhost) [sakila]> explain select count(description) from film;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | film  | ALL  | NULL          | NULL | NULL    | NULL | 1000 | NULL  |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
2、index
MySQL采取索引全扫描的方式来返回数据行,等同于Oracle的full index scan
(root@localhost) [sakila]> explain select title from film \G
*************************** 1. row ***************************           
id: 1  
select_type: SIMPLE        
table: film         
type: indexpossible_keys: NULL
          key: idx_title      
          key_len: 767          
          ref: NULL         
          rows: 1000        
          Extra: Using index1 row in set (0.00 sec)

3、  range
索引范围扫描,对索引的扫描开始于某一点,返回匹配值域的行,常见于between、<、>等的查询
等同于Oracle的index range scan
(root@localhost) [sakila]> explain select * from payment where customer_id>300 and customer_id<400\G
*************************** 1. row ***************************           
id: 1  
select_type: SIMPLE        
table: payment         
type: rangepossible_keys: idx_fk_customer_id          
key: idx_fk_customer_id      
key_len: 2          
ref: NULL         
rows: 2637        
Extra: Using where1 row in set (0.00 sec)

(root@localhost) [sakila]> explain select * from payment where customer_id in (200,300,400)\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE        
  table: payment         
  type: rangepossible_keys: idx_fk_customer_id          
  key: idx_fk_customer_id      
  key_len: 2          
  ref: NULL         
  rows: 86        
  Extra: Using index condition1 row in set (0.00 sec)

4、ref
非唯一性索引扫描或者,返回匹配某个单独值的所有行。常见于使用非唯一索引即唯一索引的非唯一前缀进行的查找
(root@localhost) [sakila]> explain select * from payment where customer_id=305\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE        
  table: payment         
  type: refpossible_keys: idx_fk_customer_id          
  key: idx_fk_customer_id      
  key_len: 2          
  ref: const         
  rows: 25        
  Extra: 1 row in set (0.00 sec)

idx_fk_customer_id为表payment上的外键索引,且存在多个不不唯一的值,如下查询
(root@localhost) [sakila]> select customer_id,count(*) from payment group by customer_id
    -> limit 2;
+-------------+----------+
| customer_id | count(*) |+-------------+----------+
|           1 |       32 ||           2 |       27 |
+-------------+----------+-- 下面是非唯一前缀索引使用ref的示例
(root@localhost) [sakila]> create index idx_fisrt_last_name on customer(first_name,last_name);
Query OK, 599 rows affected (0.09 sec)
Records: 599  Duplicates: 0  Warnings: 0(root@localhost) [sakila]> select first_name,count(*) from customer group by first_name 
    -> having count(*)>1 limit 2;
+------------+----------+| first_name | count(*) |
+------------+----------+| JAMIE      |        2 || JESSIE     |        2 |
+------------+----------+2 rows in set (0.00 sec)

(root@localhost) [sakila]> explain select first_name from customer where first_name=&#39;JESSIE&#39;\G
*************************** 1. row ***************************           
id: 1  
select_type: SIMPLE        
table: customer         
type: refpossible_keys: idx_fisrt_last_name          
key: idx_fisrt_last_name      
key_len: 137          
ref: const         
rows: 2        
Extra: Using where; Using index1 row in set (0.00 sec)

(root@localhost) [sakila]> alter table customer drop index idx_fisrt_last_name;
Query OK, 599 rows affected (0.03 sec)
Records: 599  Duplicates: 0  Warnings: 0--下面演示出现在join是ref的示例
(root@localhost) [sakila]> explain select b.*,a.* from payment a inner join    
-> customer b on a.customer_id=b.customer_id\G
*************************** 1. row ***************************           
id: 1  
select_type: 
SIMPLE        
table: b         
type: ALLpossible_keys: PRIMARY
          key: NULL
      key_len: NULL          
      ref: NULL         
      rows: 599        
      Extra: NULL
      *************************** 2. row ***************************           
      id: 1  
      select_type: SIMPLE        
      table: a         
      type: refpossible_keys: idx_fk_customer_id          
      key: idx_fk_customer_id      
      key_len: 2          
      ref: sakila.b.customer_id         
      rows: 13        
      Extra: NULL2 rows in set (0.01 sec)

5、eq_ref
类似于ref,其差别在于使用的索引为唯一索引,对于每个索引键值,表中只有一条记录与之匹配。
多见于主键扫描或者索引唯一扫描。
(root@localhost) [sakila]> explain select * from film a join film_text b 
    -> on a.film_id=b.film_id;
+----+-------------+-------+--------+---------------+---------+---------+------------------+------+-------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref              | rows | Extra       |
+----+-------------+-------+--------+---------------+---------+---------+------------------+------+-------------+
|  1 | SIMPLE      | b     | ALL    | PRIMARY       | NULL    | NULL    | NULL          | 1000 | NULL   |
|  1 | SIMPLE      | a     | eq_ref | PRIMARY       | PRIMARY | 2       | sakila.b.film_id |    1 | Using where |
+----+-------------+-------+--------+---------------+---------+---------+------------------+------+-------------+
(root@localhost) [sakila]> explain select title from film where film_id=5;
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | film  | const | PRIMARY       | PRIMARY | 2       | const |    1 | NULL  |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
6、const、system:
当MySQL对查询某部分进行优化,这个匹配的行的其他列值可以转换为一个常量来处理。
如将主键或者唯一索引置于where列表中,MySQL就能将该查询转换为一个常量
(root@localhost) [sakila]> create table t1(id int,ename varchar(20) unique);
Query OK, 0 rows affected (0.05 sec)

(root@localhost) [sakila]> insert into t1 values(1,&#39;robin&#39;),(2,&#39;jack&#39;),(3,&#39;henry&#39;);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

(root@localhost) [sakila]> explain select * from (select * from t1 where ename=&#39;robin&#39;)x;
+----+-------------+------------+--------+---------------+-------+---------+-------+------+-------+
| id | select_type | table      | type   | possible_keys | key   | key_len | ref   | rows | Extra |
+----+-------------+------------+--------+---------------+-------+---------+-------+------+-------+|  
1 | PRIMARY     | <derived2> | system | NULL          | NULL  | NULL    | NULL  |    1 | NULL  ||  
2 | DERIVED     | t1         | const  | ename         | ename | 2
3      | const |    1 | NULL  |
+----+-------------+------------+--------+---------------+-------+---------+-------+------+-------+
2 rows in set (0.00 sec)

7、type=NULL
MySQL不用访问表或者索引就可以直接得到结果
(root@localhost) [sakila]> explain select sysdate();
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra          |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | No tables used |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
1 row in set (0.00 sec)
로그인 후 복사

위 내용은 mysql 설명 유형 연결 유형 예시 내용이며, 더 많은 관련 내용은 PHP 중국어 홈페이지(www.php.cn)를 참고해주세요!

관련 라벨:
원천:php.cn
본 웹사이트의 성명
본 글의 내용은 네티즌들의 자발적인 기여로 작성되었으며, 저작권은 원저작자에게 있습니다. 본 사이트는 이에 상응하는 법적 책임을 지지 않습니다. 표절이나 침해가 의심되는 콘텐츠를 발견한 경우 admin@php.cn으로 문의하세요.
인기 튜토리얼
더>
최신 다운로드
더>
웹 효과
웹사이트 소스 코드
웹사이트 자료
프론트엔드 템플릿