第一步是确定要优化的查询的优先级：

1. 确定需要最短响应时间的数据库问题
2. 识别频繁出现的数据库问题

在数据库架构设计期间，需要优化这两个类别。

事务是一个逻辑单元，其中包含的所有语句都将完全提交或回滚。事务是为 MySQL 提供原子性、一致性、隔离性和持久性 (ACID) 的功能。

在 MySQL 的存储引擎 InnoDB 中，所有用户活动都发生在事务内。默认情况下，自动提交模式处于启用状态，这意味着每个 SQL 语句会自行形成一个事务。如需在启用自动提交的情况下执行多语句事务，请使用 START_TRANSACTION 或 START_TRANSACTION 显式启动事务，并使用 START_TRANSACTION 或 START_TRANSACTION 结束事务。如果自动提交模式已停用，会话将始终打开一个事务，直到 COMMIT 或 ROLLBACK 结束该事务并启动一个新事务。

处理事务的最佳实践是尽可能缩短事务。这是因为，长事务具有一些弊端，如本文中进一步所述。

1. 锁争用时间过长，导致查询速度变慢且查询可能失败

• 事务期间会持有 InnoDB 行级锁定
• 这可能会增加锁等待时间、锁定超时和死锁，导致查询速度变慢或完全失败

2. 由于撤消日志量较大而导致服务器性能下降

• 由于 InnoDB 多版本并发控制 (MVCC) 功能，已更改行的旧版本会存储在撤消日志中，以实现一致的读取和回滚。使用默认的“可重复读取”隔离级别时，只有在事务开始前才开始执行撤消日志，系统才会完全清除撤消日志。因此，长时间运行的事务会累积撤消日志。这可以通过 SHOW ENGINE INNODB STATUS 命令行函数中的历史记录列表进行观察和监控。
• 当历史记录列表数以百万计时，由于回滚段的互斥争用、读取撤消日志的次数增加以及遍历撤消日志的关联列表时间增加，服务器性能将受到不利影响。此外，完全清除线程的工作量也会增加。

3. 磁盘用量增加

• 存储在磁盘（系统表空间或撤消表空间）上的撤消日志增加

4. 关停时间延长

• 在正常关停期间，正在进行的事务将回滚。回滚时间通常长于达到该点所花费的时间。因此，服务器关停可能需要较长时间才能完成回滚。

5. 崩溃恢复时间延长

• 在崩溃恢复期间，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 上，对于内存高于 15GB 的 MySQL 8.0.26 及更高版本，performance_schema 功能默认处于启用状态。启用或停用此功能需要重启实例。

当 performance_schema=ON 时，查询语句插桩默认处于启用状态。sys.statement_analysis 表提供了标准化查询的汇总统计信息。它可以解答诸多问题，例如：

• 对哪些查询执行全表扫描？
• full_scan/exec_countfull_scan/exec_count：查找查询是否经常执行全表扫描（通常效率低下）
• 哪些查询运行缓慢？
• avg_latencyavg_latency：平均查询执行时间
• 哪些查询效率低下？
• rows_examined_avg/rows_examined_avg：用于读取查询。理想的比率为 1。该比率越大，查询的效率越低。
• rows_examined_avg/rows_examined_avg：用于写入查询。理想的比率为 1。该比率越大，查询的效率越低。
• 哪些查询使用临时表，并且必须转换为磁盘临时表？
• tmp_disk_tablestmp_disk_tablestmp_disk_tables/tmp_tables：查看 tmp_disk_tablestmp_disk_tablestmp_table_size/max_heap_table_size 是否足够
• 哪些查询使用文件排序？
• rows_sorted/exec_count、sort_merge_passes/exec_count：用于标识使用大量排序的查询而且可以使用更大的 sort_buffer_sizerows_sortedrows_sortedrows_sortedrows_sortedrows_sorted

如果您使用 MySQL Workbench，系统会提供基于系统视图的性能架构报告。报告中有一个“高成本 SQL 语句”部分，提供了有关查询性能的数据分析。

