首页 / 知识

关于sql：最喜欢的性能调整技巧

2023-04-12 16:17:00

Favourite performance tuning tricks

当您有需要性能调整的查询或存储过程时，首先要尝试哪些操作？

这是我经常给别人询问优化问题的便捷清单。
我们主要使用Sybase，但是大多数建议将全面适用。

例如，SQL Server附带了许多性能监视/调整位，但是如果您没有这样的功能(甚至可能没有)，那么我将考虑以下内容...

我看到的问题中有99％是由于在联接中放置太多表引起的。解决方法是进行一半的连接(使用某些表)，并将结果缓存在临时表中。然后在该临时表上进行其余查询的联接。

查询优化清单

在基础表上运行UPDATE STATISTICS
- 许多系统将其作为计划的每周工作运行
从基础表中删除记录(可能存档已删除的记录)
- 考虑每天或每周一次自动执行此操作。
重建索引
重建表(bcp数据输出/输入)
转储/重新加载数据库(严重，但可能会修复损坏)
建立更合适的新索引
运行DBCC以查看数据库中是否可能损坏
锁/死锁
- 确保没有其他进程在数据库中运行
  - 特别是DBCC
- 您正在使用行级或页面级锁定吗？
- 在开始查询之前以独占方式锁定表
- 检查所有进程是否以相同顺序访问表
是否正确使用了索引？
- 如果两个表达式的数据类型完全相同，则联接将仅使用索引
- 仅当索引中的第一个字段在查询中匹配时才使用索引
- 是否在适当的地方使用聚簇索引？
  - 范围数据
  - 值1和值2之间的WHERE字段
小联接就是好联接
- 默认情况下，优化程序一次只考虑表4。
- 这意味着在连接超过4个表时，很有可能选择非最佳查询计划
分手加入
- 你能分手加入吗？
- 将外键预选到临时表中
- 进行一半的连接并将结果放在临时表中
您使用的是正确的临时表吗？
- #temp表的性能可能比大容量(数千行)的@table变量好得多。
维护汇总表
- 在基础表上使用触发器进行构建
- 每天/每小时/等等构建
- 临时创建
- 逐步构建或拆卸/重建
使用SET SHOWPLAN ON查看查询计划是什么
开启SET STATS IO，看看实际发生了什么
使用编译指示强制索引：(索引：myindex)
使用SET FORCEPLAN ON强制执行表顺序
参数嗅探：
- 将存储过程分为2
- 从proc1调用proc2
- 如果@parameter已被proc1更改，则允许优化程序在proc2中选择索引
您可以改善硬件吗？
你什么时候跑步？有安静的时间吗？
Replication Server(或其他不间断进程)是否正在运行？你可以暂停吗？运行它例如。每小时？

对运行查询的最佳路径有一个很好的了解。

始终检查查询计划。

打开STATS，以便可以检查IO和CPU性能。集中精力减少这些数字，而不必减少查询时间(因为这可能会受到其他活动，缓存等的影响)。

寻找进入运算符的大量行，但找出少量的行。通常，索引可以通过限制进入的行数来帮助(这可以节省磁盘读取)。

首先关注最大成本的子树。更改该子树通常可以更改整个查询计划。

我见过的常见问题是：

如果有很多联接，有时Sql Server会选择扩展联接，然后应用WHERE子句。通常，您可以通过将WHERE条件移到JOIN子句或带有内联条件的派生表中来解决此问题。视图可能导致相同的问题。
次优联接(LOOP，HASH，MERGE)。我的经验法则是，当顶行与底行相比很少时，使用LOOP连接；当集合大致相等且有序时，使用MERGE；对于其他所有内容，则使用HASH。添加联接提示可让您测试理论。
参数嗅探。如果首先使用不切实际的值运行存储的proc(例如，用于测试)，则缓存的查询计划对于您的生产值可能不是最佳的。再次运行带有RECOMPILE应该验证这一点。对于某些存储过程，尤其是那些处理大小范围不同的过程(例如，今天和昨天之间的所有日期-这将需要INDEX SEEK-或去年和今年之间的所有日期-最好使用INDEX SCAN )，您可能每次都必须使用RECOMPILE运行。
缩进错误...好吧，所以Sql Server对此没有问题-但我确定在确定格式之前，无法理解查询。

