SQL 优化方法论：慢 SQL 发现、执行计划、改写、索引和效果验证（118）

-- 开启慢查询日志
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 0.5;  -- 超过 0.5 秒记录
SET GLOBAL log_queries_not_using_indexes = ON;  -- 记录未使用索引的查询

# mysqldumpslow 分析慢日志
mysqldumpslow -s t -t 10 slow.log  # 按时间排序，前 10 条

# pt-query-digest 更详细的分析
pt-query-digest slow.log > report.txt

-- 查询执行最多的 SQL
SELECT DIGEST_TEXT, COUNT_STAR, AVG_TIMER_WAIT/1000000000 AS avg_ms
FROM performance_schema.events_statements_summary_by_digest
ORDER BY AVG_TIMER_WAIT DESC LIMIT 10;

1. EXPLAIN SQL → 看 type（访问类型）
2. 看 key（使用的索引）
3. 看 rows（扫描行数）
4. 看 Extra（额外信息）
5. 如果不够详细 → EXPLAIN FORMAT=JSON
6. 如果需要实际数据 → EXPLAIN ANALYZE（MySQL 8.0.18+）

-- 坏
SELECT * FROM orders WHERE user_id = 100;

-- 好
SELECT id, amount, status FROM orders WHERE user_id = 100;

-- 坏：子查询
SELECT * FROM orders WHERE user_id IN (SELECT id FROM users WHERE status = 'ACTIVE');

-- 好：JOIN（MySQL 5.6+ 优化器已能优化半连接，但 JOIN 更可控）
SELECT o.* FROM orders o JOIN users u ON o.user_id = u.id WHERE u.status = 'ACTIVE';

-- UNION 会去重（排序），UNION ALL 不去重（更快）
SELECT id FROM t1 UNION ALL SELECT id FROM t2;

-- 坏：大偏移量
SELECT * FROM orders ORDER BY id LIMIT 1000000, 10;

-- 好：游标分页
SELECT * FROM orders WHERE id > 1000000 ORDER BY id LIMIT 10;

-- 坏：逐条插入
INSERT INTO t VALUES (1, 'a');
INSERT INTO t VALUES (2, 'b');

-- 好：批量插入
INSERT INTO t VALUES (1, 'a'), (2, 'b');

-- 原始查询
SELECT id, user_id, amount, create_time
FROM orders
WHERE user_id = 100 AND status = 'PAID'
ORDER BY create_time DESC
LIMIT 20;

-- 分析：等值(user_id, status) → 排序(create_time) → 覆盖(id, amount)
-- 最优索引
CREATE INDEX idx_ustc ON orders(user_id, status, create_time, id, amount);

-- 查找未使用的索引
SELECT * FROM sys.schema_unused_indexes;

-- 查找冗余索引
SELECT * FROM sys.schema_redundant_indexes
WHERE redundant_index_name IS NOT NULL;

-- 开启 profiling
SET profiling = 1;
SELECT ...;
SHOW PROFILES;
SHOW PROFILE FOR QUERY 1;

# 使用 sysbench 或 mysqlslap 压测
mysqlslap --concurrency=10 --iterations=100 --query="SELECT ..."

-- 优化前：Using filesort，扫描 100 万行
EXPLAIN SELECT * FROM orders WHERE user_id = 100 ORDER BY create_time DESC LIMIT 10;
-- type: ref, rows: 1000000, Extra: Using where; Using filesort

-- 优化后：Using index
CREATE INDEX idx_ut ON orders(user_id, create_time);
-- type: ref, rows: 10, Extra: Using index

-- 优化前：全表扫描
SELECT COUNT(*) FROM orders WHERE status = 'PAID';

-- 优化后：覆盖索引
CREATE INDEX idx_status ON orders(status);
-- type: index, Extra: Using index

-- 优化前：t2 全表扫描
EXPLAIN SELECT * FROM t1 JOIN t2 ON t1.a = t2.a WHERE t1.b = 1;

-- 优化后：t1 有索引，t2 有索引
CREATE INDEX idx_t1_ba ON t1(b, a);
CREATE INDEX idx_t2_a ON t2(a);
-- t1: ref, t2: ref

-- 1. 查看冗余索引
SELECT * FROM sys.schema_redundant_indexes;

-- 2. 查看未使用索引
SELECT * FROM sys.schema_unused_indexes;

-- 3. 标记为不可见（MySQL 8.0+）
ALTER TABLE t ALTER INDEX idx_name INVISIBLE;

-- 4. 观察 1-2 周，确认无性能影响后删除
ALTER TABLE t DROP INDEX idx_name;

-- 第一页
SELECT * FROM t WHERE id > 0 ORDER BY id LIMIT 20;

-- 第 100 页（估算：id ≈ 20 × 99 = 1980）
SELECT * FROM t WHERE id > 1980 ORDER BY id LIMIT 20;
-- 如果 id 不连续（有删除），实际页码可能偏移

-- 先用覆盖索引定位，再回表
SELECT t.* FROM t
JOIN (SELECT id FROM t ORDER BY id LIMIT 2000, 20) tmp ON t.id = tmp.id;