如何通過性能調優(yōu)突破MySQL數(shù)據(jù)庫性能瓶頸？
MySQL 數(shù)據(jù)庫瓶頸對 DBA 程序員而言，是非常棘手的問題。要正確的優(yōu)化SQL，我們需要快速定位能性的瓶頸點，也就是說快速找到我們SQL主要的開銷在哪里?下面小編將從數(shù)據(jù)庫數(shù)據(jù)庫性能優(yōu)化的目標和方法兩方面闡述如何通過性能調優(yōu)突破 MySQL 數(shù)據(jù)庫性能瓶頸。
一、優(yōu)化目標
1. 減少 IO 次數(shù)
IO永遠是數(shù)據(jù)庫最容易瓶頸的地方，這是由數(shù)據(jù)庫的職責所決定的，大部分數(shù)據(jù)庫操作中超過90%的時間都是 IO 操作所占用的，減少 IO 次數(shù)是 SQL 優(yōu)化中需要第一優(yōu)先考慮，當然，也是收效最明顯的優(yōu)化手段。
2. 降低 CPU 計算
除了 IO 瓶頸之外，SQL優(yōu)化中需要考慮的就是 CPU 運算量的優(yōu)化了。order by, group by,distinct … 都是消耗 CPU 的大戶(這些操作基本上都是 CPU 處理內存中的數(shù)據(jù)比較運算)。當我們的 IO 優(yōu)化做到一定階段之后，降低 CPU 計算也就成為了我們 SQL 優(yōu)化的重要目標。
二、優(yōu)化方法
1. 改變 SQL 執(zhí)行計劃
明確了優(yōu)化目標之后，我們需要確定達到我們目標的方法。對于 SQL 語句來說，達到上述2個目標的方法其實只有一個，那就是改變 SQL 的執(zhí)行計劃，讓他盡量“少走彎路”，盡量通過各種“捷徑”來找到我們需要的數(shù)據(jù)，以達到 “減少 IO 次數(shù)” 和 “降低 CPU 計算” 的目標
2. order by 一定需要排序操作
我們知道索引數(shù)據(jù)實際上是有序的，如果我們的需要的數(shù)據(jù)和某個索引的順序一致，而且我們的查詢又通過這個索引來執(zhí)行，那么數(shù)據(jù)庫一般會省略排序操作，而直接將數(shù)據(jù)返回，因為數(shù)據(jù)庫知道數(shù)據(jù)已經滿足我們的排序需求了。
實際上，利用索引來優(yōu)化有排序需求的 SQL，是一個非常重要的優(yōu)化手段
3. 盡量少 join
MySQL 的優(yōu)勢在于簡單，但這在某些方面其實也是其劣勢。MySQL 優(yōu)化器效率高，但是由于其統(tǒng)計信息的量有限，優(yōu)化器工作過程出現(xiàn)偏差的可能性也就更多。對于復雜的多表 Join，一方面由于其優(yōu)化器受限，再者在 Join 這方面所下的功夫還不夠，所以性能表現(xiàn)離 Oracle 等關系型數(shù)據(jù)庫前輩還是有一定距離。但如果是簡單的單表查詢，這一差距就會極小甚至在有些場景下要優(yōu)于這些數(shù)據(jù)庫前輩。
4. 盡量少排序
排序操作會消耗較多的 CPU 資源，所以減少排序可以在緩存命中率高等 IO 能力足夠的場景下會較大影響 SQL 的響應時間。
對于MySQL來說，減少排序有多種辦法，比如：
通過利用索引來排序的方式進行優(yōu)化：減少參與排序的記錄條數(shù)，非必要不對數(shù)據(jù)進行排序
避免使用耗費資源的操作，帶有DISTINCT,UNION,MINUS,INTERSECT,ORDER BY的SQL語句會啟動SQL引擎 執(zhí)行，耗費資源的排序(SORT)功能。DISTINCT需要一次排序操作, 而其他的至少需要執(zhí)行兩次排
5. 盡量用 join 代替子查詢
雖然 Join 性能并不佳，但是和 MySQL 的子查詢比起來還是有非常大的性能優(yōu)勢。MySQL 的子查詢執(zhí)行計劃一直存在較大的問題，雖然這個問題已經存在多年，但是到目前已經發(fā)布的所有穩(wěn)定版本中都普遍存在，一直沒有太大改善。雖然官方也在很早就承認這一問題，并且承諾盡快解決，但是至少到目前為止我們還沒有看到哪一個版本較好的解決了這一問題。
6. 盡量少 or
當 where 子句中存在多個條件以“或”并存的時候，MySQL 的優(yōu)化器并沒有很好的解決其執(zhí)行計劃優(yōu)化問題，再加上 MySQL 特有的 SQL 與 Storage 分層架構方式，造成了其性能比較低下，很多時候使用 union all 或者是union(必要的時候)的方式來代替“or”會得到更好的效果。
7. 盡量用 union all 代替 union
union 和 union all 的差異主要是前者需要將兩個(或者多個)結果集合并后再進行唯一性過濾操作，這就會涉及到排序，增加大量的 CPU 運算，加大資源消耗及延遲。所以當我們可以確認不可能出現(xiàn)重復結果集或者不在乎重復結果集的時候，盡量使用 union all 而不是 union。
8. 盡可能對每一條運行在數(shù)據(jù)庫中的SQL進行 explain
優(yōu)化 SQL，需要做到心中有數(shù)，知道 SQL 的執(zhí)行計劃才能判斷是否有優(yōu)化余地，才能判斷是否存在執(zhí)行計劃問題。在對數(shù)據(jù)庫中運行的 SQL 進行了一段時間的優(yōu)化之后，很明顯的問題 SQL 可能已經很少了，大多都需要去發(fā)掘，這時候就需要進行大量的 explain 操作收集執(zhí)行計劃，并判斷是否需要進行優(yōu)化。
9. 優(yōu)先優(yōu)化高并發(fā)的 SQL，而不是執(zhí)行頻率低某些“大”SQL
對于破壞性來說，高并發(fā)的 SQL 總是會比低頻率的來得大，因為高并發(fā)的 SQL 一旦出現(xiàn)問題，甚至不會給我們任何喘息的機會就會將系統(tǒng)壓跨。而對于一些雖然需要消耗大量 IO 而且響應很慢的 SQL，由于頻率低，即使遇到，最多就是讓整個系統(tǒng)響應慢一點，但至少可能撐一會兒，讓我們有緩沖的機會。
10. 從全局出發(fā)優(yōu)化，而不是片面調整
SQL 優(yōu)化不能是單獨針對某一個進行，而應充分考慮系統(tǒng)中所有的 SQL，尤其是在通過調整索引優(yōu)化 SQL 的執(zhí)行計劃的時候，千萬不能顧此失彼，因小失大。
三、總結
對于MySQL數(shù)據(jù)庫進行性能優(yōu)化非常重要，一方面可以提升資源使用率，另一方面可以提升業(yè)務訪問速度提升用戶體驗。除此之外為了保障業(yè)務正常穩(wěn)定的運行，同樣重要的是能夠及時的發(fā)現(xiàn)數(shù)據(jù)庫存在的性能瓶頸，例如：數(shù)據(jù)庫 IOPS 使用率過高，數(shù)據(jù)庫存在存在慢查詢等...
