避免锁表:为Update语句中的Where条件添加索引字段

简介: 在一个灰度环境中,某业务创建数据时出现异常延迟,原本以为是第三方接口问题,但日志显示接口响应正常。进一步排查发现,工单表的SQL插入操作因另一个业务的无索引UPDATE操作阻塞。具体问题在于UPDATE语句的where子句涉及字段缺失索引,导致锁表并影响并发性能。通过复现问题并为相关字段添加索引,解决了阻塞问题。重要的是,在编写UPDATE语句时要注意Where条件字段的索引,以优化查询并减少锁表影响。

最近在灰度环境中遇到一个问题:某项业务在创建数据时耗时异常长,但同样的代码在预发环境中并未出现此问题。起初我们以为是调用第三方接口导致的性能问题,但通过日志分析发现第三方接口的响应时间正常。最终,我们发现工单表的数据入库SQL一直处于等待状态。深入分析后,问题的核心暴露出来:另一业务流程中对工单表执行更新(UPDATE)操作的SQL,其where子句中涉及的字段缺少必要的索引,导致其他业务在操作表中的数据时需要等待该更新完成。今天就和大家分享一下这个经验。

_20240525233236.jpg

问题描述

mysql 修改数据时,如果where条件后的字段未加索引或者未命中索引会导致锁表。这种锁表行为会阻塞其他事务对该表的访问,显著降低并发性能和系统响应速度。

问题复现

我们在本地准备环境复现下,本地环境mysql使用的版本时8,首先准备一张表bus_pages,除了主键不创建其它索引,准备两个接口,一个修改,一个新增

@Service
@Slf4j
public class BusTestServiceImpl implements BusTestService {
   
   

    @Resource
    private BusPagesService busPagesService;

    @Override
    @Transactional(rollbackFor = Exception.class)
    public void updateInfo() {
   
   
        StopWatch sw = new StopWatch();
        sw.start();
        log.info("修改方法执行开始");
        LambdaUpdateWrapper<BusPagesEntity> updateWrapper = new LambdaUpdateWrapper();
        updateWrapper.eq(BusPagesEntity::getMarkId,18);
        updateWrapper.set(BusPagesEntity::getPage,LocalDateTime.now().toString());
        busPagesService.update(updateWrapper);

        try {
   
   
            Thread.sleep(40*1000);
        } catch (InterruptedException e) {
   
   
            throw new RuntimeException(e);
        }
        sw.stop();
        log.info("修改方法执行结束,耗时{}s",sw.getTime(TimeUnit.SECONDS));

    }

    @Override
    public void saveInfo() {
   
   
        StopWatch sw = new StopWatch();
        sw.start();
        log.info("新增方法执行开始");
        BusPagesEntity busPagesEntity = new BusPagesEntity();
        busPagesEntity.setPage(LocalDateTime.now().format(DateTimeFormatter.ISO_LOCAL_TIME));
        busPagesService.save(busPagesEntity);
        sw.stop();
        log.info("新增方法执行结束,耗时{}s",sw.getTime(TimeUnit.SECONDS));

    }


}

我们首先调用修改方法,然后在调用新增方法,可以看到新增的接口会一直等待修改的接口完成之后才会执行完成。

_20240525223958.jpg

然后我们给表bus_pagesmark__id字段创建索引

_20240525224441.jpg

然后在执行修改及新增接口,可以看到新增接口不会在等待修改接口执行完在去执行了

_20240525224736.jpg

注意: 并不是创建了索引就不会锁表,当我们的索引失效时,也会锁表

命令行查看(mysql版本8.0)

  • 查看被锁定的表
show OPEN TABLES where In_use > 0;

此命令用于列出当前正在使用中的表,也就是说那些被锁定或正在进行某些操作(如读写操作)的表。

  • 查看正在等待锁资源的查询
select * from performance_schema.data_lock_waits;
select * from sys.innodb_lock_waits;
  • 查看锁定数据
select * from performance_schema.data_locks;
  • 查看正在运行中的事务或命令的详情
select * from information_schema.innodb_trx;

总结

在编写Update语句时,务必注意Where条件中涉及的字段是否有索引支持。避免全表锁的关键在于优化查询,利用索引提高查询效率,减少系统性能的影响。通过合理地设计索引,并确保Update语句中的Where条件包含索引字段,可以有效地提升数据库的性能和并发能力。

目录
相关文章
|
1月前
|
SQL 关系型数据库 MySQL
|
1月前
|
关系型数据库 索引
select for update有什么含义,会锁表还是锁行还是其他。
select for update有什么含义,会锁表还是锁行还是其他。
|
10月前
|
SQL 移动开发 关系型数据库
MySQL一张表和另一张表符合条件的第一条数据关联
MySQL一张表和另一张表符合条件的第一条数据关联
|
1月前
|
SQL 数据库
SQL INSERT INTO 语句详解:插入新记录、多行插入和自增字段
SQL INSERT INTO 语句用于在表中插入新记录。
280 0
|
9月前
数据更新语句INSERT语句、UPDATE语句、DELETE语句等,用于向数据表中插入、更新或删除数据。示例
数据更新语句INSERT语句、UPDATE语句、DELETE语句等,用于向数据表中插入、更新或删除数据。示例
60 1
|
SQL 存储 缓存
执行update语句,用没用到索引,区别大吗?
我们都知道,当执行 select 查询语句时,用没用到索引区别是很大的,若没用到索引,一条 select 语句可能执行好几秒或更久,若使用到索引则可能瞬间完成。那么当执行 update 语句时,用没用到索引有什么区别呢,执行时间相差大吗?本篇文章我们一起来探究下。
363 0
|
SQL
**SQL某一表中重复某一字段重复记录查询与处理
sql某一表中重复某一字段重复记录查询与处理   1.查询出重复记录  select 重复记录字段 form  数据表 group by houseno having count(重复记录字段)>1 2.
933 0
|
SQL 索引
SQL创建索引语句及查看表中的索引
SQL创建索引语句及查看表中的索引
|
关系型数据库 MySQL
mysql用一个表中的字段批量更新另一个表中的字段
mysql用一个表中的字段批量更新另一个表中的字段
613 0