MySQL:: MySQL 5.7参考手册::14.13.2在线DDL性能和并发

10bet网址

版本5.7

8.0当前
5.6

8.0日本

MySQL 5.7参考手册/.．./ 在线DDL性能和并发性

14.13.2在线DDL性能和并发

在线DDL改进了MySQL操作的几个方面:

访问表的应用程序响应更快，因为在DDL操作进行时，表上的查询和DML操作可以继续进行。减少对MySQL服务器资源的锁定和等待会带来更大的可伸缩性，即使对于DDL操作中没有涉及的操作也是如此。
就地操作避免了与表复制方法相关的磁盘I/O和CPU周期，从而最大限度地减少了数据库的总体负载。最小化负载有助于在DDL操作期间保持良好的性能和高吞吐量。
就地操作比表复制操作更少地将数据读入缓冲池，从而减少了从内存中清除频繁访问的数据。清洗频繁访问的数据可能会在DDL操作之后导致暂时的性能下降。

LOCK条款

默认情况下，MySQL在DDL操作期间使用尽可能少的锁。的锁子句可以强制更严格的锁定(如果需要)。如果锁子句指定的锁定限制级别小于特定DDL操作所允许的锁级别，则语句失败并报错。锁子句按照限制从少到多的顺序描述如下:

锁=没有：
允许并发查询和DML。
例如，对于涉及客户注册或购买的表使用此子句，以避免在长时间的DDL操作期间使表不可用。
锁=共享：
允许并发查询但阻塞DML。
例如，在数据仓库表上使用此子句，您可以将数据加载操作延迟到DDL操作完成为止，但查询不能长时间延迟。
锁=违约：
允许尽可能多的并发性(并发查询、DML或两者兼有)。省略锁子句与指定相同锁=违约．
当您知道DDL语句的默认锁定级别不会导致表的可用性问题时，请使用此子句。
锁=独家：
阻塞并发查询和DML。
如果主要关心的是在尽可能短的时间内完成DDL操作，并且不需要并发查询和DML访问，则使用此子句。如果服务器应该是空闲的，也可以使用这个子句，以避免意外的表访问。

在线DDL和元数据锁

在线DDL操作可以被看作有三个阶段:

阶段1:初始化
在初始化阶段，服务器将考虑存储引擎功能、语句中指定的操作和用户指定的操作，确定操作期间允许的并发量算法而且锁选项。在此阶段，使用一个共享的可升级元数据锁来保护当前表定义。
阶段2:执行
在此阶段，准备并执行语句。元数据锁是否升级为独占依赖于初始化阶段评估的因素。如果需要排他性元数据锁，则只在语句准备期间使用它。
阶段3:提交表定义
在提交表定义阶段，元数据锁升级为排他，以清除旧表定义并提交新表定义。一旦授予，排他性元数据锁定的持续时间很短。

由于上面概述的独占元数据锁需求，在线DDL操作可能必须等待在表上持有元数据锁的并发事务才能提交或回滚。DDL操作之前或期间启动的事务可以持有正在更改的表上的元数据锁。在长时间运行或非活动事务的情况下，在线DDL操作可能会超时等待排他元数据锁。此外，在线DDL操作请求的挂起的排他元数据锁会阻塞表上的后续事务。

下面的示例演示了一个在线DDL操作等待排他元数据锁，以及挂起的元数据锁如何阻塞表上的后续事务。

阶段1:

CREATE TABLE t1 (c1 INT) =InnoDB;mysql> START TRANSACTION;mysql> SELECT * FROM t1;

会话1选择语句接受表t1上的共享元数据锁。

阶段2:

mysql> ALTER TABLE t1 ADD COLUMN x INT, ALGORITHM=INPLACE, LOCK=NONE

会话2中的在线DDL操作需要表t1上的排他元数据锁来提交表定义更改，必须等待会话1事务提交或回滚。

阶段3:

mysql> SELECT * FROM t1;

的选择语句在会话3中被阻塞，等待线程请求的独占元数据锁ALTER TABLE会话2中的操作将被授予。

你可以使用显示完整流程列表确定事务是否正在等待元数据锁。

mysql>显示完整的进程列表\G…*************************** 2。行*************************** Id: 5用户:root主机:localhost db: test命令:查询时间:44状态:Waiting for table metadata lock信息:ALTER table t1 ADD COLUMN x INT, ALGORITHM=INPLACE, lock =NONE…*************************** 4所示。Id: 7用户:root主机:localhost db: test命令:查询时间:5状态:Waiting for table metadata lock信息:SELECT * FROM t1 set中有4行(0.00 sec)

元数据锁信息也通过性能模式公开metadata_locks表，它提供了关于会话之间元数据锁依赖关系、会话正在等待的元数据锁以及当前持有元数据锁的会话的信息。有关更多信息，请参见第25.12.12.1节“metadata_locks表”．

在线DDL性能

DDL操作的性能在很大程度上取决于该操作是否执行到位以及是否重新构建表。

要评估DDL操作的相对性能，可以使用算法=原地使用结果算法=复制．或者，您可以将结果与old_alter_table禁用和启用。

对于修改表数据的DDL操作，可以确定DDL操作是就地执行更改还是执行表复制”行受影响”命令执行完成后显示的值。例如:

更改列的默认值(快速，不影响表数据):
```
查询OK, 0行受影响(0.07秒)
```
添加索引(需要时间，但是0行受影响显示表没有被复制):
```
查询OK, 0行受影响(21.42秒)
```
改变一个列的数据类型(需要大量的时间并且需要重新构建表中的所有行):
```
查询OK, 1671168行受影响(1分35.54秒)
```

在对大表执行DDL操作之前，需要检查操作的速度是快还是慢。

克隆表结构。
用少量数据填充克隆的表。
在克隆的表上运行DDL操作。
检查是否”行受影响”Value为零或不为零。非零值表示操作复制表数据，这可能需要特殊的规划。例如，您可以在计划的停机时间内执行DDL操作，或者在每个副本服务器上一次执行一个。

请注意

为了更好地理解与DDL操作相关的MySQL处理，请检查Performance Schema和INFORMATION_SCHEMA有关的表格InnoDB在DDL操作前后查看物理读、写、内存分配等的数量。

性能模式阶段事件可用于监视ALTER TABLE的进步。看到章节14.17.1“使用性能模式监视InnoDB表的ALTER TABLE进度”．

因为有一些处理工作涉及到记录并发DML操作所做的更改，然后在最后应用这些更改，因此在线DDL操作总体上比阻止来自其他会话的表访问的表复制机制花费的时间要长。原始性能的降低与使用该表的应用程序更好的响应能力相平衡。在评估改变表结构的技术时，考虑终端用户对性能的感知，基于网页的加载时间等因素。

上一页首页向上下一个