查询重写与参数化

-- 反模式
SELECT * FROM users WHERE id = 1;
-- 问题清单：
-- 1. 网络与内存：拉回 30 列只用 3 列
-- 2. 覆盖索引失效：索引只含部分列，* 强制回表
-- 3. 脆弱性：表加列后，INSERT INTO t2 SELECT * 这类语句悄悄变型
-- 4. 视图与 ORM 的隐式契约：列序变化对依赖位置取值的代码是灾难

-- 改写
SELECT name, email FROM users WHERE id = 1;

-- 相关子查询：外表每行执行一次内层
SELECT u.name,
  (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) AS order_count
FROM users u;

-- 改写：先聚合再连接（内层一次算完）
SELECT u.name, COALESCE(o.cnt, 0) AS order_count
FROM users u
LEFT JOIN (SELECT user_id, COUNT(*) AS cnt FROM orders GROUP BY user_id) o
  ON u.id = o.user_id;

-- 反模式：OFFSET 100 万 = 先扫完并丢掉 100 万行
SELECT * FROM orders ORDER BY id LIMIT 10 OFFSET 1000000;

-- 改写 A：游标分页（Keyset）——上一页末尾的 id 当书签
SELECT * FROM orders WHERE id > 1000000 ORDER BY id LIMIT 10;

-- 改写 B：无法用书签时，延迟关联——先在索引里翻页，再回表
SELECT o.* FROM orders o
JOIN (SELECT id FROM orders ORDER BY id LIMIT 10 OFFSET 1000000) t
  ON o.id = t.id;

-- UNION = 去重（内部排序/哈希），UNION ALL = 直接拼接
SELECT name FROM customers WHERE region = 'North'
UNION
SELECT name FROM suppliers WHERE region = 'North';

-- 两源本就无重复（customer 与 supplier 是不同表）时：
SELECT name FROM customers WHERE region = 'North'
UNION ALL
SELECT name FROM suppliers WHERE region = 'North';

-- IN 子查询可能物化整个结果集
SELECT * FROM orders
WHERE customer_id IN (SELECT id FROM customers WHERE vip = true);

-- EXISTS 短路：找到第一行匹配就停
SELECT * FROM orders o
WHERE EXISTS (SELECT 1 FROM customers c
              WHERE c.id = o.customer_id AND c.vip = true);

-- 逐条 INSERT：每条一次网络往返 + 一次事务
INSERT INTO users (name, email) VALUES ('Alice', 'a@test.com');
INSERT INTO users (name, email) VALUES ('Bob', 'b@test.com');

-- 批量：一条语句完成
INSERT INTO users (name, email) VALUES
  ('Alice', 'a@test.com'),
  ('Bob', 'b@test.com');

-- UPSERT（避免先查后插的竞态）
-- MySQL：
INSERT INTO counters (id, cnt) VALUES (1, 1)
  ON DUPLICATE KEY UPDATE cnt = cnt + 1;
-- PostgreSQL：
INSERT INTO counters (id, cnt) VALUES (1, 1)
  ON CONFLICT (id) DO UPDATE SET cnt = counters.cnt + 1;

-- 拼接：SQL 注入 + 每次完整解析
-- cursor.execute(f"SELECT * FROM users WHERE name = '{user_name}'")   -- 危险

-- 参数化：值走通道、语句走缓存
cursor.execute("SELECT * FROM users WHERE name = %s", (user_name,))

-- 数据库侧的等价物（了解原理用，生产走驱动参数化）：
-- PostgreSQL 扩展协议 / PREPARE
PREPARE get_user(TEXT) AS SELECT * FROM users WHERE name = $1;
EXECUTE get_user('Alice');

-- MySQL
PREPARE stmt FROM 'SELECT * FROM users WHERE name = ?';
SET @name = 'Alice';
EXECUTE stmt USING @name;
DEALLOCATE PREPARE stmt;

-- PostgreSQL：查看与手动刷新
SELECT attname, n_distinct, null_frac FROM pg_stats WHERE tablename = 'users';
ANALYZE users;                       -- 采样分析，不锁读写
ALTER TABLE users ALTER COLUMN email SET STATISTICS 500;   -- 倾斜列提高采样
ANALYZE users;

-- MySQL
ANALYZE TABLE users;
-- SQL Server
UPDATE STATISTICS users;

-- 行数估算 vs 精确行数
SELECT reltuples::bigint AS estimate FROM pg_class WHERE relname = 'users';  -- 瞬时
SELECT COUNT(*) FROM users;                                                  -- 精确但全扫