1、B+樹(shù)索引

顧名思義，結(jié)構(gòu)是B+樹(shù)的索引就是B+樹(shù)索引，一般情況下，InnoDb引擎中創(chuàng)建的常規(guī)索引都是B+的結(jié)構(gòu)。

B+樹(shù)索引就是以下這幾種。

1.1、聚集索引/聚簇索引

定義主鍵時(shí)，主鍵上自動(dòng)追加的索引就是聚集索引，也稱聚簇索引。

Mysql用組建構(gòu)建一顆B+樹(shù)，如下圖所示，每個(gè)葉子節(jié)點(diǎn)對(duì)應(yīng)一個(gè)主鍵，以及和這個(gè)主鍵對(duì)應(yīng)的其它數(shù)據(jù)。

如果我們創(chuàng)建表時(shí)沒(méi)有定義主鍵，Mysql也會(huì)自動(dòng)創(chuàng)建一個(gè)主鍵和對(duì)應(yīng)的索引，主鍵名是rowId

1.2、輔助索引/二級(jí)索引

如果我們對(duì)非主鍵的列column創(chuàng)建索引，那這個(gè)索引就稱為輔助索引/二級(jí)索引。同樣的，Mysql會(huì)為這個(gè)索引創(chuàng)建一個(gè)B+樹(shù)，樹(shù)的葉子節(jié)點(diǎn)除了包含這個(gè)列column的值以外，就只包含這個(gè)列所在行的主鍵值，這樣通過(guò)列的索引就可以查到葉子節(jié)點(diǎn)，然后葉子節(jié)點(diǎn)中的主鍵信息再?gòu)闹麈I的索引中搜索，最終得到一整行的數(shù)據(jù)。

通過(guò)二級(jí)索引找到主鍵，再?gòu)闹麈I得到一整行數(shù)據(jù)的行為叫做回表。

1.3、聯(lián)合索引/復(fù)合索引

1.3.1、什么是復(fù)合索引

聚合索引可以說(shuō)是二級(jí)索引的一種特殊情況。一般二級(jí)索引都是只對(duì)一個(gè)非主鍵的列添加索引，而聚合索引則是一次性對(duì)多個(gè)列同時(shí)添加索引。

一般的二級(jí)索引用這樣的語(yǔ)句創(chuàng)建：

CREATE INDEX  order_name_index on t_order(order_name);

復(fù)合索引則是這樣創(chuàng)建：

CREATE INDEX  order_name_and_order_type_index on t_order(order_name, order_type);

對(duì)于復(fù)合索引，Mysql會(huì)也會(huì)創(chuàng)建一個(gè)B+樹(shù)，但因?yàn)槭嵌鄠€(gè)列的索引，所以B+樹(shù)的排序規(guī)則比較特殊，是遵循最左原則。下面會(huì)講到什么是最左原則。

之后葉子節(jié)點(diǎn)包含的信息有多個(gè)，一個(gè)是作為索引的各個(gè)列的值，另一個(gè)就是主鍵的值。

1.3.2、最左原則

所謂的最左原則是，B+樹(shù)的排序規(guī)則是根據(jù)索引定義時(shí)，定義的語(yǔ)句中的列名從左到右進(jìn)行排序。

比如定義語(yǔ)句如下：

CREATE INDEX  joint_index on t_order(order_name, order_type, submit_time);

那排序規(guī)則是先排order_name，如果order_name相同，再排order_type，最后排submit_time。

那當(dāng)我們查詢時(shí)，根據(jù)定義時(shí)列的順序從左至右，where子句或者order by等子句應(yīng)該盡量先從order_name開(kāi)始，然后以此類推。

比如說(shuō)，我們已經(jīng)定義了上面的三個(gè)列組成的復(fù)合索引，那查詢或者排序的時(shí)候盡量先order_name，再order_type，最后submit_time。

select * from t_order where order_name = 'order1'
and order_type = 1
and submit_time = str_to_date('2022-08-02 00:52:26', '%Y-%m-%d %T')

原因很簡(jiǎn)單，因?yàn)槁?lián)合索引的排序規(guī)則是先排order_name，如果order_name相同，再排order_type，最后排submit_time。所以只有查詢排序時(shí)也遵循這個(gè)規(guī)則，我們才能用上索引。

如果我們不完全遵守最左原則，比如查詢排序只排兩個(gè)列，忽略中間那個(gè)order by order_name, submit_time。那這個(gè)時(shí)候Mysql會(huì)有智能化的處理，他會(huì)自己判斷是用索引快還是不用索引快。

1.3.3、聯(lián)合索引的查詢優(yōu)化

盡量使用到組成聯(lián)合索引的列，并且保證順序?？梢酝ㄟ^(guò)查詢索引查看列的順序。查看sql_in_index

show index from t_order;

查詢返回的字段盡量就只返回組成聯(lián)合索引的列和主鍵，不要返回其它的列，以免造成回表。
這應(yīng)該容易理解，因?yàn)槁?lián)合索引的B+樹(shù)的葉子節(jié)點(diǎn)就只包含主鍵和組成聯(lián)合索引的列的值，如果返回的字段就這幾列，那在一個(gè)B+樹(shù)種查詢就完事了。如果還要返回其它的列的話，就又要去主鍵的索引中查找，有回表操作。

