欧美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)樗饕募ǔ1葦?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ù),所以查詢非??臁H鐚⒅麈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)文章

最新評論