2023年8月3日发(作者：)

Mysql中update+select遇见的问题发⽣的问题⼤概在春节期间，由于特殊时期，公司的产品很多⽆法进展，⼤量的临时性决策，造成了客服访问量增⼤，导致了⼀系列问题，Mysql库居然锁⾏数，影响⾏数100W+，下⾯记录下解决的历程。排查SQL原先的SQL⼤概是这样的update testset param1 = 1where id in ( select id from test where JoinTime > '2020-03-05' limit 0,1)上⾯SQL是造成锁表的语句，原本是SQL Server的执⾏，⼀直没问题，后来切Mysql，转义过来的。步骤⼀怀疑select可能查询没⾛索引，导致查询缓慢，进⾏锁表。⾸先，当数据量过⼤，进⾏分页时，⽐如10W条数据，分页条件为

limit 10000, 5， 那么索引从数据库中查询10005次索引字节，过滤前⾯的10000条，取最后5个，浪费了⼤量的时间在这10000次的随机I/O上⾯，这个时候呢，只能使⽤select⼦表的⽅式，来避免⼤量⽆⽤的数据进⼊buffer pool中，导致Mysql的性能下降。改成⼀下模式：update test t1inner join ( select id

from test where JoinTime > '2020-03-05' limit 0,1) t2 on =

set param1 = 1这样，只有1条数据加⼊buffer pool，减少buffer pool的污染，提⾼效能。但是，这⾥咱们没有使⽤⼀个⼤数据量的分页，其次咱们没有引⽤其他的字段，直接返回⾝为索引的id，显然问题不是上⾯的原因造成的。参考资料：limit性能影响MySQL ORDER BY / LIMIT performrmaance: late row lookups步骤⼆由于SQL为update+select，现在数据库使⽤的是InnerDB，是⾏级锁，当执⾏update+select中的select进⾏扫描的时候，update会把扫描到的数据记录全部锁住，扫描的记录越多，锁住的记录越多，也就是所谓的变扫描边加锁，最后就导致整个数据库的阻塞，程序因为⽆法进⾏数据库操作，导致线程池⼤量阻塞，最后⽤完，就造成了最后的系统崩溃，业务⽆法响应。相当于使⽤了FOR UPDATE，将select的结果进⾏加⾏级锁。MyAsim 只⽀持表级锁，InnerDB⽀持⾏级锁 添加了(⾏级锁/表级锁)锁的数据不能被其它事务再锁定，也不被其它事务修改、删除 。是表级锁时，不管是否查询到记录，都会锁定表。解决发现问题，我们就可以解决这个，因为我的查询是获取最新的⼀条数据进⾏更新，更新也⽐较简单，所以就直接将查询和更新进⾏拆分，先进⾏查询(查询时，如果幂等性要求⾼时，可以加上排他锁

for update， 我这⾥数据没有要求，我就没有加上)，然后根据主键进⾏更新。虽然会造成数据库连接池的浪费，但是避免了⼤量的数据记录锁住。如果是多条数据进⾏更新，查询出来在进⾏插⼊，太复杂，⽽且性能不⾼，这个时候就可以使⽤⼦表的⽅式(步骤⼀中的⽅式)，但是要使⽤索引和⼤数据量的分页，避免⼤⾯积的记录锁定的发⽣。of update: 锁定当前⾏记录，允许其他事物进⾏读取，但是不允许修改oracle: 可以使⽤for update of 进⾏字段的锁定。查询的过程中，发现⼀个有意思的⼩知识，分享给⼤家以问题为切⼊点，全⾯了解根本原因

2023年8月3日发(作者：)

Mysql中update+select遇见的问题发⽣的问题⼤概在春节期间，由于特殊时期，公司的产品很多⽆法进展，⼤量的临时性决策，造成了客服访问量增⼤，导致了⼀系列问题，Mysql库居然锁⾏数，影响⾏数100W+，下⾯记录下解决的历程。排查SQL原先的SQL⼤概是这样的update testset param1 = 1where id in ( select id from test where JoinTime > '2020-03-05' limit 0,1)上⾯SQL是造成锁表的语句，原本是SQL Server的执⾏，⼀直没问题，后来切Mysql，转义过来的。步骤⼀怀疑select可能查询没⾛索引，导致查询缓慢，进⾏锁表。⾸先，当数据量过⼤，进⾏分页时，⽐如10W条数据，分页条件为

limit 10000, 5， 那么索引从数据库中查询10005次索引字节，过滤前⾯的10000条，取最后5个，浪费了⼤量的时间在这10000次的随机I/O上⾯，这个时候呢，只能使⽤select⼦表的⽅式，来避免⼤量⽆⽤的数据进⼊buffer pool中，导致Mysql的性能下降。改成⼀下模式：update test t1inner join ( select id

from test where JoinTime > '2020-03-05' limit 0,1) t2 on =

set param1 = 1这样，只有1条数据加⼊buffer pool，减少buffer pool的污染，提⾼效能。但是，这⾥咱们没有使⽤⼀个⼤数据量的分页，其次咱们没有引⽤其他的字段，直接返回⾝为索引的id，显然问题不是上⾯的原因造成的。参考资料：limit性能影响MySQL ORDER BY / LIMIT performrmaance: late row lookups步骤⼆由于SQL为update+select，现在数据库使⽤的是InnerDB，是⾏级锁，当执⾏update+select中的select进⾏扫描的时候，update会把扫描到的数据记录全部锁住，扫描的记录越多，锁住的记录越多，也就是所谓的变扫描边加锁，最后就导致整个数据库的阻塞，程序因为⽆法进⾏数据库操作，导致线程池⼤量阻塞，最后⽤完，就造成了最后的系统崩溃，业务⽆法响应。相当于使⽤了FOR UPDATE，将select的结果进⾏加⾏级锁。MyAsim 只⽀持表级锁，InnerDB⽀持⾏级锁 添加了(⾏级锁/表级锁)锁的数据不能被其它事务再锁定，也不被其它事务修改、删除 。是表级锁时，不管是否查询到记录，都会锁定表。解决发现问题，我们就可以解决这个，因为我的查询是获取最新的⼀条数据进⾏更新，更新也⽐较简单，所以就直接将查询和更新进⾏拆分，先进⾏查询(查询时，如果幂等性要求⾼时，可以加上排他锁

for update， 我这⾥数据没有要求，我就没有加上)，然后根据主键进⾏更新。虽然会造成数据库连接池的浪费，但是避免了⼤量的数据记录锁住。如果是多条数据进⾏更新，查询出来在进⾏插⼊，太复杂，⽽且性能不⾼，这个时候就可以使⽤⼦表的⽅式(步骤⼀中的⽅式)，但是要使⽤索引和⼤数据量的分页，避免⼤⾯积的记录锁定的发⽣。of update: 锁定当前⾏记录，允许其他事物进⾏读取，但是不允许修改oracle: 可以使⽤for update of 进⾏字段的锁定。查询的过程中，发现⼀个有意思的⼩知识，分享给⼤家以问题为切⼊点，全⾯了解根本原因