資料庫系列：字首索引和索引長度的取捨

Brand發表於2023-10-27
資料庫索引

資料庫系列：MySQL慢查詢分析和效能最佳化
資料庫系列：MySQL索引最佳化總結（綜合版）
資料庫系列：高併發下的資料欄位變更
資料庫系列：覆蓋索引和規避回表
資料庫系列：資料庫高可用及無損擴容
資料庫系列：使用高區分度索引列提升效能

1 背景

有時候我們需要在字元型別的欄位上建設索引，但是如果該欄位的值都普遍比較大的話，會讓索引變得大而且慢。
根據我們之前的瞭解，每個磁碟塊（disk）儲存的內容是有限的（InnoDB儲存引擎中頁的大小為16KB），如果一個頁中可以儲存的索引記錄越多，那麼查詢效率就會提高，因為查詢次數、查詢深度會變少。
但是索引整個字元列會讓我們索引內容特別大，會導致單個頁儲存的索引記錄減少，就需要更多的頁去儲存，B+Tree的樹深度變深，查詢效率大幅降低。
比較有效的一種辦法是指定索引的欄位長度，比如可以索引開始的部分字元，這樣可以大大節約索引空間（每個頁），從而提高索引效率。

# enp_name 如果值普遍太長會導致索引空間爆棚
create index idx_emp_empname on emp(emp_name)

# 可能合適的做法
create index idx_emp_empname on emp(emp_name(5))

2 關於索引選擇性

索引的選擇性是指不重複的索引值（也稱為基數，cardinality) 和 資料表的記錄總數的比值，範圍從1/#T到1之間。索引的選擇性越高則查詢效率越高，因為選擇性高的索引可以讓MySQL在查詢時過濾掉更多的行。唯一索引的選擇性是1，這是最好的索引選擇性，效能也是最好的。
以下圖為例，欄位emp_name的索引長度設定為4。如果不設定長度，一旦有大量長字串元素，會佔據大量的空間，而單個Disk的空間是有限的。
就會導致樹的層級很高，搜尋的IO次數變多，索引效能降低。
image

★ 說明：#T 指資料表的記錄總數
★ 參考書籍《高效能MySQL》 的5.3.2小節的第3段 ：
一般情況下某個字首的選擇性也是足夠高的，足以滿足查詢效能。對於BLOB，TEXT，或者很長的VARCHAR型別的列，必須使用字首索引，因為MySQL不允許索引這些列的完整長度。

3 如何計算索引的選擇性

3.1 分析

提供一個具有500w資料的僱員表emp，準備在僱員姓名 emp_name 欄位在做索引。
emp_name有些值還是挺長的，如果全部拿來做索引，那勢必導致索引表很龐大。我們先來檢索下這個emp_name，看看有沒有什麼特徵。

1  mysql> select distinct emp_name from emp;
2  +----------+
3  | emp_name |
4  +----------+
5  |  ali_user |
6  |  brandy_user |
7  |  cancys_user |
8  |  davide_user |
9  |  elasne_user |
10 |  finest_user |
11 |  ......... |
12 |  gagnannan_user |
13 |  halande_user |
14 |  ivil_user |
15 |  kikol_user |
16 |  Cecilia_user |
17 |  Abigail_user |
18 |  Blanche_user |
19 |  Elizabeth_user |
20 |  ....... |
21 +----------+
22 4999850 row in set

從上面的資料進行分析，可以得到以下結論：

  • 索引長度肯定不能太短（比如2或者3），前6個字元的區分度非常高，如果選擇2或者3，會掃出大量的資料，增加計算成本。
  • 不能不設定索引的長度，字尾 _user 是一致的，沒有任何的區分度優勢，反而會佔用很多無效的儲存空間，增加 I/O次數。
  • 目前看，最合適的可能是6或者7，前幾個字元的區分度很高，這只是推斷，需要用演算法判定。

3.2 字首索引的長度的判斷方法

判斷的演算法：

select count(distinct left(`c_name`,calcul_len))/count(*) from t_name;

可以套入emp表進行計算，如下：

SELECT
    count(DISTINCT LEFT(emp_name, 3)) / count(*) AS sel3,
    count(DISTINCT LEFT(emp_name, 4)) / count(*) AS sel4,
    count(DISTINCT LEFT(emp_name, 5)) / count(*) AS sel5,
    count(DISTINCT LEFT(emp_name, 6)) / count(*) AS sel6,
    count(DISTINCT LEFT(emp_name, 7)) / count(*) AS sel7
FROM
    emp

3.3 新增字首索引

下面是新增帶有索引長度的字首索引語法：

ALTER TABLE t_name ADD KEY (c_name[(lenth)]);

測試emp_name最合適的長度，因為empname的長度基本維持在6個字元左右，少數量超過6長度，所以指定empname索引長度時6是最建議的

mysql>  SELECT
     count(DISTINCT LEFT(emp_name, 3)) / count(*) AS sel3,
     count(DISTINCT LEFT(emp_name, 4)) / count(*) AS sel4,
     count(DISTINCT LEFT(emp_name, 5)) / count(*) AS sel5,
     count(DISTINCT LEFT(emp_name, 6)) / count(*) AS sel6,
     count(DISTINCT LEFT(emp_name, 7)) / count(*) AS sel7
 FROM
     emp;
+--------+--------+--------+--------+--------+
| sel3   | sel4   | sel5   | sel6   | sel7   |
+--------+--------+--------+--------+--------+
| 0.0012 | 0.0076 | 0.0400 | 0.1713 | 0.1713 |
+--------+--------+--------+--------+--------+
1 row in set

我們可以使用 不同的長度來測試檢索效率
當長度為2的時候，匹配度低於 0.0012，檢索效率自然比較慢

mysql> create index idx_emp_empname on emp(emp_name(2));
Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from emp where emp_name='LsHfFJA';
+---------+---------+---------+---------+-----+---------------------+------+------+-------+
| id      | empno   | emp_name | job     | mgr | hiredate            | sal  | comn | depno |
+---------+---------+---------+---------+-----+---------------------+------+------+-------+
| 4582071 | 4582071 | LsHfFJA | SALEMAN |   1 | 2021-01-23 16:46:03 | 2000 | 400  |   106 |
+---------+---------+---------+---------+-----+---------------------+------+------+-------+
1 row in set  (1.793 sec)

當長度為6的時候，匹配度低於 0.1713，檢索效率就比較高

mysql> create index idx_emp_empname on emp(emp_name(6));
Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from emp where emp_name='LsHfFJA';
+---------+---------+---------+---------+-----+---------------------+------+------+-------+
| id      | empno   | emp_name | job     | mgr | hiredate            | sal  | comn | depno |
+---------+---------+---------+---------+-----+---------------------+------+------+-------+
| 4582071 | 4582071 | LsHfFJA | SALEMAN |   1 | 2021-01-23 16:46:03 | 2000 | 400  |   106 |
+---------+---------+---------+---------+-----+---------------------+------+------+-------+
1 row in set  （0.003 sec）

4 總結

選擇索引長度應該要選擇足夠長的字首以保證較高的選擇性，同時又不能太長，以便節約空間。
最好的要求就是字首的基數（cardinality）接近於完整列的基數，以達到足夠優秀的效能。

相關文章