字首索引，一種優化索引大小的解決方案

深藍發表於2015-03-03
索引優化

今天在讀一篇關於資料庫索引介紹的文章時，該文章提到了字首索引，對於我這個搞資料庫應用開發那麼多年的人來說，這個詞還真是一個新詞，沒用過。於是打算研究一番。

字首索引似乎是MySQL中的一個概念，在SQL Server和Oracle中沒提出這個概念。於是就安裝了一個MySQL來做實驗，搞清楚字首索引。

字首索引說白了就是對文字的前幾個字元（具體是幾個字元在建立索引時指定）建立索引，這樣建立起來的索引更小，所以查詢更快。有點相當於Oracle中對欄位使用Left函式，建立函式索引，只不過MySQL的這個字首索引在查詢時是內部自動完成匹配的，並不需要使用left函式。

別的文章中提到：

MySQL 字首索引能有效減小索引檔案的大小，提高索引的速度。但是字首索引也有它的壞處：MySQL 不能在 ORDER BY 或 GROUP BY 中使用字首索引，也不能把它們用作覆蓋索引(Covering Index)。

建立字首索引的語法為：

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

這裡最關鍵的引數就是prefix_length，這個值需要根據實際表的內容，得到合適的索引選擇性（Index Selectivity）。索引選擇性就是不重複的個數與總個數的比值。

select 1.0*count(distinct column_name)/count(*)
from table_name

比如我們現在有個Employee表，其中有個FirstName欄位，是varchar(50)的，我們查詢該欄位的索引選擇性：

select 1.0*count(distinct FirstName)/count(*)
from Employee

得到結果0.7500，然後我們希望對FirstName建立字首索引，希望字首索引的選擇效能夠儘量貼近於對整個欄位建立索引時的選擇性。我們先看看3個字元，如何：

select 1.0*count(distinct left(FirstName,3))/count(*)
from Employee

得到的結果是0.58784，好像差距有點大，我們再試一試4個字元呢：

select 1.0*count(distinct left(FirstName,4))/count(*)
from Employee

得到0.68919，已經提升了很多，再試一試5個字元，得到的結果是0.72297，這個結果與0.75已經很接近了，所以我們這裡認為字首長度5是一個合適的取值。所以我們可以為FirstName建立字首索引：

alter table test.Employee add key(FirstName(5))

建立字首索引後查詢語句並不需要更改，如果我們要查詢所有FirstName為Devin的Employee，那麼SQL仍然寫成：

select *
from Employee e
where e.FirstName='Devin';

下面總結一下什麼情況下使用字首索引：

  • 字串列(varchar,char,text等)，需要進行全欄位匹配或者前匹配。也就是=‘xxx’ 或者 like ‘xxx%'
  • 字串本身可能比較長，而且前幾個字元就開始不相同。比如我們對中國人的姓名使用字首索引就沒啥意義，因為中國人名字都很短，另外對收件地址使用字首索引也不是很實用，因為一方面收件地址一般都是以XX省開頭，也就是說前幾個字元都是差不多的，而且收件地址進行檢索一般都是like ’%xxx%’，不會用到前匹配。相反對外國人的姓名可以使用字首索引，因為其字元較長，而且前幾個字元的選擇性比較高。同樣電子郵件也是一個可以使用字首索引的欄位。
  • 前一半字元的索引選擇性就已經接近於全欄位的索引選擇性。如果整個欄位的長度為20，索引選擇性為0.9，而我們對前10個字元建立字首索引其選擇性也只有0.5，那麼我們需要繼續加大字首字元的長度，但是這個時候字首索引的優勢已經不明顯，沒有太大的建字首索引的必要了。

 

 

 


相關文章