MySQL 執(zhí)行計(jì)劃explain與索引數(shù)據(jù)結(jié)構(gòu)推演過(guò)程是什么

了解MySQL 執(zhí)行計(jì)劃explain與索引數(shù)據(jù)結(jié)構(gòu)推演過(guò)程是什么？這個(gè)問(wèn)題可能是我們?nèi)粘W(xué)習(xí)或工作經(jīng)常見(jiàn)到的。希望通過(guò)這個(gè)問(wèn)題能讓你收獲頗深。下面是小編給大家?guī)?lái)的參考內(nèi)容，讓我們一起來(lái)看看吧！

先建好數(shù)據(jù)庫(kù)表，演示用的MySQL表，建表語(yǔ)句：

CREATE TABLE `emp` (  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主鍵',  `empno` int(11) DEFAULT NULL COMMENT '雇員工號(hào)',  `ename` varchar(255) DEFAULT NULL COMMENT '雇員姓名',  `job` varchar(255) DEFAULT NULL COMMENT '工作',  `mgr` varchar(255) DEFAULT NULL COMMENT '經(jīng)理的工號(hào)',  `hiredate` date DEFAULT NULL COMMENT '雇用日期',  `sal` double DEFAULT NULL COMMENT '工資',  `comm` double DEFAULT NULL COMMENT '津貼',  `deptno` int(11) DEFAULT NULL COMMENT '所屬部門(mén)號(hào)',
  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 '部門(mén)號(hào)',  `dname` varchar(255) DEFAULT NULL COMMENT '部門(mén)名稱',  `loc` varchar(255) DEFAULT NULL COMMENT '地址',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='部門(mén)表';CREATE TABLE `salgrade` (  `id` int(11) NOT NULL COMMENT '主鍵',  `grade` varchar(255) DEFAULT NULL COMMENT '等級(jí)',  `lowsal` varchar(255) DEFAULT NULL COMMENT '最低工資',  `hisal` varchar(255) DEFAULT NULL COMMENT '最高工資',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='工資等級(jí)表';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='獎(jiǎng)金表';復(fù)制代碼

后續(xù)執(zhí)行計(jì)劃，查詢優(yōu)化，索引優(yōu)化等等知識(shí)的演練，基于以上幾個(gè)表來(lái)操作。

MySQL執(zhí)行計(jì)劃

要進(jìn)行SQL調(diào)優(yōu)，你得知道要調(diào)優(yōu)的SQL語(yǔ)句是怎么執(zhí)行的，查看SQL語(yǔ)句的具體執(zhí)行過(guò)程，以加快SQL語(yǔ)句的執(zhí)行效率。

可以使用explain + SQL語(yǔ)句來(lái)模擬優(yōu)化器執(zhí)行SQL查詢語(yǔ)句，從而知道MySQL是如何處理SQL語(yǔ)句的。

關(guān)于explain可以看看官網(wǎng)介紹。

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  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+復(fù)制代碼

字段id，select_type等字段的解釋?zhuān)?/p>

id

select查詢的序列號(hào)，包含一組數(shù)字，表示查詢中執(zhí)行select子句或者操作表的順序。

id號(hào)分為三類(lèi)：

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

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) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+復(fù)制代碼

這個(gè)查詢，用explain執(zhí)行一下，id序號(hào)都是1，那么MySQL的執(zhí)行順序就是從上到下執(zhí)行的。

• 如果id不同，如果是子查詢，id的序號(hào)會(huì)遞增，id值越大優(yōu)先級(jí)越高，越先被執(zhí)行

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                                        |
+----+--------------+-------------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+復(fù)制代碼

這個(gè)例子的執(zhí)行順序是先執(zhí)行id為2的，然后執(zhí)行id為1的。

• id相同和不同的，同時(shí)存在：相同的可以認(rèn)為是一組，從上往下順序執(zhí)行，在所有組中，id值越大，優(yōu)先級(jí)越高，越先執(zhí)行

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

select_type

主要用來(lái)分辨查詢的類(lèi)型，是普通查詢還是聯(lián)合查詢還是子查詢。

• SIMPLE 簡(jiǎn)單的查詢，不包含子查詢和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  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+復(fù)制代碼

