Mysql性能優化：如何給字元串加索引？

Mysql性能優化：如何給字元串加索引？

導讀

現代大部分的登入系統都支援郵箱、手機号碼登入兩種方式，那麼如何在郵箱或者手機号碼這個字元串上建立索引才能保證性能最佳呢？

今天這篇文章就來探讨一下在Mysql中如何給一個字元串加索引才能達到性能最佳。

本文首發于作者的微信公衆号【碼猿技術專欄】，原創不易，喜歡的朋友支援一下，謝謝！！！

陳某将會從什麼是字首索引、字首索引和普通索引的比較、如何建麗最佳性能的字首索引、字首索引對覆寫索引的影響這幾段來講。

字首索引

顧名思義，對于列值較長，比如BLOB、TEXT、VARCHAR，就 "必須" 使用字首索引，即将值的前一部分作為索引。因為索引的存儲也是需要空間的，同樣索引太長維護起來也比較困難。

比如我們給User表中的郵箱添加字首索引，如下:

　　alter table user add index index1(email(7));

上述語句将email的前7個字元作為索引。

字首索引和普通索引比較

我們分别将email的全部作為索引和前7個字元作為索引來看看在性能上有什麼差異。建立索引的語句如下：

　　alter table user add index index1(email);

​

　　alter table user add index index2(email(7));

假設有user表中有這樣幾條資料(id,name,email)：(1,"陳某","chenmou1993@xxx")、(2,"張某","chenmou1994@xxx")、(3,"李某","chenmou1995@xxx")、(4,"王某","chenmou1996@xxx")。

對應于index1和index2的索引樹如下兩張圖：

如果執行下面的查詢語句，Mysql如何利用索引來查詢呢？

　　select * from user where email="chenmou1995@xxx";

【1】普通索引的執行過程

從index1索引樹找到滿足索引值是chenmou1995@xxx的這條記錄，取得id=2的值；

到主鍵上查到主鍵值是id=2的行，判斷email的值是正确的，将這行記錄加入結果集；

取index1索引樹上剛剛查到的位置的下一條記錄，發現已經不滿足email=chenmou1995@xxx的條件了，循環結束。

這個過程中，隻需要回主鍵索引取一次資料，是以系統認為隻掃描了一行。

【2】字首索引的執行過程

從index2索引樹找到滿足索引值是chenmou的記錄，找到的第一個是id=1；

到主鍵上查到主鍵值是id=1的行，判斷出email的值不是chenmou1995@xxx，這行記錄丢棄；

取index2上剛剛查到的位置的下一條記錄，發現仍然是chenmou，取出id=2，再到ID索引上取整行然後判斷，這次值對了，将這行記錄加入結果集；

重複上一步，直到在idxe2上取到的值不是chenmou時，循環結束。

　　在這個過程中，要回主鍵索引取4次資料，也就是掃描了4行。

通過以上查詢的對比，很容易就可以發現，使用字首索引後，可能會導緻查詢語句讀資料的次數變多。

但是對于這個查詢語句來說，如果建立的字首索引的長度為13呢？那麼滿足chenmou1995的記錄隻有一個，這樣就可以直接定位到id=2，此時不但空間縮小了，掃描的行數也減少了。

于是結論就來了：使用字首索引，隻要定義好長度，就可以做到既節省空間，又不用額外增加太多的查詢成本。

那麼如何建立正确的字首索引才能達到最佳的性能呢？接着往下看................

如何建立最佳性能的字首索引

通過上述的比較，可以得出一個結論，建立字首索引的區分度越高越好，意味着重複的鍵值越少。

那麼如何統計區分度，其實很簡單，隻需要判斷資料庫中重複的次數即可。sql如下：

　　select

　　 count(distinct left(email,4)）as L4,

　　 count(distinct left(email,5)）as L5,

 　　count(distinct left(email,6)）as L6,

　　 count(distinct left(email,7)）as L7,

　　from user;

但是如果對于使用字首區分度不太好的情況，比如，我們國家的身份證号，一共18位，其中前6位是位址碼，是以同一個縣的人的身份證号前6位一般會是相同的。 這時候如果對身份證号做長度為6的字首索引的話，這個索引的區分度就非常低了。

按照我們前面說的方法，可能你需要建立長度為12以上的字首索引，才能夠滿足區分度要求。

但是，索引選取的越長，占用的磁盤空間就越大，相同的資料頁能放下的索引值就越少，搜尋的效率也就會越低。

那麼，如果我們能夠确定業務需求裡面隻有按照身份證進行等值查詢的需求，還有沒有别的處理方法呢？這種方法，既可以占用更小的空間，也能達到相同的查詢效率。現在簡單的介紹一種解決此種問題的方式，當然方法肯定不止一種，如下：

　　倒序存儲

　　如果你存儲身份證号的時候把它倒過來存，每次查詢的時候，你可以這麼寫：

　　select field_list from t where id_card = reverse('輸入的身份證号');

　　由于身份證号的最後6位沒有位址碼這樣的重複邏輯，是以最後這6位很可能就提供了足夠的區分度。當然了，實踐中你不要忘記使用count(distinct)方法去做個驗證。

字首索引對覆寫索引的影響

字首索引會導緻覆寫索引失效，查詢語句如下：

　　select id,name from user where email="chenmou1995@xxx";

由于使用了字首索引，是以必須會回表驗證查詢到的時候正确，此處使用了覆寫索引也是無效的。

也就是說，使用字首索引就用不上覆寫索引對查詢性能的優化了，這也是你在選擇是否使用字首索引時需要考慮的一個因素。

總結

如何給字元串加索引是一個需要考量的問題，陳某在這裡給出如下的建議：

如果字元串長度很短，建議直接用全部作為索引。

使用字首索引注意分析區分度，區分度越高越好。

使用字首索引需要考慮覆寫索引失效的問題。

如果覺得作者寫的好，有所收獲的話，點個關注，推薦一波，文章首發于公衆号！！！

原文位址

https://www.cnblogs.com/Chenjiabing/p/12620427.html