欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

MySQL使用explain命令查看與分析索引的使用情況

 更新時間：2023年12月21日 09:46:34   作者：睿思達(dá)DBA_WGX  
這篇文章主要介紹了MySQL使用explain命令查看與分析索引的使用情況,具有很好的參考價(jià)值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教

在查詢語句中使用 explain 關(guān)鍵字，可以查看索引是否正在被使用，有沒有做全表掃描，并且輸出使用的索引信息。

語法格式如下：

explain select 語句;

一、數(shù)據(jù)準(zhǔn)備

有一個 emp 表，表中的索引信息如下：

mysql> show index from emp;
+-------+------------+--------------+--------------+-------------+-----------
| Table | Non_unique | Key_name     | Seq_in_index | Column_name | Collation 
+-------+------------+--------------+--------------+-------------+-----------
| emp   |          0 | PRIMARY      |            1 | e_id        | A         
| emp   |          0 | uq_idx_phone |            1 | phone       | A         
| emp   |          1 | dept_id      |            1 | dept_id     | A         
| emp   |          1 | idx_ename    |            1 | e_name      | A         
| emp   |          1 | idx_addr     |            1 | addr        | A         
+-------+------------+--------------+--------------+-------------+-----------
5 rows in set (0.00 sec)

二、使用 explain 分析查詢

執(zhí)行以下命令：

mysql> explain select * from emp where e_name='Mark'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: emp
   partitions: NULL
         type: ref
possible_keys: idx_ename
          key: idx_ename
      key_len: 81
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

查詢結(jié)果中的各個選項(xiàng)的含義如下：

（1） id：SELECT 識別符
（2） select_type：SELECT 查詢的類型
（3） table：數(shù)據(jù)表的名字
（4） partitions：匹配的分區(qū)
（5） type：訪問表的方式
（6） possible_keys：查詢時可能使用的索引
（7） key：實(shí)際使用的索引
（8） key_len：索引字段的長度
（9） ref：連接查詢時，用于顯示關(guān)聯(lián)的字段
（10） rows：需要掃描的行數(shù)(估算的行數(shù))
（11） filtered：按條件過濾后查詢到的記錄的百分比
（12） Extra：執(zhí)行情況的描述和說明

三、explain 各個選項(xiàng)的詳細(xì)說明及舉例

1、id

SELECT 識別符，是SELECT 查詢的序列號。

表示查詢中執(zhí)行 select 子句或操作表的順序，id 相同，執(zhí)行順序從上到下，id 不同，id 值越大則執(zhí)行的優(yōu)先級越高。

例如：

mysql> explain select * from emp where dept_id=(select dept_id from dept where dept_name='財(cái)務(wù)部')\G
--如果包含子查詢，id 的序號會遞增，id 值越大執(zhí)行優(yōu)先級越高
*************************** 1. row ***************************
           id: 1     ---外部查詢
  select_type: PRIMARY
        table: emp
*************************** 2. row ***************************
           id: 2   ---子查詢
  select_type: SUBQUERY
--id相同，執(zhí)行順序從上到下
mysql> explain select * from emp,dept where emp.dept_id=dept.dept_id\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: dept
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: emp

2、select_type

表示查詢中每個 select 子句的類型。有以下幾種類型：
(1) SIMPLE（簡單的 select 查詢，查詢中不包含子查詢或 union 查詢）
例如：
mysql> explain select * from dept\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
(2) PRIMARY（子查詢中最外層查詢，查詢中若包含任何復(fù)雜的子部分，最外層的 select 被標(biāo)記為 PRIMARY）
例如：
mysql> explain select * from emp where dept_id=(select dept_id from dept where dept_name='財(cái)務(wù)部')\G
*************************** 1. row ***************************
           id: 1     ---外部查詢
  select_type: PRIMARY
        table: emp
*************************** 2. row ***************************
           id: 2   ---子查詢
  select_type: SUBQUERY
(3) SUBQUERY（子查詢中的第一個SELECT，結(jié)果不依賴于外部查詢）
例如：見上一個例子。
(4) DEPENDENT SUBQUERY(子查詢中的第一個SELECT，依賴于外部查詢)
例如：
mysql> explain select * from dept where exists (select * from emp where emp.dept_id=dept.dept_id)\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
*************************** 2. row ***************************
           id: 2
  select_type: DEPENDENT SUBQUERY 
(5) UNION（UNION中的第二個或后面的SELECT語句）
例如：
mysql> explain select * from emp where dept_id=11 union select * from emp where dept_id=22\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
*************************** 2. row ***************************
           id: 2
  select_type: UNION
*************************** 3. row ***************************
           id: NULL
  select_type: UNION RESULT
(6) UNION RESULT（UNION的結(jié)果，union語句中第二個select開始后面所有select）
例如：見上一個例子

