1. 按平均执行时间排序的慢查询

SELECT SCHEMA_NAME as '数据库名',LEFT(DIGEST_TEXT, 150) as 'SQL语句摘要',COUNT_STAR as '执行次数',ROUND(AVG_TIMER_WAIT/1000000000000, 4) as '平均执行时间(秒)',ROUND(SUM_TIMER_WAIT/1000000000000, 4) as '总执行时间(秒)',ROUND(MAX_TIMER_WAIT/1000000000000, 4) as '最大执行时间(秒)',ROUND(MIN_TIMER_WAIT/1000000000000, 4) as '最小执行时间(秒)',CASE WHEN COUNT_STAR > 0 THEN ROUND(SUM_ROWS_EXAMINED/COUNT_STAR, 0)ELSE 0 END as '平均扫描行数',CASE WHEN COUNT_STAR > 0 THEN ROUND(SUM_ROWS_SENT/COUNT_STAR, 0)ELSE 0 END as '平均返回行数',FIRST_SEEN as '首次出现',LAST_SEEN as '最后出现'
FROM performance_schema.events_statements_summary_by_digest 
WHERE SCHEMA_NAME IS NOT NULL AND SCHEMA_NAME NOT IN ('information_schema', 'performance_schema', 'mysql', 'sys')AND DIGEST_TEXT IS NOT NULLAND COUNT_STAR > 0AND AVG_TIMER_WAIT > 1000000000  -- 平均执行时间超过1毫秒
ORDER BY AVG_TIMER_WAIT DESC
LIMIT 20;

---

2. 按总执行时长排序的慢查询

SELECT SCHEMA_NAME as '数据库名',LEFT(DIGEST_TEXT, 120) as 'SQL语句摘要',COUNT_STAR as '执行次数',ROUND(AVG_TIMER_WAIT/1000000000000, 4) as '平均执行时间(秒)',ROUND(SUM_TIMER_WAIT/1000000000000, 4) as '总执行时间(秒)',ROUND(MAX_TIMER_WAIT/1000000000000, 4) as '最大执行时间(秒)',CASE WHEN COUNT_STAR > 0 THEN ROUND(SUM_ROWS_EXAMINED/COUNT_STAR, 0)ELSE 0 END as '平均扫描行数',-- MySQL 5.7 不支持窗口函数，使用子查询计算比例ROUND((SUM_TIMER_WAIT / (SELECT SUM(SUM_TIMER_WAIT) FROM performance_schema.events_statements_summary_by_digest WHERE SCHEMA_NAME IS NOT NULL AND SCHEMA_NAME NOT IN ('information_schema', 'performance_schema', 'mysql', 'sys'))) * 100, 2) as '占总时间比例(%)'
FROM performance_schema.events_statements_summary_by_digest 
WHERE SCHEMA_NAME IS NOT NULL AND SCHEMA_NAME NOT IN ('information_schema', 'performance_schema', 'mysql', 'sys')AND DIGEST_TEXT IS NOT NULLAND COUNT_STAR > 0AND SUM_TIMER_WAIT > 5000000000000  -- 总执行时间超过5秒
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 20;

---

3. MySQL 5.7 慢查询配置检查

-- 查看慢查询相关配置
SHOW VARIABLES LIKE '%slow%';-- 查看慢查询时间阈值
SHOW VARIABLES LIKE 'long_query_time';-- 查看performance_schema配置状态
SHOW VARIABLES LIKE 'performance_schema';-- 检查performance_schema相关表是否启用
SELECT TABLE_NAME, ENABLED 
FROM performance_schema.setup_instruments 
WHERE NAME LIKE '%statement%' AND NAME LIKE '%sql%';-- 检查consumers是否启用
SELECT NAME, ENABLED 
FROM performance_schema.setup_consumers 
WHERE NAME LIKE '%statements%';

---

4. 扫描行数分析（找出全表扫描）

