今天，我們就來到了MySQL的最后一部分——MySQL性能！

下圖是我們涉及到的知識點，主要也是根據(jù)我們實際工作中運用比較多，或者經(jīng)常遇到的問題提出的。相比之前的理論知識，可以說實用性和實戰(zhàn)性非常強了！

MySQL上手起來其實很快，但是要深入研究還是不容易，性能調(diào)優(yōu)就是最大的攔路虎，搞定了這只攔路虎，我們就能把MySQL運用自如了。

好了，話不多說，現(xiàn)在，我們就繼續(xù)來體驗這場MySQL的沉浸式面試吧！

性能常識和調(diào)優(yōu)思路

MySQL性能怎么樣？

嗯……我之前做過測試，在MySQL5.5版本，普通8核16G的機器，一張100萬的常規(guī)表，順序?qū)懶阅?/strong>在2000tps，讀性能的話，如果索引有效，tps在5000左右。

當(dāng)然，實際性能取決于表結(jié)構(gòu)、SQL語句以及索引過濾等具體情況，需要以測試結(jié)果為準。

不同版本的MySQL性能差距非常大，不同云廠商提供MySQL做的優(yōu)化也不盡相同，不同業(yè)務(wù)數(shù)據(jù)模型也有區(qū)別，只有經(jīng)過測試的數(shù)據(jù)才有意義。

下圖是ucloud團隊針對MySQL的基礎(chǔ)測試，我們在實際使用中最好也實測一下。

那下面來談?wù)凪ySQL的調(diào)優(yōu)思路吧。

主要有三個維度：首先，針對SQL語句進行優(yōu)化，包括索引優(yōu)化、特定查詢優(yōu)化；其次，是對頻率控制優(yōu)化，包括讀緩存，寫緩沖；最后，如果規(guī)模過大，就分庫分表。

那要怎么找到MySQL執(zhí)行慢的語句呢？

我們可以看慢查詢?nèi)罩?#xff0c;它是MySQL提供的一種日志記錄，用來記錄在MySQL中響應(yīng)時間超過閥值的語句，這個閾值通常默認為10s，也可以按需配置。

Mysql是默認關(guān)閉慢查詢?nèi)罩镜?#xff0c;所以需要我們手動開啟。

那找到慢語句之后，怎么查看它的執(zhí)行計劃？

使用explain命令，它可以獲取到MySQL語句的執(zhí)行計劃 ，包括會使用的索引、掃描行數(shù)、表如何連接等信息。

通過這個命令，我們很容易就看出一條語句是否使用了我們預(yù)期的索引，并進行相應(yīng)的調(diào)整。

怎么調(diào)整呢？

數(shù)據(jù)是在不斷變化的，同時執(zhí)行器也有判斷失誤的情況，MySQL有時候的執(zhí)行計劃，會出乎意料。

這種情況，我們可以使用語句強行指定索引：

select xx from table_name force index (index_name) where ...

那MySQL索引對性能影響大嗎？

索引可以說是給MySQL的性能插上了翅膀。沒有索引查找一個Key時間復(fù)雜度需要O(n)，有索引就降低到了O(logn)。

在數(shù)據(jù)少的時候還不明顯，多一些數(shù)據(jù)，比如100萬條數(shù)據(jù)，不走索引需要遍歷100萬條數(shù)據(jù)，如果能走索引，只需要查找1000條數(shù)據(jù)。所以有無索引，性能天差地別。

MySQL如果查詢壓力太大該怎么辦？

如果SQL語句已經(jīng)足夠優(yōu)秀。那么就看請求壓力是否符合二八原則，也就是說80%的壓力都集中在20%的數(shù)據(jù)。

如果是，我們可以增加一層緩存，常用的實現(xiàn)是在MySQL前加個Redis緩存。當(dāng)然，如果實在太大了，那么只能考慮分庫分表啦。

如果是寫入壓力太大呢？

寫緩沖。一般而言可以增加消息隊列來緩解。這樣做有兩個好處，一個是緩解數(shù)據(jù)庫壓力，第二個可以控制消費頻率。

性能實戰(zhàn)

如果發(fā)現(xiàn)線上Insert導(dǎo)致cpu很高，你會怎么解決？

