如何为千万级数据的table_test表添加自增字段用于分页？

针对你的场景——1000万条数据的table_test表，需要添加自增字段用于分页，我会推荐低锁表风险的分步添加+分批填充方案，同时也会分析其他方案的利弊，帮你做出选择。

核心背景说明 #

你的表用ad_id+country作为联合主键，本身已经具备唯一性，但分页时用联合主键做条件（比如WHERE ad_id > ? AND country > ?）虽然可行，但语法相对繁琐；自增字段会让分页逻辑更简洁，同时搭配索引后性能也有保障。不过直接给大表加自增字段要注意避免长时间锁表，影响业务正常运行。

最优分步实现方案 #

步骤1：添加可空的BIGINT字段（快速完成，无长时锁表） #

首先添加一个普通的id字段（类型选BIGINT，避免INT类型存储上限不足），允许为空。InnoDB的这个操作属于在线DDL，不会重建整张表，执行速度极快：

ALTER TABLE table_test ADD COLUMN id BIGINT DEFAULT NULL;

步骤2：分批填充自增数据（避免锁表） #

直接一次性更新全表会导致长时间锁表，影响业务读写。我们可以分批更新，每次处理1000-5000条（具体数量可根据你的数据库性能调整）：

SET @row_num = 0;
-- 分批更新，每次处理1000条
UPDATE table_test 
SET id = (@row_num := @row_num + 1)
WHERE id IS NULL
LIMIT 1000;

重复执行这条SQL，直到输出的影响行数为0，说明所有数据都已填充完成。

步骤3：设置字段为非空并开启自增 #

填充完成后，把字段设置为非空，同时开启自增属性，这样后续插入的新数据会自动生成自增值：

-- 先设置非空
ALTER TABLE table_test MODIFY COLUMN id BIGINT NOT NULL;
-- 开启自增，MySQL会自动识别当前最大值作为自增起始点
ALTER TABLE table_test MODIFY COLUMN id BIGINT NOT NULL AUTO_INCREMENT;

步骤4：添加索引（优化分页性能） #

分页时需要对id排序，给id加一个普通索引即可（自增字段本身是有序的，索引效率极高）：

ALTER TABLE table_test ADD INDEX idx_id(id);

如果后续有需要，也可以考虑把id设为主键，但原联合主键已经满足唯一性需求，普通索引足够支撑分页场景。

其他方案的利弊分析 #

方案1：直接添加自增字段（不推荐） #

如果直接执行：

ALTER TABLE table_test ADD COLUMN id BIGINT NOT NULL AUTO_INCREMENT FIRST;

MySQL会重建整张表，1000万条数据的话，这个操作会耗时很久，并且全程锁表，业务会完全停摆，绝对不推荐在生产环境的大表上这么做。

方案2：用联合主键直接分页（可选替代） #

其实你完全可以不用新增字段，直接利用现有的联合主键做分页，比如：

-- 假设上一页最后一条数据的ad_id=123，country='US'
SELECT * FROM table_test 
WHERE (ad_id > 123) OR (ad_id = 123 AND country > 'US')
ORDER BY ad_id, country
LIMIT 100;

这个方案不需要修改表结构，也没有锁表风险，但分页逻辑的写法会复杂一些，适合不想改动表结构的场景。

总结 #

如果你的核心需求是用简洁的自增字段实现分页，分步添加+分批填充的方案是最优选择，既保证了性能，又避免了长时间锁表影响业务；如果可以接受复杂一点的分页语法，用现有联合主键分页也是完全可行的。

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