Mysql索引不当引发死锁问题

爱思考的People

已于 2024-08-27 15:26:47 修改

阅读量966

点赞数 9

文章标签： mysql

于 2024-08-27 15:26:31 首次发布

本文链接：https://blog.csdn.net/herry16354/article/details/141604080

版权

1. 前言

在并发量很低的情况下，mysql的响应时延一切正常，一旦并发量上去了，mysql就会出现死锁的情况，你有没有遇到过？到底是是什么原因导致的呢，让我们一起看看真实的案例。

2.遇到的问题

先介绍一下我们的库表结构，数据库表中的数据为500w

CREATE TABLE t_award (
	id BIGINT ( 30 ) NOT NULL PRIMARY KEY,
	award_no VARCHAR ( 30 ) NOT NULL COMMENT '奖券',
	award_pwd VARCHAR ( 100 ) NOT NULL COMMENT '奖券密码',
	pool_id INT ( 20 ) DEFAULT 0 NOT NULL COMMENT 'poolId',
	is_redeemed TINYINT ( 1 ) DEFAULT 0 NOT NULL COMMENT '0.兑奖　１.未兑奖',
	STATUS TINYINT ( 1 ) NOT NULL COMMENT '0 正常',
	deleted TINYINT ( 1 ) DEFAULT 0 NOT NULL COMMENT '逻辑删除　０．未删除　１．删除',
	identifier VARCHAR ( 100 ) NULL,
	identifier_type VARCHAR ( 20 ) NULL COMMENT '身份类型',
	CONSTRAINT award_no UNIQUE ( award_no ),
	CONSTRAINT uniq_ins_identifier UNIQUE ( pool_id, identifier, identifier_type ) 
) ENGINE = INNODB charset = utf8;
CREATE INDEX identifier ON t_award ( identifier );
CREATE INDEX idx_pool ON t_award ( pool_id );
CREATE INDEX idx_ins_stat ON t_award ( pool_id, identifier, STATUS, is_redeemed );

唯一索引： unique (award_no) 和unique (pool_id, identifier, identifier_type)

普通索引：identifier，pool_id ，index(pool_id, identifier,status, is_redeemed)

根据业务场景，需要从抽奖池中获取一个没有兑换过奖的奖券，执行的sql为

select id from t_award where pool_id=? and identifier is null and status=0 and is_redeemed=0 limit 1;

2.1 问题1: 死锁

2.1.1现象

从压测的第30s开始，QPS一下从1000骤降到100，紧接着就是十几了，响应时延TP95从10+ms上升到1s

从mysql的监控上看，有一堆像这样的sql语句排队等待更新

update t_award set identifier=?, identifierType=? where pool_id=? and identifier is null and status=0 and is_redeemed=0 limit 1;

紧接着出现了死锁的情况

trascation 1 WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 70 page no 699223 n bits 144 index PRIMARY of table `test`.`t_ward` trx id 79626302 lock_mode X locks rec but not gap waiting Record lock, heap no 74 PHYSICAL RECORD: n_fields 37; compact format; info bits 0 0: len 8; update t_award set identifier=?, identifierType=? where pool_id=19021 and identifier is null and status=0 and is_redeemed=0 limit 1 trascation2 79626303 HOLDS THE LOCK(S)

2.1.2原因

锁等待原因：mysql对每个update增加排它锁，更新完成之后，释放锁，其他更新操作执行，mysql对于更新操作是串行的。

在大并发量的前提下，如果update语句慢，会造成排队现象。

这个时候我们不禁想问，为什么update语句这么慢呢？

看一下我们创建的索引

index idx_ins_staton t_award (pool_id, identifier,status, is_redeemed);

从执行计划中得知并没有走我们创建的索引，是什么原因呢？

索引失效了

什么原因导致索引失效呢？

(pool_id, identifier,status, is_redeemed) 索引中identifier 该字段允许为null，这会导致identifier后边的字段失效，从而导致整个联合索引会失效,看来是索引的问题

那为什么会死锁呢?

还记得pool_id是普通索引吗，在执行以下sql的时候

where pool_id=19021 and identifier is null and status=0 and is_redeemed=0 limit 1

pool_id加间隙锁，当互相持间隙锁的时候，就造成了死锁，看下图。

当出现死锁的时候，mysql会回滚其中一个事务，其他的会正常执行，如果偶尔出现一次死锁是可以接受的，但如果大面积的出现死锁，整个系统的性能就会下降

2.1.3解决方法

从上边分析的原因得知，造成死锁的原因是有大量并发的更新导致，如果想要解决死锁问题，那我们就要控制并发数量。

那如何控制并发量呢？

在单位时间内减少请求的数量，可以采用在程序中加锁的方式。但这种方式会导致系统的性能下降。

再次分析导致死锁的现象，我们发现在死锁出现之前有大量的锁等待，如果在单位时间内能减少锁等待的update语句数量，是不是可能会出现转机？

紧接着我们把优化的方向放到了 update语句上，怎样才让update语句执行的更快呢？

归根到底，还是索引问题

既然分析清楚了索引失效的原因，那就好解决了，调整一下索引创建顺序是不是就可以了。

在创建索引的时候，把identifier放到了最后，调整后的索引为 (pool_id, status, is_redeemed, identifier);

最终的结果是：我们QPS瞬间就到3000+，瞬间就起飞了。

2.2 问题2 依然是死锁问题

