MySQL首碼索引和索引選擇性初步認識

MySQL首碼索引和索引選擇性初步認識

　　有時候需要索引很長的字元列，如 BLOB、TEXT 或者很長的 VARCHAR 類型的列，這會讓索引變得很大，導致查詢很慢。對於這種情況，我們可以使用首碼索引來索引開始的部分字元，這樣可以大大的節約索引空間，從而提高索引效率。但這樣也會降低索引的選擇性。

　　索引的選擇性是指：不重複的索引值（也稱為基數，cardinality）和資料表的記錄總數（#T）的比值，範圍從1/#T到1之間。

　　索引的選擇性越高則查詢效率越高，因為選擇性高的索引可以過濾更多的行。唯一索引的選擇性是1，這是最好的索引選擇性，效能也是最好的。如下樣本：

// 建立表
CREATE TABLE sakila.city_demo(
    city VARCHAR(50) NOT NULL
);

// 自我複製
INSERT INTO sakila.city_demo(city) SELECT `city` FROM sakila.city;

// 從城市表中隨機賦值資料到城市測試表
UPDATE sakila.city_demo SET city = (SELECT city FROM sakila.city ORDER BY RAND() LIMIT 1);

現在我們已經有了一個測試資料集，現在，我們來統計城市表中，最常見的城市：

SELECT
    COUNT(*) AS cnt,
    city
FROM
    sakila.city_demo
GROUP BY
    city
ORDER BY
    cnt DESC
LIMIT 10;

結果集如下：

以上的10個城市就是最頻繁出現的城市首碼，現在先從3個首碼字母開始：

SELECT
    COUNT(*) AS cnt ,
    LEFT(city, 3) AS pref
FROM
    sakila.city_demo
GROUP BY
    pref
ORDER BY
    cnt DESC
LIMIT 10;

結果集如下：

 由上結果可知，每個首碼都比原來的城市出現的次數更多，因此唯一首碼比唯一城市要少得多。然後我們繼續增加長度測試，直到這個首碼的選擇性接近完整列的選擇性。最後，我們發現，當長度為7時，最適合：

計算合適的前置長度的另外一個方法就是計算完整性的選擇性，並使首碼的選擇性接近於完整列的選擇性。下面顯示如何計算完整列的選擇性：

1 SELECT2     COUNT(DISTINCT city)/COUNT(*) 3 FROM 4     sakila.city_demo;

結果集為：

通常來說，如果首碼的選擇效能夠接近0.031，基本已經可以用了。當然，也可以在一個查詢中，針對不同前置長度進行計算，這對於大表非常有用。

SELECT
    COUNT(DISTINCT LEFT(city, 3))/COUNT(*) AS sel3,
    COUNT(DISTINCT LEFT(city, 4))/COUNT(*) AS sel4,
    COUNT(DISTINCT LEFT(city, 5))/COUNT(*) AS sel5,
    COUNT(DISTINCT LEFT(city, 6))/COUNT(*) AS sel6,
    COUNT(DISTINCT LEFT(city, 7))/COUNT(*) AS sel7,
FROM
    sakila.city_demo;

結果集如下：

查詢顯示當首碼索引長度到達7的時候，再增加前置長度，選擇性提升的幅度已經很小了，處於索引長度越長，索引越大，查詢越慢的考慮，所以長度為7是比較適合的。

當然，只看平均選擇性是不夠的，也有例外的情況。根據上面的平均選擇性來看，你可能會認為前置長度為4或者5的索引已經足夠了，但如果資料分布很不均勻，可能會存在陷阱，現在我們來觀察前置長度為4的最長出現城市的次數：

由上可知，如果首碼是4個位元組，則最常出現的首碼的出現次數比最常出現的城市的出現次數要大很多。即這些值的選擇性比平均選擇性要低。

下面我們來示範如何建立首碼索引：

ALTER TABLE sakila.city_demo ADD KEY (city(7));

優點：

　　能使索引更小、更快。

缺點：

　　無法使用首碼索引進行ORDER BY 和 GROUP BY ，也無法使用首碼索引做覆蓋掃描。

常見的應用情境：

　　針對很長的十六進位唯一ID使用首碼索引。