MySQL效能優化之索引設計

IT王小二發表於2022-03-08
MySql優化索引

作者：IT王小二

部落格：https://itwxe.com

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

聽我的，一個人說了算

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

一、索引設計基石

索引設計基石是什麼呢？

  • 小二認為索引設計的基石就是資料表欄位型別的合理設計，即選擇合適欄位型別和設定合適的長度。
  • 選擇正確的資料型別，那麼在欄位上建立索引時，一個資料頁可以儲存更多的索引，一次讀取載入到記憶體的索引個數更多，同時降低B+tree的高度，減少磁碟IO，對提升MySQL的效能提升有著極大的意義。

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

  • 確定合適的大型別：數字、字串、日期和時間、二進位制等。
  • 確定具體的型別：有無符號、取值範圍、變長定長等。
  • 儘量選擇更小的資料型別，因為它們通常有更好的效能，佔用更少的硬體資源。
  • 儘量把欄位定義為NOT NULL，避免使用NULL。

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

1. 數值型別

型別 大小(bytes) 範圍(有符號) 範圍(無符號) 用途
TINYINT 1 (-128, 127) (0, 255) 小整數值
SMALLINT 2 (-32768, 32767) (0, 65535) 大整數值
MEDIUMINT 3 (-8388608, 8388 607) (0, 16777215) 大整數值
INT或INTEGER 4 (-2147483648, 2147483647) (0, 4294967295) 大整數值
BIGINT 8 (-9233372036854775808, 9223372036854775807) (0, 18446744073 709551615) 極大整數值
FLOAT 4 (-3.402823466E+38, 1.175494351E-38)，0，(1.175494351E-38，3.402823466351E+38) 0, (1.175494351E-38, 3.402823466E+38) 單精度浮點數值
DOUBLE 8 (1.7976931348623157E+308, 2.2250738585072014E-308), 0, (2.2250738585072014E-308, 1.7976931348623157E+308) 0, (2.2250738585072014E-308, 1.7976931348623157E+308) 雙精度浮點數值
DECIMAL 對DECIMAL(M,D) ，如果M>D，為M+2否則為D+2 依賴於M和D的值 依賴於M和D的值 小數值

優化建議：

  • 如果整型資料沒有負數，如id號，建議指定為UNSIGNED無符號型別，容量可以擴大一倍。
  • 整數通常是最佳的資料型別，因為它速度快，並且能使用AUTO_INCREMENT。
  • 建議使用TINYINT代替ENUM、BITENUM、SET。
  • 建議使用整型型別來運算和儲存實數，一種方法是實數乘以相應的倍數後再操作；另外一種方法是使用兩個欄位來分別儲存整數位和小數位。
  • DECIMAL最適合儲存準確度要求高並且用於計算的資料，比如價格、金額等，但是在使用DECIMAL型別的時候注意長度設定。
  • 避免使用整數的顯示寬度，也就是說不要用INT(5)類似的方法指定欄位顯示寬度，直接用INT。

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

CREATE TABLE user(
	id TINYINT(2) UNSIGNED
);

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

CREATE TABLE user(
	id TINYINT(2) UNSIGNED ZEROFILL
);

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

2. 字串型別

型別 大小(bytes) 用途
CHAR 0-255 定長字串，char(n)當插入的字元數不足n時(n代表字元數)，插入空格進行補充儲存。在進行檢索時，尾部的空格會被去掉。
VARCHAR 0-65535 變長字串，varchar(n)中的n代表最大字元數，插入的字元數不足n時不會補充空格
TINYBLOB 0-255 不超過 255 個字元的二進位制字串
TINYTEXT 0-255 短文字字串
BLOB 0-65535 二進位制形式的長文字資料
TEXT 0-65535 長文字資料
MEDIUMBLOB 0-16777215 二進位制形式的中等長度文字資料
MEDIUMTEXT 0-16777215 中等長度文字資料
LONGBLOB 0-4294967295 二進位制形式的極大文字資料
LONGTEXT 0-4294967295 極大文字資料

