MySQL 數(shù)據(jù)庫(kù)瓶頸對(duì) DBA 程序員而言，是非常棘手的問(wèn)題。要正確的優(yōu)化SQL，我們需要快速定位能性的瓶頸點(diǎn)，也就是說(shuō)快速找到我們SQL主要的開(kāi)銷在哪里?下面小編將從數(shù)據(jù)庫(kù)數(shù)據(jù)庫(kù)性能優(yōu)化的目標(biāo)和方法兩方面闡述如何通過(guò)性能調(diào)優(yōu)突破 MySQL 數(shù)據(jù)庫(kù)性能瓶頸。

[[273526]]

一、優(yōu)化目標(biāo)

1. 減少 IO 次數(shù)

IO永遠(yuǎn)是數(shù)據(jù)庫(kù)最容易瓶頸的地方，這是由數(shù)據(jù)庫(kù)的職責(zé)所決定的，大部分?jǐn)?shù)據(jù)庫(kù)操作中超過(guò)90%的時(shí)間都是 IO 操作所占用的，減少 IO 次數(shù)是 SQL 優(yōu)化中需要第一優(yōu)先考慮，當(dāng)然，也是收效最明顯的優(yōu)化手段。

2. 降低 CPU 計(jì)算

除了 IO 瓶頸之外，SQL優(yōu)化中需要考慮的就是 CPU 運(yùn)算量的優(yōu)化了。order by, group by,distinct … 都是消耗 CPU 的大戶(這些操作基本上都是 CPU 處理內(nèi)存中的數(shù)據(jù)比較運(yùn)算)。當(dāng)我們的 IO 優(yōu)化做到一定階段之后，降低 CPU 計(jì)算也就成為了我們 SQL 優(yōu)化的重要目標(biāo)。

[[273527]]

二、優(yōu)化方法

1. 改變 SQL 執(zhí)行計(jì)劃

明確了優(yōu)化目標(biāo)之后，我們需要確定達(dá)到我們目標(biāo)的方法。對(duì)于 SQL 語(yǔ)句來(lái)說(shuō)，達(dá)到上述2個(gè)目標(biāo)的方法其實(shí)只有一個(gè)，那就是改變 SQL 的執(zhí)行計(jì)劃，讓他盡量“少走彎路”，盡量通過(guò)各種“捷徑”來(lái)找到我們需要的數(shù)據(jù)，以達(dá)到 “減少 IO 次數(shù)” 和 “降低 CPU 計(jì)算” 的目標(biāo)

2. order by 一定需要排序操作

我們知道索引數(shù)據(jù)實(shí)際上是有序的，如果我們的需要的數(shù)據(jù)和某個(gè)索引的順序一致，而且我們的查詢又通過(guò)這個(gè)索引來(lái)執(zhí)行，那么數(shù)據(jù)庫(kù)一般會(huì)省略排序操作，而直接將數(shù)據(jù)返回，因?yàn)閿?shù)據(jù)庫(kù)知道數(shù)據(jù)已經(jīng)滿足我們的排序需求了。

實(shí)際上，利用索引來(lái)優(yōu)化有排序需求的 SQL，是一個(gè)非常重要的優(yōu)化手段

3. 盡量少 join

MySQL 的優(yōu)勢(shì)在于簡(jiǎn)單，但這在某些方面其實(shí)也是其劣勢(shì)。MySQL 優(yōu)化器效率高，但是由于其統(tǒng)計(jì)信息的量有限，優(yōu)化器工作過(guò)程出現(xiàn)偏差的可能性也就更多。對(duì)于復(fù)雜的多表 Join，一方面由于其優(yōu)化器受限，再者在 Join 這方面所下的功夫還不夠，所以性能表現(xiàn)離 Oracle 等關(guān)系型數(shù)據(jù)庫(kù)前輩還是有一定距離。但如果是簡(jiǎn)單的單表查詢，這一差距就會(huì)極小甚至在有些場(chǎng)景下要優(yōu)于這些數(shù)據(jù)庫(kù)前輩。

4. 盡量少排序

排序操作會(huì)消耗較多的 CPU 資源，所以減少排序可以在緩存命中率高等 IO 能力足夠的場(chǎng)景下會(huì)較大影響 SQL 的響應(yīng)時(shí)間。

對(duì)于MySQL來(lái)說(shuō)，減少排序有多種辦法，比如：

通過(guò)利用索引來(lái)排序的方式進(jìn)行優(yōu)化：減少參與排序的記錄條數(shù)，非必要不對(duì)數(shù)據(jù)進(jìn)行排序

