避免鎖表：為Update語句中的Where條件添加索引字段

作者：修己xj 2024-05-27 00:02:00

在編寫Update語句時，務必注意Where條件中涉及的字段是否有索引支持。避免全表鎖的關鍵在于優化查詢，利用索引提高查詢效率，減少系統性能的影響。通過合理地設計索引，并確保Update語句中的Where條件包含索引字段，可以有效地提升數據庫的性能和并發能力。

最近在灰度環境中遇到一個問題：某項業務在創建數據時耗時異常長，但同樣的代碼在預發環境中并未出現此問題。起初我們以為是調用第三方接口導致的性能問題，但通過日志分析發現第三方接口的響應時間正常。最終，我們發現工單表的數據入庫SQL一直處于等待狀態。深入分析后，問題的核心暴露出來：另一業務流程中對工單表執行更新（UPDATE）操作的SQL，其where子句中涉及的字段缺少必要的索引，導致其他業務在操作表中的數據時需要等待該更新完成。今天就和大家分享一下這個經驗。

問題描述

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));

    }


}

我們首先調用修改方法，然后在調用新增方法，可以看到新增的接口會一直等待修改的接口完成之后才會執行完成。

圖片

然后我們給表bus_pages的mark__id字段創建索引

圖片

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

圖片

注意：并不是創建了索引就不會鎖表，當我們的索引失效時，也會鎖表

命令行查看（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;

總結

責任編輯：武曉燕來源：修己xj