記一次mysql的preparedStatement使用超限問題

標籤：alt   現象   nts   可維護性   安全   文法   image   margin   idt   

【現象&背景】

        本服務是個為資料庫的分庫分表提供路由規則計算，sql過濾執行的中間服務。即上遊將請求發給本服務，本服務根據分庫分表規則將相應的sql執行發送給對應的分庫、分表去執行，並整理結果返回給上遊。

        2016-07-14下午，上遊流量切換後，mysql大量報出" Can‘t create more than max_prepared_stmt_count statements (current value: 16382)"，mysql不能工作導致本層資料庫路由服務不能工作，大量請求失敗。

        大概意思就是說，同一時間在mysqld上所有session中preparedStatement語句超過了mysql的限制，導致建立preparedStatement失敗，產生錯誤。

        臨時解決：上遊回退。

【原因分析】

• 為什麼會建立這麼多的preparedStatement？

        1）其中有個介面由於bug導致會對所有分庫、分表進行全表sql執行操作，而每個sql執行前都會建立一個preparedStatement

        2）上遊切換流量後使用的新庫擴大了分庫數和分表數，導致一個請求對應分表數量成倍數增加，那一個請求對應preparedStatement數量也成倍數增加。

• preparedStatement是為何物？

        preparedStatement是在多次重複執行只用參數值不同的sql語句時，先發一個請求去mysql服務端將sql語句編譯好，並將其先行編譯結果儲存在preparedStatement對象中，以後便可以使用該對象高效地多次執行該語句而不用先行編譯，直接使用資料庫的緩衝區，提高資料庫訪問的效率。

【preparedStatement】

• 原理

為mysql查詢執行過程，包括：

1、傳輸sql給資料庫

2、伺服器先檢查查詢快取，如果命中，吉利返回結果；否則進入下一階段

3、資料庫驗證&解析sql

4、計算執行計畫

5、根據執行計畫調用儲存引擎的api執行查詢

6、返回資料

        在上面步驟中，第4步非常耗時。為了提高效能，資料庫會緩衝執行語句及其執行計畫。但是，sql語句本身為key，執行計畫為value，sql語句中只要有一個位元組不一樣就不能命中緩衝。

        因此，有了preparedStatement，能夠提高在只有參數不一樣的sql的快取命中率。

        preparedStatement建立的時候，會將參數化的語句發給資料庫，進行文法檢測和執行計畫計算。sql語句被先行編譯並且儲存在preparedStatement對象中，下次執行相同的sql語句時，資料庫不會再進行先行編譯，而是直接使用資料庫的緩衝區，提高資料庫的訪問效率。

 

• 作用

        1、代碼可讀性&可維護性

        2、在批量執行時提高cache命中率，提高效能

        3、提高安全性，防止sql注入

 

• 適用情境

        有大量的sql結構相同，只有數值不同的sql請求時。或者處於對寫入的sql的安全考慮。

 

記一次mysql的preparedStatement使用超限問題