查看学习笔记
ARTICLE

MySQL 数据库索引优化笔记

从索引原理到EXPLAIN分析,让慢查询从秒级降到毫秒级。

MySQL 数据库索引优化笔记

MySQL 数据库索引优化笔记

数据库慢查询是后端开发最常踩的坑之一。明明SQL写得没问题,表数据量也不大,查询却卡在2秒、5秒甚至更久。绝大多数情况下,问题都出在索引上。本文从索引原理出发,结合真实场景讲解如何用 EXPLAIN 定位问题并优化。

一、索引的本质:B+树

InnoDB 引擎的索引采用 B+ 树结构。和二叉树不同,B+ 树每个节点可以有多个子节点,整棵树矮胖,3~4 层就能存储千万级数据。这意味着一次查询只需要 3~4 次磁盘IO即可定位到记录。

没有索引时,MySQL只能从第一行扫到最后一行(全表扫描);有了索引,相当于字典有目录,可以快速翻到目标页。

二、常见索引类型

  • 主键索引(聚簇索引):叶子节点直接存储整行数据,一张表只能有一个。
  • 普通索引(二级索引):叶子节点存储主键值,需要"回表"查询完整数据。
  • 唯一索引:值唯一,可为 NULL。
  • 联合索引:多列组合,遵循最左前缀原则。
  • 覆盖索引:查询字段都在索引中,无需回表。
  • 全文索引:用于文本检索,一般用 Elasticsearch 替代。

三、创建索引的正确姿势

-- 创建表时声明
CREATE TABLE users (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  email VARCHAR(128) NOT NULL,
  phone VARCHAR(20),
  status TINYINT,
  created_at DATETIME,
  UNIQUE KEY uk_email (email),
  KEY idx_phone (phone),
  KEY idx_status_created (status, created_at)
);

-- 已有表添加索引
ALTER TABLE users ADD INDEX idx_status_created (status, created_at);

联合索引 (status, created_at) 的顺序很关键。最左前缀原则意味着可以用于 WHERE status=? 或 WHERE status=? AND created_at>?,但不能用于 WHERE created_at>?。

四、EXPLAIN:执行计划分析

在任何 SQL 前加 EXPLAIN 就能看到 MySQL 的执行计划。重点关注以下字段:

EXPLAIN SELECT * FROM users
WHERE status = 1 AND created_at > '2026-01-01';
  • type:访问类型,从好到差:system > const > eq_ref > ref > range > index > ALL。出现 ALL 表示全表扫描,必须优化。
  • key:实际使用的索引。NULL 表示没用索引。
  • rows:预估扫描行数,越少越好。
  • Extra:额外信息。Using index 表示覆盖索引;Using filesort 表示需要额外排序;Using temporary 表示用了临时表,都要警惕。

五、慢查询优化示例

示例1:函数导致索引失效

-- 索引失效,全表扫描
SELECT * FROM users WHERE DATE(created_at) = '2026-07-22';

-- 改写为范围查询,命中索引
SELECT * FROM users
WHERE created_at >= '2026-07-22 00:00:00'
  AND created_at < '2026-07-23 00:00:00';

对索引列使用函数、计算、类型转换都会让索引失效。这是最常见的性能杀手。

示例2:LIKE 左模糊

-- 索引失效
SELECT * FROM users WHERE name LIKE '%张';

-- 可以走索引
SELECT * FROM users WHERE name LIKE '张%';

示例3:隐式类型转换

-- phone 是 VARCHAR,传入数字导致隐式转换,索引失效
SELECT * FROM users WHERE phone = 13800138000;

-- 正确写法:加引号
SELECT * FROM users WHERE phone = '13800138000';

示例4:分页深度问题

-- LIMIT 越深越慢,需扫描前面所有行
SELECT * FROM orders ORDER BY id LIMIT 1000000, 20;

-- 用主键定位 + JOIN 优化
SELECT o.* FROM orders o
JOIN (SELECT id FROM orders ORDER BY id LIMIT 1000000, 20) t
ON o.id = t.id;

六、索引的代价

索引不是越多越好。每个索引都是一棵独立的 B+ 树,会带来三方面成本:

  1. 磁盘空间:索引本身占用存储。
  2. 写入性能:INSERT/UPDATE/DELETE 需要同步维护索引。
  3. 优化器选择成本:索引多了,优化器选错索引的概率也会上升。

建议单表索引数量控制在 5 个以内,联合索引列数不超过 5 列。

七、慢查询日志的开启与分析

-- my.cnf 配置
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1

-- 分析慢日志
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

线上务必开启慢查询日志,定期用 pt-query-digest 或 mysqldumpslow 分析TOP N 慢SQL,按影响范围排优先级逐条优化。

八、小结

索引优化是数据库性能调优的核心。核心心法只有三句话:先看 EXPLAIN 是否走索引、再看 rows 与 Extra 是否合理、最后审查 SQL 是否触发了索引失效的常见陷阱。把这些套路内化之后,绝大多数慢查询都能在不增加硬件成本的前提下提升一个数量级。

返回列表