登录后台

页面导航

本文编写于 162 天前,最后修改于 155 天前,其中某些信息可能已经过时。

mysql索引的小知识点

查询背景: 有一张表,每隔十分钟更新一次数据.更新的数据量不固定.表结构如下

    CREATE TABLE `table_A` (
     `id` int(11) NOT NULL AUTO_INCREMENT,
     `cid` bigint(20) NOT NULL COMMENT '广告组id',
     `hourly` int(11) NOT NULL DEFAULT '0' COMMENT '小时',
     `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '',
     `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '',
     `click_num` int(11) NOT NULL DEFAULT '0' COMMENT '',
     `show_num` int(11) NOT NULL DEFAULT '0' COMMENT '',
     PRIMARY KEY (`id`),
     KEY `idx_hourly` (`hourly`) USING BTREE,
     KEY `idx_cid` (`cid`) USING BTREE,
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='';

其中 hourlycid 分别加有普通普通索引, 两个字段同时查询时,仅有唯一的一条.

在频繁的更新时,就会出现 Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction死锁的情况.

更新语句如下:

update `table_A` set `click_num` = 1, `show_num` = 1 where (`cid` = 1683935250946109 and `hourly` = 2020112412) limit 1

死锁的情况通常是由于针对某一条语句同时修改导致的. 虽然 hourlycid两个字段同时查询时,仅有唯一的一条. 但由于数据库查询只能用到一个索引1.所以导致在执行
update语句是锁定的不是唯一的一条.从而出现死锁.

问题找到了,现在要解决的话也很简单.

  1. 从代码层面修改,首先根据hourlycid 查询出唯一的一条数据,然后根据主键id进行唯一更新.
  2. 从数据表结构进行修改,建立联合索引查询唯一数据.
 KEY `idx_cid_hourly` (`cid`,`hourly`) USING BTREE

注:表结构的修改确实可以处理死锁问题.但是在有些场景下如果单使用hourly作为条件时就不能触发联合索引,还需要在hourly上添加不同索引.导致索引过多.从而在更新和插入时消耗更多的性能.

3.使用laravel框架时可以手动开启事务的重试(重试3次)


DB::transaction(function () {
    
},3);

参考

已有 1 条评论