優化建議：

  • 當字串短，並且所有值都固定一個長度或者接近一個長度時使用CHAR，當然要是如果沒有完全可以使用整型來儲存；字串長度相差較大時使用VARCHAR。
  • CHAR和VARCHAR適合長度不超過255個字元唱的的任意字母和數字組合，例如人名、電話號碼、編碼等。用來計算的數字不要用VARCHAR型別儲存，因為可能會導致一些與計算相關的問題，同時可能影響到計算的準確性和完整性。
  • VARCHAR(255)在建立索引時會佔用比較多的儲存空間，在不要求保證資料完全精確的境況下可以使用字首索引。例如idx_name_age_position(name(20), age, position)，取前20個字元作為索引，但是這種情況下因為是不完全欄位，所以order by name asc或者group by name 排序過程無法使用索引排序。當然需要保證資料的精確性和查詢速度，最優的方案就是使用全文搜尋引擎ES了。
  • 儘量不用BLOB和TEXT，如果實在要用可以考慮將BLOB和TEXT欄位單獨存一張表，使用主鍵id來關聯。
  • BLOB和TEXT都不能有預設值。BLOB系列儲存二進位制字串，與字符集無關；TEXT系列儲存非二進位制字串，與字符集相關。

3. 時間型別

型別 大小(bytes) 範圍 格式 用途
DATE 3 1000-01-01 到 9999-12-31 YYYY-MM-DD 日期值
TIME 3 '-838:59:59' 到 '838:59:59' HH:MM:SS 時間值或持續時間
YEAR 1 1901 到 2155 YYYY 年份值
DATETIME 8 1000-01-01 00:00:00 到 9999-12-31 23:59:59 YYYY-MM-DD HH:MM:SS 混合日期和時間值
TIMESTAMP 4 1970-01-01 00:00:00 到 2038-01-19 03:14:07 (格林尼時間) YYYYMMDD HHMMSS 混合日期和時間值，時間戳

優化建議：

  • MySQL能儲存的最小時間粒度為秒。當然要是需要精確到毫秒級的話，當然也是有辦法的，新加一列在另外一列保留毫秒值即可。
  • 建議使用MySQL的內建型別DATE、TIME、DATETIME來儲存時間，而不是使用字串或者儲存時間戳，這樣的話可以通過MySQL的日期函式處理相關邏輯。
  • 當不需要具體時間時，建議用DATE資料型別來儲存日期，MySQL中預設的日期格式是YYYY-MM-DD。
  • 當資料格式為TIMESTAMP和DATETIME時，可以用CURRENT_TIMESTAMP作為(MySQL5.6以後)，MySQL會自動返回記錄插入的當前確切時間。不過需要注意的是校準MySQL執行環境的時間和時區，比如Linux時間或者docker容器的時間和時區。
  • TIMESTAMP是UTC時間戳，與時區相關；DATETIME的儲存格式是一個YYYYMMDD HH:MM:SS的整數，與時區無關，存的什麼讀出來就是什麼。
  • 一般的短期專案或者小公司專案小二建議使用TIMESTAMP，因為這種專案生命往往活不到2038年，DATETIME還更節約空間。但是如果是騰訊、阿里、京東一般會用DATETIME，因為不用考慮TIMESTAMP將來的時間上限問題。

二、索引設計原則

1. 索引未建，程式碼先行

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

2. 聯合索引儘量覆蓋條件

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

3. 不要在小基數字段上建立索引

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

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

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

4. where與order by衝突時優先where

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

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

5. 對慢查詢SQL進行優化

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

三、索引設計實戰

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

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

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

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

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

我信你個鬼

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

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

select xx from user where xx=xx and xx=xx order by xx asc limit xx,xx;

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

例如通常小夥伴們都會優先篩選出自己所屬城市和性別的人，那麼該怎麼設計索引呢？

where province = xx and city = xx and sex = xx

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

那麼這個時候小夥伴肯定又要瞅瞅年齡段了，嘿嘿?

where province = xx and city = xx and sex = xx and age >= 18 and age <= 28

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

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

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

province=xx and city=xx and sex in ('male','female') and age >= 18 and age <= 28

那麼有愛好之類的其他等值欄位。

province=xx and city=xx and sex in ('male','female') and hobby = 1 and xx = xx and age >= 18 and age <= 28

咋辦嘞，同樣往聯合索引裡面塞，例如(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事務和鎖機制。

相關文章