CREATE INDEX 是什么
CREATE INDEX 为表建立索引，支持单列、复合列与唯一索引，也可以用 ALTER TABLE ADD INDEX 把索引变更与其他表变更写在一起。落地时要把三件事定清楚：命名规范（统一前缀加表名与列名）、列顺序（等值过滤列在前，范围与排序列在后）、建前先查现有索引避免重复。大表建索引还要考虑锁与在线 DDL 的影响，别在高峰期动手。
基本信息
| 适用平台 | MySQL 8.0 / PostgreSQL / SQL Server / Oracle |
|---|---|
| MySQL 语法 | CREATE INDEX idx_name ON 表 (列…)；ALTER TABLE 表 ADD INDEX idx_name (列…) |
| 删除差异 | MySQL 要写 DROP INDEX 索引名 ON 表名；PostgreSQL 只写 DROP INDEX 索引名 |
| 命名规范 | 统一前缀 idx_ / uk_ + 表名 + 列名，便于检索、治理与删除 |
| 唯一索引 | 同时承担约束语义：写入重复值会失败 |
| 在线建索引 | PostgreSQL 用 CREATE INDEX CONCURRENTLY；MySQL InnoDB 8.0 支持在线 DDL |
典型使用场景
- 给慢查询的过滤列补索引，减少扫描行数
- 给排序列建索引，消除 filesort 与临时排序
- 用唯一索引做业务约束（邮箱、编号、单据号）
- 建复合索引支撑最左前缀查询，一条索引覆盖多种查询形态
- 覆盖索引把查询需要的列都放进索引，避免回表
- 配合不可见索引或部分索引做上线前的灰度验证
MySQL 写法
MySQL 写法
CREATE INDEX idx_user_created ON orders (user_id, created_at); # 等值列在前，范围列在后
CREATE UNIQUE INDEX uk_email ON users (email); # 唯一索引兼做业务约束
ALTER TABLE orders ADD INDEX idx_status (status); # 与其他表变更合并提交
DROP INDEX idx_status ON orders; # MySQL 删索引必须带表名
SHOW INDEX FROM orders; # 建之前先看已有索引，避免重复PostgreSQL 写法
PostgreSQL 写法
CREATE INDEX idx_user_created ON orders (user_id, created_at);
CREATE UNIQUE INDEX uk_email ON users (email);
DROP INDEX idx_user_created; -- PG 不需要表名
-- 大表在线建索引，尽量不长时间阻塞写入（不能在事务块内执行）
CREATE INDEX CONCURRENTLY idx_orders_status ON orders (status);
-- 确认索引是否建好、是否有效
SELECT indexrelid::regclass, indisvalid FROM pg_index
WHERE indrelid = 'orders'::regclass;建索引的四个关键点
| 关键点 | 做法 | 原因 |
|---|---|---|
| 命名 | 前缀 + 表名 + 列名 | 便于治理、排查与后续删除 |
| 列顺序 | 等值列在前，范围与排序列在后 | 最左前缀决定索引能否被复用 |
| 重复检查 | 建前 SHOW INDEX 或 \d 表名 | 冗余索引会拖慢写入却没有收益 |
| 唯一与普通 | 需要约束语义就用唯一索引 | 唯一索引同时承担数据校验职责 |
| 大表执行时机 | 低峰执行；PG 用 CONCURRENTLY | 减少锁等待与写入抖动 |
索引不是越多越好：每个索引都要在写入时同步维护，冗余与长期未使用的索引会明显拖慢插入与更新，建议定期用索引使用情况视图做一次清理评估。
PostgreSQL 的 CREATE INDEX CONCURRENTLY 失败会留下 INVALID 索引：它不会被查询使用却仍消耗写入成本，必须删除后重建；执行前确认没有长事务，否则它会一直等待。