MySQL索引使用

一、为什么要用索引？—— 先讲个血泪故事

想象你去图书馆找一本《MySQL从入门到入土》：

没有索引的情况（全表扫描）：

你从第一排书架开始，一本一本翻，看到《西游记》…《三体》…《Java编程思想》… 翻了3个小时，终于在第5000本书里找到了。这时候你已经想"从入门到放弃"了。

有索引的情况：

你去电脑查一下，系统告诉你"在第3区第5架第2层"，你2分钟就拿到的书，还能顺便借本《Redis深度历险》。

数据库也是这个道理。 没有索引，MySQL就要一行一行地"翻书"；有了索引，直接"导航定位"。

真实数据说话： 假设你有100万条用户数据，查 WHERE phone = '13800138000'：

索引的本质：用空间换时间，用写性能换读性能。

Tip: 在数据量小的时候，尽量不要使用索引

二、索引的原理——B+树到底是个啥？

别被"B+树"这个名字吓到，它其实就是个"很会做排序的多叉树"。

2.1 为什么不用其他结构？

2.2 B+树长什么样？（简化版）

[10 | 30 | 50] ← 根节点（只存键值，不存数据）
/ | \\
[5|10] [20|30] [40|50|60] ← 非叶子节点（还是只存键值）
/ \\ / \\ / \\ \\
[1,2,3,4,5] [10,11] [20,25] [30,35] [40,45] [50,55] [60,65] ← 叶子节点（存真实数据/指针）

所有叶子节点用链表相连：1→2→3→4→5→10→11→20→25→30→35…

B+树的三大杀手锏：

2.3 聚簇索引 vs 非聚簇索引（重点！）

聚簇索引（Clustered Index）—— 数据本身：

• 叶子节点存的就是完整的行数据
• InnoDB表必须有，且只有一个
• 默认主键就是聚簇索引；没主键就用第一个唯一索引；再没有就隐式生成6字节的row_id

聚簇索引查找：
[找主键10] → 直接定位到叶子节点 → 拿到完整数据（id=10, name='张三', age=20…）

非聚簇索引（Secondary Index）—— 数据的"快递单号"：

• 叶子节点存的是索引列 + 主键值
• 查到后还要拿主键去聚簇索引查一次完整数据（叫"回表"）

非聚簇索引查找：
[找name='张三'] → 叶子节点拿到(id=10) → 再去聚簇索引查id=10的完整数据

对比：

聚簇索引（主键id） 非聚簇索引（name列）
[1] ['Alice'] → id=1
/ \\ ['Bob'] → id=2
[1] [2] ['Carol'] → id=3
/ \\
数据行1 数据行2 查到'Bob'后，拿id=2去聚簇索引找完整数据（回表）

三、索引的用法——实战指南

3.1 索引类型全家福

— 1. 主键索引（自动创建，聚簇索引）
CREATE TABLE user (
id INT PRIMARY KEY AUTO_INCREMENT, — 这就是主键索引
name VARCHAR(50),
phone VARCHAR(20)
);

— 2. 唯一索引（值不能重复，允许NULL）
CREATE UNIQUE INDEX uk_phone ON user(phone);

— 3. 普通索引（最常用）
CREATE INDEX idx_name ON user(name);

— 4. 组合索引（多列联合，最左前缀原则！）
CREATE INDEX idx_name_age ON user(name, age);

— 5. 全文索引（MySQL 5.6+，用于文本搜索）
CREATE FULLTEXT INDEX idx_content ON article(content);

— 6. 前缀索引（省空间，用于长字符串）
CREATE INDEX idx_email ON user(email(10)); — 只索引前10个字符

3.2 组合索引的最左前缀原则（面试必问！）

创建 INDEX idx_a_b_c (a, b, c)，相当于建了3个索引：

• (a)
• (a, b)
• (a, b, c)

能用上索引的查询：

WHERE a = 1 — ✓ 用到了idx_a_b_c的a部分
WHERE a = 1 AND b = 2 — ✓ 用到了a和b
WHERE a = 1 AND b = 2 AND c = 3 — ✓ 完美，全用上
WHERE a = 1 AND c = 3 — ✓ 只用到了a（c跳过了b，断了）

用不上索引的查询（踩坑预警）：

WHERE b = 2 — ✗ 没a，最左缺失
WHERE b = 2 AND c = 3 — ✗ 没a
WHERE a = 1 OR b = 2 — ✗ OR导致索引失效（除非两边都有索引）
WHERE a LIKE '%xxx' — ✗ 前导模糊，索引失效

记忆口诀：最左优先，中间不断，范围停步。

3.3 索引下推（Index Condition Pushdown, ICP）

MySQL 5.6+的优化，在存储引擎层就过滤数据，减少回表。

— 有索引 idx_name_age(name, age)
SELECT * FROM user WHERE name LIKE '张%' AND age = 20;

— 老版本：先找到所有姓张的，回表查age，再过滤
— 5.6+：在索引里就直接判断age=20，只回表符合条件的数据

3.4 覆盖索引（Covering Index）—— 不回表的神技

如果查询的列都在索引里，直接返回，不用回表查聚簇索引。