稍微偏离主题，但是如果您可以控制这些问题...
高水平和高影响力。

对于高IO环境，请确保您的磁盘适用于RAID 10或RAID 0 + 1或RAID 1和RAID 0的某些嵌套实现。
不要使用少于1500K的驱动器。
确保磁盘仅用于数据库。 IE浏览器没有登录没有操作系统。
关闭自动增长或类似功能。让数据库使用所有预期的存储。不一定当前正在使用什么。
设计用于类型查询的架构和索引。
如果它是日志类型表(仅插入)并且必须在数据库中，则不要为其编制索引。
如果您要分配报告(带有多个联接的复杂选择)，则应考虑使用星型或雪花模式创建数据仓库。
不要害怕复制数据以换取性能！

CREATE INDEX

确保您的WHERE和JOIN子句有可用的索引。这将大大加快数据访问速度。

如果您的环境是数据集市或仓库，则几乎所有可能的查询都应包含索引。

在事务环境中，索引的数量应更少，其定义应更具战略意义，以便索引维护不会拖累资源。 (与INSERT, UPDATE,和DELETE操作一样，索引维护是指必须更改索引的叶子以反映基础表中的更改的时间。)

另外，请注意索引中字段的顺序-字段的选择性越强(基数越高)，它应该在索引中越早出现。例如，假设您要查询二手车：

1
2
3
4

SELECT i.make, i.model, i.price
FROM dbo.inventory i
WHERE i.color = 'red'
AND i.price BETWEEN 15000 AND 18000

价格通常具有较高的基数。可能只有几十种颜色可用，但可能有成千上万种不同的要价。

在这些索引选择中，idx01提供了更快的路径来满足查询：

1 2	CREATE INDEX idx01 ON dbo.inventory (price, color) CREATE INDEX idx02 ON dbo.inventory (color, price)

这是因为满足价格要求的汽车要少于颜色选择，因此查询引擎要分析的数据要少得多。

众所周知，我有两个非常相似的索引，只是在字段顺序上有所不同，以加快一个查询(名字，姓氏)和另一个查询(姓氏，名字)的速度。

我最近了解到的一个技巧是，SQL Server可以在更新语句中更新局部变量以及字段。

1 2	UPDATE TABLE SET @variable = COLUMN = @variable + otherColumn

或更易读的版本：

1
2
3
4

UPDATE TABLE
SET
@variable = @variable + otherColumn,
COLUMN = @variable

在实现递归计算时，我用它来替换复杂的游标/联接，并且还获得了很多性能。

以下是一些细节和示例代码，它们在性能上做出了惊人的改进：
http://geekswithblogs.net/Rhames/archive/2008/10/28/calculating-running-totals-in-sql-server-2005---the-optimal.aspx

假设这里使用MySQL，请使用EXPLAIN找出查询的内容，确保尽可能高效地使用索引，并尝试消除文件排序。高性能MySQL：优化，备份，复制和其他功能以及MySQL Performance Blog都是一本关于此主题的好书。

@Terrapin值得一提的是，isnull和合并之间还有一些其他区别(除了ANSI合规性，这对我来说是一个很大的区别)。

合并与IsNull

有时，在SQL Server中，如果在where子句中使用OR，它将真正提高性能。而不是使用OR，只需执行两个选择并将它们合并在一起即可。您可以以1000倍的速度获得相同的结果。

我通常从联接开始-一次将每个联接从查询中剔除，然后重新运行查询以了解是否有我遇到的特定联接。

在我所有的临时表上，我喜欢添加唯一的约束(在适当的情况下)以创建索引，并添加主键(几乎始终)。

1
2
3
4
5
6

DECLARE @temp TABLE(
RowID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
SomeUniqueColumn VARCHAR(25) NOT NULL,
SomeNotUniqueColumn VARCHAR(50) NULL,
UNIQUE(SomeUniqueColumn)
)