• primary 查詢中若包含任何復(fù)雜的子查詢，最外層查詢則被標(biāo)記為Primary
• union 若第二個(gè)select出現(xiàn)在union之后，則被標(biāo)記為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 |
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+復(fù)制代碼

這條語(yǔ)句的select_type包含了primary和union

• dependent union 跟union類(lèi)似，此處的depentent表示union或union all聯(lián)合而成的結(jié)果會(huì)受外部表影響
• union result 從union表獲取結(jié)果的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 |
+----+--------------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+復(fù)制代碼

這條SQL執(zhí)行包含了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        |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+復(fù)制代碼

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

例如：

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 |
+----+----------------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+復(fù)制代碼

• uncacheable union 表示union的查詢結(jié)果不能被緩存

table

對(duì)應(yīng)行正在訪問(wèn)哪一個(gè)表，表名或者別名，可能是臨時(shí)表或者union合并結(jié)果集。

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

type

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

訪問(wèn)的類(lèi)型有很多，效率從最好到最壞依次是：

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

一般情況下，得保證查詢至少達(dá)到range級(jí)別，最好能達(dá)到ref

• all 全表掃描，一般情況下出現(xiàn)這樣的sql語(yǔ)句而且數(shù)據(jù)量比較大的話那么就需要進(jìn)行優(yōu)化

通常，可以通過(guò)添加索引來(lái)避免ALL

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

官網(wǎng)上舉例如下：

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 利用索引來(lái)關(guān)聯(lián)子查詢，不再掃描全表

value IN (SELECT key_column FROM single_table WHERE some_expr)

• unique_subquery 該連接類(lèi)型類(lèi)似與index_subquery，使用的是唯一索引

value IN (SELECT primary_key FROM single_table WHERE some_expr)

• index_merge 在查詢過(guò)程中需要多個(gè)索引組合使用
• ref_or_null 對(duì)于某個(gè)字段既需要關(guān)聯(lián)條件，也需要null值的情況下，查詢優(yōu)化器會(huì)選擇這種訪問(wèn)方式

SELECT * FROM ref_table

WHERE key_column=expr OR key_column IS NULL;

• fulltext 使用FULLTEXT索引執(zhí)行join
• ref 使用了非唯一性索引進(jìn)行數(shù)據(jù)的查找

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 使用唯一性索引進(jìn)行數(shù)據(jù)查找

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 這個(gè)表至多有一個(gè)匹配行

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  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+復(fù)制代碼

• system 表只有一行記錄（等于系統(tǒng)表），這是const類(lèi)型的特例，平時(shí)不會(huì)出現(xiàn)

possible_keys

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

key

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

key_len

表示索引中使用的字節(jié)數(shù)，可以通過(guò)key_len計(jì)算查詢中使用的索引長(zhǎng)度，在不損失精度的情況下長(zhǎng)度越短越好

ref

顯示索引的哪一列被使用了，如果可能的話，是一個(gè)常數(shù)

rows

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

extra

包含額外的信息

• using filesort 說(shuō)明mysql無(wú)法利用索引進(jìn)行排序，只能利用排序算法進(jìn)行排序，會(huì)消耗額外的位置
• using temporary 建立臨時(shí)表來(lái)保存中間結(jié)果，查詢完成之后把臨時(shí)表刪除
• using index 這個(gè)表示當(dāng)前的查詢是覆蓋索引的，直接從索引中讀取數(shù)據(jù)，而不用訪問(wèn)數(shù)據(jù)表。如果同時(shí)出現(xiàn)using where 表明索引被用來(lái)執(zhí)行索引鍵值的查找，如果沒(méi)有，表示索引被用來(lái)讀取數(shù)據(jù)，而不是真的查找
• using where 使用where進(jìn)行條件過(guò)濾
• using join buffer 使用連接緩存
• impossible where where語(yǔ)句的結(jié)果總是false

MySQL索引基本知識(shí)

想要了解索引的優(yōu)化方式，必須要對(duì)索引的底層原理有所了解。

索引的優(yōu)點(diǎn)

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

索引的用處

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

索引的分類(lèi)

MySQL索引數(shù)據(jù)結(jié)構(gòu)推演

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

