为已有SQL表创建索引：两种实现方式的优缺点分析

场景说明：我们有一张Person表，ID为主键，表结构及数据如下：

现在要给Age列创建名为age_index的索引，有两种实现语句：

Option #01 #

create index age_index on Person(Age);

Option #02 #

alter table Person add index age_index (Age);

以下是两种方法的优缺点分析：

Option #01 优缺点 #

• 优点
  • 语法针对性强，一眼就能看出是专门创建索引的操作，可读性高。
  • 部分数据库（如PostgreSQL）支持在此语句中附加特殊参数（比如CONCURRENTLY，创建索引时不锁表），灵活度更高。
  • 操作范围明确，仅涉及索引创建，不会误触表结构的其他修改（比如修改字段、添加约束）。
• 缺点
  • 只能单独执行索引创建操作，无法和其他表结构变更（如添加字段、修改字段类型）合并到同一条语句，多操作场景下需要多次执行。
  • 极少数老旧数据库版本对该语法的支持不如ALTER TABLE完善（主流数据库无此问题）。

Option #02 优缺点 #

• 优点
  • 支持在同一条语句中批量执行多个表结构变更操作（比如同时创建索引、添加字段、修改约束），减少大表上的锁表次数，提升操作效率。
  • 属于SQL标准的表结构修改语句，兼容性极佳，所有支持SQL规范的数据库都能正常执行。
• 缺点
  • 语义宽泛，属于表结构修改范畴，和其他表变更操作混用时，可读性稍差，需要仔细核对才知道是在创建索引。
  • 若在同一条语句中执行多个操作，一旦某一步失败，整个语句会回滚，已执行的变更也会被撤销（具体取决于数据库的DDL原子性实现）。

总结 #

如果只是单纯创建索引，两种方法均可；如果有多个表结构变更需求，用ALTER TABLE的方式能减少操作次数，更高效。

内容的提问来源于stack exchange，提问作者Vamshi Reddy Verama