MySql索引怎么創建

本篇內容主要講解“MySql索引怎么創建”，感興趣的朋友不妨來看看。本文介紹的方法操作簡單快捷，實用性強。下面就讓小編來帶大家學習“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索引怎么創建”有了更深的了解，不妨來實際操作一番吧！這里是億速云網站，更多相關內容可以進入相關頻道進行查詢，關注我們，繼續學習！