MySQL 系列教程（笔记）

# 加快 ALTER TABLE 操作的速度

MySQL 的 ALTER TABLE 操作的性能对大表来说是个问题。MySQL 执行大部分修改表结构的方法是用新的结构创建一个空表，从旧表中查出所有数据插入新表，然后删除旧表。这样操作可能需要花费很长时间，如果内存不足而表又很大，而且还有很多索引的情况下更耗时间。有时候可能需要花费数小时至数天才能完成

MySQL 5.+ 包含一些类型的「在线」操作的支持，这些功能不需要在整个操作过程中锁表。最近版本的 InnoDB 也支持通过排序来建索引，这使得建索引更快并且又一个紧凑的索引布局。

一般而言，大部分 ALTER TABLE 操作将导致 MySQL 服务中断。我们会战士一些在 DDL操作时有用的技巧，但这只是针对一些特殊的场景而言的。对常见的场景，能使用的技巧只有两种：

  • 先在一台不提供服务的机器上执行 ALTER TABLE 操作，然后和提供服务的主库进行切换
  • 另一种是「影子拷贝」，用要求的表结构创建一张和源表无关的新表，然后通过重命名和删表操作交换两张表。

有一些工具可以帮助完成影子拷贝的工作，如 Facebook 数据库运维团队的 「oline schema change」工具、Shlomi Noach 的 openark toolkit、Percona Tookit。

不是所有的 ALTER TABLE 操作都会引起表重建。例如，有两种方法可以改变或则删除一个列的默认值（一个方法很快，另外一种很慢）。加入要修改电影的默认租赁期限，从三天改到 5 天。下面是很慢的方式

alter table sakla.film modify column rental_duration tinyint(3) not null default 5;
1

show status 显示这个语句做了 1000 次读和 1000 次插入操作。换句话说，它拷贝了整张表到一张新表，升值列的类型、大小和可是否为 null 属性都没有改变。

理论上，MySQL 可以跳过创建新表的步骤。列的默认值是上存在表的 .frm 文件中，所以可以直接修改这个文件而不需要改动表本身。然而 MySQL 还没有采用这种优化的方法，所有的 MODIFY COLUM 操作都将导致表重建。

另一种方式是通过 ALTER COLUMN 操作来改变列的默认值

alter table sakila.film alter column rental_duration set default 5;
1

该语句会直接修改 .frm 文件而不涉及表数据。所以该操作也非常快。

# 只修改 .frm 文件

从上面的例子看到直接修改 .frm 文件是很快的，如果愿意冒一些风险，也可以做一些其他类型的修改而不用重建表。

  • 移除（不是增加）一个列的 AUTO_INCREMENT 属性

  • 增加、移除或更改 ENUM 和 SET 常量如果移除的是已经有行数据用到其他值的常量，查询将会返回一个空子串值。

基本的技术是为想要的表结构创建一个新的 .frm 文件，然后用它替换掉已经存在的哪张表的 .frm 文件，像下面这样：

  1. 创建一章有相同结构的空表，并进行所需要的修改（例如增加 ENUM 常量）
  2. 执行 FLUSH TABLE WITH READ LOCK。 这将会关闭所有正在使用的表，并且禁止任何表被打开。
  3. 交换 .frm 文件
  4. 执行 UNLOCK TABLES 来释放第 2 步的读锁。

# 快速创建 MyISAM 索引

为了搞笑的载入数据到 MyISAM 表中，有一个常用的技巧是先禁用索引、载入数据，然后重新启用索引。

alter table test.load_data disable keys;
-- 加载数据
alter table test.load_data enable keys;
1
2
3

这个技巧是利用了构建索引的工作被延迟到数据完全载入以后，这个时候已经开源通过排序来构建索引了。这样做会快很多，并且使得索引树的碎片更小、更紧凑

只是该办法对唯一索引无效。因为 disable keys 只对非唯一索引有效。

在 InnoDB 中，有一个类似的技巧，这依赖于 InnoDB 的快速在线索引创建功能。先删除所有的非唯一索引，然后增加新的列，最后重新创建删除掉的索引。

← 创建高性能索引 →