大并发热点行更新的两个骚操作

要想db操作的性能足够高，巧妙的设计很重要，事务的操作范围要尽量的小。一般情况下我们都是使用某个orm框架来操作db，这一类框架多数的实现方式都是夸网络多次交互来开启事务上下文和执行sql操作，是个黑盒子，包括对 autocommit 设置的时机也会有一些差异，稍微不注意就会踩坑。

在大并发的情况下加上夸网络多次交互，就不可避免的由于网络延迟、丢包等原因导致事务的执行时间过长，出现雪崩概率会大大增加。建议在性能和并发要求比较高的场景下尽量少用orm，如果非要用尽量控制好事务的范围和执行时间。

大并发db操作的原则就是事务操作尽量少跨网络交互，一旦跨网络使用事务尽量用乐观锁来解决，少用悲观锁，尽量缩短当前 session 持有锁的时间。

下面分享两个在mysql innodb engine 上的大并发更新行的骚操作，这两个骚操作都是尽可能的缩小db锁的范围和时间。

转化update为insert

比较常见的大并发场景之一就是热点数据的 update，比如具有预算类的库存、账户等。

update从原理上需要innodb engine 先获取row数据，然后进行row format转换到mysql服务层，再通过mysql服务器层进行数据修改，最后再通过innodb engine写回。

这整个过程每一个环节都有一定的开销，首先需要一次innodb查询，然后需要一次row format（如果row比较宽的话性能损失还是比较大的），最后还需要一次更新和一次写入，大概需要四个小阶段。

一次update就需要上述四过程开销。此时如果qps非常大，必然会有一定性能开销（这里暂不考虑cache、mq之类的削峰）。那么我们能不能将单个行的热点分散开来，同时将update转换成insert，我们来看下如何骚操作。

我们引入 slot 概念，原来一个row 我们通过多个row来表示，结果通过sum来汇总。为了不让slot成为瓶颈，我们 rand slot，然后将update转换成insert，通过 on duplicate key update 子句来解决冲突问题。

我们创建一个sku库存表。

CREATE TABLE `tb_sku_stock` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `sku_id` bigint(20) NOT NULL,
  `sku_stock` int(11) DEFAULT '0',
  `slot` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_sku_slot` (`sku_id`,`slot`),
  KEY `idx_sku_id` (`sku_id`)
) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=utf8mb4

表中唯一性索引 idx_sku_slot 用来约束同一个 sku_id 不同 slot 。

库存增加操作和减少操作要分开来处理，我们先看增加操作。

insert into tb_sku_stock (sku_id,sku_stock,slot)
values(101010101, 10, round(rand()*9)+1) 
on  duplicate key update sku_stock=sku_stock+values(sku_stock)

我们给 sku_id=101010101 增加10个库存，通过 round(rand()*9)+1 将slot控制在10个以内（可以根据情况放宽或缩小），当 unique key 不冲突的话就一直是insert，一旦发生 duplicate 就会执行 update。（update也是分散的）

我们来看下减少库存，减少库存没有增加库存那么简单，最大的问题是要做前置检查，不能超扣。

我们先看库存总数检查，比如我们扣减10个库存数。

select sku_id, sum(sku_stock) as ss
from tb_sku_stock
where sku_id= 101010101
group by sku_id having ss>= 10 for update

mysql的查询是使用mvcc来实现无锁并发，所以为了实时一致性我们需要加上for update来做实时检查。
如果库存是够扣减的话我们就执行 insert into select 插入操作。

insert into tb_sku_stock (sku_id, sku_stock, slot)
select sku_id,-10 as sku_stock,round(rand() *9+ 1)
from(
    select sku_id, sum(sku_stock) as ss
    from tb_sku_stock
    where sku_id= 101010101
    group by sku_id having ss>= 10 for update) as tmp
on duplicate key update sku_stock= sku_stock+ values(sku_stock)

整个操作都是在一次db交互中执行完成，如果控制好单表的数据量加上 unique key 配合性能是非常高的。

消除 select...for update

大型OLTP系统，都会有一些需要周期性执行的任务，比如定期结算的订单、定期取消的协议等，还有很多兜底的检查、对账程序等都会检查一定时间范围内的状态数据，这些任务一般都需要扫描表里的某个状态字段。

这些查询基本基于类似status状态字段，由于区分度非常低，所以索引基本上在这类场景下没有太大作用。

为了保证扫描出来的数据不会发生并发重复执行的问题会对数据加排他锁，通常就是 select...for update ，那么这部分数据就不会被重复读取到。但是也就意味着当前db线程将block在此锁上，就是一个串行操作。

由于是排查锁，数据的 insert、update 都会受到影响，在 repeatable read （可重复读）且没有 unqiue key 的场合下还会触发Gap lock（间隙锁）。

我们可以通过一个方式来消除 select...for update，并且提高数据并发处理能力。

CREATE TABLE `tb_order` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `order_id` bigint(20) NOT NULL,
  `order_status` int(11) NOT NULL DEFAULT '0',
  `task_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

我们简单创建一个订单表，task_id 是任务id，先让数据结构支持多任务并行。

select order_id from tb_order where order_status=0 limit 10 for update

一般做法是通过select...for update 锁住行。我们换个方法实现同样的效果同时不会存在并发执行问题。

update tb_order set task_id=10 where order_status=0 limit 10;
Query OK, 4 rows affected
select order_id from tb_order where task_id=10 limit 4;

假设我们当前有很多并行任务（1-10），假设task_id=10任务执行，先update抢占自己的数据行。这个操作基本上在单数ms内，然后再通过select 带上自己的taskid获取到属于当前task的行，同时可以带上准确的limit，因为update是会返回受影响行数。

这里会有一个问题，就是执行的task如果由于某个原因终止了怎么办，简单方法就是用一个兜底job定期检查超过一定时间的task，然后将task_id置为空。

作者：王清培（趣头条 Tech Leader）