1
 2
 3
 4
 5
 6
 7
 8
 9
10

SQL-86（SQL-87）：第一版，IBM DB2 主导
SQL-89：minor update
SQL-92（SQL2）：重大升级，加入 JOIN 语法
SQL:1999（SQL3）：触发器、递归、正则
SQL:2003：窗口函数、XML、自动生成 ID
SQL:2006：XML 增强
SQL:2008：INSTEAD OF、TRUNCATE
SQL:2011：时态表
SQL:2016：JSON、行模式识别
SQL:2019：多维数组

1
2
3
4

观察：
  - SQL 标准 ≠ SQL 实现
  - 每家数据库都"接近标准 + 自有扩展"
  - 实际开发中以厂商文档为准

1
2
3
4
5

关键观察：
  - Oracle 的 PL/SQL 是最"重"的（包、过程、触发器、对象类型）
  - PG 的 PL/pgSQL 模仿 Oracle（让 Oracle 用户过渡平滑）
  - MySQL 没有强"方言"，更多是 ANSI SQL
  - SQL Server 的 T-SQL 在微软生态独特

1
2
3
4
5

陷阱：
  - Oracle 没有 INT 关键字（其实有别名，但底层都是 NUMBER）
  - Oracle 的 NUMBER(10,0) 占 8~21 字节，比 PG 的 int（4 字节）大
  - MySQL 的 TINYINT(1) 经常被当成 boolean，但存储的还是数字
  - PostgreSQL 的 SERIAL 是 SEQUENCE + DEFAULT 的语法糖

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22

-- Oracle 12c+ 终于有 IDENTITY
CREATE TABLE t (id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY);

-- 12c 之前：SEQUENCE + 触发器
CREATE SEQUENCE seq_t START WITH 1;
CREATE TABLE t (id INT PRIMARY KEY);
CREATE TRIGGER t_trg BEFORE INSERT ON t
FOR EACH ROW
BEGIN
  SELECT seq_t.NEXTVAL INTO :new.id FROM dual;
END;

-- SQL Server
CREATE TABLE t (id INT IDENTITY(1,1) PRIMARY KEY);

-- MySQL
CREATE TABLE t (id INT AUTO_INCREMENT PRIMARY KEY);

-- PostgreSQL
CREATE TABLE t (id SERIAL PRIMARY KEY);  -- 9.x 经典
-- 或
CREATE TABLE t (id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY);  -- 10+

1
2
3
4
5

坑：
  - Oracle 的 VARCHAR2 vs VARCHAR：官方推荐 VARCHAR2
  - MySQL 的 VARCHAR(255) 之内只用 1 字节存长度，256+ 用 2 字节
  - PostgreSQL 没有 VARCHAR2，统一 varchar
  - SQL Server 的 NVARCHAR 是 UTF-16，VARCHAR 是单字节

1
2
3
4
5

MySQL 的 TIMESTAMP 坑：
  - 范围 1970~2038（4 字节）
  - 自动 UTC 转换
  - 5.6.4+ 支持小数秒
  - DATETIME 不做时区转换，8 字节

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

-- MySQL 5.7+
CREATE TABLE t (data JSON);
SELECT data->'$.name' FROM t;

-- PostgreSQL 9.2+（JSON）/ 9.4+（JSONB）
CREATE TABLE t (data JSONB);
CREATE INDEX idx_data ON t USING GIN(data);
SELECT * FROM t WHERE data @> '{"name":"Alice"}';

-- SQL Server 2016+
CREATE TABLE t (data NVARCHAR(MAX) CHECK (ISJSON(data) > 0));

-- Oracle 12c+
CREATE TABLE t (data CLOB CHECK (data IS JSON));

1
2
3
4

观察：
  - PG 的 JSONB 最强（二进制存储、GIN 索引、丰富的操作符）
  - MySQL 的 JSON 是文本 + 解析（功能完整但性能不如 JSONB）
  - SQL Server / Oracle 都是"存文本 + 验证"

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

