在MySQL数据库的运维和开发过程中，为表添加索引是提升查询性能最常见的操作。不同的添加方式在语法、锁表行为、执行效率以及对线上业务的影响上都有明显区别。理解这些方式的差异，能帮助我们针对小表、大表、以及高并发场景选择最合适的建索引方案。
一、使用CREATE INDEX语句添加索引
CREATE INDEX是最直观、语义最清晰的添加索引方式，它属于标准SQL语法，在MySQL中会被映射为ALTER TABLE操作。通过这种方式可以创建普通索引、唯一索引、全文索引等，但不能用来创建主键索引。
下面示例为user表的email字段创建唯一索引，避免重复注册：
-- 创建普通索引 CREATE INDEX idx_name ON user(name); -- 创建唯一索引 CREATE UNIQUE INDEX uk_email ON user(email); -- 创建组合索引 CREATE INDEX idx_age_city ON user(age, city);
这种写法在MySQL 5.6之前的大表上执行时，默认会阻塞表的写入和读取，直到索引构建完成。从MySQL 5.6开始，InnoDB引擎支持在线DDL，CREATE INDEX默认采用INPLACE算法，允许在索引创建期间并发执行DML语句，大大降低了加索引对业务的影响。
需要注意的是，虽然在线创建索引允许并发DML，但在索引构建的某些阶段仍会获取元数据锁（MDL），如果此时有长事务未提交，可能造成锁等待。因此执行前应当检查是否有慢查询或长事务占用表。
二、使用ALTER TABLE语句添加索引
ALTER TABLE是MySQL内部真正执行索引变更的命令，CREATE INDEX本质上也是转换成ALTER TABLE ... ADD INDEX。使用ALTER TABLE可以添加包括主键在内的各类索引，语法更加灵活。
以下示例展示通过ALTER TABLE添加主键和二级索引：
-- 添加主键索引 ALTER TABLE user ADD PRIMARY KEY(id); -- 添加普通索引 ALTER TABLE user ADD INDEX idx_phone(phone); -- 添加全文索引 ALTER TABLE article ADD FULLTEXT INDEX ft_title(title);
ALTER TABLE在添加索引时可以显式指定算法和锁策略，例如使用ALGORITHM=INPLACE和LOCK=NONE来强制在线无锁构建。对于不支持INPLACE的索引类型（如某些全文索引），则需要使用ALGORITHM=COPY，此时会复制整张表，性能开销很大。
在实际生产中，如果要对大表加索引，推荐明确写出ALGORITHM和LOCK参数，让执行计划更可控。若数据库版本较旧不支持在线DDL，则应安排在业务低峰期，并考虑使用pt-online-schema-change等第三方工具来规避锁表问题。
三、建表时直接定义索引
在CREATE TABLE语句中直接定义索引，是开发阶段最常用的方式。此时表还没有数据或数据量极小，索引会随着表创建一并建立，不存在后期大表加索引的性能隐患。
示例在建表时定义主键、唯一索引和组合索引：
CREATE TABLE order_info (
id BIGINT NOT NULL AUTO_INCREMENT,
user_id BIGINT NOT NULL,
status TINYINT NOT NULL,
created_at DATETIME NOT NULL,
PRIMARY KEY(id),
UNIQUE KEY uk_order_no(order_no),
KEY idx_user_status(user_id, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
这种方式适合项目初期或新功能上线时规划好查询模式，提前建立索引。由于建表时数据为空，索引构建成本极低。但如果在设计阶段遗漏了高频查询字段的索引，后续仍要通过前面两种方式补建。
需要提醒的是，在CREATE TABLE中定义外键时，MySQL会自动为该列创建索引，这是隐式行为，开发者应了解这一机制，避免重复创建造成资源浪费。
四、不同添加方式的对比与选型
为了更清晰地看出差异，我们从锁表情况、适用阶段和复杂度三个维度对比上述方式：
| 添加方式 | 是否支持在线DDL | 适用场景 | 能否建主键 |
|---|---|---|---|
| CREATE INDEX | 默认INPLACE（5.6+） | 已有表补索引 | 否 |
| ALTER TABLE ADD INDEX | 可显式控制 | 已有表补索引、改结构 | 是 |
| CREATE TABLE定义 | 不涉及 | 新建表初期 | 是 |
从运维角度看，如果表数据量在百万以内，任何方式差异不大；当表数据超过千万，必须优先使用支持INPLACE的ALTER TABLE并指定LOCK=NONE，或者采用gh-ost等工具。建表时定义索引则是成本最低的方案，应当在设计评审时充分考虑。
另外，添加索引后应通过EXPLAIN验证查询是否真正走了索引，避免由于字段类型不匹配或函数包裹导致索引失效，那样即使添加了索引也无性能收益。
五、添加索引的注意事项
首先，索引并非越多越好。每个索引都会占用存储空间，并在写入时增加维护成本。对于写多读少的表，应当精简索引数量，只保留核心查询所需。
其次，大表在线加索引虽然支持并发DML，但仍会消耗大量IO和CPU，建议在资源空闲时执行，并监控主从延迟，避免从库重放缓慢导致读写分离失效。以下命令可查看当前DDL进度（MySQL 5.7+）：
-- 查看长任务进度 SELECT * FROM performance_schema.events_stages_current WHERE EVENT_NAME LIKE '%alter%';
最后，添加索引前应在测试环境用接近生产的数据量验证，确认语法和性能符合预期。如果是云数据库，也可利用其提供的在线变结构功能，进一步降低操作风险。