大促场景下库存更新 SQL 优化
在文章开始之前,我们做出如下约定:
- 业务服务器与数据库服务器在同机房中;
- 网络请求耗时 3ms,语句处理时间 0.2ms,所有操作都是成功的;
- 请求数量达到数据库瓶颈,在此基础上,
TPS = 1000ms / lockTime
(非锁时间都可并发操作)。
我相信,友好的讨论交流会让彼此快速进步!文章难免有疏漏之处,十分欢迎大家在评论区中批评指正。
两种事务代码,谁更优?
我们售卖一件商品的流程是什么呢?首先要先查询一下商品是否还有库存?如果有库存,我们就创建一个订单,商品库存减去 1(为了方便分析,我们只讨论这种比较简单的情况)。并且,我们还知道上述操作应当封装在一个事务中,如果其中一步失败了,就应当进行回滚。
好的,基于上述的描述,我们有如下两种事务代码的编写方式。
写法 1
begin;
select stock from goods where id = 1;
if (stock > 0) {
insert into order values(...); // 插入订单表
update goods set stock = stock - 1 where id = 1 and stock > 0; // 更新库存
}
if (updateCount > 0) {
commit;
} else {
rollback;
}
写法 2
begin;
select stock from goods where id = 1;
if (stock > 0) {
update goods set stock = stock - 1 where id = 1 and stock > 0; // 更新库存
insert into order values(...); // 插入订单表
}
if (updateCount > 0) {
commit;
} else {
rollback;
}
两种写法的区别就是一个先插入再更新,另一个是先更新再插入。仔细想一想,哪一种写法更优,从 TPS 的角度来考虑一下。
公布答案
第一种写法更优(先插入再更新),TPS 比第二种高得多。
为什么呢?首先,我们要知道上面的每一条数据库操作语句,包括最后的 commit 或者 rollback 都要由业务服务向数据库发送网络请求,并且要等待数据库返回语句执行结果(同步)。
还记得我们在最开始做的约定吗?在计算两种写法的 TPS 之前,我再给上面的代码加些注释,让你更容易理解。
- 业务服务器与数据库服务器在同机房中;
- 网络请求耗时 3ms,语句处理时间 0.2ms,所有操作都是成功的;
- 请求数量达到数据库瓶颈,则
TPS = 1000ms / lockTime
(非锁时间都可并发操作)。
写法 1 注释版
begin;
select stock from goods where id = 1;
if (stock > 0) {
insert into order values(...);
update goods set stock = stock - 1 where id = 1 and stock > 0; // 行锁开始
}
if (updateCount > 0) {
commit; // 网络请求 1
} else {
rollback;
}
// 行锁结束
写法 2 注释版
begin;
select stock from goods where id = 1;
if (stock > 0) {
update goods set stock = stock - 1 where id = 1 and stock > 0; // 行锁开始
insert into order values(...); // 网络请求 1
}
if (updateCount > 0) {
commit; // 网络请求 2
} else {
rollback;
}
// 行锁结束
计算两种写法的 TPS
在计算之前,我们还要再回顾一下关于数据库中锁的基础知识。
1、update 命令会施加一个 X 型记录锁,X 型记录锁是写写互斥的。如果 A 事务对 goods 表中 id = 1 的记录行加了记录锁,B 事务想要对这行记录加记录锁就会被阻塞。
2、insert 命令会施加一个插入意向锁,但插入意向锁是互相兼容的。如果 A 事务向 order 表 insert 一条记录,不会影响 B 事务 insert 一条记录。
3、记录锁要等到事务提交之后才会释放!
好的,基于最开始的约定,代码的注释,以及基础知识,我们可以来计算了。
写法 1 的 TPS
commit 网络请求 1 次,commit 语句执行一次,我们在这里可以先忽略语句执行耗时。
TPS = 1000ms / 3ms = 333.33
写法 2 的 TPS
insert、commit 共两次网络请求,两条语句执行,我们也忽略语句执行耗时。
TPS = 1000ms / 6ms = 166.67
我们可以看到两者的 TPS 差了 2 倍。试想一下,如果事务中有更多的数据库操作,写法 2 的 TPS 会进一步降低。
继续优化
写法 1 是否还有进一步优化的空间呢?update 执行成功与否数据库是知道的,如果省去 commit 这个网络请求,那么 TPS 是多少呢?
TPS = 1000ms / 0.2ms = 5000
(0.2ms 是 commit 语句执行时间)
当然,这个优化就要依靠你们公司的 DBA 了。
总结
当我们在编写一个事务的时候,加行锁的操作应在不影响业务的情况下,尽可能地靠近 commit 语句,这样单行记录的行锁时间才会更短,TPS 会更高。
这篇好文章是转载于:学新通技术网
- 版权申明: 本站部分内容来自互联网,仅供学习及演示用,请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系,请提供相关证据及您的身份证明,我们将在收到邮件后48小时内删除。
- 本站站名: 学新通技术网
- 本文地址: /boutique/detail/tanhgeegbf
-
photoshop保存的图片太大微信发不了怎么办
PHP中文网 06-15 -
Android 11 保存文件到外部存储,并分享文件
Luke 10-12 -
《学习通》视频自动暂停处理方法
HelloWorld317 07-05 -
word里面弄一个表格后上面的标题会跑到下面怎么办
PHP中文网 06-20 -
photoshop扩展功能面板显示灰色怎么办
PHP中文网 06-14 -
微信公众号没有声音提示怎么办
PHP中文网 03-31 -
excel下划线不显示怎么办
PHP中文网 06-23 -
excel打印预览压线压字怎么办
PHP中文网 06-22 -
怎样阻止微信小程序自动打开
PHP中文网 06-13 -
TikTok加速器哪个好免费的TK加速器推荐
TK小达人 10-01