SELECT SCHEMA_NAME as '数据库名',LEFT(DIGEST_TEXT, 100) as 'SQL语句摘要',COUNT_STAR as '执行次数',ROUND(AVG_TIMER_WAIT/1000000000000, 4) as '平均执行时间(秒)',SUM_ROWS_EXAMINED as '总扫描行数',CASE WHEN COUNT_STAR > 0 THEN ROUND(SUM_ROWS_EXAMINED/COUNT_STAR, 0)ELSE 0 END as '平均扫描行数',SUM_ROWS_SENT as '总返回行数',CASE WHEN SUM_ROWS_EXAMINED > 0 THEN ROUND(SUM_ROWS_SENT/SUM_ROWS_EXAMINED*100, 2)ELSE 0 END as '扫描效率(%)',CASE WHEN SUM_ROWS_SENT > 0 THEN ROUND(SUM_ROWS_EXAMINED/SUM_ROWS_SENT, 0)ELSE SUM_ROWS_EXAMINED END as '扫描/返回比例'
FROM performance_schema.events_statements_summary_by_digest 
WHERE SCHEMA_NAME IS NOT NULL AND SCHEMA_NAME NOT IN ('information_schema', 'performance_schema', 'mysql', 'sys')AND DIGEST_TEXT IS NOT NULLAND COUNT_STAR > 0AND SUM_ROWS_EXAMINED > 100000  -- 扫描行数超过10万
ORDER BY SUM_ROWS_EXAMINED DESC
LIMIT 15;

---

5. 高频执行的慢查询

SELECT SCHEMA_NAME as '数据库名',LEFT(DIGEST_TEXT, 120) as 'SQL语句摘要',COUNT_STAR as '执行次数',ROUND(AVG_TIMER_WAIT/1000000000000, 4) as '平均执行时间(秒)',ROUND(SUM_TIMER_WAIT/1000000000000, 4) as '总执行时间(秒)',ROUND(AVG_TIMER_WAIT/1000000, 2) as '平均执行时间(毫秒)',CASE WHEN COUNT_STAR > 0 THEN ROUND(SUM_ROWS_EXAMINED/COUNT_STAR, 0)ELSE 0 END as '平均扫描行数',DATE(FIRST_SEEN) as '首次出现日期',DATE(LAST_SEEN) as '最后出现日期',TIMESTAMPDIFF(DAY, FIRST_SEEN, LAST_SEEN) as '持续天数'
FROM performance_schema.events_statements_summary_by_digest 
WHERE SCHEMA_NAME IS NOT NULL AND SCHEMA_NAME NOT IN ('information_schema', 'performance_schema', 'mysql', 'sys')AND DIGEST_TEXT IS NOT NULLAND COUNT_STAR > 1000  -- 执行次数超过1000次AND AVG_TIMER_WAIT > 100000000  -- 平均执行时间超过100毫秒
ORDER BY COUNT_STAR DESC, AVG_TIMER_WAIT DESC
LIMIT 15;

---

6. 当前正在执行的查询

SELECT p.ID as '进程ID',p.USER as '用户',p.HOST as '主机',p.DB as '数据库',p.COMMAND as '命令类型',p.TIME as '执行时间(秒)',p.STATE as '状态',LEFT(IFNULL(p.INFO, ''), 200) as 'SQL语句',CASE WHEN p.TIME > 60 THEN '极慢'WHEN p.TIME > 10 THEN '慢'WHEN p.TIME > 1 THEN '一般'ELSE '正常'END as '性能等级'
FROM information_schema.PROCESSLIST p
WHERE p.COMMAND != 'Sleep'AND p.TIME > 1  -- 执行时间超过1秒AND p.ID != CONNECTION_ID()  -- 排除当前连接
ORDER BY p.TIME DESC;

---

7. 慢查询统计汇总