如果沒(méi)有索引，MySQL必須從第一行開(kāi)始，然后通讀整個(gè)表以找到相關(guān)的行。

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

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

幾種可能的存儲(chǔ)結(jié)構(gòu)

hash

hash表的索引格式

hash表存儲(chǔ)數(shù)據(jù)的缺點(diǎn)：

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

事實(shí)上，MySQL存儲(chǔ)引擎是memory時(shí)，索引數(shù)據(jù)結(jié)構(gòu)采用的就是hash表。

二叉樹(shù)

二叉樹(shù)的結(jié)構(gòu)是這樣的：

二叉樹(shù)會(huì)因?yàn)闃?shù)的深度而造成數(shù)據(jù)傾斜，如果樹(shù)的深度過(guò)深，會(huì)造成io次數(shù)變多，影響數(shù)據(jù)讀取的效率。

AVL樹(shù) 需要旋轉(zhuǎn)，看圖例：

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

二叉樹(shù)、AVL樹(shù)、紅黑樹(shù) 都會(huì)因?yàn)闃?shù)的深度過(guò)深而造成io次數(shù)變多，影響數(shù)據(jù)讀取的效率。

再來(lái)看一下 B樹(shù)

B樹(shù)特點(diǎn)：

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

圖例說(shuō)明：

每個(gè)節(jié)點(diǎn)占用一個(gè)磁盤(pán)塊，一個(gè)節(jié)點(diǎn)上有兩個(gè)升序排序的關(guān)鍵字和三個(gè)指向子樹(shù)根節(jié)點(diǎn)的指針，指針存儲(chǔ)的是子節(jié)點(diǎn)所在磁盤(pán)塊的地址。

兩個(gè)關(guān)鍵詞劃分成的三個(gè)范圍域?qū)?yīng)三個(gè)指針指向的子樹(shù)的數(shù)據(jù)的范圍域。

以根節(jié)點(diǎn)為例，關(guān)鍵字為 16 和 34，P1 指針指向的子樹(shù)的數(shù)據(jù)范圍為小于 16，P2 指針指向的子樹(shù)的數(shù)據(jù)范圍為 16~34，P3 指針指向的子樹(shù)的數(shù)據(jù)范圍為大于 34。

查找關(guān)鍵字過(guò)程：

1、根據(jù)根節(jié)點(diǎn)找到磁盤(pán)塊 1，讀入內(nèi)存?！敬疟P(pán) I/O 操作第 1 次】

2、比較關(guān)鍵字 28 在區(qū)間（16,34），找到磁盤(pán)塊 1 的指針 P2。

3、根據(jù) P2 指針找到磁盤(pán)塊 3，讀入內(nèi)存?！敬疟P(pán) I/O 操作第 2 次】

4、比較關(guān)鍵字 28 在區(qū)間（25,31），找到磁盤(pán)塊 3 的指針 P2。

5、根據(jù) P2 指針找到磁盤(pán)塊 8，讀入內(nèi)存?！敬疟P(pán) I/O 操作第 3 次】

6、在磁盤(pán)塊 8 中的關(guān)鍵字列表中找到關(guān)鍵字 28。

由此，我們可以得知B樹(shù)存儲(chǔ)的缺點(diǎn)：

• 每個(gè)節(jié)點(diǎn)都有key，同時(shí)也包含data，而每個(gè)頁(yè)存儲(chǔ)空間是有限的，如果data比較大的話會(huì)導(dǎo)致每個(gè)節(jié)點(diǎn)存儲(chǔ)的key數(shù)量變小
• 當(dāng)存儲(chǔ)的數(shù)據(jù)量很大的時(shí)候會(huì)導(dǎo)致深度較大，增大查詢時(shí)磁盤(pán)io次數(shù)，進(jìn)而影響查詢性能

那么MySQL索引數(shù)據(jù)結(jié)構(gòu)是什么呢

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

不要誤會(huì)，其實(shí)MySQL索引的存儲(chǔ)結(jié)構(gòu)是B+樹(shù)，上面我們一頓分析，知道B樹(shù)是不合適的。

mysql索引數(shù)據(jù)結(jié)構(gòu)---B+Tree

B+Tree是在BTree的基礎(chǔ)之上做的一種優(yōu)化，變化如下：

