[TOC]
不知道你在實際運維過程中有沒有碰到這樣的情景：業(yè)務高峰期，生產(chǎn)環(huán)境的 MySQL 壓力太大，沒法正常響應，需要短期內(nèi)、臨時性地提升一些性能。

我以前做業(yè)務護航的時候，就偶爾會碰上這種場景。用戶的開發(fā)負責人說，不管你用什么方案，讓業(yè)務先跑起來再說。

但，如果是無損方案的話，肯定不需要等到這個時候才上場。今天我們就來聊聊這些臨時方案，并著重說一說它們可能存在的風險。

短連接風暴

正常的短連接模式就是連接到數(shù)據(jù)庫后，執(zhí)行很少的 SQL 語句就斷開，下次需要的時候再重連。如果使用的是短連接，在業(yè)務高峰期的時候，就可能出現(xiàn)連接數(shù)突然暴漲的情況。

我在第 1 篇文章《基礎架構：一條 SQL 查詢語句是如何執(zhí)行的？》中說過，MySQL 建立連接的過程，成本是很高的。除了正常的網(wǎng)絡連接三次握手外，還需要做登錄權限判斷和獲得這個連接的數(shù)據(jù)讀寫權限。

在數(shù)據(jù)庫壓力比較小的時候，這些額外的成本并不明顯。

但是，短連接模型存在一個風險，就是一旦數(shù)據(jù)庫處理得慢一些，連接數(shù)就會暴漲。max_connections 參數(shù)，用來控制一個 MySQL 實例同時存在的連接數(shù)的上限，超過這個值，系統(tǒng)就會拒絕接下來的連接請求，并報錯提示“Too many connections”。對于被拒絕連接的請求來說，從業(yè)務角度看就是數(shù)據(jù)庫不可用。

在機器負載比較高的時候，處理現(xiàn)有請求的時間變長，每個連接保持的時間也更長。這時，再有新建連接的話，就可能會超過 max_connections 的限制.

碰到這種情況時，一個比較自然的想法，就是調(diào)高 max_connections 的值。但這樣做是有風險的。因為設計 max_connections 這個參數(shù)的目的是想保護 MySQL，如果我們把它改得太大，讓更多的連接都可以進來，那么系統(tǒng)的負載可能會進一步加大，大量的資源耗費在權限驗證等邏輯上，結果可能是適得其反，已經(jīng)連接的線程拿不到 CPU 資源去執(zhí)行業(yè)務的 SQL 請求。

那么這種情況下，你還有沒有別的建議呢？我這里還有兩種方法，但要注意，這些方法都是有損的。

第一種方法：先處理掉那些占著連接但是不工作的線程。

max_connections 的計算，不是看誰在 running，是只要連著就占用一個計數(shù)位置。對于那些不需要保持的連接，我們可以通過 kill connection 主動踢掉。這個行為跟事先設置 wait_timeout 的效果是一樣的。設置 wait_timeout 參數(shù)表示的是，一個線程空閑 wait_timeout 這么多秒之后，就會被 MySQL 直接斷開連接。

但是需要注意，在 show processlist 的結果里，踢掉顯示為 sleep 的線程，可能是有損的。我們來看下面這個例子。

image.png

在上面這個例子里，如果斷開 session A 的連接，因為這時候 session A 還沒有提交，所以 MySQL 只能按照回滾事務來處理；而斷開 session B 的連接，就沒什么大影響。所以，如果按照優(yōu)先級來說，你應該優(yōu)先斷開像 session B 這樣的事務外空閑的連接。

但是，怎么判斷哪些是事務外空閑的呢？session C 在 T 時刻之后的 30 秒執(zhí)行 show processlist，看到的結果是這樣的。

image.png

圖中 id=4 和 id=5 的兩個會話都是 Sleep 狀態(tài)。而要看事務具體狀態(tài)的話，你可以查 information_schema 庫的 innodb_trx 表。

image.png

這個結果里，trx_mysql_thread_id=4，表示 id=4 的線程還處在事務中。

因此，如果是連接數(shù)過多，你可以優(yōu)先斷開事務外空閑太久的連接；如果這樣還不夠，再考慮斷開事務內(nèi)空閑太久的連接。

