MySQL DBA 巡检必备：70 个常用 SQL 脚本

适用版本：MySQL 5.7 / 8.0 / 8.4
数据库类型：关系型数据库（OLTP）
核心场景：MySQL DBA 日常巡检命令速查手册，涵盖实例状态、空间分析、锁等待诊断、主从复制、性能分析、用户权限、错误日志等核心场景

---

1｜环境说明

项目	说明
适用版本	MySQL 5.7 / 8.0 / 8.4
权限要求	PROCESS、REPLICATION CLIENT、SELECT
推荐授权	GRANT PROCESS, REPLICATION CLIENT, SELECT ON *.* TO 'dba_user'@'%';
执行方式	mysql 客户端或 DBA 工具均可执行

版本差异说明：

• MySQL 8.0+：information_schema.processlist 已废弃，统一使用 performance_schema.processlist
• MySQL 8.0+：SHOW SLAVE STATUS 已废弃（8.0.22+），统一使用 SHOW REPLICA STATUS
• MySQL 8.0+：information_schema.innodb_lock_waits 已移除，统一使用 performance_schema.data_lock_waits 或 sys.innodb_lock_waits

---

2｜实例基础信息

2.1 获取 MySQL 版本

-- 获取 MySQL 版本信息
SELECT VERSION();

-- 获取 Server UUID
SELECT @@GLOBAL.server_uuid;

-- 获取主机名
SELECT @@GLOBAL.hostname;

2.2 全局变量快照

-- MySQL 8.0+ 获取所有全局变量（排除敏感信息）
SELECT VARIABLE_NAME, VARIABLE_VALUE
FROM performance_schema.global_variables
WHERE VARIABLE_NAME NOT LIKE '%rsa_public_key%'
  AND VARIABLE_NAME <> 'caching_sha2_password_rsa_public_key'
ORDER BY VARIABLE_NAME;

2.3 全局状态快照

-- MySQL 8.0+ 获取所有全局状态
SELECT VARIABLE_NAME, VARIABLE_VALUE
FROM performance_schema.global_status
WHERE VARIABLE_NAME NOT LIKE '%rsa_public_key%'
  AND VARIABLE_NAME <> 'Caching_sha2_password_rsa_public_key'
ORDER BY VARIABLE_NAME;

2.4 连接数统计

-- 当前连接数
SHOW STATUS LIKE 'Threads_connected';

-- 历史最大连接数
SHOW STATUS LIKE 'Max_used_connections';

-- 当前配置的最大连接数
SHOW VARIABLES LIKE 'max_connections';

-- 正在执行的查询数
SHOW STATUS LIKE 'threads_running';

-- 缓存中的线程数
SHOW STATUS LIKE 'threads_cached';

2.5 字符集与时区

-- 字符集配置
SHOW VARIABLES LIKE 'character%';

-- 时区信息
SELECT @@global.time_zone;

-- 事务隔离级别
SELECT @@transaction_isolation;

---

3｜空间分析与表统计

3.1 数据库总大小

-- 数据库总大小（GB）
SELECT ROUND(SUM(data_length + index_length) / 1024 / 1024 / 1024, 2) AS 'DBSIZE_GB'
FROM information_schema.tables;

3.2 各库大小汇总

-- 各库大小汇总（排除系统库）
SELECT
    table_schema                                                             AS '数据库',
    ROUND(SUM(data_length + index_length) / 1024 / 1024 / 1024, 3) AS 'SIZE_GB',
    COUNT(*)                                                                 AS '表数量'
FROM information_schema.tables
WHERE table_schema NOT IN ('sys','mysql','information_schema','performance_schema')
GROUP BY table_schema
ORDER BY SIZE_GB DESC;

3.3 TOP 30 大表

-- TOP 30 大表（MB）
SELECT
    table_schema                                          AS '数据库',
    table_name                                            AS '表名',
    ROUND((data_length + index_length) / 1024 / 1024, 2) AS 'SIZE_MB',
    table_rows                                            AS '行数',
    engine                                                AS '引擎'
FROM information_schema.tables
WHERE table_schema NOT IN ('sys','mysql','information_schema','performance_schema')
  AND table_type = 'BASE TABLE'
ORDER BY (data_length + index_length) DESC
LIMIT 30;

3.4 碎片 Top10

-- 碎片率最高的表 Top10
SELECT
    table_schema                                                    AS '数据库',
    table_name                                                      AS '表名',
    ROUND((data_length + index_length) / 1024 / 1024, 2)           AS '总大小MB',
    ROUND(data_free / 1024 / 1024, 2)                              AS '碎片MB',
    ROUND(data_free * 100 / (data_length + index_length), 2) AS '碎片率%',
    CURDATE()                                                       AS '统计日期'
FROM information_schema.tables
WHERE table_schema NOT IN ('mysql','sys','information_schema','performance_schema')
  AND (data_length + index_length) > 0
ORDER BY data_free * 100 / (data_length + index_length) DESC
LIMIT 10;

3.5 表统计信息

-- 查看指定表的统计信息
SELECT *
FROM mysql.innodb_table_stats
WHERE database_name = 'your_database'
  AND table_name = 'your_table';

-- 手动更新统计信息
ANALYZE TABLE your_database.your_table;

3.6 索引统计信息

-- 查看指定表所有索引的统计信息
SELECT *
FROM mysql.innodb_index_stats
WHERE database_name = 'your_database'
  AND table_name = 'your_table';

---

4｜表结构与存储引擎

4.1 无主键表查询

-- 查找所有无主键表（InnoDB 强制建议有主键）
SELECT
    A.table_schema AS '数据库',
    A.table_name   AS '表名',
    A.engine       AS '引擎',
    A.table_rows   AS '行数'
FROM information_schema.tables AS A
LEFT JOIN (
    SELECT table_schema, table_name
    FROM information_schema.statistics
    WHERE index_name = 'PRIMARY'
) AS B
  ON A.table_schema = B.table_schema
 AND A.table_name = B.table_name
WHERE A.table_schema NOT IN ('information_schema','mysql','performance_schema','sys')
  AND A.table_type = 'BASE TABLE'
  AND B.table_name IS NULL
ORDER BY A.data_length DESC;

4.2 非 InnoDB 表

-- 查找非 InnoDB 引擎的表
SELECT
    table_schema AS '数据库',
    table_name   AS '表名',
    engine       AS '存储引擎',
    table_rows   AS '行数'
FROM information_schema.tables
WHERE table_schema NOT IN ('mysql','sys','information_schema','performance_schema')
  AND table_type = 'BASE TABLE'
  AND engine != 'InnoDB'
ORDER BY table_schema, table_name;

4.3 非 UTF8 字符集表

-- 查找非 UTF8 字符集的表
SELECT
    TABLE_SCHEMA AS '数据库',
    TABLE_NAME   AS '表名',
    TABLE_COLLATION AS '字符集'
FROM information_schema.TABLES
WHERE TABLE_COLLATION NOT LIKE 'utf8%'
  AND table_schema NOT IN ('information_schema','mysql','performance_schema','sys')
ORDER BY TABLE_SCHEMA, TABLE_NAME;

4.4 自增 ID 使用率 Top30

-- 自增主键使用率监控（InnoDB 最大值 9223372036854775807，其他引擎 4294967295）
SELECT
    table_schema                        AS '数据库',
    table_name                          AS '表名',
    AUTO_INCREMENT                      AS '当前值',
    CASE engine
        WHEN 'InnoDB' THEN 9223372036854775807
        ELSE 4294967295
    END                                 AS '最大值',
    ROUND(
        AUTO_INCREMENT * 100.0 / CASE engine
            WHEN 'InnoDB' THEN 9223372036854775807
            ELSE 4294967295
        END, 4
    )                                   AS '使用率%',
    engine                              AS '引擎'
FROM information_schema.tables
WHERE AUTO_INCREMENT IS NOT NULL
  AND table_schema NOT IN ('mysql','information_schema','performance_schema','sys')
ORDER BY AUTO_INCREMENT * 100.0 / CASE engine WHEN 'InnoDB' THEN 9223372036854775807 ELSE 4294967295 END DESC
LIMIT 30;

4.5 数据库对象统计

-- 统计所有数据库对象数量
SELECT '表'       AS type, COUNT(*) AS cnt FROM information_schema.tables
    WHERE table_type='BASE TABLE'
    AND table_schema NOT IN ('mysql','information_schema','performance_schema','sys')
UNION ALL
SELECT '视图', COUNT(*) FROM information_schema.views
    WHERE table_schema NOT IN ('mysql','information_schema','performance_schema','sys')
UNION ALL
SELECT '存储过程', COUNT(*) FROM information_schema.routines
    WHERE routine_type='PROCEDURE'
    AND routine_schema NOT IN ('mysql','information_schema','performance_schema','sys')
UNION ALL
SELECT '函数', COUNT(*) FROM information_schema.routines
    WHERE routine_type='FUNCTION'
    AND routine_schema NOT IN ('mysql','information_schema','performance_schema','sys')
UNION ALL
SELECT '触发器', COUNT(*) FROM information_schema.triggers
    WHERE trigger_schema NOT IN ('mysql','information_schema','performance_schema','sys');

---

5｜会话与连接管理

5.1 查看所有活动会话

-- MySQL 8.0+ 查看活动会话
SELECT
    id,
    user,
    host,
    db,
    command,
    time,
    state,
    LEFT(info, 100) AS '执行的SQL'
FROM performance_schema.processlist
WHERE command <> 'Sleep'
ORDER BY time DESC;

