PolarDB MySQL 版 DBA 应该掌握的 100 条命令（建议收藏）

原创三笠丶 2026-08-03

113

前言

PolarDB MySQL 版兼容 MySQL 协议和常用 SQL，但底层采用计算与存储分离架构。一个集群通常包含主节点、只读节点、集群地址和主地址，连接经过代理后还可能启用读写分离、会话一致性和事务拆分。

因此，排查 PolarDB 问题时既要掌握 MySQL 的会话、事务锁、执行计划和 Performance Schema，也要知道哪些能力属于云平台管理范围。节点扩缩容、主备切换、集群重启、参数模板、自动备份、时间点恢复、读写分离和 SQL 洞察等操作，通常需要在阿里云控制台、DAS 或 API 中完成，不能用普通 MySQL 命令替代。

下面整理了 PolarDB MySQL 版 DBA 常用的 100 条命令，覆盖连接、实例信息、对象、参数、会话、事务锁、SQL 性能、空间、索引、用户权限、导入导出和云平台检查等场景。

本文主要面向兼容 MySQL 8.0 的 PolarDB MySQL 集群。不同产品版本、企业版与标准版、集群规格和兼容内核之间可能存在差异。文中的地址、用户、数据库和对象名均为示例。

一、连接与实例信息

1. 使用 MySQL 客户端连接 PolarDB

mysql -h pc-example.rwlb.rds.aliyuncs.com \
  -P 3306 -u appuser -p

应根据业务需求选择集群地址、主地址或自定义地址。

2. 测试连接

SELECT 1;

3. 查看数据库版本

SELECT VERSION();

4. 查看基础实例信息

SELECT
    @@hostname AS hostname,
    @@port AS port,
    @@server_uuid AS server_uuid,
    @@version AS version,
    CONNECTION_ID() AS connection_id;

经过集群地址连接时，多次建立新连接可能被路由到不同节点。

5. 查看当前数据库和用户

SELECT
    DATABASE(),
    USER(),
    CURRENT_USER();

6. 查看当前节点是否只读

SELECT
    @@global.read_only AS read_only,
    @@global.super_read_only AS super_read_only;

只读节点通常不能执行写操作。

7. 查看当前时间和时区

SELECT
    NOW() AS local_time,
    UTC_TIMESTAMP() AS utc_time,
    @@session.time_zone,
    @@system_time_zone;

8. 查看数据库启动时间

SELECT
    VARIABLE_VALUE AS uptime_seconds,
    NOW() - INTERVAL VARIABLE_VALUE SECOND AS startup_time
FROM performance_schema.global_status
WHERE VARIABLE_NAME = 'Uptime';

9. 查看字符集

SELECT
    @@character_set_server,
    @@character_set_database,
    @@character_set_connection,
    @@character_set_client,
    @@character_set_results;

10. 查看 SQL 模式

SELECT
    @@global.sql_mode,
    @@session.sql_mode;

二、数据库与表对象

11. 查看所有数据库

SHOW DATABASES;

12. 创建数据库

CREATE DATABASE appdb
DEFAULT CHARACTER SET utf8mb4
DEFAULT COLLATE utf8mb4_0900_ai_ci;

排序规则应根据当前兼容版本确认。

13. 查看数据库创建语句

SHOW CREATE DATABASE appdb;

14. 查看当前数据库

SELECT DATABASE();

15. 查看数据库中的表

SHOW FULL TABLES FROM appdb;

16. 查看表结构

DESC appdb.orders;

17. 查看完整建表语句

SHOW CREATE TABLE appdb.orders\G

18. 查看表状态

SHOW TABLE STATUS FROM appdb LIKE 'orders'\G

19. 查看字段定义

SELECT
    column_name,
    column_type,
    is_nullable,
    column_key,
    column_default,
    extra
FROM information_schema.columns
WHERE table_schema = 'appdb'
  AND table_name = 'orders'
ORDER BY ordinal_position;

20. 查看表约束

SELECT
    constraint_name,
    constraint_type
FROM information_schema.table_constraints
WHERE table_schema = 'appdb'
  AND table_name = 'orders'
ORDER BY constraint_type, constraint_name;

三、参数与状态

21. 查看指定参数

SHOW VARIABLES LIKE 'max_connections';

22. 查看全部参数

SHOW VARIABLES;

23. 查看全局和会话参数

