本文介紹了如何對慢SQL進行有效限流。
在資料庫會話或者慢日誌中發現大量慢SQL，大量佔用資料庫資源，同時活躍會話數、CPU使用率、IOPS、記憶體使用量率等監控指標一項或者多項處於高位。分析後發現這些慢SQL不屬於核心業務，是最佳化不足的爛SQL，為保障核心業務的穩定運行，此時我們需要對其進行限流。
相關限流文法，請參見SQL限流。
SQL限流的營運操作步驟
- 在執行個體會話頁面或使用如下語句發現慢SQL。
select * from information_schema.processlist where COMMAND!= 'SLEEP' and TIME>= 1000 order by TIME DESC; - 分析慢SQL，請參見如何分析及最佳化慢SQL。
- 建立限流規則，可通過SQL命令，或者使用控制台上執行個體會話頁面的SQL限流功能。
- 從以下幾方面觀察限流規則效果。
- 監控指標恢複情況；
- 業務側反饋；
show ccl_rules查看每個限流規則的限流情況的統計資訊；- 查看會話和SQL日誌。
- 建立索引、修改SQL、增加資源等。
- 使用
DROP CCL_RULE或者CLEAR CCL_RULES語句關閉限流規則。
如下案例說明了如何對發現的慢SQL進行限流，您可以參照案例中的限流規則，修改後使用。
案例1: 慢SQL屬於同一個SQL模板
某DBA收到了資料庫資源某指標處於高位的警示，查看資料庫慢日誌和會話後均發現有如下的慢SQL：
+--------+---------------+---------------------+--------------------+---------+------+-------+----------------------------------------------+-----------------+
| ID | USER | HOST | DB | COMMAND | TIME | STATE | INFO | SQL_TEMPLATE_ID |
+--------+---------------+---------------------+--------------------+---------+------+-------+----------------------------------------------+-----------------+
| 951494 | userxxxxxxxxx | 47.100.XX.XX:33830 | analy_db | Query | 40 | | select * from bmsql_oorder where `o_id` > 12 | 65c92c88 |
| 952468 | userxxxxxxxxx | 47.100.XX.XX:33517 | analy_db | Query | 43 | | select * from bmsql_oorder where `o_id` > 10 | 65c92c88 |
| 953468 | userxxxxxxxxx | 47.100.XX.XX:33527 | analy_db | Query | 43 | | select * from bmsql_oorder where `o_id` > 23 | 65c92c88 |
| 954468 | userxxxxxxxxx | 47.100.XX.XX:33537 | analy_db | Query | 43 | | select * from bmsql_oorder where `o_id` > 25 | 65c92c88 |
| 955468 | userxxxxxxxxx | 47.100.XX.XX:33547 | analy_db | Query | 43 | | select * from bmsql_oorder where `o_id` > 27 | 65c92c88 |
+--------+---------------+---------------------+--------------------+---------+------+-------+----------------------------------------------+-----------------+可見，這些慢SQL屬於同一個SQL模板（模板ID為65c92c88）：
select * from bmsql_oorder where `o_id` > ?bmsql_oorder為一個資料量較大的表，而且列o_id上沒有索引，顯然這是一個未經最佳化的SQL，佔盡了資料庫資源影響了其他重要SQL的正常執行。這是一個非常適合利用模板ID去做SQL限流的情境。
建立限流規則- 如果這個SQL模板的SQL不應該在當時執行，而是應該在業務低峰期執行，可以建立SQL限流規則不讓它執行：
CREATE CCL_RULE `KILL_CCL` //限流規則名稱為KILL_CCL ON `analy_db`.`*` //&匹配analy_db下的所有表上執行的SQL TO 'userxxxxxxxxx'@'%' //&匹配來自userxxxxxxxxx使用者的SQL FOR SELECT //&匹配是SELECT類型的SQL語句 FILTER BY TEMPLATE '65c92c88' //&匹配模板ID WITH MAX_CONCURRENCY = 0; //設定單節點並發度為0，不允許匹配到的SQL執行用戶端再次執行這類SQL的時候將會返回報錯資訊：
ERROR 3009 (HY000): [13172dbaf2801000][10.93.159.222:3029][analy_db]Exceeding the max concurrency 0 per node of ccl rule KILL_CCL - 如果允許這個SQL模板的SQL少量執行，只要不佔盡資料庫資源就行，可以在建立限流規則的時候設定一定的並發度：
CREATE CCL_RULE `KILL_CCL_2` //限流規則名稱為KILL_CCL_2 ON `analy_db`.`*` //&匹配analy_db下的所有表上執行的SQL TO 'userxxxxxxxxx'@'%' //&匹配來自userxxxxxxxxx使用者的SQL FOR SELECT //&匹配是SELECT類型的SQL語句 FILTER BY TEMPLATE '65c92c88' //&匹配模板ID WITH MAX_CONCURRENCY = 2; //允許單個節點可以同時有兩個這樣的SQL在執行也可使用執行個體會話頁面裡的SQL限流功能，進行如下操作：
- 如果希望這個SQL模板的SQL執行的時候可以慢，但盡量不要出錯，則可以設定等待隊列和等待逾時時間（預設為600秒）：
CREATE CCL_RULE `QUEUE_CCL_2` //限流規則名稱為QUEUE_CCL_2 ON `analy_db`.`*` //&匹配analy_db下的所有表上執行的SQL TO 'userxxxxxxxxx'@'%' //&匹配來自userxxxxxxxxx使用者的SQL FOR SELECT //&匹配是SELECT類型的SQL語句 FILTER BY TEMPLATE '65c92c88' //&匹配模板ID WITH MAX_CONCURRENCY = 2, WAIT_QUEUE_SIZE=20, WAIT_TIMEOUT=500; //單節點並發度為2，單節點等待隊列長度為20，等待逾時時間為500秒建立完後，可以通過
show ccl_rules指令查詢各個限流規則的實際效果，例如當前匹配到某個限流規則的正在執行的SQL數量、被限流引起報錯的SQL數量、總匹配成功次數等。如果想放開被限流SQL，例如在增加了某個索引後，被限流SQL的執行效率變高了，則可以通過drop ccl_rule命令來關閉指定限流規則，或者使用clear ccl_rules來關閉所有的限流規則。上面的SQL也可以通過關鍵字來限流，將SQL語句上的關鍵字做拆分，得到如下關鍵字列表：
- select
- from
- bmsql_oorder
- where
- `o_id`
建立限流規則：
CREATE CCL_RULE `KILL_CCL` //限流規則名稱為KILL_CCL ON `analy_db`.`*` //&匹配analy_db下的所有表上執行的SQL TO 'userxxxxxxxxx'@'%' //&匹配來自userxxxxxxxxx使用者的SQL FOR SELECT //&匹配是SELECT類型的SQL語句 FILTER BY KEYWORD('select','from','bmsql_oorder','where','`o_id`') //&匹配模板ID WITH MAX_CONCURRENCY = 0; //設定單節點並發度為0，不允許匹配到的SQL執行在能擷取到模板ID（在SQL日誌、explain命令、會話中）的情況下，還推薦使用更精準的基於模板ID的限流。
也可使用執行個體會話頁面裡的SQL限流功能，操作如下：
案例2: 慢SQL都是同一個SQL
某DBA收到了資料庫資源某指標處於高位的警示，查看資料庫慢日誌和會話後均發現有如下的慢SQL：
+--------+---------------+---------------------+--------------------+---------+------+-------+---------------------------------------------------+-----------------+
| ID | USER | HOST | DB | COMMAND | TIME | STATE | INFO | SQL_TEMPLATE_ID |
+--------+---------------+---------------------+--------------------+---------+------+-------+---------------------------------------------------+-----------------+
| 951494 | userxxxxxxxxx | 47.100.XX.XX:33830 | analy_db | Query | 40 | | select * from bmsql_oorder where o_carrier_id = 2 | 438b00e4 |
| 952468 | userxxxxxxxxx | 47.100.XX.XX:33517 | analy_db | Query | 43 | | select * from bmsql_oorder where o_carrier_id = 2 | 438b00e4 |
| 953468 | userxxxxxxxxx | 47.100.XX.XX:33527 | analy_db | Query | 43 | | select * from bmsql_oorder where o_carrier_id = 2 | 438b00e4 |
| 954468 | userxxxxxxxxx | 47.100.XX.XX:33537 | analy_db | Query | 43 | | select * from bmsql_oorder where o_carrier_id = 2 | 438b00e4 |
| 955468 | userxxxxxxxxx | 47.100.XX.XX:33547 | analy_db | Query | 43 | | select * from bmsql_oorder where o_carrier_id = 2 | 438b00e4 |
+--------+---------------+---------------------+--------------------+---------+------+-------+---------------------------------------------------+-----------------+bmsql_oorder中的符合o_carrier_id = 2條件的有較多記錄，導致了慢SQL，如果使用模板ID限流，則會影響o_carrier_id不是2的SQL語句，如果使用關鍵字限流則會影響類似如下的正常SQL：
select * from bmsql_oorder where o_carrier_id = 2 limit 1;
select * from bmsql_oorder where o_carrier_id = 2 and o_c_id = 1;限流具體的SQL，可以使用模板ID加關鍵字的方法，建立如下限流規則：
CREATE CCL_RULE `KILL_CCL` //限流規則名稱為KILL_CCL
ON `analy_db`.`*` //&匹配analy_db下的所有表上執行的SQL
TO 'userxxxxxxxxx'@'%' //&匹配來自userxxxxxxxxx使用者的SQL
FOR SELECT //&匹配是SELECT類型的SQL語句
FILTER BY TEMPLATE '438b00e4' //&匹配模板ID 438b00e4
FILTER BY KEYWORD('o_carrier_id','2') //&匹配參數關鍵字
WITH MAX_CONCURRENCY = 0; //設定單節點並發度為0，不允許匹配到的SQL執行如果使用的PolarDB-X版本為5.4.11以上，且該SQL不在prepare模式下執行，還可以使用如下高階文法進行限流：
CREATE CCL_RULE `KILL_CCL` //限流規則名稱為KILL_CCL
ON `analy_db`.`*` //&匹配analy_db下的所有表上執行的SQL
TO 'userxxxxxxxxx'@'%' //&匹配來自userxxxxxxxxx使用者的SQL
FOR SELECT //&匹配是SELECT類型的SQL語句
FILTER BY QUERY 'select * from bmsql_oorder where o_carrier_id = 2' //&匹配SQL語句
WITH MAX_CONCURRENCY = 0; //設定單節點並發度為0，不允許匹配到的SQL執行案例3: 慢SQL集包含多個SQL模板
某DBA收到了資料庫資源某指標處於高位的警示，查看資料庫慢日誌和會話後均發現有如下的慢SQL：
+--------+---------------+---------------------+--------------------+---------+------+-------+---------------------------------------------------+-----------------+
| ID | USER | HOST | DB | COMMAND | TIME | STATE | INFO | SQL_TEMPLATE_ID |
+--------+---------------+---------------------+--------------------+---------+------+-------+---------------------------------------------------+-----------------+
| 951494 | userxxxxxxxxx | 47.100.XX.XX:33830 | analy_db | Query | 40 | | select * from bmsql_oorder where o_carrier_id = 2 | 438b00e4 |
| 952468 | userxxxxxxxxx | 47.100.XX.XX:33517 | analy_db | Query | 43 | | select * from bmsql_order_line where ol_o_id = 2 | 57a572f9 |
| 953468 | userxxxxxxxxx | 47.100.XX.XX:33527 | analy_db | Query | 43 | | select * from bmsql_new_order where no_w_id = 2 | de6eefdb |
+--------+---------------+---------------------+--------------------+---------+------+-------+---------------------------------------------------+-----------------+
此種情況較為複雜，如果一條明顯執行效率很高的SQL也成了慢SQL，則不排除是由於網路抖動或者服務節點異常等原因導致運行效率降低從而產生大批量的慢SQL，也可能是由於真正的爛SQL完全耗盡了資源，導致原本正常的SQL也成了慢SQL，需要通過SQL分析具體原因，不在本文的討論範圍內。假設已經確定了需要限流的慢SQL，則可以針對每個模板ID建立一個限流規則。但隨著限流規則增加，匹配效率會略有降低，當PolarDB-X的CN核心版本為5.4.11以上時，推薦您使用多模板限流：
CREATE CCL_RULE `KILL_CCL` //限流規則名稱為KILL_CCL
ON `analy_db`.`*` //&匹配analy_db下的所有表上執行的SQL
TO 'userxxxxxxxxx'@'%' //&匹配來自userxxxxxxxxx使用者的SQL
FOR SELECT //&匹配是SELECT類型的SQL語句
FILTER BY TEMPLATE('438b00e4','57a572f9','de6eefdb') //&匹配中其中一個模板ID，則該匹配項算匹配成功
WITH MAX_CONCURRENCY = 0; //設定單節點並發度為0，不允許匹配到的SQL執行如果確定會話中的慢SQL是都是需要限流的爛SQL，且PolarDB-X的版本為5.4.11以上時，可以開啟慢SQL限流觸發器。也可以使用執行個體會話頁面裡的SQL限流功能，進行如下操作：
總結
SQL限流為應急措施，可在資料庫由於爛SQL導致效率降低的時候，起到快速恢複的作用。對爛SQL進行限流後，需要將注意力集中在如何最佳化爛SQL上，並在合適的時機清空SQL限流規則。