5.2 排除自身会话

-- 排除当前连接的活动会话
SELECT
    id,
    user,
    host,
    db,
    command,
    time,
    state,
    LEFT(info, 100) AS '执行的SQL'
FROM performance_schema.processlist
WHERE command <> 'Sleep'
  AND id <> CONNECTION_ID();

5.3 按条件过滤会话

-- 按用户过滤
SELECT * FROM performance_schema.processlist
WHERE user IN ('app_user', 'readonly_user');

-- 按主机过滤
SELECT * FROM performance_schema.processlist
WHERE host LIKE '192.168.1%';

-- 按数据库过滤
SELECT * FROM performance_schema.processlist
WHERE db = 'your_database';

5.4 连接信息（含 SQL）

-- 当前所有连接详细信息
SELECT
    ID,
    USER,
    HOST,
    DB,
    COMMAND,
    TIME,
    STATE,
    LEFT(REPLACE(REPLACE(IFNULL(INFO,''),'\n',' '),'\r',' '),200) AS INFO
FROM performance_schema.processlist
ORDER BY TIME DESC
LIMIT 100;

-- MySQL 5.7 兼容写法
-- SELECT ID, USER, HOST, DB, COMMAND, TIME, STATE, LEFT(IFNULL(INFO, ''), 200) AS INFO
-- FROM information_schema.processlist ORDER BY TIME DESC LIMIT 100;

---

6｜锁等待与事务诊断

6.1 InnoDB 锁等待（MySQL 8.0+）

-- MySQL 8.0+ 使用 performance_schema.data_lock_waits
SELECT
    r.trx_id                                 AS '等待事务ID',
    TIMESTAMPDIFF(SECOND, r.trx_wait_started, NOW()) AS '等待秒数',
    LEFT(REPLACE(rp.info,'|',';'),100)      AS '等待SQL',
    b.trx_id                                 AS '阻塞事务ID',
    LEFT(REPLACE(bp.info,'|',';'),100)       AS '阻塞SQL'
FROM performance_schema.data_lock_waits w
JOIN information_schema.innodb_trx r ON r.trx_id = w.REQUESTING_ENGINE_TRANSACTION_ID
JOIN information_schema.innodb_trx b ON b.trx_id = w.BLOCKING_ENGINE_TRANSACTION_ID
LEFT JOIN performance_schema.processlist rp ON rp.id = r.trx_mysql_thread_id
LEFT JOIN performance_schema.processlist bp ON bp.id = b.trx_mysql_thread_id
LIMIT 20;

6.2 InnoDB 锁等待（MySQL 5.7 兼容）

-- MySQL 5.7 使用 information_schema.innodb_lock_waits
SELECT
    r.trx_id                                 AS '等待事务ID',
    TIMESTAMPDIFF(SECOND, r.trx_wait_started, NOW()) AS '等待秒数',
    LEFT(REPLACE(rp.info,'|',';'),100)      AS '等待SQL',
    b.trx_id                                 AS '阻塞事务ID',
    LEFT(REPLACE(bp.info,'|',';'),100)       AS '阻塞SQL'
FROM information_schema.innodb_lock_waits w
JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id
JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
LEFT JOIN information_schema.processlist rp ON rp.id = r.trx_mysql_thread_id
LEFT JOIN information_schema.processlist bp ON bp.id = b.trx_mysql_thread_id
LIMIT 20;

6.3 sys.innodb_lock_waits 视图

-- 使用 sys 库的锁等待视图（MySQL 8.0）
SELECT
    wait_started           AS '等待开始时间',
    wait_age               AS '等待时长',
    lock_type              AS '锁类型',
    lock_mode              AS '锁模式',
    lock_status            AS '锁状态',
    lock_table             AS '锁表',
    lock_index             AS '锁索引',
    blocking_pid           AS '阻塞进程ID',
    blocking_lock_mode     AS '阻塞锁模式'
FROM sys.innodb_lock_waits\G

6.4 当前长事务（运行超60秒）

-- 当前活跃长事务（可能阻塞 purge 或导致主从延迟）
SELECT
    trx_id,
    trx_state,
    TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS '运行时长秒',
    trx_rows_locked,
    trx_rows_modified,
    LEFT(REPLACE(p.info,'|',';'),150) AS '当前SQL',
    p.user,
    p.host
FROM information_schema.innodb_trx t
LEFT JOIN performance_schema.processlist p ON p.id = t.trx_mysql_thread_id
WHERE TIMESTAMPDIFF(SECOND, trx_started, NOW()) > 60
ORDER BY trx_started DESC
LIMIT 20;

6.5 Metadata Locks 阻塞链

