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

更容易记住我

477人浏览 · 2026-05-30 16:39:44

更容易记住我 · 2026-05-30 16:39:44 发布

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

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

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

本质是排好序的数据结构（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 个高频场景

字段使用函数

-- 失效
WHERE DATE(create_time) = '2025-01-01'
-- 生效
WHERE create_time >= '2025-01-01' AND create_time < '2025-01-02'

隐式类型转换

-- phone是字符串，数字会失效
WHERE phone = 13800138000

模糊查询以 % 开头
```
WHERE name LIKE '%张三' -- 失效
```
使用 NOT IN / != / IS NOT NULL（有时会失效）
OR 连接无索引字段
联合索引不满足最左前缀
ORDER BY 字段混乱
MySQL 觉得全表扫描更快（数据少时）

六、查看索引是否生效：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，速度极快。

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

单表索引数量建议 3~5 个，越多写入越慢。
联合索引字段数量最多 3~4 个，字段越长，索引越大，速度越慢。
字符串索引不要对长字符串建全索引，使用前缀索引。
```
INDEX idx_email (email(10))
```
避免冗余索引：已有 (a,b,c)，不要再建 (a)、(a,b)。

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

必须用自增主键。
尽量使用覆盖索引。
**禁止 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;