MySql索引和索引建立策略

1、B+樹索引

顧名思義，結構是B+樹的索引就是B+樹索引，一般情況下，InnoDb引擎中建立的常規索引都是B+的結構。

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

1.1、聚集索引/聚簇索引

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

Mysql用組建構建一顆B+樹，如下圖所示，每個葉子節點對應一個主鍵，以及和這個主鍵對應的其它資料。

如果我們建立表時沒有定義主鍵，Mysql也會自動建立一個主鍵和對應的索引，主鍵名是rowId

1.2、輔助索引/二級索引

如果我們對非主鍵的列column建立索引，那這個索引就稱為輔助索引/二級索引。同樣的，Mysql會為這個索引建立一個B+樹，樹的葉子節點除了包含這個列column的值以外，就只包含這個列所在行的主鍵值，這樣通過列的索引就可以查到葉子節點，然後葉子節點中的主鍵資訊再從主鍵的索引中搜尋，最終得到一整行的資料。

通過二級索引找到主鍵，再從主鍵得到一整行資料的行為叫做回表。

1.3、聯合索引/複合索引

1.3.1、什麼是複合索引

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

一般的二級索參照這樣的語句建立：

CREATE INDEX  order_name_index on t_order(order_name);

複合索引則是這樣建立：

CREATE INDEX  order_name_and_order_type_index on t_order(order_name, order_type);

對於複合索引，Mysql會也會建立一個B+樹，但因為是多個列的索引，所以B+樹的排序規則比較特殊，是遵循最左原則。下面會講到什麼是最左原則。

之後葉子節點包含的資訊有多個，一個是作為索引的各個列的值，另一個就是主鍵的值。

1.3.2、最左原則

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

比如定義語句如下：

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

那排序規則是先排order_name，如果order_name相同，再排order_type，最後排submit_time。

那當我們查詢時，根據定義時列的順序從左至右，where子句或者order by等子句應該儘量先從order_name開始，然後以此類推。

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

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

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

1.3.3、聯合索引的查詢優化

儘量使用到組成聯合索引的列，並且保證順序。可以通過查詢索引檢視列的順序。檢視sql_in_index

show index from t_order;

查詢返回的欄位儘量就只返回組成聯合索引的列和主鍵，不要返回其它的列，以免造成回表。
這應該容易理解，因為聯合索引的B+樹的葉子節點就只包含主鍵和組成聯合索引的列的值，如果返回的欄位就這幾列，那在一個B+樹種查詢就完事了。如果還要返回其它的列的話，就又要去主鍵的索引中查詢，有回表操作。

2、雜湊索引

一般資料庫都會用B+樹索引查詢資料，但是當資料庫使用一段時間後，InnoDB 會記錄一些使用頻率較高的熱資料，然後為這些熱資料建立雜湊結構的索引，這就是雜湊索引的應用場景。

這個索引在Mysql 5.7開始預設開啟。

2.1、檢視雜湊索引的命中率等資訊

使用語句：

show engine innodb status;

其中的status有很多資訊，其中就包括雜湊索引的情況。我們把資訊複製到編輯器中檢視。其中的這一段就是雜湊索引的資訊。

-------------------------------------
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)
-- 雜湊索引的命中率，可根據這個來決定是否使用雜湊索引
0.00 hash searches/s, 0.00 non-hash searches/s
---

3、索引的建立策略

3.1、 單列索引的策略

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

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

3.1.2、根據列的值的離散性

離散性是指資料的值重複的程度高不高，假如有N條資料的話，那離散性就可以用數值表示，範圍是1/N 到 1。

比如說某個列在資料庫中有下面幾條資料（1, 2, 3, 4, 5, 5, 3），其中5和3都有重複，去重後應該是（1, 2, 3, 4, 5）。我們將去重後的條數除以總條數就得到離散性。這裡是5/7。那麼這個數值越小，代表這個列的重複資料越多；值越大代表重複資料越少。

如果一個列的資料的重複性越低，那麼這個列就越適合加索引。

因為索引是需要起到篩選的作用。比如我們有個where條件是where id = 1，如果資料重複性較高，那可能根據索引會返回100條資料，然後我們在根據其他where條件在100條資料中再篩選。

如果資料重複性較低，那可能就只返回1條資料，那之後的運算量明顯小得多。

所以一個列的資料離散性越高，那這個列越適合新增索引。

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

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

3.1.3、字首索引

字首索引和字尾索引：

有些列的值比較長，比如一些備註紀錄檔資訊也會記錄在資料庫當中，這類資訊的長度往往比較長，如果我們需要對這類列加索引，那索引並不是索引字串的全部長度。這時候我們就可以建立字首索引，即對字串的前面幾位建立索引。

所以字首索引就是建立範圍更小索引，選擇一個好字首位數就能有一個更好的查詢效率。

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

Mysql沒有字尾索引，如果非要實現字尾索引，那在資料儲存時我們應該將資料反轉，這樣就能用字首索引達到字尾索引的效果。字尾索引的一個經典應用就是郵箱，快速查詢某種型別的郵箱。

選擇字首索引的位數：

這裡的邏輯和列的離散性類似，我們需要看看字串的前面幾位的子字串的離散性如何。比如對於下面的表，內容是電影票的相關資訊，我們需要對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開始就比較高，那我們可以根據實際情況，建立13~15位的字首索引。

建立字首索引SQL語句：

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

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

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

3.2、 多列索引的策略

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

原因很簡單，查詢時根據定義複合索引時的列的順序來查詢的，離散性高的列放在前面的話，就能更早的將更多的資料排除在外。

3.2.2、三星索引

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

三條策略分別是

索引將相關記錄放在一起。

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

所以查詢需要的資料在葉子節點上越連續，越窄就越好。

索引中的資料順序與查詢中的資料排序一致。

這容易理解，講解聯合索引中說過，B+樹的排序順序和索引中的資料一樣，所以查詢時的where的資料順序越貼近索引中的順序，就越能更好地利用B+樹。

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

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

三星索引的權重：

一般來說第三個策略權重佔到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);

我們建立以上的索引，那麼對於下面的查詢語句，這個索引就是三星索引。

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+樹的葉子節點中查詢，讓我們的查詢變窄了很多，並且這部分的資料是連續的，因為B+樹是先根據city排序，再根據lname查詢。

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

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

到此這篇關於MySql索引和索引建立策略的文章就介紹到這了,更多相關MySql索引建立內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！