【7】Statement Queue
背景
MySQL 的 server 层和引擎层在 statement 并发执行过程中，有很多串行化的点，在 DML 语句中，事务锁冲突比较常见，InnoDB 中事务锁的最细粒度是行级锁，如果语句针对相同行进行并发操作，会导致冲突比较严重，系统吞吐量会随着并发的增加而递减。
AliSQL 设计了针对语句的排队机制，相同的行或者不同的语句进行分桶排队，尽可能的把具有相同冲突可能的在一个桶内排队，减少 conflict 的开销。
语法
变量
系统提供了两个变量来定义 CCL queue 的 bucket 数量和大小。
1. ccl_queue_bucket_count
表示：一共有多少个bucket， 默认值：4，取值范围：[1, 64]
2. ccl_queue_bucket_size
表示：一个 bucket 允许并发数是多少， 默认值：64， 取值范围：[1, 4096]
Hint
系统支持两个 hint语法：
- CCL_QUEUE_VALUE
根据 value 的值进行 HASH 分桶
语法：
/*+ CCL_QUEUE_VALUE([INT | STRING)] */
例子：
UPDATE /*+ ccl_queue_value(1) */ t
SET c=c+1
WHERE id = 1;
UPDATE /*+ ccl_queue_value('xpchild') */ t
SET c=c+1
WHERE name = 'xpchild';- CCL_QUEUE_FIELD
根据 where 条件中的 field 指定的值进行 hash 分桶
语法：
/*+ CCL_QUEUE_FIELD(STRING) */
例如：
UPDATE /*+ ccl_queue_field("id") */ t
SET c=c+1
WHERE id = 1 AND name = 'xpchild';
在 WHERE 条件中查找 id 字段指定的条件常量值来进行分桶注意： CCL_QUEUE_FIELD 填入的字段名字， 在 WHERE 条件的查找过程中：
- 只支持对裸字段的二元运算符的条件
- 二元运算的右值必须是数字或者字符串
接口
系统支持两个接口进行查询当前的状态：
- dbms_ccl.show_ccl_queue()
mysql> call dbms_ccl.show_ccl_queue();
+------+-------+-------------------+---------+---------+----------+
| ID | TYPE | CONCURRENCY_COUNT | MATCHED | RUNNING | WAITTING |
+------+-------+-------------------+---------+---------+----------+
| 1 | QUEUE | 64 | 1 | 0 | 0 |
| 2 | QUEUE | 64 | 40744 | 65 | 6 |
| 3 | QUEUE | 64 | 0 | 0 | 0 |
| 4 | QUEUE | 64 | 0 | 0 | 0 |
+------+-------+-------------------+---------+---------+----------+
4 rows in set (0.01 sec)CONCURRENCY_COUNT: 最大并发数
MATCHED： 命中规则的累积数量
RUNNING：当前并发的数量
WAITTING: 当前等待的数量
- dbms_ccl.flush_ccl_queue()
清理内存中的状态， 重新加载
mysql> call dbms_ccl.flush_ccl_queue(); Query OK, 0 rows affected (0.00 sec)
mysql> call dbms_ccl.show_ccl_queue();
+------+-------+-------------------+---------+---------+----------+
| ID | TYPE | CONCURRENCY_COUNT | MATCHED | RUNNING | WAITTING |
+------+-------+-------------------+---------+---------+----------+
| 1 | QUEUE | 64 | 0 | 0 | 0 |
| 2 | QUEUE | 64 | 0 | 0 | 0 |
| 3 | QUEUE | 64 | 0 | 0 | 0 |
| 4 | QUEUE | 64 | 0 | 0 | 0 |
+------+-------+-------------------+---------+---------+----------+
4 rows in set (0.00 sec)效果
针对单行进行并发 update 的场景下，目前进行的测试，相比较原生的 MySQL， AliSQL 有接近 4 倍的提升。
配合 outline 在线修改
为了能够快速在线修改 SQL statement 的并发控制，而不介入冗长的应用业务代码的修改，这里可以使用 AliSQL 提供的 Outline 来配合， 下面使用 sysbench 的 update_non_index 作为一个例子：
测试环境：
测试表结构：
CREATE TABLE `sbtest1` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`k` int(10) unsigned NOT NULL DEFAULT '0',
`c` char(120) NOT NULL DEFAULT '',
`pad` char(60) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `k_1` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 MAX_ROWS=1000000;测试语句：
UPDATE sbtest1 SET c='xpchild' WHERE id=0;测试脚本：
./sysbench
--mysql-host= {$ip}
--mysql-port= {$port}
--mysql-db=test
--test=./sysbench/share/sysbench/update_non_index.lua
--oltp-tables-count=1
--oltp_table_size=1
--num-threads=128
--mysql-user=u0测试过程
1. 在线增加 outline
mysql> CALL DBMS_OUTLN.add_optimizer_outline('test', '', 1,
' /*+ ccl_queue_field("id") */ ',
"UPDATE sbtest1 SET c='xpchild' WHERE id=0");
Query OK, 0 rows affected (0.01 sec)2. 查看 outline 并验证
mysql> call dbms_outln.show_outline();
+------+--------+------------------------------------------------------------------+-----------+-------+------+--------------------------------+------+----------+---------------------------------------------+
| ID | SCHEMA | DIGEST | TYPE | SCOPE | POS | HINT | HIT | OVERFLOW | DIGEST_TEXT |
+------+--------+------------------------------------------------------------------+-----------+-------+------+--------------------------------+------+----------+---------------------------------------------+
| 1 | test | 7b945614749e541e0600753367884acff5df7e7ee2f5fb0af5ea58897910f023 | OPTIMIZER | | 1 | /*+ ccl_queue_field("id") */ | 0 | 0 | UPDATE `sbtest1` SET `c` = ? WHERE `id` = ? |
+------+--------+------------------------------------------------------------------+-----------+-------+------+--------------------------------+------+----------+---------------------------------------------+
1 row in set (0.00 sec)3. 验证 outline 生效
mysql> explain UPDATE sbtest1 SET c='xpchild' WHERE id=0;
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| 1 | UPDATE | sbtest1 | NULL | range | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | Using where |
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> show warnings;
+-------+------+-----------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------+
| Note | 1003 | update /*+ CCL_QUEUE_FIELD('id') */ `test`.`sbtest1` set `test`.`sbtest1`.`c` = 'xpchild' where (`test`.`sbtest1`.`id` = 0) |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
4. 查看 ccl queue 初始状态
mysql> call dbms_ccl.show_ccl_queue();
+------+-------+-------------------+---------+---------+----------+
| ID | TYPE | CONCURRENCY_COUNT | MATCHED | RUNNING | WAITTING |
+------+-------+-------------------+---------+---------+----------+
| 1 | QUEUE | 64 | 0 | 0 | 0 |
| 2 | QUEUE | 64 | 0 | 0 | 0 |
| 3 | QUEUE | 64 | 0 | 0 | 0 |
| 4 | QUEUE | 64 | 0 | 0 | 0 |
+------+-------+-------------------+---------+---------+----------+
4 rows in set (0.00 sec)5. 开启测试
sysbench
--mysql-host= {$ip}
--mysql-port= {$port}
--mysql-db=test
--test=./sysbench/share/sysbench/update_non_index.lua
--oltp-tables-count=1
--oltp_table_size=1
--num-threads=128
--mysql-user=u06. 验证测试效果
mysql> call dbms_ccl.show_ccl_queue();
+------+-------+-------------------+---------+---------+----------+
| ID | TYPE | CONCURRENCY_COUNT | MATCHED | RUNNING | WAITTING |
+------+-------+-------------------+---------+---------+----------+
| 1 | QUEUE | 64 | 10996 | 63 | 4 |
| 2 | QUEUE | 64 | 0 | 0 | 0 |
| 3 | QUEUE | 64 | 0 | 0 | 0 |
| 4 | QUEUE | 64 | 0 | 0 | 0 |
+------+-------+-------------------+---------+---------+----------+
4 rows in set (0.03 sec)ccl queue 显示命中了10996 次排队， 当前运行并发63个，排队等待4个。
mysql> call dbms_outln.show_outline();
+------+--------+-----------+-----------+-------+------+--------------------------------+--------+----------+---------------------------------------------+
| ID | SCHEMA | DIGEST | TYPE | SCOPE | POS | HINT | HIT | OVERFLOW | DIGEST_TEXT |
+------+--------+-----------+-----------+-------+------+--------------------------------+--------+----------+---------------------------------------------+
| 1 | test | xxxxxxxxx | OPTIMIZER | | 1 | /*+ ccl_queue_field("id") */ | 115795 | 0 | UPDATE `sbtest1` SET `c` = ? WHERE `id` = ? |
+------+--------+-----------+-----------+-------+------+--------------------------------+--------+----------+---------------------------------------------+
1 row in set (0.00 sec)outline 显示命中了115795 次。
Ccl queue 可以配合着 outline 进行在线修改业务，方便快捷。
