实践了5千万的数据表和重建索引，学到了！
背景
项目中有一张历史记录表，主要用于记录一些接口调用流水，因为该表的地位不是那么重要，当初的创建者并未对核心字段创建索引。
不知不觉这张表的数据已经有5千万数据了，由于没有索引，在排查问题时，发现这种表根本查不动。
于是，决定下手进行分表并建立索引。这张表在系统中只负责插入，影响范围极小，正好拿来练手。
解决思路
我们知道，在Mysql 5.5及之前版本，在运行的生成环境中对大表执行alter操作，会引起表的重建和锁表，影响业务正常运转。
从Mysql 5.6开始，Online DDL特性被引进，运行alter操作时同时允许运行select、insert、update、delete语句。
在数据量小于100w时，可以考虑直接修改表结构建立索引，正常几秒钟就可以完成。但当表的数据量超过百万，无论Mysql 5.6及之前版本的锁表行为、Mysql 5.6中因慢SQL引起的等待，都不允许直接在生产库中进行alter table操作。
目前，五千万的数据，直接修改表来建立索引，肯定是不可行的，弄不好还把数据库给弄崩了。只能想另外的方法。
解决方案
鉴于这张表本身的影响范围有限，想到的解决方案就分表。无论是将所有数据一个区间一个区间的拆分出去，还是将整个表都换成新表，然后再处理历史数据，基本上都要做拆分处理。
基本解决思路：
- 第一步：创建一张数据结构一样的新表（补全索引），将业务切换至新表，这样新生成的数据便有了索引；
- 第二步：对旧表数据进行备份，已被后续处理过程中有问题进行恢复；
- 第三步：按照数据ID，1000万条数据拆分一个表，新拆分的表（补全索引）；
对于分表的数据，数据库访问层并未使用，如果业务中有其他地方使用，则可考虑在数据库访问层根据请求时间区间或ID等来切换数据库表名。
基本操作
备份数据
数据库基于阿里云的云服务，导出数据有多种方式，比如直接copy出一张表、基于Navicat导出、基于mysqldump导出等。
copy出一张新表语句如下：
create table account_log_1 select * from account_log;
在测试环境上验证了一下，粗略估计该方式得1小时左右才能执行完数据的备份。
由于没有安装Mysql的linux生产服务器可用，就没采用mysqldump方式导致。
最终，采用在堡垒机上通过Navicat的导出功能，导出内容为SQL语句。
结果也很令人失望，5千万的数据：导出耗时1小时22分钟，导出SQL语句磁盘空间占用38.5G。还好在导出过程中，通过监控查看数据库的整体性能还比较平稳。
为了节省堡垒机的磁盘空间，又花费了十多分钟将38.5G的数据进行压缩，最终占用3.8G的存储空间。
Navicat与mysqldump性能对比
Navicat导出的数据是一条条的insert语句，每一行一条插入语句。
mysqldump导出的数据，多行数据合并成一行插入。批插入减少SQL语法词法解析，减少插事务（最大的开销），较少数据的传输；
数据分区
完成了数据备份，剩下的就是创建不同的新表，并安装分区将数据导入了。
复制表结构
执行表结构的copy：
create table account_log_1 like account_log;
创建一个结构一样的不带数据的表，并对表添加索引。然后再基于添加过索引的表，创建出account_log_2、account_log_3等表。
不同的表机构复制方式有所区别，复制完成之后，注意检查一下新表的主键、索引等是否存在。
由于该表并为具体的实际业务，而且表在设计时缺少创建时间字段，因此就以ID为区分，每1000w条数据一张表。
迁移数据
执行以下语句，直接将前1000w条数据插入到第一张表中：
INSERT INTO account_log_1 SELECT * FROM account_log WHERE id <= 10000000;
执行1000w条数据，用时205秒，大概3分钟25秒。粗略估算，5000万数据如果通过此种方式将全表数据备份，也只需要18分钟左右。
因此，上面到导出操作算是走的弯路，也见证了一下通过Navicat导出的性能问题。
验证数据
执行两条查询语句，验证一下导入新表的数据与原始数据的数据量是否一致：
select count(1) from account_log_1;
select count(1) from account_log WHERE id <= 10000000;
数据条数一致，验证无误。
删除历史数据
已经导入新表的历史数据（备份数据）是可以进行删除的，提升续查询速度。当然，如果该表已经不使用，则也可以暂时保留。
删除语句：
delete from account_log where id <= 10000000;
这里就暂时不删除了；
循环执行导入
后续操作就是循环执行导入操作了，将id的条件区间进行扩展：
INSERT 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(*)的查询，发现在1000w数据的情况下，性能差别并不明显。
select count(*) from account_log_2;
select count(id) from account_log_2;
在实验的过程中发现，Mysql可能进行了缓存处理，在第一次查的时候时间较长，后续再查就比较快了。
后续有验证了根据索引查询的效率，1000w数据中查询记录，800毫秒能能查询出结果来，提升效率非常显著；
大表数据迁移思考
经过此次大表数据迁移的实践，对大表迁移有了新的认知和直观感受。单纯的只看技术文章，感觉一切都轻而易举可以实施，但真正实践时才会发现有很懂可提升和改进的地方。
学到和一些值得思考的问题：
- 大表导出不仅要考虑导出的时间问题，还需要考虑导出数据的空间问题，以及衍生出来的存储和传输问题；
- 大数据读取与插入是否会造成表的死锁。一般，导出数据没有表锁，导出会对表加锁；
- 监控导出操作是否会对服务器实例的IO、带宽、内存造成影响，造成内存溢出等。
- 迁移的数据特殊类型例如（blob）会不会在导入的时候丢失；
- 不同的引擎之间是否会对导入数据有影响；
通过本篇文章你学到了什么？了解到了什么不曾知道的点？如果有那么一些启发，给点个赞吧。
关注公众号：程序新视界，一个让你软实力、硬技术同步提升的平台
除非注明，否则均为程序新视界原创文章，转载必须以链接形式标明本文链接
