避免鎖表：為Update語句中的Where條件添加索引字段
作者：修己xj
在編寫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;總結
在編寫Update語句時，務必注意Where條件中涉及的字段是否有索引支持。避免全表鎖的關鍵在于優化查詢，利用索引提高查詢效率，減少系統性能的影響。通過合理地設計索引，并確保Update語句中的Where條件包含索引字段，可以有效地提升數據庫的性能和并發能力。
責任編輯：武曉燕
來源：
修己xj