-- 标准 SQL，四家都支持
WITH active_users AS (
  SELECT id, name FROM users WHERE status = 'active'
),
recent_orders AS (
  SELECT user_id, COUNT(*) AS cnt FROM orders
  WHERE created_at > NOW() - INTERVAL '7 days'
  GROUP BY user_id
)
SELECT u.name, COALESCE(r.cnt, 0) AS order_count
FROM active_users u
LEFT JOIN recent_orders r ON u.id = r.user_id;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

-- 经典场景：组织架构、菜单树、路径搜索
WITH RECURSIVE org_tree AS (
  -- 基础查询：CEO
  SELECT id, name, parent_id, 1 AS level
  FROM employees
  WHERE id = 1

  UNION ALL

  -- 递归：下属
  SELECT e.id, e.name, e.parent_id, ot.level + 1
  FROM employees e
  JOIN org_tree ot ON e.parent_id = ot.id
)
SELECT level, name FROM org_tree ORDER BY level;

1
2
3
4
5

四家支持情况：
  - PostgreSQL：WITH RECURSIVE
  - SQL Server：WITH（不需 RECURSIVE 关键字）
  - Oracle：WITH（11g R2+）
  - MySQL：WITH RECURSIVE（8.0+）

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

-- 排名：每个部门薪水前 3
SELECT dept, name, salary,
       ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn,
       RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS rk,
       DENSE_RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS dense_rk
FROM employees
ORDER BY dept, rn;

-- 累计求和
SELECT date, sales,
       SUM(sales) OVER (ORDER BY date) AS cum_sales
FROM daily_sales;

-- 移动平均
SELECT date, sales,
       AVG(sales) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS ma7
FROM daily_sales;

-- LAG / LEAD（前后行比较）
SELECT date, sales,
       LAG(sales) OVER (ORDER BY date) AS prev_sales,
       sales - LAG(sales) OVER (ORDER BY date) AS diff
FROM daily_sales;

1
2
3
4
5
6

支持情况：
  - 窗口函数：SQL:2003 标准
  - PostgreSQL 8.4+
  - SQL Server 2005+
  - Oracle 8i+
  - MySQL 8.0+（最晚加入的）

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

RBO（Rule-Based Optimizer）：
  - 基于规则
  - 比如："有索引就走索引"
  - 不考虑数据分布
  - 简单但僵化
  - Oracle 早期用，现代基本淘汰

CBO（Cost-Based Optimizer）：
  - 基于代价
  - 估算每种执行计划的代价（CPU + I/O）
  - 选最优
  - 需要"统计信息"支撑
  - 现代主流

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20

1. 解析 SQL → AST
2. 查询重写（Query Rewrite）
   - 视图展开
   - 谓词下推
   - 子查询展开
   - 常量折叠

3. 计划枚举
   - 单表访问路径（全表扫 / 索引扫 / 索引合并）
   - JOIN 顺序枚举（A JOIN B vs B JOIN A）
   - JOIN 方法（Nested Loop / Hash / Merge）

4. 代价估算
   - 单表代价 = 行数 × 选择率 × 每行代价
   - JOIN 代价 = 左代价 + 右代价 + 连接代价
   - 综合考虑 CPU + I/O

5. 选择最小代价的计划

6. 计划执行

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

问题：多表 JOIN 的顺序枚举复杂度爆炸
  - N 张表 JOIN，组合数 ~ N!
  - 12 表 JOIN：~4.79 亿种顺序

PG 的解法：
  - 表数 ≥ geqo_threshold（默认 12）
  - 切换到 GEQO（Genetic Query Optimization）
  - 用遗传算法找近似最优

代价：
  - 不保证最优
  - 但避免了组合爆炸

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

CBO 基于统计信息估算：
  - 表行数
  - 列的基数（distinct value 数）
  - 列的最值
  - 数据分布（直方图）
  - 索引高度、叶子数
  - 索引聚簇因子（Cluster Factor）

没有统计信息：
  - CBO 只能猜
  - 经常选错索引
  - 经常全表扫

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

直方图：解决"数据分布不均"

例：status 字段 99% 是 'A'，1% 是 'B'
  WHERE status = 'A' → 99% 行匹配
  WHERE status = 'B' → 1% 行匹配