1、B+Tree每個(gè)節(jié)點(diǎn)可以包含更多的節(jié)點(diǎn)，這個(gè)做的原因有兩個(gè)，第一個(gè)原因是為了降低樹(shù)的高度，第二個(gè)原因是將數(shù)據(jù)范圍變?yōu)槎鄠€(gè)區(qū)間，區(qū)間越多，數(shù)據(jù)檢索越快。

2、非葉子節(jié)點(diǎn)存儲(chǔ)key，葉子節(jié)點(diǎn)存儲(chǔ)key和數(shù)據(jù)。

3、葉子節(jié)點(diǎn)兩兩指針相互連接（符合磁盤(pán)的預(yù)讀特性），順序查詢性能更高。

B+樹(shù)存儲(chǔ)查找示意圖：

注意：

在B+Tree上有兩個(gè)頭指針，一個(gè)指向根節(jié)點(diǎn)，另一個(gè)指向關(guān)鍵字最小的葉子節(jié)點(diǎn)，而且所有葉子節(jié)點(diǎn)（即數(shù)據(jù)節(jié)點(diǎn)）之間是一種鏈?zhǔn)江h(huán)結(jié)構(gòu)。

因此可以對(duì) B+Tree 進(jìn)行兩種查找運(yùn)算：一種是對(duì)于主鍵的范圍查找和分頁(yè)查找，另一種是從根節(jié)點(diǎn)開(kāi)始，進(jìn)行隨機(jī)查找。

由于B+樹(shù)葉子結(jié)點(diǎn)只存放data，根節(jié)點(diǎn)只存放key，那么我們計(jì)算一下，即使只有3層B+樹(shù)，也能制成千萬(wàn)級(jí)別的數(shù)據(jù)。

你得知道的技（zhuang）術(shù)（b）名詞

假設(shè)有這樣一個(gè)表如下，其中id是主鍵：

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

回表

我們對(duì)普通列建普通索引，這時(shí)候我們來(lái)查：

select * from stu where name='Pony';復(fù)制代碼

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

這個(gè)最終會(huì)回到主鍵上來(lái)查找B+樹(shù)，這個(gè)就是回表。

覆蓋索引

如果是這個(gè)查詢：

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

就沒(méi)有回表了，因?yàn)橹苯诱业街麈Iid，返回就完了，不需要再找其他的了。

沒(méi)有回表就叫覆蓋索引。

最左匹配

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

select * from stu where name=? and age=?復(fù)制代碼

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

select * from stu where age=?復(fù)制代碼

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

加入我就要按age查，還要有索引來(lái)優(yōu)化呢？可以這樣做：

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

索引下推

可能也叫謂詞下推。。。

select t1.name,t2.name from t1 join t2 on t1.id=t2.id復(fù)制代碼

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

我們猜想一下，這個(gè)要么按這個(gè)方式執(zhí)行：

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

或者：

先把t1.name,t2.name找出來(lái)，再按照id關(guān)聯(lián)

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

當(dāng)使用了索引條件下推優(yōu)化技術(shù)后，可以通過(guò)索引中存儲(chǔ)的數(shù)據(jù)判斷當(dāng)前索引對(duì)應(yīng)的數(shù)據(jù)是否符合條件，只有符合條件的數(shù)據(jù)才將整行數(shù)據(jù)查詢出來(lái)。

小結(jié)

1. Explain 為了知道優(yōu)化SQL語(yǔ)句的執(zhí)行，需要查看SQL語(yǔ)句的具體執(zhí)行過(guò)程，以加快SQL語(yǔ)句的執(zhí)行效率。
2. 索引優(yōu)點(diǎn)及用處。
3. 索引采用的數(shù)據(jù)結(jié)構(gòu)是B+樹(shù)。
4. 回表，覆蓋索引，最左匹配和索引下推。

感謝各位的閱讀！看完上述內(nèi)容，你們對(duì)MySQL 執(zhí)行計(jì)劃explain與索引數(shù)據(jù)結(jié)構(gòu)推演過(guò)程是什么大概了解了嗎？希望文章內(nèi)容對(duì)大家有所幫助。如果想了解更多相關(guān)文章內(nèi)容，歡迎關(guān)注億速云行業(yè)資訊頻道。