【原】mysql的for update优化心得

场景

一个消息表，需要被多个节点抓取，存在并发的情况，要求节点抓取的数据不能重复。

消息表定义

-- 备注：mysql5.5
CREATE TABLE `msg_tbl` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `state` tinyint(4) DEFAULT NULL COMMENT '消息状态.0=未抓取,1=已抓取',
  `type` int(11) DEFAULT NULL COMMENT '消息类型',
  `content` varchar(128) DEFAULT NULL COMMENT '消息内容',
  `create_time` datetime DEFAULT NULL COMMENT '消息产生时间',
  PRIMARY KEY (`id`),
  KEY `idx-query` (`state`,`type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

尝试1

SELECT `id` FROM `msg_tbl` where `state`=0 and `type`=1 order by id asc limit 20 for update;

结论：可以解决需求，但会导致表锁，原因是for update只有在限制主键ID时，才会采用行锁，否则会采用表锁。所以要使用for update，必须限制查询表的主键ID。

尝试2

SELECT * FROM `msg_tbl` where `id` in (SELECT `id` FROM `msg_tbl` where `state`=0 and `type`=1 order by `id` asc ) limit 20 for update;

结论：不能解决问题，且会造成DEPENDENT SUBQUERY，从而导致慢查询。原因是子查询的查询次数依赖于外层查询，当外查询数据过多时，会严重影响查询性能。

子查询扩展
mysql 在处理子查询时，会改写子查询。
通常情况下，我们希望由内到外，先完成子查询的结果，然后再用子查询来驱动外查询的表，完成查询。
例如：
select * from test where tid in (select fk_tid from sub_test where gid=10)
通常我们会感性地认为该 sql 的执行顺序是：
sub_test 表中根据 gid 取得 fk_tid(2,3,4,5,6)记录，
然后再到 test 中，带入 tid=2,3,4,5,6，取得查询数据。
但是实际mysql的处理方式为：
select * from test where exists (select * from sub_test where gid=10 and sub_test.fk_tid=test.tid)
mysql 将会扫描 test 中所有数据，每条数据都将会传到子查询中与 sub_test 关联，子查询不会先被执行，所以如果 test 表很大的话，那么性能上将会出现问题。

尝试3

SELECT * FROM `msg_tbl` a,(SELECT `id` FROM `msg_tbl` where `state`=0 and `type`=1 order by `id` asc limit 20) b where a.`id`=b.`id` for update; 

结论：不会造成慢查询，但会造成数据重复抓取。原因是临时表的查询没有采用for update，依然可以读取到正在修改的数据，所以当有并发请求时，可能会取到已被修改过的数据，造成脏读。

尝试4（最终解决方案）

-- 在尝试3基础上外层where语句增加state条件限制
SELECT * FROM `msg_tbl` a,(SELECT `id` FROM `msg_tbl` where `state`=0 and `type`=1 order by id asc limit 20) b where a.`id`=b.`id` and `state`=0  for update; 

结论：能满足需求，且在百万级数据下仍然做到毫秒级查询(当然也跟机器配置有关)。

希望能帮到有需要的人。