如何使用並發控制CCL規則 - ApsaraDB RDS

為了確保MySQL執行個體在面對突發請求流量、高資源消耗語句和SQL訪問模型變化時的穩定運行，阿里雲提供了基於語句規則的並發控制（CCL）及相應的工具包（DBMS_CCL）。通過DBMS_CCL，您可以限制特定類型SQL語句的最大並存執行數量。當指定SQL的並存執行數達到設定的限額後，額外的SQL語句則會進入等待狀態，直到SQL的並行數量小於設定的限額。

前提條件

執行個體版本如下：

MySQL 8.0（核心小版本20190816或以上）
MySQL 5.7（核心小版本20200630或以上）

參數說明

參數	說明
ccl_max_waiting_count	命中同一條CCL規則的SQL中，最多能處於`Concurrency control waiting`狀態的SQL數量。超過此限制後，SQL將執行失敗並返回錯誤碼`ERROR 7534 (HY000): Concurrency control waiting count exceed max waiting count`。預設值為0，表示沒有限制。
ccl_wait_timeout	處於`Concurrency control waiting`狀態的SQL能夠等待的最長時間。超過此限制後，SQL會繼續執行，不再受CCL的限制。預設值為86400秒。

注意事項

CCL的操作不產生日誌，所以CCL的操作隻影響當前執行個體。例如主執行個體進行CCL操作，不會同步到備執行個體、唯讀執行個體或災備執行個體。
CCL提供逾時機制以應對DML導致事務鎖死結，等待中的線程也會響應事務逾時和線程KILL操作以應對死結。

功能設計

CCL規則定義了如下四個維度特徵：

SQL command：SQL命令類型，例如SELECT、UPDATE、INSERT、DELETE等。
Object：SQL命令操作的對象，例如TABLE、VIEW等。
keywords：SQL命令的關鍵字。
Template：SQL命令的模板。

建立CCL規則表

AliSQL設計了一個系統資料表（concurrency_control）儲存CCL規則，系統啟動時會自動建立該表，無需您手動建立。這裡提供表的建立語句供您參考：

