sql的执行顺序和优化逻辑(经典实用)

SQL执行顺序的底层逻辑可分为逻辑处理流程和物理优化实现两个层面：

一、逻辑顺序：

1、From /Join 阶段
From 子句首先确定基表或子查询数据源，生成初始虚拟表T1
多表连接时按JOIN→ON顺序处理：先计算笛卡尔积，再通过ON条件过滤生成虚拟表T2
2、Where 过滤阶段
在连接后对虚拟表T1执行行级过滤（不可使用聚合函数），生成虚拟表T3
3、Group by 分组
对过滤后虚拟表T3，按指定列分组 → 计算聚合函数,输出每组单行记录,生成虚拟表T4
4、Having筛选
对分组结果进行二次过滤，可使用聚合函数，生成虚拟表T5
5、Select
选择最终输出列 → 计算表达式 → 定义列别名，生成虚拟表6；（窗口函数在此阶段计算但暂不排序）
6、Distinct 去重
对虚拟表T7去重，生成虚拟表T7
7、Union
UNION连接的两个SELECT查询语句，会重复执行步骤1~6，产生两个虚拟表T7，UNION会将这些记录合并到虚拟表T8中
8、Order by 排序
按指定列排序 → 可使用SELECT阶段定义的别名
窗口函数的排序实际在此阶段生效，生成虚拟表T9
9、Limit 分页
对T9进行截取，生成结果集。

但是MYSQL有个特性，在GROUP BY、 HAVING语句中，可以使用 SELECT 中设定的别名。这不是因为MYSQL中会提前执行SELECT，而是因为在GROUP BY这一步返回了游标，大家了解一下就好。

书写顺序：SELECT->DISTINCT -> FROM -> JOIN -> ON -> WHERE -> GROUP BY -> HAVING -> UNION -> ORDER BY ->LIMIT

逻辑执行顺序：FROM -> JOIN ON -> WHERE -> GROUP BY -> HAVING -> SELECT ->DISTINCT-> UNION -> ORDER BY ->LIMIT

二、实际实现（优化器）

1、‌谓词下推‌：WHERE条件可能被提前到数据扫描阶段
例.SELECT a.name FROM users a JOIN orders b ON a.id=b.user_id WHERE b.amount>100
逻辑顺序：join->where 优化器优化：先执行where后连接表
2、‌连接重排‌：根据表大小和索引动态调整JOIN顺序，优先选择行数少或过滤后结果集小的表‌作为驱动表，需有JOIN字段索引避免全表扫描
例如SELECT * FROM A JOIN B ON A.id=B.id中，如果B表更小，优化器会优先使用B表作为驱动表进行链接‌
3、‌子查询展开‌：将嵌套查询转换为JOIN操作
IN/EXISTS子查询可转为INNER JOIN或SEMI JOIN（半连接）

三、sql优化

1、FROM/JOIN阶段优化（最高优先级）
‌小表驱动原则‌：将数据量小的表作为驱动表，减少循环嵌套次数
‌连接顺序优化‌：多表连接时，优先连接过滤条件最多的表
‌分区裁剪‌：对分区表查询时，利用分区键条件提前过滤数据分区
‌物化视图‌：对频繁访问的复杂连接建立预计算视图
2、WHERE阶段优化
‌索引列优先‌：确保WHERE条件中的字段有适当索引
‌避免索引失效‌：
不在索引列上使用函数或计算（如YEAR(date_col)=2023）
‌范围查询优化‌：将范围条件放在WHERE子句末尾（如WHERE a=1 AND b>2 AND c=3优于WHERE b>2 AND a=1 AND c=3）
3、GROUP BY优化
‌索引利用‌：为GROUP BY字段创建合适索引
‌预聚合‌：对大数据量使用物化视图或预计算表
4、HAVING优化
‌前置过滤‌：尽可能在WHERE阶段完成过滤，减少HAVING处理的数据量
‌简化条件‌：避免在HAVING中使用复杂计算
5、SELECT阶段优化
‌列裁剪‌：只查询必要字段，避免SELECT *
‌覆盖索引‌：确保查询只需访问索引即可完成
‌避免重复计算‌：对复杂表达式考虑使用计算列或物化视图
6、DISTINCT优化
‌替代方案‌：考虑使用GROUP BY或EXISTS替代DISTINCT
‌索引优化‌：为DISTINCT字段建立合适索引
7、ORDER BY优化
‌索引排序‌：为排序字段建立合适索引
‌限制结果集‌：结合LIMIT使用避免全表排序
‌避免混合排序‌：同一查询中避免ASC和DESC混用
8、LIMIT/OFFSET优化（最低优先级）
‌深度分页优化‌：使用WHERE id>last_id LIMIT n替代LIMIT m,n