1.查看是不是請求量突然飆升導(dǎo)致，如果是攻擊，則增加對應(yīng)的防護；

2.查看是否因為數(shù)據(jù)規(guī)模達到一個閾值，導(dǎo)致MySQL的處理能力發(fā)生了下降；

3.查看二級索引是否建立過多，這種情況需要去清理非必要索引。

為什么二級索引過多，會導(dǎo)致性能下降？

因為一個二級索引，就相當(dāng)于一棵B+樹。如果我們建了10個索引，這10個索引就相當(dāng)于10次隨機I/O，那粗略估算性能至少也會慢10倍。

分頁操作為什么在offset過大的時候會很慢？

以offset 10000, limit 10為例。慢的原因有兩點：第一，由于offset是其實就是先找到第幾大的數(shù)字，因此沒法使用樹的結(jié)構(gòu)來快速檢索。只能使用底層鏈表順序找10000個節(jié)點，時間復(fù)雜度O(n)，

其次，即使這10000個節(jié)點是不需要的，MySQL也會通過二級索引上的主鍵id，去聚簇索引上查一遍數(shù)據(jù)，這可是10000次隨機I/O，自然慢成哈士奇。這和它的優(yōu)化器有關(guān)系，也算是MySQL的一個大坑，時至今日，也沒有優(yōu)化。

那我們怎么優(yōu)化呢？

一般有兩種優(yōu)化方案：

方案一：繞過去。將分頁替換為上一頁、下一頁。這樣子就可以通過和上次返回數(shù)據(jù)進行比較，搭上樹索引的便車。在ios，android端，上下頁是很常見的。

方案二：正面剛。有一個概念叫索引覆蓋，就是當(dāng)輔助索引查詢的數(shù)據(jù)只有主鍵id和輔助索引本身，那么就不必再去查聚簇索引。

如此一來，減少了offset時10000次隨機I/O，只有l(wèi)imit出來的10個主鍵id會去查詢聚簇索引，這樣只會十次隨機I/O，可以大幅提升性能，通常能滿足業(yè)務(wù)要求。

那你說一下這兩種方式的優(yōu)缺點吧。

本質(zhì)上來說，上下頁方案屬于產(chǎn)品設(shè)計優(yōu)化。索引覆蓋是技術(shù)方案優(yōu)化。

上下頁方案能利用樹的分支結(jié)構(gòu)實現(xiàn)快速過濾，還能直接通過主鍵索引查找，性能會高很多。但是它的使用場景受限，而且把主鍵ID暴露了。

索引覆蓋方案維持了分頁需求，適用場景更大，性能也提升了不少，但二級索引還是會走下層鏈表遍歷。

如果產(chǎn)品本身，可以接受上下頁頁面結(jié)構(gòu)，且沒用其它過濾條件，可以用方案一。方案二更具有普適性，同時由于合理分表的大小，一般也就500w，二級索引上O(n)的查找損耗，通常也在可接受范圍。

針對分頁性能問題，《高性能MySQL》中提到了這兩個方案，感興趣的小伙伴可以去看看。如果想成為高級工程師，那么不僅要知道怎么做，還需要對兩者的優(yōu)缺點進行對比，闡述選型思路。

Count操作的性能怎么優(yōu)化？

有幾種查詢場景通用性優(yōu)化方案。

第一種，是用Redis緩存來計數(shù)。每次服務(wù)啟動，就將個數(shù)加載進Redis，當(dāng)然，無論是Cache Aside還是Write Through，緩存和存儲之間都會存在偏差，可以考慮用一個離線任務(wù)來矯正Redis中的個數(shù)。這種方案適用于對數(shù)據(jù)精確度，要求不是特別高的場景。

第二種，為count的篩選條件建立聯(lián)合索引。這樣可以實現(xiàn)索引覆蓋，在二級索引表中就可以得到結(jié)果，不用再回表，回表可是O(n)次隨機I/O呢。這種方案適用于有where條件的情況，并且與其它方案不沖突，可共同使用。

第三種，可以多維護一個計數(shù)表，通過事務(wù)的原子性，維持一個準確的計數(shù)。這種方案適用于對數(shù)據(jù)精度高，讀多寫少場景。

你對MySQL分表有了解嗎？