SELECT
    @@global.wait_timeout AS global_wait_timeout,
    @@session.wait_timeout AS session_wait_timeout,
    @@global.max_connections AS max_connections;

24. 查看重要 InnoDB 参数

SELECT
    @@innodb_buffer_pool_size,
    @@innodb_flush_log_at_trx_commit,
    @@innodb_lock_wait_timeout,
    @@transaction_isolation;

25. 查看参数来源

SELECT
    variable_name,
    variable_value,
    variable_source
FROM performance_schema.variables_info
WHERE variable_name IN (
    'max_connections',
    'wait_timeout',
    'long_query_time'
);

26. 修改当前会话参数

SET SESSION wait_timeout = 1800;

27. 设置当前会话 SQL 超时

SET SESSION max_execution_time = 30000;

单位为毫秒。

28. 查看状态变量

SHOW GLOBAL STATUS;

29. 查看连接状态

SHOW GLOBAL STATUS LIKE 'Threads%';

30. 查看临时表状态

SHOW GLOBAL STATUS LIKE 'Created_tmp%';

磁盘临时表持续增加，通常需要检查排序、分组、连接和内存参数。

四、连接与会话

31. 查看当前连接数

SELECT COUNT(*) AS connections
FROM information_schema.processlist;

32. 查看完整会话

SHOW FULL PROCESSLIST;

33. 查询会话明细

SELECT
    id,
    user,
    host,
    db,
    command,
    time,
    state,
    info
FROM information_schema.processlist
ORDER BY time DESC;

34. 按用户统计连接

SELECT
    user,
    COUNT(*) AS connections
FROM information_schema.processlist
GROUP BY user
ORDER BY connections DESC;

35. 按客户端统计连接

SELECT
    SUBSTRING_INDEX(host, ':', 1) AS client_host,
    COUNT(*) AS connections
FROM information_schema.processlist
GROUP BY SUBSTRING_INDEX(host, ':', 1)
ORDER BY connections DESC;

36. 查看长时间运行的 SQL

SELECT
    id,
    user,
    host,
    db,
    time,
    state,
    info
FROM information_schema.processlist
WHERE command <> 'Sleep'
  AND time >= 60
ORDER BY time DESC;

37. 查看空闲连接

SELECT
    id,
    user,
    host,
    db,
    time
FROM information_schema.processlist
WHERE command = 'Sleep'
ORDER BY time DESC;

38. 取消正在执行的 SQL

KILL QUERY 12345;

39. 终止数据库连接

KILL CONNECTION 12345;

终止连接会回滚未提交事务，执行前应核对连接 ID、用户和来源地址。

40. 查看连接使用率

SELECT
    current_connections,
    max_connections,
    ROUND(current_connections * 100 / max_connections, 2) AS usage_pct
FROM (
    SELECT
        (SELECT COUNT(*) FROM information_schema.processlist) AS current_connections,
        @@global.max_connections AS max_connections
) t;

五、事务、锁与死锁

41. 查看正在运行的事务

SELECT
    trx_id,
    trx_state,
    trx_started,
    trx_mysql_thread_id,
    trx_rows_locked,
    trx_rows_modified,
    trx_query
FROM information_schema.innodb_trx
ORDER BY trx_started;

42. 查看长事务

SELECT
    trx_id,
    trx_mysql_thread_id,
    TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS trx_seconds,
    trx_rows_locked,
    trx_rows_modified,
    trx_query
FROM information_schema.innodb_trx
ORDER BY trx_seconds DESC;

43. 查看数据锁

SELECT
    engine_transaction_id,
    thread_id,
    object_schema,
    object_name,
    index_name,
    lock_type,
    lock_mode,
    lock_status,
    lock_data
FROM performance_schema.data_locks;

44. 查看锁等待

SELECT *
FROM performance_schema.data_lock_waits;

45. 查看阻塞关系

SELECT
    r.trx_mysql_thread_id AS waiting_thread,
    TIMESTAMPDIFF(SECOND, r.trx_started, NOW()) AS waiting_seconds,
    b.trx_mysql_thread_id AS blocking_thread,
    r.trx_query AS waiting_query,
    b.trx_query AS blocking_query
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;

46. 查看元数据锁

SELECT
    object_type,
    object_schema,
    object_name,
    lock_type,
    lock_duration,
    lock_status,
    owner_thread_id
