1、B+樹索引

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

B+樹索引就是以下這幾種。

1.1、聚集索引/聚簇索引

定義主鍵時，主鍵上自動追加的索引就是聚集索引，也稱聚簇索引。

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

如果我們創(chuàng)建表時沒有定義主鍵，Mysql也會自動創(chuàng)建一個主鍵和對應(yīng)的索引，主鍵名是rowId

1.2、輔助索引/二級索引

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

通過二級索引找到主鍵，再從主鍵得到一整行數(shù)據(jù)的行為叫做回表。

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

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

聚合索引可以說是二級索引的一種特殊情況。一般二級索引都是只對一個非主鍵的列添加索引，而聚合索引則是一次性對多個列同時添加索引。

一般的二級索引用這樣的語句創(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);

對于復(fù)合索引，Mysql會也會創(chuàng)建一個B+樹，但因為是多個列的索引，所以B+樹的排序規(guī)則比較特殊，是遵循最左原則。下面會講到什么是最左原則。

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

1.3.2、最左原則

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

比如定義語句如下：

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

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

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

比如說，我們已經(jīng)定義了上面的三個列組成的復(fù)合索引，那查詢或者排序的時候盡量先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')

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

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

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

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

show index from t_order;

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

2、哈希索引

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

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

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

使用語句：

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ù)這個來決定是否使用哈希索引
0.00 hash searches/s, 0.00 non-hash searches/s
---

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

3.1、 單列索引的策略

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

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

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

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

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

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

因為索引是需要起到篩選的作用。比如我們有個where條件是where id = 1，如果數(shù)據(jù)重復(fù)性較高，那可能根據(jù)索引會返回100條數(shù)據(jù)，然后我們在根據(jù)其他where條件在100條數(shù)據(jù)中再篩選。

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

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

我們可以用下面的語句得到某個列的離散性程度。

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

3.1.3、前綴索引

前綴索引和后綴索引：

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

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

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

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

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

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

來比較一下各個位的子字符串的離散性。

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開始就比較高，那我們可以根據(jù)實際情況，建立13~15位的前綴索引。

建立前綴索引SQL語句：

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

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

這個理由很簡單，不解釋了。

3.2、 多列索引的策略

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

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

3.2.2、三星索引

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

三條策略分別是

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

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

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

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

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

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

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

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

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

三星索引實例：

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)建以上的索引，那么對于下面的查詢語句，這個索引就是三星索引。

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+樹的葉子節(jié)點中查詢，讓我們的查詢變窄了很多，并且這部分的數(shù)據(jù)是連續(xù)的，因為B+樹是先根據(jù)city排序，再根據(jù)lname查詢。

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

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

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