SQL 优化
→ 返回 数据库基础
SQL 优化的核心是减少扫描行数和减少磁盘 IO。正确的流程：先定位慢查询 → 看执行计划 → 针对性优化。
慢查询定位
开启慢查询日志（MySQL）
-- 查看当前状态
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';
-- 动态开启（重启后失效，生产建议写入 my.cnf）
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1; -- 超过 1s 记录
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
SET GLOBAL log_queries_not_using_indexes = ON; -- 未走索引的也记录pt-query-digest 分析
# 分析慢查询日志，按总耗时排序
pt-query-digest /var/log/mysql/slow.log \
--order-by=Query_time:sum \
--limit=20 \
> slow_report.txt输出结构：
# Query 1: 总次数, 平均耗时, 占比
# Fingerprint（模板化的 SQL）
# 示例 SQL
Performance Schema（实时）
-- 找出累计耗时最长的 SQL
SELECT DIGEST_TEXT, COUNT_STAR, SUM_TIMER_WAIT/1e12 AS total_sec
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;EXPLAIN 执行计划详解
EXPLAIN SELECT o.id, u.name
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status = 'paid' AND o.created_at > '2024-01-01';type 字段（最重要）
从最差到最优：
ALL → index → range → ref → eq_ref → const → system
| type | 说明 | 何时出现 |
|---|---|---|
ALL | 全表扫描 | 无索引 / 优化器放弃索引 |
index | 全索引扫描（扫索引树所有叶节点） | 覆盖索引但无过滤条件 |
range | 索引范围扫描 | >, <, BETWEEN, IN, LIKE 'x%' |
ref | 非唯一索引等值查询 | 普通索引 WHERE idx_col = val |
eq_ref | 唯一索引等值查询 | JOIN 时关联唯一索引 |
const | 主键/唯一索引等值，最多一行 | WHERE id = 1 |
rows × filtered = 实际预估行数
EXPLAIN SELECT * FROM orders WHERE user_id = 1 AND status = 'paid';
-- rows=100, filtered=50.00 → 预估最终结果 50 行EXPLAIN ANALYZE（MySQL 8.0+，实际执行）
EXPLAIN ANALYZE SELECT ...;
-- 返回实际执行时间、实际行数 vs 预估行数，定位估算偏差常见优化手段
避免 SELECT *
-- 差：传输无用列，阻止覆盖索引
SELECT * FROM orders WHERE user_id = 1;
-- 好：只取需要的列，可能触发覆盖索引
SELECT id, amount, status FROM orders WHERE user_id = 1;深分页优化
-- 差：OFFSET 100万，扫描并丢弃 100万行
SELECT * FROM orders ORDER BY id LIMIT 1000000, 20;
-- 好：游标分页（记住上次最大 id）
SELECT * FROM orders WHERE id > 1000000 ORDER BY id LIMIT 20;
-- 好：延迟关联（先定位主键，再关联取完整行）
SELECT o.* FROM orders o
JOIN (
SELECT id FROM orders ORDER BY id LIMIT 1000000, 20
) t ON o.id = t.id;JOIN 优化
-- 驱动表应是小表（结果集小的）
-- MySQL 会自动选择 Nested Loop Join，被驱动表需要索引
-- 确保 JOIN 条件列有索引
-- 差：右表 status 列无索引，每行都全表扫描 users
SELECT u.name FROM orders o JOIN users u ON o.user_id = u.id
WHERE u.status = 'active'; -- 确保 idx_status(status) 存在
-- 避免 JOIN 过多表（通常不超过 3 张）
-- 过多 JOIN → 执行计划复杂，优化器可能选错OR 改 UNION ALL
-- 差：OR 可能导致全表扫描（特别是 OR 两边列来自不同索引）
SELECT * FROM users WHERE name = 'Alice' OR email = 'alice@x.com';
-- 好：拆成两个索引查询，各自走索引
SELECT * FROM users WHERE name = 'Alice'
UNION ALL
SELECT * FROM users WHERE email = 'alice@x.com' AND name != 'Alice';IN 子查询改 JOIN / EXISTS
-- IN 子查询（子查询结果集大时性能差）
SELECT * FROM orders WHERE user_id IN (SELECT id FROM users WHERE vip = 1);
-- 改为 JOIN（通常更高效）
SELECT o.* FROM orders o
JOIN users u ON o.user_id = u.id WHERE u.vip = 1;
-- EXISTS（关联子查询，适合外表小、子查询大的场景）
SELECT * FROM orders o
WHERE EXISTS (SELECT 1 FROM users u WHERE u.id = o.user_id AND u.vip = 1);ORDER BY 优化
-- 利用索引顺序避免 filesort
-- 条件：ORDER BY 的列与索引顺序一致，且 WHERE 等值条件覆盖了前缀
-- 索引 idx_user_time(user_id, created_at)
-- ✅ 走索引排序（user_id 等值 + created_at 排序，满足最左前缀）
SELECT * FROM orders WHERE user_id = 1 ORDER BY created_at DESC LIMIT 20;
-- ❌ filesort（WHERE 用了 IN，破坏了排序的连续性）
SELECT * FROM orders WHERE user_id IN (1, 2) ORDER BY created_at DESC;GROUP BY 优化
-- 确保 GROUP BY 列有索引
-- 避免 Using temporary + Using filesort
-- ✅ 有 idx_status 索引，直接按索引 group
SELECT status, COUNT(*) FROM orders GROUP BY status;
-- 大表聚合优化：先过滤再聚合，减少参与 GROUP BY 的行数
SELECT status, COUNT(*) FROM orders
WHERE created_at > '2024-01-01' -- 先过滤，走 idx_created_at
GROUP BY status;大表查询优化
小范围代替大范围
-- 差：扫描全年数据
SELECT COUNT(*) FROM logs WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31';
-- 好：按月分批查询，避免单次扫描过多数据
-- 配合缓存，每月只查一次统计类查询走汇总表
-- 实时 COUNT 大表，每次全表扫描
SELECT COUNT(*) FROM orders WHERE status = 'paid';
-- 维护汇总表（由 CDC 或定时任务更新）
SELECT cnt FROM order_stats WHERE status = 'paid';避免在大表上做 DDL（在线 DDL）
-- 直接 ALTER 大表会锁表（MySQL 5.5 以前）
ALTER TABLE orders ADD COLUMN remark VARCHAR(500);
-- 推荐：pt-online-schema-change 或 gh-ost 在线变更
pt-online-schema-change --alter "ADD COLUMN remark VARCHAR(500)" D=mydb,t=orders --execute查询重写技巧
范围查询替代函数
-- 差：函数导致索引失效
WHERE YEAR(created_at) = 2024
-- 好：范围查询保留索引
WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01'前缀替代全模糊
-- 差：前置通配符，无法用索引
WHERE name LIKE '%Alice%'
-- 好（如果能保证前缀匹配）：
WHERE name LIKE 'Alice%'
-- 全文搜索需求 → Elasticsearch / MySQL FULLTEXTNULL 值处理
-- IS NULL / IS NOT NULL 在有些场景可以走索引（MySQL 8.0+）
-- 但建议业务上对频繁查询的列设置 DEFAULT 值，避免 NULL
-- 聚合函数忽略 NULL：COUNT(*) 统计行数，COUNT(col) 忽略 NULL 值行
SELECT COUNT(*), COUNT(email) FROM users;优化 checklist
1. □ EXPLAIN 看 type，是否有 ALL（全表扫描）
2. □ rows 是否合理（预估扫描行数）
3. □ Extra 有无 Using filesort / Using temporary
4. □ 查询列是否有 SELECT *（改为指定列）
5. □ WHERE 条件列是否有索引（考虑联合索引）
6. □ 函数 / 运算 是否作用在索引列上（改为等价范围写法）
7. □ JOIN 被驱动表是否有索引
8. □ 深分页是否已改为游标分页
9. □ 大聚合是否能走汇总表/预计算
10. □ 数据量增长后是否需要分表