MySQL加索引及导数导致的问题

增加主键索引会锁表

1. 在navicat中开一个窗口，执行以下语句：

BEGIN;
select * from ur_user where id = '0000000053843f8901538447f27c0002';
-- COMMIT;

2. 再开另外一个窗口，执行以下语句：

ALTER TABLE `ur_user` ADD PRIMARY KEY ( `id` ) 

3. 再开第三个窗口，执行以下语句：

SHOW PROCESSLIST;

第三个窗口发现如下内容，说明即使增加主键索引，会锁表。这点毫无疑问，因为我们查询是用的该主键。

如果我们提交第一步中的COMMIT，则会释放表锁，第2步中的增加索引会执行。

猜想应该增加非主键索引应该不会锁表。看下以下示例。

增加二级索引也会锁表

1. 在navicat中开一个窗口，执行以下语句：

BEGIN;
select * from ur_user where id = '0000000053843f8901538447f27c0002';
-- COMMIT;

2. 再开另外一个窗口，执行以下语句：

ALTER TABLE `ur_user` ADD  KEY `idx_mobile_tel` (`mobile_tel`)

3. 再开第三个窗口，执行以下语句：

SHOW PROCESSLIST;

第三个窗口发现如下内容，说明即使增加非主键索引，也同样会锁表。

执行第一个窗口中的COMMIT语句，发现锁表解除。

为啥增加二级索引也会锁表？

先从我们的metadata lock说起，为了在并发环境下维护表元数据的数据一致性，在表上有活动事务（显式或隐式）的时候，不可以对元数据进行写入操作。因此从MySQL5.5版本开始引入了MDL锁（metadata lock），来保护表的元数据信息，用于解决或者保证DDL操作与DML操作之间的一致性。
对于引入MDL，其主要解决了2个问题，一个是事务隔离问题，比如在可重复隔离级别下，会话A在2次查询期间，会话B对表结构做了修改，两次查询结果就会不一致，无法满足可重复读的要求；另外一个是数据复制的问题，比如会话A执行了多条更新语句期间，另外一个会话B做了表结构变更并且先提交，就会导致slave在重做时，先重做alter，再重做update时就会出现复制错误的现象。所以在对表进行上述操作时，如果表上有活动事务（未提交或回滚），请求写入的会话会等待在Metadata lock wait 。
支持事务的InnoDB引擎表和不支持事务的MyISAM引擎表，都会出现Metadata Lock Wait等待现象。一旦出现Metadata Lock Wait等待现象，后续所有对该表的访问都会阻塞在该等待上，导致连接堆积，业务受影响。

所以为了解决这些问题，所以就会在创建索引时会出现锁表的情况。

常见的MDL锁场景

1. 当前有执行DML操作时执行DDL操作，尤其是长事务运行时，阻塞DDL，从而阻塞同表的后续操作，将导致灾难性的后果。
2. 当前有对表的长时间查询或使用mysqldump/mysqlpump时，使用alter会锁住。
3. 显式或者隐式开启事务后未提交或回滚，比如查询完成后未提交或回滚，将导致DDL会被堵住。

其他什么情况会锁表

导数

我们经常会导数，但是如果在正在运行的生产机器上导数，那就会出现问题。当时有同事在导数，并且数据量比较大，导致30个线程全部占用，一旦超过等待时间，就会报错。

我特意看了下运维的脚本，通过mysqldump进行的导数。脚本如下。

mysqldump -uUSER -pPASSWORD --databases DATABASE --ignore-table=DATABASE.TABLE1 --ignore-table=DATABASE.TABLE2 > /tmp/tmp.sql

对一个正在运行的数据库进行备份请慎重！！ 如果一定要 在服务运行期间备份，请添加 --skip-opt选项。如下。

mysqldump --skip-opt -u root --password=123456 dbname >mySQL.SQL

因为MySQL默认是开启–lock-tables参数的，若不需要锁表，则要关闭此参数–skip-lock-tables。可以在不锁表的情况导出数据，速度更快一些。但是不能保证最新数据的导出操作。