2、哈希索引

一般數(shù)據(jù)庫(kù)都會(huì)用B+樹(shù)索引查詢數(shù)據(jù)，但是當(dāng)數(shù)據(jù)庫(kù)使用一段時(shí)間后，InnoDB 會(huì)記錄一些使用頻率較高的熱數(shù)據(jù)，然后為這些熱數(shù)據(jù)建立哈希結(jié)構(gòu)的索引，這就是哈希索引的應(yīng)用場(chǎng)景。

這個(gè)索引在Mysql 5.7開(kāi)始默認(rèn)開(kāi)啟。

2.1、查看哈希索引的命中率等信息

使用語(yǔ)句：

show engine innodb status;

其中的status有很多信息，其中就包括哈希索引的情況。我們把信息復(fù)制到編輯器中查看。其中的這一段就是哈希索引的信息。

-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
 insert 0, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 5 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 1 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 1 buffer(s)
Hash table size 34679, node heap has 1 buffer(s)
-- 哈希索引的命中率，可根據(jù)這個(gè)來(lái)決定是否使用哈希索引
0.00 hash searches/s, 0.00 non-hash searches/s
---

3、索引的創(chuàng)建策略

3.1、 單列索引的策略

3.1.1、列的類型占用的空間越小，越適合作為索引

因?yàn)锽+樹(shù)也是占用空間的，所以在固定空間中，如果列的類型占用的空間越小，那我們一次就能讀取更多的B+樹(shù)節(jié)點(diǎn)，這樣自然就加快了效率。

3.1.2、根據(jù)列的值的離散性

離散性是指數(shù)據(jù)的值重復(fù)的程度高不高，假如有N條數(shù)據(jù)的話，那離散性就可以用數(shù)值表示，范圍是1/N 到 1。

比如說(shuō)某個(gè)列在數(shù)據(jù)庫(kù)中有下面幾條數(shù)據(jù)（1, 2, 3, 4, 5, 5, 3），其中5和3都有重復(fù)，去重后應(yīng)該是（1, 2, 3, 4, 5）。我們將去重后的條數(shù)除以總條數(shù)就得到離散性。這里是5/7。那么這個(gè)數(shù)值越小，代表這個(gè)列的重復(fù)數(shù)據(jù)越多；值越大代表重復(fù)數(shù)據(jù)越少。

如果一個(gè)列的數(shù)據(jù)的重復(fù)性越低，那么這個(gè)列就越適合加索引。

因?yàn)樗饕切枰鸬胶Y選的作用。比如我們有個(gè)where條件是where id = 1，如果數(shù)據(jù)重復(fù)性較高，那可能根據(jù)索引會(huì)返回100條數(shù)據(jù)，然后我們?cè)诟鶕?jù)其他where條件在100條數(shù)據(jù)中再篩選。

如果數(shù)據(jù)重復(fù)性較低，那可能就只返回1條數(shù)據(jù)，那之后的運(yùn)算量明顯小得多。

所以一個(gè)列的數(shù)據(jù)離散性越高，那這個(gè)列越適合添加索引。

我們可以用下面的語(yǔ)句得到某個(gè)列的離散性程度。

select count(distinct id)/count(*) form t_table;

3.1.3、前綴索引

前綴索引和后綴索引：

有些列的值比較長(zhǎng)，比如一些備注日志信息也會(huì)記錄在數(shù)據(jù)庫(kù)當(dāng)中，這類信息的長(zhǎng)度往往比較長(zhǎng)，如果我們需要對(duì)這類列加索引，那索引并不是索引字符串的全部長(zhǎng)度。這時(shí)候我們就可以建立前綴索引，即對(duì)字符串的前面幾位建立索引。

所以前綴索引就是建立范圍更小索引，選擇一個(gè)好前綴位數(shù)就能有一個(gè)更好的查詢效率。

不過(guò)有一些缺點(diǎn)，就是這類索引無(wú)法應(yīng)用到order by和group語(yǔ)句上。

Mysql沒(méi)有后綴索引，如果非要實(shí)現(xiàn)后綴索引，那在數(shù)據(jù)存儲(chǔ)時(shí)我們應(yīng)該將數(shù)據(jù)反轉(zhuǎn)，這樣就能用前綴索引達(dá)到后綴索引的效果。后綴索引的一個(gè)經(jīng)典應(yīng)用就是郵箱，快速查詢某種類型的郵箱。

選擇前綴索引的位數(shù)：

這里的邏輯和列的離散性類似，我們需要看看字符串的前面幾位的子字符串的離散性如何。比如對(duì)于下面的表，內(nèi)容是電影票的相關(guān)信息，我們需要對(duì)order_note建立前綴索引。

來(lái)比較一下各個(gè)位的子字符串的離散性。

