MySQL 索引优化详细指南（实战版）

一、先搞懂：索引是什么？

索引 = 数据库的 “目录”。

没有索引：全表扫描（逐行找）。
有索引：快速定位（像查字典）。

本质是排好序的数据结构（B+树），能大幅提升查询速度，但会减慢写入（增删改），占用额外空间。

二、最核心：哪些字段必须建索引？

1. 必建索引的场景

• WHERE 条件里频繁使用的字段
• JOIN 关联字段（左右表都要建）
• ORDER BY / GROUP BY 字段
• DISTINCT 字段
• 覆盖查询字段（查询只走索引，不回表）

2. 绝对不要建索引的场景

• 区分度极低的字段（性别、状态 0/1）
• 频繁修改的字段
• 小表（数据 < 1000 行）
• 模糊查询 %xxx 开头的字段
• 函数 / 表达式计算后的字段（无法命中索引）

三、最实用：索引类型怎么选？

1. 主键索引（PRIMARY KEY）

• 每张表必须有。
• 建议用自增 ID / 雪花 ID（避免页分裂）。
• 非空 + 唯一。

2. 唯一索引（UNIQUE）

• 字段值不重复（手机号、身份证、订单号）。
• 比普通索引更快。

3. 普通索引（INDEX）

• 最常用，加速查询。
• 无唯一性限制。

4. 联合索引（最左前缀原则）

核心口诀：查询条件越多，索引越要组合。

规则：

• 等值条件放前面。
• 范围条件放最后。
• 区分度高的放前面。

示例：

INDEX idx_name_age_gender (name, age, gender)

能命中：

• name
• name + age
• name + age + gender

不能命中：

• age
• gender
• age + gender

四、联合索引设计黄金法则

公式：

等值匹配 + 排序字段 + 范围查询

最优示例：

WHERE a=? AND b=? ORDER BY c LIMIT 10

索引应该建：

(a, b, c)

错误示范：

WHERE a>10 ORDER BY b

索引 (a,b) 无法优化排序，因为 a 是范围。

五、SQL 编写规范（决定索引是否生效）

索引失效的 8 个高频场景

六、查看索引是否生效：EXPLAIN

最简单的优化工具，必学。

使用方法：

EXPLAIN SELECT * FROM user WHERE name='张三';

重点看 3 列：

• type：最优 → 最差
  system > const > eq_ref > ref > range > index > ALL
  达到 range 以上才算合格。
• key：实际使用的索引（NULL = 未命中）。
• Extra：
  • Using index：覆盖索引（性能极好）。
  • Using where; Using index：最优。
  • Using filesort：文件排序（必须优化）。
  • Using temporary：临时表（必须优化）。

七、高级优化：覆盖索引（性能爆炸）

定义：查询的字段 正好都在索引里，不需要回表查原数据。

示例：

SELECT name, age FROM user WHERE name='张三';

索引：

INDEX idx_name_age (name, age)

Extra 会显示 Using index，速度极快。

八、索引设计最佳实践（直接照抄）

九、千万级数据表优化方案

• 必须用自增主键。
• 尽量使用覆盖索引。
• **禁止 SELECT ***。
• LIMIT 分页优化WHERE id > 10000 LIMIT 100
• 大字段拆分到单独表（text、blob）。
• 时间范围查询优先用覆盖索引。

十、索引创建规范模板（用户表示例）

CREATE TABLE `user` (
`id` bigint NOT NULL AUTO_INCREMENT,
`name` varchar(20) NOT NULL DEFAULT '',
`phone` varchar(11) NOT NULL DEFAULT '',
`age` tinyint NOT NULL DEFAULT '0',
`create_time` datetime NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uk_phone` (`phone`),
KEY `idx_name_age` (`name`,`age`),
KEY `idx_create_time` (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

十一、索引维护（防止变慢）

• 定期删除无用索引。
• 避免频繁 ALTER TABLE。
• 大表加索引用 pt-online-schema-change。
• 定期分析表：ANALYZE TABLE 表名。

总结（最核心的 5 条）