[深度]MySQL查询优化完全手册：索引原理、执行计划与分区实战

2026年4月11日作者 sayamber

阿里云推广

MySQL查询优化完全手册

数据库慢查询是后端性能问题最常见的元凶。本文从索引的B+树原理讲起，到EXPLAIN执行计划解读，再到分区表实战，系统梳理MySQL查询优化全链路。

一、索引底层：B+树数据结构

MySQL InnoDB的索引是B+树，理解B+树结构才能理解索引的所有规则。

# B+树特点:
# 1. 所有数据都在叶子节点
# 2. 叶子节点之间有双向链表连接
# 3. 非叶子节点只存索引键值,不存数据
#
# 示意图(简化):
#         [  50  |  100  ]
#        /         |          \
#  [10,20,30]  [60,70,80]  [110,120]
#  ↓→↓→↓→↓       ↓→↓→↓       ↓→↓  (叶子节点双向链表)
#
# 范围查询高效的原因:
# 找到起始位置后,沿链表顺序读取,不用多次回到根节点

二、主键索引 vs 二级索引：回表问题

-- 主键索引(聚簇索引): 叶子节点直接存完整数据行
SELECT * FROM users WHERE id = 100;  -- 直接找到数据

-- 二级索引: 叶子节点存 索引值+主键ID
-- 需要回表(再用主键查一次)才能拿到完整数据
SELECT * FROM users WHERE email = 'a@b.com';
-- 第一步: email索引找到 id=100
-- 第二步: 用id=100回主键索引拿完整数据

-- 覆盖索引: 避免回表的神技!
-- 只查索引包含的字段,不需要回表
SELECT id, email FROM users WHERE email = 'a@b.com';
-- 如果索引包含id和email,这一步不需要回表!

三、EXPLAIN执行计划完整解读

EXPLAIN SELECT o.id, u.name
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status = 'paid' AND o.created_at > '2026-01-01';

-- 重点关注字段:
-- type(访问类型,从好到坏):
--   const > ref > range > index > ALL
--   ALL = 全表扫描,必须优化!

-- key: 实际使用的索引
-- rows: 估计扫描行数(越小越好)
-- Extra中的关键词:
--   Using index        好! 覆盖索引
--   Using filesort     差! 需要额外排序
--   Using temporary    很差! 使用了临时表
--   Using where        正常,在server层过滤

四、索引设计规则与反模式

规则	说明	示例
最左前缀	复合索引按最左字段开始	索引(a,b,c)可用a、ab、abc查询
区分度高的字段放前面	区分度=唯一值/总行数	用户ID(高)比性别(低)更适合加索引
不在索引列做运算	函数/运算会让索引失效	WHERE year(created_at)=2026 索引失效
避免前缀模糊查询	LIKE ‘%abc’不走索引	改为LIKE ‘abc%’或全文索引
控制索引数量	索引越多写越慢	一张表控制在5-8个以内

-- 索引失效的常见陷阱
-- 1. 类型不匹配
SELECT * FROM users WHERE phone = 13800138000;  -- phone是varchar,传数字会隐式转换,索引失效!

-- 2. 使用了函数
WHERE DATE(created_at) = '2026-01-01'  -- 失效
WHERE created_at >= '2026-01-01' AND created_at < '2026-01-02'  -- 正确

-- 3. OR条件包含非索引列
WHERE id = 1 OR name = 'abc'  -- name没索引,全表扫描
-- 改成: WHERE id=1 UNION ALL SELECT * FROM t WHERE name='abc'

五、分区表：千万级数据的性能提升实战

-- 按月分区(时序数据常用)
CREATE TABLE orders (
    id         BIGINT NOT NULL AUTO_INCREMENT,
    user_id    INT NOT NULL,
    created_at DATETIME NOT NULL,
    amount     DECIMAL(10,2),
    PRIMARY KEY (id, created_at)  -- 分区键必须包含在主键中
) PARTITION BY RANGE (YEAR(created_at)*100 + MONTH(created_at)) (
    PARTITION p202601 VALUES LESS THAN (202602),
    PARTITION p202602 VALUES LESS THAN (202603),
    PARTITION p202603 VALUES LESS THAN (202604),
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

-- 按时间范围查询时,MySQL只扫描相关分区
EXPLAIN SELECT * FROM orders WHERE created_at BETWEEN '2026-01-01' AND '2026-01-31';
-- partitions: p202601  只扫描1月份那个分区!

-- 删除历史数据:直接删除分区,比DELETE快100倍
ALTER TABLE orders DROP PARTITION p202501;

总结：MySQL优化的核心链路：慢查询日志 → EXPLAIN分析 → 索引优化 → 覆盖索引避免回表 → 分区表处理大数据量。掌握B+树原理是理解所有索引规则的基础，建议每次写SQL都养成EXPLAIN的习惯。

发表评论取消回复