数据库对任何应用的性能都起着至关重要的作用。MySQL 数据库也不例外。因此,了解数据库调整、设计和配置可帮助提高应用性能的多种方式非常重要。您可以通过下面这些方式优化 MySQL 以实现最佳性能。
查询调整
数据库性能调整从应用开始。应用如何将业务要求转化为数据库查询,这对应用的复杂性和效率有着巨大的影响。真正衡量性能的是每个数据库实例对业务需求的贡献程度。
架构设计
在关系型数据库中定义实体和关系的方式决定了对数据库查询的响应有多简单或复杂。此外,主键和二级索引的定义方式也起着重要作用。
服务器配置
服务器配置负责优化和最大程度地利用系统资源。系统资源是指中央处理单元 (CPU) 核心、物理机器或虚拟机 (VM) 上的内存、底层存储系统,以及网络。
动态服务器调整
持续监控、优化和性能调整,以适应数据库工作负载的实际动态变化。
本文重点介绍查询调整。我们将在后续文章中介绍其余主题。
我们通常通过查看数据库查询来开始查询调整过程。更好的方法是先评估将业务要求转换为数据库查询的效率如何。对查询的业务需求的解释和处理决定了成本的大小。
第一步是排定要优化的查询的优先级:
在数据库架构设计期间,这两个类别需要优化。
事务是一个逻辑单元,其中包含的所有语句要么全部提交,要么回滚。事务是一项为 MySQL 提供原子性、一致性、隔离性和持久性 (ACID) 的功能。
在 MySQL 的存储引擎 InnoDB 中,所有用户活动都发生在事务内。默认情况下,自动提交模式处于启用状态,这意味着每个 SQL 语句都会自行形成单个事务。如需在启用自动提交的情况下执行多语句事务,请使用 START_TRANSACTION 或 BEGIN 显式启动事务,并使用 COMMIT 或 ROLLBACK 结束事务。如果停用自动提交模式,会话始终会打开一个事务,直到 COMMIT 或 ROLLBACK 结束事务并开始新的事务。
处理事务的最佳实践是尽可能缩短事务。这是因为,长事务具有一些弊端,如本文中进一步所述。
可以在应用端或数据库端捕获查询。
记录数据库查询和查询执行时间是一种很好的开发做法。应用端日志记录有助于轻松评估查询在其业务环境中的效果和效率。例如,用户可能会记录每个查询的响应时间或针对某些功能的日志响应时间。这也是获取多语句事务的总执行时间的一种简单方式。
此外,通过应用端日志记录测量的查询响应时间是包括网络时间在内的端到端测量时间。它对从数据库中记录的查询执行时间进行了补充,并可让您轻松确定问题出在网络问题还是数据库上。
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 表提供了标准化查询的汇总统计信息。它可以解答诸多问题,例如:
如果您使用 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 计划。
我们在输出中寻找什么?
会话状态变量可用于获取查询执行详情。
首先,清除会话变量,然后运行查询并检查计数器。例如,Handler_* 状态显示了数据访问模式和行数。如果创建了临时表和/或磁盘上的临时表,则会显示 Created_*。Sort_* 显示排序合并传递的次数和已排序的行数。有关更多会话变量的说明,请参阅文档。
SHOW PROFILE 语句按执行阶段提供查询执行时间,这也非常有用。
在了解查询执行计划后,您可以通过多种方式来影响和优化计划。
若要针对特定查询优化服务器配置,强烈建议您使用会话级变量,而不是更改会影响所有会话的全局值。
常用的会话值包括:
总的来说,关于查询调整,我们讨论了以下三个方面: