MySQL问题两则

这段时间处理了两个比较有意思的MySQL问题,一个死锁的,一个优化的,陡然发现其实自己对MySQL的理解还不深入,很多运行机制也是知其然但不知其所以然,后续还需要好好恶补一下底层知识。

一次不可思议的死锁

假设有如下表结构:

mysql> show create table tt \G;
*************************** 1. row ***************************
       Table: tt
Create Table: CREATE TABLE `tt` (
  `id` int(11) NOT NULL DEFAULT '0',
  `fileid` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `fileid` (`fileid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

启动三个shell,连接MySQL,然后begin开启一个事务,各个shell分别执行对应的更新语句,

shell 1:

shell 1> update tt set id = 2 where fileid = 1;

shell 2:

shell 2> update tt set id = 3 where fileid = 1;

shell 3:

shell 3> update tt set id = 4 where fileid = 1;

假设shell 1先执行,这时候2和3会block,然后shell 1 commit提交,我们发现shell 2执行成功,但是3出现死锁错误,通过show engine innodb status我们得到如下死锁信息:

------------------------
LATEST DETECTED DEADLOCK
------------------------
2015-01-23 14:24:16 10ceed000
*** (1) TRANSACTION:
TRANSACTION 24897, ACTIVE 3 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s)
MySQL thread id 8, OS thread handle 0x10cea5000, query id 138 127.0.0.1 root updating
update tt set id = 4 where fileid = 1
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 495 page no 4 n bits 72 index `fileid` of table `test`.`tt` trx id 24897 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 80000001; asc     ;;
 1: len 4; hex 80000002; asc     ;;

*** (2) TRANSACTION:
TRANSACTION 24896, ACTIVE 8 sec updating or deleting
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1184, 3 row lock(s), undo log entries 2
MySQL thread id 7, OS thread handle 0x10ceed000, query id 136 127.0.0.1 root updating
update tt set id = 3 where fileid = 1
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 495 page no 4 n bits 72 index `fileid` of table `test`.`tt` trx id 24896 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 80000001; asc     ;;
 1: len 4; hex 80000002; asc     ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 495 page no 4 n bits 72 index `fileid` of table `test`.`tt` trx id 24896 lock mode S waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 80000001; asc     ;;
 1: len 4; hex 80000002; asc     ;;

*** WE ROLL BACK TRANSACTION (1)
------------

刚开始碰到这个死锁问题,真心觉得很奇怪,每个事务一条语句,通过一个唯一索引去更新同一条记录,正常来说完全不可能发生死锁,但确确实实发生了。笔者百思不得其解,幸好有google,然后搜到了这篇,一个最不可思议的MySQL死锁分析,虽然触发情况不一样,但是死锁原理都应该类似的,后续如果有精力,笔者将好好深入研究一下。

顺带再说一下,MySQL 加锁处理分析这篇文章也是干活满满,这两篇加起来深入理解了,对MySQL的deadlock就会有一个很全面的认识了。

一次坑爹的优化

我们需要在一张表里面删除某种类型的数据,大概的表结构类似这样:

CREATE TABLE t (
    id INT,
    tp ENUM ("t1", "t2"),
    PRIMARY KEY(id)
) ENGINE=INNODB;

假设我们需要删除类型为t2的数据,语句可能是这样delete from t where tp = "t2",这样没啥问题,但我们这张表有5亿数据,好吧,真的是5亿,所以以后别再跟我说MySQL表存储百万级别数据就要分表了,百万太小case了。

这事情我交给了一个小盆友去帮我搞定,他最开始写出了如下的语句delete from t where tp = "t2" limit 1000,使用limit来限制一次删除的个数,可以了,不过这有个很严重的问题,就是越往后,随着t2类型的减少,我们几乎都是全表遍历来删除,所以总的应该是O(n*n)的开销。

于是我让他考虑主键,每次操作的时候,记录当前最大的主键,这样下次就可以从这个主键之后开始删除了,首先 select id from t where id > last_max_select_id and tp = "t2" limit 1000,然后delete from t where id in (ids),虽然这次优化采用了两条语句,但是通过主键,我们只需要遍历一次表就可以了,总的来说,性能要快的。

但是,实际测试的时候,我们却发现,select这条语句耗时将近30s,太慢了。虽然我们使用了主键,但是MySQL仍然需要不停的读取数据判断条件,加之t2类型的数据在表里面比较少量,所以为了limit 1000这个条件,MySQL需要持续的进行IO读取操作,结果自然是太慢了。

想清楚了这个,其实就好优化了,我们只需要让条件判断在应用层做,MySQL只查询数据返回,语句就是 select id, tp from t where id > last_max_select_id limit 1000,得到结果集之后,自行判断需要删除的id,然后delete。看似我们需要额外处理逻辑,并且网络开销也增大了,但MySQL只是简单的IO读取,非常快,总的来说,性能提升很显著。当然笔者后续还需要更深入的分析。

最后执行,很happy的是,非常快速的就删完了相关数据,而select的查询时间消耗几乎忽略不计。