MySQL在创建索引之前一定要想到的事情

MySQL在5.5.3版本引入了metadata lock
他的本意是解决之前版本事务隔离特性的几个bug,但是引入的问题也不小.先说说MySQL的事务吧.
Oracle的事务指的是需要分配回滚段的SQL语句,也就是说select并不是oracle事务的一部分.
比如运行一个查询,然后在另外一个会话查询v$transaction,并不会有任何相关的信息.直到事务中出现insert,update,delete。
而innodb的事务包括select查询.
无论事务隔离级别是可重复读,还是读提交,只要有查询,事务就开始了
下图证明了在5.6.15,设置了autocommit=0之后,运行一个查询就可以开启一个事务.
第一个会话运行查询.第二个会话,运行 show engine innodb statusG 查看事务情况可以看到id为1的线程,已经开始了一个事务.为什么Oracle的事务仅包括insert,update和delete的语句,而innodb的事务包括所有的语句呢？
我觉得这个和厂商支持的隔离级别有很大的关系.
众所周知,Oracle仅仅支持读提交和串行化两种事务隔离级别,而读提交是绝大多数数据库的选择.
读提交意味着可以出现幻读和不可重复读,那么从实现原理的角度,Oracle可以在语句（Statement级别）开始的时候,记录SCN然后应用MVCC查询.每个查询只需要记录自己开始的SCN即可.而语句开始的SCN和事务并没有关系.所以Oracle的事务,并不包括查询.而innodb支持可重复读隔离级别,也就是说在一个事务中,无论运行多少次查询,结果都必须是一致的.
（innodb不仅支持可重复读,并且使用间隙锁在可重复读级别避免了幻读,当然这也带来了很多问题..）
所以它记录的不是每个查询语句的LSN,而是事务第一个语句发生时的LSN,无论第一个语句是查询,还是修改.
innodb在可重复读的级别下,查询用事务开始时的LSN应用MVCC,与Oracle不同的是,innodb查询回滚段中小于事务开始的LSN的数据版本,
而oracle查询回滚段中小于语句SCN的数据版本.
也就是说,同样都是MVCC,oracle是语句级的,innodb是事务级的这里有一个问题,按说事务包括查询是因为可重复读隔离级别的需要,但是innodb读提交隔离级别同样也将查询作为了事务的一部分.
可能是因为架构或者代码实现层面的问题吧.
不管怎么样,Innodb就是这么做了.然后再说说metadata lock
在5.5.3之前,metadata lock是语句级的,这实际上破坏了事务的一致性.
比如一个事务,在可重复读隔离级别,运行两次查询,居然结果不一致.这正是因为metadata lock是语句级造成的问题,
在两个查询的间隔,另外一个会话执行了truncate table.
所以再次运行查询,没有任何结果.MySQL为了解决这个问题,在5.5.3将metadata lock提升为事务级别的锁.
任何DDL都需要先获得metadata lock,但是这个锁需要等事务结束的时候释放.
同样的实验,在5.6.13就变成这样的了.
第一个会话的事务没有结束,那么第二个会话的DDL就被阻塞使用show processlist可以看到DDL语句在等待第一个会话事务的metadata lock通过这种方式,就保证了可重复读隔离级别下,事务的一致性.和之前提到的查询也作为事务的一部分一样,innodb并没有为读提交量身定制一些东西,
比如读提交并不需要查询作为事务的一部分
和读提交并不需要事务级别的metadata lock.
可能是出于架构层面的问题,很多可重复读的特性强加在了读提交上,
所以一旦这些特性出现问题,即使将隔离级别降为读提交也不能避免.接下来问题来了,
刚才的DDL被metadata lock阻塞,这个DDL还会进一步阻塞其他的事务.甚至是查询（查询是innodb事务的一部分.）这就有点抓狂了,因为这个时候,系统其实已经Hung了.
假设id为1的线程持有metadata lock 没有提交,
id为2的线程进行DDL,然后被阻塞在线程1的metadata锁上,
这时,数据库依次来了8个查询,他们都阻塞在了线程2上.
假如线程1的事务不结束,其他的线程都被阻塞.
即使线程1的事务结束了..也是后面8个事务依次获得metadata锁,与此同时,这个DDL可能又阻塞了80个事务..这时候,系统的并发为1,这个DDL可能永远不能执行.并且这种情况不在死锁检测的范围内.
它的锁超时时间,由lock_wait_timeout参数控制,默认是31536000（一年,坑爹吧）MySQL虽然保证了事务的一致性,避免了bug,但是引入的问题却可能让我这样的初级dba丢了饭碗..最后梳理一下可能引发metadata lock连环阻塞的情况
1.在有其他事务运行的时候,进行DDL操作（alter table;truncate;）
2.在mysqldump运行的时候,进行DDL操作.（想想就觉得坑爹）
3.在Master-Slave复制环境,在Slave运行查询,会导致Master传过来的DDL阻塞.导致复制延迟增大.
4.创建索引（...）作为初级dba来说,为了保住饭碗,可以有两个动作
1.将lock_wait_timeout参数调低
2.在运行DDL之前,查看事务是否频繁,在运行DDL之后,开启另外一个会话,使用show processlist查看是否被metadata lock阻塞.
一旦阻塞,先Kill ddl的操作.MySQL索引之隔离列 http://www.linuxidc.com/Linux/2012-08/69405.htmMySQL索引之哈希索引 http://www.linuxidc.com/Linux/2012-08/69404.htmMySQL索引之前缀索引和索引选择性 http://www.linuxidc.com/Linux/2012-08/69403.htmMySQL索引使用率监控 http://www.linuxidc.com/Linux/2012-05/61176.htmMySQL索引优化和in or替换为union all http://www.linuxidc.com/Linux/2012-08/69364.htmMySQL索引设计的原则 http://www.linuxidc.com/Linux/2014-10/107761.htm本文永久更新链接地址