充分利用 MySQL 性能：查询调整
数据库对任何应用的性能都起着至关重要的作用。MySQL 数据库也不例外。因此，了解数据库调优、设计和配置有助于提高应用性能的多种方式非常重要。您可以采用以下方式优化 MySQL，以实现最佳性能。
相关文章
概览
查询调整
数据库性能调整从应用开始。应用如何将业务要求转化为数据库查询，会对应用的复杂性和效率产生巨大影响。真正的性能衡量依据是每个数据库实例满足业务需求的效率。
架构设计
如何在关系型数据库中定义实体和关系决定了响应数据库查询的直接程度或复杂程度。此外，主键和二级索引的定义方式也发挥着重要作用。
服务器配置
服务器配置负责优化和最大程度地利用系统资源。系统资源是指中央处理单元 (CPU) 核心、物理机器或虚拟机 (VM) 上的内存、底层存储系统，以及网络。
动态服务器调整
持续监控、优化和性能调整，以适应数据库工作负载的实际动态变化。
本文重点介绍查询调整。其余主题将在后续文章中介绍。
我们通常会通过查看数据库查询来启动查询调整过程。更好的方法是，先评估将业务要求转化为数据库查询的效率。业务需求对查询的解释和处理决定了成本的大小。
设计查询
第一步是确定要优化的查询优先级：
- 确定需要最佳响应时间的数据库问题
- 识别频繁出现的数据库问题
这两个类别需要在数据库架构设计期间进行优化。
确保事务简短
事务是一个逻辑单元，其中的所有语句要么全部提交，要么回滚。 事务是为 MySQL 提供原子性、一致性、隔离性和持久性 (ACID) 的功能。
在 MySQL 的存储引擎 InnoDB 中，所有用户活动都发生在事务内。默认情况下，自动提交模式处于启用状态，这意味着每条 SQL 语句都会独立形成单项事务。如需在启用自动提交后执行多语句事务，请使用 START_TRANSACTION 或 BEGIN 明确启动事务，并使用 COMMIT 或 ROLLBACK 结束事务。如果停用自动提交模式，则会话将始终保持事务打开状态，直到 COMMIT 或 ROLLBACK 将其结束并启动新事务。
处理事务的最佳实践是尽可能缩短事务。这是因为，长事务具有一些弊端，如本文中进一步所述。
长事务的缺点
- 长时间的锁争用，因而导致查询速度变慢以及查询失败
- 在事务期间保留 InnoDB 行级锁定
- 可能会增加锁定等待时间、锁定超时和死锁，从而导致查询速度变慢或完全失败
- 由于撤消日志量较大，服务器性能下降
- 由于 InnoDB 多版本并发控制 (MVCC)，旧版本的变更行存储在撤消日志中，以实现一致的读取和回滚。对于默认的可重复读取隔离级别，撤消日志将不会被完全清除，直到事务在其完成之前开始。因此，长时间运行的事务会累积撤消日志。您可以通过 SHOW ENGINE INNODB STATUS 命令行函数的历史记录列表来观察和监控这种情况。
- 当历史记录列表数以百万计时，由于回滚段的互斥争用、读取撤消日志的次数增加以及遍历撤消日志的关联列表时间增加，服务器性能将受到不利影响。此外，完全清除线程的工作量也会增加。
- 磁盘使用量增加
- 增加存储在磁盘（无论是在系统表空间还是撤消表空间）中的撤消日志
- 关停时间延长
- 在正常关停期间，系统会回滚正在进行的事务。回滚时间通常比完成处理所需的时间长。因此，服务器关停可能需要很长时间才能回滚。
- 崩溃恢复时间延长
- 在崩溃恢复期间，InnoDB 会重复上一个检查点中的事务，并展开未提交的事务。长事务会使相应的步骤花费更长的时间。
针对单查询事务的注意事项
- SELECT 查询
- 它们不保留行锁定
- 它们可能会导致撤消日志累积
- 请参阅下面的查询优化部分
- UPDATE/INSERT/DELETE 查询
- 批量查询的性能要优于许多单行更改
- 拆分并将批处理执行时间限制为几秒钟
多语句事务的注意事项
- 考虑分离 SELECT 查询
- 如果数据库查询之间存在应用逻辑，请考虑拆分事务
- 估算每条语句中会有多少行锁
- 评估执行顺序，以尽可能减少行锁定
- 寻找机会缩小事务规模
捕获查询
查询可以在应用端或数据库端捕获。
应用端
记录数据库查询和查询执行时间是一种很好的开发做法。借助应用端日志记录，您可以轻松评估查询在其业务环境中的有效性和效率。例如，用户可以记录每个查询的响应时间或特定功能的响应时间。通过这种方式，您还可以轻松获得多语句事务的总执行时间。
此外，从应用端日志记录测量的查询响应时间是端到端测量结果（包括网络时间）。它补充了从数据库记录的查询执行时间，可让您轻松确定是网络问题还是数据库问题。
数据库端
Cloud SQL MySQL Query Insights
Cloud SQL Query Insights 工具支持查询捕获、监控和诊断。
借助 Query Insights，您可以根据执行时间和执行频率轻松找到热门查询。
该工具具有多种过滤选项，例如时间范围、数据库、用户帐号和客户端地址。它带有显示 CPU 使用情况的图表，以及 IO 和锁定等待时间的细分数据。“热门查询和标记”表会按执行时间列出热门查询，并将查询标准化。除了执行时间之外，它还包含“扫描的平均行数”和“返回的平均行数”的统计信息，可提供有关查询效率的数据洞见。
使用 performance_schema
在 Cloud SQL for MySQL 中，内存超过 15 GB 的 MySQL 8.0.26 及更高版本默认启用 performance_schema 功能。启用或停用它需要重启实例。
当 performance_schema=ON 时，查询语句插桩默认处于启用状态。sys.statement_analysis 表提供了标准化查询的汇总统计信息。它可以解答以下问题：
- 哪些查询会进行全表扫描？
- full_scan/exec_count：查找查询是否经常执行全表扫描（通常效率低下）
- 哪些查询运行速度较慢？
- avg_latency：平均查询执行时间
- 哪些查询效率低下？
- rows_examined_avg/rows_sent_avg：用于读取查询。理想的比率为 1。比率越高，查询的效率就越低。
- rows_examined_avg/rows_affected_avg：用于写入查询。理想的比率为 1。比率越高，查询的效率就越低。
- 哪些查询使用临时表且必须转换为磁盘临时表？
- tmp_disk_tables/tmp_tables：查看 tmp_table_size/max_heap_table_size 是否足够
- 哪些查询使用 filesort？
- rows_sorted/exec_count、sort_merge_passes/exec_count：用于标识使用大量排序的查询而且可以使用更大的 sort_buffer_size
如果您使用 MySQL Workbench，则它具有基于 sys 视图的性能架构报告。此报告包含“高费用 SQL 语句”部分，其中提供了有关查询性能的数据分析。
使用慢日志和工具
慢速日志会捕获运行时间超过 long_query_time 的所有查询。此外，它还会记录查询执行时间、锁定时间、检查的数据行和发送的数据行。与使用常规日志相比，额外的执行统计信息使其成为分析数据库查询的首选候选对象。
启用慢速日志是一种很好的做法。通常，long_query_time 应保持在合理的阈值范围内，以捕获要查看和优化的查询。
log_output=FILE
slow_query_log=ON
long_query_time=2
有时，最好设置 long_query_time=0 以捕获一小段时间内的所有查询，并查看查询量和性能概况。
还有一些工具（例如 mysqldumpslow 和 pt-query-digest）可以提取查询签名并生成报告显示查询统计信息。
还有其他一些第三方监控工具可用于生成有关查询统计信息的报告，例如 Percona Monitoring and Management、SolarWinds Database Performance Monitor（以前称为 VividCortex）等。
优化查询
在事务中捕获查询后，下一步是优化查询。
说明 - 需要关注的内容
EXPLAIN 命令提供查询执行计划，从 8.0.18 开始，EXPLAIN ANALYZE 命令将运行一条语句，并生成 EXPLAIN 输出以及执行时间。
MySQL Query Insights 可让您便捷地访问 EXPLAIN 方案。
我们在输出内容中寻找什么？
- “rows”字段显示要读取的行数
- IO 是最耗时的部分。如果查询需要读取大量数据，可能会降低速度。 如需大致了解这一点，请在联接的表之间乘以“rows”。在上面的示例中，即为 858 * 23523。从 t2 读取 23523 行，从 t1 读取 858 行，这不是最理想的。鉴于此，优化应在每次迭代时减少 t2 中的数据访问量。
- “type”字段说明表的联接类型
- 类型“索引”表示已扫描该索引。如果索引满足表中所需的全部数据，“额外”字段将会显示“使用索引”。
- 类型“范围”意味着，不仅会使用索引，还会提供范围条件以限制数据扫描。
- 对于联接顺序中的后续表，类型“eq_ref”意味着，对于先前表中行的每个组合，从该表中读取一行，这是最有效的。
- 类型“ref”表示索引匹配为 1:m 而不是 1:1。对于之前表中的每个行组合，系统将从此表中读取多行。
- 需要避免的类型是“ALL”。也就是说，系统会对之前表中的每个行组合进行全表扫描。
- “key”字段会显示实际使用的索引。
- 选择使用哪个索引取决于可能已过期的索引基数。因此，请务必验证是否使用了选择性最强的索引。
- “key_len”字段表示密钥长度（以字节为单位）。
- 对于多列索引，key_len 会建议使用的索引部分。例如，如果索引包含 (col1, col2, col3)，且查询条件为“col1 = n and col2 like '%string%'”，则只有 col1 用于索引过滤。如果查询可以更改为“col1 = n and col2 like 'string%'”，则这两者（col1、col2）都将用于索引过滤。这种小小的变化可能会对查询性能产生巨大影响。
- “Extra”字段包含有关查询计划的更多信息
- “使用临时表”表示创建了可能会生成磁盘临时表的内部临时表
- “使用文件排序”意味着排序无法利用任何索引，并且需要排序缓冲区，还可能需要临时磁盘文件
- “使用索引”意味着此表中需要的所有数据都包含在索引中；无需读取数据行
查询性能分析
会话状态变量可用于获取查询执行详情。
首先，清除会话变量，然后运行查询并检查计数器。例如，Handler_* 状态显示了数据访问模式和行数。Created_* 会显示是否创建了临时表和/或磁盘临时表。Sort_* 将显示排序合并凭证的数量和排序的行数。如需了解更多会话变量，请参阅文档。
SHOW PROFILE 语句按执行阶段提供查询执行时间，这也是一种有用的信息。
优化查询执行计划
在了解查询执行计划后，您可以通过多种方式来影响和优化该计划。
- 添加或更新索引定义
- 过滤效果更好，数据访问权限更少
- 如需排序，请避免文件排序
- 更新索引统计信息（如果此功能已关闭）
- ANALYZE TABLE <tbl>；
- 然后重新检查 EXPLAIN 方案输出
- 使用索引提示
- 如需建议或强制使用某个索引进行过滤，请使用联接或排序/分组语句
- 使用 STRAIGHT_JOIN 定义表联接顺序
- 使用优化器提示
优化会话的执行
要针对特定查询优化服务器配置，强烈建议您使用会话级变量，而不是更改会影响所有会话的全局值。
常用的会话值包括：
总结
总而言之，对于查询调整，我们讨论了三个方面：
- 在编写查询时做出明智的决策。 这些决策是查询性能、总体服务器吞吐量和服务器性能的驱动因素。
- 在应用端和数据库端都跟踪查询执行数据。应用端日志记录非常重要。它可根据业务兴趣进行配置并反映业务运营。
- 最后，还有一些工具可以帮助您了解查询执行计划、与不同步骤关联的费用以及优化查询的方法。
相关产品和服务
Google Cloud 提供旨在满足您业务需求的代管式 MySQL 数据库，可以完成包括弃用本地数据中心、运行 SaaS 应用和迁移核心业务系统在内的各种任务。
