MySQL 性能分析

1. 使用 SHOW STATUS 查看全局性能指标

使用 MySQL 的 SHOW GLOBAL STATUS 分析性能时，以下关键指标需重点关注：

1.1. 连接与线程

SHOW GLOBAL STATUS WHERE Variable_name IN ('Threads_connected', 'Threads_running', 'Threads_created', 'Aborted_connects');

• Threads_connected：当前已建立的连接数；

  数值过高可能表明连接池配置不当或存在资源泄漏。

• Threads_running：正在执行查询的活跃线程数；

  若持续高位，可能并发负载过高或存在慢查询。

• Threads_created：累计创建的线程数；

  若增长快，可能 thread_cache_size 过小，导致频繁创建线程。

• Aborted_connects：尝试连接失败的次数；

  数值高可能暗示网络问题或认证错误。

1.2. 查询统计

SHOW GLOBAL STATUS WHERE Variable_name IN ('Com_delete', 'Com_insert', 'Com_select', 'Com_update', 'Handler_read_rnd_next', 'Questions', 'Select_scan', 'Slow_queries');

• Questions：服务器已处理的查询总数（含简单 Ping 命令）；

  反映整体负载。

• Com_select / Com_insert / Com_update / Com_delete：各类型 SQL 操作的执行次数；

  分析读写比例及负载特征。

• Slow_queries：超过 long_query_time 的慢查询数量；

  持续增长需优化查询或索引。

• Select_scan：全表扫描次数；

  高值表明可能缺少有效索引。

• Handler_read_rnd_next：在执行全表扫描时，MySQL 读取下一行的请求次数；

  衡量全表扫描效率的一个指标。

1.3. InnoDB 缓冲池

SHOW GLOBAL STATUS WHERE Variable_name IN ('Innodb_buffer_pool_read_requests', 'Innodb_buffer_pool_reads');

• Innodb_buffer_pool_read_requests

  从缓冲池读取页的请求次数（逻辑读）。

• Innodb_buffer_pool_reads

  从磁盘读取页到缓冲池的次数（物理读）。

  $$\text{缓冲池命中率}=(1-\frac{\text{Innodb\_buffer\_pool\_reads}}{\text{Innodb\_buffer\_pool\_read\_requests}})\times 100\mathrm{\%}$$

  命中率低（如 < 95%）需增大 innodb_buffer_pool_size。

1.4. 锁与争用

SHOW GLOBAL STATUS WHERE Variable_name IN ('Innodb_row_lock_waits', 'Innodb_row_lock_time_avg', 'Table_locks_waited');

• Innodb_row_lock_waits：等待行锁的总次数；

  高值表明锁竞争激烈。

• Innodb_row_lock_time_avg：行锁平均等待时间（毫秒）；

  长时间等待需优化事务或查询。

• Table_locks_waited：表级锁等待次数；

  InnoDB 通常行锁，但显式锁表或元数据锁可能触发。

1.5. 临时表与磁盘 I/O

SHOW GLOBAL STATUS WHERE Variable_name IN ('Created_tmp_tables', 'Created_tmp_disk_tables');

• Created_tmp_tables：内存中创建的临时表数量；

• Created_tmp_disk_tables：磁盘上创建的临时表数量；

  若占比高（如 > 5%），需优化查询或增大 tmp_table_size / max_heap_table_size。

1.6. 网络与日志

SHOW GLOBAL STATUS WHERE Variable_name IN ('Bytes_received', 'Bytes_sent', 'Innodb_log_waits');

• Bytes_received / Bytes_sent：接收/发送的字节数。

  异常高流量可能需优化查询减少数据传输。

• Innodb_log_waits：因日志缓冲区不足导致的等待次数。

  高值需增大 innodb_log_buffer_size。

2. 检查慢查询日志

MySQL 提供慢查询日志（Slow Query Log）来记录执行时间较长的 SQL 语句。

开启慢查询日志：

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2; -- 设置超过 2 秒的查询记录到日志中

查看慢查询日志文件位置：

SHOW VARIABLES LIKE 'slow_query_log_file';

示例：查看最近的慢查询

tail -n 100 /var/lib/mysql/localhost-slow.log

3. 使用 SHOW PROCESSLIST

实时查看当前正在执行的 SQL 语句，找到耗时较长的查询。

SHOW PROCESSLIST;

可以改用更详细的：

# 这个命令显示与 SHOW PROCESSLIST 相同的信息，但在 Info 字段中会显示完整的查询内容，而不会被截断
SHOW FULL PROCESSLIST;

4. 使用 EXPLAIN

EXPLAIN 可以显示 SQL 查询的执行计划，包括索引使用情况、扫描行数等。

基本用法：

EXPLAIN SELECT * FROM your_table WHERE condition;

以 JSON 格式显示结果：

EXPLAIN FORMAT=JSON
EXPLAIN SELECT * FROM your_table WHERE condition;

结果字段解析：

• id：查询的执行顺序，id 值大的先执行，值相同，上方的先执行；

• select_type：查询类型，常见的有：

  • SIMPLE：简单表查询（即不适用表连接或子查询）；
  • SUBQUERY：子查询；
  • PRIMARY：主查询（即外层的查询）；
  • UNION：UNION 查询；

