生产环境加索引不阻塞业务
在大表上加索引如果按默认方式执行，会长时间持有较强的锁，业务写入被挡在外面。两种数据库都提供了降低阻塞的手段：MySQL 可以在变更语句里显式声明算法与锁级别，让不支持在线执行的场景直接报错而不是偷偷退化成锁表；PostgreSQL 用专门的并发建索引语句，代价是耗时更长、不能在事务块里执行，中途失败还会留下无效索引需要清理。
基本信息
| 适用平台 | MySQL 8.0 / PostgreSQL 12 及以上 |
|---|---|
| MySQL 手段 | 变更语句里显式声明算法与锁级别 |
| PostgreSQL 手段 | 并发建索引语句，专门为不阻塞写入设计 |
| 事务限制 | PostgreSQL 的并发建索引不能在事务块内执行 |
| 失败残留 | PostgreSQL 失败会留下无效索引，需删除后重试 |
典型使用场景
- 线上大表加索引：要求写入不被长时间阻塞
- 索引重建：碎片化严重时重新构建
- 变更前的可行性验证：确认操作是否支持在线执行
- 清理失败残留：删除无效索引后重新创建
- 评估执行窗口：结合复制延迟与磁盘压力安排时间
两库在线加索引
两库在线加索引
-- MySQL：显式声明算法与锁级别
ALTER TABLE orders
ADD INDEX idx_status (status), ALGORITHM=INPLACE, LOCK=NONE;
-- 若引擎或操作不支持，语句直接报错，而不是退化成锁表执行
-- PostgreSQL：并发建索引，降低锁级别
CREATE INDEX CONCURRENTLY idx_orders_status ON orders (status);
-- 不能在事务块中执行；需额外扫描与等待，耗时更长
-- 失败会留下无效索引，先清理再重试
DROP INDEX CONCURRENTLY idx_orders_status;
-- 确认索引是否有效
-- SELECT indexrelid::regclass, indisvalid FROM pg_index
-- WHERE indrelid = 'orders'::regclass;
-- 执行期间关注复制延迟与磁盘占用要点对照
| 要点 | MySQL | PostgreSQL |
|---|---|---|
| 声明方式 | 变更语句内的算法与锁子句 | 专用的并发建索引语句 |
| 不支持时 | 直接报错，不退化 | 不适用 |
| 能否放事务里 | 可以 | 不可以，会报错 |
| 耗时 | 相对较短 | 更长，需要额外扫描 |
| 失败残留 | 自动回滚 | 留下无效索引需手动清理 |
MySQL 显式声明算法与锁级别的价值在于「宁可报错也不锁表」：不支持在线执行时立刻失败，避免在业务高峰期悄悄执行一次阻塞全表的操作。
PostgreSQL 的并发建索引对写入影响小，但对资源消耗更大，而且需要等待已有事务结束。安排在业务低峰、并观察复制延迟，是常规做法。