没直方图：CBO 估算都是 50%（默认假设均匀分布）
有直方图：CBO 知道 'A' 99%，'B' 1%，做精确选择

类型：
  - 频率直方图（Frequency Histogram）
  - 等高直方图（Height-Balanced Histogram）
  - 顶级频率（Top-Frequency）
  - 混合（Hybrid，Oracle 12c+）

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

-- Oracle
EXEC DBMS_STATS.GATHER_TABLE_STATS('SCHEMA', 'TABLE', cascade => TRUE);
EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SCHEMA');

-- SQL Server
UPDATE STATISTICS t;
-- 自动：默认开启（auto_update_statistics）

-- MySQL
ANALYZE TABLE t;
-- 自动：8.0+ 自动采样

-- PostgreSQL
ANALYZE t;
-- 自动：autovacuum 自动分析

1
2
3
4
5

关键配置：
  - 目标行数（采样精度）
  - 是否生成直方图
  - 收集频率
  - 收集时机（避开业务高峰）

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22

-- Oracle
EXPLAIN PLAN FOR SELECT * FROM t WHERE id = 1;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- 或更详细：
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format => 'ALLSTATS LAST'));

-- SQL Server
SET SHOWPLAN_TEXT ON;
GO
SELECT * FROM t WHERE id = 1;
GO
-- 或图形：SSMS → 显示估计的执行计划

-- MySQL
EXPLAIN SELECT * FROM t WHERE id = 1;
EXPLAIN FORMAT=JSON SELECT * FROM t WHERE id = 1;
EXPLAIN ANALYZE SELECT * FROM t WHERE id = 1;  -- 8.0+，实际执行

-- PostgreSQL
EXPLAIN SELECT * FROM t WHERE id = 1;
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM t WHERE id = 1;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21

关键字：
  Seq Scan：顺序扫描（全表扫）
  Index Scan：索引扫描（取行）
  Index Only Scan：覆盖索引（不回表）
  Bitmap Heap Scan：先索引位图，再批量取行
  Hash Join：哈希连接（适合大表）
  Nested Loop：嵌套循环（适合小表）
  Merge Join：归并连接（已有序）
  HashAggregate：哈希聚合
  Sort：排序

阅读顺序：
  - 从最里层（缩进最深）开始读
  - 自下而上组合
  - 关注 rows、cost、actual time

判断：
  - "rows 估算 = 实际行数" → 统计信息准确
  - "rows 估算 << 实际行数" → 统计信息过时
  - "Seq Scan 在大表" → 缺索引
  - "Hash Join 内存爆炸" → 内存配置低

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

-- PostgreSQL
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT u.name, COUNT(*) AS cnt
FROM users u JOIN orders o ON u.id = o.user_id
WHERE u.created_at > '2026-01-01'
GROUP BY u.name
ORDER BY cnt DESC
LIMIT 10;

-- 输出包含：
  - 估算行数 vs 实际行数
  - 估算代价 vs 实际时间
  - 缓冲区命中（shared hits vs read）
  - 内存使用（Hash Memory）
  - IO 时间

1
2
3
4
5
6
7
8

-- 经典坑：phone 是 VARCHAR，传 INT
SELECT * FROM users WHERE phone = 13800000000;

MySQL：自动转换 → CAST(phone AS INT) → 不走索引
PG：报错（更严格，避免错误）
Oracle：可能转换 → 索引失效

→ 解决：传字符串 '13800000000'

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18

SQL Server 的经典问题：

  CREATE PROCEDURE get_user(@age INT) AS
    SELECT * FROM users WHERE age = @age;
  GO

  -- 第一次执行：@age = 18
  -- CBO 看 'age=18' 选择率高 → 走索引
  -- 缓存这个计划

  -- 第二次执行：@age = 30
  -- 实际选择率低，应该全表扫
  -- 但用了缓存的"走索引"计划 → 慢

解决：
  - OPTIMIZE FOR UNKNOWN：用平均选择率
  - OPTION (RECOMPILE)：每次重新编译
  - 局部变量绕过嗅探

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

