SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

-- 開啟慢查詢?nèi)罩?
SET GLOBAL slow_query_log = 'ON';

-- 設(shè)置閾值：超過 2 秒算慢 SQL
SET GLOBAL long_query_time = 2;

-- 設(shè)置日志輸出到表（推薦，方便直接查詢）
SET GLOBAL log_output = 'TABLE';

[mysqld]
slow_query_log = 1
long_query_time = 2
log_output = TABLE
log_queries_not_using_indexes = 1   # 同時記錄未使用索引的 SQL

SELECT
    start_time,
    ROUND(TIME_TO_SEC(query_time), 3) AS 執(zhí)行秒數(shù),
    ROUND(TIME_TO_SEC(lock_time), 3)  AS 鎖等待秒數(shù),
    rows_examined                      AS 掃描行數(shù),
    rows_sent                          AS 返回行數(shù),
    db                                 AS 數(shù)據(jù)庫,
    sql_text                           AS SQL內(nèi)容
FROM mysql.slow_log
ORDER BY query_time DESC
LIMIT 20;

SELECT * FROM mysql.slow_log
WHERE db = 'your_database'
ORDER BY query_time DESC
LIMIT 20;

SELECT
    DIGEST_TEXT                                           AS SQL摘要,
    COUNT_STAR                                            AS 執(zhí)行次數(shù),
    ROUND(AVG_TIMER_WAIT / 1000000000000, 3)             AS 平均耗時秒,
    ROUND(MAX_TIMER_WAIT / 1000000000000, 3)             AS 最大耗時秒,
    ROUND(SUM_TIMER_WAIT / 1000000000000, 3)             AS 總耗時秒,
    SUM_ROWS_EXAMINED                                     AS 總掃描行數(shù)
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

-- 查詢最耗時的 SQL（按總耗時排序）
SELECT * FROM sys.statement_analysis
ORDER BY total_latency DESC
LIMIT 10;

-- 查詢?nèi)頀呙枳疃嗟?SQL
SELECT * FROM sys.statements_with_full_table_scans
ORDER BY no_index_used_count DESC
LIMIT 10;

EXPLAIN SELECT * FROM orders WHERE user_id = 123 AND status = 1;

-- MySQL 8.0+ 支持更詳細(xì)的 EXPLAIN ANALYZE
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 123 AND status = 1;

-- 查看慢查詢配置
SHOW VARIABLES LIKE 'slow%';

-- 開啟慢查詢?nèi)罩?
SET GLOBAL slow_query_log = 'ON';

-- 設(shè)置慢查詢閾值（秒）
SET GLOBAL long_query_time = 2;

-- 查詢慢 SQL 列表
SELECT * FROM mysql.slow_log ORDER BY query_time DESC LIMIT 20;

-- 查 TOP 10 耗時 SQL
SELECT * FROM sys.statement_analysis LIMIT 10;

-- 查全表掃描的 SQL
SELECT * FROM sys.statements_with_full_table_scans LIMIT 10;

-- 分析執(zhí)行計劃
EXPLAIN SELECT ...;

-- 詳細(xì)執(zhí)行計劃（MySQL 8.0+）
EXPLAIN ANALYZE SELECT ...;

-- 清空慢日志表
TRUNCATE TABLE mysql.slow_log;