参数解释：

  --opt（优化，导入导出快）              

 Same as --add-drop-table, --add-locks, --create-options,   --quick, --extended-insert, --lock-tables, --set-charset, and --disable-keys. Enabled by default, disable with
 
 --skip-opt （忽略优化）        

Disable --opt. Disables --add-drop-table, --add-locks,  --create-options, --quick, --extended-insert,  --lock-tables, --set-charset, and --disable-keys.

InnoDB引擎为行锁，因此，备份时可以不对数据库加锁的操作，可以加选项–single-transaction进行备份。它有一些要求：只能是 innodb 引擎；导出的过程中，不能有任何人执行 alter table, drop table, rename table, truncate table等DDL语句。实际上DDL会被事务所阻塞，因为事务持有表的metadata lock 的共享锁，而DDL会申请metadata lock的互斥锁，所以阻塞了。

mysqldump -uroot -p -A -B -F -R --events --single-transaction|gzip >/tmp/all_$(date +%F).sql.gz

几个使用mysqldump时的报错

1. mysqldump:Got error:2013
   在使用mysqldump的时候（尤其是向NFS上备份的时候），很多人都被’mysqldump:Got error:2013: Lost connection to MySQL server during query when dumping table’的问题困扰，在Manual中对这个问题有一些简单的说明。
   解决办法：增加net_write_timeout可以解决上述的问题的

2. Mysqldump导出AUTO_INCREMENT列的问题
   如果是复制表的结构需要去掉auto_increment的option，可以写个脚本把这个选项去掉

3. Mysqldump过程中遇到的Out of Memory问题处理
   当DB数据量很大的时候，导出数据可加上-q Option。但是如果用了–skip-opt，那么-q Option必须放在–skip-opt的后面

常见处理方式

以上测试是通过结束掉该事务。但是生成很多时候是没办法控制应用的事务提交的。我们通常采用以下方式解决。

1. kill 40968; – 通过kill掉DDL所在的session.
2. 如果show processlist看不到刚才未提交的事务，可以通过 select * from information_schema.innodb_trx\G, 找到未提交事物的sid, 然后 kill 掉，让其回滚。
3. 通过show processlist看不到TableA上有任何操作，在information_schema.innodb_trx中也没有任何进行中的事务。这很可能是因为在一个显式的事务中，对TableA进行了一个失败的操作（比如查询了一个不存在的字段），这时事务没有开始，但是失败语句获取到的锁依然有效，没有释放。从performance_schema.events_statements_current表中可以查到失败的语句。

总之，alter table的语句是很危险的(其实他的危险其实是未提交事物或者长事务导致的)，在操作之前最好确认对要操作的表没有任何进行中的操作、没有未提交事务、也没有显式事务中的报错语句。如果有alter table的维护任务，在无人监管的时候运行，最好通过lock_wait_timeout设置好超时时间，避免长时间的metedata锁等待。

扩展

InnoDB在不同隔离级别下的一致性读及锁的差异

前面讲过，锁和多版本数据是InnoDB实现一致性读和ISO/ANSI SQL92隔离级别的手段，因此，在不同的隔离级别下，InnoDB处理SQL时采用的一致性读策略和需要的锁是不同的。同时，数据恢复和复制机制的特点，也对一些SQL的一致性读策略和锁策略有很大影响。将这些特性归纳成如表20-16所示的内容，以便读者查阅。

从上表可以看出：对于许多SQL，隔离级别越高，InnoDB给记录集加的锁就越严格（尤其是使用范围条件的时候），产生锁冲突的可能性也就越高，从而对并发性事务处理性能的影响也就越大。因此，我们在应用中，应该尽量使用较低的隔离级别，以减少锁争用的机率。实际上，通过优化事务逻辑，大部分应用使用Read Commited隔离级别就足够了。对于一些确实需要更高隔离级别的事务，可以通过在程序中执行SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ或SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE动态改变隔离级别的方式满足需求。

对于myisam的表select 是会锁定表的 ，会导致其他操作挂起，处于等待状态。
对于innodb的表select 是不会锁表的。其实这里使用到了快照。

参考：https://dev.mysql.com/doc/refman/5.7/en/where-optimization.html
https://www.cnblogs.com/sessionbest/articles/8689082.html