SELECT '指标类型' as metric_type,'数值' as metric_value,'单位' as unit
FROM (SELECT 1 as dummy) t
WHERE 1=0  -- 创建表头UNION ALLSELECT '总查询类型数',CAST(COUNT(*) as CHAR),'个'
FROM performance_schema.events_statements_summary_by_digest 
WHERE SCHEMA_NAME IS NOT NULL AND SCHEMA_NAME NOT IN ('information_schema', 'performance_schema', 'mysql', 'sys')UNION ALLSELECT '慢查询类型数(>1秒)',CAST(COUNT(*) as CHAR),'个'
FROM performance_schema.events_statements_summary_by_digest 
WHERE SCHEMA_NAME IS NOT NULL AND SCHEMA_NAME NOT IN ('information_schema', 'performance_schema', 'mysql', 'sys')AND AVG_TIMER_WAIT > 1000000000000UNION ALLSELECT '极慢查询类型数(>10秒)',CAST(COUNT(*) as CHAR),'个'
FROM performance_schema.events_statements_summary_by_digest 
WHERE SCHEMA_NAME IS NOT NULL AND SCHEMA_NAME NOT IN ('information_schema', 'performance_schema', 'mysql', 'sys')AND AVG_TIMER_WAIT > 10000000000000UNION ALLSELECT '总执行次数',CAST(SUM(COUNT_STAR) as CHAR),'次'
FROM performance_schema.events_statements_summary_by_digest 
WHERE SCHEMA_NAME IS NOT NULL AND SCHEMA_NAME NOT IN ('information_schema', 'performance_schema', 'mysql', 'sys')UNION ALLSELECT '总执行时间',CAST(ROUND(SUM(SUM_TIMER_WAIT)/1000000000000/3600, 2) as CHAR),'小时'
FROM performance_schema.events_statements_summary_by_digest 
WHERE SCHEMA_NAME IS NOT NULL AND SCHEMA_NAME NOT IN ('information_schema', 'performance_schema', 'mysql', 'sys');

---

8. 表结构和索引分析

8.1 表索引详情查询

SELECT s.TABLE_SCHEMA as '数据库',s.TABLE_NAME as '表名',s.INDEX_NAME as '索引名',s.COLUMN_NAME as '列名',s.SEQ_IN_INDEX as '索引位置',s.CARDINALITY as '基数',s.NULLABLE as '可为空',CASE s.INDEX_TYPEWHEN 'BTREE' THEN 'B树索引'WHEN 'HASH' THEN '哈希索引'WHEN 'FULLTEXT' THEN '全文索引'ELSE s.INDEX_TYPEEND as '索引类型',CASE WHEN s.INDEX_NAME = 'PRIMARY' THEN '主键'WHEN s.NON_UNIQUE = 0 THEN '唯一索引'ELSE '普通索引'END as '索引分类'
FROM information_schema.STATISTICS s
WHERE s.TABLE_SCHEMA NOT IN ('information_schema', 'performance_schema', 'mysql', 'sys')
ORDER BY s.TABLE_SCHEMA, s.TABLE_NAME, s.INDEX_NAME, s.SEQ_IN_INDEX;

8.2 表大小统计

SELECT t.TABLE_SCHEMA as '数据库',t.TABLE_NAME as '表名',t.ENGINE as '存储引擎',IFNULL(t.TABLE_ROWS, 0) as '估算行数',ROUND(IFNULL(t.DATA_LENGTH, 0)/1024/1024, 2) as '数据大小(MB)',ROUND(IFNULL(t.INDEX_LENGTH, 0)/1024/1024, 2) as '索引大小(MB)',ROUND((IFNULL(t.DATA_LENGTH, 0) + IFNULL(t.INDEX_LENGTH, 0))/1024/1024, 2) as '总大小(MB)',t.AUTO_INCREMENT as '自增值',t.CREATE_TIME as '创建时间',t.UPDATE_TIME as '更新时间'
FROM information_schema.TABLES t
WHERE t.TABLE_SCHEMA NOT IN ('information_schema', 'performance_schema', 'mysql', 'sys')AND t.TABLE_TYPE = 'BASE TABLE'
ORDER BY (IFNULL(t.DATA_LENGTH, 0) + IFNULL(t.INDEX_LENGTH, 0)) DESC
LIMIT 20;