3、table

查詢所用的表名稱，可能是別名。例如：

mysql> explain select * from emp e,dept d where e.dept_id=d.dept_id\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: d
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: e

mysql> explain select * from emp\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: emp

4、partitions

查詢匹配的分區(qū)：例子略。

5、type

訪問表的方式，表示 MySQL 在表中找到所需行的方式。

常用的類型有： ALL、index、range、 ref、eq_ref、const、system、NULL（從左到右，性能從差到好）。

例如：

查詢用到的表及索引情況如下：

mysql> show index from dept;
+-------+------------+----------+--------------+-------------+-----------
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation 
+-------+------------+----------+--------------+-------------+-----------
| dept  |          0 | PRIMARY  |            1 | dept_id     | A         
+-------+------------+----------+--------------+-------------+-----------
1 row in set (0.00 sec)

mysql> show index from emp;
+-------+------------+--------------+--------------+-------------+-----------
| Table | Non_unique | Key_name     | Seq_in_index | Column_name | Collation 
+-------+------------+--------------+--------------+-------------+-----------
| emp   |          0 | PRIMARY      |            1 | e_id        | A         
| emp   |          0 | uq_idx_phone |            1 | phone       | A         
| emp   |          1 | dept_id      |            1 | dept_id     | A         
| emp   |          1 | idx_ename    |            1 | e_name      | A         
| emp   |          1 | idx_addr     |            1 | addr        | A         
+-------+------------+--------------+--------------+-------------+-----------
5 rows in set (0.00 sec)

常用的類型的說明及舉例：

（1）ALL：Full Table Scan，如果查詢沒有使用索引，MySQL將遍歷全表以找到匹配的行。
例如：
mysql> explain select * from emp where birth='1998-1-1'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: emp
   partitions: NULL
         type: ALL
--該查詢的條件沒有創(chuàng)建索引，因此是全表掃描。
（2）index: 全索引掃描，和 ALL 相比，index 只遍歷索引樹，通常比 ALL 快，因?yàn)樗饕募ǔ１葦?shù)據(jù)文件小。
例如：
mysql> explain select e_id from emp\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: emp
   partitions: NULL
         type: index
--該查詢是把 e_id 列中所有數(shù)據(jù)全部取出，并且對 e_id 列創(chuàng)建了索引，因此需要遍歷整個索引樹
（3）range：檢索給定范圍的行，可以在 key 列中查看使用的索引，一般出現(xiàn)在 where 語句的條件中，如使用between、>、<、in等查詢。
例如：
mysql> explain select * from emp where e_id>1000\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: emp
   partitions: NULL
         type: range
（4）ref: 非唯一性索引掃描，返回匹配某個單獨(dú)值的所有行。本質(zhì)上也是一種索引訪問，返回匹配某條件的多行值。
例如：
mysql> explain select * from emp where dept_id=11\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: emp
   partitions: NULL
         type: ref
--該查詢針對 dept_id 字段進(jìn)行查詢，查詢到多條記錄，并且為 dept_id 字段創(chuàng)建了索引。
（5）eq_ref: 唯一索引掃描，對于每個索引鍵，表中只有一條記錄與之匹配。常見主鍵或唯一索引掃描。
mysql> explain select * from emp join dept on emp.dept_id=dept.dept_id where e_name='Jack'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: emp
   partitions: NULL
         type: ref
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: dept
   partitions: NULL
         type: eq_ref
（6）const: 表示通過一次索引就找到了結(jié)果，常出現(xiàn)于 primary key 或 unique 索引。因?yàn)橹黄ヅ湟恍袛?shù)據(jù)，所以查詢非?？臁Ｈ鐚⒅麈I置于 where 條件中，MySQL 就能將查詢轉(zhuǎn)換為一個常量。
例如：
mysql> explain select * from emp where e_id=1002\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: emp
   partitions: NULL
         type: const
（7）NULL: 執(zhí)行時不用訪問表或索引。
例如：
mysql> explain select 1 from dual\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: NULL
   partitions: NULL
         type: NULL

6、possible_keys

顯示可能應(yīng)用在表中的索引，可能一個或多個。

查詢涉及到的字段若存在索引，則該索引將被列出，但不一定被查詢實(shí)際使用。

例如：

mysql> explain select * from emp join dept on emp.dept_id=dept.dept_id where e_name='Jack'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: emp
   partitions: NULL
         type: ref
possible_keys: dept_id,idx_ename
          key: idx_ename
      key_len: 81
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using where
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: dept
   partitions: NULL
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: wanggx.emp.dept_id
         rows: 1
     filtered: 100.00
        Extra: NULL
2 rows in set, 1 warning (0.00 sec)

7、key

實(shí)際使用的索引，如為NULL，則表示未使用索引。

