07. 查询优化与执行计划

EXPLAIN ANALYZE
SELECT e.name, d.name, SUM(s.amount)
FROM employees e
JOIN departments d ON e.dept_id = d.id
JOIN sales s ON e.id = s.emp_id
WHERE s.date > '2024-01-01'
GROUP BY e.id, d.name;

-- 1. 避免 SELECT *
SELECT id, name FROM users WHERE status = 'active';
  -- 比 SELECT * 更可能走覆盖索引

-- 2. 范围查询后的列用不了索引
CREATE INDEX idx_a_b ON t(a, b);
SELECT * FROM t WHERE a > 1 AND b = 2;
  -- a 是范围，b 不走索引
  -- 解决：INDEX(a) 或改写为多个 = 条件

-- 3. 大偏移分页优化（1000000, 10）
-- ❌ 慢：扫描 1000010 行，取 10 行
SELECT * FROM orders ORDER BY id LIMIT 1000000, 10;

-- ✅ 快：先查 id，再关联
SELECT o.* FROM orders o
JOIN (SELECT id FROM orders ORDER BY id LIMIT 1000000, 10) tmp
ON o.id = tmp.id;

-- 4. 隐式转换导致索引失效
WHERE phone = 13800138000  -- phone 是 varchar，先转 int
-- ✅ WHERE phone = '13800138000'

-- 5. IN vs EXISTS
-- 大表 IN 小表 → EXISTS 更优
-- 小表 IN 大表 → IN 更优

-- 驱动表选择：小表驱动大表
SELECT * FROM small_table s
JOIN big_table b ON s.id = b.small_id;
  -- small_table 作为驱动表（外表）
  -- 对 big_table 走索引查找

-- 避免笛卡尔积
SELECT * FROM a, b;  -- 如果没有连接条件 → CROSS JOIN
SELECT * FROM a JOIN b ON a.id = b.a_id;  -- ✅ 正确

-- 为列创建直方图，解决非均匀分布问题
ANALYZE TABLE orders UPDATE HISTOGRAM ON status WITH 128 BUCKETS;

-- 查看直方图
SELECT COLUMN_NAME, HISTOGRAM
FROM information_schema.COLUMN_STATISTICS
WHERE TABLE_NAME = 'orders';

-- 删除直方图
ANALYZE TABLE orders DROP HISTOGRAM ON status;

-- 持久化统计信息（重启后不丢失）
SET GLOBAL innodb_stats_persistent = ON;
SET GLOBAL innodb_stats_auto_recalc = ON;

-- 查看表的最后分析时间
SELECT last_update, n_rows
FROM mysql.innodb_table_stats
WHERE table_name = 'orders';

-- 手动更新统计信息
ANALYZE TABLE orders;

-- PostgreSQL 中查看统计信息
SELECT relname, reltuples, relpages
FROM pg_class
WHERE relname = 'orders';

-- PostgreSQL 更新统计
ANALYZE orders;

-- 典型场景：IN 子查询优化器选择物化子查询（慢）
SELECT * FROM orders
WHERE customer_id IN (
  SELECT id FROM customers WHERE country = 'CN'
);

-- 优化器可能选择：
-- 1) 物化子查询 → 创建临时表 → 与 orders 做 JOIN（内存/磁盘临时表）
-- 2) 半连接（Semi Join） → EXISTS 方式逐行检查（通常更优）

-- 强制优化器使用半连接（MariaDB/PostgreSQL）
SELECT * FROM orders
WHERE customer_id IN (
  SELECT id FROM customers WHERE country = 'CN'
) SEMI JOIN customers USING (customer_id);

-- 或改写为 EXISTS（多数数据库优化得更好）
SELECT * FROM orders o
WHERE EXISTS (
  SELECT 1 FROM customers c
  WHERE c.id = o.customer_id AND c.country = 'CN'
);

-- 创建不可见索引（优化器不会使用，但会维护）
CREATE INDEX idx_test ON orders(created_at) INVISIBLE;

-- 测试索引效果（强制使用）
SELECT /*+ INDEX(orders idx_test) */ * FROM orders WHERE created_at > '2024-01-01';

-- 确认有效后设为可见
ALTER TABLE orders ALTER INDEX idx_test VISIBLE;

-- 支持显式 DESC 索引（8.0 之前是升序索引反向扫描，效率较低）
CREATE INDEX idx_created_desc ON orders(created_at DESC);

-- 优化 ORDER BY DESC 场景
SELECT * FROM orders ORDER BY created_at DESC LIMIT 10;
-- 8.0 之前：升序索引反向扫描（低效）
-- 8.0+：直接利用降序索引（高效）

-- 为函数表达式创建索引
CREATE INDEX idx_year ON orders( (YEAR(created_at)) );

-- 查询时直接使用
SELECT * FROM orders WHERE YEAR(created_at) = 2024;
-- 走函数索引，而非全表扫描

-- JSON 路径索引
CREATE INDEX idx_json ON products( (data->>'$.category') );
SELECT * FROM products WHERE data->>'$.category' = 'electronics';

-- 当连接条件没有索引、且两个表数据量都较大时
SELECT * FROM large_A a
JOIN large_B b ON a.hash_col = b.hash_col;

