SQL限流
更新時間 2025-07-11 18:03:33
最近更新時間: 2025-07-11 18:03:33
分享文章
本文介紹DRDS管理命令中的的SQL限流相關命令和使用示例。
背景信息
在實際生產環境中，數據庫資源（例如CPU、內存、I/O等）有限，當某一資源使用達到上限時，大量并發SQL查詢可能因資源不足而超時，從而影響業務可用性。為了讓高優先級甚至直接影響核心業務的查詢能夠獲得足夠資源，而低優先級獲得較少的資源（甚至可以限制低優先級任務不在此時執行），DRDS提供了節點級別的SQL限流功能來解決上述問題。
SQL限流是一種對SQL的查詢速度進行限制的能力，讓用戶能夠在核心業務的查詢受到其他邊緣業務查詢排擠的情況下，快速采取措施限制邊緣業務的查詢，以恢復核心業務，從而保證數據庫的持續穩定運行。
創建限流規則
語法
UDAL CCL CREATE `ccl_rule_name` ON `database` TO '<usename>'@'<host>' FOR { UPDATE | SELECT | INSERT | DELETE } [ filter_options ] WITH { max_concurrency_option | max_merge_rows_option }
filter_options:
[ FILTER BY KEYWORD('KEYWORD1', 'KEYWORD2',...) ]
[ FILTER BY QUERY ('select ...') ]
max_concurrency_option:
MAX_CONCURRENCY = value1 [ and WAIT_QUEUE_SIZE = value2 ] [ and WAIT_TIMEOUT = value3 ]
max_merge_rows_option:
MAX_MERGE_ROWS = value1參數說明如下：
| 參數 | 是否必選 | 說明 |
|---|---|---|
| ccl_rule_name | 是 | 限流規則的名稱。 為避免名稱與SQL關鍵字沖突，建議在規則名稱的前后各加一個反引號（`）。 |
| database | 是 | 數據庫的名稱，支持使用星號（*）表示任意匹配。為避免名稱與SQL關鍵字沖突，建議在數據庫名稱的前后各加一個反引號（`）。 |
| '<usename>'@'<host>' | 是 | 賬號名稱，其中<host>支持用百分號（%）來表示任意匹配。 |
| { UPDATE | SELECT | INSERT | DELETE } | 是 | SQL操作類型，取值范圍：
|
| [ filter_options ] | 否 | 過濾條件，支持以下兩種條件： 注意 一個語句只能選擇一種過濾條件。
|
| { max_concurrency_option | max_merge_rows_option } | 是 | 限流規則，支持以下兩種規則： 注意
|
示例
mysql> UDAL CCL CREATE 'test1' ON 'shard' TO 'udal'@'%' FOR SELECT FILTER BY KEYWORD('select', 'limit') with max_concurrency=0;
Query OK, 1 row affected (0.05 sec)
mysql> UDAL CCL CREATE 'test2' ON 'shard' TO 'udal'@'%' FOR SELECT FILTER BY QUERY('select * from t1') WITH MAX_MERGE_ROWS=10;
Query OK, 1 row affected (0.02 sec)
查看限流規則
語法
UDAL CCL SHOW ALL; --查看所有規則
UDAL CCL SHOW 'rule_name'; --查看名稱為rule_name的規則，大小寫敏感
UDAL CCL SHOW LIKE '%rule%'; --查看名稱包含rule的規則，大小寫不敏感參數說明如下：
rule_name: 必選參數，表示待查看限流規則的名稱。
示例
mysql> UDAL CCL SHOW ALL;
+-----------+---------+---------+--------+-------------+--------------------------+--------------------------+--------------+----------------+----------+------+------+-------+--------------+-------------+------------------------------+-------------------+
| Rule_name | Running | Waiting | Killed | Total_match | Max_concurrency_per_node | Wait_queue_size_per_node | Wait_timeout | Max_merge_rows | Sql_type | User | Host | Db | Keywords | Templateid | Created_time | Template_sql |
+-----------+---------+---------+--------+-------------+--------------------------+--------------------------+--------------+----------------+----------+------+------+-------+--------------+-------------+------------------------------+-------------------+
| test1 | 0 | 0 | 0 | 0 | 0 | 0 | 60 | -1 | SELECT | udal | % | shard | select,limit | | Fri May 16 09:44:11 HKT 2025 | |
| test2 | 0 | 0 | 0 | 0 | -1 | 0 | 60 | 10 | SELECT | udal | % | shard | | -1853761305 | Fri May 16 09:53:19 HKT 2025 | SELECT * FROM t1 |
+-----------+---------+---------+--------+-------------+--------------------------+--------------------------+--------------+----------------+----------+------+------+-------+--------------+-------------+------------------------------+-------------------+
2 rows in set (0.00 sec)
mysql> UDAL CCL SHOW 'test1';
+-----------+---------+---------+--------+-------------+--------------------------+--------------------------+--------------+----------------+----------+------+------+-------+--------------+------------+------------------------------+--------------+
| Rule_name | Running | Waiting | Killed | Total_match | Max_concurrency_per_node | Wait_queue_size_per_node | Wait_timeout | Max_merge_rows | Sql_type | User | Host | Db | Keywords | Templateid | Created_time | Template_sql |
+-----------+---------+---------+--------+-------------+--------------------------+--------------------------+--------------+----------------+----------+------+------+-------+--------------+------------+------------------------------+--------------+
| test1 | 0 | 0 | 0 | 0 | 0 | 0 | 60 | -1 | SELECT | udal | % | shard | select,limit | | Fri May 16 09:44:11 HKT 2025 | |
+-----------+---------+---------+--------+-------------+--------------------------+--------------------------+--------------+----------------+----------+------+------+-------+--------------+------------+------------------------------+--------------+
1 row in set (0.00 sec)
udal@test:0001 [temp]> UDAL CCL SHOW like 'test%';
+-----------+---------+---------+--------+-------------+--------------------------+--------------------------+--------------+----------------+----------+------+------+-------+--------------+-------------+------------------------------+-------------------+
| Rule_name | Running | Waiting | Killed | Total_match | Max_concurrency_per_node | Wait_queue_size_per_node | Wait_timeout | Max_merge_rows | Sql_type | User | Host | Db | Keywords | Templateid | Created_time | Template_sql |
+-----------+---------+---------+--------+-------------+--------------------------+--------------------------+--------------+----------------+----------+------+------+-------+--------------+-------------+------------------------------+-------------------+
| test1 | 0 | 0 | 0 | 0 | 0 | 0 | 60 | -1 | SELECT | udal | % | shard | select,limit | | Fri May 16 09:44:11 HKT 2025 | |
| test2 | 0 | 0 | 0 | 0 | -1 | 0 | 60 | 10 | SELECT | udal | % | shard | | -1853761305 | Fri May 16 09:53:19 HKT 2025 | SELECT * FROM t1 |
+-----------+---------+---------+--------+-------------+--------------------------+--------------------------+--------------+----------------+----------+------+------+-------+--------------+-------------+------------------------------+-------------------+
2 rows in set (0.00 sec)回顯說明如下：
| 參數 | 說明 |
|---|---|
| Rule_name | 限流規則名稱。 |
| Running | 匹配到該限流規則，且正常執行的SQL語句數量。 |
| Waiting | 匹配到該限流規則，且正在等待隊列里的SQL語句數量。 |
| Killed | 匹配到該限流規則，且被Kill的SQL語句數量。 |
| Total_match | 匹配到該限流規則的總次數。 |
| Max_concurrency_per_node | 每個計算節點的并發度。 |
| Wait_queue_size_per_node | 每個計算節點上等待隊列的最大長度。 |
| Wait_timeout | SQL語句在等待隊列的最大等待時間。 |
| Max_merge_rows | SELECT語句從DN返回的最大行數。 |
| Sql_type | SQL語句類型，可能值：
|
| User | 用戶名。 |
| Keywords | 關鍵詞列表。 |
| Templateid | SQL模板的哈希值。 |
| Created_time | 限流規則的創建時間。 |
| Template_sql | SQL模板。 |
刪除限流規則
語法
UDAL CCL DELETE ALL; --刪除所有規則
UDAL CCL DELETE 'rule_name'; --刪除名稱為rule_name的單條規則參數說明
rule_name: 必選參數，表示待刪除限流規則的名稱。
示例
mysql> UDAL CCL DELETE 'test1';
Query OK, 1 row affected (0.02 sec)
mysql> UDAL CCL SHOW ALL;
+-----------+---------+---------+--------+-------------+--------------------------+--------------------------+--------------+----------------+----------+------+------+-------+----------+-------------+------------------------------+-------------------+
| Rule_name | Running | Waiting | Killed | Total_match | Max_concurrency_per_node | Wait_queue_size_per_node | Wait_timeout | Max_merge_rows | Sql_type | User | Host | Db | Keywords | Templateid | Created_time | Template_sql |
+-----------+---------+---------+--------+-------------+--------------------------+--------------------------+--------------+----------------+----------+------+------+-------+----------+-------------+------------------------------+-------------------+
| test2 | 0 | 0 | 0 | 0 | -1 | 0 | 60 | 10 | SELECT | udal | % | shard | | -1853761305 | Fri May 16 09:53:19 HKT 2025 | SELECT * FROM t1 |
+-----------+---------+---------+--------+-------------+--------------------------+--------------------------+--------------+----------------+----------+------+------+-------+----------+-------------+------------------------------+-------------------+
1 row in set (0.00 sec)
mysql> UDAL CCL DELETE ALL;
Query OK, 1 row affected (0.01 sec)
mysql> UDAL CCL SHOW ALL;
Empty set (0.01 sec)