— 有索引 idx_name_age(name, age)
SELECT name, age FROM user WHERE name = '张三';
— ✓ 覆盖索引！索引里就有name和age，直接返回，速度飞起

SELECT * FROM user WHERE name = '张三';
— ✗ 需要回表，因为索引里没有其他列（如phone、address等）

设计技巧： 经常一起查的字段，考虑建组合索引或加入索引。

四、提升效率——索引优化实战

4.1 EXPLAIN命令——索引优化的"体检报告"

EXPLAIN SELECT * FROM user WHERE phone = '13800138000';

关键字段解读：

type性能排序（从好到坏）：

system > const > eq_ref > ref > range > index > ALL
↓ ↓ ↓ ↓ ↓ ↓ ↓
最快 主键/唯一 联表主键 普通索引 范围扫描 索引扫描 全表扫描

4.2 索引设计的"三要三不要"

三要：

1.要建在WHERE、JOIN、ORDER BY、GROUP BY的列上

— 经常这样查？
SELECT * FROM order WHERE user_id = 100 AND status = 1 ORDER BY create_time;
— 考虑：INDEX idx_user_status_time(user_id, status, create_time)

2.要高选择性的列放前面

— 性别（只有男女）选择性低，放后面
— 手机号（几乎唯一）选择性高，放前面
CREATE INDEX idx_phone_gender ON user(phone, gender); — ✓ 好
CREATE INDEX idx_gender_phone ON user(gender, phone); — ✗ 差，gender区分度太低

3.要利用覆盖索引减少回表

— 如果经常只查name和email
CREATE INDEX idx_name_email ON user(name, email);
SELECT name, email FROM user WHERE name = 'xxx'; — 覆盖索引，不回表

三不要：

1.不要在低选择性列上建单列索引

— 性别字段只有0和1，建索引后MySQL可能直接全表扫描
SELECT * FROM user WHERE gender = 1; — 可能走可能不走，看数据分布

2.不要对索引列做函数或运算

WHERE YEAR(create_time) = 2023 — ✗ 函数导致索引失效
WHERE create_time >= '2023-01-01' AND create_time < '2024-01-01' — ✓ 范围查询

WHERE id + 1 = 100 — ✗ 运算导致失效
WHERE id = 99 — ✓ 直接比较

3.不要建太多索引（写操作会哭）

• • 每个索引都是一棵B+树，插入/更新/删除时要维护所有索引
  • 建议：单表索引不超过5个，组合索引列不超过5个

4.3 索引失效的常见坑（排雷手册）

— 1. 前导模糊查询
WHERE name LIKE '%张%' — ✗ 失效
WHERE name LIKE '张%' — ✓ 有效（用到索引的name部分）

— 2. 隐式类型转换
WHERE phone = 13800138000 — ✗ phone是字符串，数字会转换，索引失效
WHERE phone = '13800138000' — ✓ 正确

— 3. 不等于、NOT IN（可能失效，看数据分布）
WHERE status != 0 — 数据量大时可能全表扫描

— 4. IS NULL vs IS NOT NULL（看列是否允许NULL）
— 如果列NOT NULL，IS NULL直接返回空，很快
— 如果列允许NULL，IS NOT NULL可能扫描大量数据

— 5. OR条件（两边都要有索引）
WHERE id = 1 OR name = '张三'
— 如果只有id有索引，name没索引，可能全表扫描
— 解决：分别查询UNION，或给name也建索引

4.4 大表优化策略

场景：千万级用户表，查询慢

— 慢：OFFSET越大越慢，需要排序后跳过前面1000000条
SELECT * FROM user ORDER BY id LIMIT 1000000, 10;

— 快：先查id，再JOIN（利用覆盖索引）
SELECT * FROM user u
JOIN (SELECT id FROM user ORDER BY id LIMIT 1000000, 10) tmp ON u.id = tmp.id;

— 更快：记录上次位置（游标分页）
SELECT * FROM user WHERE id > 上次最大id ORDER BY id LIMIT 10;

— 按时间分区，查询只扫相关分区
CREATE TABLE log (
id INT,
create_time DATETIME
) PARTITION BY RANGE (YEAR(create_time)) (
PARTITION p2022 VALUES LESS THAN (2023),
PARTITION p2023 VALUES LESS THAN (2024),
PARTITION p2024 VALUES LESS THAN MAXVALUE
);

• • 热数据（最近3个月）放主库，有索引，快速查询
  • 冷数据归档到历史库，甚至可以去掉部分索引省空间

五、总结：索引使用

□ 查询是否用了索引？（EXPLAIN看key字段）
□ 是否避免了全表扫描？（type不是ALL）
□ 组合索引是否遵循最左前缀？
□ 是否利用了覆盖索引减少回表？
□ 索引列是否做了函数/运算/隐式转换？
□ 前导模糊查询是否必须？能否用全文索引？
□ 分页是否太深？是否需要优化？
□ 写性能是否可接受？（索引别太多）

最后一句忠告： 索引不是银弹，它是读性能的加速器，写性能的减速带。设计时平衡读写比例，监控慢查询日志，定期用OPTIMIZE TABLE整理碎片，才能让MySQL跑得又快又稳。