避免鎖表：為Update語句中的Where條件添加索引字段
最近在灰度環(huán)境中遇到一個問題：某項業(yè)務在創(chuàng)建數(shù)據(jù)時耗時異常長，但同樣的代碼在預發(fā)環(huán)境中并未出現(xiàn)此問題。起初我們以為是調用第三方接口導致的性能問題，但通過日志分析發(fā)現(xiàn)第三方接口的響應時間正常。最終，我們發(fā)現(xiàn)工單表的數(shù)據(jù)入庫SQL一直處于等待狀態(tài)。深入分析后，問題的核心暴露出來：另一業(yè)務流程中對工單表執(zhí)行更新（UPDATE）操作的SQL，其where子句中涉及的字段缺少必要的索引，導致其他業(yè)務在操作表中的數(shù)據(jù)時需要等待該更新完成。今天就和大家分享一下這個經(jīng)驗。
問題描述
mysql 修改數(shù)據(jù)時，如果where條件后的字段未加索引或者未命中索引會導致鎖表。這種鎖表行為會阻塞其他事務對該表的訪問，顯著降低并發(fā)性能和系統(tǒng)響應速度。
問題復現(xiàn)
我們在本地準備環(huán)境復現(xiàn)下，本地環(huán)境mysql使用的版本時8，首先準備一張表bus_pages,除了主鍵不創(chuàng)建其它索引，準備兩個接口，一個修改，一個新增
@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("修改方法執(zhí)行開始");
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("修改方法執(zhí)行結束，耗時{}s",sw.getTime(TimeUnit.SECONDS));
}
@Override
public void saveInfo() {
StopWatch sw = new StopWatch();
sw.start();
log.info("新增方法執(zhí)行開始");
BusPagesEntity busPagesEntity = new BusPagesEntity();
busPagesEntity.setPage(LocalDateTime.now().format(DateTimeFormatter.ISO_LOCAL_TIME));
busPagesService.save(busPagesEntity);
sw.stop();
log.info("新增方法執(zhí)行結束，耗時{}s",sw.getTime(TimeUnit.SECONDS));
}
}我們首先調用修改方法，然后在調用新增方法，可以看到新增的接口會一直等待修改的接口完成之后才會執(zhí)行完成。
圖片
然后我們給表bus_pages的mark__id字段創(chuàng)建索引
圖片
然后在執(zhí)行修改及新增接口,可以看到新增接口不會在等待修改接口執(zhí)行完在去執(zhí)行了
圖片
注意： 并不是創(chuàng)建了索引就不會鎖表，當我們的索引失效時，也會鎖表
命令行查看（mysql版本8.0）
- 查看被鎖定的表
show OPEN TABLES where In_use > 0;此命令用于列出當前正在使用中的表，也就是說那些被鎖定或正在進行某些操作（如讀寫操作）的表。
- 查看正在等待鎖資源的查詢
select * from performance_schema.data_lock_waits;select * from sys.innodb_lock_waits;- 查看鎖定數(shù)據(jù)
select * from performance_schema.data_locks;- 查看正在運行中的事務或命令的詳情
select * from information_schema.innodb_trx;總結
在編寫Update語句時，務必注意Where條件中涉及的字段是否有索引支持。避免全表鎖的關鍵在于優(yōu)化查詢，利用索引提高查詢效率，減少系統(tǒng)性能的影響。通過合理地設計索引，并確保Update語句中的Where條件包含索引字段，可以有效地提升數(shù)據(jù)庫的性能和并發(fā)能力。
