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

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

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

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

在 MySQL 的存储引擎 InnoDB 中，所有用户活动都发生在事务内。默认情况下，自动提交模式处于启用状态，这意味着每个 SQL 语句都会自行形成单个事务。如需在启用自动提交的情况下执行多语句事务，请使用 START_TRANSACTION 或 BEGIN 显式启动事务，并使用 COMMIT 或 ROLLBACK 结束事务。如果停用自动提交模式，会话始终会打开一个事务，直到 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 及更高版本，performance_schema 功能默认处于启用状态。启用或停用此功能需要重启实例。

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

• 对哪些查询执行全表扫描？
• full_scan/exec_count：查找查询是否经常执行全表扫描（通常效率低下）
• 哪些查询运行缓慢？
• avg_latencyavg_latency：平均查询执行时间
• 哪些查询效率低下？
• rows_examined_avg/rows_sent_avg：用于读取查询。理想的比率为 1。该比率越高，查询的效率越低。
• rows_examined_avg/rows_affected_avg：用于写入查询。理想的比率为 1。该比率越高，查询的效率越低。
• 哪些查询使用临时表，并且必须转换为磁盘临时表？
• tmp_disk_tablestmp_disk_tablestmp_disk_tables/tmp_tables：查看 tmp_disk_tablestmp_disk_tablestmp_table_size/max_heap_table_size 是否足够
• 哪些查询使用 filesort？
• 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_outputlog_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 Query 数据分析让您可以便捷地访问 EXPLAIN 计划。

我们在输出中寻找什么？

• “rows”字段显示要读取的行数
• IO 是最耗时的部分。如果查询需要读取大量数据，则查询速度可能会很慢。如需大致了解这一点，请将联接表中的“行”相乘。在上述示例中，乘积为 858 * 23523。从 t2 读取 23523 行，并为 t1 中的 858 行每行读取 23523 行，这听起来不太理想。有鉴于此，优化将是每次迭代减少 t2 中的数据访问量。
• “type”字段说明表的联接类型
• 类型“index”表示索引已扫描。如果索引满足表中所需的所有数据，“Extra”字段将显示“Uses index”。
• “范围”类型表示不仅使用索引，还表示提供了范围条件来限制数据扫描。
• 对于联接顺序中的后续表，类型“eq_ref”意味着，对于先前表中行的每个组合，从该表中读取一行，这是最有效的。
• 类型“ref”表示索引匹配为 1:m 而不是 1:1。对于之前表中的每个行组合，系统将从此表中读取多行。
• 需要避免的类型是“ALL”。也就是说，系统会对之前表中的每个行组合进行全表扫描。
• “key”字段会显示实际使用的索引。
• 选择使用哪个索引是基于索引基数的，而该基数可能已过时。因此，请务必验证是否使用了最具选择性的索引。  
• “key_len”字段用于指明密钥长度（以字节为单位）。
• 使用多列索引时，key_len 会建议索引所使用的部分。例如，如果索引包含 (col1, col2, col3)，并且查询条件为“col1 = n，col2 类似于‘%string%’”，则只有 col1 将用于索引过滤。如果查询可以更改为“col1 = n and col2 like 'string%'”，则两者（col1、col2）都将用于索引过滤。这一小小的变化可能会对查询性能产生显著影响。
• “Extra”字段包含有关查询计划的其他信息
• “使用临时表”表示创建了可能会生成磁盘临时表的内部临时表
• “使用文件排序”意味着排序无法利用任何索引，并且需要排序缓冲区，还可能需要临时磁盘文件
• “使用索引”是指此表中需要的所有数据都包含在索引中；无需读取数据行

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

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

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

在了解查询执行计划后，您可以通过多种方式来影响和优化计划。

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

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

常用的会话值包括：

总的来说，关于查询调整，我们讨论了以下三个方面：

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