-- 元数据锁阻塞链分析
SELECT
    w.OBJECT_TYPE,
    IFNULL(w.OBJECT_SCHEMA,'') AS OBJECT_SCHEMA,
    IFNULL(w.OBJECT_NAME,'') AS OBJECT_NAME,
    wt.PROCESSLIST_ID AS waiting_pid,
    IFNULL(wt.PROCESSLIST_USER,'') AS waiting_user,
    IFNULL(wt.PROCESSLIST_HOST,'') AS waiting_host,
    IFNULL(wt.PROCESSLIST_TIME,0) AS waiting_secs,
    LEFT(REPLACE(REPLACE(IFNULL(wt.PROCESSLIST_INFO,''),'|',';'),'\n',' '),120) AS waiting_sql,
    bt.PROCESSLIST_ID AS blocking_pid,
    IFNULL(bt.PROCESSLIST_USER,'') AS blocking_user,
    IFNULL(bt.PROCESSLIST_HOST,'') AS blocking_host,
    IFNULL(bt.PROCESSLIST_TIME,0) AS blocking_secs,
    LEFT(REPLACE(REPLACE(IFNULL(bt.PROCESSLIST_INFO,''),'|',';'),'\n',' '),120) AS blocking_sql,
    w.LOCK_TYPE AS waiting_lock_type,
    b.LOCK_TYPE AS blocking_lock_type
FROM performance_schema.metadata_locks w
JOIN performance_schema.metadata_locks b
  ON w.OBJECT_TYPE = b.OBJECT_TYPE
 AND w.OBJECT_SCHEMA <=> b.OBJECT_SCHEMA
 AND w.OBJECT_NAME <=> b.OBJECT_NAME
 AND b.LOCK_STATUS = 'GRANTED'
 AND w.OWNER_THREAD_ID <> b.OWNER_THREAD_ID
LEFT JOIN performance_schema.threads wt ON wt.THREAD_ID = w.OWNER_THREAD_ID
LEFT JOIN performance_schema.threads bt ON bt.THREAD_ID = b.OWNER_THREAD_ID
WHERE w.LOCK_STATUS = 'PENDING'
ORDER BY waiting_secs DESC
LIMIT 30;

6.6 Metadata Locks 摘要

-- 元数据锁统计摘要
SELECT
    LOCK_STATUS,
    LOCK_TYPE,
    COUNT(*) AS cnt
FROM performance_schema.metadata_locks
GROUP BY LOCK_STATUS, LOCK_TYPE
ORDER BY LOCK_STATUS, cnt DESC;

---

7｜InnoDB 引擎状态

7.1 InnoDB 引擎整体状态

-- InnoDB 引擎整体状态（一屏展示，诊断死锁首选）
SHOW ENGINE INNODB STATUS\G

7.2 InnoDB 关键指标

-- InnoDB 全局状态
SHOW GLOBAL STATUS LIKE 'Innodb%';

-- 缓冲池配置
SHOW VARIABLES LIKE 'innodb_buffer_pool%';

-- 缓冲池页面统计
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages%';

7.3 Buffer Pool 命中率计算

