SQL层 #
一、SQL层概述 #
1.1 SQL层架构 #
text
SQL 层架构
┌─────────────────────────────────────────────────────────────┐
│ │
│ 客户端连接 │
│ │ │
│ ▼ │
│ ┌─────────────────────────────────────────────────────┐ │
│ │ PostgreSQL 协议层 │ │
│ │ 连接管理 / 协议解析 / 认证授权 │ │
│ └─────────────────────────────────────────────────────┘ │
│ │ │
│ ▼ │
│ ┌─────────────────────────────────────────────────────┐ │
│ │ SQL 解析器 │ │
│ │ 词法分析 → 语法分析 → AST │ │
│ └─────────────────────────────────────────────────────┘ │
│ │ │
│ ▼ │
│ ┌─────────────────────────────────────────────────────┐ │
│ │ 查询优化器 │ │
│ │ 逻辑优化 → 物理优化 → 执行计划 │ │
│ └─────────────────────────────────────────────────────┘ │
│ │ │
│ ▼ │
│ ┌─────────────────────────────────────────────────────┐ │
│ │ 执行引擎 │ │
│ │ 本地执行 / 分布式执行 │ │
│ └─────────────────────────────────────────────────────┘ │
│ │ │
│ ▼ │
│ 事务层 / 分布式层 │
│ │
└─────────────────────────────────────────────────────────────┘
1.2 核心组件 #
| 组件 | 职责 |
|---|---|
| 协议层 | PostgreSQL协议兼容 |
| 解析器 | SQL语法解析 |
| 优化器 | 查询优化 |
| 执行引擎 | 查询执行 |
二、PostgreSQL协议兼容 #
2.1 协议支持 #
text
PostgreSQL 协议支持
┌─────────────────────────────────────────────────────────────┐
│ │
│ 支持的功能: │
│ ├── 连接认证 │
│ ├── 简单查询 │
│ ├── 扩展查询 │
│ ├── 预处理语句 │
│ ├── 绑定参数 │
│ └── 结果集获取 │
│ │
│ 客户端兼容: │
│ ├── psql │
│ ├── pgAdmin │
│ ├── psycopg2 (Python) │
│ ├── pgx (Go) │
│ ├── node-postgres (Node.js) │
│ ├── JDBC (Java) │
│ └── 大多数 PostgreSQL 客户端 │
│ │
└─────────────────────────────────────────────────────────────┘
2.2 连接示例 #
bash
# 使用 psql 连接
psql -h localhost -p 26257 -U root -d defaultdb
# 使用连接字符串
psql "postgresql://root@localhost:26257/defaultdb?sslmode=disable"
python
# Python psycopg2
import psycopg2
conn = psycopg2.connect(
host='localhost',
port=26257,
user='root',
database='defaultdb'
)
go
// Go pgx
package main
import (
"context"
"github.com/jackc/pgx/v5"
)
func main() {
conn, _ := pgx.Connect(context.Background(),
"postgresql://root@localhost:26257/defaultdb?sslmode=disable")
defer conn.Close(context.Background())
}
三、SQL解析器 #
3.1 解析流程 #
text
SQL 解析流程
┌─────────────────────────────────────────────────────────────┐
│ │
│ 输入: SELECT * FROM users WHERE id = 1 │
│ │
│ ┌─────────────────────────────────────────────────────┐ │
│ │ 词法分析 (Lexer) │ │
│ │ │ │
│ │ SELECT → 关键字 │ │
│ │ * → 标识符 │ │
│ │ FROM → 关键字 │ │
│ │ users → 标识符 │ │
│ │ WHERE → 关键字 │ │
│ │ id → 标识符 │ │
│ │ = → 操作符 │ │
│ │ 1 → 字面量 │ │
│ └─────────────────────────────────────────────────────┘ │
│ │ │
│ ▼ │
│ ┌─────────────────────────────────────────────────────┐ │
│ │ 语法分析 (Parser) │ │
│ │ │ │
│ │ 生成抽象语法树 (AST): │ │
│ │ │ │
│ │ SelectStmt │ │
│ │ │ │ │
│ │ ┌─────┼─────┐ │ │
│ │ │ │ │ │ │
│ │ Fields From Where │ │
│ │ │ │ │ │ │
│ │ * users Comparison │ │
│ │ │ │ │
│ │ ┌────┴────┐ │ │
│ │ │ │ │ │
│ │ id = 1 │ │
│ └─────────────────────────────────────────────────────┘ │
│ │
└─────────────────────────────────────────────────────────────┘
3.2 支持的SQL语法 #
sql
-- DDL
CREATE TABLE users (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
name VARCHAR(100) NOT NULL,
email VARCHAR(255) UNIQUE,
created_at TIMESTAMP DEFAULT NOW()
);
ALTER TABLE users ADD COLUMN age INT;
DROP TABLE users;
-- DML
INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');
UPDATE users SET name = 'Bob' WHERE id = '...';
DELETE FROM users WHERE id = '...';
-- DQL
SELECT * FROM users WHERE name = 'Alice';
SELECT u.name, COUNT(o.id) FROM users u JOIN orders o ON u.id = o.user_id GROUP BY u.name;
-- DCL
CREATE USER admin WITH PASSWORD 'password';
GRANT ALL ON DATABASE mydb TO admin;
四、查询优化器 #
4.1 优化流程 #
text
查询优化流程
┌─────────────────────────────────────────────────────────────┐
│ │
│ 输入: AST │
│ │ │
│ ▼ │
│ ┌─────────────────────────────────────────────────────┐ │
│ │ 逻辑优化 (Logical Optimization) │ │
│ │ │ │
│ │ ├── 谓词下推 (Predicate Pushdown) │ │
│ │ ├── 投影下推 (Projection Pushdown) │ │
│ │ ├── 常量折叠 (Constant Folding) │ │
│ │ ├── 子查询展开 (Subquery Unnesting) │ │
│ │ └── JOIN 重排序 (Join Reordering) │ │
│ └─────────────────────────────────────────────────────┘ │
│ │ │
│ ▼ │
│ ┌─────────────────────────────────────────────────────┐ │
│ │ 物理优化 (Physical Optimization) │ │
│ │ │ │
│ │ ├── 索引选择 (Index Selection) │ │
│ │ ├── JOIN 策略选择 (Join Strategy) │ │
│ │ ├── 聚合策略选择 (Aggregation Strategy) │ │
│ │ └── 分布策略选择 (Distribution Strategy) │ │
│ └─────────────────────────────────────────────────────┘ │
│ │ │
│ ▼ │
│ 输出: 执行计划 │
│ │
└─────────────────────────────────────────────────────────────┘
4.2 查看执行计划 #
sql
-- 基本执行计划
EXPLAIN SELECT * FROM users WHERE id = 1;
-- 详细执行计划
EXPLAIN ANALYZE SELECT * FROM users WHERE id = 1;
-- 执行计划示例
EXPLAIN SELECT u.name, o.order_no
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.id = 1;
-- 输出示例:
-- │
-- • hash join │
-- │ │
-- ├── • scan │
-- │ table: users@primary │
-- │ spans: [1-1] │
-- │ │
-- └── • scan │
-- table: orders@idx_user_id │
-- spans: [1-1] │
4.3 优化提示 #
sql
-- 强制使用索引
SELECT * FROM users@idx_email WHERE email = 'test@example.com';
-- 强制使用特定索引
SELECT /*+ Index(users idx_email) */ * FROM users WHERE email = 'test@example.com';
-- 禁用索引
SELECT /*+ NoIndex(users) */ * FROM users WHERE email = 'test@example.com';
五、执行引擎 #
5.1 执行模式 #
text
执行引擎模式
┌─────────────────────────────────────────────────────────────┐
│ │
│ 本地执行 (Local Execution): │
│ ├── 单节点操作 │
│ ├── 小数据量查询 │
│ └── 低延迟 │
│ │
│ 分布式执行 (Distributed Execution): │
│ ├── 多节点并行 │
│ ├── 大数据量查询 │
│ └── 高吞吐 │
│ │
│ 执行计划树: │
│ │
│ ┌─────────┐ │
│ │ Root │ │
│ │ (结果) │ │
│ └────┬────┘ │
│ │ │
│ ┌──────────┼──────────┐ │
│ │ │ │ │
│ ┌────┴────┐┌────┴────┐┌────┴────┐ │
│ │ Join ││ Join ││ Join │ │
│ └────┬────┘└────┬────┘└────┬────┘ │
│ │ │ │ │
│ ┌────┴────┐┌────┴────┐┌────┴────┐ │
│ │ Scan ││ Scan ││ Scan │ │
│ │ Node 1 ││ Node 2 ││ Node 3 │ │
│ └─────────┘└─────────┘└─────────┘ │
│ │
└─────────────────────────────────────────────────────────────┘
5.2 执行算子 #
sql
-- 常见执行算子
-- Scan: 表扫描
EXPLAIN SELECT * FROM users;
-- • scan
-- Index Join: 索引连接
EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';
-- • index join
-- Hash Join: 哈希连接
EXPLAIN SELECT * FROM users u JOIN orders o ON u.id = o.user_id;
-- • hash join
-- Merge Join: 归并连接
EXPLAIN SELECT * FROM users u JOIN orders o ON u.id = o.user_id ORDER BY u.id;
-- • merge join
-- Aggregation: 聚合
EXPLAIN SELECT COUNT(*) FROM users;
-- • group by (hash)
-- Sort: 排序
EXPLAIN SELECT * FROM users ORDER BY name;
-- • sort
-- Limit: 限制
EXPLAIN SELECT * FROM users LIMIT 10;
-- • limit
5.3 向量化执行 #
text
向量化执行
┌─────────────────────────────────────────────────────────────┐
│ │
│ 传统执行 (Volcano Model): │
│ ├── 一次处理一行 │
│ ├── 函数调用开销大 │
│ └── CPU缓存利用率低 │
│ │
│ 向量化执行 (Vectorized Execution): │
│ ├── 一次处理一批行 │
│ ├── 减少函数调用 │
│ ├── CPU缓存友好 │
│ └── SIMD指令优化 │
│ │
│ 示例: │
│ SELECT SUM(amount) FROM orders; │
│ │
│ 传统: 逐行累加 │
│ 向量化: 批量累加 │
│ │
└─────────────────────────────────────────────────────────────┘
六、DDL处理 #
6.1 Schema变更 #
sql
-- 在线Schema变更
-- CockroachDB 支持在线DDL，不阻塞读写
-- 添加列
ALTER TABLE users ADD COLUMN age INT DEFAULT 0;
-- 添加索引
CREATE INDEX idx_email ON users(email);
-- 修改列类型
ALTER TABLE users ALTER COLUMN name TYPE VARCHAR(200);
-- 删除列
ALTER TABLE users DROP COLUMN age;
-- 查看Schema变更状态
SHOW JOBS;
6.2 Schema变更流程 #
text
Schema 变更流程
┌─────────────────────────────────────────────────────────────┐
│ │
│ 1. 提交DDL语句 │
│ │ │
│ ▼ │
│ 2. 验证Schema │
│ │ │
│ ▼ │
│ 3. 创建Job │
│ │ │
│ ▼ │
│ 4. 后台执行 │
│ ├── 添加新版本Schema │
│ ├── 数据回填 (如需要) │
│ └── 删除旧版本Schema │
│ │ │
│ ▼ │
│ 5. 完成变更 │
│ │
│ 特点: │
│ ├── 非阻塞 │
│ ├── 可取消 │
│ └── 可恢复 │
│ │
└─────────────────────────────────────────────────────────────┘
七、统计信息 #
7.1 统计信息收集 #
sql
-- 创建统计信息
CREATE STATISTICS users_stats FROM users;
-- 查看统计信息
SHOW STATISTICS FOR TABLE users;
-- 自动统计信息收集
-- CockroachDB 自动收集统计信息
-- 手动收集
ANALYZE users;
7.2 统计信息内容 #
text
统计信息内容
┌─────────────────────────────────────────────────────────────┐
│ │
│ 表级统计: │
│ ├── 行数 (row count) │
│ └── 表大小 (table size) │
│ │
│ 列级统计: │
│ ├── 不同值数量 (distinct count) │
│ ├── NULL值比例 (null fraction) │
│ ├── 直方图 (histogram) │
│ └── 最常见值 (MCV) │
│ │
│ 索引统计: │
│ ├── 索引大小 │
│ └── 索引选择性 │
│ │
└─────────────────────────────────────────────────────────────┘
八、会话管理 #
8.1 会话变量 #
sql
-- 查看会话变量
SHOW ALL;
-- 设置时区
SET TIME ZONE 'Asia/Shanghai';
-- 设置隔离级别
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- 设置查询超时
SET statement_timeout = '30s';
-- 设置结果格式
SET database = 'mydb';
8.2 连接池配置 #
python
# Python psycopg2 连接池
import psycopg2
from psycopg2 import pool
connection_pool = psycopg2.pool.ThreadedConnectionPool(
minconn=5,
maxconn=20,
host='localhost',
port=26257,
user='root',
database='defaultdb'
)
# 获取连接
conn = connection_pool.getconn()
# 释放连接
connection_pool.putconn(conn)
九、总结 #
SQL层要点：
| 组件 | 功能 |
|---|---|
| 协议层 | PostgreSQL兼容 |
| 解析器 | SQL解析 |
| 优化器 | 查询优化 |
| 执行引擎 | 查询执行 |
下一步，让我们学习存储层！
最后更新：2026-03-27