#慢查询故障实战复盘

-- 查看慢查询日志配置
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';
SHOW VARIABLES LIKE 'log_queries%';

-- 设置慢查询阈值为100ms
SET GLOBAL long_query_time = 0.1;
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL slow_query_log_file = '/var/lib/mysql/slow.log';

-- 查看慢查询数量
SHOW STATUS LIKE 'Slow_queries';
-- +---------------+-------+
-- | Variable_name | Value |
-- +---------------+-------+
-- | Slow_queries  | 1234  |
-- +---------------+-------+

-- 分析SQL执行计划
EXPLAIN SELECT * FROM orders WHERE user_id = 123 AND status = 1 ORDER BY create_time DESC LIMIT 10;

-- 输出：
-- +----+-------------+--------+------------+-------+--------------------------+--------------------------+---------+--------+---------+----------+----------+-------------+
-- | id | select_type| table  | type       | key   | key_len                 | ref                      | rows    | filtered| Extra  |
-- +----+-------------+--------+------------+-------+--------------------------+--------------------------+---------+--------+---------+----------+----------+-------------+
-- |  1 | SIMPLE     | orders | ref        | idx_user_status | 8     | const,const              | 1500    | 100.00 | Using index condition; Using filesort |
-- +----+-------------+--------+------------+-------+--------------------------+--------------------------+---------+--------+---------+----------+----------+-------------+

-- 关键字段解读：

-- type（访问类型，从好到差）：
-- - const：主键或唯一索引，等值查询，最多1条
-- - eq_ref：主键或唯一索引，非等值查询
-- - ref：普通索引，等值查询
-- - range：索引范围查询
-- - index：索引全扫描
-- - ALL：全表扫描（最差！）

-- key：实际使用的索引

-- rows：扫描的行数，越少越好

-- Extra（额外信息）：
-- - Using filesort：需要额外排序（需要优化！）
-- - Using temporary：需要临时表（需要优化！）
-- - Using index：覆盖索引，不需要回表
-- - Using index condition：索引下推

-- 问题1：全表扫描
SELECT * FROM orders WHERE status = 1;
-- type = ALL, rows = 500000
-- 解决：status字段添加索引

-- 问题2：索引失效
SELECT * FROM orders WHERE status = 1 AND LEFT(create_time, 7) = '2024-01';
-- 解决：避免函数运算，改用范围查询

-- 问题3：最左前缀匹配
CREATE INDEX idx_a_b_c ON table(a, b, c);
SELECT * FROM table WHERE b = 1;  -- 索引失效！
SELECT * FROM table WHERE a = 1;  -- 索引生效
SELECT * FROM table WHERE a = 1 AND b = 1;  -- 索引生效
SELECT * FROM table WHERE a = 1 AND c = 1;  -- 部分生效

-- 问题4：隐式类型转换
CREATE TABLE users (user_id VARCHAR(20));
SELECT * FROM users WHERE user_id = 123;
-- user_id是字符串，传入数字会全表扫描！

-- 场景：订单查询优化
-- 原SQL
SELECT * FROM orders
WHERE user_id = 123
  AND status IN (1, 2, 3)
  AND create_time BETWEEN '2024-01-01' AND '2024-01-31'
ORDER BY create_time DESC
LIMIT 100;

-- 分析执行计划
EXPLAIN SELECT * FROM orders
WHERE user_id = 123
  AND status IN (1, 2, 3)
  AND create_time BETWEEN '2024-01-01' AND '2024-01-31'
ORDER BY create_time DESC
LIMIT 100;

-- type = ALL（全表扫描！）需要优化

-- 优化1：添加复合索引
ALTER TABLE orders ADD INDEX idx_user_status_time
    (user_id, status, create_time DESC);

-- 优化2：改写SQL
-- IN改为多个OR
SELECT * FROM orders
WHERE user_id = 123 AND status = 1 AND create_time BETWEEN '2024-01-01' AND '2024-01-31'
UNION ALL
SELECT * FROM orders
WHERE user_id = 123 AND status = 2 AND create_time BETWEEN '2024-01-01' AND '2024-01-31'
UNION ALL
SELECT * FROM orders
WHERE user_id = 123 AND status = 3 AND create_time BETWEEN '2024-01-01' AND '2024-01-31';

-- 优化3：只查需要的字段
SELECT id, user_id, amount, status, create_time FROM orders
WHERE user_id = 123
  AND status IN (1, 2, 3)
  AND create_time BETWEEN '2024-01-01' AND '2024-01-31'
ORDER BY create_time DESC
LIMIT 100;

-- 查看索引使用情况
SELECT index_name, cardinality, seq_in_index
FROM information_schema.statistics
WHERE table_schema = 'ecommerce' AND table_name = 'orders'
ORDER BY index_name, seq_in_index;

-- 分析表（更新索引统计）
ANALYZE TABLE orders;

-- 检查索引是否被使用
SHOW STATUS LIKE 'Handler_read%';
-- Handler_read_first: 索引第一条的读取次数
-- Handler_read_key: 按索引读取次数
-- Handler_read_last: 索引最后一条的读取次数
-- Handler_read_next: 按索引顺序读取下一条
-- Handler_read_prev: 按索引顺序读取上一条
-- Handler_read_rnd: 基于固定位置读取
-- Handler_read_rnd_next: 全表扫描读取

-- 删除冗余索引
SHOW INDEX FROM orders;
-- 查看索引列表，找出重复索引
-- DROP INDEX idx_user ON orders;