隨著業(yè)務(wù)持續(xù)擴張，單表性能一定會達到極限，分表是把一個數(shù)據(jù)庫中的數(shù)據(jù)表拆分成多張表，通過分布式思路提供可擴展的性能。

那有哪些分表方式？

通常來說有水平分表、垂直分表兩種劃分方式。

垂直分表將一張表的數(shù)據(jù)，根據(jù)場景切分成多張表，本質(zhì)是由于前期抽象不足，需要將業(yè)務(wù)數(shù)據(jù)進一步拆分。

水平分表則是將一張大表拆成多個結(jié)構(gòu)相同的子表。直觀來看表結(jié)構(gòu)都是一樣的，可以按某個字段來進行業(yè)務(wù)劃分，也可以按照數(shù)據(jù)量來劃分，劃分的規(guī)則實際就是按某種維度，預(yù)判數(shù)據(jù)量進行拆分。

那你做過的項目中，分表邏輯怎么實現(xiàn)的？

分表邏輯一定是在一個公共的，可復(fù)用的位置來實現(xiàn)。我之前做的項目，是實現(xiàn)了一個本地依賴包，即將分表邏輯寫在公共的代碼庫里，每個需要調(diào)用服務(wù)的客戶方都集成該公共包，就接入了自動分表的能力。

優(yōu)點在于簡單，不引入新的組件，不增加運維難度。缺點是公共包更改后每個客戶端都需要更新。

能說出優(yōu)缺點，說明對方案還是比較清楚的。如果想更進一步加分，需要有競爭方案，比如分表是常規(guī)地通過中間件，還是放公共包。

小伙伴們?nèi)粘６嘟Y(jié)合自己的項目思考，可以說是自己業(yè)務(wù)有特殊性，比如需要二維分表，很多中間件不支持，也可以說分表邏輯經(jīng)過評估，是比較固定的，為此引入新的組件反而成本更大，自圓其說，才能凸顯能力。

這里給大家推薦一個開源組件——Mycat，它是一個優(yōu)秀的數(shù)據(jù)庫中間件，其本質(zhì)就是提供代理服務(wù)，對數(shù)據(jù)庫進行訪問，提供包括讀寫分離、分庫分表等能力。

部署容易，耦合性低，感興趣的朋友可以了解一下。

如果初期沒做分表，已有3000W數(shù)據(jù)，此時要分庫分表怎么做？

最復(fù)雜的情況，持續(xù)比較大的訪問流量下，并且要求不停服。我們可以分幾個階段來操作：

1. 雙寫讀老階段：通過中間件，對write sql同時進行兩次轉(zhuǎn)發(fā)，也就是雙寫，保持新數(shù)據(jù)一致，同時開始歷史數(shù)據(jù)拷貝。本階段建議施行一周；

2. 雙寫雙讀階段：采用灰度策略，一部分流量讀老表，一部分流量讀新表，讀新表的部分在一開始，還可以同時多讀一次老表數(shù)據(jù)，進行比對檢查，觀察無誤后，隨著時間慢慢切量到新表。本階段建議施行至少兩周；

3. 雙寫讀新階段：此時基本已經(jīng)穩(wěn)定，可以只讀新表，為了安全保證，建議還是多雙寫一段時間，防止有問題遺漏。本階段建議周期一個月；

4. 寫新讀新階段：此時已經(jīng)完成了分表的遷移，老表數(shù)據(jù)可以做個冷備

看著很簡單的四個步驟，但在業(yè)務(wù)量已經(jīng)比較龐大的情況下，操作也是非常復(fù)雜的。首先為了安全，每一階段通常需要比較大的流轉(zhuǎn)時間，也就是說可能已經(jīng)跨越了多個開發(fā)版本。

其次是會帶來短期性能損失——無論是雙寫，還是讀檢查，都做了額外的數(shù)據(jù)請求。在同樣的請求量下，服務(wù)響應(yīng)時間至少增大了一倍。

面試點評

MySQL的性能測試與調(diào)優(yōu)，是MySQL常見但又高階的內(nèi)容，深入理解MySQL的性能與調(diào)優(yōu)，才能良好的勝任相關(guān)開發(fā)與維護工作。

有道無術(shù)，術(shù)可成；有術(shù)無道，止于術(shù)

歡迎大家關(guān)注Java之道公眾號

好文章，我在看??