慢查询日志

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

-- 查看慢查询时间阈值
SHOW VARIABLES LIKE 'long_query_time';

-- 临时开启慢查询日志（需 SUPER 权限，重启后失效）
SET GLOBAL slow_query_log = 'ON';

-- 设置慢查询日志文件路径
SET GLOBAL slow_query_log_file = '/var/lib/mysql/slow.log';

-- 设置慢查询时间阈值（超过 1 秒视为慢查询）
SET GLOBAL long_query_time = 1;

-- 开启记录未使用索引的查询（强烈建议开启）
SET GLOBAL log_queries_not_using_indexes = 'ON';

-- 验证配置是否生效
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

-- 准备测试数据：创建一个大表
CREATE TABLE slow_test_table (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status INT DEFAULT 0
);

-- 插入大量数据（使用存储过程或批量插入）
INSERT INTO slow_test_table (name, description, status)
SELECT 
    CONCAT('user_', seq),
    CONCAT('description_', seq, '_', REPEAT('x', 100)),
    FLOOR(RAND() * 10)
FROM (
    SELECT @row := @row + 1 AS seq
    FROM (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3) t1,
         (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3) t2,
         (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3) t3,
         (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3) t4,
         (SELECT @row := 0) t5
) numbers
WHERE seq <= 100000;

-- 执行一条可能很慢的查询（全表扫描）
SELECT * FROM slow_test_table WHERE status = 5 ORDER BY created_at DESC LIMIT 100;

-- 查看当前阈值
SHOW VARIABLES LIKE 'long_query_time';

-- 临时调整为 0.5 秒（用于排查问题时捕获更多查询）
SET GLOBAL long_query_time = 0.5;

-- 验证修改
SHOW VARIABLES LIKE 'long_query_time';

-- 排查完成后恢复为 1 秒
SET GLOBAL long_query_time = 1;

-- 确保慢查询日志已开启
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 0.1;

-- 执行一个故意很慢的查询：使用 SLEEP 函数
SELECT SLEEP(0.5), 'this is a slow query' AS msg;

-- 确保开启记录未使用索引的查询
SET GLOBAL log_queries_not_using_indexes = 'ON';

-- 执行一个未走索引的查询（假设 status 列没有索引）
SELECT COUNT(*) FROM slow_test_table WHERE status = 3;

-- 为 status 列添加索引
CREATE INDEX idx_status ON slow_test_table(status);

-- 再次执行相同查询
SELECT COUNT(*) FROM slow_test_table WHERE status = 3;

-- 查看 statements_summary_by_digest 表（需提前开启 performance_schema）
SELECT 
    DIGEST_TEXT AS query,
    COUNT_STAR AS exec_count,
    SUM_TIMER_WAIT/1000000000000 AS total_latency_sec,
    AVG_TIMER_WAIT/1000000000000 AS avg_latency_sec,
    SUM_ROWS_EXAMINED AS rows_examined,
    SUM_ROWS_SENT AS rows_sent
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 5;