使用慢速日志 + 工具

慢日志会捕获运行时间超过 long_query_time 的所有查询。还会记录查询执行时间、锁定时间、检查的数据行和发送的数据行。额外的执行统计信息使其成为分析数据库查询的首选，而不是使用常规日志。

启用慢速日志是一种很好的做法。通常，long_query_time 应保持在合理的阈值，以捕获您想要查看和优化的查询。

log_output=FILE

slow_query_logslow_query_log=ON

long_query_timelong_query_time=2

有时，最好设置 long_query_timelong_query_time=0 以捕获一小段时间内的所有查询，并查看查询量和性能概况。

mysqldumpslow 和 pt-query-digest 等工具可以提取查询签名并生成报告以显示查询统计信息。   

还有其他一些第三方监控工具可用于生成有关查询统计信息的报告，例如 Percona Monitoring and Management、SolarWinds Database Performance Monitor（以前称为 VividCortex）等。

在事务中捕获查询后，下一步是对其进行优化。

EXPLAIN 命令提供查询执行计划，从 8.0.18 开始，EXPLAIN ANALYZE 命令将运行一条语句，并生成 EXPLAIN 输出以及执行时间。

借助 MySQL 查询数据分析，您可以便捷地访问 EXPLAIN 计划。

我们在输出结果中查找什么？

• “rows”字段显示要读取的行数
• IO 是最耗时的部分。如果查询需要读取大量数据，则可能会很慢。如需大致了解此情况，请将联接的表中的“rows”相乘。在上述示例中，乘积为 858 * 23523。对于 t1 中的 858 行，每一行都从 t2 中读取 23523 行，这听起来不太理想。因此，每次迭代的优化目标是减少从 t2 开始的数据访问量。
• “type”字段说明表的联接类型
• “index”类型表示扫描索引。如果索引满足表中所需的所有数据，则“额外”字段会显示“使用索引”。
• “range”类型表示不仅会使用索引，还会提供范围条件来限制数据扫描。
• 对于联接顺序中的后续表，类型“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) 都将用于索引过滤。这种细微的变化可能会显著改善查询性能。
• “额外”字段包含有关查询计划的其他信息
• “使用临时表”表示创建了可能会生成磁盘临时表的内部临时表
• “使用文件排序”意味着排序无法利用任何索引，并且需要排序缓冲区，还可能需要临时磁盘文件
• “使用索引”表示该表中所需的所有数据都包含在索引中；无需读取数据行

会话状态变量可用于获取查询执行详细信息。

首先，清除会话变量，然后运行查询并检查计数器。例如，Handler_* 状态会显示数据访问模式和行量。Created_* 会显示是否创建了临时表和/或磁盘上的临时表。Sort_* 将显示排序合并传递数和已排序的行数。如需了解更多会话变量，请参阅文档。

SHOW PROFILE 语句按执行阶段提供查询执行时间，这也非常有用。

理解查询执行计划后，有多种方式可以影响和优化计划。

• 添加或更新索引定义
• 可减少数据访问，实现更精准的过滤
• 如需排序，请避免文件排序
• 更新索引统计信息（如果此功能已关闭）
• ANALYZE TABLE <tbl>；
• 然后重新检查 EXPLAIN 计划输出
• 使用索引提示
• 如需建议或强制使用某个索引进行过滤，请使用联接或排序/分组语句
• 使用 STRAIGHT_JOIN 定义表联接顺序
• 使用优化器提示

要针对特定查询优化服务器配置，强烈建议您使用会话级变量，而不是更改会影响所有会话的全局值。

常用的会话值包括：

总而言之，关于查询调整，我们讨论了三个方面：

• 在编写查询时有意识地做出决策。这些决策是查询性能、整体服务器吞吐量和服务器性能的驱动因素。
• 在应用端和数据库端跟踪查询执行数据。应用端日志记录非常重要。您可以根据商业利益进行配置，并反映经营活动情况。
• 最后，有几种工具有助于了解查询执行计划、与不同步骤相关的费用以及优化查询的方法。