-- MySQL 8.0 选择 Hash Join：
-- 1. 扫描小表 B，构建哈希表
-- 2. 扫描大表 A，探测哈希表
-- 3. 时间复杂度 O(m + n)，优于嵌套循环 O(m × n)

-- ❌ 深度分页（越往后越慢）
SELECT * FROM logs ORDER BY id DESC LIMIT 1000000, 10;
-- MySQL 需要扫描 1000010 行，丢弃前 1000000 行

-- ✅ 方案一：书签式分页（推荐）
-- 利用上一页最后一条的 id 作为游标
SELECT * FROM logs
WHERE id < 9900_000_000  -- 上一页最后一条的 id
ORDER BY id DESC
LIMIT 10;
-- 时间复杂度 O(LIMIT)，与页数无关！

-- ✅ 方案二：延迟关联
SELECT l.* FROM logs l
JOIN (
  SELECT id FROM logs
  ORDER BY id DESC LIMIT 1000000, 10
) t ON l.id = t.id;
-- 先查 id（覆盖索引），再关联完整行

-- ✅ 方案三：搜索引擎（海量数据）
-- 使用 Elasticsearch / OpenSearch 的 from/size 或 search_after

-- 表结构
CREATE TABLE users (
    id BIGINT PRIMARY KEY,
    phone VARCHAR(20),
    name VARCHAR(50),
    INDEX idx_phone (phone)   -- B+树索引
);

-- 查询
SELECT * FROM users WHERE phone LIKE '138%';
-- 执行计划：type=range, key=idx_phone ✅ 走索引范围扫描

-- 但如果改为
SELECT * FROM users WHERE phone LIKE '%138%';
-- 执行计划：type=ALL, key=NULL ❌ 前导通配符无法走索引

-- 解决方案：倒排索引或搜索引擎
-- MySQL 8.0.13+ 支持降序索引 + 虚拟列
ALTER TABLE users ADD COLUMN phone_reverse VARCHAR(20) AS (REVERSE(phone)) VIRTUAL;
CREATE INDEX idx_phone_rev ON users(phone_reverse);

-- 改写查询
SELECT * FROM users WHERE phone_reverse LIKE REVERSE('%138');
-- 利用后缀匹配走索引（REVERSE('138%') = '%831' 仍是前导通配符，
-- 实际应使用全文索引或 Elasticsearch 倒排索引）

-- MySQL 全文索引
CREATE FULLTEXT INDEX ft_idx ON users(name, description);
SELECT * FROM users WHERE MATCH(name, description) AGAINST('搜索关键词' IN BOOLEAN MODE);

CREATE TABLE orders (
    id BIGINT PRIMARY KEY,
    phone VARCHAR(20),        -- 字符串类型
    status INT,
    INDEX idx_phone (phone)
);

-- ❌ 错误：整数与字符串比较，发生隐式转换
SELECT * FROM orders WHERE phone = 13800138000;
-- 执行计划：type=ALL（全表扫描），Extra=Using where

-- 原理：phone 是 VARCHAR，比较时 MySQL 将 phone 列的每个值转为 INT
-- 导致无法使用索引，且函数转换本身消耗 CPU

-- ✅ 正确：使用字符串常量
SELECT * FROM orders WHERE phone = '13800138000';
-- 执行计划：type=ref, key=idx_phone ✅

-- 表：orders (1000万行), customers (100万行), order_items (5000万行)

-- ❌ 低效：大表驱动小表（Nested Loop 遍历 5000万行）
SELECT o.*, c.name
FROM order_items oi          -- 5000万行（驱动表）
JOIN orders o ON oi.order_id = o.id
JOIN customers c ON o.customer_id = c.id
WHERE c.country = 'CN';

-- 优化器可能错误选择驱动表，此时需要 STRAIGHT_JOIN 强制顺序

-- ✅ 高效：小结果集驱动大表
SELECT o.*, c.name
FROM customers c             -- 过滤后约 10 万行
STRAIGHT_JOIN orders o ON o.customer_id = c.id
STRAIGHT_JOIN order_items oi ON oi.order_id = o.id
WHERE c.country = 'CN';

-- 或者使用子查询先过滤
SELECT o.*, c.name
FROM (
    SELECT id FROM customers WHERE country = 'CN'  -- 先找 CN 客户
) c
JOIN orders o ON o.customer_id = c.id
JOIN order_items oi ON oi.order_id = o.id;

-- ❌ 低效：NOT IN 遇到 NULL 返回空结果集，且通常不走索引
SELECT * FROM orders
WHERE customer_id NOT IN (
    SELECT id FROM customers WHERE status = 'banned'
);

-- ✅ 改写为 NOT EXISTS（更语义化，通常性能更好）
SELECT * FROM orders o
WHERE NOT EXISTS (
    SELECT 1 FROM customers c
    WHERE c.id = o.customer_id AND c.status = 'banned'
);

-- ✅ 或 LEFT JOIN + IS NULL
SELECT o.* FROM orders o
LEFT JOIN customers c ON c.id = o.customer_id AND c.status = 'banned'
WHERE c.id IS NULL;

-- 性能对比（1亿行 orders，10万 banned customers）：
-- NOT IN: ~15s（全表扫描子查询）
-- NOT EXISTS: ~200ms（索引半连接）
-- LEFT JOIN: ~300ms（索引 JOIN）