如何优化热点更新场景_云原生数据库 PolarDB(PolarDB)-阿里云帮助中心

本文介绍了优化热点更新场景的方法。

背景介绍

数据库中数据更新的顺序为lock -> update -> unlock，当对数据库中的同一条记录有大量修改请求时，会造成大量的锁争抢与锁等待。请求量增加会导致TPS下降，延迟飙升。例如秒杀场景中对于商品库存的扣减。

您可以选择在数据库内核中进行批处理，即对该条记录进行的更新操作使用组提交，数据更新的顺序变为lock-> group update -> unlock，从而减少锁争抢。结合流水线处理等优化，可以大大提高该场景的TPS，详情可见测试结果。

使用方法

开启hotspot相关功能，使用高权限账号打开以下两个开关项。
```
set global hotspot=on;
set global hotspot_lock_type=on
```
设置后可使用show global variables like '%hotspot%' 语句查看是否设置成功，若未设置成功，可先检查是否使用的是高权限账号。
检查共享ReadView状态。
```
# 查看共享ReadView是否打开
show variables like '%share_read_view%';
```
如果共享ReadView是打开的状态，则可通过如下指令全局关闭：
```
set global share_read_view = false;
```
重要
全局关闭共享ReadView可能会导致一部分查询变慢，请谨慎考虑。
检查事务类型。
```
show variables like 'transaction_policy';
```
如果事务类型不是XA，则需要将事务类型切换为XA，然后才能使用热点更新能力，命令如下（或者在每次使用前设置session级别）。
```
set global transaction_policy = 'xa'; 
```
执行以下命令，可查看设置是否生效。
```
#首先需要执行begin
BEGIN;
#其次查看是否已成功修改
show variables like 'transaction_policy';
rollback;
```
重要
该修改会导致隔离级别由RR改为RC，请自行判断该变化是否会产生非预期结果。

在业务的update语句中添加inventory hint，并将该语句置为最后的一条。

BEGIN;
UPDATE /*+ commit_on_success rollback_on_fail target_affect_row(number)*/ table_reference 
    SET assignment_list
    [WHERE where_condition];
COMMIT | ROLLBACK; #取决于更新成功还是失败

注意事项

仅适用于PolarDB-X 2.0。
where条件应为主键或唯一键的等值条件，且不支持带有全局索引的表（可包含本地索引）。
若已开启共享read_view，则应当先将共享read_view进行关闭，而后使用热点更新的能力。
Inventory hint的使用场景为单分片事务，无法在跨库场景中使用。
用户需使用COMMIT | ROLLBACK以提交或回滚事务。

Inventory Hint各参数含义

commit_on_success（必选）
如果该语句成功，则进行提交，连同该语句之前的未提交语句一起提交。
rollback_on_fail
如果该语句失败，则进行回滚，连同该语句之前的未提交语句一并回滚。
target_affect_row(number)
校验更新的行数是否符合预期，若不符合则更新失败。

示例

添加commit_on_success以使用组提交等针对热点更新场景的优化（id为主键，使用如下语句对id=1的记录进行更新时，若更新成功则自动提交）
```
BEGIN;
UPDATE /*+ commit_on_success*/ table_test SET c = c - 1 WHERE id = 1;
COMMIT | ROLLBACK;
```

使用rollback_on_fail，可使得更新失败时自动进行回滚

BEGIN;
UPDATE /*+ commit_on_success rollback_on_fail*/ table_test SET c = c - 1 WHERE id = 1;
COMMIT | ROLLBACK;

使用target_affect_row(number)，使得该update语句的预期更新行数为number，若不为number，则更新失败

BEGIN;
UPDATE /*+ commit_on_success rollback_on_fail target_affect_row(1)*/ table_test SET c = c - 1 WHERE id = 1;
COMMIT | ROLLBACK;

在带有inventory hint的update语句前，可对同一个物理库中的表进行DML操作

