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.兑奖 1.未兑奖',STATUS TINYINT ( 1 ) NOT NULL COMMENT '0 正常',deleted TINYINT ( 1 ) DEFAULT 0 NOT NULL COMMENT '逻辑删除 0.未删除 1.删除',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);
第一个sql加锁的范围是(0,10),所以阻塞了insert的插入。
注意 mysql加锁的粒度是next key lock
4.4 锁的退化机制
- mysql 加锁的初始粒度是next key lock,遵循左开右闭原则
- 等值查询,如果是唯一索引,退化成行锁
- 等值查询,如果是非唯一索引,向右查找到不等于的等值的第一个停止查询,则退化成间隙锁
- 唯一索引的范围查询,会查找到不满足条件的第一个值为止。
总结
本文通过具体遇到的问题,抽丝剥茧的方式介绍了引起死锁的原因,从而介绍了mysql的索引机制和类型。
重点需要弄懂mysql的加锁机制,方便在日后的工作中使用。