FROM performance_schema.metadata_locks
WHERE lock_status = 'PENDING';

47. 查看 InnoDB 状态

SHOW ENGINE INNODB STATUS\G

重点关注最近死锁、事务、Buffer Pool 和 I/O。

48. 查看锁等待超时

SELECT @@session.innodb_lock_wait_timeout;

49. 设置会话锁等待超时

SET SESSION innodb_lock_wait_timeout = 30;

50. 查看事务隔离级别

SELECT
    @@global.transaction_isolation,
    @@session.transaction_isolation;

六、SQL 性能与执行计划

51. 查看执行计划

EXPLAIN
SELECT *
FROM appdb.orders
WHERE customer_id = 1001;

52. 查看实际执行计划

EXPLAIN ANALYZE
SELECT *
FROM appdb.orders
WHERE customer_id = 1001;

EXPLAIN ANALYZE 会真正执行 SQL，不应随意用于修改类语句和高开销查询。

53. 查看 JSON 执行计划

EXPLAIN FORMAT=JSON
SELECT *
FROM appdb.orders
WHERE customer_id = 1001;

54. 查看 Digest SQL 统计

SELECT
    schema_name,
    digest,
    count_star,
    round(sum_timer_wait / 1000000000000, 2) AS total_seconds,
    round(avg_timer_wait / 1000000000000, 6) AS avg_seconds,
    sum_rows_examined,
    sum_rows_sent,
    digest_text
FROM performance_schema.events_statements_summary_by_digest
ORDER BY sum_timer_wait DESC
LIMIT 20;

55. 查看平均耗时最高的 SQL

SELECT
    schema_name,
    count_star,
    round(avg_timer_wait / 1000000000000, 6) AS avg_seconds,
    digest_text
FROM performance_schema.events_statements_summary_by_digest
WHERE count_star >= 10
ORDER BY avg_timer_wait DESC
LIMIT 20;

56. 查看扫描行数最高的 SQL

SELECT
    schema_name,
    count_star,
    sum_rows_examined,
    sum_rows_sent,
    digest_text
FROM performance_schema.events_statements_summary_by_digest
ORDER BY sum_rows_examined DESC
LIMIT 20;

57. 查看 sys 慢 SQL 汇总

SELECT *
FROM sys.statement_analysis
ORDER BY total_latency DESC
LIMIT 20;

58. 查看全表扫描 SQL

SELECT *
FROM sys.statements_with_full_table_scans
ORDER BY total_latency DESC
LIMIT 20;

59. 清空 Digest 统计

TRUNCATE TABLE
performance_schema.events_statements_summary_by_digest;

清空会影响趋势分析，应在明确采样窗口时执行。

60. 查看 Performance Schema 是否开启

SELECT @@performance_schema;

七、空间与 InnoDB

61. 查看数据库大小

SELECT
    table_schema,
    ROUND(SUM(data_length + index_length) / 1024 / 1024 / 1024, 2) AS size_gb
FROM information_schema.tables
WHERE table_schema NOT IN (
    'information_schema',
    'mysql',
    'performance_schema',
    'sys'
)
GROUP BY table_schema
ORDER BY size_gb DESC;

62. 查看大表排行

SELECT
    table_schema,
    table_name,
    table_rows,
    ROUND(data_length / 1024 / 1024, 2) AS data_mb,
    ROUND(index_length / 1024 / 1024, 2) AS index_mb,
    ROUND((data_length + index_length) / 1024 / 1024, 2) AS total_mb
FROM information_schema.tables
WHERE table_schema = 'appdb'
ORDER BY data_length + index_length DESC
LIMIT 20;

63. 查看表碎片候选

SELECT
    table_schema,
    table_name,
    table_rows,
    ROUND(data_free / 1024 / 1024, 2) AS data_free_mb,
    ROUND((data_length + index_length) / 1024 / 1024, 2) AS total_mb
FROM information_schema.tables
WHERE engine = 'InnoDB'
  AND data_free > 0
ORDER BY data_free DESC
LIMIT 20;

data_free 不能直接等同于可回收空间，应结合表结构和存储实现判断。

64. 查看 Buffer Pool 状态

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool%';

65. 查看 Buffer Pool 命中率

SELECT
    ROUND(
        (1 - reads / NULLIF(read_requests, 0)) * 100,
        4
    ) AS buffer_pool_hit_pct
