SQL 执行全流程深度解析-优快云博客

本文链接：https://blog.youkuaiyun.com/csdn_tom_168/article/details/148946987

SQL 执行全流程深度解析

一条 SQL 的执行是数据库系统的核心操作，其过程可划分为多个精密协作的阶段。下面我们将深入剖析 SQL 从提交到结果返回的全过程，并重点解析查询优化器的核心原理。

一、SQL 执行全流程

1. 语法解析（Parsing）

词法分析：将 SQL 字符串拆分为有意义的 token
```
SELECT name FROM users WHERE age > 25;
```
被拆解为：[SELECT], [name], [FROM], [users], [WHERE], [age], [>], [25]
语法分析：构建抽象语法树（AST）

2. 语义分析（Semantic Analysis）

验证对象存在性（表/列是否存在）
检查权限（用户是否有操作权限）
类型校验（WHERE age > ‘25’ 会报类型错误）
绑定元数据（关联表结构信息）

3. 查询重写（Query Rewriting）

视图展开：将视图替换为底层查询

子查询优化：

-- 重写前
SELECT * FROM orders 
WHERE customer_id IN (SELECT id FROM customers WHERE country = 'US');

-- 重写为JOIN
SELECT orders.* FROM orders
JOIN customers ON orders.customer_id = customers.id
WHERE customers.country = 'US';

谓词下推：将过滤条件提前
常量折叠：WHERE 1=1 AND age>25 → WHERE age>25

4. 查询优化（Query Optimization）

(1) 逻辑优化

关系代数等价变换

(2) 物理优化（核心阶段）

5. 执行计划生成（Execution Plan）

EXPLAIN SELECT name FROM users WHERE age > 25;

-- 输出示例
| Id | Operation          | Table | Rows |
|----|--------------------|-------|------|
| 1  | INDEX SCAN(age_idx)| users | 200  |

6. 执行引擎处理

火山模型（Volcano Model）：迭代式拉取数据

def execute_plan(plan):
    while (row := plan.next()) is not None:
        yield row

向量化引擎：批量处理数据提升CPU利用率

7. 结果返回

结果集封装（JDBC/ODBC等协议）
缓存管理（结果集缓存）

二、查询优化器核心原理

1. 代价模型（Cost Model）

代价公式示例：

Total Cost = 
  (Pages Read * seq_page_cost) +
  (Rows Processed * cpu_tuple_cost) +
  (Predicate Evaluations * cpu_operator_cost)

2. 基数估算（Cardinality Estimation）

直方图统计：存储数据分布

age分布直方图：
[18-25] : 1000行
[26-35] : 2500行
[36-45] : 1500行

选择率计算：

WHERE age > 25：
  总行数 = 5000
  满足条件行数 = 2500 + 1500 = 4000
  选择率 = 4000/5000 = 0.8

3. 连接顺序优化

动态规划算法（System R 风格）

def find_best_join_order(tables):
    for i in range(1, len(tables)+1):
        for subset in combinations(tables, i):
            for left in subsets(subset):
                right = subset - left
                cost = calculate_join_cost(left, right)
                update_best_plan(subset, cost)
    return best_plan[all_tables]

贪心算法（快速近似）
遗传算法（超多表连接优化）

4. 索引选择策略

graph TD
    A[可用索引] --> B{覆盖查询？}
    B -->|是| C[使用索引]
    B -->|否| D{高选择率？}
    D -->|是| E[索引扫描+回表]
    D -->|否| F[全表扫描]

5. 并行执行优化

分区并行：将数据分片处理

-- PostgreSQL并行查询
Gather (cost=1000.00..78247.67 rows=1 width=4)
  Workers Planned: 4
  -> Parallel Seq Scan on orders

流水线并行：操作间并行执行

三、高级优化技术

1. 物化视图重写

-- 原始查询
SELECT product, SUM(sales) 
FROM orders 
WHERE year = 2023 
GROUP BY product;

-- 自动重写为
SELECT product, sum_sales 
FROM sales_summary_mv  -- 预计算物化视图
WHERE year = 2023;

2. 自适应查询执行

graph LR
    A[执行计划] --> B[运行时统计]
    B --> C{性能偏差？}
    C -->|是| D[动态调整计划]
    C -->|否| E[继续执行]

3. 机器学习优化

使用历史执行数据训练代价模型
LSTM预测查询延迟
强化学习选择连接顺序

四、各数据库优化器对比

优化器特性	MySQL	PostgreSQL	Oracle
优化架构	基于规则+代价模型	纯代价模型	基于代价+规则
连接排序算法	贪心算法	动态规划+遗传算法	动态规划
直方图统计	支持	高级统计	多维度统计
并行查询	有限支持	强支持	企业级支持
实时优化	无	JIT编译	自适应执行计划

五、SQL 执行示例分析

查询语句：

SELECT o.order_id, c.name 
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.total > 1000 AND c.country = 'US'
ORDER BY o.order_date DESC
LIMIT 10;

优化过程：

谓词下推：

-- 将过滤提前
SELECT ... 
FROM (SELECT * FROM orders WHERE total > 1000) o
JOIN (SELECT * FROM customers WHERE country = 'US') c

索引选择：
- orders.total_idx 用于 total > 1000
- customers.country_idx 用于国家过滤
连接优化：
- 小表驱动大表（customers → orders）
- 选择Hash Join（无索引时）
排序优化：
- 使用 order_date 索引避免全排序
- LIMIT 10 减少处理量

最终执行计划：

Limit (cost=157.29 rows=10)
  -> Nested Loop
      -> Index Scan Backward using order_date_idx on orders
          Filter: (total > 1000)
      -> Index Scan using customers_pkey on customers
          Index Cond: (id = orders.customer_id)
          Filter: (country = 'US')