在解决了死锁的问题之后，我们又再一次面临了死锁问题，上次索引顺序已经调整过来了，这次又是什么原因呢?

2.2.1现象

这次是偶发？什么，偶发？程序员最担心的就是偶发，那就意味着很难复现，很难定位问题。

因为这个问题，还发生了一段小插曲

这次的版本迭代目标是是补写单元测试和优化redis操作，不涉及到核心功能的修改，想着快速做一下压测就行了，但因为这个事情还弄的QA非常不开心，这是我俩的对话。

QA：修改啥了，上个版本还没问题的（唉声叹气！！）我：就只做了单元测试和redis的优化。 QA：那为啥之前还好好的，现在不行了？我：那看看是啥具体原因，要不你把上个版本也压测一下，看看结果呢

结果，你们猜怎么着，也是同样的问题，那就证明一个事，这个问题跟咱们这次改的真没关系。

但这也不是QA的问题，还是咱的问题，毕竟之前的代码也是咱写的啊。

这次的问题根本不知道什么原因引起的。

2.2.2 原因

无论多难，问题还是要解决的。

既然和上次的表现一样，按照上次的经验，怀疑可能还是跟索引有问题，于是拿着出现问题的pool_id做了一次执行计划

意想不到的事情竟然出现了

这次依然没走计划中的索引，竟然走了index idx_pool on t_award (pool_id)这个索引，是什么原因呢？

mysql为了查询效率，会对索引做优化，有时候会选错索引。

2.2.3解决方法

既然mysql选错索引了，那我们可以强制mysql走某个索引 force index()不就可以了吗？

事情真的这么简单吗？

如果真的是这样，mysql为什么会对索引做优化了呢？

所以还是不要修改mysql优化索引的机制，有可能会出现意想不到的情况，还是看看自己的索引创建的有没有问题

这是创建的索引

(pool_id, identifier,status, is_redeemed)

pool_id

(pool_id, identifier, identifier_type)

仔细一看，还真是，pool_id这个索引存在的必要性在哪里呢？ 按照最左匹配规则，另外两个索引是可以覆盖到pool_id的。经过测试验证之后，就drop 掉了pool_id这个索引。

这下mysql索引匹配正常了，问题解决了。

3. Mysql索引机制

索引的用处在于能够快速找到你需要的东西，比如你在图书馆找本书，图书管理员告诉你在几号书架第几个，这就是索引。

索引和数据有时候不一定是放在一起的。图书管理员和图书有时候并不在一起。

3.1 索引的类型

先看一个例子

create table stx ( id bigint primary key auto_increment not null, a int not null default 0, b varchar2(12) null, index (b), index(a,b) ) engine = InnoDB;

select * from stx where id=1;
select * from stx wehre b='a';
select id from stx wehre a=1

这三个sql语句分别用到了唯一索引（聚簇索引），普通索引和覆盖索引

唯一索引

sql1 直接通过id=1找到索引，定位到叶子节点，不需要回表就可以查询到数据

普通索引

sql2查找数据的流程为通过普通索引b='a'找到所在位置；

通过a值获取到主键id=1；

通过id=1回表获取到整行数据

sql3 是联合索引但和普通索引的结构是一样的，唯一不同的是sql3不需要回表,为什么呢？

因为id的值可以直接拿到，性能更快一些。

3.2 索引的数据结构

常见的索引结构有Hash、有序数组，B+树

Hash结构

Hash结构最大的优点是快速查找，时间复杂度为O(1), 如果Hash值冲突会存入到链表，如果链表过大就会影响查询效率，链表是挨个遍历查询。

Hash是典型的KV结构

有序数组

有序数组在查找和插入上的效率非常高，比如按照区间查询 between 3 AND 5. 但有序数组不适合动态增加的场景，因为动态增加会涉及到页分裂，从而导致随机磁盘的IO。有序数组适合类似归档的静态数据库。

二叉树

二叉树的特点是左节点小于父节点，父节点小于右节点。

如果要找到leaf3的路径是 root->index2->leaf3,时间复杂度为 O(log(N))

4. Mysql锁机制详解

4.1 行锁 record lock

对一行数据或者多行数据加锁称为行锁，请看下图

开启两个命令窗口，执行sql，结果对比

从实验中看出，对id=1加了行锁。

4.2 间隙锁 gap lock

执行以下sql

set autocommit =0;

update t_award set award_pwd='xxx' where id>1 and id<5;

结果是：3 rows affected in 15 ms

执行

update t_award set award_pwd='aaa' where id=1;

update t_award set award_pwd='aaa' where id=6;

update t_award set award_pwd='aaa' where id=5;

结果是：

我们看到id=1和id=6成功了，但id=5没成功，为什么呢？

因为mysql的锁定区间是(1,5] 左开右闭原则。

间隙锁仅适用于可重复读级别，因为可重复读级别有幻读的问题产生，mysql为了防止幻读的问题出现才有了间隙锁。

幻读是：同一个事务，在同一个时刻读取的数据不一样。

4.3 行锁+间隙锁=next Key lock

看一个例子

CREATE TABLE `t` ( `id` int(11) NOT NULL, `c` int(11) DEFAULT NULL, `d` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `c` (`c`) ) ENGINE=InnoDB; insert into t values(0,0,0),(5,5,5), (10,10,10),(15,15,15),(20,20,20),(25,25,25);