從服務端斷開連接使用的是 kill connection + id 的命令， 一個客戶端處于 sleep 狀態(tài)時，它的連接被服務端主動斷開后，這個客戶端并不會馬上知道。直到客戶端在發(fā)起下一個請求的時候，才會收到這樣的報錯“ERROR 2013 (HY000): Lost connection to MySQL server during query”。

從數(shù)據(jù)庫端主動斷開連接可能是有損的，尤其是有的應用端收到這個錯誤后，不重新連接，而是直接用這個已經(jīng)不能用的句柄重試查詢。這會導致從應用端看上去，“MySQL 一直沒恢復”。

所以，如果你是一個支持業(yè)務的 DBA，不要假設所有的應用代碼都會被正確地處理。即使只是一個斷開連接的操作，也要確保通知到業(yè)務開發(fā)團隊。

第二種方法：減少連接過程的消耗。

有的業(yè)務代碼會在短時間內(nèi)先大量申請數(shù)據(jù)庫連接做備用，如果現(xiàn)在數(shù)據(jù)庫確認是被連接行為打掛了，那么一種可能的做法，是讓數(shù)據(jù)庫跳過權限驗證階段。

跳過權限驗證的方法是：重啟數(shù)據(jù)庫，并使用–skip-grant-tables 參數(shù)啟動。這樣，整個 MySQL 會跳過所有的權限驗證階段，包括連接過程和語句執(zhí)行過程在內(nèi)。

但是，這種方法特別符合我們標題里說的“飲鴆止渴”，風險極高，是我特別不建議使用的方案。尤其你的庫外網(wǎng)可訪問的話，就更不能這么做了。

在 MySQL 8.0 版本里，如果你啟用–skip-grant-tables 參數(shù)，MySQL 會默認把 --skip-networking 參數(shù)打開，表示這時候數(shù)據(jù)庫只能被本地的客戶端連接?？梢姡琈ySQL 官方對 skip-grant-tables 這個參數(shù)的安全問題也很重視。

慢查詢性能問題

在 MySQL 中，會引發(fā)性能問題的慢查詢，大體有以下三種可能：

1. 索引沒有設計好；
2. SQL 語句沒寫好；
3. MySQL 選錯了索引。

接下來，我們就具體分析一下這三種可能，以及對應的解決方案。

導致慢查詢的第一種可能是，索引沒有設計好。

這種場景一般就是通過緊急創(chuàng)建索引來解決。MySQL 5.6 版本以后，創(chuàng)建索引都支持 Online DDL 了，對于那種高峰期數(shù)據(jù)庫已經(jīng)被這個語句打掛了的情況，最高效的做法就是直接執(zhí)行 alter table 語句。

比較理想的是能夠在備庫先執(zhí)行。假設你現(xiàn)在的服務是一主一備，主庫 A、備庫 B，這個方案的大致流程是這樣的：

1. 在備庫 B 上執(zhí)行 set sql_log_bin=off，也就是不寫 binlog，然后執(zhí)行 alter table 語句加上索引；
2. 執(zhí)行主備切換；
3. 這時候主庫是 B，備庫是 A。在 A 上執(zhí)行 set sql_log_bin=off，然后執(zhí)行 alter table 語句加上索引。

導致慢查詢的第二種可能是，語句沒寫好。
比如，我們犯了在第 18 篇文章《為什么這些 SQL 語句邏輯相同，性能卻差異巨大？》中提到的那些錯誤，導致語句沒有使用上索引。

這時，我們可以通過改寫 SQL 語句來處理。MySQL 5.7 提供了 query_rewrite 功能，可以把輸入的一種語句改寫成另外一種模式。

比如，語句被錯誤地寫成了 select * from t where id + 1 = 10000，你可以通過下面的方式，增加一個語句改寫規(guī)則。

mysql> insert into query_rewrite.rewrite_rules(pattern, replacement, pattern_database) values ("select * from t where id + 1 = ?", "select * from t where id = ? - 1", "db1");

call query_rewrite.flush_rewrite_rules();

這里，call query_rewrite.flush_rewrite_rules() 這個存儲過程，是讓插入的新規(guī)則生效，也就是我們說的“查詢重寫”。你可以用圖 4 中的方法來確認改寫規(guī)則是否生效。

image.png

