从含627列85万行的大表提取两列建表超时求助

这种大表执行CREATE TABLE ... AS SELECT慢到出不来结果的情况我遇到过很多次，结合你的表结构（85万条记录、627列，roll_no有唯一索引），核心问题大概率是数据库需要全表扫描+回表查询date_of_birth——毕竟原表列数太多，即使只选两列，无覆盖索引的情况下数据库还是得读取整个数据块来获取date_of_birth，开销极大。下面是几个实用的解决方案：

1. 先创建覆盖索引（最推荐） #

既然roll_no有唯一索引，但这个索引里没有包含date_of_birth，数据库查询时需要通过索引找到每条记录的位置，再回到原表数据块里取date_of_birth——也就是所谓的"回表"，85万条记录的回表开销会非常大。

我们可以创建一个覆盖索引，把需要的两列都包含进去，这样数据库直接扫描索引就能拿到所有数据，不用碰原表的大字段：

针对不同数据库的语法： #

• PostgreSQL：

  CREATE INDEX idx_roll_dob_cover ON sample_1 (roll_no) INCLUDE (date_of_birth);
  

• MySQL（8.0.19+）：

  CREATE INDEX idx_roll_dob_cover ON sample_1 (roll_no) INCLUDE (date_of_birth);
  

  如果你用的是旧版MySQL，直接把date_of_birth加到索引列里（因为roll_no已经是唯一的，所以这个索引也不会有重复）：

  CREATE UNIQUE INDEX idx_roll_dob ON sample_1 (roll_no, date_of_birth);
  

• SQL Server：

  CREATE NONCLUSTERED INDEX idx_roll_dob_cover ON sample_1 (roll_no) INCLUDE (date_of_birth);
  

创建完覆盖索引后，再执行你的CTAS语句，速度会提升几个量级——因为数据库会选择走索引扫描，而不是全表扫描。

2. 分批插入（适合不想创建临时索引的场景） #

如果不想额外创建索引，可以先建空表，再分批次插入数据，这样不会一次性占用过多内存和IO资源，也避免长时间锁表：

1. 先创建结构一致的空表：

   CREATE TABLE test_sample (
       roll_no VARCHAR(/* 这里填原表roll_no的长度 */),
       date_of_birth DATE
   );
   

2. 分批插入数据（比如每次插入10000条，根据你的数据库性能调整批次大小）：

   -- 第一次插入
   INSERT INTO test_sample (roll_no, date_of_birth)
   SELECT roll_no, date_of_birth FROM sample_1 ORDER BY roll_no LIMIT 10000;
   
   -- 后续每次插入，用上次的最大roll_no作为过滤条件
   INSERT INTO test_sample (roll_no, date_of_birth)
   SELECT roll_no, date_of_birth FROM sample_1 
   WHERE roll_no > (SELECT MAX(roll_no) FROM test_sample)
   ORDER BY roll_no LIMIT 10000;
   

   重复执行第二条语句，直到没有新数据插入为止。

3. 检查数据库资源与锁情况 #

有时候CTAS慢不是查询本身的问题，而是数据库被其他任务挤占了资源，或者存在锁等待：

• 用数据库的进程查看工具（比如PostgreSQL的pg_stat_activity、MySQL的SHOW PROCESSLIST）看看是否有其他长查询在运行，或者你的CTAS语句是否被锁阻塞。
• 检查数据库的内存配置（比如work_mem、sort_buffer_size），如果内存不足，数据库会频繁使用磁盘临时表，导致速度骤降。

4. 分区表并行处理（如果原表是分区表） #

如果sample_1是分区表（比如按roll_no或日期分区），可以按分区单独提取数据插入，甚至并行执行多个插入任务，进一步提升速度。

内容的提问来源于stack exchange，提问作者lobh