Oracle 经典问题：共享池污染

  不用绑定变量：
    SELECT * FROM t WHERE id = 1;
    SELECT * FROM t WHERE id = 2;
    SELECT * FROM t WHERE id = 3;
    → 每条都是不同 SQL → 都要解析 → 共享池爆炸

  用绑定变量：
    SELECT * FROM t WHERE id = :1;
    → 一次解析多次复用 → 高效

MySQL：
  - 没有"共享池"概念
  - 但 prepared statement 也有类似优化

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

-- 优化前：先 JOIN 再过滤
SELECT * FROM big_table b JOIN small_table s ON b.id = s.id
WHERE s.status = 'active';

-- 优化后（CBO 自动）：先过滤 small_table，再 JOIN
SELECT * FROM big_table b JOIN (
  SELECT * FROM small_table WHERE status = 'active'
) s ON b.id = s.id;

-- 四家 CBO 都会自动做谓词下推
-- 但视图 / 子查询嵌套深时可能失败 → 手动改写

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

ORM 常见问题：
  users = User.objects.all()[:100]    # 1 次查询
  for u in users:
      print(u.profile.bio)            # 每次循环都查一次

  → 100 个用户做了 101 次查询（N+1）

解决：
  - ORM 的 prefetch_related / eager loading
  - 手写 JOIN
  - DataLoader 模式

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

-- Oracle
SELECT /*+ INDEX(t idx_name) */ * FROM t WHERE name = 'Alice';
SELECT /*+ FULL(t) */ * FROM t WHERE id = 1;
SELECT /*+ LEADING(t1 t2) USE_HASH(t2) */ ...
FROM t1 JOIN t2 ON ...;

-- MySQL 8.0+ 也支持
SELECT /*+ NO_RANGE_OPTIMIZATION(t PRIMARY) */ * FROM t WHERE id < 100;
SELECT /*+ JOIN_ORDER(a, b, c) */ ...

-- PostgreSQL：不支持 Hint（设计哲学不同）
-- 替代：调整统计信息、SET enable_xxx = off、改写 SQL

1
2
3
4

PG 反对 Hint 的哲学：
  - Hint 让 SQL 变得"非声明式"
  - 数据变化后 Hint 仍然生效 → 性能反而变差
  - 应该让 CBO 不断改进，而不是手工指定

1
2
3
4
5
6
7
8

适用场景：
  - CBO 选错索引（统计信息不足）
  - 临时强制某种 JOIN 方法
  - 临时强制某种顺序

不适用：
  - 长期解决方案（应该修统计信息）
  - 每条 SQL 都加（说明统计信息严重缺失）

1
2
3
4
5
6
7
8
9

-- 经典问题：IN vs EXISTS 谁快？

-- 没有定论，取决于：
  - 子查询大小
  - 外查询大小
  - CBO 实现

-- 现代优化器都能互相转换，效果相同
-- 经验：小表用 IN，大表用 EXISTS（CBO 时代无所谓了）

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

-- NOT IN 的 NULL 陷阱
SELECT * FROM a WHERE id NOT IN (SELECT a_id FROM b);
-- 如果 b.a_id 有 NULL → 整个查询返回空！

-- NOT EXISTS 更安全
SELECT * FROM a WHERE NOT EXISTS (SELECT 1 FROM b WHERE b.a_id = a.id);

-- 解决方案：IS NOT NULL
SELECT * FROM a WHERE id NOT IN (
  SELECT a_id FROM b WHERE a_id IS NOT NULL
);

1
2
3
4
5
6
7
8
9

不要 SELECT *：
  - 占用带宽（无用列也传输）
  - 无法用覆盖索引
  - 表结构变化时可能出 bug
  - 阻断某些视图优化

例外：
  - 临时调试
  - COUNT(*)（COUNT(1) 没有差异）

1
2
3
4

记住三句话：
  1. 90% 的"数据库慢"问题，根因是统计信息过时
  2. EXPLAIN ANALYZE（或等效命令）是性能调优的第一工具
  3. 优化 SQL 不是优化"语法"，是优化"执行计划"