接上篇继续写一些explain调优案例

案例1：全表扫描优化

-- 问题查询
EXPLAIN SELECT * FROM orders WHERE status = 'pending' AND total_amount > 1000;
-- type: ALL, key: NULL, rows: 100000

-- 优化方案1：添加单列索引
ALTER TABLE orders ADD INDEX idx_status(status);
-- 重新 EXPLAIN: type: ref, rows: 5000

-- 优化方案2：添加复合索引（更优）
ALTER TABLE orders ADD INDEX idx_status_amount(status, total_amount);
-- 重新 EXPLAIN: type: range, rows: 1000
-- 因为 status 是等值，total_amount 是范围

案例2：文件排序优化

-- 问题查询
EXPLAIN SELECT * FROM products 
WHERE category_id = 5 
ORDER BY price DESC;
-- Extra: Using filesort

-- 优化：添加复合索引
ALTER TABLE products ADD INDEX idx_category_price(category_id, price);
-- 重新 EXPLAIN: 
-- type: ref, Extra: Using where (没有 Using filesort)

案例3：覆盖索引优化

-- 问题查询
EXPLAIN SELECT id, name, email FROM users WHERE age > 25;
-- type: ALL, rows: 50000

-- 优化：创建覆盖索引
ALTER TABLE users ADD INDEX idx_age_cover(age, name, email);
-- 重新 EXPLAIN:
-- type: range, key: idx_age_cover, Extra: Using index

案例4：最左前缀原则

-- 有复合索引 (a, b, c)

-- 有效使用索引：
EXPLAIN SELECT * FROM t WHERE a = 1;
EXPLAIN SELECT * FROM t WHERE a = 1 AND b = 2;
EXPLAIN SELECT * FROM t WHERE a = 1 AND b = 2 AND c = 3;
EXPLAIN SELECT * FROM t WHERE a = 1 AND c = 3;  -- 只用到了 a

-- 无效使用索引：
EXPLAIN SELECT * FROM t WHERE b = 2;  -- 没有 a
EXPLAIN SELECT * FROM t WHERE c = 3;  -- 没有 a,b
EXPLAIN SELECT * FROM t WHERE b = 2 AND c = 3;  -- 没有 a

案例5：索引失效场景

-- 1. 对索引列进行计算
EXPLAIN SELECT * FROM users WHERE YEAR(created_at) = 2023;
-- 优化：
EXPLAIN SELECT * FROM users WHERE created_at >= '2023-01-01' AND created_at < '2024-01-01';

-- 2. 对索引列使用函数
EXPLAIN SELECT * FROM users WHERE LEFT(name, 1) = '张';
-- 优化：
EXPLAIN SELECT * FROM users WHERE name LIKE '张%';

-- 3. 类型转换
-- 假设 phone 是 varchar，但有索引
EXPLAIN SELECT * FROM users WHERE phone = 13800138000;  -- 类型转换，索引失效
EXPLAIN SELECT * FROM users WHERE phone = '13800138000';  -- 使用索引

-- 4. OR 条件
EXPLAIN SELECT * FROM users WHERE age = 20 OR city = '北京';
-- 如果 age 和 city 都有单独索引，可能用 index_merge
-- 否则全表扫描

高级调优技巧

1. 使用 EXPLAIN FORMAT=JSON

EXPLAIN FORMAT=JSON SELECT * FROM users WHERE age > 20\G

返回详细的 JSON 信息，包括：成本估算、索引选择原因、更详细的执行步骤

2. 使用 EXPLAIN ANALYZE（MySQL 8.0.18+）

EXPLAIN ANALYZE SELECT * FROM users WHERE age > 20;

实际执行查询并返回：实际执行时间、实际扫描行数、循环次数、真实成本

3. 优化器提示

-- 强制使用某个索引
SELECT * FROM users FORCE INDEX(idx_age) WHERE age > 20;

-- 忽略索引
SELECT * FROM users IGNORE INDEX(idx_age) WHERE age > 20;

-- 优化 JOIN 顺序
SELECT /*+ STRAIGHT_JOIN */ * FROM a JOIN b ON a.id = b.a_id;

4. 分析索引使用情况

-- 查看索引使用统计
SELECT * FROM sys.schema_index_statistics 
WHERE table_schema = 'your_db';

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

常用系统表辅助调优

-- 1. 查看表索引
SHOW INDEX FROM users;

-- 2. 查看表统计信息
ANALYZE TABLE users;  -- 更新统计信息
SHOW TABLE STATUS LIKE 'users';

-- 3. 查看索引统计
SELECT * FROM mysql.innodb_index_stats 
WHERE table_name = 'users';

-- 4. 查看当前连接执行情况
SHOW PROCESSLIST;
SHOW ENGINE INNODB STATUS\G

-- 5. 性能库查询（MySQL 5.6+）
SELECT * FROM performance_schema.events_statements_summary_by_digest
ORDER BY sum_timer_wait DESC LIMIT 10;

总结：EXPLAIN 是手段，不是目的。最终目标是通过分析执行计划，找到性能瓶颈，然后通过合适的索引、SQL 改写、架构调整来解决问题。