MySQL 性能调优入门 - 慢查询分析与索引优化基础

MySQL 性能调优入门 - 慢查询分析与索引优化基础

性能问题诊断的通用思路

当数据库出现性能问题时，切忌盲目猜测或随意调整参数。一个科学的诊断流程通常包括：

1. 基于数据，而非猜测 (Data-Driven, Not Guesswork)：利用我们在上一篇讨论的性能监控指标和建立的基线。查看哪些指标偏离了正常范围？QPS、TPS、连接数、CPU 使用率、I/O 等待、InnoDB 缓冲池命中率等。
2. 明确问题范围 (Define the Problem Scope)：
   • 问题是具体表现为什么？是某个特定查询变慢？是整个系统响应迟缓？是 CPU/内存/I/O 资源耗尽？
   • 问题何时开始？是突然发生还是逐渐恶化？
   • 是否与某些特定事件相关联（例如，新代码上线、数据量激增、配置变更、特定时间段的批处理任务）？
3. 缩小排查范围 (Narrow Down the Scope)：
   • 是 CPU 密集型问题（CPU 使用率高，Threads_running 多）？
   • 是 I/O 密集型问题（iowait 高，磁盘读写繁忙，缓冲池命中率低）？
   • 是 内存不足问题（Swap 使用增加，OOM 发生）？
   • 还是网络问题（连接延迟高，丢包）？
   • 问题是否集中在特定的查询、特定的表、或者特定的时间段？
4. 善用诊断工具 (Utilize Diagnostic Tools)：
   • SHOW GLOBAL STATUS; / SHOW GLOBAL VARIABLES;：查看 MySQL 服务器状态和配置。
   • SHOW PROCESSLIST; (或 information_schema.processlist)：查看当前正在执行的线程和查询。
   • 慢查询日志 (Slow Query Log)：记录执行时间超过阈值的查询。
   • EXPLAIN 命令: 分析 SQL 查询的执行计划。
   • performance_schema 和 sys schema (MySQL 5.6+): 提供更细致的性能监控和诊断信息。
   • Percona Toolkit (如 pt-query-digest, pt-stalk) 等第三方工具。

头号公敌：慢查询分析

大多数数据库性能问题的根源往往在于低效的 SQL 查询。

启用与配置慢查询日志

确保慢查询日志已开启，并设置合理的阈值。

• 配置 (输入 - my.cnf 或 my.ini):

  [mysqld]
  slow_query_log = ON                             # 开启慢查询日志
  slow_query_log_file = /var/log/mysql/mysql-slow.log # 日志文件路径 (确保 MySQL 用户有权限写入)
  long_query_time = 1                             #