🗄️ 第07章 · 索引与性能优化

CREATE INDEX EXPLAIN 慢查询

索引与性能优化¶

这一章讲让 MySQL 查询变快的核心:索引。理解索引"是什么、怎么建、什么时候反而别建",再学会用 EXPLAIN 看清查询计划、定位慢查询。90% 的"数据库变慢"是索引缺失或建立不当造成的。

索引是什么¶

索引可以理解为书的目录。没有目录,你要从第一页翻到最后才能找到一个词;有了目录,直接跳到那页。数据库同理:没有索引,查一行要全表扫描;有索引,按 B+ 树快速定位。

代价:索引占用额外磁盘与内存;插入/更新/删除也要同步维护索引,所以写变慢一点。
取舍:读多写少的表适合多建索引;写极频繁的表别盲目堆索引。

常见索引类型一句话¶

类型	作用	说明
主键 `PRIMARY KEY`	唯一标识一行,自动建聚簇索引	每张表就一个
唯一索引 `UNIQUE`	保证某个字段值唯一	主键的一种
普通索引 `INDEX`	加速常见查询	可多个
联合索引 `(a,b,c)`	同时加速多列,遵循最左前缀	最左原则很重要
全文索引 `FULLTEXT`	全文搜索	如 `MATCH ... AGAINST`

创建 / 查看 / 删除索引¶

-- 显式创建普通索引
CREATE INDEX idx_name ON band (name);

-- 创建联合索引(查询常按这两列一起过滤/排序时)
CREATE INDEX idx_band_yr ON members (band_id, join_year);

-- 查看某表所有索引
SHOW INDEX FROM band;

-- 删除索引
DROP INDEX idx_name ON band;

主键建议 | 用自增整数作为主键(如 id BIGINT AUTO_INCREMENT)通常最优;用长字符串或 UUID 做主键会导致聚簇索引膨胀、页分裂,读写都不利。

什么时候该建索引(口诀:查询用到的 WHERE / JOIN / ORDER BY / GROUP BY)¶

-- WHERE 常用过滤列 → 建
SELECT * FROM members WHERE status = 'active';
-- 建: INDEX(status)

-- 连表 JOIN 的条件列 → 建(两边都建更稳)
SELECT * FROM band JOIN members ON band.id = members.band_id;
-- 建: band.id(主键已自带)、members.band_id

-- 排序/分组列的索引可避免 filesort
SELECT band_id, COUNT(*) FROM members GROUP BY band_id ORDER BY band_id;
-- 建: 联合索引(band_id, ...)

最左前缀原则(联合索引的坑)¶

联合索引 (a, b, c) 真正生效的组合是 a、a,b、a,b,c。查询必须从最左列开始:

WHERE a=? AND b=? ✅ 用上
WHERE b=? ❌ 用不上(跳过 a)
WHERE a=? AND c=? ⚠️ 只用上 a

只要你经常按 b 或 c 单独查询,就应单独建它们的索引,而不是只想着"放进联合索引"。

用 EXPLAIN 看清查询计划¶

EXPLAIN 让 MySQL 告诉你"它打算怎么执行这条查询":

EXPLAIN SELECT * FROM members WHERE band_id = 3 AND join_year > 2019;

要看的关键列	含义
`type`	访问类型,从好到差: `const > eq_ref > range > index > ALL`
`possible_keys`	可能用到的索引
`key`	实际用到的索引(关键!为空=没走索引)
`rows`	预估扫描行数
`Extra`	出现 `Using filesort`/`Using temporary` 往往要优化

判断标准 | key 为 NULL 或 type = ALL(全表扫描)通常要优化;type 最好到 range 或 const。用 Extra 里的 Using filesort / Using temporary 定位排序/分组慢。

定位慢查询:开启慢查询日志¶

-- 开启慢查询日志(超过阈值记录)
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 2;        -- 单位秒
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

再用命令看:

# 用 mysqldumpslow 汇总慢查询(需安装 mysql-client 完整包)
mysqldumpslow -s c -t 10 /var/log/mysql/slow.log

优化套路(按顺序)¶

先 EXPLAIN 看有没有走索引。
按 WHERE/JOIN/ORDER BY 列补索引。
避免 SELECT *,只取需要的列。
避免在索引列上做函数/运算/% 开头模糊查询 → 索引失效。
LIMIT 分页、避免大结果集。
ORDER BY/GROUP BY 尽量匹配索引(联合索引)。

注意 | 别为了"每个字段都加索引"而堆索引。写多的表,索引越多写越慢,还会增大磁盘与内存占用。索引要给"真正频繁查询的条件列"。