我們不能等著系統(tǒng)上線，慢 SQL 吃光數(shù)據(jù)庫資源之后，再找出慢 SQL 來改進，那樣就晚了。那么，怎樣才能在開發(fā)階段盡量避免寫出慢 SQL 呢？
定量認識 MySQL
一臺 MySQL 數(shù)據(jù)庫，大致處理能力的極限是，每秒一萬條左右的簡單 SQL，這里的“簡單 SQL”，指的是類似于主鍵查詢這種不需要遍歷很多條記錄的 SQL。根據(jù)服務器的配置高低，可能低端的服務器只能達到每秒幾千條，高端的服務器可以達到每秒鐘幾萬條，所以這里給出的一萬 TPS 是中位數(shù)的經(jīng)驗值。考慮到正常的系統(tǒng)不可能只有簡單 SQL，所以實際的 TPS 還要打很多折扣。
我的經(jīng)驗數(shù)據(jù)，一般一臺 MySQL 服務器，平均每秒鐘執(zhí)行的 SQL 數(shù)量在幾百左右，就已經(jīng)是非常繁忙了，即使看起來 CPU 利用率和磁盤繁忙程度沒那么高，你也需要考慮給數(shù)據(jù)庫“減負”了。
另外一個重要的定量指標是，到底多慢的 SQL 才算慢 SQL。這里面這個“慢”，衡量的單位本來是執(zhí)行時長，但是時長這個東西，我們在編寫 SQL 的時候并不好去衡量。那我們可以用執(zhí)行 SQL 查詢時，需要遍歷的數(shù)據(jù)行數(shù)替代時間作為衡量標準，因為查詢的執(zhí)行時長基本上是和遍歷的數(shù)據(jù)行數(shù)正相關的。
你在編寫一條查詢語句的時候，可以依據(jù)你要查詢數(shù)據(jù)表的數(shù)據(jù)總量，估算一下這條查詢大致需要遍歷多少行數(shù)據(jù):
- 如果遍歷行數(shù)在百萬以內的，只要不是每秒鐘都要執(zhí)行幾十上百次的頻繁查詢，可以認為是安全的。
- 遍歷數(shù)據(jù)行數(shù)在幾百萬的，查詢時間最少也要幾秒鐘，你就要仔細考慮有沒有優(yōu)化的辦法。
- 遍歷行數(shù)達到千萬量級和以上的，我只能告訴你，這種查詢就不應該出現(xiàn)在你的系統(tǒng)中。當然我們這里說的都是在線交易系統(tǒng)，離線分析類系統(tǒng)另說。
遍歷行數(shù)在千萬左右，是 MySQL 查詢的一個坎兒。MySQL 中單個表數(shù)據(jù)量，也要盡量控制在一千萬條以下，最多不要超過二三千萬這個量級。原因也很好理解，對一個千萬級別的表執(zhí)行查詢，加上幾個 WHERE 條件過濾一下，符合條件的數(shù)據(jù)最多可能在幾十萬或者百萬量級，這還可以接受。但如果再和其他的表做一個聯(lián)合查詢，遍歷的數(shù)據(jù)量很可能就超過千萬級別了。所以，每個表的數(shù)據(jù)量最好小于千萬級別。
使用索引避免全表掃描
絕大多數(shù)情況下，我們編寫的查詢語句，都應該使用索引，避免去遍歷整張表，也就是通常說的，避免全表掃描。你在每次開發(fā)新功能，需要給數(shù)據(jù)庫增加一個新的查詢時，都要評估一下，是不是有索引可以支撐新的查詢語句，如果有必要的話，需要新建索引來支持新增的查詢。
增加索引付出的代價是，會降低數(shù)據(jù)插入、刪除和更新的性能。這個也很好理解，增加了索引，在數(shù)據(jù)變化的時候，不僅要變更數(shù)據(jù)表里的數(shù)據(jù)，還要去變更每個索引。所以，對于更新頻繁并且對更新性能要求較高的表，可以盡量少建索引。而對于查詢較多更新較少的表，可以根據(jù)查詢的業(yè)務邏輯，適當多建一些索引。
分析 SQL 執(zhí)行計劃
在 MySQL 中使用執(zhí)行計劃也非常簡單，只要在你的 SQL 語句前面加上 EXPLAIN 關鍵字，然后執(zhí)行這個查詢語句就可以了。
比如有一個用戶表，包含用戶 ID、姓名、部門編號和狀態(tài)這幾個字段：
我們希望查詢某個二級部門下的所有人，查詢條件就是，部門代號以 00028 開頭的所有人。下面這兩個 SQL，他們的查詢結果是一樣的，都滿足要求，但是，哪個查詢性能更好呢？
SELECT * FROM user WHERE left(department_code, 5) = '00028';
SELECT * FROM user WHERE department_code LIKE '00028%';
我們分別查看一下這兩個 SQL 的執(zhí)行計劃：
row 列:
- MySQL 預估執(zhí)行這個 SQL 可能會遍歷的數(shù)據(jù)行數(shù)。第一個 SQL 遍歷了四千多行，這就是整個 User 表的數(shù)據(jù)條數(shù)；第二個 SQL 只有 8 行，這 8 行其實就是符合條件的 8 條記錄。顯然第二個 SQL 查詢性能要遠遠好于第一個 SQL。
type 列:
- 表示這個查詢的訪問類型。ALL 代表全表掃描，這是最差的情況。range 代表使用了索引，在索引中進行范圍查找，因為第二個 SQL 語句的 WHERE 中有一個 LIKE 的查詢條件。如果直接命中索引，type 這一列顯示的是 index。如果使用了索引，可以在 key 這一列中看到，實際上使用了哪個索引。
總結
在開發(fā)階段，衡量一個 SQL 查詢語句查詢性能的手段是，估計執(zhí)行 SQL 時需要遍歷的數(shù)據(jù)行數(shù)。遍歷行數(shù)在百萬以內，可以認為是安全的 SQL，百萬到千萬這個量級則需要仔細評估和優(yōu)化，千萬級別以上則是非常危險的。為了減少慢 SQL 的可能性，每個數(shù)據(jù)表的行數(shù)最好控制在千萬以內。 索引可以顯著減少查詢遍歷數(shù)據(jù)的數(shù)量，所以提升 SQL 查詢性能最有效的方式就是，讓查詢盡可能多的命中索引，但索引也是一把雙刃劍，它在提升查詢性能的同時，也會降低數(shù)據(jù)更新的性能。 對于復雜的查詢，最好使用 SQL 執(zhí)行計劃，事先對查詢做一個分析。在 SQL 執(zhí)行計劃的結果中，可以看到查詢預估的遍歷行數(shù)，命中了哪些索引。執(zhí)行計劃也可以很好地幫助你優(yōu)化你的查詢語句。
