| 命令 | 作用 | 關鍵指標 |
SHOW STATUS [LIKE 'pat'] | 服務器狀態變量(計數器) | Threads_connected, Questions, Slow_queries, Innodb_buffer_pool_reads, Qcache_hits |
SHOW GLOBAL STATUS | 全局狀態(需 SUPER 權限) | 同上,跨會話聚合 |
SHOW SESSION STATUS | 當前會話狀態 | 調試當前連接 |
SHOW VARIABLES [LIKE 'pat'] | 配置參數(當前生效值) | innodb_buffer_pool_size, max_connections, query_cache_size |
SHOW GLOBAL VARIABLES | 全局配置 | 持久化配置確認 |
SHOW PROCESSLIST [FULL] | 當前線程/查詢 | Id, User, Host, db, Command, Time, State, Info |
SHOW ENGINE INNODB STATUS | InnoDB 內部狀態 | 事務、鎖、死鎖、緩存池、刷盤 |
SHOW OPEN TABLES | 打開表緩存狀態 | In_use, Name_locked |
日誌與複製類
| 命令 | 作用 |
SHOW BINARY LOGS | 二進制日誌文件列表 |
SHOW MASTER STATUS | 主庫二進制日誌位置 |
SHOW SLAVE STATUS / SHOW REPLICA STATUS (8.0+) | 從庫複製狀態 |
SHOW RELAYLOG EVENTS | 中繼日誌事件 |
SHOW BINLOG EVENTS [IN 'file'] [FROM pos] [LIMIT n] | 解析二進制日誌事件 |
SHOW WARNINGS / SHOW ERRORS | 最後語句的警告/錯誤 |
實用組合拳
1. 快速健康檢查
SHOW GLOBAL STATUS LIKE 'Uptime';
SHOW GLOBAL STATUS LIKE 'Threads_connected';
SHOW GLOBAL STATUS LIKE 'Max_used_connections';
SHOW GLOBAL STATUS LIKE 'Aborted_connects';
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_wait_free';
2. 找出長事務
SELECT * FROM information_schema.INNODB_TRX
WHERE TIME_TO_SEC(TIMEDIFF(NOW(), trx_started)) > 60
ORDER BY trx_started;
3. 索引使用統計
SELECT * FROM sys.schema_unused_indexes;
SELECT * FROM sys.schema_redundant_indexes;
4. 表大小 Top 10
SELECT table_schema, table_name,
ROUND((data_length+index_length)/1024/1024,2) AS size_mb
FROM information_schema.TABLES
ORDER BY size_mb DESC LIMIT 10;
輸出格式技巧
\G 結尾:垂直顯示,適合寬表(如 SHOW ENGINE INNODB STATUS\G)
LIKE 'pattern%':支持通配符 % _
WHERE 子句(8.0+):SHOW TABLE STATUS WHERE Engine='InnoDB' AND Rows > 1000000;
權限要求
- 大多數 SHOW 需要相應對象的
SELECT 或 SHOW DATABASES 權限
SHOW PROCESSLIST 查看所有進程需 PROCESS 權限
SHOW ENGINE INNODB STATUS 需 PROCESS 權限
SHOW GRANTS FOR ... 需 SELECT ON mysql.user 或管理員
copyright ©
萬盛學電腦網 all rights reserved