慢查询日志：性能优化的起点

发现慢（慢查询日志） → 定位慢在哪（EXPLAIN / 执行分析） → 针对性修复 → 验证前后对比

-- 查看当前配置
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

-- 开启慢查询日志（无需重启）
SET GLOBAL slow_query_log = ON;
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

-- 阈值：执行超过 1 秒的语句入日志（单位支持小数，如 0.2）
SET GLOBAL long_query_time = 1;

-- 未使用索引的语句也记录（即使没到时间阈值）
SET GLOBAL log_queries_not_using_indexes = ON;

# Time: 2026-09-18T09:15:42.123456Z
# User@Host: app_user[app_user] @  [10.0.3.17]  Id: 88231
# Query_time: 3.421882  Lock_time: 0.000123 Rows_sent: 20  Rows_examined: 4823110
SET timestamp=1763186142;
SELECT * FROM orders WHERE user_id = 100 ORDER BY created_at DESC LIMIT 20;

# 按总耗时排序的前 10 类慢查询
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

# 常用参数：-s 排序键（t 总时长 / c 次数 / r 返回行 / at 平均时长）
#          -t N 只看前 N 条

pt-query-digest /var/log/mysql/slow.log > digest-report.txt

-- 启用查询性能分析
SET profiling = 1;

-- 查看最近执行的查询及 Query_ID
SHOW PROFILES;

-- 查看指定查询各阶段耗时
SHOW PROFILE CPU FOR QUERY 1;
-- 查看所有资源使用
SHOW PROFILE ALL FOR QUERY 1;

-- 1. 开启采样并制造慢查询
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 0.1;

-- （使用此前篇章建的 demo 表；数据量小时先放大，如插入 100 万行）

-- 2. 触发问题语句：函数套列 + 前置通配符双重失效
SELECT * FROM users WHERE LOWER(email) LIKE '%@example.com';

-- 3. 日志确认被抓到
--    Rows_examined 等于全表行数，Rows_sent 只有零星几条

-- 4. EXPLAIN 定位
EXPLAIN SELECT * FROM users WHERE LOWER(email) LIKE '%@example.com';
-- type=ALL：全表扫描。两个失效原因：
--   LOWER() 是函数索引课题（/mysql/290-FunctionalIndex）
--   前置 % 使 LIKE 无法走 B+ 树有序性

-- 5. 修复：后缀匹配 + 表达式归一
--    业务若是找域名后缀，改为正向存储域名冗余列或函数索引 + 后缀 LIKE
SELECT * FROM users WHERE LOWER(email) LIKE 'zhang%';   -- 前缀匹配可走索引

-- 6. 验证：日志里不再出现该指纹，Query_time 从秒级降到毫秒级

-- 至少扫描多少行才记录（过滤掉小表误报）
SET GLOBAL min_examined_row_limit = 100;

-- 日志写表还是写文件（写表方便 SQL 分析，写文件方便工具采集）
SET GLOBAL log_output = 'TABLE';     -- 后 SELECT * FROM mysql.slow_log
SET GLOBAL log_output = 'FILE';      -- 后走 slow_query_log_file

-- 案例 1：全表扫描 -> 加索引（最常见的结构性慢因）
-- 慢：EXPLAIN 显示 type=ALL, rows=1000000
SELECT * FROM orders WHERE customer_id = 1001;
CREATE INDEX idx_customer ON orders(customer_id);
-- 优化后：type=ref, rows=50

-- 案例 2：索引列套函数 -> 改写查询或函数索引
-- 慢：YEAR() 让索引失效，type=ALL
SELECT * FROM users WHERE YEAR(created_at) = 2024;
-- 改写为范围条件（索引可用）：
SELECT * FROM users
WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';
-- 或 8.0.13+ 直接建函数索引：
CREATE INDEX idx_year ON users ((YEAR(created_at)));

-- 案例 3：OR 两侧不同列 -> UNION 改写
-- 慢：OR 让优化器只能全表扫
SELECT * FROM orders WHERE customer_id = 1001 OR status = 'urgent';
-- 改写为两条各走各的索引：
SELECT * FROM orders WHERE customer_id = 1001
UNION
SELECT * FROM orders WHERE status = 'urgent' AND customer_id != 1001;