MySQL索引設計原則深入分析講解

哪些情況適合建立索引？ 欄位的數值有唯一性的限制

索引本身可以起到約束的作用，比如唯一索引，主鍵索引都是可以起到唯一性約束的，因此在我們的資料表中如果某個欄位是唯一性的，就可以直接建立唯一性索引，或者主鍵索引。這樣可以更快速地通過該索引來確定某條記錄。

業務上具有唯一特性的欄位，即使是組合欄位，也必須建成唯一索引。(來源:Alibaba)

說明：不要以為唯一索引影響了 insert 速度，這個速度損耗可以忽略，但提高查詢速度是明顯的。

頻繁作為 WHERE 查詢條件的欄位

某個欄位在SELECT語句的 WHERE 條件中經常被使用到，那麼就需要給這個欄位建立索引了。尤其是在資料量大的情況下，建立普通索引就可以大幅提升資料查詢的效率。

經常 GROUP BY 和 ORDER BY 的列

索引就是讓資料按照某種順序進行儲存或檢索，因此當我們使用 GROUP BY 對資料進行分組查詢，或者使用 ORDER BY 對資料進行排序的時候，就需要對分組或者排序的欄位進行索引。如果待排序的列有多個，那麼可以在這些列上建立組合索引。

如果既有GROUP BY又有ORDER BY，可以考慮聯合索引，由於GROUP BY先執行，聯合索引中GROUP BY使用的欄位排列在前面。

UPDATE、DELETE的WHERE條件列

當我們對某條資料進行UPDATE或者DELETE操作的時候，是否也需要對WHERE條件列建立索引呢？

對資料按照某個條件進行查詢後再進行 UPDATE 或 DELETE 的操作，如果對 WHERE 欄位建立了索引，就能大幅提升效率。原理是因為我們需要先根據 WHERE 條件列檢索出來這條記錄，然後再對它進行更新或刪除。如果進行更新的時候，更新的欄位是非索引欄位，提升的效率會更明顯，這是因為非索引欄位更新不需要對索引進行維護。

DISTINCT欄位需要建立索引

有時候我們需要對某個欄位進行去重，使用 DISTINCT，那麼對這個欄位建立索引，也會提升查詢效率。索引會對資料按照某種順序進行排序，所以在去重的時候也會快很多。

多表 JOIN 連線操作時，建立索引注意事項

首先，連線表的數量儘量不要超過 3 張，因為每增加一張表就相當於增加了一次巢狀的迴圈，數量級增長會非常快，嚴重影響查詢的效率。

其次，對 WHERE 條件建立索引，因為 WHERE 才是對資料條件的過濾。如果在資料量非常大的情況下，沒有 WHERE 條件過濾是非常可怕的。

最後，對用於連線的欄位建立索引，並且該欄位在多張表中的型別必須一致。

使用列的型別小的建立索引

我們這裡所說的型別大小指的就是該型別表示的資料範圍的大小。

我們在定義表結構的時候要顯式的指定列的型別，以整數型別為例，有TINYINT、MEDIUMINT、INT、BIGINT等，它們佔用的儲存空間依次遞增，能表示的整數範圍當然也是依次遞增。如果我們想要對某個整數列建立索引的話，在表示的整數範圍允許的情況下，儘量讓索引列使用較小的型別，比如我們能使用INT就不要使用 BIGINT ，能使用 MEDIUMINT 就不要使用 INT。這是因為：

資料型別越小，在查詢時進行的比較操作越快；資料型別越小，索引佔用的儲存空間就越少，在一個資料頁內就可以放下更多的記錄 ，從而減少磁碟 I/O 帶來的效能損耗，也就意味著可以把更多的資料頁快取在記憶體中，從而加快讀寫效率。

這個建議對於表的主鍵來說更加適用 ，因為不僅是聚簇索引中會儲存主鍵值，其他所有的二級索引的節點處都會儲存一份記錄的主鍵值，如果主鍵使用更小的資料型別，也就意味著節省更多的儲存空間和更高效的I/O。

使用字串字首建立索引

假設我們的字串很長，那儲存一個字串就需要佔用很大的儲存空間。在我們需要為這個字串列建立索引時，那就意味著在對應的B+樹中有這麼兩個問題：

B+樹索引中的記錄需要把該列的完整字串儲存起來，更費時，而目字串越長，在索引中佔用的儲存空間越大。如果B+樹索引中索引列儲存的字串很長，那在做字串比較時會佔用更多的時間。