CREATE TABLE `concurrency_control` (
  `Id` bigint NOT NULL AUTO_INCREMENT,
  `Type` enum('SELECT','UPDATE','INSERT','DELETE','TEMPLATE') CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT 'SELECT',
  `Schema_name` varchar(64) COLLATE utf8mb3_bin DEFAULT NULL,
  `Table_name` varchar(64) COLLATE utf8mb3_bin DEFAULT NULL,
  `Concurrency_count` bigint NOT NULL,
  `Keywords` text COLLATE utf8mb3_bin,
  `State` enum('N','Y') CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT 'Y',
  `Ordered` enum('N','Y') CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT 'N',
  `Digest` varchar(64) COLLATE utf8mb3_bin DEFAULT NULL,
  `SQL_template` longtext COLLATE utf8mb3_bin,
  PRIMARY KEY (`Id`)
) /*!50100 TABLESPACE `mysql` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_bin STATS_PERSISTENT=0 COMMENT='Concurrency control'

參數	說明
Id	CCL規則ID。
Type	SQL command，即SQL命令類型。
Schema_name	資料庫名。
Table_name	資料庫內的表名。
Concurrency_count	並發數。當並發數設定為0，會拒絕所有匹配該規則的SQL執行並返回錯誤碼`ERROR 7535 (HY000): Concurrency control refuse to execute query`。
Keywords	關鍵字，多個關鍵字用英文分號（;）分隔。
State	本規則是否啟用。
Ordered	Keywords中多個關鍵字是否按順序匹配。
Digest	根據SQL_template進行Hash計算得到的64位元組的Hash字串。
SQL_template	SQL語句的模板。

管理CCL規則

為了便捷地管理CCL規則，AliSQL在DBMS_CCL中定義了四個本機存放區規則。詳細說明如下：

add_ccl_rule：增加規則

命令模板

dbms_ccl.add_ccl_rule('<Type>','<Schema_name>','<Table_name>',<Concurrency_count>,'<Keywords>');

範例程式碼
- 增加SQL命令規則：SELECT語句的並發數為10。
```
mysql> call dbms_ccl.add_ccl_rule('SELECT', '', '', 10, '');
```
- 增加關鍵字規則：SELECT語句中出現關鍵字key1的並發數為20。
```
mysql> call dbms_ccl.add_ccl_rule('SELECT', '', '', 20, 'key1');
```
- 增加完整規則：test.t表且出現關鍵字key2的SELECT語句的並發數為20。
```
mysql> call dbms_ccl.add_ccl_rule('SELECT', 'test', 't', 20, 'key2');
```

add_ccl_template_rule：增加模板規則

前提條件
增加模板規則時，執行個體版本需滿足以下要求：
- MySQL 8.0 （核心小版本20230630及以上）
- MySQL 5.7 （核心小版本20241231及以上）

命令模板
```
dbms_ccl.add_ccl_rule('<Type>','<Schema_name>','<Table_name>',<Concurrency_count>,'', 'Template_sql');
```
說明
- Table_name無需填寫。Template_sql不可為空並且只能填寫一條SQL。
- 可以通過STATEMENT_DIGEST_TEXT內建函數查看SQL對應的SQL模板。

範例程式碼
增加模板規則，test資料庫下，SQL模板為'SELECT c FROM t1 WHERE id=?'的並發數為30。
```
call dbms_ccl.add_ccl_rule('TEMPLATE', 'test', '', 30, '', 'SELECT c FROM t1 WHERE id=4');
```
說明
- 在匹配限流規則時，每條SQL語句只能匹配一條限流規則。
- 規則匹配的優先順序順序如下：模板規則 > 完整限流規則 > 關鍵字規則 > SQL命令規則。依次檢查每個規則集，以確定是否存在匹配的限流規則。

del_ccl_rule：刪除規則

命令模板
```
dbms_ccl.del_ccl_rule(<Id>);
```

範例程式碼

刪除規則ID為15的CCL規則。

mysql> call dbms_ccl.del_ccl_rule(15);

說明

如果刪除的規則不存在，系統會報相應的警告，您可以使用show warnings;查看警告內容。

mysql> call dbms_ccl.del_ccl_rule(100);
  Query OK, 0 rows affected, 2 warnings (0.00 sec)

mysql> show warnings;
+---------+------+----------------------------------------------------+
| Level   | Code | Message                                            |
+---------+------+----------------------------------------------------+
| Warning | 7514 | Concurrency control rule 100 is not found in table |
| Warning | 7514 | Concurrency control rule 100 is not found in cache |
+---------+------+----------------------------------------------------+

show_ccl_rule：查看記憶體中已啟用規則

命令模板
```
dbms_ccl.show_ccl_rule();
```

範例程式碼

mysql> call dbms_ccl.show_ccl_rule();
+------+--------+--------+-------+-------+-------+-------------------+---------+---------+----------+----------+
| ID   | TYPE   | SCHEMA | TABLE | STATE | ORDER | CONCURRENCY_COUNT | MATCHED | RUNNING | WAITTING | KEYWORDS |
+------+--------+--------+-------+-------+-------+-------------------+---------+---------+----------+----------+
|   17 | SELECT | test   | t     | Y     | N     |                30 |       0 |       0 |        0 |          |
|   16 | SELECT |        |       | Y     | N     |                20 |       0 |       0 |        0 | key1     |
|   18 | SELECT |        |       | Y     | N     |                10 |       0 |       0 |        0 |          |
+------+--------+--------+-------+-------+-------+-------------------+---------+---------+----------+----------+

關於MATCHED、RUNNING和WAITTING的說明如下。

參數	說明
MATCHED	規則匹配成功次數。
RUNNING	此規則下正在並發執行的線程數。
WAITTING	此規則下正在等待執行的線程數。

flush_ccl_rule：規則生效

命令模板
如果您直接操作了表concurrency_control修改規則，規則不能立即生效，您需要讓規則重新生效。命令如下：
```
dbms_ccl.flush_ccl_rule();
```

範例程式碼

mysql> update mysql.concurrency_control set CONCURRENCY_COUNT = 15 where Id = 18;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> call dbms_ccl.flush_ccl_rule();
Query OK, 0 rows affected (0.00 sec)

功能測試

測試規則

設計如下三條規則對應三個維度：

-- SELECT命令動作表sbtest1並發數為3
call dbms_ccl.add_ccl_rule('SELECT', 'test', 'sbtest1', 3, '');

-- SELECT命令關鍵字sbtest2並發數為2
call dbms_ccl.add_ccl_rule('SELECT', '', '', 2, 'sbtest2');  

-- SELECT命令並發數為2
call dbms_ccl.add_ccl_rule('SELECT', '', '', 2, '');

測試情境
使用sysbench進行測試，情境如下：
- 64 threads
- 4 tables
- select.lua

測試結果

查看規則並發數情況如下：

mysql> call dbms_ccl.show_ccl_rule();
+------+--------+--------+---------+-------+-------+-------------------+---------+---------+----------+----------+
| ID   | TYPE   | SCHEMA | TABLE   | STATE | ORDER | CONCURRENCY_COUNT | MATCHED | RUNNING | WAITTING | KEYWORDS |
+------+--------+--------+---------+-------+-------+-------------------+---------+---------+----------+----------+
|   20 | SELECT | test   | sbtest1 | Y     | N     |                 3 |     389 |       3 |        9 |          |
|   21 | SELECT |        |         | Y     | N     |                 2 |     375 |       2 |       14 | sbtest2  |
|   22 | SELECT |        |         | Y     | N     |                 2 |     519 |       2 |       34 |          |
+------+--------+--------+---------+-------+-------+-------------------+---------+---------+----------+----------+
3 rows in set (0.00 sec)

查看RUNNING列，符合預期的並行數量。