SELECT COUNT(DISTINCT LEFT(order_note,3))/COUNT(*) AS sel3,
COUNT(DISTINCT LEFT(order_note,4))/COUNT(*)AS sel4,
COUNT(DISTINCT LEFT(order_note,5))/COUNT(*) AS sel5,
COUNT(DISTINCT LEFT(order_note, 6))/COUNT(*) As sel6,
COUNT(DISTINCT LEFT(order_note, 7))/COUNT(*) As sel7,
COUNT(DISTINCT LEFT(order_note, 8))/COUNT(*) As sel8,
COUNT(DISTINCT LEFT(order_note, 9))/COUNT(*) As sel9,
COUNT(DISTINCT LEFT(order_note, 10))/COUNT(*) As sel10,
COUNT(DISTINCT LEFT(order_note, 11))/COUNT(*) As sel11,
COUNT(DISTINCT LEFT(order_note, 12))/COUNT(*) As sel12,
COUNT(DISTINCT LEFT(order_note, 13))/COUNT(*) As sel13,
COUNT(DISTINCT LEFT(order_note, 14))/COUNT(*) As sel14,
COUNT(DISTINCT LEFT(order_note, 15))/COUNT(*) As sel15,
COUNT(DISTINCT order_note)/COUNT(*) As total
FROM order_exp;

可以看出，前面幾位的子字符串的離散程度較低，后面sel13開(kāi)始就比較高，那我們可以根據(jù)實(shí)際情況，建立13~15位的前綴索引。

建立前綴索引SQL語(yǔ)句：

alter table order_exp add key(order_note(13));

3.1.2、只為搜索、排序和分組的列建索引

這個(gè)理由很簡(jiǎn)單，不解釋了。

3.2、 多列索引的策略

3.2.1、離散性最高的列放前面

原因很簡(jiǎn)單，查詢時(shí)根據(jù)定義復(fù)合索引時(shí)的列的順序來(lái)查詢的，離散性高的列放在前面的話，就能更早的將更多的數(shù)據(jù)排除在外。

3.2.2、三星索引

三星索引是一種策略。有三種條件，滿足一條則索引獲得一顆星，三顆星則是很好的索引。

三條策略分別是

索引將相關(guān)記錄放在一起。

意思是查詢需要的數(shù)據(jù)在索引樹(shù)的葉子節(jié)點(diǎn)中連續(xù)或者足夠靠近。舉個(gè)例子，下面是某個(gè)索引的B+樹(shù)。如果查詢需要的數(shù)據(jù)只覆蓋葉子節(jié)點(diǎn)的前兩個(gè)片，即0000 ~ a。這很明顯，后面的片我們就沒(méi)必要再去查詢了，這無(wú)疑增加了效率。如果查詢需要的數(shù)據(jù)每個(gè)片都分散一點(diǎn)，那么查詢的次數(shù)就增加了很多。

所以查詢需要的數(shù)據(jù)在葉子節(jié)點(diǎn)上越連續(xù)，越窄就越好。

索引中的數(shù)據(jù)順序與查找中的數(shù)據(jù)排序一致。

這容易理解，講解聯(lián)合索引中說(shuō)過(guò)，B+樹(shù)的排序順序和索引中的數(shù)據(jù)一樣，所以查詢時(shí)的where的數(shù)據(jù)順序越貼近索引中的順序，就越能更好地利用B+樹(shù)。

索引的列包含查詢中的所有列。

這個(gè)可以避免回文操作，不多解釋。

三星索引的權(quán)重：

一般來(lái)說(shuō)第三個(gè)策略權(quán)重占到50%，之后是第一個(gè)策略27%， 第二個(gè)策略23%。

三星索引實(shí)例：

CREATE TABLE customer (
	cno INT,
	lname VARCHAR (10),
	fname VARCHAR (10),
	sex INT,
	weight INT,
	city VARCHAR (10)
);

CREATE INDEX idx_cust ON customer (city, lname, fname, cno);

我們創(chuàng)建以上的索引，那么對(duì)于下面的查詢語(yǔ)句，這個(gè)索引就是三星索引。

select cno,fname from customer where lname='xx' and city ='yy' order by fname;

首先，查詢條件中有lname=’xx’ and city =’yy’，這條件讓我們這需要在lname=’xx’ and city =’yy’的那一片B+樹(shù)的葉子節(jié)點(diǎn)中查詢，讓我們的查詢變窄了很多，并且這部分的數(shù)據(jù)是連續(xù)的，因?yàn)锽+樹(shù)是先根據(jù)city排序，再根據(jù)lname查詢。

另外，因?yàn)橐呀?jīng)鎖定lname=’xx’ and city =’yy’，所以這部分的數(shù)據(jù)是根據(jù)fname和cno排序。查詢語(yǔ)句正好是根據(jù)`fname```排序，所以第二點(diǎn)也滿足。

最后是查詢的結(jié)果都包含正在索引中，不會(huì)有回文，第三點(diǎn)也滿足，所以這個(gè)索引是三星索引。

到此這篇關(guān)于MySql索引和索引創(chuàng)建策略的文章就介紹到這了,更多相關(guān)MySql索引創(chuàng)建內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論