我們可以通過擷取欄位的前面一部分內容建立索引，這個就叫字首索引。這樣在查詢記錄時雖然不能精確的定位到記錄的位置，但是能定位到相應字首所在的位置，然後根據字首相同的記錄的主鍵值回表查詢完整的字串值。既節約空間，又減少了字串的比較時間，還大體能解決排序的問題。

例如，TEXT和BLOG型別的欄位，進行全文檢索會很浪費時間，如果只檢索欄位前面的若干字元，這樣可以提高檢索速度。

範例：建立一張商戶表，因為位址列位比較長，在位址列位上建立字首索引

CREATE TABLE shop(address VARCHAR(120) NOT NULL);
ALTER TABLE shop ADD INDEX idx_address(address(12));

問題是，擷取多少呢？擷取得多了，達不到節省索引儲存空間的目的；擷取得少了，重複內容太多，欄位的雜湊度（選擇性）會降低。怎麼計算不同的長度的選擇性呢？

先看一下欄位在全部資料中的選擇度：

SELECT COUNT(DISTINCT address) / COUNT(*) FROM shop;

通過不同長度去計算，與全表的選擇性對比：

公式：

COUNT(DISTINCT LEFT(列名, 索引長度)) / COUNT(*) 

例如：

SELECT COUNT(DISTINCT LEFT(address,10)) / COUNT(*) AS sub10, -- 擷取前10個字串的選擇度
COUNT(DISTINCT LEFT(address,15)) / COUNT(*) AS sub15, -- 擷取前15個字串的選擇度
COUNT(DISTINCT LEFT(address,20)) / COUNT(*) AS sub20, -- 擷取前20個字串的選擇度
COUNT(DISTINCT LEFT(address,25)) / COUNT(*) AS sub25 -- 擷取前25個字串的選擇度
FROM shop;

引申另一個問題：索引列字首對排序的影響

如果使用了索引列字首，比方說前邊只把address列的 前12個字元 放到了二級索引中，下邊這個查詢可能就有點兒尷尬了：

SELECT * FROM shop
ORDER BY address
LIMIT 10;

因為二級索引中不包含完整的address列資訊，所以無法對前12個字元相同，後邊的字元不同的記錄進行排序，也就是使用索引列字首的方式，無法支援使用索引排序，只能使用檔案排序。

拓展：Alibaba《 Java開發手冊(黃山版)》

【強制】在 varchar 欄位上建立索引時，必須指定索引長度，沒必要對全欄位建立索引，根據實際文字區分度決定索引長度。

說明：索引的長度與區分度是一對矛盾體，一般對字串型別資料，長度為20的索引，區分度會高達 90%以上，可以使用COUNT(DISTINCT LEFT(列名, 索引長度)) / COUNT(*) 的區分度來確定。

區分度高(雜湊性高)的列適合作為索引

列的基數指的是某一列中不重複資料的個數，比方說某個列包含值2, 5, 8, 2, 5, 8, 2, 5, 8，雖然有9條記錄，但該列的基數卻是3。也就是說，在記錄行數一定的情況下，列的基數越大，該列中的值越分散；列的基數越小，該列中的值越集中。這個列的基數指標非常重要，直接影響我們是否能有效的利用索引，最好為列的基數大的列建立索引，為基數太小列的建立索引效果可能不好。

可以使用公式：

SELECT COUNT(DISTINCT 列名) / COUNT(*) FROM 表名;

計算區分度，越接近1越好，一般超過33%就算是比較高效的索引了。

拓展：聯合索引把區分度高(雜湊性高)的列放在前面。

使用最頻繁的列放到聯合索引的左側

這樣也可以較少的建立一些索引。同時，由於"最左字首原則"，可以增加聯合索引的使用率。

在多個欄位都要建立索引的情況下，聯合索引優於單值索引 限制索引的數目

在實際工作中，我們也需要注意平衡，索引的數目不是越多越好。我們需要限制每張表上的索引數量，建議單張表索引數量不超過6個。原因:

每個索引都需要佔用 磁碟空間，索引越多，需要的磁碟空間就越大。索引會影響 INSERT、DELETE、UPDATE等語句的效能，因為表中的資料更改的同時，索引也會進行調整和更新，會造成負擔。優化器在選擇如何優化查詢時，會根據統一資訊，對每一個可以用到的索引來進行評估，以生成出一個最好的執行計劃，如果同時有很多個索引都可以用於查詢，會增加MySQL優化器生成執行計劃時間，降低查詢效能。 哪些情況不適合建立索引 在where中使用不到的欄位，不要設定索引

