这段时间处理了两个比较有意思的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的查询时间消耗几乎忽略不计。