FROM (
    SELECT
        MAX(CASE WHEN variable_name = 'Innodb_buffer_pool_reads'
                 THEN variable_value END) AS reads,
        MAX(CASE WHEN variable_name = 'Innodb_buffer_pool_read_requests'
                 THEN variable_value END) AS read_requests
    FROM performance_schema.global_status
) s;

66. 查看脏页比例

SELECT
    ROUND(dirty * 100 / NULLIF(total_pages, 0), 2) AS dirty_page_pct
FROM (
    SELECT
        MAX(CASE WHEN variable_name = 'Innodb_buffer_pool_pages_dirty'
                 THEN variable_value END) AS dirty,
        MAX(CASE WHEN variable_name = 'Innodb_buffer_pool_pages_total'
                 THEN variable_value END) AS total_pages
    FROM performance_schema.global_status
) s;

67. 查看 Redo 日志等待

SHOW GLOBAL STATUS LIKE 'Innodb_log_waits';

68. 查看行操作统计

SHOW GLOBAL STATUS LIKE 'Innodb_rows_%';

69. 查看打开表数量

SHOW GLOBAL STATUS LIKE 'Open%tables';

70. 更新表统计信息

ANALYZE TABLE appdb.orders;

八、索引与表结构

71. 查看表索引

SHOW INDEX FROM appdb.orders;

72. 查看索引基数

SELECT
    index_name,
    seq_in_index,
    column_name,
    cardinality,
    non_unique
FROM information_schema.statistics
WHERE table_schema = 'appdb'
  AND table_name = 'orders'
ORDER BY index_name, seq_in_index;

73. 查看没有主键的表

SELECT
    t.table_schema,
    t.table_name
FROM information_schema.tables t
LEFT JOIN information_schema.table_constraints c
  ON c.table_schema = t.table_schema
 AND c.table_name = t.table_name
 AND c.constraint_type = 'PRIMARY KEY'
WHERE t.table_schema = 'appdb'
  AND t.table_type = 'BASE TABLE'
  AND c.constraint_name IS NULL;

74. 查看未使用索引

SELECT *
FROM sys.schema_unused_indexes
WHERE object_schema = 'appdb';

实例重启和统计清空后数据会重新累计，不能仅凭一次结果删除索引。

75. 查看重复索引

SELECT *
FROM sys.schema_redundant_indexes
WHERE table_schema = 'appdb';

76. 创建索引

CREATE INDEX idx_orders_customer
ON appdb.orders(customer_id);

77. 创建联合索引

CREATE INDEX idx_orders_customer_time
ON appdb.orders(customer_id, order_time);

字段顺序应结合过滤、排序和选择性设计。

78. 删除索引

DROP INDEX idx_orders_customer
ON appdb.orders;

79. 增加字段

ALTER TABLE appdb.orders
ADD COLUMN remark VARCHAR(500) NULL;

80. 查看正在执行的 DDL

SELECT
    processlist_id,
    processlist_user,
    processlist_host,
    processlist_time,
    processlist_state,
    processlist_info
FROM performance_schema.threads
WHERE processlist_command <> 'Sleep'
  AND processlist_info REGEXP
      '^(ALTER|CREATE|DROP|TRUNCATE|RENAME)';

九、用户与权限

81. 查看用户

SELECT
    user,
    host,
    account_locked,
    password_expired
FROM mysql.user
ORDER BY user, host;

82. 创建用户

CREATE USER 'appuser'@'10.%'
IDENTIFIED BY 'Replace_With_Strong_Password';

83. 修改用户密码

ALTER USER 'appuser'@'10.%'
IDENTIFIED BY 'Replace_With_New_Strong_Password';

84. 锁定和解锁用户

ALTER USER 'appuser'@'10.%' ACCOUNT LOCK;

解锁：

ALTER USER 'appuser'@'10.%' ACCOUNT UNLOCK;

85. 查看用户权限

SHOW GRANTS FOR 'appuser'@'10.%';

86. 授予只读权限

GRANT SELECT ON appdb.*
TO 'report_user'@'10.%';

87. 授予读写权限

GRANT SELECT, INSERT, UPDATE, DELETE
ON appdb.*
TO 'appuser'@'10.%';

88. 回收权限

REVOKE INSERT, UPDATE, DELETE
ON appdb.*
FROM 'appuser'@'10.%';

