春節長假某日，陽光明媚，春暖花開，恰逢冬奧會開幕，想著一定是一個黃道吉日，必能順風順水，沒想到卻遇到一個有點小波折 的客戶報障，
01故障起因故障起因是客戶前一天從自建MySQL遷移到云上RDS，在執行某個并發較高的業務時出現了大量鎖等待，客戶當時升級了實體到最高規格，但故障依舊，客戶反饋升級后的實體規格比自建實體高了一倍，自建實體上從未發生過類似情況，后客戶根據當時的業務故障模擬了現場，主要是并發執行如下存盤程序的時候性能很差：
02初步診斷
從存盤程序的邏輯看，比較簡單，主要涉及兩個SQL，一個從表t（隱藏了真實表名）中meeting_id根據傳入引數值查詢，具體的入參由字符型變數p_meeting_id帶入；另外一個根據meeting_id和剛查出的phone_id去更新t中的phone_id為phone_id+3，表t資料量約40w左右，
第一感覺這是個簡單問題，估計兩個SQL的meeting_id索引沒有生效，查詢表上索引后果然發現meeting_id和phone_id上沒有索引，建議客戶在兩個欄位上分別創建了索引，且meeting_id為主鍵，此時用戶執行模擬的并發腳本反饋速度有了明顯提升，200個并發最高執行時間40s左右，但模擬500個并發的時候，超過了8分鐘還沒有執行完，用戶反饋在自建MySQL上并發500執行都是秒級完成，此時在控制臺看，這個存盤程序在慢查詢日志中批量出現，且掃描行數巨大，客戶端已經完全hang住:
03進一步優化
雖然優化有了初步的效果， 但距離客戶自建環境性能描述還差距很大，由于并發高， 從監控看測驗期間CPU到了100%，懷疑引數innodb_thread_concurrency的設定可能不當，此引數的作用是控制 InnoDB 的并發執行緒上限，也就是說，一旦并發執行緒數達到這個值，InnoDB 在接收到新請求的時候，就會進入等待狀態，直到有執行緒退出，RDS默認值為0，也就是沒有限制上限，在高并發的場景下可能會產生較多的背景關系切換，導致CPU升高，和客戶咨詢了一下，他們自建環境的值設定為32，建議他們將RDS的值也改為32再看看效果，客戶很快反饋，修改后的確有效果，500個并發在3分鐘內完成，沒有再發生hang住不動的情況，性能有了進一步的提升，但引數innodb_thread_concurrency進一步調整效果不明顯，
04加trace診斷客戶看到性能不斷提升也很有信心，但和自建環境差距還是很大，還有哪里可能有問題？突然想到，創建索引后，在控制臺的慢查詢串列中看到很多存盤程序的呼叫sql，且掃描記錄數巨大，如果是走meeting_id唯一索引，應該掃描很少的記錄數才對，難道沒有走索引？或者沒有走meeting_id主鍵索引？聯系客戶，希望提供測驗環境登陸測驗，
在測驗環境，首先希望驗證一下兩個SQL的執行計劃到底是怎么樣的，登陸實體后，分別對兩個存盤程序中的SQL執行explain，發現走的確實是主鍵（meeting_id）：
為了進一步確認SQL在存盤程序中的實際執行計劃，修改了一下測驗的存盤程序邏輯，加入了SQL執行的explain結果和實際執行的trace，程序中主要增加的代碼如下：
執行計劃結果如下：
從結果看，兩個SQL居然真的沒有走主鍵meeting_id索引，而是都走了phone_id這個普通的二級索引，其中第一個查詢SQL走的索引全掃描，掃描記錄數rows為397399，和表的記錄數一致，顯然走了全索引掃描，雖然比全表掃描好一些，但效率仍然低下；另外一個update的SQL走了正常的索引掃描，rows只有2，性能高效，為什么兩個SQL沒有走meeting_id這個主鍵索引呢？看trace列印的部分內容：
trace顯示兩個SQL在優化器分析時，將meeting_id做了隱式轉換，轉換函式為convert('meeting_id' using utf8mb4)，也就是將meeting_id做了字符集的轉換，熟悉索引機制的同學都清楚，這種情況下優化器是不會走meeting_id索引的，這也可以解釋了客戶第一次創建索引的時候為啥有性能提升，但效果并不明顯，原因就是只有update陳述句真正用到了索引帶來的性能提升，而且是phone_id索引帶來的提升，不是性能更高的主鍵meeting_id，
05真相大白
現在聚焦到最關鍵的問題，meeting_id為啥要做字符集的隱式轉換？查看了一下實體相關字符集的設定：
-
表和列的字符集都為utf8；
-
表所在庫的字符集為utf8mb4；
-
server字符集（（character_set_server））為utf8
-
character_set_client/character_set_connection/character_set_results為utf8mb4
果然，server、database、table的字符集不完全一致，猜想一下實際流程應該是這樣的：存盤程序中傳入的字符引數字符集為utf8mb4，和表中字符集為utf8的欄位meeting_id比較時，meeting_id做了字符集的隱式轉換，轉換為utf8mb4后再和輸入引數比較，從而導致meeting_id上的索引無法使用，
根據這個猜測，建議用戶將表的字符集更改為utf8mb4，這樣應該可以避免字符集的轉換，由于這個功能還未上線，用戶直接對 表做了字符集的修改：
alter table zm_meeting convert to character set utf8mb4;
修改后讓用戶再次測驗，預期效果終于出現，并發500測驗在秒級完成，trace查看執行計劃，都走了meeting_id的主鍵索引，隱式轉換也隨之消失，性能問題得到了徹底解決，
06后續思考存盤程序的入參為啥使用了utf8mb4？這是本次案例的核心，查閱mysql檔案，存盤程序介紹里面有一段描述：
簡單說，就是存盤程序的字符型引數，如果沒有顯式指定字符集，默認將會使用所在資料庫的字符集，而本案例中表所在的資料庫字符集為utf8mb4，所以引數默認使用了utf8mb4，導致了匹配程序的隱式轉換，存盤程序外直接寫SQL為什么沒有這種情況發生，我猜測比較的字串應該會自動匹配‘=’左邊表欄位的字符集，
既然這樣，理論上直接修改引數的字符集應該也可以達到同樣結果，簡單測驗下，將存盤程序引數加上表上的字符集屬性：
CREATE PROCEDURE `zm_sp_next_phone_id`(IN `p_meeting_id` VARCHAR(36) character set utf8)
測驗結果如我們預期，不會產生隱式轉換，執行計劃正確，
問題雖然解決了，原因也找到了，但反思一下整個程序，如果用戶的server、庫、表字符集能夠保持一致，將完全可以避免這個故障，與字符集相關的類似故障也可以大概率避免，所以客戶側還是要有一定的設計規范；產品側如果有一定的檢查規則可以幫客戶發現類似的隱患，對提升客戶體驗也是一種很有價值的服務，
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/503428.html
標籤：MySQL
上一篇：MySQL學習筆記