PolarDB MySQL 版兼容 MySQL 协议和常用 SQL但底层采用计算与存储分离架构。一个集群通常包含主节点、只读节点、集群地址和主地址连接经过代理后还可能启用读写分离、会话一致性和事务拆分。因此排查 PolarDB 问题时既要掌握 MySQL 的会话、事务锁、执行计划和 Performance Schema也要知道哪些能力属于云平台管理范围。节点扩缩容、主备切换、集群重启、参数模板、自动备份、时间点恢复、读写分离和 SQL 洞察等操作通常需要在阿里云控制台、DAS 或 API 中完成不能用普通 MySQL 命令替代。下面整理了 PolarDB MySQL 版 DBA 常用的 100 条命令覆盖连接、实例信息、对象、参数、会话、事务锁、SQL 性能、空间、索引、用户权限、导入导出和云平台检查等场景。本文主要面向兼容 MySQL 8.0 的 PolarDB MySQL 集群。不同产品版本、企业版与标准版、集群规格和兼容内核之间可能存在差异。文中的地址、用户、数据库和对象名均为示例。一、连接与实例信息1. 使用 MySQL 客户端连接 PolarDBmysql -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\G18. 查看表状态SHOW TABLE STATUS FROM appdb LIKE orders\G19. 查看字段定义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. 查看长时间运行的 SQLSELECT 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. 取消正在执行的 SQLKILL 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 FORMATJSON 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. 查看平均耗时最高的 SQLSELECT 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. 查看扫描行数最高的 SQLSELECT 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. 查看全表扫描 SQLSELECT * 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;七、空间与 InnoDB61. 查看数据库大小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. 查看正在执行的 DDLSELECT 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 appuser10.% IDENTIFIED BY Replace_With_Strong_Password;83. 修改用户密码ALTER USER appuser10.% IDENTIFIED BY Replace_With_New_Strong_Password;84. 锁定和解锁用户ALTER USER appuser10.% ACCOUNT LOCK;解锁ALTER USER appuser10.% ACCOUNT UNLOCK;85. 查看用户权限SHOW GRANTS FOR appuser10.%;86. 授予只读权限GRANT SELECT ON appdb.* TO report_user10.%;87. 授予读写权限GRANT SELECT, INSERT, UPDATE, DELETE ON appdb.* TO appuser10.%;88. 回收权限REVOKE INSERT, UPDATE, DELETE ON appdb.* FROM appuser10.%;89. 查看角色SELECT * FROM mysql.role_edges;90. 删除用户DROP USER appuser10.%;删除前应确认应用已经停止使用该账号。十、导入导出与云平台运维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.sql93. 导入 SQL 文件mysql -h pc-example.rwlb.rds.aliyuncs.com \ -P 3306 -u restore_user -p \ appdb appdb.sql94. 导出查询结果mysql -h pc-example.rwlb.rds.aliyuncs.com \ -P 3306 -u report_user -p \ --batch --raw \ -e SELECT * FROM appdb.orders LIMIT 1000 \ orders.tsv95. 查看计划任务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 MySQLhttps://help.aliyun.com/zh/polardb/polardb-for-mysql/user-guide/connect-to-polardb/PolarDB 关键术语https://help.aliyun.com/zh/polardb/polardb-for-mysql/terminologySQL 洞察https://help.aliyun.com/zh/polardb/polardb-for-mysql/sql-insight