MySQL性能优化：关键策略与最佳实践

当你不再把MySQL性能优化当作一个清单，而是当作一次工作负载审查时，效果最好。数据库只是在精确执行应用程序的指令。有时，修复方案是一个索引；有时，是一个更好的查询；有时，是更少的连接、不同的模式选择，或者是一个不应该在中午在主库上运行的报表。

最好的MySQL性能优化工作首先会减少不必要的工作。硬件和配置固然重要，但它们应该支持一个干净的工作负载，而不是去弥补一个每次请求都要读取半个数据库的查询。

1. 最佳索引策略

索引是数据库性能的基础，尤其对于读密集型工作负载。它们允许MySQL快速定位行，而无需扫描整个表，从而显著加速SELECT操作、WHERE子句过滤、ORDER BY和GROUP BY子句以及JOIN操作。

什么是索引，为什么它们很重要？

索引是一种特殊的查找表，数据库搜索引擎可以用它来加速数据检索。可以把它想象成书中的索引：无需阅读每一页来查找主题，而是去索引中找到主题，然后被引导到正确的页码。在MySQL中，索引通常是B-Tree结构，对于范围查询和精确查找非常高效。

虽然索引加速了读取，但它们确实会给写操作（INSERT、UPDATE、DELETE）增加开销，因为索引本身也必须更新。因此，需要仔细考虑以避免过度索引。

索引的最佳实践

• 为WHERE、JOIN、ORDER BY、GROUP BY子句中使用的列建立索引：这些是索引的主要候选对象。确保用于表间连接条件的列在两个表中都已建立索引。
• 优先使用复合索引：当查询经常在多个列上进行过滤或排序时，复合索引（(col1, col2, col3)）可能比多个单列索引更高效。复合索引中列的顺序很重要。等值谓词通常放在范围谓词之前，并且索引应该匹配实际的查询形状，而不是一个通用的选择性概念。

  -- 在last_name和first_name上创建复合索引
  CREATE INDEX idx_last_first_name ON users (last_name, first_name);
  

• 避免过度索引：过多的索引会减慢写操作并消耗过多的磁盘空间。只为那些真正受益的列建立索引。
• 考虑索引选择性：当索引能显著减少MySQL需要检查的行数时，它是最有效的。具有高基数（许多唯一值）的列是建立索引的良好候选。
• 定期审查索引使用情况：使用SHOW INDEX FROM table_name;检查定义和基数估计，并在可用时检查sys.schema_unused_indexes。将未使用的索引报告视为候选，而非证据；服务器可能尚未观察到月度任务或罕见的管理工作流。

2. 掌握查询优化

即使有完美的索引，编写糟糕的查询也会严重影响性能。查询优化是关于编写高效的SQL，有效利用索引并最小化资源消耗。

EXPLAIN语句：你最好的朋友

EXPLAIN语句对于理解MySQL如何执行你的查询非常宝贵。它显示了执行计划，包括使用了哪些索引、表如何连接以及潜在的性能瓶颈。

EXPLAIN SELECT * FROM orders WHERE customer_id = 123 AND order_date > '2023-01-01';

关键EXPLAIN输出解读：

• type：指示表是如何连接的。目标是const、eq_ref、ref、range。如果可能，避免ALL（全表扫描）。
• rows：MySQL必须检查的行数的估计值。越低越好。
• key：MySQL实际使用的索引。
• Extra：提供关键细节：
  • Using filesort：MySQL需要执行额外的传递来对数据进行排序（可能很慢）。
  • Using temporary：MySQL需要创建一个临时表来处理查询（可能很慢）。
  • Using index：使用了“覆盖索引”，意味着查询所需的所有数据都直接在索引中找到，避免了访问数据行。非常高效。

高效的WHERE子句

• 使用LIMIT进行分页：在获取结果子集时，始终指定LIMIT子句，尤其是分页时。
• 避免在LIKE中使用前导通配符：LIKE '%keyword'会阻止在该列上使用索引，强制进行全表扫描。优先使用LIKE 'keyword%'。
• 不要在WHERE中对索引列使用函数：WHERE YEAR(order_date) = 2023会阻止在order_date上使用索引。应使用WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31'。
• 使用清晰的范围谓词：对于包含性范围，WHERE id >= 10 AND id <= 20和WHERE id BETWEEN 10 AND 20是等价的。对于日期和时间戳，半开范围通常更安全：

  WHERE created_at >= '2025-01-01'
    AND created_at <  '2025-02-01'
  

优化JOIN

• 在索引列上进行连接：确保JOIN条件中使用的列在两个表中都已建立索引。
• 选择合适的JOIN类型：理解INNER JOIN、LEFT JOIN、RIGHT JOIN，并使用精确匹配你需求的那个。
• 让优化器工作，然后验证：MySQL可以重新排序内连接，因此SQL文本顺序不总是执行顺序。使用EXPLAIN查看计划。只有在测量到糟糕的计划并理解其为何糟糕时，才使用优化器提示。

通用查询最佳实践

• 避免SELECT *：明确列出你需要的列。这减少了网络流量、内存使用，并允许使用覆盖索引。
• 不要假设子查询不好：现代MySQL可以很好地优化许多子查询。只有在检查计划和计时后才重写。一个可读性好且性能不错的子查询，比一个没人想维护的巧妙连接要好。
• 批量操作：对于多行的INSERT或UPDATE，使用单个语句插入/更新多个值，而不是为每行使用单独的语句。这减少了事务开销。

  -- 批量INSERT示例
  INSERT INTO products (name, price) VALUES
  ('产品A', 10.00),
  ('产品B', 20.00),
  ('产品C', 30.00);
  

3. 面向性能的数据库模式设计

设计良好的模式构成了高性能数据库的基础。模式设计期间做出的决策会显著影响查询效率和数据完整性。

• 规范化与非规范化：
  • 规范化（例如，3NF）减少数据冗余并提高数据完整性，但通常会导致更多的JOIN。
  • 非规范化引入受控的冗余以减少JOIN并加速特定的读取查询，但可能使数据一致性复杂化。一种平衡的方法，通常为了报表或特定的高读取场景而略微非规范化，是常见的。
• 合适的数据类型：选择能够存储所需信息的最小可能数据类型。当较小范围足够时，使用INT而不是BIGINT，或对较短的字符串使用VARCHAR(255)而不是TEXT，可以节省空间并提高性能。
  • CHAR是固定长度的，VARCHAR是可变长度的。对固定长度数据（例如，如果长度始终相同的UUID）使用CHAR，对可变长度数据使用VARCHAR。
• 始终使用主键：每个InnoDB表都应该有一个主键。自增整数对于许多OLTP系统来说简单高效，但它们不是唯一有效的选择。选择一个稳定的键，使二级索引保持合理大小，并避免随机写入模式，除非你已为此做好计划。
• 为外键建立索引：确保外键关系中的列已建立索引。这加速了JOIN和级联操作。

4. 服务器配置调优（my.cnf/my.ini）

MySQL的行为在很大程度上受其配置文件（Linux上为my.cnf，Windows上为my.ini）的影响。优化这些设置以匹配你的硬件和工作负载至关重要。

关键的InnoDB设置

对于大多数使用InnoDB存储引擎的现代MySQL部署，以下设置至关重要：

• innodb_buffer_pool_size：这通常是最关键的设置。 它是InnoDB缓存表数据和索引的内存区域。在专用数据库服务器上，一个常见的起点是RAM的50-75%，有时在测量后可以更高。为操作系统、连接内存、备份和监控代理留出空间。

  [mysqld]
  innodb_buffer_pool_size = 8G  # 例如，对于16GB RAM的服务器
  

• innodb_log_file_size：InnoDB重做日志的大小。对于写密集型工作负载，更大的日志可以减少检查点压力，但可能会增加崩溃恢复时间。正确的值取决于写入量和恢复预期；不要从旧的调优指南中复制固定大小。
• innodb_flush_log_at_trx_commit：控制InnoDB在事务持久性方面遵循ACID合规性的严格程度。
  • 1（默认）：完全ACID合规。每次事务提交时，日志都会刷新到磁盘。最安全但最慢。
  • 0：日志大约每秒写入一次日志文件。最快，但崩溃时可能丢失最多1秒的事务。
  • 2：每次提交时日志写入操作系统缓存，并每秒刷新到磁盘一次。一种折衷方案，但操作系统崩溃可能导致事务丢失。
  • 根据应用程序的数据完整性要求与性能需求进行选择。

其他重要设置

• max_connections：最大同时客户端连接数。设置过高会消耗更多RAM；设置过低可能导致“Too many connections”错误。根据应用程序的连接池和峰值负载进行调整。
• tmp_table_size和max_heap_table_size：这些定义了内存中临时表的最大大小。如果临时表超过此大小，MySQL会将其写入磁盘，导致显著减速。如果EXPLAIN频繁显示Using temporary，尤其是在大数据集上进行GROUP BY或ORDER BY操作时，请增加这些值。
• sort_buffer_size：用于排序操作（ORDER BY、GROUP BY）的缓冲区。如果查询经常涉及大量排序，并且EXPLAIN中出现Using filesort，考虑增加此值（每个连接）。
• join_buffer_size：用于在没有索引的情况下连接表时的全表扫描。如果EXPLAIN显示此情况，通常表明缺少索引，但更大的缓冲区可以帮助无索引的连接。
• query_cache_size：在MySQL 5.7.20中已弃用，并在MySQL 8.0中移除。 虽然缓存查询结果似乎很有吸引力，但由于高锁争用，它常常成为性能瓶颈，尤其是在繁忙的服务器上。通常建议禁用它（query_cache_size = 0），并依赖应用程序级缓存或更快的存储引擎。

提示： 更改配置后，重启MySQL服务器以使更改生效。在应用到生产环境之前，始终在测试环境中测试更改。

5. 硬件和操作系统考量

即使是最优化的MySQL实例，也可能因硬件不足或操作系统配置不当而成为瓶颈。

• RAM：对innodb_buffer_pool_size至关重要。缓冲池可用的RAM越多，MySQL需要访问磁盘的次数就越少。
• CPU：多核CPU有益，尤其对于并发查询执行和复杂操作。
• 磁盘I/O：这通常是主要瓶颈。对于繁忙的生产MySQL，SSD存储是正常基线，因为随机I/O很重要。对于自管理服务器，仔细考虑冗余和写入行为。对于云数据库，注意预置的IOPS、突发限制、延迟和备份窗口。
• 网络延迟：对于远程数据库访问，最小化应用服务器和数据库服务器之间的网络延迟。
• 操作系统调优：确保操作系统设置为数据库工作负载进行了优化。对于Linux，考虑调整vm.swappiness（以防止不必要的交换）、file-max（打开文件限制）和ulimit设置。

6. 主动监控与分析

优化是一个持续的过程。持续监控有助于识别性能趋势、及早发现瓶颈，并验证调优工作的影响。

• 慢查询日志：配置MySQL记录执行时间超过指定时间（long_query_time）的查询。这是你识别问题查询的主要工具。

  [mysqld]
  slow_query_log = 1
  slow_query_log_file = /var/log/mysql/mysql-slow.log
  long_query_time = 1
  log_queries_not_using_indexes = 1
  

• 分析慢查询日志：像pt-query-digest（来自Percona Toolkit）这样的工具可以解析大型慢查询日志并提供聚合报告，突出显示最频繁和最慢的查询。
• MySQL状态变量（SHOW STATUS）：提供关于服务器活动、内存使用、连接等的实时信息。对于实时发现问题很有用。

  SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read_requests';
  SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_reads';
  

  • Innodb_buffer_pool_reads与Innodb_buffer_pool_read_requests的高比率表明缓冲池命中率低，可能意味着innodb_buffer_pool_size太小。
• 监控工具：使用专门的监控解决方案，如Percona监控与管理（PMM）、带有Grafana的Prometheus或MySQL企业监控。这些提供全面的指标、仪表板和警报。
• 定期审计：定期审查你的数据库模式、查询模式和索引使用情况，以确保它们随着应用程序的发展保持优化。

一个实用的优化工作流

如果你接手一个慢速的MySQL系统，请克制住在一小时内更改十个设置的冲动。使用一个可重复的流程。

从慢查询日志和应用程序跟踪开始。根据总时间找到重要的查询，而不仅仅是根据最差的单次执行。一个耗时200毫秒但每小时运行50,000次的查询，可能比一个每晚运行一次耗时20秒的报表更伤人。

然后对确切的查询形状使用EXPLAIN，包括实际的参数值：

EXPLAIN
SELECT id, customer_id, total, created_at
FROM orders
WHERE customer_id = 42
  AND status = 'paid'
ORDER BY created_at DESC
LIMIT 20;

对于这样的查询，一个在(customer_id, status, created_at)上的索引可能有用。如果屏幕通常首先在所有客户中按status过滤，那么(status, created_at)可能更好。正确的索引来自访问模式，而不是列名。

在查询和索引审查之后，查看内存。如果活动数据集远大于缓冲池，MySQL将更频繁地从存储中读取。如果缓冲池已经很大但服务器仍然很慢，问题可能是表扫描、局部性差、临时表或写入压力。只有当工作负载可以重用时，更多内存才有帮助。

接下来，查看并发性。一个数据库可以处理大量小查询，但它不能处理无限的并行工作。如果应用程序打开太多连接，MySQL可能会花费更多时间在会话之间切换，而不是完成有用的工作。具有合理最大值的连接池通常比提高max_connections更能提高性能。

最后，验证更改。一个好的优化应该在某个地方体现出来：检查的行数减少、查询延迟降低、磁盘读取压力减小、锁等待时间缩短、副本延迟降低或超时减少。如果指标没有变化，要么更改没有解决瓶颈，要么测量过于模糊。

使MySQL变慢的常见错误

一个常见错误是为每个外键和每个过滤列单独建立索引，然后想知道为什么写入很慢。外键列通常应该建立索引，过滤列通常受益于索引，但一堆单列索引并不能取代一个设计良好的复合索引。

另一个错误是使用带有大偏移量的分页：

SELECT *
FROM events
ORDER BY created_at DESC
LIMIT 50 OFFSET 500000;

MySQL仍然需要遍历大量行。对于深层页面，键集分页通常更好：

SELECT *
FROM events
WHERE created_at < '2025-05-01 12:00:00'
ORDER BY created_at DESC
LIMIT 50;

长事务是另一个安静的痛苦来源。一个等待用户输入、调用外部API或在持有锁的同时处理大批量数据的事务，可能会阻塞不相关的工作。保持事务简短。完成数据库工作，提交，然后执行缓慢的外部工作。

全局缓冲区更改也可能适得其反。像sort_buffer_size和join_buffer_size这样的设置是每个连接的。因为一个报表慢而全局提高它们，可能会在多个会话中成倍增加内存使用。首先修复查询。如果需要，对特殊作业使用会话级更改。

“良好”状态的样子

一个健康的MySQL环境不是每个查询都瞬间完成的环境。而是一个团队能够解释昂贵的查询、预测繁重的工作，并在用户报告之前看到瓶颈的环境。慢查询日志已启用。仪表板显示查询延迟、检查的行数、缓冲池读取、锁等待、磁盘延迟、连接计数和复制延迟。模式更改在真实数据上进行了测试。索引有所有者和理由。

这不如一个巨大的调优清单那么引人注目，但这就是MySQL在应用程序变化时保持快速的方式。测量工作负载，减少不必要的工作，一次更改一件事，并保留证据。