MySQL查詢性能優化一則

公司有一套Web系統， 使用方反饋系統某些頁面訪問速度緩慢， 用戶體驗很差， 並且偶爾還會出現HTTP 502錯誤。

這是典型的伺服器端IO阻塞引發的問題，通過對訪問頁面的程序邏輯進行跟蹤，發現問題應該是出在某個SQL查詢上。

在頁面程序運行的某個步驟中，有這樣一段SQL

select distinct(server) n from user_record n where type = GD0001n

user_record表中的數據大概有2000萬條左右 , 欄位type的值為GD0001的記錄大概有500萬，而這段SQL執行的結果大概有30多條。type欄位上有索引，但是SQL語句的執行時間卻要超過一分鐘。

得到去重後server欄位的值是導致頁面訪問緩慢的根本原因。

根據程序的要求， server欄位的值需要實時求得，所以當初在設計程序的時候才會使用這段SQL去獲得結果。數據量少的時候，不會出現問題，然而， 數據增長的速度超出當初的預期，於是就導致了性能問題的出現。

要解決這個問題不難，因為server欄位值的範圍相對是穩定的，可以想辦法把值提取出來放到一個冗餘的表裡面，並且通過某種機制讓這個新表的值與原表中server欄位的值保持同步，查的時候查這個新表， 這樣訪問速度緩慢的問題也就迎刃而解了。

顯然，使用這種方案解決問題需要不小的工作量。要使解決這個問題的成本最小化，最好的方法是優化這個查詢，假如原本這個查詢運行的時間是一分鐘，那麼能使運行這個查詢的時間下降至一秒，問題也算解決。

這個目標看起來似乎難以實現，事實上卻是可以做到的。

select distinct(server) n from user_record n where type = GD0001n

因為這段SQL語句的篩選條件type欄位有索引，所以整個SQL語句的邏輯查詢步驟大致如下

1. 通過type索引篩選出符合要求記錄的主鍵欄位的標識
2. 通過主鍵標識定位到表中記錄的源數據
3. 拿到欄位的值進行distinct去重得到最終的結果。

上面的三個步驟中，最消耗性能的是第二步。因為索引和表的實際數據其實是分開放置的，大概的樣子如下面這個圖。圖中長的最大的那個其實就是數據表，表中所有的數據都在上面，只是看起來不像一張「表」而已。

第二步是通過索引篩選出符合條件的記錄的主鍵標識定位到實際數據，過程大概如下面這張圖

想像一下， 要優化的那段SQL，而type值為GD0001記錄有500萬條， 就算MySQL不會蠢到去查500萬次才能得到結果，但也肯定不是輕輕鬆鬆就能完成的。 如果能優化掉這一步，整個查詢的開銷也就下去了。

select distinct(server) n from user_record n where type = GD0001n

對於這段SQL，我們的目標是並不是得到所有欄位的值，僅僅server欄位的值就足夠了。

假如我們把server欄位的值放在type欄位的索引里，那麼在第一步查索引的時候就能得到第二步的結果。執行過程如下圖

在關係資料庫中，有一種索引稱為覆蓋索引，就是為了滿足這種優化需求而設計的。

針對這段SQL語句優化的覆蓋索引創建語句如下

create index index_type_server n on user_record(type, server)n

這個索引創建語句會將type和server兩個欄位的值組織在一個索引裡面， 因此當

select distinct(server) n from user_record n where type = GD0001n

所有的查詢步驟在索引中就能完成，而不用再去源數據表裡提取數據，也就是在沒建立這個索引時進行查詢的第二步被消除了，因此查詢的性能極大幅度的得到了提升。

在沒建立覆蓋索引前，查詢的時間需要一分鐘以上，在建立索引後，查詢的時間下降到幾百毫秒的級別。原本網頁載入緩慢和偶爾報HTTP 502錯誤失去響應的問題也得到了解決。

讓SQL語句合理的利用索引快速的得到查詢結果是一門學問，值得深究。 合理利用索引，能讓對程序性能的優化從代碼層面轉移到資料庫層面， 讓問題由最適合解決的工具和手段去解決，物盡其用，如此不但能減少代碼複雜度，還能提高解決問題的效率。這是一個程序員必須要具備的一種技能。

延伸閱讀：深入淺出資料庫索引原理