MySQL Shell诊断实用程序能够分析MySQL服务器的性能，并能够生成运行状况、性能和单个查询的诊断报告。注意，这是MySQL Shell在8.0.31提供的新功能，用户必须使用8.0.31之后的版本。
诊断工具包括三个：
util.debug.collectDiagnostics()
util.debug.collectHighLoadDiagnostics()
util.debug.collectSlowQueryDiagnostics()
util.debug.collectDiagnostics()用于从MySQL服务器、InnoDB Cluster收集原始的诊断数据，并生成tsv和yaml格式的报告文件。（文件使用zip压缩）
util.debug.collectHighLoadDiagnostics()在MySQL服务器上运行多次迭代的诊断报告，使用户能够在负载下从多个方面分析服务器，并生成tsv和yaml格式的报告文件。（文件使用zip压缩）
util.debug.collectSlowQueryDiagnostics()在MySQL服务器上运行多次迭代的诊断报告，使用户能够在执行指定查询的情况下从多个方面分析服务器，并生成tsv和yaml格式的报告文件。（文件使用zip压缩）
工具适用于MySQL5.7之后的服务器版本，并且需要使用root用户执行。
工具执行非常简单，只需要指定一些参数或选项即可，以util.debug.collectDiagnostics()为例：
代码语言：javascript复制util.debug.collectDiagnostics("path/",{options})这里的"path"是输出报告的位置和文件名称。如果用户没有提供位置和名称，默认会输出在当前路径，文件名为mysql-diagnostics-YYYYMMDD-HHMMSS.zip。
选项包括：allMembers、innodbMutex、schemaStats、slowQueries、ignoreErrors、customSql，及customShell，详细的指定方法可以参阅官网的在线手册“https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-utilities-debug-diagnostics.html”
执行该工具，我们实际来看一看能够输出哪些内容。下面是执行过程：
代码语言：javascript复制MySQL Shell 8.0.31
Copyright (c) 2016, 2022, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates.
Other names may be trademarks of their respective owners.
Type 'help' or '?' for help; 'quit' to exit.
WARNING: Permissions 0644 for log file "/Users/yitao.xu/.mysqlsh/mysqlsh.log" are too open. Permissions have been adjusted for user only access.
MySQL JS > c user@ip:3306
Creating a session to 'user@ip:3306'
Fetching schema names for auto-completion... Press ^C to stop.
Your MySQL connection id is 965
Server version: 8.0.30 MySQL Community Server - GPL
No default schema selected; type use <schema> to set one.
MySQL ip:3306 ssl JS > util.debug.collectDiagnostics("/Users/yitao.xu/Downloads/Dia_report.zip")
Collecting diagnostics information from mysql://user@ip:3306...
Copying shell log file...
- Gathering schema tables without a PK...
- Gathering schema routine size...
- Gathering schema table count...
- Gathering schema unused indexes...
- Gathering error_log
- Gathering performance_schema.host_cache...
- Gathering performance_schema.persisted_variables...
- Gathering performance_schema.replication_applier_configuration...
- Gathering performance_schema.replication_applier_filters...
- Gathering performance_schema.replication_applier_global_filters...
- Gathering performance_schema.replication_applier_status...
- Gathering performance_schema.replication_applier_status_by_coordinator...
- Gathering performance_schema.replication_applier_status_by_worker...
- Gathering performance_schema.replication_asynchronous_connection_failover...
- Gathering performance_schema.replication_asynchronous_connection_failover_managed...
- Gathering performance_schema.replication_connection_configuration...
- Gathering performance_schema.replication_connection_status...
- Gathering performance_schema.replication_group_member_stats...
- Gathering performance_schema.replication_group_members...
- Gathering global variables...
- Gathering XA RECOVER CONVERT xid...
- Gathering SHOW BINARY LOGS...
- Gathering SHOW REPLICAS...
- Gathering SHOW MASTER STATUS...
- Gathering SHOW REPLICA STATUS...
- Gathering replication master_info...
- Gathering replication relay_log_info...
- Gathering pfs actors...
- Gathering pfs objects...
- Gathering pfs consumers...
- Gathering pfs instruments...
- Gathering pfs threads...
- Gathering performance_schema.metadata_locks...
- Gathering performance_schema.threads...
- Gathering sys.schema_table_lock_waits...
- Gathering sys.session_ssl_status...
- Gathering sys.session...
- Gathering sys.processlist...
- Gathering performance_schema.events_waits_current...
- Gathering information_schema.innodb_trx...
- Gathering information_schema.innodb_metrics...
- Gathering sys.memory_by_host_by_current_bytes...
- Gathering sys.memory_by_thread_by_current_bytes...
- Gathering sys.memory_by_user_by_current_bytes...
- Gathering sys.memory_global_by_current_bytes...
- Gathering SHOW GLOBAL STATUS...
- Gathering SHOW ENGINE INNODB STATUS...
- Gathering SHOW ENGINE PERFORMANCE_SCHEMA STATUS...
- Gathering SHOW FULL PROCESSLIST...
- Gathering SHOW OPEN TABLES...
Diagnostics information was written to /Users/yitao.xu/Downloads/Dia_report.zip
输出报告一览：
代码语言：javascript复制yitao.xu@yitao Dia_report % ls
0.SHOW_BINARY_LOGS.tsv
0.SHOW_BINARY_LOGS.yaml
0.SHOW_ENGINE_INNODB_STATUS.tsv
0.SHOW_ENGINE_INNODB_STATUS.yaml
0.SHOW_ENGINE_PERFORMANCE_SCHEMA_STATUS.tsv
0.SHOW_ENGINE_PERFORMANCE_SCHEMA_STATUS.yaml
0.SHOW_FULL_PROCESSLIST.tsv
0.SHOW_FULL_PROCESSLIST.yaml
0.SHOW_GLOBAL_STATUS.tsv
0.SHOW_GLOBAL_STATUS.yaml
0.SHOW_MASTER_STATUS.tsv
0.SHOW_MASTER_STATUS.yaml
0.SHOW_OPEN_TABLES.tsv
0.SHOW_OPEN_TABLES.yaml
0.SHOW_REPLICAS.tsv
0.SHOW_REPLICAS.yaml
0.SHOW_REPLICA_STATUS.tsv
0.SHOW_REPLICA_STATUS.yaml
0.XA_RECOVER_CONVERT_xid.tsv
0.XA_RECOVER_CONVERT_xid.yaml
0.error_log.tsv
0.global_variables.tsv
0.global_variables.yaml
0.information_schema.innodb_metrics.tsv
0.information_schema.innodb_metrics.yaml
0.information_schema.innodb_trx.tsv
0.information_schema.innodb_trx.yaml
0.instance
0.metrics.tsv
0.performance_schema.events_waits_current.tsv
0.performance_schema.events_waits_current.yaml
0.performance_schema.host_cache.tsv
0.performance_schema.host_cache.yaml
0.performance_schema.metadata_locks.tsv
0.performance_schema.metadata_locks.yaml
0.performance_schema.persisted_variables.tsv
0.performance_schema.persisted_variables.yaml
0.performance_schema.replication_applier_configuration.tsv
0.performance_schema.replication_applier_configuration.yaml
0.performance_schema.replication_applier_filters.tsv
0.performance_schema.replication_applier_filters.yaml
0.performance_schema.replication_applier_global_filters.tsv
0.performance_schema.replication_applier_global_filters.yaml
0.performance_schema.replication_applier_status.tsv
0.performance_schema.replication_applier_status.yaml
0.performance_schema.replication_applier_status_by_coordinator.tsv
0.performance_schema.replication_applier_status_by_coordinator.yaml
0.performance_schema.replication_applier_status_by_worker.tsv
0.performance_schema.replication_applier_status_by_worker.yaml
0.performance_schema.replication_asynchronous_connection_failover.tsv
0.performance_schema.replication_asynchronous_connection_failover.yaml
0.performance_schema.replication_asynchronous_connection_failover_managed.tsv
0.performance_schema.replication_asynchronous_connection_failover_managed.yaml
0.performance_schema.replication_connection_configuration.tsv
0.performance_schema.replication_connection_configuration.yaml
0.performance_schema.replication_connection_status.tsv
0.performance_schema.replication_connection_status.yaml
0.performance_schema.replication_group_member_stats.tsv
0.performance_schema.replication_group_member_stats.yaml
0.performance_schema.replication_group_members.tsv
0.performance_schema.replication_group_members.yaml
0.performance_schema.threads.tsv
0.performance_schema.threads.yaml
0.pfs_actors.tsv
0.pfs_actors.yaml
0.pfs_consumers.tsv
0.pfs_consumers.yaml
0.pfs_instruments.tsv
0.pfs_instruments.yaml
0.pfs_objects.tsv
0.pfs_objects.yaml
0.pfs_threads.tsv
0.pfs_threads.yaml
0.replication_master_info.tsv
0.replication_master_info.yaml
0.replication_relay_log_info.tsv
0.replication_relay_log_info.yaml
0.sys.memory_by_host_by_current_bytes.tsv
0.sys.memory_by_host_by_current_bytes.yaml
0.sys.memory_by_thread_by_current_bytes.tsv
0.sys.memory_by_thread_by_current_bytes.yaml
0.sys.memory_by_user_by_current_bytes.tsv
0.sys.memory_by_user_by_current_bytes.yaml
0.sys.memory_global_by_current_bytes.tsv
0.sys.memory_global_by_current_bytes.yaml
0.sys.processlist.tsv
0.sys.processlist.yaml
0.sys.schema_table_lock_waits.tsv
0.sys.schema_table_lock_waits.yaml
0.sys.session.tsv
0.sys.session.yaml
0.sys.session_ssl_status.tsv
0.sys.session_ssl_status.yaml
0.uri
mysqlsh.log
schema_routine_size.tsv
schema_routine_size.yaml
schema_table_count.tsv
schema_table_count.yaml
schema_tables_without_a_PK.tsv
schema_tables_without_a_PK.yaml
schema_unused_indexes.tsv
schema_unused_indexes.yaml
shell_info.yaml查看其中一个内容
代码语言：javascript复制yitao.xu@yitao Dia_report % cat schema_tables_without_a_PK.yaml
# Query:
# SELECT t.table_schema, t.table_name, t.table_rows, t.engine, t.data_length, t.index_length
# FROM information_schema.tables t
# LEFT JOIN information_schema.statistics s on t.table_schema=s.table_schema and t.table_name=s.table_name and s.index_name='PRIMARY'
# WHERE s.index_name is NULL and t.table_type = 'BASE TABLE'
# and t.table_schema not in ('performance_schema', 'sys', 'mysql', 'information_schema')
#
# Started: 2022-10-19T10:09:59.931152
# Execution Time: 0.3573 sec以上内容是关于MySQL诊断工具的一个简介，感兴趣的读者可以继续挖掘它的潜力。
