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)
