MySQL8.4必备脚本：25+条高频运维SQL脚本，效率翻倍！

俊才

发布于 2026-01-19 14:03:18

730

文章被收录于专栏：数据库干货铺数据库干货铺

随着 MySQL 8.x 系列的全面普及，MySQL 8.4 已成为企业级数据库部署的主流选择。新版本在性能、安全、高可用和可维护性方面都有显著提升，但对 DBA 的运维能力也提出了更高要求。

日常工作中，你是否经常遇到这些问题：

数据库突然变慢，却不知道是哪个 SQL 在“作妖”？
主从复制延迟飙升，排查无从下手？
表空间暴涨，却找不到“罪魁祸首”？
用户权限混乱，存在安全隐患？

别慌！今天为你整理了一份 MySQL 8.4 DBA高频运维SQL脚本清单，涵盖连接管理、性能监控、锁分析、复制状态、空间统计、权限审计等八大场景，共25+条实用脚本，助你快速定位问题、提升运维效率！

一、连接与会话管理

查看活跃连接（排除 Sleep及自己）

SELECT * FROM performance_schema.processlist
WHERE COMMAND != 'Sleep' and id<>connection_id();

除了上面的方式，还可以按照如下方式查看：

SELECT th.PROCESSLIST_ID, th.PROCESSLIST_USER, th.PROCESSLIST_HOST,
       esh.EVENT_NAME, esh.SQL_TEXT, esh.TIMER_WAIT
FROM performance_schema.events_statements_current esh
JOIN performance_schema.threads th ON esh.THREAD_ID = th.THREAD_ID
WHERE esh.SQL_Text IS NOT NULL;

mysql> show processlist;
+----+-----------------+----------------------+--------+---------+------+------------------------+---------------------+
| Id | User            | Host                 | db     | Command | Time | State                  | Info                |
+----+-----------------+----------------------+--------+---------+------+------------------------+---------------------+
|  5 | event_scheduler | localhost            | NULL   | Daemon  | 1319 | Waiting on empty queue | NULL                |
|  8 | root            | localhost            | testdb | Query   |    0 | init                   | show processlist    |
|  9 | test            | 192.168.56.102:43772 | NULL   | Query   |  749 | User sleep             | select sleep(2000)  |
| 10 | test            | 10.0.2.15:49404      | NULL   | Query   |  279 | User sleep             | select sleep(30000) |
+----+-----------------+----------------------+--------+---------+------+------------------------+---------------------+
4 rows in set, 1 warning (0.00 sec)

mysql> show  full processlist;
+----+-----------------+----------------------+--------+---------+------+------------------------+------------------------+
| Id | User            | Host                 | db     | Command | Time | State                  | Info                   |
+----+-----------------+----------------------+--------+---------+------+------------------------+------------------------+
|  5 | event_scheduler | localhost            | NULL   | Daemon  | 1325 | Waiting on empty queue | NULL                   |
|  8 | root            | localhost            | testdb | Query   |    0 | init                   | show  full processlist |
|  9 | test            | 192.168.56.102:43772 | NULL   | Query   |  755 | User sleep             | select sleep(2000)     |
| 10 | test            | 10.0.2.15:49404      | NULL   | Query   |  285 | User sleep             | select sleep(30000)    |
+----+-----------------+----------------------+--------+---------+------+------------------------+------------------------+
4 rows in set, 1 warning (0.00 sec)

2. 按用户统计连接数

mysql> SELECT USER, left(HOST,instr(HOST,':')-1)host, COUNT(*) AS conn_count  FROM performance_schema.processlist  GROUP BY USER, HOST  ORDER BY conn_count DESC;

3. 找出运行超 60 秒的“慢查询”

SELECT * FROM performance_schema.processlist 
WHERE TIME > 60 AND COMMAND != 'Sleep';

4. 安全生成 KILL 命令（避免误杀）

SELECT CONCAT('KILL ', ID, ';') AS kill_cmd 
FROM performance_schema.processlist 
WHERE USER = 'test' AND TIME > 300;

✅ 提示：执行前务必确认会话内容，避免中断关键业务！

二、性能与资源监控

5. 实时查看正在执行的 SQL

除了上面的方式，还可以按照如下方式查看

SELECT th.PROCESSLIST_ID, th.PROCESSLIST_USER, th.PROCESSLIST_HOST,
       esh.EVENT_NAME, esh.SQL_TEXT, esh.TIMER_WAIT
FROM performance_schema.events_statements_current esh
JOIN performance_schema.threads th ON esh.THREAD_ID = th.THREAD_ID
WHERE esh.SQL_Text IS NOT NULL;

6. Top 10 最耗时 SQL（历史汇总）

SELECT DIGEST_TEXT, COUNT_STAR, AVG_TIMER_WAIT/1e12 AS avg_sec,  SUM_TIMER_WAIT/1e12 AS total_sec
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC LIMIT 10;

