B-Tree 索引
不同的存儲(chǔ)引擎也可能使用不同的存儲(chǔ)結(jié)構(gòu)，i如，NDB集群存儲(chǔ)引擎內(nèi)部實(shí)現(xiàn)使用了T-Tree結(jié)構(gòu)存儲(chǔ)這種索引，即使其名字是BTREE；InnoDB使用的是B+Tree。
B-Tree通常一位這所有的值都是按順序存儲(chǔ)的，并且每一個(gè)葉子頁(yè)道根的距離相同。下圖大致反應(yīng)了InnoDB索引是如何工作的。
為什么mysql索引要使用B+樹(shù)，而不是B樹(shù)，紅黑樹(shù)
看完上面的文章就可以理解為何B-Tree索引能夠快速訪問(wèn)數(shù)據(jù)了。因?yàn)榇鎯?chǔ)引擎不再需要進(jìn)行全表掃描獲取需要的數(shù)據(jù)，葉子節(jié)點(diǎn)包含了所有元素信息，每一個(gè)葉子節(jié)點(diǎn)指針都指向下一個(gè)節(jié)點(diǎn)，所以很適合查找范圍數(shù)據(jù)。
索引對(duì)多個(gè)值進(jìn)行排列的依據(jù)是CREATE TABLE 語(yǔ)句中定義索引時(shí)的順序。
那么，索引排序的規(guī)則就是按照 last_name ,first_name ,dob 的順序來(lái)的。
可以使用 B-Tree 索引的查詢類型
B-Tree索引適用于全鍵值、鍵值范圍或鍵前綴查找。
鍵前綴查找只是用于根據(jù)最左前綴查找。
舉個(gè)粒子：
CREATE TABLE People (
last_name VARCHAR ( 50 ) NOT NULL,
first_name VARCHAR ( 50 ) NOT NULL,
dob date NOT NULL,
gender enum ( 'm', 'f' ) NOT NULL,
KEY ( last_name, first_name, dob )
);
這個(gè)表的索引如下：
type結(jié)果
type結(jié)果值從好到壞依次是：
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
一般來(lái)說(shuō)，得保證查詢至少達(dá)到range級(jí)別，最好能達(dá)到ref，否則就可能會(huì)出現(xiàn)性能問(wèn)題。
possible_keys：sql所用到的索引
key：顯示MySQL實(shí)際決定使用的鍵（索引）。如果沒(méi)有選擇索引，鍵是NULL
（1）全值匹配
全值匹配指的是和索引中的所有列進(jìn)行匹配。
例如上面的People表的索引（last_name,first_name,dob）可以用于查找last_name='Cuba Allen',first_name='Chuang',dob='1996-01-01'的人。這就是使用了索引中的所有列進(jìn)行匹配，即全值匹配。
mysql> EXPLAIN select * from People where last_name = 'aaa' and first_name = 'bbb' and dob='2020-11-20' \G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: People
partitions: NULL
type: ref
possible_keys: last_name
key: last_name -----可以看到這個(gè)key就是我們定義的索引
key_len: 307
ref: const,const,const
rows: 1
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)
ERROR:
No query specified
（2）匹配最左前綴
可以只使用索引的第一個(gè)列進(jìn)行匹配。
例如可以用于查找last_name='aaa'的人，即用于查找姓為Zeng的人，這里只使用了索引的最左列進(jìn)行匹配，即匹配最左前綴。
mysql> EXPLAIN select * from People where last_name = 'aaa' \G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: People
partitions: NULL
type: ref
possible_keys: last_name
key: last_name ----使用了索引
key_len: 152
ref: const
rows: 3
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)
ERROR:
No query specified
（3）匹配列前綴
可以只匹配某一列的值的開(kāi)頭部分。
例如可以用于查找last_name LIKE ‘a(chǎn)%'的人，即用于查找所有以Z開(kāi)頭的姓的人，這里只使用了索引最左列的前綴進(jìn)行匹配，即匹配列前綴。
mysql> EXPLAIN select * from People where last_name = 'a%' \G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: People
partitions: NULL
type: ref
possible_keys: last_name
key: last_name ---使用了索引
key_len: 152
ref: const
rows: 1
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)
ERROR:
No query specified
（4）匹配范圍值
可以只適用索引的第一列查找符合某個(gè)范圍內(nèi)的數(shù)據(jù)。
例如可以用于查找last_name BETWEEN ‘a(chǎn)aa' AND ‘a(chǎn)aabbbccc'的人，即用于查找姓在aaa和aaabbbccc之間的人，這里只使用了索引最左列的前綴進(jìn)行范圍匹配，即匹配范圍值。
mysql> EXPLAIN select * from People where last_name BETWEEN 'aaa' and 'aaabbbccc'\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: People
partitions: NULL
type: range
possible_keys: last_name
key: last_name ---使用了索引
key_len: 152
ref: NULL
rows: 3
filtered: 100.00
Extra: Using index condition
1 row in set, 1 warning (0.00 sec)
ERROR:
No query specified
（5）精確匹配某一列并范圍匹配另外一列
可以使第一列全匹配，第二列范圍匹配。
例如可以用于查找last_name='aaa' AND first_name LIKE 'b%'的人，即用于查找姓是Zeng，名字以C開(kāi)頭的人，這里使用了索引的最左列精確匹配，第二列進(jìn)行范圍匹配。
mysql> EXPLAIN select * from People where last_name = 'aaa' and first_name like 'b%'\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: People
partitions: NULL
type: range
possible_keys: last_name
key: last_name ---使用了索引
key_len: 304
ref: NULL
rows: 1
filtered: 100.00
Extra: Using index condition
1 row in set, 1 warning (0.00 sec)
ERROR:
No query specified
（6）只訪問(wèn)索引的查詢
查詢只需訪問(wèn)索引，而無(wú)須訪問(wèn)數(shù)據(jù)行。
例如select last_name, first_name where last_name='aaa'; 這里只查詢索引所包含的last_name和first_name列，則無(wú)須讀取數(shù)據(jù)行。
mysql> explain select last_name,first_name,dob from People where last_name = 'aaa'
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: People
partitions: NULL
type: ref
possible_keys: last_name
key: last_name
key_len: 152
ref: const
rows: 1
filtered: 100.00
Extra: Using index
1 row in set, 1 warning (0.00 sec)
ERROR:
No query specified
B-Tree 的限制
（1）只能按照索引的最左列開(kāi)始查找。
例如People表中的索引無(wú)法用于查找first_name為'bbb'的人，也無(wú)法查找某個(gè)特定生日的人，因?yàn)檫@兩個(gè)列都不是最左數(shù)據(jù)列。
（2）只能按照索引最左列的最左前綴進(jìn)行匹配。
例如People表中的索引無(wú)法查找last_name LIKE ‘%b'的人，雖然last_name就是此索引的最左列，但MySQL索引無(wú)法查找以‘b'結(jié)尾的last_name的記錄。
（3）只能按照索引定義的順序從左到右進(jìn)行匹配，不能跳過(guò)索引中的列。
例如People表中的索引無(wú)法用于查找last_name='a' AND bod='1996-01-01'的人，因?yàn)镸ySQL無(wú)法跳過(guò)索引中的某一列而使用索引中最左列和排在末尾的列進(jìn)行組合。如果不指定索引中中間的列，則MySQL只能使用索引的最左列，即第一列。
（4）如果查詢中有某個(gè)列的范圍查詢，則其右邊所有列都無(wú)法使用索引優(yōu)化查找。
例如有這樣一個(gè)查詢：where last_name='a' AND first_name LIKE 'b%' AND dob='1996-01-01'; 這個(gè)查詢只能使用索引的前兩列，因?yàn)檫@里L(fēng)IKE是一個(gè)范圍條件，則first_name后面的索引列都將失效。（優(yōu)化點(diǎn)：盡量不要在索引列中使用LIKE等范圍條件，改用多個(gè)等于條件來(lái)替代，保證后面的索引列能生效。）
以上就是淺析MysQL B-Tree 索引的詳細(xì)內(nèi)容，更多關(guān)于MysQL B-Tree 索引的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！
您可能感興趣的文章:- MySQL全文索引、聯(lián)合索引、like查詢、json查詢速度哪個(gè)快
- MySQL全文索引實(shí)現(xiàn)簡(jiǎn)單版搜索引擎實(shí)例代碼
- MySQL創(chuàng)建全文索引分享
- MySQL全文索引應(yīng)用簡(jiǎn)明教程
- 基于mysql全文索引的深入理解
- MySQL索引失效的幾種情況詳析
- Mysql普通索引與唯一索引的選擇詳析
- MySQL8.0中的降序索引
- MySQL 8.0 之索引跳躍掃描(Index Skip Scan)
- 詳解MySQL 8.0 之不可見(jiàn)索引
- Mysql索引常見(jiàn)問(wèn)題匯總
- MySql索引提高查詢速度常用方法代碼示例
- MySQL 全文索引的原理與缺陷
