优秀的编程知识分享平台

网站首页 > 技术文章 正文

如何优化数据库的查询速度

nanyue 2024-11-26 07:01:27 技术文章 1 ℃

作为一个程序员,数据库查询是必不可少的事情,有时候,我们写的SQL语句执行速度很慢,那么这是什么原因呢?我们需要怎么来优化它呢?接下来我们来进行分析。

导致sql查询缓慢的原因有很多种,它可能是索引的错误使用,也可能是存储引擎本身的错误,在大多数情况下,查询变得缓慢是因为开发人员或数据库管理员忽略了监控它们并密切关注它们的性能。接下来我们就来重点搞清楚如何避免这种情况发生。

Mysql执行查询的顺序

什么是查询?这是最简单的问题,但是我相信大多数程序员都回答不上来。查询是由较小的任务组成的任务。

如果我们想提高它们的性能,我们可以缩短那些较小的任务或消除它们。以下是 MySQL 执行查询的方式:

  1. 首先,MySQL 将 SQL 语句发送到服务器执行。
  2. MySQL 检查查询缓存。如果那里存在结果,则返回它们,如果没有,MySQL 继续下一步。
  3. 服务器解析给定的 SQL 查询,并制定查询优化计划。
  4. 执行计划,调用存储引擎。
  5. 存储引擎返回行——我们看到了结果!

然而,要观察 MySQL 如何在更深层次上执行其查询,我们可能应该对它们进行分析,而不是简单地查看它们的外部。

分析查询

为了优化我们的查询速度,在尝试优化任何东西之前,我们必须首先了解它们做了什么以及它们经历了哪些执行阶段——换句话说,我们需要分析它们。

Mysql本身已经提供了分析的功能,我们利用它自带的功能,可以轻而易举地对查询过程进行分析。

开启分析功能也非常简单,只需要修改配置参数即可:SET profiling = 1。开启后,运行SHOW PROFILES,我们就可以清晰地看到查询共经历了哪些阶段以及每个阶段执行所需的时间。

STATE

DURATION

Starting

0.000017

Waiting for query cache lock

0.000004

Waiting on query cache mutex

0.000006

Checking query cache for query

0.000027

Checking permissions

0.000005

Opening tables

0.000276

System lock

0.000010

Waiting for query cache lock

0.000022

Waiting on query cache mutex

0.000021

Init

0.000007

Optimizing

0.000005

Statistics

0.000004

Preparing

0.000055

Executing

0.000042

Sending data

0.000004

End

0.000002

Query end

0.000067

Closing tables

0.000005

Freeing items

0.000056

Logging slow query

0.000001

Cleaning up

0.000002

到这里大家明白我上文中所说的“查询是由较小的任务组成的任务”是什么意思吗?上面列出了这些较小的任务。既然我们知道我们的查询在结果到达我们的屏幕之前经历了哪些任务,那么优化起来就会比较容易了。

优化查询

为了优化我们的查询,我们可以缩短这些任务的执行过程,或者直接让Mysql不去执行它们。

接下来有几种方法提供给大家:

  1. 确保使用适当的权限:检查权限是执行查询时首先发生的事情之一,通过确保运行查询的用户具有适当的权限,我们可以缩短任务。
  2. 保持查询缓存足够大:如果您的MySQL版本为5.7.20以前,可以通过设置参数query_cache_size来修改它。但请注意,MySQL 8 中已经删除这个参数了。
  3. 正确使用索引和分区:虽然索引和分区都会降低INSERT、UPDATE 和DELETE的性能,但它们会加快 read ( SELECT) 查询的速度,有时会提高一个数量级。使用索引,MySQL 将能够SELECT更快地执行查询,因为它将扫描更少的数据——因此,您的查询将更快。如果需要,您还可以使用log_queries_not_using_indexes参数记录所有不使用索引的查询,但为此,您需要启用该slow_query_log参数(将其设置为 的值ON)
  4. 不要向 MySQL查询您不需要的数据:不要使用SELECT *,而只选择查询成功执行所需的列。换句话说,不要让 MySQL 检查不必要的数据。
  5. 如果需要,请启用慢查询日志:正如你所看到的,如果一个查询很慢,它会被记录在慢查询日志中(它是最后执行的任务之一)——保持你的slow_query_log变量ON,也许可以调整 MySQL 理解什么是“慢查询”的方式:默认情况下, MySQL 会记录运行时间超过 10 秒的任何查询,但可以通过调整long_query_time parameter参数来设置。

分析之后做什么?

  1. 架构和数据类型:正确优化架构和数据类型对于高性能至关重要,永远不要忽视它们,如果您忽略了这些事情,很可能会浪费硬盘空间,并可能在未来降低数据库实例的性能。
  2. 字符集和排序规则:MySQL 支持许多不同的字符集和排序规则,它们中的每一个都必须小心使用,以节省空间并避免对性能产生负面影响。在这种情况下,请避免使用未知的小众字符集和排序规则。
  3. 索引:MySQL 支持多种索引,当然,它们有自己的优点和缺点。然而,在大多数用例中,它们可以显着提高性能。
  4. 分区:分区当然是索引的好朋友,它们也有助于提高查询性能——但是,必须要正确的来使用它。例如,它们可以让 MySQL 一次备份或恢复与一个分区相关的数据,而不是所有数据。
  5. 服务器设置:服务器设置是 MySQL 实例的基石——时刻注意它们。我们需要学习如何为大数据集优化 InnoDB 。
  6. 硬件的支持:硬件位于你在 MySQL 中所做的一切,通过拥有合适的硬件,你可以正确优化服务器设置以获得高性能。因此,请始终根据需要使用尽可能多的硬件,并为 MySQL 分配足够的资源,使其以最佳的状态运行。
  7. 借助技术大神的博客和文档:博客是扩展知识的理想场所。他们可以告诉你应该如何使用数据库实例来随时随地提高数据库性能。始终关注相关文档,这对您的数据库现在和将来都至关重要。

Tags:

最近发表
标签列表