技術文章 » 什麼是字首索引？

什麼是字首索引？

2022-09-30 18:00:44

一、什麼是字首索引？

所謂字首索引，說白了就是對文字的前幾個字元建立索引（具體是幾個字元在建立索引時去指定），比如以產品名稱的前 10 位來建索引，這樣建立起來的索引更小，查詢效率更快！

有點類似於 Oracle 中對欄位使用 Left 函數來建立函數索引，只不過 MySQL 的這個字首索引在查詢時是內部自動完成匹配的，並不需要使用 Left 函數。

二、為什麼要用字首索引？

可能有的同學會發出疑問，為什麼不對整個欄位建立索引呢？

一般來說，當某個欄位的資料量太大，而且查詢又非常的頻繁時，使用字首索引能有效的減小索引檔案的大小，讓每個索引頁可以儲存更多的索引值，從而提高了索引查詢的速度。

比如，客戶店鋪名稱，有的名稱很長，有的很短，如果完全按照全覆蓋來建索引，索引的儲存空間可能會非常的大，有的表如果索引建立的很多，甚至會出現索引儲存的空間都比資料表的儲存空間大很多，因此對於這種文字很長的欄位，我們可以擷取前幾個字元來建索引，在一定程度上，既能滿足資料的查詢效率要求，又能節省索引儲存空間。

但是另一方面，字首索引也有它的缺點，MySQL 中無法使用字首索引進行 ORDER BY 和 GROUP BY，也無法用來進行覆蓋掃描，當字串本身可能比較長，而且前幾個字元完全相同，這個時候字首索引的優勢已經不明顯了，就沒有建立字首索引的必要了。

因此這又回到一個概念，那就是關於索引的選擇性！

關於資料庫表索引的選擇性，我會單獨開篇來講解，大家只需要記住一點：索引的選擇性越高則查詢效率越高，因為選擇性高的索引可以讓 MySQL 在查詢時過濾掉更多的行，資料查詢速度更快！

當某個欄位內容的前幾位區分度很高的時候，這個時候採用字首索引，可以在查詢效能和空間儲存方面達到一個很高的價效比。

那麼問題來了，怎麼建立字首索引呢？

三、怎麼建立字首索引？

建立字首索引的方式，方法很簡單，通過如下方式即可建立！

ALTER TABLE table_name ADD KEY(column_name(prefix_length));

其中prefix_length這個引數，就是字首長度的意思，通常通過如下方式進行確認，步驟如下：

第一步，先計算某欄位全列的區分度。

SELECT COUNT(DISTINCT column_name) / COUNT(*) FROM table_name;

第二步，然後再計算字首長度為多少時和全列的區分度最相似

SELECT COUNT(DISTINCT LEFT(column_name, prefix_length)) / COUNT(*) FROM table_name;

最後，不斷地調整prefix_length的值，直到和全列計算出區分度相近，最相近的那個值，就是我們想要的值。

下面以某個測試表為例，資料體量在 100 萬以上，表結構如下！

CREATE TABLE `tb_test` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

測試一下正常的帶name條件查詢，效率如下：

select * from tb_test where name like '1805.59281427%'

我們以name欄位為例，建立字首索引，找出最合適的prefix_length值。

首先，我們大致計算一下name欄位全列的區分度。

可以看到，結果為 0.9945，也就是說全域性不相同的資料率在99.45%這個比例。

下面我們一起來看看，不同的prefix_length值下，對應的資料不重複比例。

  • 當prefix_length為5，區分度為0.2237

  • 當prefix_length為10，區分度為0.9944

  • 當prefix_length為11，區分度為0.9945

通過對比，我們發現當prefix_length為11，最接近全域性區分度，因此可以為name建立一個長度為11的字首索引，建立索引語句如下：

alter table tb_test add key(name(11));

下面，我們再試試上面那個語句查詢！

建立字首索引之後，查詢效率倍增！

四、使用字首索引需要注意的事項？

是不是所有的欄位，都適合用字首索引呢？

答案顯然不是，在上文我們也說到了，當某個索引的字串列很大時，建立的索引也就變得很大，為了減小索引體積，提高索引的掃描速度，使用索引的前部分字串作為索引值，這樣索引佔用的空間就會大大減少，並且索引的選擇性也不會降低很多，這時字首索引顯現的作用就會非常明顯，字首索引本質是索引查詢效能和儲存空間的一種平衡。

對於 BLOB 和 TEXT 列進行索引，或者非常長的 VARCHAR 列，就必須使用字首索引，因為 MySQL 不允許索引它們的全部長度。

但是如果某個欄位內容，比如字首部分相似度很高，此時的字首索引顯現效果就不會很明顯，採用覆蓋索引效果會更好！

五、小結

好了，本文主要圍繞字首索引做了一次初步的知識講解，具體資料庫表索引的選擇性，還需要結合業務實際需求來考慮！

今天就說這麼多，後面的問題，我們繼續再扯！

六、參考

1、CSDN - Java架構設計 - 一篇文章弄懂字首索引

2、知乎 - Java程式設計宇宙 - 什麼是字首索引