mysql如何优化大表Alter Table添加索引速度_调整排序缓冲区大小

ALTER TABLE 添加索引卡住或极慢，本质是排序阶段阻塞

处理大表时，ALTER TABLE ... ADD INDEX 命令卡住不动，或者进度慢得让人心焦，这种情况想必不少朋友都遇到过。问题出在哪？很多时候，真正的瓶颈并非加锁或数据复制，而是隐藏在背后的排序阶段。

免费影视、动漫、音乐、游戏、小说资源长期稳定更新！ 👉 点此立即查看 👈

当 MySQL 尝试使用“原地算法”（比如 ALGORITHM=INPLACE）失败后，它会退回到一条更传统的路径：“拷表+排序重建索引”。这个过程中，系统需要对全表数据按照索引列进行排序。如果内存缓冲区不够大，这个排序操作就会从内存计算退化为频繁的磁盘读写，I/O 压力陡增，速度自然一落千丈。其核心依赖，就是 sort_buffer_size 和 read_rnd_buffer_size 这两个参数。

调大 sort_buffer_size 能明显提速，但有边界

顾名思义，sort_buffer_size 控制着每个线程用于排序的内存缓冲区大小。它的默认值通常只有 256KB，在面对千万级大表时，这点空间简直是杯水车薪。

• 一旦单次排序无法在内存中完成，系统就会触发多路归并排序，导致磁盘临时文件数量呈指数级增长，性能急剧下降。
• 将其增大到 4MB 至 16MB 是常见且有效的做法，但超过 32MB 后，性能收益会急剧衰减。这是因为排序效率还受到 max_sort_length 和实际字段长度的制约。
• 这是一个会话级变量，只影响当前连接。因此，在执行 DDL 前，可以临时设置：SET SESSION sort_buffer_size = 8388608;（即 8MB）。
• 需要注意的是，切勿在全局范围内将此值设得过大，否则在高并发场景下极易引发内存溢出（OOM）。最佳实践是在专用的维护窗口临时调高，操作完成后立即恢复。

别忽略 read_rnd_buffer_size 和临时目录 IO

排序完成后，系统还需要“回表”读取相关数据（例如，建立二级索引时需要获取对应的主键值），这个过程则由 read_rnd_buffer_size 参数控制。它同样常常被低估，默认值（通常也是 256KB）在处理大表时显得捉襟见肘。

• 建议将其调整到与 sort_buffer_size 相近的量级：SET SESSION read_rnd_buffer_size = 8388608;。
• 另一个关键点是 MySQL 的临时目录（tmpdir）。务必确保它位于 SSD 磁盘上，并且有充足的剩余空间。要知道，排序产生的临时文件大小可能达到原表数据的 1.5 倍。
• 可以通过 SHOW VARIABLES LIKE 'tmpdir'; 命令查看当前路径，避免临时目录落在系统盘或已经满载的磁盘上，那会成为新的性能杀手。

更稳的替代方案：用 ALGORITHM=COPY + LOCK=NONE 组合

从 MySQL 5.6 开始，虽然支持在线 DDL，但并非所有操作都会自动启用最优算法。有时候，显式指定策略反而能绕过排序瓶颈。

• 尝试使用命令：ALTER TABLE t ADD INDEX idx_col (col) ALGORITHM=COPY, LOCK=NONE;。这强制 MySQL 采用拷表方式，但同时允许并发读写（前提是使用 InnoDB 引擎，且不涉及全文索引等限制）。
• 这种方式不依赖于上述的排序缓冲区，而是边读取原表数据边构建索引 B+树，内存压力分布更为平缓。
• 需要确认 innodb_online_alter_log_max_size 参数足够大（默认 128MB），否则 DDL 操作可能因记录并发修改的日志溢出而中途失败。
• 执行前，最好用 SELECT COUNT(*) 预估一下时间：在 SSD 环境下，每百万行数据大约需要 30 到 90 秒。

话说回来，还有一个根本性问题容易被忽略：即便把排序缓冲区调得再大，也拯救不了索引列本身选择性极差的场景。例如，在仅有‘M’和‘F’两种取值的 `gender` 字段上建索引。这种索引本身就不该创建，强行添加只会拖慢所有写入操作，而对查询速度的提升微乎其微。在优化技术细节之前，先审视索引设计的合理性，这才是治本之道。