1. 前言
在并发量很低的情况下，mysql的响应时延一切正常，一旦并发量上去了，mysql就会出现死锁的情况，你有没有遇到过？到底是是什么原因导致的呢，让我们一起看看真实的案例。
2.遇到的问题
先介绍一下我们的库表结构，数据库表中的数据为500w
sql
代码解读
复制代码
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为
csharp
代码解读
复制代码
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语句排队等待更新
js
代码解读
复制代码
update t_award
set identifier=?, identifierType=?
where pool_id=? and identifier is null and status=0 and is_redeemed=0 limit 1;
紧接着出现了死锁的情况
js
代码解读
复制代码
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);
