技術文章 » MySQL 執行計劃explain與索引資料結構推演

MySQL 執行計劃explain與索引資料結構推演

2020-11-13 18:01:07

欄目介紹執行計劃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等欄位的解釋：

ColumnMeaning
idThe SELECT identifier（該SELECT識別符號）
select_typeThe SELECT type（ 該SELECT型別）
tableThe table for the output row（輸出該行的表名）
partitionsThe matching partitions（匹配的分割區）
typeThe join type（連線型別）
possible_keysThe possible indexes to choose（可能的索引選擇）
keyThe index actually chosen（實際選擇的索引）
key_lenThe length of the chosen key（所選鍵的長度）
refThe columns compared to the index（與索引比較的列）
rowsEstimate of rows to be examined（檢查的預估行數）
filteredPercentage of rows filtered by table condition（按表條件過濾的行百分比）
extraAdditional information（附加資訊）

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

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

select_type ValueJSON NameMeaning
SIMPLENoneSimple SELECT (not using UNION or subqueries)
PRIMARYNoneOutermost SELECT
UNIONNoneSecond or later SELECT statement in a UNION
DEPENDENT UNIONdependent (true)Second or later SELECT statement in a UNION, dependent on outer query
UNION RESULTunion_resultResult of a UNION.
SUBQUERYNoneFirst SELECT in subquery
DEPENDENT SUBQUERYdependent (true)First SELECT in subquery, dependent on outer query
DERIVEDNoneDerived table
MATERIALIZEDmaterialized_from_subqueryMaterialized subquery
UNCACHEABLE SUBQUERYcacheable (false)A subquery for which the result cannot be cached and must be re-evaluated for each row of the outer query
UNCACHEABLE UNIONcacheable (false)The second or later select in a UNION that belongs to an uncacheable subquery (see UNCACHEABLE SUBQUERY)
  • 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樹

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

紅黑樹

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

再來看一下 B樹

B樹特點：

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

B樹儲存

圖例說明：

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

兩個關鍵詞劃分成的三個範圍域對應三個指標指向的子樹的資料的範圍域。

以根節點為例，關鍵字為 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+樹儲存

注意：

在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與索引資料結構推演的詳細內容，更多請關注TW511.COM其它相關文章！