【MySQL】使用前綴索引降低佔用空間與提升效率

https://www.flickr.com/photos/8058098@N07/2718571627/

在資料表中，有些字串欄位的內容可能比較多。若針對這種欄位製作索引，則佔用空間會較大。進而增加硬碟的 I/O 成本，影響查詢效率。

本文介紹「前綴索引」（prefix index）的概念。首先說明索引大小為何影響效率，接著透過測試的過程，評估建立前綴索引的適當做法。

一、索引佔用空間對效率的影響

在資料表中，有些字串欄位的值會比較長，例如：

當針對字串欄位建立索引時，若字串內容太長，那麼索引的體積也會隨之變大。

索引原先是儲存在硬碟，當 MySQL 運行時，為了提升查詢效率，會將索引內容載入到記憶體，作為快取。而這份快取空間是有限制大小的，因此當索引體積太大，則可能只會載入一部份的內容。

至於其他內容，需要時才從硬碟讀取到快取中，並適時地汰舊換新。這樣的 I/O 操作，會影響 MySQL 的執行效率。

若能降低索引的體積，那麼 MySQL 運行時能放入快取的索引資料就能增加，如此就不必太常到硬碟讀取。

索引的底層是一個樹狀結構（B+ tree），每個樹節點的大小通常是固定的。

在 B+ tree 的樹節點中，可儲存多個 key，也就是用來建立索引的資料表欄位值。若每個值的內容變少，則一個樹節點便能儲存更多資料，使得樹的高度下降。如此一來，即可縮短在樹中查找資料的路徑，意味著效率的提升。

此外，進行寫入操作（插入、修改、刪除）時，資料庫也得維護索引的內容。故縮短查找路徑，對寫入效率亦有幫助。

那麼該如何降低索引的體積呢？我們可以只針對字串值的前幾個字來建立索引，而不是用全部的內容。這種索引稱為「前綴索引」。

以下語法是針對學生資料表的信箱欄位，使用前面 5 個字來建立索引。

CREATE INDEX `idx_student_email_prefix_5` ON `student`(`email`(5));

建立索引的語法中，在欄位名稱後方，可以用小括弧 () 來定義要擷取前幾個字。

執行以下語法，可以確認索引的資訊。

SHOW INDEX FROM `student`;

節錄部份結果如下：

Key_name	Column_name	Sub_part
idx_student_email_prefix_5	email	5

從索引資訊中能看到，「Sub_part」屬性的值，就是從字串中擷取的長度。

至於在建立前綴索引時要擷取幾個字，可根據被擷取的字的「代表性」來決定。

代表性的大小，能從是否趨近於「唯一」來評估。比方說，主鍵欄位和唯一欄位，它們的值在資料表中一定不會重複，故代表性是最高的。

讓我們回到學生信箱的情境，假設有以下 5 個 email 地址：

代表性可藉由以下範例語法來評估。這是計算 email 的前 2 個字，其不重複值佔整個資料表的比例。

SELECT COUNT(DISTINCT SUBSTRING(`email`, 1, 4)) / COUNT(`email`) FROM `student`;

下表整理出擷取不同字串長度的代表性。

從表格中可得知，若擷取前 2 個字，雖然索引體積最小，但代表性太低。若以「andy」這個字來查詢，在索引中會透過「an」找到 3 筆資料；同理，「mark」則找到 2 筆。

若擷取前 4 個字，索引儲存的 key 變多了，但數量正好佔資料總筆數的 100%。這是將代表性提升到最高時，索引體積最小的選擇，現況不必再擷取更多字了。

隨著增加擷取長度的測試過程中，可歸納出不重複 key 的數量 / 資料表總筆數的公式，來計算代表性。我們應該在索引體積和代表性之間來權衡。