避免使用耗費(fèi)資源的操作，帶有DISTINCT,UNION,MINUS,INTERSECT,ORDER BY的SQL語(yǔ)句會(huì)啟動(dòng)SQL引擎 執(zhí)行，耗費(fèi)資源的排序(SORT)功能。DISTINCT需要一次排序操作, 而其他的至少需要執(zhí)行兩次排

[[273528]]

5. 盡量用 join 代替子查詢

雖然 Join 性能并不佳，但是和 MySQL 的子查詢比起來(lái)還是有非常大的性能優(yōu)勢(shì)。MySQL 的子查詢執(zhí)行計(jì)劃一直存在較大的問(wèn)題，雖然這個(gè)問(wèn)題已經(jīng)存在多年，但是到目前已經(jīng)發(fā)布的所有穩(wěn)定版本中都普遍存在，一直沒(méi)有太大改善。雖然官方也在很早就承認(rèn)這一問(wèn)題，并且承諾盡快解決，但是至少到目前為止我們還沒(méi)有看到哪一個(gè)版本較好的解決了這一問(wèn)題。

6. 盡量少 or

當(dāng) where 子句中存在多個(gè)條件以“或”并存的時(shí)候，MySQL 的優(yōu)化器并沒(méi)有很好的解決其執(zhí)行計(jì)劃優(yōu)化問(wèn)題，再加上 MySQL 特有的 SQL 與 Storage 分層架構(gòu)方式，造成了其性能比較低下，很多時(shí)候使用 union all 或者是union(必要的時(shí)候)的方式來(lái)代替“or”會(huì)得到更好的效果。

7. 盡量用 union all 代替 union

union 和 union all 的差異主要是前者需要將兩個(gè)(或者多個(gè))結(jié)果集合并后再進(jìn)行唯一性過(guò)濾操作，這就會(huì)涉及到排序，增加大量的 CPU 運(yùn)算，加大資源消耗及延遲。所以當(dāng)我們可以確認(rèn)不可能出現(xiàn)重復(fù)結(jié)果集或者不在乎重復(fù)結(jié)果集的時(shí)候，盡量使用 union all 而不是 union。

8. 盡可能對(duì)每一條運(yùn)行在數(shù)據(jù)庫(kù)中的SQL進(jìn)行 explain

優(yōu)化 SQL，需要做到心中有數(shù)，知道 SQL 的執(zhí)行計(jì)劃才能判斷是否有優(yōu)化余地，才能判斷是否存在執(zhí)行計(jì)劃問(wèn)題。在對(duì)數(shù)據(jù)庫(kù)中運(yùn)行的 SQL 進(jìn)行了一段時(shí)間的優(yōu)化之后，很明顯的問(wèn)題 SQL 可能已經(jīng)很少了，大多都需要去發(fā)掘，這時(shí)候就需要進(jìn)行大量的 explain 操作收集執(zhí)行計(jì)劃，并判斷是否需要進(jìn)行優(yōu)化。

9. 優(yōu)先優(yōu)化高并發(fā)的 SQL，而不是執(zhí)行頻率低某些“大”SQL

對(duì)于破壞性來(lái)說(shuō)，高并發(fā)的 SQL 總是會(huì)比低頻率的來(lái)得大，因?yàn)楦卟l(fā)的 SQL 一旦出現(xiàn)問(wèn)題，甚至不會(huì)給我們?nèi)魏未⒌臋C(jī)會(huì)就會(huì)將系統(tǒng)壓跨。而對(duì)于一些雖然需要消耗大量 IO 而且響應(yīng)很慢的 SQL，由于頻率低，即使遇到，最多就是讓整個(gè)系統(tǒng)響應(yīng)慢一點(diǎn)，但至少可能撐一會(huì)兒，讓我們有緩沖的機(jī)會(huì)。

10. 從全局出發(fā)優(yōu)化，而不是片面調(diào)整

SQL 優(yōu)化不能是單獨(dú)針對(duì)某一個(gè)進(jìn)行，而應(yīng)充分考慮系統(tǒng)中所有的 SQL，尤其是在通過(guò)調(diào)整索引優(yōu)化 SQL 的執(zhí)行計(jì)劃的時(shí)候，千萬(wàn)不能顧此失彼，因小失大。

三、總結(jié)

對(duì)于MySQL數(shù)據(jù)庫(kù)進(jìn)行性能優(yōu)化非常重要，一方面可以提升資源使用率，另一方面可以提升業(yè)務(wù)訪問(wèn)速度提升用戶體驗(yàn)。除此之外為了保障業(yè)務(wù)正常穩(wěn)定的運(yùn)行，同樣重要的是能夠及時(shí)的發(fā)現(xiàn)數(shù)據(jù)庫(kù)存在的性能瓶頸，例如：數(shù)據(jù)庫(kù) IOPS 使用率過(guò)高，數(shù)據(jù)庫(kù)存在存在慢查詢等...