7. InnoDB 缓冲池使用情况

SELECT POOL_ID, POOL_SIZE, FREE_BUFFERS, DATABASE_PAGES, MODIFIED_DATABASE_PAGES
FROM information_schema.INNODB_BUFFER_POOL_STATS;

三、锁与阻塞分析（DBA 最头疼的问题！）

8. 查看当前锁等待关系（查看谁在等谁）

SELECT r.trx_id waiting_trx_id,
       r.trx_mysql_thread_id waiting_thread,
       r.trx_query waiting_query,
       b.trx_id blocking_trx_id,
       b.trx_mysql_thread_id blocking_thread,
       b.trx_query blocking_query
FROM performance_schema.data_lock_waits w
JOIN information_schema.INNODB_TRX b ON b.trx_id = w.BLOCKING_ENGINE_TRANSACTION_ID
JOIN information_schema.INNODB_TRX r ON r.trx_id = w.REQUESTING_ENGINE_TRANSACTION_ID;

9. 查看所有行锁持有情况

SELECT * FROM performance_schema.data_locks;

10. 检查是否有死锁

SHOW ENGINE INNODB STATUS\G

输出中搜索 LATEST DETECTED DEADLOCK 即可定位。

我编写了一个自动抓取并将告警死锁及具体死锁内容的程序，如果需要可联系我获取

四、复制与高可用（MGR / 主从）

11. 传统主从复制状态

MySQL8.4已经禁用了旧的show slave status命令，现在只能用新命令进行查询

SHOW REPLICA STATUS\G

12. Group Replication 成员状态（InnoDB Cluster）

SELECT * FROM performance_schema.replication_group_members;
SELECT * FROM performance_schema.replication_group_member_stats;

13. 复制延迟监控（基于心跳）

mysql> SELECT CHANNEL_NAME, 
    ->        (UNIX_TIMESTAMP() - UNIX_TIMESTAMP(LAST_HEARTBEAT_TIMESTAMP)) AS delay_sec
    -> FROM performance_schema.replication_connection_status;
+--------------+-----------+
| CHANNEL_NAME | delay_sec |
+--------------+-----------+
|              | 14.999999 |
+--------------+-----------+
1 row in set (0.00 sec)

五、空间与存储分析

14. 按库统计数据大小（GB）

mysql> SELECT table_schema AS db,
    ->        ROUND(SUM(data_length + index_length) / 1024 / 1024 / 1024, 2) AS size_gb
    -> FROM information_schema.tables
    -> GROUP BY table_schema
    -> ORDER BY size_gb DESC;
+--------------------+---------+
| db                 | size_gb |
+--------------------+---------+
| mysql              |    0.00 |
| information_schema |    0.00 |
| performance_schema |    0.00 |
| sys                |    0.00 |
| testdb             |    0.00 |
+--------------------+---------+
5 rows in set (0.37 sec)

注：上述脚本没有算上表碎片（data_free）,如果需要计算可以加上

15. 找出最大的 10 张表

SELECT table_schema, table_name,
       ROUND((data_length + index_length) / 1024 / 1024 / 1024, 2) AS size_gb
FROM information_schema.tables
ORDER BY (data_length + index_length) DESC
LIMIT 10;

注：同上，脚本没有算上表碎片（data_free）,如果需要计算可以加上

16. 估算表碎片率（InnoDB）


SELECT table_schema, table_name,
       ROUND(100 * data_free / (data_length + index_length + data_free), 2) AS frag_pct
FROM information_schema.tables
WHERE data_free > 0 AND table_schema NOT IN ('mysql', 'sys')

六、用户与权限审计

17. 列出所有用户及状态

SELECT User, Host, Account_locked, Password_expired  FROM mysql.user;

18. 查看某用户权限

SHOW GRANTS FOR 'test'@'%';

19. 找出从未登录过的“僵尸账户”

SELECT u.User, u.Host
FROM mysql.user u
LEFTJOIN performance_schema.accounts a ON u.User = a.USER AND u.Host = a.HOST
WHERE a.USER ISNULL;

20. 查看角色分配（MySQL 8.0+ 特性）

SELECT * FROM mysql.role_edges;

七、配置与参数检查