查看where子句-验证索引的使用/验证没有任何傻事

1	WHERE SomeComplicatedFunctionOf(TABLE.Column) = @param --silly

我习惯总是使用绑定变量。如果RDBMS不缓存SQL语句，则绑定变量可能无济于事。但是，如果您不使用绑定变量，则RDBMS将没有机会重用查询执行计划和已解析的SQL语句。节省的费用可能是巨大的：http：//www.akadia.com/services/ora_bind_variables.html。我主要使用Oracle，但是Microsoft SQL Server的工作方式几乎相同。

以我的经验，如果您不知道是否正在使用绑定变量，则可能不是。如果您的应用程序语言不支持它们，请找到一种支持的语言。有时，您可以通过对查询B使用绑定变量来修复查询A。

之后，我与我们的DBA进行了交谈，以找出导致RDBMS最痛苦的原因。请注意，您不应询问"为什么此查询速度慢？"这就像要求您的医生将您的阑尾取出。当然，您的查询可能是问题所在，但很可能其他问题出了问题。作为开发人员，我们倾向于考虑代码行。如果线路较慢，请修复该线路。但是RDBMS是一个非常复杂的系统，查询速度慢可能是更大问题的征兆。

太多的SQL调优技巧是狂热的偶像。在大多数情况下，问题与您使用的语法无关或具有最小的关系，因此通常最好使用可以使用的最简洁的语法。然后，您可以开始研究调整数据库的方法(而不是查询)。仅在失败时调整语法。

像任何性能调整一样，请始终收集有意义的统计信息。除非您正在调整用户体验，否则不要使用挂钟时间。取而代之的是看诸如CPU时间，读取的行和从磁盘读取的块之类的事情。人们常常为错误的事情进行优化。

如果可能，将NOT IN查询转换为LEFT OUTER JOIN。例如，如果要在表1中查找表2中外键未使用的所有行，可以执行以下操作：

1
2
3
4
5

SELECT *
FROM Table1
WHERE Table1.ID NOT IN (
SELECT Table1ID
FROM Table2)

但是，您可以获得以下更好的性能：

1
2
3
4

SELECT Table1.*
FROM Table1
LEFT OUTER JOIN Table2 ON Table1.ID = Table2.Table1ID
WHERE Table2.ID IS NULL

第一步：
看一下查询执行计划！
TableScan->错误
NestedLoop->警告提示
TableScan在NestedLoop后面-> DOOM！

将统计信息设置为"打开"
开启统计时间

在我这里，使用WITH(NoLock)运行查询几乎是标准操作。任何人都可以在数十GB的表上捕获正在运行的查询，而不会将其取出并删除。

确保索引长度尽可能小。这使DB一次可以从文件系统中读取更多键，从而加快了连接速度。我认为这适用于所有数据库，但是我知道这是针对MySQL的特定建议。

@戴维

Assuming MySQL here, use EXPLAIN to find out what is going on with the query, make sure that the indexes are being used as efficiently as possible...

在SQL Server中，执行计划使您有同感–告诉您正在命中哪些索引，等等。

用您筛选依据的clm为表格建立索引

本身不一定是SQL性能技巧，但肯定相关：

一个好主意是在可能的情况下使用memcached，因为它将直接从内存中获取预编译的数据而不是从数据库中获取预编译的数据会更快。还有一种内置了memcached的MySQL(第三方)。

我寻找：

展开所有CURSOR循环，并将其转换为基于集合的UPDATE / INSERT语句。
请注意以下任何应用程序代码：
- 调用返回大量记录的SP，
- 然后在应用程序中，遍历每条记录并调用带有参数的SP以更新记录。
- 将其转换为可以在一个事务中完成所有工作的SP。
任何执行大量字符串操作的SP。有证据表明数据结构不正确/未规范化。
任何重新发明轮子的SP。
一分钟内我无法理解它正在尝试执行的任何SP！

在SQL Server中，使用nolock指令。它允许select命令完成而不必等待-通常是其他事务完成。