如何为MySQL表添加索引以提升查询速度？

CREATE INDEX适用于小表快速建索引，ALTER TABLE ADD INDEX更灵活但可能重建表，建表时定义索引最高效，需注意联合索引顺序、前缀索引及EXPLAIN验证使用效果。

CREATE INDEX 适合快速加单列或复合索引

表已经上线、数据量不大（比如 10 万行以下），直接用 CREATE INDEX 最省事。它不改表结构，只建二级索引，锁表时间极短（InnoDB 下通常毫秒级）。

常见错误：在大表上盲目执行，没加 ALGORITHM=INPLACE 或没评估锁影响。MySQL 5.6+ 支持在线 DDL，但默认仍可能触发表拷贝。

• 加单列索引：CREATE INDEX idx_user_id ON orders (user_id);
• 加复合索引（注意顺序）：CREATE INDEX idx_user_status_created ON orders (user_id, status, created_at);
• 加唯一索引：CREATE UNIQUE INDEX idx_order_no ON orders (order_no);

别漏掉 IF NOT EXISTS（MySQL 8.0.19+ 支持），避免重复建索引报错：CREATE INDEX IF NOT EXISTS idx_user_id ON orders (user_id);

ALTER TABLE ADD INDEX 更灵活但可能触发重建

当你需要同时加索引 + 调整字段类型/约束时，ALTER TABLE ... ADD INDEX 是更自然的选择。但它在旧版本 MySQL 或某些配置下会走“copy-alter”流程——即重建整张表，对大表（> 100 万行）可能卡住业务几小时。

容易踩的坑：ALTER TABLE orders ADD INDEX idx_status (status); 看似简单，但如果 status 区分度极低（比如只有 'pending'/'done' 两种值），这个索引几乎不会被优化器选中，纯属浪费空间和写入开销。

• 支持 UNIQUE、FULLTEXT、SPATIAL 等类型：ALTER TABLE articles ADD FULLTEXT INDEX ft_title (title);
• 可一次加多个索引：ALTER TABLE logs ADD INDEX idx_level_time (level, log_time), ADD INDEX idx_host (host);
• 生产环境务必加 ALGORITHM=INPLACE, LOCK=NONE（MySQL 5.6+）确保在线操作：ALTER TABLE orders ADD INDEX idx_user_id (user_id), ALGORITHM=INPLACE, LOCK=NONE;

建表时定义索引避免后期迁移成本

新表设计阶段就定好索引，是最高效的方式。没有锁表风险，不产生额外 I/O，也不用担心索引命名冲突或遗漏。

关键点在于联合索引字段顺序：等值查询字段放最左，排序字段居中，范围查询字段放最后。例如高频查询是 WHERE tenant_id = ? AND status = ? ORDER BY updated_at DESC LIMIT 20，那就该建 (tenant_id, status, updated_at)，而不是反过来。

• 建表带主键和普通索引：CREATE TABLE events (id BIGINT PRIMARY KEY AUTO_INCREMENT, tenant_id INT, event_type VARCHAR(32), created_at DATETIME, INDEX idx_tenant_type (tenant_id, event_type));
• 别忽略前缀索引场景：对长文本字段（如 url VARCHAR(2048)）直接建全文索引代价高，可用 INDEX idx_url_prefix (url(255)) 平衡区分度与体积
• 如果字段常用于 GROUP BY 或 DISTINCT，优先考虑覆盖索引，把 SELECT 字段也塞进索引里

EXPLAIN 验证索引是否真被用上

加完索引不代表万事大吉。EXPLAIN 才是最终裁判。重点盯三个字段：type（别是 ALL）、key（显示实际命中哪个索引）、rows（预估扫描行数，越小越好）。

典型失效现象：明明建了 (a,b,c) 索引，但 WHERE b = ? 还是 type: ALL；或者 WHERE a = ? AND c > ? 只用到 a，c 后面的字段全失效——这就是最左前缀中断+范围查询截断。

• 检查是否回表：Extra 列出现 Using where; Using index 表示覆盖索引；若只有 Using where，说明还要回主键查数据
• 避免函数导致失效：WHERE DATE(created_at) = '2026-07-01' 不会走 created_at 索引，应改写为 WHERE created_at >= '2026-07-01' AND created_at
• 字符串比较记得加引号：WHERE status = 1（数字） vs WHERE status = '1'（字符串），类型不一致可能触发隐式转换，让索引失效

索引不是越多越好，每多一个索引，INSERT/UPDATE/DELETE 就多一份维护成本。真正要盯的，是慢查询日志里反复出现的 WHERE 条件和 ORDER BY 字段——那里才是索引该落下的地方。