SQL Server ->> 谈表数据迁移时，先建索引再插入数据，还是先插入数据再建索引的问题

这个事情源于我前阵子做一个数据迁移时遇到的惨痛经历。几个月前我们生产环境有张10几亿行数据的表因为自增列是INT类型，数据类型需要改成BIGINT，只能迁移数据到新表后通过重命名表的方式来实现表迁移。这个我在另外一篇博文SQL Server ->> 谈SQL Server数据库大表迁移中有谈到。这里主要谈过程中我遇到的问题。

其实这个问题准确来说是三种选择，而不是题目的两种。应该是：到底是：1、先建索引再全表插入数据；2、先插入数据再建索引；3、先建索引，再批次插入数据

 

先建索引再全表插入数据：

这是三者风险最高最高的一种选择，绝对是。先说风险性，整个表的数据一次性插入，整个事务大小级别是极其恐怖的（假设表数据量非常大，而且索引还不少），风险性极高，一旦出现问题，整个回滚的时间是完全不可预期的。你可以想象这个过程要耗费多少系统资源：

1、事务日志会按插入数据量增长，等同于日志文件大小增量只是是表数据写入数据量的大小，假设你的数据本身是100G，那你要保证你的事务日志磁盘空间至少要大于100G；

2、数据插入过程SQL SERVER不仅要把申请数据本身的内存空间，还需要申请索引本身所需的内存空间，也就是SQL SERVER一遍插入数据，一遍还要维护索引。这个时候系统资源压力最大的，我觉得是内存。

3、最后是磁盘IO，连续的数据写入会消耗整个磁盘的最大吞吐，其他的数据查询请求就必须等待。堵塞数据库。

所以先建索引再全表插入数据这种做法，除非表的数据量在百万级别以下，且是在数据库相对空闲的时间点去做这个事情。

 

先插入数据再建索引：

这种应该性能最好的，这里指的性能做好是指所需的时间最短。因为首先先插入数据，数据库无需维护索引，所以整个过程只需要按照聚集索引的顺序写入数据到磁盘。后续建索引的时候，由于建索引可以触发并行处理，所以会尽可能多的利用服务器资源。但是它的风险点很高，就是当表的数据量达到一定量级的时候，虽然比起第一种全体回滚，它的回滚成本要更小，但是如果表的数据量非常大，而且索引的宽度也不小的情况下，即便回滚单挑索引的创建，回滚也是需要一定时长。

这点我犯了一个很致命的错误，就是在迁移一张10几亿行数据的表（聚集索引数据大小就高达450G），选择了先插入数据再建索引。最后在创建索引的时候，由于并行处理，导致服务器内存资源和线程吃紧，服务器出现其他的线程无法连接上数据库的情况。还好是短暂的3分钟，当时是晚上8点多。但是我还是低估了影响，以为服务器性能那么强，服务器是一台Azure的云服务器，32核 128G内存的配置，磁盘是Azure的P50高性能SSD。

所以除非你的表是千万级以下的表，而且服务器窗口期比较长，服务器资源也不错，我觉得用这种方式也不是不行。经过测试，千万级以下的表还是挺快的。

 

先建索引，再批次插入数据：

这种方式是比较推荐的。降低了风险，虽然性能上可能是三者时间消耗最长的（也不一定，和第一种方式比较，都有可能久，反正肯定比第二种要长）。

通过批次的方式，每个几十万一个批次的输入插入，虽然性能上会比较差，尤其表中的数据累计到一定体量后索引维护的耗时会很长。但是风险小。

所以我建议，如果你的表上千万，迁移数据还是先建索引，然后按一个批次一个批次这样插入数据导新表。