若查詢中使用了覆蓋索引，則該索引和查詢的 select 字段重疊。

見上一個例子。

8、key_len

表示索引所使用的字節(jié)數(shù)，可通過該列計(jì)算查詢中使用的索引長度。

在不損失精確性的情況下，長度越短越好。

例如：

mysql> explain select * from emp where e_id=1001\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: emp
   partitions: NULL
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)
--索引長度為4，因?yàn)橹麈I時整型，長度為4
mysql> explain select * from emp where phone='13037316644'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: emp
   partitions: NULL
         type: const
possible_keys: uq_idx_phone
          key: uq_idx_phone
      key_len: 81
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

9、ref

顯示關(guān)聯(lián)的字段，如果是非連接查詢，則顯示 const，如果是連接查詢，則會顯示關(guān)聯(lián)的字段。

例如：

mysql> explain select * from emp join dept on emp.dept_id=dept.dept_id where e_name='Jack'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: emp
   partitions: NULL
         type: ref
possible_keys: dept_id,idx_ename
          key: idx_ename
      key_len: 81
          ref: const
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: dept
   partitions: NULL
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: wanggx.emp.dept_id

10、rows

根據(jù)表統(tǒng)計(jì)信息及索引選用情況大致估算出找到所需記錄所要讀取的行數(shù)。

當(dāng)然該值越小越好。

mysql> explain select * from emp where salary=5000\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: emp
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 3
     filtered: 33.33
        Extra: Using where
1 row in set, 1 warning (0.00 sec)
--沒有使用索引，需要進(jìn)行全表掃描，一共讀取3行
mysql> explain select * from emp where dept_id=11\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: emp
   partitions: NULL
         type: ref
possible_keys: dept_id
          key: dept_id
      key_len: 5
          ref: const
         rows: 2
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)
--使用了索引，掃描2行

11、filtered

表示選取的行和讀取的行的百分比，100表示選取了100%，80表示讀取了80%。

例如：

mysql> explain select * from emp where phone = '13703735488'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: emp
   partitions: NULL
         type: const
possible_keys: uq_idx_phone
          key: uq_idx_phone
      key_len: 81
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.01 sec)
--根據(jù)phone創(chuàng)建了唯一索引，并且條件是等號（=），因此filtered為100%

mysql> explain select * from emp where salary = 5200\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: emp
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2
     filtered: 50.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)
--由于salary字段沒有創(chuàng)建索引，因此執(zhí)行全表掃描，filtered為50%

12、extra

顯示一些重要的額外信息。一般有以下幾項(xiàng)：

（1）Using filesort：對數(shù)據(jù)使用一個外部的索引排序，而不是按照表內(nèi)的索引順序進(jìn)行讀取。當(dāng)Query中包含 order by 操作，而且無法利用索引完成的排序操作稱為“文件排序”。
例如：
mysql> explain select * from emp order by salary\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: emp
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2
     filtered: 100.00
        Extra: Using filesort
1 row in set, 1 warning (0.01 sec)
（2）Using temporary：表示MySQL需要使用臨時表來存儲結(jié)果集，常見于排序和分組查詢，常見 group by 與 order by。
例如：
mysql> explain select count(*) from emp group by salary\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: emp
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2
     filtered: 100.00
        Extra: Using temporary; Using filesort
1 row in set, 1 warning (0.00 sec)
（3）Using index：表明相應(yīng)的select操作中使用了覆蓋索引（select的數(shù)據(jù)列只從索引中就能取得數(shù)據(jù)，不必讀取數(shù)據(jù)行）。
mysql> explain select e_name,salary from emp order by e_name\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: emp
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2
     filtered: 100.00
        Extra: Using filesort
1 row in set, 1 warning (0.00 sec)

mysql> explain select e_name from emp order by e_name\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: emp
   partitions: NULL
         type: index
possible_keys: NULL
          key: idx_ename
      key_len: 81
          ref: NULL
         rows: 2
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

（4）Using join buffer：表明在獲取連接條件時沒有使用索引，并且需要連接緩沖區(qū)來存儲中間結(jié)果。如果出現(xiàn)了這個值，應(yīng)注意根據(jù)查詢的具體情況可能需要添加索引來改進(jìn)能。
例如：
mysql> explain select * from emp join dept on emp.dept_id=dept.dept_id\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: dept
   partitions: NULL
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2
     filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: emp
   partitions: NULL
         type: ALL
possible_keys: dept_id
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2
     filtered: 100.00
        Extra: Using where; Using join buffer (Block Nested Loop)
2 rows in set, 1 warning (0.00 sec)

總結(jié)

以上為個人經(jīng)驗(yàn)，希望能給大家一個參考，也希望大家多多支持腳本之家。

相關(guān)文章

最新評論