本文是从微信公众号「程序新视界」转载的，作者是二师兄。若需转载此文请与程序新视界公众号联系。在项目背景中有一张历史记录表，主要记录了一些接口调用的流水。由于该表的重要性不高，初始创建者并没有为核心字段创建索引。不知不觉这张表已经积累了5千万条数据，但由于没有建立索引，当我们尝试排查问题时，发现这个表根本无法查询。他们决定着手进行数据分表并创建索引。这个表格在系统中只执行插入操作，对整体影响非常有限，正适合用来练习。

解决方案

：我们都知道，在Mysql 5.5以及之前的版本中，在生产环境中对大表进行alter操作会导致表的重建和锁定，从而影响业务正常运行。自MySQL 5.6版本开始，引入了Online DDL特性，这使得在执行alter操作时可以同时运行select、insert、update和delete语句。当数据量小于100万时，建议考虑直接修改表结构并创建索引，这样正常情况下只需要几秒钟就可以完成。当表的数据量超过一百万时，无论是Mysql 5.6及之前的版本中的表锁行为，还是因为慢SQL引起的Mysql 5.6中的等待，都不允许在生产库中直接进行alter table操作。目前，拥有五千万条数据，直接修改表格以建立索引是不可行的，可能会导致数据库崩溃。必须寻找其他解决办法。考虑到这个方案只对局部产生影响，我们想到了将其拆分成多个表的解决方案。无论是逐个区间拆分所有数据，还是将整个表替换为新表然后处理历史数据，基本上都需要进行拆分处理。解决

的基本思路如下：

\n1. 首先，创建一个和数据结构相同的新表（包含完整的索引），将业务迁移到新表，这样新生成的数据就拥有了索引； \n2. 其次，对旧表数据进行备份，以便后续处理中出现问题时进行恢复； \n3. 最后，按照数据ID，把一千万条数据拆分成一个新的表（带有完整的索引）； \n目前数据库访问层并未使用分表数据，但如果业务中有其他地方使用，可以考虑根据请求时间区间或ID等在数据库访问层切换数据库表名。使用阿里云的数据库服务进行数据备份有多种操作方式，可以通过直接复制表、使用Navicat或者mysqldump等方式来导出数据。复制一张新表的语句如下：\n```sql\ncreate table account_log_1 as select * from account_log;\n```\n在测试环境中验证了一下，粗略估计这种方式大约需要1小时才能完成数据备份。由于Linux生产服务器上未安装MySQL，无法使用mysqldump导出数据。最终，我们使用堡垒机上的Navicat导出功能，将内容导出为SQL语句。

结果也相当令人失望，一份5千万的数据：导出所需时间为1小时22分钟，导出SQL语句占用磁盘空间38.5G。在导出过程中，通过监控查看数据库的整体性能还是相对稳定的。

为了节省堡垒机的磁盘空间，特意花了十多分钟将38.5G的数据进行压缩，最后只占用了3.8G的存储空间。Navicat和mysqldump的性能进行对比时，Navicat导出的数据是以一条条的insert语句显示，每一行对应一条插入语句。将由mysqldump导出的多行数据合并为一行并插入。批量插入可以减少SQL语法解析的次数，减少插入操作的事务开销，同时减少数据传输量。数据分区可以完成数据备份，接下来需要做的是创建不同的新表，并按照分区的方式将数据导入。复制表格结构：执行以下命令复制表格结构：{换行} create table account_log_1 like account_log;{换行}这将创建一个结构相同但不包含数据的表，并添加索引。接着，利用已添加索引的表，创建出account_log_2、account_log_3等表。不同的表结构复制方法有所差别，在完成复制后，应检查新表的主键、索引等是否存在。由于该表并不涉及具体的实际业务，而且在设计时缺少创建时间字段，因此采用以ID为区分的方式，每1000万条数据一张表。执行

数据迁移

时，可以使用以下语句，将前1000万条数据直接插入到第一张表中：

INSERT INTO account_log_1 SELECT * FROM account_log WHERE id <= 10000000; 

执行1000万条数据仅花费了205秒，约3分25秒。粗略计算，使用这种方法备份完整的5000万条数据，只需要约18分钟。因此，可以说上述的导出操作走了一条弯路，同时也暴露了使用Navicat导出的性能问题。

在验证数据

执行两个查询语句，以确认导入新表的数据与原始数据的记录数是否一致：

执行以下两条查询来验证：\n```sql\nselect count(1) from account_log_1;\nselect count(1) from account_log where id <= 10000000;\n```\n

通过比较数据条数确认一致，验证无误。删除旧表中的历史数据并导入到新表（备份数据），可以提高后续查询速度。如果这个表已经不再使用，当然也可以暂时保留。删除语句：从account_log表中删除id小于等于10000000的记录；\n这里暂时不删除；\n接下来将循环执行导入操作。扩展id条件范围为：

NSERT INTO account_log_2 SELECT * FROM account_log WHERE id <= 20000000 and id > 10000000; 

接着循环验证、删除等操作，直至整个大表被完整分割。在进行循环查询插入操作时，发现在未删除数据记录的情况下，处于中间部分的数据迁移耗时最长。这主要是由于查询时索引的特性所决定的。在循环查询插入的过程中发现一个现象：当未删除数据记录时，位于中间部分的数据迁移耗时最长，主要原因是查询时索引的特性所决定的。性能验证中，验证了count语句的耗时情况：执行了如下查询 `select count(1) from account_log_2;` 耗时1.8秒完成查询并返回结果；同时也验证了 `count(id)` 和 `count(*)` 的查询，在数据量达到1000万条时，并未观察到明显的性能差异。在实验中发现，MySQL 可能会对查询结果进行缓存处理。第一次查询时可能会花费较长时间，但后续的查询则可能会更快。

经过验证，根据索引进行数据查询的效率非常高。在1000万条数据中进行查询记录时，只需要800毫秒就能够得到结果，效率大大提升。

针对大表数据迁移进行了深入思考

通过实际操作大表数据迁移，我对这一过程有了全新的认识和直观体会。仅仅阅读技术文章，会让人觉得一切都很简单，可以轻而易举地实施。但实际操作时，才会发现有很多可以提升和改进的地方。在

学习过程中，我们遇到了一些值得深思的问题：

首先，在进行大量数据的导出时，我们不仅需要考虑导出的时间，还需要考虑导出数据所需的空间以及随之产生的存储和传输问题；同时，我们还需要关注大数据的读取和插入是否会导致表的死锁问题。通常情况下，导出数据不会对表加锁，导出时会对表加锁; 监控导出操作是否会对服务器实例的IO、带宽、内存产生影响，例如内存溢出等问题; 迁移特殊类型的数据，例如(blob)，在导入时是否会丢失; 不同的引擎之间对导入数据是否会有影响。阅读本文，您获得了什么收获？你发现了哪些之前不曾知道的内容？