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

性能问题诊断的通用思路

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

基于数据，而非猜测 (Data-Driven, Not Guesswork)：利用我们在上一篇讨论的性能监控指标和建立的基线。查看哪些指标偏离了正常范围？QPS、TPS、连接数、CPU 使用率、I/O 等待、InnoDB 缓冲池命中率等。
明确问题范围 (Define the Problem Scope)：

问题是具体表现为什么？是某个特定查询变慢？是整个系统响应迟缓？是 CPU/内存/I/O 资源耗尽？
问题何时开始？是突然发生还是逐渐恶化？
是否与某些特定事件相关联（例如，新代码上线、数据量激增、配置变更、特定时间段的批处理任务）？

缩小排查范围 (Narrow Down the Scope)：

是 CPU 密集型问题（CPU 使用率高，Threads_running 多）？
是 I/O 密集型问题（iowait 高，磁盘读写繁忙，缓冲池命中率低）？
是 内存不足问题（Swap 使用增加，OOM 发生）？
还是网络问题（连接延迟高，丢包）？
问题是否集中在特定的查询、特定的表、或者特定的时间段？

善用诊断工具 (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                             # 定义“慢”查询的阈值，单位秒 (根据实际情况调整，初始可设为 1 或 2 秒)
# 可选配置:
# log_queries_not_using_indexes = ON            # (可选) 记录没有使用索引的查询 (即使它们执行很快)
# log_throttle_queries_not_using_indexes = 10   # (可选) 每分钟最多记录多少条未使用索引的查询
# min_examined_row_limit = 1000                 # (可选) 记录那些扫描行数超过此值的查询
# log_slow_admin_statements = ON                # (可选) 记录慢的 DDL 语句

修改配置后需要重启 MySQL 服务或通过 SET GLOBAL 动态修改（部分参数支持）。

分析慢查询日志

手动查看: 对于偶尔出现的少量慢查询，可以直接查看日志文件。
mysqldumpslow 工具: MySQL 自带的简单分析工具，可以对慢查询日志进行汇总统计。

# 按执行时间排序，显示前 10 条
mysqldumpslow -s t -t 10 /var/log/mysql/mysql-slow.log
# 按执行次数排序，显示前 10 条
mysqldumpslow -s c -t 10 /var/log/mysql/mysql-slow.log

pt-query-digest (Percona Toolkit – 强烈推荐): 功能远比 mysqldumpslow 强大。它可以解析慢查询日志、通用日志 (general log)、SHOW PROCESSLIST 的输出、甚至 tcpdump 捕获的网络流量，对查询进行归一化（例如，WHERE id=1 和 WHERE id=2 视为同类查询），并生成非常详细的分析报告，指出最耗时、执行最频繁、扫描行数最多的查询等。

pt-query-digest /var/log/mysql/mysql-slow.log > slow_query_report.html # 生成 HTML 报告
pt-query-digest /var/log/mysql/mysql-slow.log --since='5 minutes ago' # 分析最近5分钟的

理解 `EXPLAIN` 输出 (至关重要!)

对于从慢查询日志中定位到的问题 SQL，必须使用 EXPLAIN 命令来分析其执行计划。EXPLAIN 会显示 MySQL 优化器打算如何执行这条查询，而不会真正执行它。

用法: 在你的 SELECT, INSERT, UPDATE, DELETE 语句前加上 EXPLAIN 关键字。例如： EXPLAIN SELECT * FROM users WHERE username = 'test';
关键列解读 (输出/解释):

id: SELECT 查询的标识符。
select_type: SELECT 类型（如 SIMPLE, PRIMARY, SUBQUERY, DERIVED, UNION）。
table: 当前行正在访问的表名。
partitions: 查询匹配到的分区（如果表使用了分区）。
type (连接类型 – 非常重要!): 显示了 MySQL 如何查找表中的行。效率从高到低（常见类型）：

system: 表只有一行（系统表），是 const 类型的特例。
const: 表最多只有一行匹配，在查询开始时就被读取，例如基于主键或唯一索引的等值查询。
eq_ref: 对于前一个表中每个行组合，从该表中只读取一行。通常在 JOIN 操作中，连接条件使用了主键或唯一非空索引。
ref: 对于前一个表中每个行组合，所有具有匹配索引值的行都会从该表中读取。使用非唯一性索引或唯一性索引的最左前缀进行查找。
range: 只检索给定范围内的行，使用一个索引来选择行。例如 WHERE id BETWEEN ... 或 WHERE key_col > ...。
index: 全索引扫描。遍历整个索引树来查找匹配的行。通常比 ALL 快，因为索引通常比表数据小。但如果索引很大，仍然很慢。
ALL: 全表扫描。MySQL 将遍历整个表来找到匹配的行。对于大表来说，这是性能灾难的标志！

possible_keys: 显示 MySQL 优化器认为可以用于此查询的索引。
key: 实际决定使用的索引。如果为 NULL，则表示没有使用索引（通常是个坏消息）。
key_len: 使用的索引的长度（字节数）。可以帮助判断复合索引是否被充分利用。
ref: 显示了哪些列或常量被用于与 key 列进行比较以选择行。
rows: MySQL 估计为了找到所需的行而需要读取的行数。这个值越小越好。
filtered: (MySQL 5.7+) 估计被表条件过滤后，剩余行数的百分比，乘以 rows 可以估算将与前一个表连接的行数。
Extra (额外信息 – 非常重要!): 包含了很多关于查询执行方式的宝贵信息：

Using index: 好消息！ 查询所需的数据可以直接从索引中获取，无需回表查询数据行（称为“覆盖索引”）。
Using where: 在存储引擎检索行后，MySQL 服务器层将应用 WHERE 子句中的条件进行进一步过滤。
Using temporary: 坏消息！ MySQL 需要创建一个临时表来处理查询结果，通常发生在 GROUP BY 或 ORDER BY 子句中的列没有合适的索引时。非常消耗性能。
Using filesort: 坏消息！ MySQL 必须在获取所有行之后，在内存或磁盘上进行一次额外的排序操作，因为 ORDER BY 子句无法利用现有索引进行排序。
Using index condition (Index Condition Pushdown – ICP): 优化，允许存储引擎层利用索引过滤部分 WHERE 条件，减少回表次数。

SRE 行动: 分析 EXPLAIN 结果，重点已关注 type 列（避免 ALL 和 index 在大表上），rows 列（是否过大），以及 Extra 列中是否有 Using temporary 或 Using filesort。这些通常指向了索引缺失或使用不当的问题。

索引优化基础

索引是提升数据库查询性能最直接、最有效的手段。

什么是索引？ 索引是一种特殊的数据结构（在 InnoDB 中通常是 B+Tree），它允许数据库引擎快速地定位到表中包含特定值的行，从而避免全表扫描。代价是写操作（INSERT, UPDATE, DELETE）会变慢，因为索引也需要被更新，并且索引本身也占用磁盘空间。
为何需要索引？ 主要目的是加速 SELECT 查询，尤其是那些带有 WHERE、JOIN、ORDER BY 或 GROUP BY 子句的查询。
基本原则:

为 WHERE 子句中经常用于过滤条件的列创建索引。
为 JOIN 子句中 ON 条件的列创建索引（通常是在“被驱动表”的连接列上）。
为 ORDER BY 和 GROUP BY 子句中出现的列创建索引，有助于避免 Using filesort 和 Using temporary。
索引的选择性 (Selectivity)：索引列中不同值的数量（基数 cardinality）越高，索引的选择性就越好，查询时能更快地筛选掉不匹配的行。基数很低的列（例如，性别列只有男/女/未知）通常不适合单独创建索引（除非与其他列组成复合索引且该列在前）。
复合索引 (Composite/Multi-column Indexes)：可以为一个查询涉及到的多个列创建一个组合索引，例如 INDEX idx_name_city_age (name, city, age)。

顺序至关重要！ 遵循“最左前缀原则 (Leftmost Prefix Principle)”。上述索引可以有效地用于以下条件的查询：

WHERE name = 'A'
WHERE name = 'A' AND city = 'B'
WHERE name = 'A' AND city = 'B' AND age = 'C'

通常不能有效地用于只涉及 city 或 age，或者 city 和 age 的查询（除非优化器有特殊技巧如 Index Skip Scan，但不能指望）。

覆盖索引 (Covering Indexes)：如果一个索引包含了查询所需的所有列（即 SELECT 列表中的列以及 WHERE 子句中的列都在这个索引中），那么 MySQL 就可以只通过读取索引而无需访问实际的数据行来满足查询。这在 EXPLAIN 的 Extra 列中会显示 Using index，性能极好。
避免冗余和未使用索引: 过多的索引会降低写性能并占用空间。定期检查并清理那些不再使用或效果不佳的索引（可以使用 performance_schema 或 sys schema 中的视图来辅助识别，如 schema_unused_indexes）。
短索引: 如果索引字符串列，可以考虑只索引其前缀 (INDEX idx_name (name(10)))，以减少索引大小和提高效率，前提是前缀的选择性足够好。

其他常见性能瓶颈与考量 (简述)

配置调优:

innodb_buffer_pool_size: InnoDB 缓冲池大小，最重要的配置参数之一。应设置为物理内存的 50%-80%（根据服务器是否专用以及其他进程需求而定），尽可能让热数据和索引都缓存在内存中。
innodb_log_file_size 和 innodb_log_files_in_group: InnoDB 重做日志文件的大小和数量，影响写入性能和恢复时间。
query_cache_size: (MySQL 8.0 中已移除) 在旧版本中，查询缓存通常弊大于利，建议禁用 (query_cache_type=0, query_cache_size=0)。
max_connections: 最大连接数，需根据应用并发和服务器能力调整。
tmp_table_size, max_heap_table_size: 控制内存临时表的大小，超过则会转为磁盘临时表。

硬件资源: CPU 性能不足、内存不足（导致频繁 Swap 或 OOM）、磁盘 I/O 性能低下（尤其是机械硬盘）都是常见的硬件瓶颈。
应用层连接池: 应用程序应该使用连接池来复用数据库连接，避免频繁创建和销毁连接带来的开销。
架构设计: 对于极高并发或超大数据量，可能需要考虑读写分离、数据分片 (Sharding) 等更复杂的架构方案。