1介绍大家好，今天给大家分享的是mysqlDDL执行方法。一般来说，MySQL分为DDL（定义）和DML（运行）。DDL：DataDefinitionLanguage，即数据定义语言，相关的定义操作都是DDL，包括：新建、修改、删除等；相关的命令有：CREATE、ALTER、DROP、TRUNCATE截断表的内容（在开发期间，还是很常见的），COMMENT给数据字典添加注释。DML：DataManipulationLanguage，即数据操作语言，即处理数据库中数据的操作是DML，包括：选择、插入、更新、删除等；相关命令有：SELECT、INSERT、UPDATE、DELETE和LOCKTABLE，以及不常见的CALL——调用一个PL/SQL或Java子程序，EXPLAINPLAN——分析和分析数据访问路径。我们可以认为：CREATE、ALTER、DROP、TRUNCATE，定义相关命令为DDL；SELECT、INSERT、UPDATE、DELETE，操作和处理数据的命令是DML；DDL和DML的区别：DML操作可以手动控制事务的开启、提交和回滚。DDL操作是隐式提交的，不可回滚，所以要小心！对于日常开发，我们熟悉的是一条DML语句。SQL的执行过程很多开发人员都很熟悉，但是DDL是如何执行的，大部分开发人员可能不太关心，认为没必要去了解，留给DBA吧。事实上，并非如此。知道了一些可以尽量避免一些ddl的陷阱，接下来就带大家了解下DDL的执行方式，也算是一个很好的例子。如有错误，请指正。2概述在MySQL的使用过程中，根据业务需要改变表结构是一种常见的运维操作，这些操作称为DDL操作。常见的DDL操作包括向表中添加新列或向列中添加索引。我们常用的一维平台提供了两种执行DDL的方式，一种是MySQL原生在线DDL（onlineDDL），另一种是第三方工具pt-osc。下图是执行方式的性能对比和说明：本文将简单介绍和分析DDL的执行工具OnlineDDL，pt-osc再做介绍。3简介MySQLOnlineDDL功能从5.6版本正式引入到现在的8.0版本，经历了多次调整和完善。其实INPLACEDDL方法早在MySQL5.5就加入了，但是因为实现上的问题，仍然会阻塞INSERT、UPDATE、DELETE操作，这也是早期版本的MySQL长期被吐槽的原因之一时间。在MySQL5.6之前，最昂贵的数据库操作之一是执行DDL语句，尤其是ALTER语句，因为MySQL在修改表时会阻塞整个表的读写操作。例如对A表进行DDL的具体过程如下：根据A表的定义创建新的B表，对A表加写锁，对B表执行DDL指定的操作，复制数据inAtoB，释放A的写锁，deleteA表重命名B表为A在上面2-4的过程中，如果A表的数据量比较大，复制到B表的过程会消耗大量时间并占用额外的存储空间。另外，由于DDL操作占用了A表的写锁，A表上的DDL和DML都会阻塞，无法提供服务。如果遇到一个巨大的表，可能需要几个小时才能完成，这势必会影响应用，所以这些操作需要做好计划，避免在高峰时段执行这些更改。对于那些想要提供全天候（24*7）服务或者维护时间有限的人来说，在大表上执行DDL无疑是一场真正的噩梦。因此，MySQL官方不断加强DDL声明。从MySQL5.6开始，开始支持更多的ALTERTABLE类型操作，避免数据拷贝。同时支持在线DDL过程中的非阻塞DML操作，真正实现了。在线DDL，允许在DDL执行过程中不中断数据库服务的情况下执行DML（插入、更新、删除）。但是，并不是所有的DDL操作都支持在线操作。MySQL5.7在5.6的基础上增加了一些新的特性，比如：增加了对重命名索引的支持，支持数字类型的长度增减，支持VARCHAR类型的在线增加等。但是基本的与5.6相比，实现逻辑和约束没有太大变化。4用法ALTERTABLEtbl_nameADDPRIMARYKEY(column),ALGORITHM=INPLACE,LOCK=NONE;ALTER语句中可以指定参数ALGORITHM和LOCK，分别指定DDL执行的算法模式和DDL过程中DML的锁控制方式。ALGORITHM=INPLACE表示DDL执行过程中不发生表拷贝，过程中允许并发执行DML（INPLACE不需要像COPY那样占用大量的磁盘I/O和CPU，减少了数据库负载。同时减少缓冲池的使用。避免大量删除缓冲池中的原始查询缓存而导致的性能问题）。如果设置了ALGORITHM=COPY，DDL会像MySQL5.6之前的方式一样以表复制的方式进行，过程中所有的DML都会被阻塞。此外，还可以设置ALGORITHEM=DAFAULT，让MySQL以尽可能保证DML并发操作的原则来选择执行方式。LOCK=NONE表示DML操作不加锁，DDL过程中允许所有DML操作。另外还有EXCLUSIVE（持有独占锁，阻塞所有请求，适用于DDL需要尽快完成或者服务库空闲的场景），SHARED（允许SELECT，但是阻塞INSERTUPDATEDELETE，适用于数据仓库等可以允许数据写入输入延迟的场景）和DEFAULT（在保证最大并发的原则下，根据DDL的类型选择LOCK的值）。5两种算法第一种Copy：根据原表的定义创建一个新的临时表；对原表加写锁（禁止DML，允许select）；对第一步创建的临时表执行DDL；将原表copy中的数据保存到临时表；释放原表的写锁；删除原表，将临时表重命名为原表。从上面可以看出，在copy模式下，需要锁住表，禁止DML，所以不Online。比如：删除主键，修改列类型，修改字符集，这些操作都会引起行记录格式的改变（网上无法通过全量+增量实现）。第二种Inplace：在原表上进行修改，不生成临时表，不经过数据拷贝的过??程。根据行记录格式是否可以分为两类：重建：需要重建表（重新组织聚集索引）。如优化表、增加索引、增加/删除列、修改列NULL/NOTNULL属性等；no-rebuild：不需要重建表，只需要修改表的元数据，比如删除索引，修改列名，修改列默认值，修改列自增等。对于rebuild方式，online是通过在DDL时缓存DML，DDL完成后将DML应用到表中来实现。例如执行altertableAengine=InnoDB;重建表的DDL的大致过程如下：创建一个临时文件，扫描表A的主键的所有数据页；将数据页中表A的记录生成一颗B+树，并存储在临时文件中，在生成临时文件的过程中，将对A的所有操作记录在一个日志文件（rowlog）中；临时文件生成后，将日志文件中的操作应用到临时文件中，得到一个与A表逻辑一致的数据文件；用临时文件替换表A的数据文件。说明：在向新表复制数据期间，对原表加了MDL读锁（允许DML，禁止DDL）；MDL写锁在申请增量时加在原表上（禁止DML和DDL）；rebuild根据TableA输出的数据放在tmp_file中，这个临时文件是InnoDB内部创建的，整个DDL过程都是在InnoDB内部完成的。对于server层来说，数据并没有移动到临时表中，是in-place操作，这就是“inplace”这个名字的来源。Inplace方式执行的DDL在出错或被kill时需要一定的回滚期。执行时间越长，回滚时间越长。copy方式执行的DDL，过程中需要记录undo和redo日志，同时消耗bufferpool资源，效率低，优点是可以快速停止。但是并不是所有的DDL操作都可以在INPLACE模式下进行，具体支持可以查看（OnlineDDLOperations）。以下是常见的DDL操作：官网支持列表：6执行流程OnlineDDL主要包括3个阶段，prepare阶段，ddl执行阶段，commit阶段。下面将主要介绍ddl执行过程中三个阶段的流程。1）Prepare阶段：在初始化阶段，会根据存储引擎、用户指定的操作、用户指定的ALGORITHM和LOCK来计算DDL过程中允许的并发数。在这个过程中，会得到一个共享元数据锁来保护表的结构定义。创建新的临时frm文件（与InnoDB无关）。持有EXCLUSIVE-MDL锁，禁止读写。根据altertype确定执行方式（copy、online-rebuild、online-norebuild）。如果是AddIndex，选择online-norebuild或者INPLACE模式。更新数据字典的内存对象。分配row_log对象以记录增量（仅重建类型需要）。生成新的临时ibd文件（仅重建类型需要）。在数据字典上提交事务并释放锁。注意：Rowlog是排他结构，不是redolog。它以块的形式管理DML记录的存储。块的大小由参数innodb_sort_buffer_size控制。默认大小为1M。初始化阶段会申请两个block。2）DDL执行阶段：执行时的共享元数据锁保证不会同时执行其他DDL，但DML可以正常执行。降级EXCLUSIVE-MDL锁，允许读写（copy不可写）。扫描old_table的聚簇索引的每条记录rec。遍历新表的聚簇索引和二级索引，一一处理。根据rec构造对应的索引项，将构造好的索引项插入到sort_buffer块中进行排序。将sort_buffer块更新为新索引。记录ddl执行过程中产生的增量（仅rebuild类型需要）并replayrow_log中的操作到新索引（no-rebuild数据在原表上更新）。追加到row_log最后一个Block的dml操作是在row_log重放时产生的。3）提交阶段：将共享元数据锁升级为独占元数据锁，禁止DML，删除旧表定义，提交新表定义。当前块为row_log中的最后一个时，禁止读写，锁升级为EXCLUSIVE-MDL。重做row_log中增量的最后一部分。更新innodb的数据字典表。提交事务（刷新事务的重做日志）。修改统计。重命名临时idb文件、frm文件。更改完成。OnlineDDL过程中占用独占MDL的步骤执行速度非常快，所以DML语句几乎不会被阻塞。但是，其他事务可以在DDL执行之前或执行时获取MDL。由于需要使用exclusiveMDL，以上两个涉及MDL的地方必须在其他占用元数据锁的事务提交或回滚后执行。7踩坑前面说到OnlineDDL在执行过程中需要获取MDL（元数据锁）。MDL（metadatalock）是MySQL5.5引入的表级锁。访问表时会自动添加，保证读写的正确性。对表进行DML操作时，加MDL读锁；在做DDL操作时，添加MDL写锁。为了保证在大表执行DDL的过程中可以并发执行DML，前面使用了ALGORITHM=INPLACE的OnlineDDL，但是这里还是存在死锁的风险。问题出在OnlineDDL过程中需要独享MDL。例如，会话1在事务中执行SELECT操作，此时共享MDL被提取。由于是在事务中执行，共享MDL只有在事务结束后才会被释放。#Session1>STARTTRANSACTION;>SELECT*FROMtbl_name;#正常执行此时Session2如果要进行DML操作，只需要获取共享MDL，仍然可以正常执行。#Session2>SELECT*FROMtbl_name;#正常执行，但是如果Session3要执行DDL操作，就会阻塞，因为此时Session1已经占用了共享MDL，执行DDL需要获取独占MDL首先，所以它不能正常执行。#Session3>ALTERTABLEtbl_nameADDCOLUMNnINT;#Blocking通过showprocesslist可以看到ALTER操作正在等待MDL。由于独占MDL的获取优先于共享MDL，所有后续获取共享MDL的尝试也将被阻止+----+----------------+----------------+------+--------+------+-----------------------------+----------------+|Id|User|Host|db|命令|时间|状态|信息|│----+----------------+----------------+------+--------+------+--------------------------------+--------------+|11|根|172.17.0.1:53048|演示|查询|3|等待表元数据锁|altertable...|+----+----------------+----------------+------+------------+------+------------------------------+----------------+此时，DML和DDL都会被阻塞，直到Session1提交或回滚，Session1占用共享后MDL被释放，后续操作可以继续。出现上述问题主要有两个原因：Session1中的事务没有及时提交，导致Session3的DDLSession3OnlineDDL被阻塞，后续的DML和DDL也被阻塞。对于问题1，一些ORM框架默认将用户语句封装成事务执行，如果客户端程序中断退出，事务没有及时提交或回滚，就会出现Session1中的情况。那么这个时候可以在infomation_schema.innodb_trx中找到未完成事务对应的线程，强制退出。>SELECT*FROMinformation_schema.innodb_trx\G******************************1.行*****************************trx_id:421564480355704trx_state:RUNNINGtrx_started:2022-05-01014:49:41trx_requested_lock_id:NULLtrx_wait_started:NULLtrx_weight:0trx_mysql_thread_id:9trx_query:NULLtrx_operation_state:NULLtrx_tables_in_use:0trx_tables_locked:0trx_lock_structs:0trx_lock_memory_bytes:1136trx_rows_locked:0trx_rows_modified:0trx_concurrency_tickets:0trx_isolation_level:REPEATABLEREADtrx_unique_checks:1trx_foreign_key_checks:1trx_last_foreign_key_error:NULLtrx_adaptive_hash_latched:0trx_adaptive_hash_timeout:0trx_is_read_only:0trx_autocommit_non_locking:0trx_schedule_weight:NULL1rowinset(0.0025sec)可以看到Session1正在执行的事务对应的trx_mysql_thread_id为9，然后执行KILL9中断Session1中的事务。对于问题2，在大量查询的情况下，被阻塞的Session数量会迅速增加。这种情况下，可以先中断DDL操作，避免对服务造成过大的影响。也可以尝试在从库上修改表结构后进行主从切换，或者使用pt-osc等第三方工具。8限制仅适用于InnoDB（语法上它可以与MyISAM等其他存储引擎一起使用，但MyISAM只允许algorithm=copy，与传统方法相同）；无论使用何种锁（NONE、sharedorexclusive），在表头和尾部都需要很短的时间来锁定表（exclusivelock）；添加/删除外键时，应禁用foreign_key_checks以避免表复制；还有一些alter操作需要复制或者锁表（老方法），哪些表的修改需要复制表或者锁表，请查看官网；如果表上有ON...CASCADE或ON...SETNULL约束，则altertable语句中不允许使用LOCK=NONE；在线DDL会复制到从库（和主库一样，如果LOCK=NONE，从库不会加锁），但是复制本身会阻塞，因为alter是在从库单线程执行的，会造成主从延迟问题。官方参考：MySQL::MySQL5.7参考手册::14.13.6OnlineDDLLimitations9小结本次与大家分享SQLDDL、DML的理解以及它们的区别，同时介绍OnlineDDL的执行方式。目前可用的DDL操作工具有github的pt-osc、gh-ost，以及MySQL提供的在线表结构修改命令OnlineDDL。pt-osc和gh-ost都是通过复制表来实现的，即创建一个空的新表，通过select+insert将旧表中的记录一条一条读取插入到新表中。区别在于DDL处理表DML操作时的业务。在MySQL8.0中，官方对DDL的实现也进行了重新设计。最大的改进之一是DDL操作支持原子特性。另外在OnlineDDL的ALGORITHM参数中增加了一个新选项：INSTANT，只需要修改数据字典中的元数据，不需要复制数据或重建表，也不需要加排他MDL锁，和原表数据不会受到影响。整个DDL过程几乎瞬间完成，DML不会阻塞。不过目前8.0INSTANT的使用范围比较小，后面我们会详细介绍8.0INSTANT。此外，一维平台还提供了pt-osc的执行方式。下次我会把pt-osc的执行方法分享给大家，敬请期待！作者：刘登中
