金三資料庫

A. MySQL性能優化之索引設計

上一篇給小夥伴們講了關於SQL查詢性能優化的相關技巧，一個好的查詢SQL離不開合理的索引設計。這篇小二就來嘮一嘮怎麼合理的設計一個索引來優化我們的查詢速度，要是有不合理的地方...嗯..

當然啦，開個玩笑，歡迎小夥伴們指正！

通常情況下，欄位類型的選擇是需要根據業務來判斷的，通常需要遵循以下幾點。

下列各種類型表格內容來自菜鳥教程，權當備忘。

優化建議：

注意： INT(2)設置的為顯示寬度，而不是整數的長度，需要配合 ZEROFILL 使用 。

例如 id 設置為 TINYINT(2) UNSIGNED ，表示無符號，可以存儲的最大數值為255，其中 TINYINT(2) 沒有配合 ZEROFILL 實際沒有任何意義，例如插入數字200，長度雖然超過了兩位，但是這個時候是可以插入成功的，查詢結果同樣為200；插入數字5時，同樣查詢結果為5。

而 TINYINT(2) 配合 ZEROFILL 後，當插入數字5時，實際存儲的還是5，不過在查詢是MySQL會在前面補上一個0，即查詢出來的實際為 05 。

優化建議：

優化建議：

通常來說，考慮好表中每個欄位應該使用什麼類型和長度，建完表需要做的事情不是馬上建立索引，而是先把相關主體業務開發完畢，然後把涉及該表的SQL都拿出來分析之後再建立索引。

盡量少建立單值索引( 唯一索引除外 )，應當設計一個或者兩三個聯合索引，讓每一個聯合索引都盡量去包含SQL語句中的 where、order by、group by 的欄位，同時確保聯合索引的欄位順序盡量滿足SQL查詢的最左前綴原則。

索引基數是指這個欄位在表裡總共有多少個不同的值，比如一張表總共100萬行記錄，其中有個性別欄位，性別一共有三個值：男、女、保密，那麼該欄位的基數就是3。

如果對這種小基數欄位建立索引的話，因為索引樹中只有男、女、保密三個值，根本沒法進行快速的二分查找，同時還需要回表查詢，還不如全表掃描嘞。

一般建立索引，盡量使用那些基數比較大的欄位，那麼才能發揮出B+樹快速二分查找的優勢來。

在 where 和 order by 出現索引設計沖突時，是優先針對where去設計索引？還是優先針對order by設計索引？

通常情況下都是優先針對 where 來設計索引，因為通常情況下都是先 where 條件使用索引快速篩選出來符合條件的數據，然後對進行篩選出來的數據進行排序和分組，而 where 條件快速篩選出來的的數據往往不會很多。

對生產實際運行過程中，或者測試環境大數據量測試過程中發現的慢查詢SQL進行特定的索引優化、代碼優化等策略。

終於輪到實戰了，小二最喜歡實戰了。

寫到這里不得不吐槽一下，這個金三銀四的跳槽季節，年前提離職了，結果離職還沒辦完就封村整整兩個禮拜了，嗚嗚嗚...

上節小二就提到會有個很有意思的小案例，那麼在疫情當下，門都出不去的日子，感覺這個例子更有意思了，咱們來討論一下各種社交平台怎麼做的用戶信息搜索呢。

社交平台有一個小夥伴們都喜歡的功能，搜索好友信息，比如小二熟練的點開省份...城市..性別..年齡..身高...

咳咳咳...小二怎麼可能幹這種事情，小二的心裡只有代碼，嗯...沒錯，就是這樣。

這個就可以說是對於用戶信息的查詢篩選了，通常這種表都是非常大數據量的，在不考慮分庫分表的情況下，怎麼通過索引配合SQL來優化呢？

通常我們在編寫SQL是會寫出類似如下的SQL來執行，有 where、order by、limit 等條件來查詢。

那麼接下來小二一個一個慢慢增加欄位來分析分析，怎麼根據業務場景來設計索引。

針對這種情況，很簡單，設計一個聯合索引 (provice, city, sex) 就完事了。

那麼這時候有小夥伴就會說了，很簡單啊，范圍欄位放最後咱還是知道的，聯合索引改成 (provice, city, sex, age) 不就可以了。

嗯，是的，這么干沒毛病，但是小夥伴們有沒有想過有些人萬一既喜歡帥哥又喜歡美女，別想歪了哈...，挺多小姐姐就既喜歡帥哥又喜歡美女的。

那麼這個時候小姐姐就不搜索性別了，那麼這個時候聯合索引只能用到前兩個欄位了，那麼不符合咱們的專業標准啊，咋辦呢？這時候還是有辦法的，咱們只需要動動小腦袋改改SQL就行了，在沒有選擇性別時判斷一下，改成下面這樣就可以了。

咋辦嘞，同樣往聯合索引裡面塞，例如 (provice, city, sex, hobby, xx, age) 。

針對這種多個范圍查詢的話，為了比較好的利用索引，在業務允許的情況下可以使用固定范圍，然後資料庫欄位存儲范圍標識就可以了，這樣就轉化為了等值匹配，就可以很好地利用索引了。

例如最後登錄時間欄位不記錄最後登錄時間，而是記錄設置欄位 is_login_within_seven_days 在7天內有登錄則為1，否則為0，最後索引設計成 (provice, city, sex, hobby, xx, is_login_within_seven_days, age) 。

那麼根據場景最後設計出來的這個索引可能已經可以覆蓋大部分的查詢流量了，那麼如果還有其他一部分熱度比較高的查詢怎麼辦呢，辦法也很簡單啊，再加一兩個索引即可。

例如通常會查詢這個城市比較受歡迎(評分：score)的小姐姐，這時候添加一個聯合索引 (provice, city, sex, score) 那麼就可以了。

可以看出，索引時必須結合場景來設計的，思路就是盡量用不超過3個復雜的聯合索引來抗住大部分的80%以上的常用查詢流量，然後再用一兩個二級索引來抗下一些非常用查詢流量。

以上就是小二要給大家分享的索引設計，如果能動動你發財的小手給小二點個免費的贊就更好啦~

下篇小二就來講講MySQL事務和鎖機制。