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

創(chuàng)新互聯(lián)建站堅信：善待客戶，將會成為終身客戶。我們能堅持多年，是因為我們一直可值得信賴。我們從不忽悠初訪客戶，我們用心做好本職工作，不忘初心，方得始終。十載網(wǎng)站建設(shè)經(jīng)驗創(chuàng)新互聯(lián)建站是成都老牌網(wǎng)站營銷服務(wù)商,為您提供成都網(wǎng)站制作、網(wǎng)站建設(shè)、網(wǎng)站設(shè)計、html5、網(wǎng)站制作、品牌網(wǎng)站建設(shè)、成都小程序開發(fā)服務(wù),給眾多知名企業(yè)提供過好品質(zhì)的建站服務(wù)。

先建好數(shù)據(jù)庫表，演示用的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 '經(jīng)理的工號',  `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='獎金表';復(fù)制代碼

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

MySQL執(zhí)行計劃

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

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

關(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等字段的解釋：

Column	Meaning
id	The SELECT identifier（該SELECT標(biāo)識符）
select_type	The SELECT type（ 該SELECT類型）
table	The table for the output row（輸出該行的表名）
partitions	The matching partitions（匹配的分區(qū)）
type	The join type（連接類型）
possible_keys	The possible indexes to choose（可能的索引選擇）
key	The index actually chosen（實(shí)際選擇的索引）
key_len	The length of the chosen key（所選鍵的長度）
ref	The columns compared to the index（與索引比較的列）
rows	Estimate of rows to be examined（檢查的預(yù)估行數(shù)）
filtered	Percentage of rows filtered by table condition（按表條件過濾的行百分比）
extra	Additional information（附加信息）

id

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

id號分為三類：

• 如果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ù)制代碼

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

• 如果id不同，如果是子查詢，id的序號會遞增，id值越大優(yōu)先級越高，越先被執(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ù)制代碼

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

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

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

select_type

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

select_type Value	JSON Name	Meaning
SIMPLE	None	Simple SELECT (not using UNION or subqueries)
PRIMARY	None	Outermost SELECT
UNION	None	Second or later SELECT statement in a UNION
DEPENDENT UNION	dependent (true)	Second or later SELECT statement in a UNION, dependent on outer query
UNION RESULT	union_result	Result of a UNION.
SUBQUERY	None	First SELECT in subquery
DEPENDENT SUBQUERY	dependent (true)	First SELECT in subquery, dependent on outer query
DERIVED	None	Derived table
MATERIALIZED	materialized_from_subquery	Materialized subquery
UNCACHEABLE SUBQUERY	cacheable (false)	A subquery for which the result cannot be cached and must be re-evaluated for each row of the outer query
UNCACHEABLE UNION	cacheable (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  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+復(fù)制代碼

• primary 查詢中若包含任何復(fù)雜的子查詢，最外層查詢則被標(biāo)記為Primary
• union 若第二個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ù)制代碼

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

• dependent union 跟union類似，此處的depentent表示union或union all聯(lián)合而成的結(jié)果會受外部表影響
• 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

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

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

type

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

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

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

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

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

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

• index 全索引掃描這個比all的效率要好，主要有兩種情況：
  • 一種是當(dāng)前的查詢時覆蓋索引，即我們需要的數(shù)據(jù)在索引中就可以索取
  • 一是使用了索引進(jìn)行排序，這樣就避免數(shù)據(jù)的重排序
• range 表示利用索引查詢的時候限制了范圍，在指定范圍內(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 利用索引來關(guān)聯(lián)子查詢，不再掃描全表

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 對于某個字段既需要關(guān)聯(lián)條件，也需要null值的情況下，查詢優(yōu)化器會選擇這種訪問方式

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

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類型的特例，平時不會出現(xiàn)

possible_keys

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

key

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

key_len

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

ref

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

rows

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

extra

包含額外的信息

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

MySQL索引基本知識

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

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

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

索引的用處

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

索引的分類

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

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

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

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

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

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

hash

hash表的索引格式

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

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

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

二叉樹

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

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

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

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

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

再來看一下 B樹

B樹特點(diǎn)：

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

圖例說明：

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

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

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

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

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

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

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

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

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

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

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

• 每個節(jié)點(diǎn)都有key，同時也包含data，而每個頁存儲空間是有限的，如果data比較大的話會導(dǎo)致每個節(jié)點(diǎn)存儲的key數(shù)量變小
• 當(dāng)存儲的數(shù)據(jù)量很大的時候會導(dǎo)致深度較大，增大查詢時磁盤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

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

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

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

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

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

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

B+樹存儲查找示意圖：

注意：

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

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

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

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

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

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

回表

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

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

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

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

覆蓋索引

如果是這個查詢：

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

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

沒有回表就叫覆蓋索引。

最左匹配

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

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

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

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

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

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

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

索引下推

可能也叫謂詞下推。。。

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

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

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

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

或者：

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

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

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

小結(jié)

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

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

網(wǎng)站題目：MySQL執(zhí)行計劃explain與索引數(shù)據(jù)結(jié)構(gòu)推演過程是什么
文章網(wǎng)址：http://jinyejixie.com/article24/ijjgce.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供營銷型網(wǎng)站建設(shè)、網(wǎng)頁設(shè)計公司、品牌網(wǎng)站制作、動態(tài)網(wǎng)站、建站公司、搜索引擎優(yōu)化

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)