WHERE 條件（包括 GROUP BY、ORDER BY）裡用不到的欄位不需要建立索引，索引的價值是快速定位，如果起不到定位的欄位通常是不需要建立索引的。

資料量小的表最好不要使用索引

如果表記錄太少，比如少於 1000 個，那麼是不需要建立索引的。表記錄太少，是否建立索引對查詢效率的影響並不大。甚至說，查詢花費的時間可能比遍歷索引的時間還要短，索引可能不會產生優化效果。

結論：在資料表中的資料行數比較少的情況下，比如不到1000行，是不需要建立索引的。

有大量重複資料的列上不要建立索引

在條件表示式中經常用到的不同值較多的列上建立索引，但欄位中如果有大量重複資料，也不用建立索引。

比如在學生表的"性別"欄位上只有“男”與“女”兩個不同值，因此無須建立索引。如果建立索引，不但不會提高查詢效率，反而會嚴重降低資料更新速度。

範例：要在 100 萬行資料中查詢其中的 50 萬行（比如性別為男的資料），一旦建立了索引，你需要先存取 50萬次索引，然後再存取 50 萬次資料表，這樣加起來的開銷比不使用索引可能還要大。

索引的價值是幫你快速定位。如果想要定位的資料有很多，那麼索引就失去了它的使用價值，比如通常情況下的性別欄位。

結論：當資料重複度大，比如 高於10% 的時候，也不需要對這個欄位使用索引。

避免對經常更新的表建立過多的索引

第一層含義：頻繁更新的欄位不一定要建立索引。因為更新資料的時候，也需要更新索引，如果索引太多，在更新索引的時候也會造成負擔，從而影響效率。

第二層含義：避免對經常更新的表建立過多的索引，並且索引中的列儘可能少。此時，雖然提高了查詢速度，同時卻會降低更新表的速度。

不建議用無序的值作為索引

例如身份證、UUID(在索引比較時需要轉為ASCII，並且插入時可能造成頁分裂)、MD5、HASH、無序長字串等。

刪除不再使用或者很少使用的索引

表中的資料被大量更新，或者資料的使用方式被改變後，原有的一些索引可能不再需要。資料庫管理員應當定期找出這些索引，將它們刪除，從而減少索引對更新操作的影響。

不要定義冗餘或重複的索引 冗餘索引

有時候有意或者無意的就對同一個列建立了多個索引，比如：index(a,b,c)相當於index(a)、index(a,b)、index(a,b,c)。

範例：

CREATE TABLE person_info (
	id INT UNSIGNED NOT NULL AUTO_INCREMENT,
	name VARCHAR (100) NOT NULL,
	birthday DATE NOT NULL,
	phone_number CHAR (11) NOT NULL,
	country VARCHAR (100) NOT NULL,
	PRIMARY KEY (id),
	KEY idx_name_birthday_phone_number (name(10), birthday, phone_number),
	KEY idx_name (name(10)) 
);

我們知道，通過idx_name_birthday_phone_number 索引就可以對 name 列進行快速搜尋，再建立一個專門針對 name 列的索引就算是一個冗餘索引，維護這個索引只會增加維護的成本，並不會對搜尋有什麼好處。

重複索引

另一種情況，我們可能會對某個列重複建立索引：

CREATE TABLE repeat_index_demo (
	col1 INT PRIMARY KEY,
	co12 INT,
	UNIQUE uk_idx_c1 (col1),
	INDEX idx_c1 (col1)
);

我們看到，col1既是主鍵、又給它定義為一個唯一索引，還給它定義了一個普通索引。可是主鍵本身就會生成聚簇索引，所以定義的唯一索引和普通索引是重複的，這種情況要避免。

小結

索引是一把雙刃劍，可提高查詢效率，但也會降低插入和更新的速度並佔用磁碟空間。

選擇索引的最終目的是為了使查詢的速度變快，上面給出的原則是最基本的準則，但不能拘泥於上面的準則，大家要在以後的學習和工作中進行不斷的實踐，根據應用的實際情況進行分析和判斷，選擇最合適的索引方式。

到此這篇關於MySQL索引設計原則深入分析講解的文章就介紹到這了,更多相關MySQL索引內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！