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