mysql中MDL（元数据锁）的长事务读写阻塞如何解决

本文主要是介绍mysql中MDL（元数据锁）的长事务读写阻塞如何解决，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

MDL，即元数据锁是什么，我们已经介绍过了
那其存在的长事务读写阻塞问题，一般是怎么解决的呢，主要有两种解决方法。

online ddl

MySQL5.6开始，推出一项新功能Online DDL，在ALTER或者CREATE INDEX等语句后添加了两个参数：

ALTER TABLE user ADD INDEX idx_test_id (test_id), ALGORITHM=INPLACE, LOCK=NONE

ALGORITHM:

INPLACE: 表的更改将在原表进行，而不用重建整个表格（在大多数情况下，不需要将数据复制到临时表）
COPY: 将数据复制到临时表中，重建表格并重建二级索引（相当于传统方法）

LOCK:

NONE: Read and write operations are allowed during the altering process.
SHARED: Only read operations are allowed during the altering operations (DML is not allowed).
EXCLUSIVE: The entire table will be locked for both reading and writing (neither select nor DML are allowed).

ONLINE DDL的局限性

仅适用于InnoDB（语法上它可以与其他存储引擎一起使用，如MyISAM，但MyISAM只允许algorithm =
copy，与传统方法相同）；
无论使用何种锁（NONE，共享或排它），在开始和结束时都需要一个短暂的时间来锁表（排它锁）；参考这里
在添加/删除外键时，应该禁用 foreign_key_checks 以避免表复制；
仍然有一些 alter 操作需要 copy 或lock 表（老方法），有关哪些表更改需要表复制或表锁定，请查看手册；
如果在表上有 ON … CASCADE 或 ON …SET NULL 约束，则在 alter table 语句中不允许LOCK = NONE；
Online DDL会被复制到从库（同主库一样，如果 LOCK = NONE，从库也不会加锁），但复制本身将被阻止，因为 alter在从库以单线程执行，这将导致主从延迟问题。

pt-online-schema-change

pt-osc 用于 alter table 时不锁表，简单地说，这个工具创建一个与原始表一样的新的空表，并根据需要更改表结构，然后将原始表中的数据以小块形式复制到新表中，然后删除原始表，然后将新表重命名为原始名称。在复制过程中，对原始表的所有新的更改（insert，delete，update）都将应用于新表，因为在原始表上创建了一个触发器，以确保所有新的更改都将应用于新表。
pt-osc工作过程

创建一个和要执行 alter 操作的表一样的新的空表结构(是alter之前的结构)
在新表执行alter table 语句（速度应该很快）
在原表中创建触发器3个触发器分别对应insert,update,delete操作
以一定块大小从原表拷贝数据到临时表，拷贝过程中通过原表上的触发器在原表进行的写操作都会更新到新建的临时表（会限制每次拷贝数据的行数以保证拷贝不会过多消耗服务器资源，采用LOCK IN SHARE MODE来获取要拷贝数据段的最新数据并对数据加共享锁阻止其他会话修改数据，不过每次加S锁的行数不多，很快就会被释放）
Rename原表到old表中，在把临时表Rename为原表（整个过程只在rename表的时间会锁一下表，其他时候不锁表）
如果有参考该表的外键，根据alter-foreign-keys-method参数的值，检测外键相关的表，做相应设置的处理
默认最后将旧原表删除

PT-ONLINE-SCHEMA-CHANGE 的局限性

在使用此工具之前，应为表定义PRIMARY KEY或唯一索引，因为它是DELETE触发器所必需的；
如果表已经定义了触发器，则不支持pt-osc；（显然不是不能有任何触发器，只是不能有针对insert、update、delete的触发器存在，因为一个表上不能有两个相同类型的触发器）
如果表具有外键约束，需要使用选项 --alter-foreign-keys-method ；
还是因为外键，对象名称可能会改变（indexes names 等）；
在Galera集群环境中，不支持更改MyISAM表，系统变量wsrep_OSU_method 必须设置为 TOI（total order isolation）。