过去几天我们一直在努力解决一个问题。我们要为一个有60M记录的大表添加索引。起初我们尝试使用基本的 mysql 语法添加它。但它堵塞了我们的生产数据库。该表在生产查询中使用非常频繁。所以一切都受苦了。
我们的数据库托管在 AWS RDS 上。它的Mysql 5.7。我们使用 Laravel 作为 PHP 框架
我们读到的下一件事是,我们可以将当前表复制到新表中。然后为新表添加索引。然后移动 laravel 模型以使用新表。我们认为这是有道理的,而且很容易
但是将表数据从一个表复制到新表需要花费相当多的时间。我们的计算表明这需要几天的时间。我们尝试使用 Laravel 以及 SQL 命令。但无论哪种方式都太慢了。
然后我们尝试将数据导出为 CSV 并导入,但还是太慢了。前几百万条记录插入速度很快,但随后表的插入速度会变得非常慢。
最后我们尝试了mysqldump
,我们意识到它在插入时也会锁定新表,所以也许这就是它足够快的原因。将表格复制到新表格大约花了 6 个小时。但是我们在这个方法中丢失了 2M 条记录。我们还检查了导出/导入时有多少记录进入现有表,只有大约 100K。因此导出/导入丢失了 190 万条记录,我们无法找出原因。
在经历了所有这些不同的方法之后,我们决定让应用程序停机并在巨大的表上添加索引
我想知道其他人也面临这个问题吗?有没有办法在一个巨大的表上添加索引而不导致生产停机?或者有没有更快的方法来复制大的 mysql 表而不丢失数据?
对于此示例,假设数据库 mydb 中有以下内容
并且您想要创建两个索引,如下所示:
有两种方法您确实需要研究。
方法#1:使用在线 DDL
您可以使用以下语法通过 ALTER TABLE 启动更改
由于您要添加索引并且没有更改任何列的任何数据类型,因此将扫描行数据以生成索引页。
方法#2:使用pt-online-schema-change
多年来,这个工具一直是救星
您需要分两个阶段执行此操作
第 1 阶段:试运行
此阶段基本上是语法检查,运行时间不到 5 秒
第 2 阶段:实时运行
这是pt-online-schema-change将会执行的操作
pt-online-schema-change 创建三个触发器
然后,pt-online-schema-change 会将行从 mytable 复制到 _mytable_new。如果运行期间有任何 INSERT、UPDATE 或 DELETE,触发器会将这些更改回填到 _mytable_new
将每一行复制到 _mytable_new 后, pt-online-schema-change 将执行此操作
三个触发器在接近尾声时被删除。
您可以在 dryrun.log 文件中看到这些步骤
可以在liverun.log中看到进度
注意:请在屏幕会话或后台进程中运行 liverun