導致慢查詢的第三種可能，就是碰上了我們在第 10 篇文章《MySQL 為什么有時候會選錯索引？》中提到的情況，MySQL 選錯了索引。

這時候，應急方案就是給這個語句加上 force index。

同樣地，使用查詢重寫功能，給原來的語句加上 force index，也可以解決這個問題。

上面我和你討論的由慢查詢導致性能問題的三種可能情況，實際上出現(xiàn)最多的是前兩種，即：索引沒設計好和語句沒寫好。而這兩種情況，恰恰是完全可以避免的。比如，通過下面這個過程，我們就可以預先發(fā)現(xiàn)問題。

1. 上線前，在測試環(huán)境，把慢查詢?nèi)罩荆╯low log）打開，并且把 long_query_time 設置成 0，確保每個語句都會被記錄入慢查詢?nèi)罩荆?/li>
2. 在測試表里插入模擬線上的數(shù)據(jù)，做一遍回歸測試；
3. 觀察慢查詢?nèi)罩纠锩款愓Z句的輸出，特別留意 Rows_examined 字段是否與預期一致。

QPS 突增問題

有時候由于業(yè)務突然出現(xiàn)高峰，或者應用程序 bug，導致某個語句的 QPS 突然暴漲，也可能導致 MySQL 壓力過大，影響服務。

我之前碰到過一類情況，是由一個新功能的 bug 導致的。當然，最理想的情況是讓業(yè)務把這個功能下掉，服務自然就會恢復。

而下掉一個功能，如果從數(shù)據(jù)庫端處理的話，對應于不同的背景，有不同的方法可用。我這里再和你展開說明一下。

1. 一種是由全新業(yè)務的 bug 導致的。假設你的 DB 運維是比較規(guī)范的，也就是說白名單是一個個加的。這種情況下，如果你能夠確定業(yè)務方會下掉這個功能，只是時間上沒那么快，那么就可以從數(shù)據(jù)庫端直接把白名單去掉。
2. 如果這個新功能使用的是單獨的數(shù)據(jù)庫用戶，可以用管理員賬號把這個用戶刪掉，然后斷開現(xiàn)有連接。這樣，這個新功能的連接不成功，由它引發(fā)的 QPS 就會變成 0。
3. 如果這個新增的功能跟主體功能是部署在一起的，那么我們只能通過處理語句來限制。這時，我們可以使用上面提到的查詢重寫功能，把壓力最大的 SQL 語句直接重寫成"select 1"返回。

上期問題時間

前兩期我給你留的問題是，下面這個圖的執(zhí)行序列中，為什么 session B 的 insert 語句會被堵住。

image.png

我們用上一篇的加鎖規(guī)則來分析一下，看看 session A 的 select 語句加了哪些鎖：

1. 由于是 order by c desc，第一個要定位的是索引 c 上“最右邊的”c=20 的行，所以會加上間隙鎖 (20,25) 和 next-key lock (15,20]。
2. 在索引 c 上向左遍歷，要掃描到 c=10 才停下來，所以 next-key lock 會加到 (5,10]，這正是阻塞 session B 的 insert 語句的原因。 （next-key lock 是左開右閉，一個索引的落在了右閉上即為這個索引的next-key lock，這個和查詢順序無關。 所以c>=15 and c <=20 order by c desc 從右向左，掃描到c=10才停止，c=10的next-key lock為(5,10] 故c上加鎖(5,25) 如果去除order by c desc即從左向右，掃描到c=25才停止，c=25的next-key lock為(20,25] 故c上加鎖(10,25] 而不是(10，+∞)）
3. 在掃描過程中，c=20、c=15 這三行都存在值，由于是 select *，所以會在主鍵 id 上加2個行鎖。

因此，session A 的 select 語句鎖的范圍就是：

1. 索引 c 上 (5, 25)；
2. 主鍵索引上 id=15、20 兩個行鎖。

這里寫的應該是對的，只是索引C上C等于10有行鎖，id等于10這一行沒有行鎖，可以通過 update t set d = 3 where id = 10;進行驗證。 上面第三點說的是有點問題

首先鎖是加在索引上的，c和id的是不同的索引，c上的鎖和id上的鎖分開分析。id上只有15和20的鎖，你update id=10肯定是不block的

剛也實驗了下，確實id=10是沒有行鎖的