BEGIN;
INSERT into table_test_2 values (1,1);
UPDATE /*+ commit_on_success rollback_on_fail target_affect_row(1)*/ table_test SET c = c - 1 WHERE id = 1;
COMMIT | ROLLBACK;

查看inventory hint是否生效

使用命令show global status like "%Group_update%"查看组提交状态，Group_update_leader_count一直增加则说明触发了热点组提交的优化逻辑。

mysql> show global status like "%Group_update%";
+---------------------------------------+--------+
| Variable_name                         | Value  |
+---------------------------------------+--------+
| Group_update_fail_count               | 54     |
| Group_update_follower_count           | 962869 |
| Group_update_free_count               | 2      |
| Group_update_group_same_count         | 0      |
| Group_update_gu_leak_count            | 0      |
| Group_update_ignore_count             | 0      |
| Group_update_insert_dup               | 0      |
| Group_update_leader_count             | 168292 |
| Group_update_lock_fail_count          | 0      |
| Group_update_mgr_recycle_queue_length | 0      |
| Group_update_recycle_queue_length     | 0      |
| Group_update_reuse_count              | 23329  |
| Group_update_total_count              | 2      |
+---------------------------------------+--------+
13 rows in set (0.01 sec)

使用show physical full processlist查看update的状态，是否出现hotspot字样。

mysql> show physical full processlist where command != 'Sleep';
+-------+------+-----+---------+-------------+---------+------+-------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Group | Atom | Id  | User    | db          | Command | Time | State                   | Info                                                                                                                                                                                         |
+-------+------+-----+---------+-------------+---------+------+-------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|     0 |    0 |  56 | diamond | test_000001 | Query   |    0 | hotspot wait for commit | /*DRDS /127.0.0.1/12e774cab8800000-128/0// */UPDATE  /*+COMMIT_ON_SUCCESS ROLLBACK_ON_FAIL TARGET_AFFECT_ROW(1) */ `test_hotline_lZTr` AS `test_hotline` SET `b` = (`b` + 1) WHERE (`a` = 1) |
|     0 |    0 | 822 | diamond | test_000001 | Query   |    0 | query end               | /*DRDS /127.0.0.1/12e774c4e9400000-563/0// */UPDATE  /*+COMMIT_ON_SUCCESS ROLLBACK_ON_FAIL TARGET_AFFECT_ROW(1) */ `test_hotline_lZTr` AS `test_hotline` SET `b` = (`b` + 1) WHERE (`a` = 1) |
|     0 |    0 | 831 | diamond | test_000001 | Query   |    0 | hotspot wait for commit | /*DRDS /127.0.0.1/12e774c551000000-509/0// */UPDATE  /*+COMMIT_ON_SUCCESS ROLLBACK_ON_FAIL TARGET_AFFECT_ROW(1) */ `test_hotline_lZTr` AS `test_hotline` SET `b` = (`b` + 1) WHERE (`a` = 1) |
|     0 |    0 | 838 | diamond | test_000000 | Query   |    0 | starting                | show full processlist                                                                                                                                                                        |
+-------+------+-----+---------+-------------+---------+------+-------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
4 rows in set (0.33 sec)

热点更新测试

测试表定义

CREATE TABLE sbtest(id INT UNSIGNED NOT NULL PRIMARY KEY, c BIGINT UNSIGNED NOT NULL);

测试语句

UPDATE /*+ COMMIT_ON_SUCCESS ROLLBACK_ON_FAIL TARGET_AFFECT_ROW(1) */ sbtest SET c=c+1 WHERE id = 1;

测试工具

sysbench

机器规格

4C8 GB×2（两节点）

测试结果

场景	1线程	4线程	8线程	16线程	32线程	64线程	128线程	256线程	512线程
热点更新	298	986	1872	3472	6315	10138	13714	15803	23262
普通更新	318	423	409	409	412	428	448	497	615

以上结果的单位为TPS，即每秒处理的交易数（Transaction per second）；
热点更新的TPS与机器规格、并发请求数和更新语句有关，测试结果仅供参考。