MySQL 執行計劃explain與索引數據結構推演過程是什么

了解MySQL 執行計劃explain與索引數據結構推演過程是什么？這個問題可能是我們日常學習或工作經常見到的。希望通過這個問題能讓你收獲頗深。下面是小編給大家帶來的參考內容，讓我們一起來看看吧！

先建好數據庫表，演示用的MySQL表，建表語句：

CREATE TABLE `emp` (  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主鍵',  `empno` int(11) DEFAULT NULL COMMENT '雇員工號',  `ename` varchar(255) DEFAULT NULL COMMENT '雇員姓名',  `job` varchar(255) DEFAULT NULL COMMENT '工作',  `mgr` varchar(255) DEFAULT NULL COMMENT '經理的工號',  `hiredate` date DEFAULT NULL COMMENT '雇用日期',  `sal` double DEFAULT NULL COMMENT '工資',  `comm` double DEFAULT NULL COMMENT '津貼',  `deptno` int(11) DEFAULT NULL COMMENT '所屬部門號',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='雇員表';CREATE TABLE `dept` (  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主鍵',  `deptno` int(11) DEFAULT NULL COMMENT '部門號',  `dname` varchar(255) DEFAULT NULL COMMENT '部門名稱',  `loc` varchar(255) DEFAULT NULL COMMENT '地址',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='部門表';CREATE TABLE `salgrade` (  `id` int(11) NOT NULL COMMENT '主鍵',  `grade` varchar(255) DEFAULT NULL COMMENT '等級',  `lowsal` varchar(255) DEFAULT NULL COMMENT '最低工資',  `hisal` varchar(255) DEFAULT NULL COMMENT '最高工資',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='工資等級表';CREATE TABLE `bonus` (  `id` int(11) NOT NULL COMMENT '主鍵',  `ename` varchar(255) DEFAULT NULL COMMENT '雇員姓名',  `job` varchar(255) DEFAULT NULL COMMENT '工作',  `sal` double DEFAULT NULL COMMENT '工資',  `comm` double DEFAULT NULL COMMENT '津貼',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='獎金表';復制代碼

后續執行計劃，查詢優化，索引優化等等知識的演練，基于以上幾個表來操作。

MySQL執行計劃

要進行SQL調優，你得知道要調優的SQL語句是怎么執行的，查看SQL語句的具體執行過程，以加快SQL語句的執行效率。

可以使用explain + SQL語句來模擬優化器執行SQL查詢語句，從而知道MySQL是如何處理SQL語句的。

關于explain可以看看官網介紹。

explain的輸出格式

mysql> explain select * from emp;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+|  1 | SIMPLE      | emp   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+復制代碼

字段id，select_type等字段的解釋：

id

select查詢的序列號，包含一組數字，表示查詢中執行select子句或者操作表的順序。

id號分為三類：

• 如果id相同，那么執行順序從上到下

mysql> explain select * from emp e join dept d on e.deptno = d.deptno join salgrade sg on e.sal between sg.lowsal and sg.hisal;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                              |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
|  1 | SIMPLE      | e     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL                                               |
|  1 | SIMPLE      | d     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where; Using join buffer (Block Nested Loop) |
|  1 | SIMPLE      | sg    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+復制代碼

這個查詢，用explain執行一下，id序號都是1，那么MySQL的執行順序就是從上到下執行的。

• 如果id不同，如果是子查詢，id的序號會遞增，id值越大優先級越高，越先被執行

mysql> explain select * from emp e where e.deptno in (select d.deptno from dept d where d.dname = 'SALEDept');
+----+--------------+-------------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+| id | select_type  | table       | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                              |
+----+--------------+-------------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+|  1 | SIMPLE       | <subquery2> | NULL       | ALL  | NULL          | NULL | NULL    | NULL | NULL |   100.00 | NULL                                               |
|  1 | SIMPLE       | e           | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |    50.00 | Using where; Using join buffer (Block Nested Loop) |
|  2 | MATERIALIZED | d           | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where                                        |
+----+--------------+-------------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+復制代碼

這個例子的執行順序是先執行id為2的，然后執行id為1的。

• id相同和不同的，同時存在：相同的可以認為是一組，從上往下順序執行，在所有組中，id值越大，優先級越高，越先執行

還是上面那個例子，先執行id為2的，然后按順序從上往下執行id為1的。

select_type

主要用來分辨查詢的類型，是普通查詢還是聯合查詢還是子查詢。

• SIMPLE 簡單的查詢，不包含子查詢和union

mysql> explain select * from emp;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+|  1 | SIMPLE      | emp   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+復制代碼

• primary 查詢中若包含任何復雜的子查詢，最外層查詢則被標記為Primary
• union 若第二個select出現在union之后，則被標記為union

mysql> explain select * from emp where deptno = 1001 union select * from emp where sal < 5000;
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+| id | select_type  | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra           |
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+|  1 | PRIMARY      | emp        | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |    25.00 | Using where     |
|  2 | UNION        | emp        | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |    33.33 | Using where     |
| NULL | UNION RESULT | <union1,2> | NULL       | ALL  | NULL          | NULL | NULL    | NULL | NULL |     NULL | Using temporary |
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+復制代碼

這條語句的select_type包含了primary和union

• dependent union 跟union類似，此處的depentent表示union或union all聯合而成的結果會受外部表影響
• union result 從union表獲取結果的select
• dependent subquery subquery的子查詢要受到外部表查詢的影響

mysql> explain select * from emp e where e.empno  in ( select empno from emp where deptno = 1001 union select empno from emp where sal < 5000);
+----+--------------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+| id | select_type        | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra           |
+----+--------------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+|  1 | PRIMARY            | e          | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |   100.00 | Using where     |
|  2 | DEPENDENT SUBQUERY | emp        | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |    25.00 | Using where     |
|  3 | DEPENDENT UNION    | emp        | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |    25.00 | Using where     |
| NULL | UNION RESULT       | <union2,3> | NULL       | ALL  | NULL          | NULL | NULL    | NULL | NULL |     NULL | Using temporary |
+----+--------------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+復制代碼

這條SQL執行包含了PRIMARY、DEPENDENT SUBQUERY、DEPENDENT UNION和UNION RESULT

• subquery 在select或者where列表中包含子查詢

舉例：

mysql> explain select * from emp where sal > (select avg(sal) from emp) ;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+|  1 | PRIMARY     | emp   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |    33.33 | Using where |
|  2 | SUBQUERY    | emp   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |   100.00 | NULL        |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+復制代碼

• DERIVED from子句中出現的子查詢，也叫做派生表
• MATERIALIZED Materialized subquery？
• UNCACHEABLE SUBQUERY 表示使用子查詢的結果不能被緩存

例如：

mysql> explain select * from emp where empno = (select empno from emp where deptno=@@sort_buffer_size);
+----+----------------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+| id | select_type          | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+----------------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+|  1 | PRIMARY              | emp   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |   100.00 | Using where |
|  2 | UNCACHEABLE SUBQUERY | emp   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |    25.00 | Using where |
+----+----------------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+復制代碼

• uncacheable union 表示union的查詢結果不能被緩存

table

對應行正在訪問哪一個表，表名或者別名，可能是臨時表或者union合并結果集。

1. 如果是具體的表名，則表明從實際的物理表中獲取數據，當然也可以是表的別名
2. 表名是derivedN的形式，表示使用了id為N的查詢產生的衍生表
3. 當有union result的時候，表名是union n1,n2等的形式，n1,n2表示參與union的id

type

type顯示的是訪問類型，訪問類型表示我是以何種方式去訪問我們的數據，最容易想到的是全表掃描，直接暴力的遍歷一張表去尋找需要的數據，效率非常低下。

訪問的類型有很多，效率從最好到最壞依次是：

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

一般情況下，得保證查詢至少達到range級別，最好能達到ref

• all 全表掃描，一般情況下出現這樣的sql語句而且數據量比較大的話那么就需要進行優化

通常，可以通過添加索引來避免ALL

• index 全索引掃描這個比all的效率要好，主要有兩種情況：
  • 一種是當前的查詢時覆蓋索引，即我們需要的數據在索引中就可以索取
  • 一是使用了索引進行排序，這樣就避免數據的重排序
• range 表示利用索引查詢的時候限制了范圍，在指定范圍內進行查詢，這樣避免了index的全索引掃描，適用的操作符： =, <>, >, >=, <, <=, IS NULL, BETWEEN, LIKE, or IN()

官網上舉例如下：

SELECT * FROM tbl_name WHERE key_column = 10;

SELECT * FROM tbl_name WHERE key_column BETWEEN 10 and 20;

SELECT * FROM tbl_name WHERE key_column IN (10,20,30);

SELECT * FROM tbl_name WHERE key_part1 = 10 AND key_part2 IN (10,20,30);

• index_subquery 利用索引來關聯子查詢，不再掃描全表

value IN (SELECT key_column FROM single_table WHERE some_expr)

• unique_subquery 該連接類型類似與index_subquery，使用的是唯一索引

value IN (SELECT primary_key FROM single_table WHERE some_expr)

• index_merge 在查詢過程中需要多個索引組合使用
• ref_or_null 對于某個字段既需要關聯條件，也需要null值的情況下，查詢優化器會選擇這種訪問方式

SELECT * FROM ref_table

WHERE key_column=expr OR key_column IS NULL;

• fulltext 使用FULLTEXT索引執行join
• ref 使用了非唯一性索引進行數據的查找

SELECT * FROM ref_table WHERE key_column=expr;

SELECT * FROM ref_table,other_table WHERE ref_table.key_column=other_table.column;

SELECT * FROM ref_table,other_table WHERE ref_table.key_column_part1=other_table.column AND ref_table.key_column_part2=1;

• eq_ref 使用唯一性索引進行數據查找

SELECT * FROM ref_table,other_table WHERE ref_table.key_column=other_table.column;

SELECT * FROM ref_table,other_table WHERE ref_table.key_column_part1=other_table.column AND ref_table.key_column_part2=1;

• const 這個表至多有一個匹配行

SELECT * FROM tbl_name WHERE primary_key=1;

SELECT * FROM tbl_name WHERE primary_key_part1=1 AND primary_key_part2=2;

例如：

mysql> explain select * from emp where id = 1;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+|  1 | SIMPLE      | emp   | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+復制代碼

• system 表只有一行記錄（等于系統表），這是const類型的特例，平時不會出現

possible_keys

顯示可能應用在這張表中的索引，一個或多個，查詢涉及到的字段上若存在索引，則該索引將被列出，但不一定被查詢實際使用

key

實際使用的索引，如果為null，則沒有使用索引，查詢中若使用了覆蓋索引，則該索引和查詢的select字段重疊

key_len

表示索引中使用的字節數，可以通過key_len計算查詢中使用的索引長度，在不損失精度的情況下長度越短越好

ref

顯示索引的哪一列被使用了，如果可能的話，是一個常數

rows

根據表的統計信息及索引使用情況，大致估算出找出所需記錄需要讀取的行數，此參數很重要，直接反應的sql找了多少數據，在完成目的的情況下越少越好

extra

包含額外的信息

• using filesort 說明mysql無法利用索引進行排序，只能利用排序算法進行排序，會消耗額外的位置
• using temporary 建立臨時表來保存中間結果，查詢完成之后把臨時表刪除
• using index 這個表示當前的查詢是覆蓋索引的，直接從索引中讀取數據，而不用訪問數據表。如果同時出現using where 表明索引被用來執行索引鍵值的查找，如果沒有，表示索引被用來讀取數據，而不是真的查找
• using where 使用where進行條件過濾
• using join buffer 使用連接緩存
• impossible where where語句的結果總是false

MySQL索引基本知識

想要了解索引的優化方式，必須要對索引的底層原理有所了解。

索引的優點

1. 大大減少了服務器需要掃描的數據量
2. 幫助服務器避免排序和臨時表
3. 將隨機io變成順序io（提升效率）

索引的用處

1. 快速查找匹配WHERE子句的行
2. 從consideration中消除行,如果可以在多個索引之間進行選擇，mysql通常會使用找到最少行的索引
3. 如果表具有多列索引，則優化器可以使用索引的任何最左前綴來查找行
4. 當有表連接的時候，從其他表檢索行數據
5. 查找特定索引列的min或max值
6. 如果排序或分組時在可用索引的最左前綴上完成的，則對表進行排序和分組
7. 在某些情況下，可以優化查詢以檢索值而無需查詢數據行

索引的分類

MySQL索引數據結構推演

索引用于快速查找具有特定列值的行。

如果沒有索引，MySQL必須從第一行開始，然后通讀整個表以找到相關的行。

表越大花費的時間越多，如果表中有相關列的索引，MySQL可以快速確定要在數據文件中間查找的位置，而不必查看所有數據。這比順序讀取每一行要快得多。

既然MySQL索引能幫助我們快速查詢到數據，那么它的底層是怎么存儲數據的呢？

幾種可能的存儲結構

hash

hash表的索引格式

hash表存儲數據的缺點：

1. 利用hash存儲的話需要將所有的數據文件添加到內存，比較耗費內存空間
2. 如果所有的查詢都是等值查詢，那么hash確實很快，但是在實際工作環境中范圍查找的數據更多一些，而不是等值查詢，這種情況下hash就不太適合了

事實上，MySQL存儲引擎是memory時，索引數據結構采用的就是hash表。

二叉樹

二叉樹的結構是這樣的：

二叉樹會因為樹的深度而造成數據傾斜，如果樹的深度過深，會造成io次數變多，影響數據讀取的效率。

AVL樹 需要旋轉，看圖例：

紅黑樹 除了旋轉操作還多了一個變色的功能（為了減少旋轉），這樣雖然插入的速度快，但是損失了查詢的效率。

二叉樹、AVL樹、紅黑樹 都會因為樹的深度過深而造成io次數變多，影響數據讀取的效率。

再來看一下 B樹

B樹特點：

• 所有鍵值分布在整顆樹中
• 搜索有可能在非葉子結點結束，在關鍵字全集內做一次查找,性能逼近二分查找
• 每個節點最多擁有m個子樹
• 根節點至少有2個子樹
• 分支節點至少擁有m/2顆子樹（除根節點和葉子節點外都是分支節點）
• 所有葉子節點都在同一層、每個節點最多可以有m-1個key，并且以升序排列

圖例說明：

每個節點占用一個磁盤塊，一個節點上有兩個升序排序的關鍵字和三個指向子樹根節點的指針，指針存儲的是子節點所在磁盤塊的地址。

兩個關鍵詞劃分成的三個范圍域對應三個指針指向的子樹的數據的范圍域。

以根節點為例，關鍵字為 16 和 34，P1 指針指向的子樹的數據范圍為小于 16，P2 指針指向的子樹的數據范圍為 16~34，P3 指針指向的子樹的數據范圍為大于 34。

查找關鍵字過程：

1、根據根節點找到磁盤塊 1，讀入內存。【磁盤 I/O 操作第 1 次】

2、比較關鍵字 28 在區間（16,34），找到磁盤塊 1 的指針 P2。

3、根據 P2 指針找到磁盤塊 3，讀入內存。【磁盤 I/O 操作第 2 次】

4、比較關鍵字 28 在區間（25,31），找到磁盤塊 3 的指針 P2。

5、根據 P2 指針找到磁盤塊 8，讀入內存。【磁盤 I/O 操作第 3 次】

6、在磁盤塊 8 中的關鍵字列表中找到關鍵字 28。

由此，我們可以得知B樹存儲的缺點：

• 每個節點都有key，同時也包含data，而每個頁存儲空間是有限的，如果data比較大的話會導致每個節點存儲的key數量變小
• 當存儲的數據量很大的時候會導致深度較大，增大查詢時磁盤io次數，進而影響查詢性能

那么MySQL索引數據結構是什么呢

官網：Most MySQL indexes (PRIMARY KEY, UNIQUE, INDEX, and FULLTEXT) are stored in B-trees

不要誤會，其實MySQL索引的存儲結構是B+樹，上面我們一頓分析，知道B樹是不合適的。

mysql索引數據結構---B+Tree

B+Tree是在BTree的基礎之上做的一種優化，變化如下：

1、B+Tree每個節點可以包含更多的節點，這個做的原因有兩個，第一個原因是為了降低樹的高度，第二個原因是將數據范圍變為多個區間，區間越多，數據檢索越快。

2、非葉子節點存儲key，葉子節點存儲key和數據。

3、葉子節點兩兩指針相互連接（符合磁盤的預讀特性），順序查詢性能更高。

B+樹存儲查找示意圖：

注意：

在B+Tree上有兩個頭指針，一個指向根節點，另一個指向關鍵字最小的葉子節點，而且所有葉子節點（即數據節點）之間是一種鏈式環結構。

因此可以對 B+Tree 進行兩種查找運算：一種是對于主鍵的范圍查找和分頁查找，另一種是從根節點開始，進行隨機查找。

由于B+樹葉子結點只存放data，根節點只存放key，那么我們計算一下，即使只有3層B+樹，也能制成千萬級別的數據。

你得知道的技（zhuang）術（b）名詞

假設有這樣一個表如下，其中id是主鍵：

mysql> select * from stu;
+------+---------+------+| id   | name    | age  |
+------+---------+------+|    1 | Jack Ma |   18 |
|    2 | Pony    |   19 |
+------+---------+------+復制代碼

回表

我們對普通列建普通索引，這時候我們來查：

select * from stu where name='Pony';復制代碼

由于name建了索引，查詢時先找name的B+樹，找到主鍵id后，再找主鍵id的B+樹，從而找到整行記錄。

這個最終會回到主鍵上來查找B+樹，這個就是回表。

覆蓋索引

如果是這個查詢：

mysql> select id from stu where name='Pony';復制代碼

就沒有回表了，因為直接找到主鍵id，返回就完了，不需要再找其他的了。

沒有回表就叫覆蓋索引。

最左匹配

再來以name和age兩個字段建組合索引(name, age)，然后有這樣一個查詢：

select * from stu where name=? and age=?復制代碼

這時按照組合索引(name, age)查詢，先匹配name，再匹配age，如果查詢變成這樣：

select * from stu where age=?復制代碼

直接不按name查了，此時索引不會生效，也就是不會按照索引查詢---這就是最左匹配原則。

加入我就要按age查，還要有索引來優化呢？可以這樣做：

• （推薦）把組合索引(name, age)換個順序，建(age, name)索引
• 或者直接把age字段單獨建個索引

索引下推

可能也叫謂詞下推。。。

select t1.name,t2.name from t1 join t2 on t1.id=t2.id復制代碼

t1有10條記錄，t2有20條記錄。

我們猜想一下，這個要么按這個方式執行：

先t1,t2按id合并（合并后20條），然后再查t1.name,t2.name

或者：

先把t1.name,t2.name找出來，再按照id關聯

如果不使用索引條件下推優化的話，MySQL只能根據索引查詢出t1,t2合并后的所有行，然后再依次比較是否符合全部條件。

當使用了索引條件下推優化技術后，可以通過索引中存儲的數據判斷當前索引對應的數據是否符合條件，只有符合條件的數據才將整行數據查詢出來。

小結

1. Explain 為了知道優化SQL語句的執行，需要查看SQL語句的具體執行過程，以加快SQL語句的執行效率。
2. 索引優點及用處。
3. 索引采用的數據結構是B+樹。
4. 回表，覆蓋索引，最左匹配和索引下推。

感謝各位的閱讀！看完上述內容，你們對MySQL 執行計劃explain與索引數據結構推演過程是什么大概了解了嗎？希望文章內容對大家有所幫助。如果想了解更多相關文章內容，歡迎關注億速云行業資訊頻道。