You are currently viewing 如何提高SQL性能

如何提高SQL性能

SQL 性能问题的常见原因

  • 糟糕的架构设计:通常,糟糕的架构设计会减慢查询速度。存储数字的过宽数据类型(例如 VARCHAR)是常见的罪魁祸首。
  • 遗留数据仓库策略:许多遗留策略(如多维数据集和预聚合表)可能隐藏了许多级别的复杂性,包括嵌套视图。
  • 糟糕的索引策略:索引不足或索引过多都会产生相同的结果——查询速度慢和磁盘空间使用率高。
  • 错误配置的服务器:每台服务器都有自己的最佳实践,用于在 BIOS 级别和操作系统级别优化 CPU、内核和网络。不遵循这些建议可能会导致查询速度降低约 20%。
  • 功能不足的硬件或配置不足的实例:即使您不管理自己的数据库,运行 DBMS 的功能不足或过时的服务器也会降低您的性能。

提高现有系统的性能

当您的查询速度很慢时,可以尝试以下一些方法,而无需更改您的服务器或实例:

让数据库引擎做尽可能多的工作

大多数 DBMS 都非常有效地处理数据——比客户端应用程序更有效:

  • 文本处理、数学、摘要和排序方面的大量功能最好留给 DBMS,而不是应用程序
  • 将一些逻辑从应用程序转移到 DBMS 确保跨应用程序和用户的可重用性

最小化进出数据库的数据 I/O

对于非常大的查询,压力可能来自将结果集传输到客户端。你真的需要所有的数据吗? 如果不是,请减少 DBMS 中的结果集大小:

  • 仅提取您需要的列的名称,而不是使用SELECT *。否则,如果您有一张非常大的表格,客户可能很难将它们放在一起
  • 除非您需要查看每一行,否则限制结果集大小LIMIT
 
将复杂查询分解为更小的查询

大型查询可能很难解决。将查询分成小块并使用临时表可以使查询更易于理解。

  • 临时表和小查询更容易调试——通常不需要特殊语法
  • 临时表对失败不太敏感,因为查询优化器决定以不同的方式做事
  • 临时表和小查询让您有机会优化自己。有时这比 DBMS 优化器自己没有发现的东西要好
 
 
首选优化函数,限制通配符

供应商编写的函数通常优于链接在一起的 SQL 函数。

一些例子:

  • 对于大量 Unicode 文本,使用模式匹配 ( LIKE ‘%foo%’) 会导致计划非常低效。类似的功能ISPREFIXOF(x,’foo’)可能会表现得更好
  • 一些 DBMS(如 SQream DB 和 Postgres)区分大小写。如果您需要匹配字符串,请尝试仅在一侧执行或使用不区分大小写的匹配 ( ILIKE)
 
谨慎使用视图
视图是从查询创建的虚拟表。当您运行访问它们的查询时,通常会具体化视图。
 
  • 如果您的查询使用一个视图,或者甚至您的视图有另一个视图,您会在不知情的情况下运行许多查询(这在 SQream DB 中不是问题,但对于其他一些 DBMS,它可能会让您感到惊讶!)
  • 每小时/每天/每周或根据需要使用临时表来具体化视图
  • 考虑使用CTE而不是视图
 
检查您的索引策略
在许多 DBMS 中,索引可以加快查询速度,让 DBMS 知道在哪里查找数据。有选择地应用索引——关注具有高基数的列或您经常使用的列。
索引过多会降低写入性能(并占用大量空间):
 
  • 删除不使用或很少使用的索引。
  • 删除放置在具有随机数据或经常更新的列上的索引

索引策略不仅会影响性能,还会影响数据大小。当您的数据库扩展时,这可能会成为一个真正的问题。

遵循 DBMS 的最佳实践

每个 DBMS 都不同,因此请务必为您使用的 DBMS 应用最佳实践。

但是,有些事情永远是正确的:

1.如果一个查询很慢,请检查查询优化的最佳实践

2.如果您的整个数据库很慢,请检查您的系统:

  • 集群状态——所有节点都已启动,对语句开放
  • 查询分布——所有语句是否都在同一个节点上结束?
  • 数据分布——数据分布不正确吗?架构更改可以做一些好事吗?
  • 删除的行——包括 SQream DB、Vertica、Postgres 在内的许多大数据 DBMS 都受益于对已删除行的定期维护(也称为删除谓词、删除向量)
  • Locks – 检查是否有由于锁而等待的语句,例如在更新大表时
  • 计数器 – 如果您的 DBMS 支持,请设置计数器来跟踪内存和计算使用情况

更新你的DBMS

您可以调整的只有这么多。当您不能(或不会)进一步调整时,请考虑更新您的 DBMS。

新的 SQL DBMS,例如 SQream DB:

 

  • 充分利用现代计算资源
  • 可以自动化元数据收集
  • 拥有可以更好扩展的新方法,不易受到“成长的痛苦”的影响
  • 允许加入任意数量的表而不会对性能产生重大影响

即使在大部分优化良好的工作负载上,遵循最佳实践也可以将查询时间缩短 10%-30%。

这篇文章有一个评论

  1. gate io

    At the beginning, I was still puzzled. Since I read your article, I have been very impressed. It has provided a lot of innovative ideas for my thesis related to gate.io. Thank u. But I still have some doubts, can you help me? Thanks.

发表回复