-- Buffer Pool 命中率计算
-- 命中率 = 1 - (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests)
-- 正常命中率应 > 99%
SELECT
    (SELECT VARIABLE_VALUE FROM performance_schema.global_status
     WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests') AS '读请求数',
    (SELECT VARIABLE_VALUE FROM performance_schema.global_status
     WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads') AS '物理读数',
    ROUND(
        1 - (
            (SELECT VARIABLE_VALUE FROM performance_schema.global_status
             WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads')
            /
            (SELECT VARIABLE_VALUE FROM performance_schema.global_status
             WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests')
        ), 4
    ) AS '命中率';

7.4 Semaphores 信号量

-- InnoDB 信号量统计（从 SHOW ENGINE INNODB STATUS 解析）
-- 关注 Mutex spin waits, RW-shared spins, RW-excl spins
SHOW ENGINE INNODB STATUS\G

7.5 死锁信息

-- 最近死锁详情（从 SHOW ENGINE INNODB STATUS 解析 LATEST DETECTED DEADLOCK）
SHOW ENGINE INNODB STATUS\G

---

8｜主从复制状态

8.1 高可用配置总览

-- 复制相关关键参数
SELECT VARIABLE_NAME, VARIABLE_VALUE
FROM performance_schema.global_variables
WHERE VARIABLE_NAME IN (
    'read_only','super_read_only','log_bin','gtid_mode',
    'enforce_gtid_consistency','binlog_format',
    'rpl_semi_sync_source_enabled','rpl_semi_sync_replica_enabled',
    'server_id','server_uuid'
) ORDER BY VARIABLE_NAME;

8.2 主库侧副本连接

-- 主库查看从库连接列表
SELECT
    ID,
    USER,
    HOST,
    COMMAND,
    TIME,
    STATE
FROM performance_schema.processlist
WHERE COMMAND IN ('Binlog Dump','Binlog Dump GTID')
ORDER BY TIME DESC;

8.3 SHOW REPLICA STATUS（MySQL 8.0+）

-- MySQL 8.0+ 从库复制状态
SHOW REPLICA STATUS\G

8.4 SHOW SLAVE STATUS（旧版兼容）

-- MySQL 5.7 命令（8.0 已废弃但仍可运行）
SHOW SLAVE STATUS\G

8.5 异步复制 Receiver 状态

-- 复制通道接收线程状态
SELECT
    CHANNEL_NAME,
    SERVICE_STATE,
    RECEIVER_THREAD_STATE,
    LAST_ERROR_NUMBER,
    LEFT(LAST_ERROR_MESSAGE,200) AS LAST_ERROR_MESSAGE
FROM performance_schema.replication_connection_status;

8.6 异步复制 Applier 状态

-- 复制通道应用线程状态
SELECT
    CHANNEL_NAME,
    SERVICE_STATE,
    REMAINING_DELAY,
    LAST_ERROR_NUMBER,
    LEFT(LAST_ERROR_MESSAGE,200) AS LAST_ERROR_MESSAGE
FROM performance_schema.replication_applier_status;

8.7 异步复制关键参数

-- 异步复制详细参数
SELECT VARIABLE_NAME, VARIABLE_VALUE
FROM performance_schema.global_variables
WHERE VARIABLE_NAME IN (
    'server_id','log_bin','binlog_format','gtid_mode','enforce_gtid_consistency',
    'log_replica_updates','relay_log','relay_log_recovery',
    'replica_parallel_workers','replica_parallel_type','replica_preserve_commit_order',
    'replica_net_timeout','sync_binlog','sync_relay_log','sync_relay_log_info',
    'binlog_expire_logs_seconds','rpl_semi_sync_source_enabled','rpl_semi_sync_replica_enabled'
) ORDER BY VARIABLE_NAME;

8.8 主库状态

-- 主库 binlog 位置
SHOW MASTER STATUS\G

-- 查看从库主机列表
SHOW SLAVE HOSTS;

-- 查看所有复制通道（多源复制）
SHOW ALL REPLICAS STATUS;

8.9 GTID 复制诊断

-- GTID 执行状态
SELECT
    SOURCE_UUID      AS '主库UUID',
    BINLOG_FILE      AS 'binlog文件',
    BINLOG_POS       AS 'binlog位置',
    SQL_Delay        AS '延迟秒数',
    SQL_Remaining_Delay AS '剩余延迟秒数',
    Slave_SQL_Running_State AS 'SQL线程状态'
FROM mysql.slave_master_info;

---

9｜Group Replication (MGR) 状态

9.1 MGR 成员状态

-- Group Replication 成员状态
SELECT
    MEMBER_ID,
    MEMBER_HOST,
    MEMBER_PORT,
    MEMBER_STATE,
    MEMBER_ROLE
FROM performance_schema.replication_group_members
ORDER BY MEMBER_HOST, MEMBER_PORT;

9.2 MGR 关键状态

-- MGR 性能指标
SHOW GLOBAL STATUS LIKE 'group_replication_%';

9.3 MGR 关键参数

-- MGR 详细参数
SELECT VARIABLE_NAME, VARIABLE_VALUE
FROM performance_schema.global_variables
WHERE VARIABLE_NAME IN (
    'group_replication_group_name','group_replication_single_primary_mode',
    'group_replication_enforce_update_everywhere_checks','group_replication_consistency',
    'group_replication_group_seeds','group_replication_local_address',
    'group_replication_ip_allowlist','group_replication_ssl_mode',
    'group_replication_recovery_use_ssl','group_replication_exit_state_action',
    'group_replication_member_expel_timeout','group_replication_autorejoin_tries',
    'group_replication_start_on_boot','group_replication_transaction_size_limit',
    'group_replication_flow_control_mode','group_replication_paxos_single_leader'
) ORDER BY VARIABLE_NAME;

9.4 Galera/PXC 状态

-- Galera/PXC 集群状态
SELECT VARIABLE_NAME, VARIABLE_VALUE
FROM performance_schema.global_status
WHERE VARIABLE_NAME IN (
    'wsrep_cluster_size','wsrep_cluster_status','wsrep_ready','wsrep_connected',
    'wsrep_local_state_comment','wsrep_flow_control_paused',
    'wsrep_local_recv_queue_avg','wsrep_local_send_queue_avg'
) ORDER BY VARIABLE_NAME;

---

10｜慢查询分析

10.1 慢查询配置

-- 慢查询配置参数
SELECT VARIABLE_NAME, VARIABLE_VALUE
FROM performance_schema.global_variables
WHERE VARIABLE_NAME IN (
    'slow_query_log','long_query_time','log_output','slow_query_log_file',
    'log_queries_not_using_indexes','log_slow_admin_statements','min_examined_row_limit'
) ORDER BY VARIABLE_NAME;

-- 慢查询计数器
SHOW GLOBAL STATUS LIKE 'Slow_queries';

10.2 慢日志表查询（已开启 log_output=TABLE）

-- 从 mysql.slow_log 表查询（需设置 log_output=TABLE，非 FILE）
-- 设置方式：SET GLOBAL log_output = 'TABLE';
SELECT
    start_time,
    user_host,
    query_time,
    lock_time,
    rows_sent,
    rows_examined,
    LEFT(REPLACE(REPLACE(sql_text,'\n',' '),'\r',' '),200) AS sql_text
FROM mysql.slow_log
ORDER BY start_time DESC
LIMIT 20;

10.3 语句分析（sys 视图）

-- 执行耗时最多的 SQL（sys.statement_analysis）
SELECT
    db,
    exec_count,
    ROUND(total_latency/1000000000000,3) AS total_seconds,
    rows_examined,
    rows_sent,
    SUBSTRING(REPLACE(REPLACE(query,'\n',' '),'\r',' '),1,200) AS query
FROM sys.statement_analysis
ORDER BY total_seconds DESC
LIMIT 20;

10.4 语句分析（performance_schema）

-- 执行耗时最多的 SQL（performance_schema）
SELECT
    IFNULL(schema_name,'') AS db,
    COUNT_STAR AS exec_count,
    ROUND(SUM_TIMER_WAIT/1000000000000,3) AS total_seconds,
    SUM_ROWS_EXAMINED AS rows_examined,
    SUM_ROWS_SENT AS rows_sent,
    SUBSTRING(REPLACE(REPLACE(DIGEST_TEXT,'\n',' '),'\r',' '),1,200) AS digest_text
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 20;

10.5 全表扫描 SQL

-- 全表扫描 SQL（sys 视图）
SELECT
    db,
    exec_count,
    rows_sent,
    rows_examined,
    ROUND(total_latency/1000000000000,3) AS total_seconds,
    SUBSTRING(REPLACE(REPLACE(query,'\n',' '),'\r',' '),1,200) AS query
FROM sys.statements_with_full_table_scans
ORDER BY rows_examined DESC
LIMIT 20;

-- 全表扫描 SQL（performance_schema）
SELECT
    IFNULL(schema_name,'') AS db,
    COUNT_STAR AS exec_count,
    ROUND(SUM_TIMER_WAIT/1000000000000,3) AS total_seconds,
    SUM_ROWS_SENT,
    SUM_ROWS_EXAMINED,
    SUM_NO_INDEX_USED,
    SUM_NO_GOOD_INDEX_USED,
    SUBSTRING(REPLACE(REPLACE(DIGEST_TEXT,'\n',' '),'\r',' '),1,200) AS digest_text
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_ROWS_EXAMINED DESC
LIMIT 20;

10.6 临时表与排序 SQL

-- 使用临时表的 SQL
SELECT
    db,
    exec_count,
    ROUND(total_latency/1000000000000,3) AS total_seconds,
    SUBSTRING(REPLACE(REPLACE(query,'\n',' '),'\r',' '),1,200) AS query
FROM sys.statements_with_temp_tables
ORDER BY total_latency DESC
LIMIT 20;

10.7 执行次数 TOP 10

-- 执行次数最多的 SQL
SELECT
    SCHEMA_NAME                     AS '数据库',
    LEFT(DIGEST_TEXT, 80)           AS 'SQL指纹',
    COUNT_STAR                      AS '执行次数',
    ROUND(SUM_TIMER_WAIT / 1e12, 4) AS '总耗时秒',
    ROUND(AVG_TIMER_WAIT / 1e12, 4) AS '平均耗时秒',
    FIRST_SEEN                      AS '首次出现',
    LAST_SEEN                       AS '最后出现'
FROM performance_schema.events_statements_summary_by_digest
ORDER BY COUNT_STAR DESC
LIMIT 10;

10.8 平均响应时间 TOP 10

-- 平均响应时间最长的 SQL
SELECT
    LEFT(DIGEST_TEXT, 80)            AS 'SQL摘要',
    ROUND(AVG_TIMER_WAIT / 1e12, 4) AS '平均耗时秒',
    ROUND(MIN_TIMER_WAIT / 1e12, 4) AS '最小耗时秒',
    ROUND(MAX_TIMER_WAIT / 1e12, 4) AS '最大耗时秒',
    SUM_ROWS_EXAMINED               AS '扫描行数',
    SUM_ROWS_SENT                   AS '返回行数',
    COUNT_STAR                      AS '执行次数'
FROM performance_schema.events_statements_summary_by_digest
ORDER BY AVG_TIMER_WAIT DESC
LIMIT 10;

---

11｜索引诊断

11.1 未使用索引

-- 从未使用的索引（建议评估后删除）
SELECT
    object_schema AS '数据库',
    object_name   AS '表名',
    index_name    AS '索引名',
    row_scan_count AS '扫描次数'
FROM sys.schema_index_statistics
WHERE object_schema NOT IN ('performance_schema')
  AND index_name != 'PRIMARY'
  AND row_scan_count = 0
ORDER BY object_schema, object_name;

11.2 未使用索引（performance_schema）

-- 基于 performance_schema 的未使用索引查询
SELECT
    t.TABLE_SCHEMA,
    t.TABLE_NAME,
    t.INDEX_NAME,
    '自实例启动以来未被使用，建议评估是否删除' AS note
FROM information_schema.statistics t
WHERE t.TABLE_SCHEMA NOT IN ('mysql','information_schema','performance_schema','sys')
  AND t.INDEX_NAME <> 'PRIMARY'
  AND NOT EXISTS (
      SELECT 1
      FROM performance_schema.table_io_waits_summary_by_index_usage io
      WHERE io.OBJECT_SCHEMA = t.TABLE_SCHEMA
        AND io.OBJECT_NAME   = t.TABLE_NAME
        AND io.INDEX_NAME    = t.INDEX_NAME
        AND io.COUNT_FETCH   > 0
  )
LIMIT 30;

11.3 冗余索引

-- 查找冗余索引
SELECT
    s.table_schema    AS '数据库',
    s.table_name      AS '表名',
    s.index_name      AS '冗余索引',
    s.column_names    AS '冗余索引列',
    d.index_name      AS '被覆盖索引',
    d.column_names    AS '被覆盖索引列'
FROM sys.schema_redundant_indexes s
JOIN information_schema.statistics d
  ON s.table_schema = d.table_schema
 AND s.table_name = d.table_name
 AND d.index_name = s.redundant_index_name;

---

12｜Binlog 管理

12.1 Binlog 基本信息

-- 查看当前 binlog 文件列表
SHOW BINARY LOGS;

-- 查看当前 binlog 文件和位置
SHOW MASTER STATUS\G

12.2 Binlog 配置参数

-- binlog 相关参数
SHOW VARIABLES LIKE 'binlog%';

-- binlog 过期设置
SELECT @@binlog_expire_logs_seconds;
SELECT @@expire_logs_days;

-- binlog 格式检查
SELECT @@binlog_format;

12.3 Binlog 解析命令

# 查看 binlog 内容（可读格式）
mysqlbinlog --no-defaults \
    --base64-output=decode-rows \
    --verbose \
    /var/lib/mysql/mysql-bin.001026

# 只看指定位置范围的日志
mysqlbinlog --no-defaults \
    --start-position=1234 \
    --stop-position=5678 \
    /var/lib/mysql/mysql-bin.001026

# 从远程服务器拉取 binlog（实时同步）
mysqlbinlog --read-from-remote-server \
    --host=主库IP \
    --port=3306 \
    --user=rep_user \
    --password \
    --stop-never \
    mysql-bin.001026

---

13｜错误日志

13.1 Error Log 配置

-- 错误日志配置
SELECT VARIABLE_NAME, VARIABLE_VALUE
FROM performance_schema.global_variables
WHERE VARIABLE_NAME IN (
    'log_error','log_error_verbosity','log_error_services','log_timestamps'
) ORDER BY VARIABLE_NAME;

-- 错误日志状态计数
SHOW GLOBAL STATUS LIKE 'Error_log_%';

13.2 performance_schema.error_log 异常

-- MySQL 8.0+ 从 performance_schema 读取错误日志
SELECT
    logged,
    PRIO,
    ERROR_CODE,
    SUBSYSTEM,
    LEFT(REPLACE(REPLACE(DATA,'|',';'),'\n',' '),220) AS DATA
FROM performance_schema.error_log
WHERE PRIO IN ('Error','Warning','System')
ORDER BY logged DESC
LIMIT 50;

---

14｜用户与权限管理

14.1 账户列表

-- 所有用户账户信息
SELECT
    CONCAT(user,'@',host) AS account,
    IFNULL(account_locked,'N') AS locked,
    IFNULL(Super_priv,'N')     AS super_priv,
    IFNULL(password_expired,'N') AS pwd_expired
FROM mysql.user
WHERE user NOT IN ('mysql.sys','mysql.session','mysql.infoschema')
ORDER BY user;

14.2 root 远程访问检查

-- 检查 root 是否允许远程登录
SELECT COUNT(*) FROM mysql.user WHERE user='root' AND host='%';

14.3 查看用户权限

-- 查看指定用户权限
SHOW GRANTS FOR 'app_user'@'192.168.1.%';

-- 查看当前用户权限
SHOW GRANTS;
SHOW GRANTS FOR CURRENT_USER;

14.4 密码验证插件检查

-- 检查密码验证插件
SELECT COUNT(*)
FROM mysql.component
WHERE component_urn LIKE '%validate_password%';

-- 或使用 SHOW PLUGINS
SHOW PLUGINS;

---

15｜DDL 执行进度

15.1 开启 DDL 监控

-- 开启 alter 语句的 stage 采集
UPDATE performance_schema.setup_instruments
SET enabled = 'YES'
WHERE name LIKE 'stage/innodb/alter%';

MySQL 8.0 注意：setup_consumers 不支持 UPDATE，需在 my.cnf 中配置：

[mysqld]
performance-schema-consumer-events-stages-current = ON
performance-schema-consumer-events-stages-history = ON
performance-schema-consumer-events-stages-history-long = ON

15.2 查看 DDL 进度

-- 查看 DDL 执行进度
SELECT
    stmt.sql_text                                    AS '执行的SQL',
    stage.event_name                                 AS 'DDL阶段',
    CONCAT(work_completed, ' / ', work_estimated)   AS '进度',
    CONCAT(ROUND(100 * work_completed / work_estimated, 2), ' %') AS '完成百分比',
    sys.format_time(stage.timer_wait)                AS '已耗时',
    CASE WHEN work_completed > 0
         THEN CONCAT(ROUND(
             (stage.timer_end - stmt.timer_start) / 1e12
             * (work_estimated - work_completed) / work_completed, 2), ' s')
         ELSE '无法估算' END                        AS '预计剩余时间'
FROM performance_schema.events_stages_current stage
JOIN performance_schema.events_statements_current stmt
  ON stage.thread_id = stmt.thread_id
 AND stage.nesting_event_id = stmt.event_id\G

---

16｜复制安全检查

16.1 跳过错误检查

-- 检查是否配置跳过复制错误
SELECT @@replica_skip_errors;
SELECT @@slave_skip_errors;

16.2 半同步复制状态

-- 半同步复制参数
SELECT VARIABLE_NAME, VARIABLE_VALUE
FROM performance_schema.global_variables
WHERE VARIABLE_NAME IN (
    'rpl_semi_sync_source_enabled',
    'rpl_semi_sync_replica_enabled',
    'rpl_semi_sync_master_enabled',
    'rpl_semi_sync_slave_enabled'
) ORDER BY VARIABLE_NAME;

---

17｜系统信息

17.1 获取系统变量值

-- 获取指定全局变量值
SELECT @@GLOBAL.join_buffer_size;
SELECT @@GLOBAL.sort_buffer_size;
SELECT @@GLOBAL.tmp_table_size;
SELECT @@GLOBAL.max_heap_table_size;
SELECT @@GLOBAL.table_open_cache;
SELECT @@GLOBAL.max_connections;

17.2 字符集与排序规则

-- 支持的字符集
SHOW CHARACTER SET;

-- 支持的排序规则
SHOW COLLATION;

---

18｜命令速查索引

场景	代表命令
连接数据库	mysql -uroot -p -h IP -P 3306
会话状态	performance_schema.processlist
锁等待	sys.innodb_lock_waits / performance_schema.data_lock_waits
大表查询	information_schema.tables ORDER BY size DESC LIMIT 30
慢 SQL 分析	events_statements_summary_by_digest / sys.statement_analysis
索引诊断	sys.schema_redundant_indexes / sys.schema_index_statistics
无主键表	information_schema.tables LEFT JOIN statistics
主从状态	SHOW REPLICA STATUS\G（8.0+）
Binlog	SHOW MASTER STATUS / mysqlbinlog
DDL 进度	performance_schema.events_stages_current
InnoDB 状态	SHOW ENGINE INNODB STATUS\G
Buffer Pool	SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool%'
用户权限	mysql.user / SHOW GRANTS

---

19｜总结 & 注意事项

核心要点

1. MySQL 8.0 命令变化：information_schema.processlist 已废弃，统一使用 performance_schema.processlist；SHOW SLAVE STATUS 改为 SHOW REPLICA STATUS

2. 锁等待处理：超过 30 秒的锁等待应立即介入，优先 Kill 阻塞者

3. 索引维护：删除无用索引前必须用 schema_index_statistics 双重确认无业务使用

4. Buffer Pool 命中率：正常应 > 99%，低于此值需排查全表扫描

5. 长事务风险：超过 10 分钟的未提交事务可能导致主从延迟或行锁堆积

6. Binlog 清理：通过 expire_logs_days 或 PURGE BINARY LOGS 防止 binlog 撑满磁盘

建议巡检频率

检查项	建议频率
连接数与会话状态	每日多次
慢查询分析	每日
锁等待检查	每日
主从复制状态	每小时
空间增长趋势	每周
用户权限审计	每月

---