MySQL索引選擇及規則整理

weixin_34353714發表於2018-09-13
MySql索引

索引選擇性

索引選擇性就是結果個數與總個數的比值。
用sql語句表示為：

SELECT COUNT(*) FROM table_name WHERE column_name/SELECT COUNT(*) FROM table_name

一般來說(例如書 “SQL Tuning“)，如果選擇性超過 20% 那麼全表掃描比使用索引效能更優。
但MySQL是沒有計算索引的選擇性的，只是預測邏輯IO操作的數量，因此對於MySQL索引要慎重選擇。
舉個栗子，tinyint型別的列，用以儲存性別，就算用上“保密”和“變性”2項，選擇性也最小也才25%，因此也就沒有設定索引的必要了。

索引規則

以下是整理出來的規則，以供參考：（由於很多實際專案都是查詢操作佔了絕大部分，因此不必太過呆板）

  1. 選擇維度高的列
  2. 選擇 where,on,group by,order by 中出現的列
  3. 選擇較小的資料列，這樣的索引檔案更小，同時可裝載更多的索引鍵
  4. 為較長的字串使用字首索引
  5. 組合索引能減低索引檔案的大小，使用速度也優於多個單列索引
  6. 切勿濫用索引，因為除了磁碟空間的開銷外，每次增刪改都需要重新建立索引
  7. 索引不會包含有NULL值的列（若組合索引包含NULL值的列則整個組合索引無效）

另外，使用索引應當儘量避免 “OR” 、“否定查詢” 、“模糊查詢”、“NOT IN”、“<>” 等操作！
注意：字首索引和組合索引是建立索引的一種技巧，並非索引型別。

組合索引

假設test表中有a，b，c三個列。

ALTER TABLE test ADD INDEX abc(a,b,c);

相當於分別建立了
a,b,c
a,b
a
這樣的3組索引，也是“最左字首”這個規則的結果。
舉個使用該組合索引的栗子：

SELECT * FROM test WHERE a="1" AND b="2" SELECT * FROM test WHERE a="1"

以下則用不到索引：

SELECT * FROM test WHERE b="1" AND c="2" SELECT * FROM test WHERE c="1"

因此組合索引有一定優勢，但在使用上需謹慎。

字首索引

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

ALTER TABLE table_name ADD KEY column_name(prefix_length);

建立字首索引的關鍵在於"prefix_length"這個引數，並且字首索引的選擇性上也有一點特殊。
字首索引的選擇性公式為：SELECT COUNT(DISTINCT column_name)/COUNT(*) FROM table_name
繼續舉栗子！
現在有個user表，列 family_name varchar(50) 儲存的是英文姓氏（我也想用中文姓名來舉例，但是不大適合，看下去就明白了。。。）
要取得設定字首索引最理想的"prefix_length"，我們首先要取得整列的選擇性，如下：

SELECT COUNT(DISTINCT family_name)/COUNT(*) FROM user;

假設這裡得到值是0.188。
然後我們繼續去看看該列前1個字元的選擇性又是多少

SELECT COUNT(DISTINCT LEFT(family_name,1))/COUNT(*) FROM user;

假設這裡得到的結果是0.532，和整列的選擇性出入太大，不可取，繼續：

SELECT COUNT(DISTINCT LEFT(family_name,2))/COUNT(*) FROM user;
SELECT COUNT(DISTINCT LEFT(family_name,3))/COUNT(*) FROM user;
...

假設直接到“prefix_length”為5時，得到的值為0.189，非常接近！
而取6時得到的值為0.18891，這個選擇性和5並沒有太大的偏差。
再結合減少索引檔案大小的這個思路
“prefix_length”值設定為5才是此處設定字首索引的最優方案！
選擇性講完，還得再講清楚這個字首索引該怎麼用！
書接上面的栗子~
正確的用法如下：

SELECT * FROM user WHERE family_name LIKE "lee%";
SELECT * FROM user WHERE family_name LIKE "david%";

以下則用不上該索引：

SELECT * FROM user WHERE family_name LIKE "_ee%";
SELECT * FROM user WHERE family_name LIKE "%en%";
SELECT * FROM user WHERE family_name LIKE "%ar%";

注意：SQL的模式預設是忽略大小寫的！
另外，“_”代表一個字元，“%”代表任意多個字元！

相關文章