程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> mysql explain type連接類型示例

mysql explain type連接類型示例

編輯:MySQL綜合教程

mysql explain type連接類型示例


對於MySQL執行計劃的獲取,我們可以通過explain方式來查看,explain方式看似簡單,實際上包含的內容很多,尤其是輸出結果中的type類型列。理解這些不同的類型,對於我們SQL優化舉足輕重,本文僅描述explian輸出結果中的type列,同時給出其演示。

一、EXPLAIN 語句中type列的值

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             全表掃描

二、連接類型部分示例

1、all
-- 環境描述
(root@localhost) [sakila]> show variables like 'version';
+---------------+--------+
| 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: index
possible_keys: NULL
          key: idx_title
      key_len: 767
          ref: NULL
         rows: 1000
        Extra: Using index
1 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: range
possible_keys: idx_fk_customer_id
          key: idx_fk_customer_id
      key_len: 2
          ref: NULL
         rows: 2637
        Extra: Using where
1 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: range
possible_keys: idx_fk_customer_id
          key: idx_fk_customer_id
      key_len: 2
          ref: NULL
         rows: 86
        Extra: Using index condition
1 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: ref
possible_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='JESSIE'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: customer
         type: ref
possible_keys: idx_fisrt_last_name
          key: idx_fisrt_last_name
      key_len: 137
          ref: const
         rows: 2
        Extra: Using where; Using index
1 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: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 599
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: a
         type: ref
possible_keys: idx_fk_customer_id
          key: idx_fk_customer_id
      key_len: 2
          ref: sakila.b.customer_id
         rows: 13
        Extra: NULL
2 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,'robin'),(2,'jack'),(3,'henry');
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='robin')x;
+----+-------------+------------+--------+---------------+-------+---------+-------+------+-------+
| id | select_type | table      | type   | possible_keys | key   | key_len | ref   | rows | Extra |
+----+-------------+------------+--------+---------------+-------+---------+-------+------+-------+
|  1 | PRIMARY     |  | 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)

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved