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;