mysql锁表分析

因为近期MYSQL在改表移库时，发生了锁表现象.现在对该现象进行分析，并提出一些建议。

一、改表

Mysql 5.6 虽然引入了Online DDL，但是并不是修改表结构的时候，一定不会导致锁表，在一些场景下还是会锁表的，比如
1）某个慢SQL或者比较大的结果集的SQL在运行，执行ALTER TABLE时将会导致锁表发生；
2）存在一个事务在操作表的时候，执行ALTER TABLE也会导致修改等待；

 

按如下步骤执行测试:

1)开启A事务，更新数据，且不提交

执行结果如下:

2)开启B事务，更新数据，且不提交

这两条语句互相不影响，将这两个事务回滚掉，继续下一轮测试。

3)开启C事务，更新数据，且不提交

执行结果如下:

1. 修改表结构

1. 开启D事务，更新数据

执行时会发现表被锁住了.

1. 将C事务提交或者回滚，这里我选择了回滚

7)C事务释放锁后,D事务更改成功，但是耗时很长。

结论与建议:

在执行alter table 操作时，事务会由并行变为串行，同时只能有一个事务更新表数据。尽量选择流量小的时候执行alter 语句。并且要注意避开跑批等耗时较长的更新操作。在执行时最好先看一下有没有未提交的事务。

 

• 更新单条语句
  MYSQL的锁是加在索引上的，如果update 语句中的where 未使用到索引，即会锁表。

按如下步骤执行测试:

Dsfp_order_jnl表 busi_serial字段不存在索引。

1)开启A事务，更新数据，且不提交

执行结果如下

 

2)开启B事务，更新数据，且不提交

B事务更新时锁超时。

结论与建议:

update语句必须使用索引,哪怕是更新很快的操作，不然在并发高时一样会导致问题。

 

 

• 数据集合使用索引
  mysql对于数据集合使用索引时，存在内部优化策略，当你选择的数据范围较大时，会放弃使用索引，而扫描全表，如果是select for update,update,delete等操作就会锁定全表，影响业务。

范围数据有没有使用索引我们可以使用explain查询。

1. 较小数据范围

Possible_keys表示查询可能使用哪些索引,key表示mysql决定采用哪个索引对查询进行优化。Type range表示这是一个范围扫描。

1. 扩大数据范围

此时虽然可以使用索引，但是mysql最终选择了全表扫描。 type 为all,key为null.

对于查询我们可以强制mysql放弃内部优化，使用我们指定的索引。

1. 使用强制索引

Update 语句与select 语句可以使用相同的处理方式。

1. 更新不使用强制索引

 

1. 更细使用强制索引

Delete 语句不支持force index语法，我们可以使用limit 语句替代，limit语句会优先使用索引查找数据

1. Delete不使用limit

1. Delete使用limit

结论与建议:

在对批量数据进行操作时，先到生产环境或者1b1环境，对要执行的SQL进行一次explain看是否使用了索引。 select for update,update 可以使用force_index。Delete 可以使用limit。

转载于:https://my.oschina.net/u/3944601/blog/3050600