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

為了應對突發的資料庫請求流量、資源消耗過高的語句訪問以及SQL訪問模型的變化，保證MySQL執行個體持續穩定運行，阿里雲提供基於語句規則的並發控制CCL（Concurrency Control），並提供了工具包（DBMS_CCL）便於您快捷使用。

前提條件

執行個體版本如下：

MySQL 8.0
MySQL 5.7

注意事項

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	並發數。
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>');

樣本：

增加規則，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表的SELECT語句的並發數為20。

mysql> call dbms_ccl.add_ccl_rule('SELECT', 'test', 't', 20, '');

說明

Id越大，規則的優先順序越高。

add_ccl_template_rule
增加模板規則。命令如下：
```
dbms_ccl.add_ccl_rule('<Type>','<Schema_name>','<Table_name>',<Concurrency_count>,'', 'Template_sql');
```
說明
- Table_name無需填寫。Template_sql不可為空並且只能填一條SQL。
- 僅適用於MySQL 8.0版本（小版本為20230630及之後版本）。
樣本：
增加模板規則，test庫模板為'SELECT c FROM t1 WHERE id=?'的SQL並發數為30。
```
call dbms_ccl.add_ccl_rule('TEMPLATE', 'test', '', 30, '', 'SELECT c FROM t1 WHERE id=4');
```

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列，符合預期的並行數量。