漫漫长路,其修远兮!
分类: Mysql/postgreSQL
2013-03-27 11:40:38
原文地址:
5.5 InnoDB存储引擎表在线DDL
5.5.1.在线DDL概述
以前,InnoDB存储引擎表的许多DDL操作代价是非常昂贵的。许多ALTER TABLE操作的原理是通过创建新的空表,定义被要求的表选项和索引,然后逐行拷贝已存在记录到新表,在插入行时更新索引。在旧表所有行被拷贝完,旧表被删除和那新表被重命名为旧的表名。 MySQL5.5,和MySQL5.1 有了InnoDB Plugin,优化了CREATE INDEX和DROP INDEX 避免表的拷贝行为。这个特性被称为Fast index Creation。MySQL 5.6 加强ALTER TABLE操作许多方式来避免拷贝表。另外加强的允许SELECT查询和INSERT, UPDATE,和DELETE(DML)语句被处理当该表正被修改时。这些功能的组合现在被称为online DDL。
这种新的机制还意味着你可以普通加快创建和加载表和在创建表时,没有任何二级索引,然后在数据被加载后再添加二级索引的整个过程的速度。
虽然在CREATE INDEX 或 DROP INDEX命令没有语法的变化要求,但一些因素影响性能,空间的使用,和操作的语义(见5.5.9节,“在线DDL极限性”)。
MySQL 5.6在线DDL的增强改善许多DDL操作,以前需要复制表,阻塞在表上DML操作或两者兼而有之。表5.9,“DDL操作的在线状态总结”显示了ALTER Table语句的变化和展示了在线DDL特性如何适用于每个操作。
表5.9。对DDL在线操作的状态总结
Operation |
In-Place? |
Copies Table? |
Allows Concurrent DML? |
Allows Concurrent Query? |
Notes |
CREATE INDEX, ADD INDEX |
Yes* |
No* |
Yes |
Yes |
对全文索引的一些限制; 参照下一行。目前,该操作不是in-place(它需要复制表)如果索引将被创建和在ALTER TABLE语句之前删除语句相同 |
ADD FULLTEXT INDEX |
Yes |
No* |
No |
Yes |
对表创建第一个全文索引包含表复制,除非用户提供了FTS_DOC_ID列。随后该表的全文索引可以被就地(in-place)创建 |
DROP INDEX |
Yes |
No |
Yes |
Yes |
|
对一列设置默认值 |
Yes |
No |
Yes |
Yes |
仅对该表.frm修改不是数据文件。 |
对一列修改auto-increment 的值 |
Yes |
No |
Yes |
Yes |
修改一个值存储在内存而不是数据文件。 |
添加 foreign key constraint |
Yes* |
No* |
Yes |
Yes |
避免拷贝表,在约束创建时禁用
|
删除 foreign key constraint |
Yes |
No |
Yes |
Yes |
foreign_key_checks 选项可用,可不用 |
Rename a column |
Yes* |
No* |
Yes* |
Yes |
为允许DML并发, 保持相同数据类型,仅改变列名 |
Add a column |
Yes |
Yes |
Yes* |
Yes |
当添加列是
auto-increment ,不允许DML并发。尽管 ALGORITHM=INPLACE 被允许,但数据大幅重组,所以它仍然是一项昂贵的操作。 |
Drop a column |
Yes |
Yes |
Yes |
Yes |
尽管ALGORITHM=
INPLACE 被允许, 但数据大幅重组,所以它仍然是一项昂贵的操作。 |
Reorder columns |
Yes |
Yes |
Yes |
Yes |
尽管ALGORITHM
=INPLACE 被允许,但数据大幅重组,所以它仍然是一项昂贵的操作。 |
Change ROW_FORMAT property |
Yes |
Yes |
Yes |
Yes |
尽管ALGORITHM
=INPLACE被允许, 但数据大幅重组,所以它仍然是一项昂贵的操作。 |
Change KEY_BLOCK_SIZE property |
Yes |
Yes |
Yes |
Yes |
尽管ALGORITHM
=INPLACE被允许,但数据大幅重组,所以它仍然是一项昂贵的操作。 |
Make column NULL |
Yes |
Yes |
Yes |
Yes |
尽管ALGORITHM
=INPLACE被允许,但数据大幅重组,所以它仍然是一项昂贵的操作。 |
Make column NOT NULL |
Yes* |
Yes |
Yes |
Yes |
当 SQL_MODE 包含 strict_all_tables 或strict_all_tables,如果该列包含任何NULL,该操作失败。尽管ALGORITHM
=INPLACE被允许,但数据大幅重组,所以它仍然是一项昂贵的操作。 |
Change data type of column |
No |
Yes |
No |
Yes |
|
Add primary key |
Yes* |
Yes |
Yes |
Yes |
尽管 ALGORITHM
=INPLACE被允许,但数据大幅重组,所以它仍然是一项昂贵的操作。 ALGORITHM=INPLACE在下列条件是不允许的,如果有列必须转化NOT NULL。参照Example 5.9, “Creating and Dropping the Primary Key”. |
Drop primary key and add another |
Yes |
Yes |
Yes |
Yes |
ALGORITHM=INPLACE 仅当你在同一个ALTER TABLE语句添加新主键是被允许;数据大幅重组,所以它仍然是一项昂贵的操作。 |
Drop primary key |
No |
Yes |
No |
Yes |
限制的应用,当删除一个主键在同一个ALTER TABLE 语句没有添加新主键 |
Convert character set |
No |
Yes |
No |
Yes |
如果新的字符集编码不用,重建表。 |
Specify character set |
No |
Yes |
No |
Yes |
如果新的字符集编码不用,重建表。 |
Rebuild with FORCE option |
No |
Yes |
No |
Yes |
等效于ALGORITHM
=COPY 或设置 |
以下部分显示了基本的语法和用法说明相关的在线DDL,对每个主要的操作,可以进行DML并发、就地(in-place)、或两者:
二级索引
在InnoDB存储引擎表创建和删除二级索引跳过表复制行为和在MySQL 5.5和MySQL 5.1的InnoDB Plugin 一样。
在MySQL 5.6和更高版本, 当索引在创建或删除时表仍然可以读和写操作。CREATE INDEX 或 DROP INDEX语句只在所有访问表的事务完成才完成,因此, 索引的初始状态反映表最近的内容。此前版本,当索引是在创建或删除,更新表通常导致死锁,撤销该表的INSERT, UPDATE, DELETE语句。
列属性
列的默认值存储在表的 .frm 文件, 不是InnoDB data 目录。
特别是在一个分布式系统使用复制和分片,你有时会为一个表重置自增计数器为一个特定的值。插入表下一行为其自动递增列使用指定的值。你可能在数据仓库环境中也使用这种技术,你定期清空所有表和重新加载它们,您可以重新启动自动递增顺序从1开始。
当你保持相同的数据类型和[NOT]NULL属性,只改变列名,这个操作总可以在线执行。
作为增强的一部分,现在您可以重命名一个列,该列为外键约束部分的,以前这是不允许的。外键的定义自动更新使用新的列名称。列重命名参与到外键只适用ALTER TABLE就地(in-place)的模式。如果你使用ALGORITHM=COPY条款,或其他条件使命令在幕后使用ALGORITHM=COPY,ALTER TABLE语句将失败。
外键
ALTER TABLE tbl1 ADD CONSTRAINT fk_name FOREIGN KEY index (col1) REFERENCES tbl2(col2) referential_actions;
ALTER TABLE tbl DROP FOREIGN KEY fk_name;
删除外键可以在在线执行,不管foreign_key_checks是否启用,但在线创建外键需要foreign_key_checks 禁用。
如果你不知道在一个特定的表的外键约束名字,发出以下语句和为每个外键约束条款找到约束名称:
show create table table\G
或者,查询information_schema.table_constraints 表和使用constraint_name和 constraint_type 列来查出外键名字.
由于这种增强,你现在还可以在一个语句中删除一个外键及其相关索引,此前要求严格独立语句顺序:
ALTER TABLE table DROP FOREIGN KEY constraint, DROP INDEX index;
如果 foreign keys 已存在将被修改 (也就是说, child table 包含任何FOREIGN KEY … REFERENCE 子句), 额外的限制适用于在线DDL操作, 甚至那些不直接涉及外键列:
同样的方式, 如果表在外键关系中是 父表, 尽管它不包含任何外键子句, 它会等待 ALTER TABLE 完成, 如果 INSERT, UPDATE, 或 DELETE 语句引起在子表ON UPDATE 或 ON DELETE 操作.
注意ALGORITHM=COPY
任何 ALTER TABLE 操作在ALGORITHM=COPY 子句下运行防止 DML并发操作。并发查询还是运行的,即表拷贝操作至少在LOCK=SHARED的并发限制条件总被包含。你可以进一步限制并发等操作 通过指定LOCK=EXCLUSIVE (阻止 DML和 查询).
DML并发但表拷贝也是必须的
一些其它的 ALTER TABLE 操作允许 DML并发, 和比 MySQL 5.5 和更早版本更快:表复制操作优化了,尽管表复制仍然是必需的:
注意
由于你的数据库模式有新列、数据类型、约束、索引等演变,保持你的CREATE TABLE语句以获得最新的表定义。即使在线DDL性能改进了,它在开始创建稳定的数据库结构更有效率,而不是创建模式的一部分,然后发出ALTER TABLE语句。
这个指导主要的例外是有大量的数据表二级索引。它通常最有效的是创建指定所有细节除二级索引外的表,加载数据,然后创建二级索引。对外键你可以使用相同的技术 (加载数据首先,然后设置外键)如果你知道初始数据是干净的和在加载过程中不需要数据一致性的检查。
无论CREATE TABLE, CREATE INDEX, ALTER TABLE的什么顺序和类似的语句将一个表放在一起,您可以捕获SQL需要重构当前表的形式通过发出SHOW CREATE TABLE table\G语句显示创建表表 (大写\ G需要整洁的格式)。该输出显示条款如数值精度, NOT NULL,和CHARACTER SET,有时在幕后被添加,而且你可能遗漏,当在一个新系统克隆表或设置外键列具有相同类型。
5.5.2.Online DDL性能和并发注意事项
Online DDL提高了MySQL的几个方面的操作,如性能、并发性、可用性和可伸缩性:
对Online DDL锁选项
当InnoDB表被DDL操作修改,表可能会或可能不会被锁定,这取决于操作的内部原理,和ALTER table语句的锁子句。默认情况下,MySQL使用尽可能少的锁在DDL操作期间;您指定子句要么使锁比通常有更多的限制 (因此限制DML并发、或DML和查询),要么确保锁的一些预期程度允许对该操作。如果锁子句指定了锁的一个级别,不能用于特定类型的DDL操作,如在创建或删除主键时指定LOCK=SHARED 或LOCK=NONE,该条款的工作就像一个声明,导致语句因错误而失败。下面的列表显示了锁子句不同的可能性,从最宽容到最严格:
对InnoDB表ONLINE DDL语句总是等待当前访问表执行的事务提交或回滚,因为当DDL语句正在准备,它在短暂时间内需要独占访问表。同样, 在DDL完成之前,它需要在短暂的时间内独占访问表。因此,一个ONLINE DDL语句在这过程中等待任何已发起,查询或修改表的事务提交或回滚。
因为有一些处理工作涉及到由当前DML对记录所做的修改,然后在最后应用这些改变,一个ONLINE DDL操作可能需要更长的时间比旧式阻止来自其他对表的访问的机制。减少原料(raw)性能来平衡更好的响应应用程序使用的表。当对改变表结构来评估理想的技术,考虑终端用户感知的性能,基于对web页面加载时间等因素。
一个新创建的InnoDB二级索引仅包含在CREATE INDEX 或 ALTER TABLE完成时表已提交的数据。它不包含任何未提交的值,旧版本的值,或标记为删除但没有移除从旧的索引的值。
就地(in-place)与表复制的DDL操作性能对比
ONLINE DDL操作的原始性能很大程度上取决于是否就地进行操作还是需要复制和重建整个表。见Table 5.9, “Summary of Online Status for DDL Operations”看到什么样的操作可以就地进行的,和什么要求避免表复制操作
就地DDL操作性能加速适用于二级索引操作,并不适用于主键。InnoDB表的行存储在一个基于主键的聚集索引组织,形成一些数据库系统称为一个“索引组织表”。因为表结构是如此紧密与主键联系在一起的,重新定义主键仍然需要复制数据。
当一个操作主键使用ALGORITHM=INPLACE,即使数据仍然是复制,它比使用ALGORITHM=COPY 更有效,是因为:
判断ONLINE DDL操作的相对性能,您可以在一张大的Inno DB表使用MySQL当前和以前版本运行这些操作。您还可以在最新的MySQL版本运行所有的性能测试,模拟之前DDL行为的“之前”结果,通过设置old-alter-table系统变量。在会话中发起语句 set old_alter_ table = 1,测量性能记录DDL“之前”的数据。然后set old_alter_ table = 0重新用新的、更快的行为,并运行DDL操作再次记录“之后”的数据。
对于DDL操作一个基本的想法:它的变化是就地执行还是执行表拷贝, 在命令结束之后看看显示“rows affected “的值。例如,这里您可能会看到在做不同类型的DDL操作:
Query OK, 0 rows affected (0.07 sec)
Query OK, 0 rows affected (21.42 sec)
Query OK, 1671168 rows affected (1 min 35.54 sec)
例如, 在一个大表运行一个DDL操作之前,你可能会检查操作是将快还是慢,如下所示:
进一步了解减少MySQL处理,在InnoDB相关的表DDL操作之前和之后检查performance_schema和INFORMATION_SCHEMA表,查看物理读、写、内存分配的量,等等。
5.5.3.ONLINE DDL 的SQL语法
通常当对InnoDB表使用ALTER TABLE语句,你没有必要对online DDL做任何特殊的事,见Table 5.9, “Summary of Online Status for DDL Operations”对各种DDL操作可以就地(in-place)执行,允许并发DML,或两者都可以。一些变化需要配置设置的额外结合或是alter table语句。
你可以通过alter table语句中LOCK 和ALGORITHM子句来控制特定online DDL的各个方面。这些子句位于语句的结束的位置,它们之间用逗号隔开。LOCK子句对表的并发访问有不同粒度控制。ALGORITHM子句主要对性能比较和作为以前表拷贝一个备用,以防你遇到存在DDL代码中的问题。例如:
LOCK子句更详细信息请参照Section 5.5.2, “Performance and Concurrency Considerations for Online DDL”。Online DDL 的完整的事例,见Section 5.5.5, “Examples of Online DDL”。
5.5.4.结合或分开的DDL语句
在介绍Online DDL之前,最常见操作就是把多个DDL操作结合成一条ALTER TABLE语句。因为每次alter table语句涉及到复制和重建表,一次使表做几次修改,它更有效率。既然做一次重建操作就完成表这些改变。缺点是,SQL代码涉及DDL操作是难以维护和重用在不同的脚本。如果特定的变化是不同的每一次,你可能为每一个稍微不同的场景必须构造一个新的复杂的ALTER TABLE。
对DDL可以就地操作,见Table 5.9, “Summary of Online Status for DDL Operations”,现在你可以把它们分成单独ALTER TABLE语句,使脚本更容易和维护,又不牺牲效率。例如:你可能采用一个复杂语句如:
alter table t1 add index i1(c1), add unique index i2(c2), change c4_old_name c4_new_name integer unsigned;
并将其分解为更简单的部分,可以单独测试并执行,例如:
alter table t1 add index i1(c1);
alter table t1 add unique index i2(c2);
alter table t1 change c4_old_name c4_new_name integer unsigned not null;
你也许对下列情况还会使用多个部分的alter_table 语句:
5.5.6.Online DDL 实现细节
每个对InnoDB表的ALTER TABLE 操作由几个方面组成:
本节解释这些因素将如何影响不同类型的ALTER TABLE对InnoDB表操作。
Online DDL的错误条件
下面是Online DDL操作出错主要原因:
虽然配置选项InnoDB_file_per_table有一个戏剧性的影响表现为InnoDB表, 是否启用或禁用这个选项,表的物理位置是否在它自己的.ibd文件还是系统内的表空间,所有在线DDL操作同样可以工作。
InnoDB有两种类型的索引:聚集索引代表表中的所有数据,和可选的二级索引来加快查询。既然聚集索引包含数据值在b -树节点,添加或删除一个聚集索引确实涉及到复制数据,并创建一个表的新副本。二级索引,然而,只包含索引键和主键的值。这种类型的索引可以创建或删除而不复制聚集索引的数据。因为每个二级索引包含主键值的副本(用于需要时访问聚集索引),当你改变主键的定义,所有二级索引重新进行。
删除一个二级索引很简单的。只有InnoDB内部系统表和MySQL数据字典表更新以反映该索引已不存在的事实。InnoDB返回用于存储索引的表空间,以便新索引或额外的表行可以使用该空间。
添加一个二级索引到现有表, InnoDB对表进行扫描,使用二次索引键列在内存缓冲区和临时文件进行行排序。B-treean按键-值顺序进行构件,这比用随机顺序插入行到一个索引更有效。当他们填充时B-tree节点会发生分裂,以这种方式构建索引对索引来说有一个较高的填充因子,使后续访问更有效。
主键和二次键索引
从历史上看, 对表和索引结构MySQL服务器和InnoDB各自保持自己的元数据。MySQL服务器存储这些信息.frm文件,不受事务机制的保护,而InnoDB有其自己的数据词典作为系统表空间的一部分。如果一个DDL操作被一个崩溃或其他意外事件中断了,元数据可能在这两个位置之间产生不一致,导致其他问题,如启动错误或无法访问正被改变的表。现在,InnoDB是默认的存储引擎,解决这些问题是当务之急。这些增强DDL操作减少发生这样的问题的机会。
5.5.7.崩溃恢复如何适用于在线DDL
当ALTER TABLE语句执行时,如果服务器崩溃了,虽然没有数据丢失 , 聚集索引和二级索引崩溃恢复过程是不同的。
如果当创建一个InnoDB辅助索引,服务器崩溃,在恢复时,MySQL删除任何部分创建的索引。你必须重新运行ALTER TABLE或CREATE INDEX语句。
当在创建InnoDB聚集索引期间发生系统崩溃,恢复更复杂,因为表中的数据必须被复制到一个全新的聚集索引。记住,所有InnoDB表存储为聚集索引。在接下来的讨论中,我们使用word表和聚集索引可以互换。
MySQL创建新集群索引通过从原始InnoDB表复制现有的数据到所需的索引结构的一个临时表。一旦数据是完全复制到这个临时表,原始表重命名为一个不同于临时表的名字。包括新的聚集索引临时表重命名为原始表的名称,和原始表被从数据库删除。
如果系统崩溃发生在创建一个新的聚集索引,没有数据丢失,但你必须使用过程中存在临时表来完成恢复。虽然它在大表重建一个聚集索引或重新定义主键,或在此操作遇到系统崩溃是罕见的,本书册没有提供这个场景中恢复的信息。
5.5.8.对分区表的DDL
这个机制用于分区表的DDL操作有点不同online DDL中的讨论。ALTER TABLE语句的分区条款只允许结合ALGORITHM=DEFAULT和LOCK=DEFAULT。尽管这些的操作不经过用于非分区表内部的online DDL API,MySQL仍然试图在可能的情况下最小化数据复制和锁:
5.5.9.Online DDL的局限性
在创建或删除InnoDB索引考虑下列事项:
【译者注】
后续会对本片文章的内容,进行功能性为主的测试验证,并且告诉读者DDL什么情况下会对数据服务有影响,什么情况下对数据服务不会影响,以及DDL变更的技巧。