21. 快速检查关键参数(节选）

需要生产环境相关参数检查的完整脚本请关注我的公众号“数据库干货铺”联系我获取。

SHOW VARIABLES WHERE Variable_name IN (
  'innodb_buffer_pool_size',
  'max_connections',
  'log_bin',
  'binlog_format',
  'gtid_mode',
  'default_authentication_plugin'
);

八、其他实用脚本

22. 自增列使用率（预防溢出！）

-- 自动计算 TINYINT/INT/BIGINT 等类型的使用百分比
SELECT t.table_schema, t.table_name, c.column_name, t.auto_increment,
       ROUND(100 * t.auto_increment / 
         CASE data_type
           WHEN 'tinyint' THEN 255
           WHEN 'smallint' THEN 65535
           WHEN 'mediumint' THEN 16777215
           WHEN 'int' THEN 4294967295
           WHEN 'bigint' THEN 18446744073709551615
         END, 2) AS pct_used
FROM information_schema.tables t
JOIN information_schema.columns c 
  ON t.table_schema = c.table_schema AND t.table_name = c.table_name
WHERE c.extra = 'auto_increment' AND t.auto_increment IS NOT NULL
ORDER BY pct_used DESC;

23. 查找超过 10 分钟未提交的长事务

SELECT trx_id, trx_started, trx_state, trx_query
FROM information_schema.innodb_trx
WHERE trx_started < NOW() - INTERVAL 10 MINUTE;

24. 查找无主键的表

SELECT 
    t.TABLE_SCHEMA AS database_name,
    t.TABLE_NAME AS table_name,
    t.ENGINE
FROM 
    information_schema.TABLES t
LEFT JOIN 
    information_schema.KEY_COLUMN_USAGE kcu 
    ON t.TABLE_SCHEMA = kcu.TABLE_SCHEMA
    AND t.TABLE_NAME = kcu.TABLE_NAME
    AND kcu.CONSTRAINT_NAME = 'PRIMARY'
WHERE 
    t.TABLE_TYPE = 'BASE TABLE'
    AND t.ENGINE IN ('InnoDB', 'MyISAM')  -- 只查常用引擎
    AND kcu.TABLE_NAME IS NULL
    AND t.TABLE_SCHEMA NOT IN (
        'mysql', 'information_schema', 'performance_schema', 'sys'
    )
ORDER BY 
    t.TABLE_SCHEMA, t.TABLE_NAME;

也可以用如下SQL

SELECT 
    t.TABLE_SCHEMA AS database_name,
    t.TABLE_NAME AS table_name,
    t.ENGINE
FROM 
    information_schema.TABLES t
WHERE 
    t.TABLE_TYPE = 'BASE TABLE'
    AND t.ENGINE = 'InnoDB'  -- 建议重点关注 InnoDB
    AND t.TABLE_SCHEMA NOT IN (
        'mysql', 'information_schema', 'performance_schema', 'sys'
    )
    AND NOT EXISTS (
        SELECT 1 
        FROM information_schema.STATISTICS s
        WHERE s.TABLE_SCHEMA = t.TABLE_SCHEMA
          AND s.TABLE_NAME = t.TABLE_NAME
          AND s.INDEX_NAME = 'PRIMARY'
    )
ORDER BY 
    t.TABLE_SCHEMA, t.TABLE_NAME;

还可以用columns表，可以自己编写一下了。

25. 批量添加主键（假设可以设置Id字段为主键）

SELECT 
    CONCAT(
        'ALTER TABLE `', TABLE_SCHEMA, '`.`', TABLE_NAME, '` ADD PRIMARY KEY (id);'
    ) AS add_pk_sql
FROM ( SELECT 
    t.TABLE_SCHEMA AS  TABLE_SCHEMA,
    t.TABLE_NAME AS table_name,
    t.ENGINE
FROM 
    information_schema.TABLES t
LEFT JOIN 
    information_schema.KEY_COLUMN_USAGE kcu 
    ON t.TABLE_SCHEMA = kcu.TABLE_SCHEMA
    AND t.TABLE_NAME = kcu.TABLE_NAME
    AND kcu.CONSTRAINT_NAME = 'PRIMARY'
WHERE 
    t.TABLE_TYPE = 'BASE TABLE'
    AND t.ENGINE IN ('InnoDB', 'MyISAM')  -- 只查常用引擎
    AND kcu.TABLE_NAME IS NULL
    AND t.TABLE_SCHEMA NOT IN (
        'mysql', 'information_schema', 'performance_schema', 'sys'
    )
ORDER BY 
    t.TABLE_SCHEMA, t.TABLE_NAME) AS no_pk_tables;

✅ 使用建议以上所有脚本均在 MySQL 8.4 环境验证通过； performance_schema 需启用（默认已开启）；生产环境执行前，建议先在测试库验证；可将常用脚本封装为视图或存储过程，一键调用；搭配官方 sys schema（如 sys.session, sys.statement_analysis）效果更佳！

九、结语

数据库稳定，是业务连续性的基石。掌握这些“武器级”SQL 脚本，能让你在故障排查、容量规划、安全审计等场景中游刃有余。

收藏本文，下次数据库如果出现告警时，直接 Ctrl+F 搜索关键词，秒级响应！

如果你觉得有用，欢迎点赞 + 在看 + 转发给你的 DBA 同事！也欢迎在评论区留言你最常用的运维脚本，我们一起共建 DBA 工具箱！

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2026-01-17，如有侵权请联系 cloudcommunity@tencent.com 删除

运维