• type：连接类型，性能由好到差依次为：

  $$null>system>const>eq\mathrm{\_}ref>ref>range>index>all$$

• table：正在访问的表；

• possible_keys：可能使用的索引；

• key：实际使用的索引；

• key_len：索引字段最大可能长度（非实际使用长度）；

• rows：查询的行数（在 InnoDB 中是一个预估值）；

• filtered：返回结果的行数占需读取行数的百分比，值越大越好；

• extra：额外信息，如 Using index 表示使用覆盖索引，Using filesort 和 Using temporary 是性能警告

5. 使用 SHOW PROFILE 进行详细性能分析

SHOW PROFILE 用于分析单条 SQL 的执行各个阶段耗时。

开启 Profiling：

SET profiling = 1;

执行要分析的 SQL：

SELECT * FROM users WHERE age > 30;

查看分析结果：

SHOW PROFILES;

查看详细分析：

SHOW PROFILE FOR QUERY query_id;

示例：

SHOW PROFILE CPU, BLOCK IO FOR QUERY 1;

6. 使用 Performance Schema 进行全面性能监控

6.1. 启用 Performance Schema

在 MySQL 中执行以下命令：

SHOW VARIABLES LIKE 'performance_schema';

如果返回值为 ON，则已启用；如果为 OFF，则需要在配置文件中启用。

在 MySQL 配置文件 my.cnf 中添加：

[mysqld]
performance_schema = ON

然后重启 MySQL 服务：

systemctl restart mysql

6.2. Performance Schema 的架构

Performance Schema 的数据存储在 performance_schema 数据库中，可以通过以下命令查看所有可用表：

USE performance_schema;
SHOW TABLES;

6.3. 常用表说明

• 事件等待类：用于分析线程等待事件、锁争用等问题；

  • events_waits_current：当前等待事件；
  • events_waits_history：线程最近等待事件的历史记录；
  • events_waits_history_long：更长时间范围内的等待事件历史记录；

• 事件阶段类：用于分析 SQL 执行阶段，例如解析、优化、执行等；

  • events_stages_current：当前正在执行的阶段；
  • events_stages_history：最近执行阶段的历史记录；
  • events_stages_history_long：更长时间范围内的阶段历史记录；

• 事件语句类：用于分析 SQL 语句的执行情况；

  • events_statements_current：当前正在执行的 SQL 语句；
  • events_statements_history：最近执行过的 SQL 语句历史；
  • events_statements_history_long：更长时间范围内的 SQL 语句历史记录；
  • events_statements_summary_by_digest：按语句模式（digest）汇总的统计信息，例如执行次数、平均耗时等；
  • events_statements_summary_by_account_by_event_name：按账户和事件名统计 SQL 语句执行情况；

• 事件事务类：用于分析事务的执行和锁等待情况；

  • events_transactions_current：当前事务状态；
  • events_transactions_history：最近事务的历史记录；
  • events_transactions_history_long：更长时间范围内的事务历史记录；

• 资源使用统计类：用于查看资源消耗情况，如内存、CPU、I/O 等；

  • status_by_account：按账户统计状态变量（如执行次数、错误次数等）；
  • status_by_host：按主机统计状态变量；
  • status_by_user：按用户统计状态变量；
  • status_by_thread：按线程统计状态变量；
  • global_status：全局状态变量；
  • session_status：当前会话的状态变量；

• 文件 I/O 类：用于分析文件 I/O 操作的性能，如慢查询日志、二进制日志等；

  • file_summary_by_event_name：按事件名汇总的文件 I/O 操作统计；
  • file_summary_by_instance：按文件实例汇总的 I/O 操作统计；

• 线程类：用于查看当前 MySQL 线程的状态和活动；

  • threads：显示当前所有线程及其状态；
  • session_connect_attrs：显示当前会话的连接属性，如连接时间、客户端信息等；

• 用户和主机统计类：用于按用户或主机分析 SQL 执行和资源消耗情况；

  • accounts：显示所有账户的统计信息；
  • hosts：显示所有主机的统计信息；
  • users：显示所有用户的统计信息；

• 锁和事务类：

  • metadata_locks：查看当前元数据锁；
  • data_locks：查看当前数据锁；
  • data_lock_waits：显示当前数据锁等待情况，适用于分析死锁和锁争用问题；

• 系统变量和配置类：用于查看 Performance Schema 的配置和系统变量；

  • setup_instruments：配置监控事件（如等待事件、阶段事件等）；
  • setup_consumers：配置数据收集消费者（如历史记录、汇总统计等）；
  • setup_actors：配置监控哪些用户、主机、账户的活动；
  • setup_objects：配置监控哪些数据库对象（如表、存储过程等）；

  Note：setup_ 前缀的表用于配置和控制性能数据的收集方式以及粒度。通过配置这些表，可以灵活地启用或禁用不同类型的事件监控，以便在性能和详细度之间取得平衡。

6.4. 开发和运维建议

1. 开发阶段：在测试环境中启用 Performance Schema，尽早发现性能问题。

2. 生产环境：

   • 谨慎开启，不要收集过多事件，以免影响性能；
   • 针对性启用 setup_* 中的特定事件；