当前位置：首页 > news >正文

MySQL查询优化器底层原理解析：从逻辑优化到物理优化

news 来源：原创 2025/7/27 8:25:17

MySQL查询优化器底层原理解析：从逻辑优化到物理优化

引言

在数据库系统中，SQL语句的执行效率直接影响着整个应用的性能表现。一条普通的SQL执行前会经历五个关键阶段：SQL输入、语法分析、语义检查、SQL优化、SQL执行。其中，SQL优化又分为逻辑优化和物理优化两个步骤。本文将深入探讨MySQL查询优化器的底层工作原理，帮助开发者更透彻地理解SQL优化器的工作机制，为慢查询优化提供更多分析依据。

查询优化器架构概览

SQL执行流程中有一个核心模块叫做查询优化器，其主要功能是对SQL语法树进行逻辑优化和物理优化，最终生成执行计划交给执行引擎执行。查询优化器主要分为两部分：

逻辑优化：将SQL语法树中的谓词转化为逻辑代数操作符，把语法树转化为关系代数语法树，然后进行语义优化、子查询优化、裁减冗余操作、连接提取公共表达式等一系列优化，最后生成逻辑查询执行计划。
物理优化：在逻辑优化后继续对SQL语法树进行改造，对多表连接的顺序进行调整，使用代价估算器对单表扫描和多表连接顺序进行代价评估，选择代价最小的方案作为优化基础，最后生成物理查询执行计划。

逻辑查询优化详解

子查询优化

子查询在日常SQL编写中很常见，但执行效率往往较低。早期的MySQL对子查询采用嵌套执行方式，父查询每查询一行就执行一次子查询，效率极差。优化器对子查询进行了多种优化：

子查询转为连接操作：将子查询转变为连接操作，减少执行次数，并将子查询条件转化为父查询条件下推。

-- 原子查询
select * from a where col_1 in (select col_1 from b);
-- 转为内连接
select a.* from a inner join b on a.col_1 = b.col_1;

半连接(Semi-Join)：保留a表在b表存在匹配的记录，不关心b表有多少条匹配记录。MySQL提供了临时表和首次匹配策略两种实现方式。
派生表优化：将FROM子句中的子查询(派生表)上拉优化为内连接。

-- 派生表
select * from (select * from a where col_1 = 1) as t inner join b on t.col_1 = b.col_1;
-- 优化后
select * from a inner join b on a.col_1 = b.col_1 where a.col_1 = 1;

等价谓词重写

优化器会对多种谓词进行重写，使其能够利用索引提高效率：

LIKE重写：将LIKE模糊查询转为范围查询。

select * from a where col_1 like 'abc%';
-- 重写为
select * from a where col_1 >= 'abc' and col_1 <= 'abd';

BETWEEN-AND重写：转为简单的比较表达式。

select * from a where col_1 between 1 and 10;
-- 重写为
select * from a where col_1 >= 1 and col_1 <= 10;

IN/OR重写：将IN谓词转为OR表达式或ANY函数。

select * from a where col_1 in (1,2,3);
-- 重写为
select * from a where col_1 = 1 or col_1 = 2 or col_1 = 3;

条件化简

优化器会对查询条件进行多种化简操作：

条件下推：将过滤条件尽可能下推到基表扫描阶段。

select * from a,b where a.col_1=b.col_1 and a.col_2=2;
-- 优化后先扫描a表时带上a.col_2=2条件

HAVING并入WHERE：无GROUP BY时合并HAVING和WHERE条件。

select * from a where col_1>1 having col_2=3;
-- 优化为
select * from a where col_1>1 and col_2=3;

常量传递：通过等式传递优化条件。

select * from a where col_1=col_2 and col_2=2;
-- 优化为
select * from a where col_1=2 and col_2=2;

连接优化

外连接消除：当外连接的WHERE条件包含内表列的非空判断时，可优化为内连接。

select * from a left outer join b on a.col_1 = b.col_1 where b.col_1 is not null;
-- 优化为内连接
select * from a,b where a.col_1 = b.col_1;

嵌套连接消除：对于纯内连接的嵌套可以消除括号并交换连接顺序。

select * from a join (b join c on b.col_2=c.col_2) on a.col_1=b.col_1;
-- 优化为
select * from a join b on a.col_1=b.col_1 join c on b.col_2=c.col_2;

连接消除：主外键关系或多表连接列都有唯一索引时可消除中间表。

-- b.col_1是主键，a.col_1是外键
select a.*,c.* from a join b on a.col_1 = b.col_1 join c on b.col_1 = c.col_1;
-- 优化为
select a.*,c.* from a join c on a.col_1 = c.col_1;

分组排序优化

GROUP BY优化：根据代价评估选择分组操作下移或上移。
ORDER BY优化：利用索引排序消除排序操作，或下推排序到基表。
DISTINCT优化：利用索引去重或将去重操作迁移到子查询。
LIMIT优化：与ORDER BY、DISTINCT等结合时的特殊优化策略。

物理查询优化详解

代价估算模型

物理优化的核心是代价估算模型，其公式为：

总代价 = IO代价 + CPU代价
COST = P*a_page_cpu_time + W*T

其中：

P：访问的页面数
a_page_cpu_time：读取每个页面的时间
W：选择率(满足条件的记录数/总记录数)
T：访问的记录数

单表扫描算法

顺序扫描：直接读取表中的数据，适合小数据量。
索引扫描：通过索引定位记录，选择率<0.1时效果较好。
只读索引扫描：覆盖索引避免回表查询。
行扫描：通过特殊列直接定位行数据。
并行扫描：并行顺序或索引扫描。
组合索引扫描：对多条件查询进行多次索引扫描。

两表连接算法

嵌套循环连接：
- 基本嵌套循环：效率差，基本被淘汰
- 基于索引的嵌套循环：内表连接列有索引
- BNL算法：通过join buffer减少内表扫描次数
- BKA算法：结合MRR技术解决随机IO问题
排序归并连接：对已排序的表进行归并连接，适合大数据量。
Hash连接：用连接列作为hash键，适合等值连接且内表数据量适中。

多表连接算法

多表连接顺序组合随表数N呈阶乘增长(N!)。MySQL采用两种算法：

动态规划算法：PostgreSQL采用，穷举所有可能选择最优解。
- 树叶层：评估单表扫描代价
- 第二层：两表连接评估
- 第三层：三表连接评估
- 依次类推直到解决整个问题
贪婪算法：MySQL采用，每次选择局部最优解，不一定全局最优但效率高。

总结

MySQL查询优化器通过逻辑优化和物理优化两个阶段，对SQL查询进行全方位优化：

逻辑优化：通过子查询优化、等价谓词重写、条件化简、连接优化等手段，重写查询逻辑使其更高效。
物理优化：通过代价模型评估，选择最优的单表扫描方式、两表连接算法和多表连接顺序。

理解这些底层原理，可以帮助开发者在编写SQL时更有针对性，也能更有效地分析和优化慢查询。正如《阿里巴巴编码规范》建议的，多表连接时应控制表数量在3个以内，因为连接顺序组合会随表数呈阶乘增长，导致优化器评估代价急剧增加。