如何在MySQL中创建索引？MySQL索引创建与优化方法详解

在MySQL数据库中，索引是提升查询效率最核心的手段之一。理解索引的创建方式与后续优化思路，能够让我们在海量数据场景下依然保持稳定的响应速度。本文围绕MySQL索引的创建语法、联合索引设计原则以及常见优化方法展开说明。

一、MySQL中创建索引的基础方式

MySQL支持在已有表上追加索引，也支持在建表时直接定义索引。最常用的三种创建途径分别是CREATE INDEX语句、ALTER TABLE语句以及建表时的INDEX约束。它们底层都依赖存储引擎（如InnoDB）的B+树结构来维护有序数据。

例如，我们有一个用户表user，其中email字段经常被用来做登录查询。如果不为该字段建立索引，每次查询都要进行全表扫描。通过下面的语句可以创建一个普通二级索引：

-- 使用CREATE INDEX创建普通索引
CREATE INDEX idx_user_email ON user(email);

-- 使用ALTER TABLE追加索引
ALTER TABLE user ADD INDEX idx_user_phone (phone);

-- 建表时直接声明索引
CREATE TABLE order_info (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  user_id BIGINT NOT NULL,
  amount DECIMAL(10,2),
  INDEX idx_user_id (user_id)
) ENGINE=InnoDB;

上述三种方式在功能上等价，但在大表上执行CREATE INDEX或ALTER TABLE可能会引起锁表与磁盘IO压力。InnoDB从5.6版本开始支持Online DDL，大多数索引创建操作不会长时间阻塞写入，但仍建议业务低峰期执行。

二、联合索引与最左前缀原则

当查询条件经常同时涉及多个字段时，联合索引比多个单列索引更高效。联合索引遵循最左前缀原则，即索引树按照定义顺序依次排序，只有从最左侧列开始匹配才能有效利用索引。

假设我们频繁执行“根据城市与年龄筛选用户”的查询，可以建立(city, age)的联合索引。下面代码展示了创建语句与两个查询的执行差异：

-- 创建联合索引
CREATE INDEX idx_city_age ON user(city, age);

-- 可以使用到联合索引的查询
SELECT * FROM user WHERE city = 'beijing' AND age = 30;

-- 无法充分利用联合索引（缺少最左列）
SELECT * FROM user WHERE age = 30;

在第二个查询中，由于跳过了city列，MySQL一般只能进行索引扫描或回表过滤，无法精准定位。因此在设计联合索引时，应把区分度高的字段放在左侧，并尽量覆盖常用查询的WHERE与ORDER BY组合。

三、使用EXPLAIN分析索引命中情况

创建索引后，必须验证其是否真正被查询使用。EXPLAIN命令可以展示执行计划，其中的type、key和rows字段直接反映索引利用程度。

下面我们通过EXPLAIN观察一条查询是否命中了前面建立的idx_city_age索引：

EXPLAIN SELECT * FROM user WHERE city = 'shanghai' AND age > 20;

如果结果中key列显示为idx_city_age，且type为range或ref，说明索引生效。若key为NULL，则可能是统计信息过期或索引选择性太低。此时可运行ANALYZE TABLE user;来更新基数统计，帮助优化器做出正确选择。

四、索引创建与优化实用建议

索引并非越多越好。每一个额外索引都会增加插入、更新和删除操作的开销，因为存储引擎必须同步维护多棵B+树。冗余索引与低选择性索引是常见的性能陷阱。

例如对(a, b)已经建立联合索引时，再单独建立(a)索引通常没有意义，因为前者已经能满足a的单列查询。此外，像性别这类只有少数取值的字段，建立单列索引往往不如组合到其他联合索引中。优化时可定期通过sys.schema_unused_indexes视图排查长期未使用的索引并清理。

综上，MySQL索引的创建需要结合业务查询模式、数据分布与写负载综合考量。正确使用CREATE INDEX与联合索引设计，并借助EXPLAIN持续验证，才能构建出既快又省的数据库结构。

MySQL索引创建索引优化修改时间：2026-08-07 14:51:31