MySQL性能优化盲区(高并发情况下,事务内的数据先更新还是先查询?)

近期看到了一个前阿里资深开发的学术分析视频:
高并发情况下,一个事务内有更新操作还有查询操作,那是先更新好,还是先无锁查询好?
仅70秒的视频,深感学问太深,但是海哥讲的有待补充,于是写下了这篇文章,作为补充。

鸣谢:前阿里资深开发极海Channel的技术分享。

先说答案

这是个开放性的问题,必须看业务场景,抛开业务场景谈架构设计,都是耍流氓。

  • 场景1:如果update语句的参数操作依赖于查询操作,那么必须先查询,再更新,否则update语句的参数都凑不齐。
  • 场景2:如果update语句的参数操作不依赖于查询操作,但两个操作的是一张表,业务强制要求select获取的数据必须是最新的,则也需要先更新再读取。
  • 场景3:以上两种情况除外,则优先考虑先查询再更新。

场景1受参数限制,顺序毋庸置疑。
场景2受业务限制,顺序毋庸置疑。
场景3是为了性能优化,才去选择的方案。

测试表

CREATE TABLE `cs` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `num` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '数字列',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `temp`.`cs` (`id`, `num`) VALUES (1, 1);
INSERT INTO `temp`.`cs` (`id`, `num`) VALUES (2, 2);
INSERT INTO `temp`.`cs` (`id`, `num`) VALUES (3, 3);


假设场景2的代码如下:
start transaction;
update cs set num = 13 where id = 1;
SELECT * FROM `cs`;
commit;


场景3的代码如下:
start transaction;
SELECT * FROM `cs`;
update cs set num = 13 where id = 1;
commit;

对场景2,同一个事务内,先执行update,事务没提交,能读取到最新的数据吗?

能。

步骤 SQL 补充
1 start transaction; 开启事务
2 select num from cs where id = 1; num值是1
3 update cs set num = 12 where id = 1; 更新num值为12
4 select num from cs where id = 1; num值是12
5 rollback; 回滚事务

都说不加锁的select是快照读,为什么select还能读取当前事务的最新数据?

根据现象反推,个人认为:
不加锁的select是快照读,针对的是事务之外的不加锁的select,MySQL RR的隔离级别,只要更新的事务未提交,其它事务就读取不到更改的新数据。
当前事务内的select,就是不加锁的当前读(个人称谓)。当然,真正的当前读概念是为了保证读的最新数据,必须加锁。

对场景3的性能优化原理分析

得知道4个前提:

  1. 场景3的实现,不会影响业务和开发。
  2. 因为select没有加S或X锁,所以不会阻塞。
  3. update语句会上X锁,可能是行锁、间隙锁,或者表锁(受索引、where条件的影响),所以并发请求过来后,其它事务的update有被阻塞的可能。
  4. 用事务,就需要InnoDB引擎,InnoDB引擎支持行级锁,如果确定update的X锁,在并发情况下锁定的范围没有交集,这种优化方式起不到作用,这意味着不会阻塞。
  • 如果先更新再查询:
    分析原理:
    事务A的update语句会上锁,并发情况下阻塞事务B的update操作,如果事务A的select是个慢查询,事务A的X锁释放需要等到事务提交,而不是update语句本身执行完毕,这就意味着事务A select的环节,X锁也未释放,从而阻塞其它事务的update,降低性能。
    实操模拟:
步骤 事务A 事务B 补充
1 start transaction; start transaction; 双方开启事务,模拟并发请求
2 update cs set num = 1234 where id = 1; update cs set num = 1234; 两个不同where范围的update,模拟线上的场景
3 / 阻塞 事务A的行X锁,阻塞了事务B的表X锁
4 select * from cs where id = 1; 阻塞 这一步很重要,优化就是为了避免这一步的阻塞耗时,特别是慢查询
5 commit; 阻塞 事务A提交
6 / select * from cs where id = 1; 事务A完成,事务B不会再阻塞了
7 / commit; 结束事务B
  • 如果先查询再更新:
    分析原理:事务A的select语句不会上锁,此时事务不会导致事务B阻塞,如果执行到事务A执行到update,才回去上X锁,直到事务提交锁资源释放,即使事务A的select是一个慢查询,也不会加大事务A释放锁资源的事件,进而减少事务B的阻塞时间。
    实操模拟:
步骤 事务A 事务B 补充
1 start transaction; start transaction; 双方开启事务,模拟并发请求
2 select * from cs where id = 1; select * from cs where id = 1; 两个事务不加锁不阻塞,这一步的阻塞时间省了
3 update cs set num = 1234 where id = 1; update cs set num = 1234; 两个事务更新
4 / 阻塞 两个X锁范围有冲突,阻塞
5 commit; 阻塞 事务A提交
6 / commit; 事务B提交

所以说,只要业务允许,调整SQL语句的执行顺序,高并发情况下,就能得到不小的性能提升,但是这一点很容易忽略。

热门相关:美少女战士Sailor Moon 新辉夜岛传说   邻家小姐   善良的姐夫   跟踪邻家女孩   节拍器