MySQL表添加索引提升查询速度的方法

在MySQL数据库日常运维中，为表添加索引看似简单，但不同场景下选择错误的方式可能导致截然不同的后果。首先给出核心结论：没有万能方案，只有权衡取舍。

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

最简单的场景是：表已上线且数据量不大（例如10万行以下），此时直接使用CREATE INDEX最为高效简便。它不修改表结构，只创建二级索引，锁表时间极短——InnoDB下通常为毫秒级。

然而一个常见误区是：在大表上盲目执行CREATE INDEX，既未指定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);

一个小细节：MySQL 8.0.19+支持IF NOT EXISTS，别忘了加上，能避免重复建索引报错：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 < '2026-07-02'
• 字符串比较记得加引号：WHERE status = 1（数字） vs WHERE status = '1'（字符串），类型不一致可能触发隐式转换，让索引失效

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