89. 查看角色

SELECT *
FROM mysql.role_edges;

90. 删除用户

DROP USER 'appuser'@'10.%';

删除前应确认应用已经停止使用该账号。

十、导入导出与云平台运维

91. 使用 mysqldump 导出数据库

mysqldump -h pc-example.rwlb.rds.aliyuncs.com \
  -P 3306 -u backup_user -p \
  --single-transaction \
  --routines --events --triggers \
  appdb > appdb.sql

大规模迁移建议使用 DTS、DMS 或官方迁移方案。

92. 导出指定表

mysqldump -h pc-example.rwlb.rds.aliyuncs.com \
  -P 3306 -u backup_user -p \
  --single-transaction \
  appdb orders > orders.sql

93. 导入 SQL 文件

mysql -h pc-example.rwlb.rds.aliyuncs.com \
  -P 3306 -u restore_user -p \
  appdb < appdb.sql

94. 导出查询结果

mysql -h pc-example.rwlb.rds.aliyuncs.com \
  -P 3306 -u report_user -p \
  --batch --raw \
  -e "SELECT * FROM appdb.orders LIMIT 1000" \
  > orders.tsv

95. 查看计划任务

SELECT
    event_schema,
    event_name,
    status,
    event_type,
    execute_at,
    interval_value,
    interval_field
FROM information_schema.events
ORDER BY event_schema, event_name;

96. 查看分区表

SELECT
    table_schema,
    table_name,
    partition_name,
    partition_method,
    partition_expression,
    table_rows
FROM information_schema.partitions
WHERE partition_name IS NOT NULL
ORDER BY table_schema, table_name, partition_ordinal_position;

97. 验证读写地址的节点路由

SELECT
    @@hostname,
    @@server_uuid,
    @@global.read_only,
    CONNECTION_ID();

分别通过主地址、集群地址和自定义地址多次建立新连接，可以辅助确认路由结果。正式判断仍应结合控制台地址配置。

98. 查询数据库内可见的错误和告警

SELECT
    error_number,
    error_name,
    sql_state,
    sum_error_raised,
    first_seen,
    last_seen
FROM performance_schema.events_errors_summary_global_by_error
WHERE sum_error_raised > 0
ORDER BY sum_error_raised DESC
LIMIT 20;

99. 生成快速巡检摘要

SELECT 'connections' AS item,
       COUNT(*) AS value
FROM information_schema.processlist
UNION ALL
SELECT 'running_transactions',
       COUNT(*)
FROM information_schema.innodb_trx
UNION ALL
SELECT 'lock_waits',
       COUNT(*)
FROM performance_schema.data_lock_waits
UNION ALL
SELECT 'max_connections',
       @@global.max_connections;

100. 检查控制台运维项目

数据库内没有一条 SQL 可以完整替代云平台巡检。完成 SQL 检查后，还应在 PolarDB 控制台或 API 中确认：

集群与节点状态
主节点和只读节点拓扑
集群地址、主地址和自定义地址配置
读写分离与一致性级别
CPU、内存、连接、IOPS、吞吐和存储使用率
慢 SQL、SQL 洞察和一键诊断
参数模板及待重启参数
自动备份、日志备份和可恢复时间范围
告警规则、维护窗口和近期变更记录

结语

PolarDB MySQL 版的大部分 SQL 排查方法与 MySQL 8.0 相似，但架构判断不能停留在单机数据库思路。通过集群地址连接时，读请求可能被路由到只读节点；同一条查询在不同连接中可能落到不同计算节点；备份、扩缩容和切换也由云平台统一管理。

因此，实际排查时应把数据库内信息和控制台信息放在一起看。数据库内重点检查会话、事务锁、执行计划、Digest SQL 和空间；控制台重点检查节点拓扑、地址路由、读写分离、监控趋势、SQL 洞察、备份和近期变更。

官方资料

PolarDB MySQL 版文档：https://help.aliyun.com/zh/polardb/polardb-for-mysql/
连接 PolarDB MySQL：https://help.aliyun.com/zh/polardb/polardb-for-mysql/user-guide/connect-to-polardb/
PolarDB 关键术语：https://help.aliyun.com/zh/polardb/polardb-for-mysql/terminology
SQL 洞察：https://help.aliyun.com/zh/polardb/polardb-for-mysql/sql-insight

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者