最喜欢的性能调优把戏

当您有一个查询或存储过程需要性能调优时，您首先尝试的是什么？

38888 次浏览
小开

看看 where 子句——验证索引的使用/验证没有做什么傻事

where SomeComplicatedFunctionOf(table.Column) = @param --silly
小开

假设这里是 MySQL，使用 EXPLAIN 查找查询的情况，确保尽可能有效地使用索引，并尝试消除文件排序。高性能 MySQL: 优化、备份、复制等和 MySQL 性能博客都是关于这个话题的好书。

小开

@ 大卫

假设这里是 MySQL，使用 EXPLAIN 查找查询的情况，确保尽可能有效地使用索引..。

在 SQLServer 中，执行计划得到同样的结果——它告诉您正在访问哪些索引，等等。

小开

我通常从连接开始——我将一次从查询中剔除每个连接，然后重新运行查询，以了解是否存在某个特定的连接存在问题。

小开

根据筛选所依据的 clm 对表进行索引

小开

有时在 SQLServer 中，如果在 where 子句中使用 OR，那么性能将受到影响。不要使用 OR，只要做两个选择并将它们联合在一起。你在1000倍的速度下得到相同的结果。

小开

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

一个好主意是尽可能使用 memcached，因为直接从内存获取预编译数据比从数据库获取快得多。还有一种 MySQL 的味道，它内置了 memcached (第三方)。

小开
  • 在所有表前面加上 dbo.to 防止重新编译。
  • 查看查询计划并搜索表/索引扫描。
  • 在2005年，仔细检查管理视图中缺少的索引。
小开

在我的所有临时表中，我喜欢添加惟一的约束(在适当的地方)来创建索引和主键(几乎总是)。

declare @temp table(
RowID int not null identity(1,1) primary key,
SomeUniqueColumn varchar(25) not null,
SomeNotUniqueColumn varchar(50) null,
unique(SomeUniqueColumn)
)
小开

确保你的索引长度尽可能的小。这允许 DB 一次从文件系统中读取更多的键，从而加快连接的速度。我假设这对所有数据库都适用，但我知道这是对 MySQL 的特别推荐。

小开

我喜欢用

isnull(SomeColThatMayBeNull, '')

完毕

coalesce(SomeColThatMayBeNull, '')

当我不需要合并给你的多参数支持的时候。

Http://blog.falafel.com/2006/04/05/sqlserverarcanaisnullvscoalesce.aspx

小开

不要给存储过程名称加上“ sp _”前缀，因为所有系统过程都以“ sp _”开头，而且当调用 SQLServer 时，必须更加努力地搜索才能找到您的过程。

小开

有点跑题了，但如果你能控制这些问题..。
高水平和高影响。

  • 对于高 IO 环境，请确保您的磁盘适用于 RAID10或 RAID0 + 1，或者一些 RAID1和 RAID0的嵌套实现。
  • 不要使用少于150万的硬盘。
  • 请确保您的磁盘只用于您的数据库。 IE 没有日志没有操作系统。
  • 关闭自动增长或类似功能。让数据库使用预期的所有存储。不一定是目前正在使用的。
  • 为类型查询设计架构和索引。
  • 如果它是一个日志类型的表(只插入) ，并且必须在数据库不索引它。
  • 如果您进行了大量的报告(使用许多连接进行复杂的选择) ，那么您应该考虑使用星型或雪花模式创建一个数据仓库。
  • 不要害怕复制数据以换取性能！
小开
  1. 对于在头脑中运行查询的最佳路径有一个非常好的想法。
  2. 检查查询计划-始终如此。
  3. 打开 STATS，这样您就可以检查 IO 和 CPU 性能。专注于降低这些数字，而不一定是查询时间(因为查询时间可能受到其他活动、缓存等的影响)。
  4. 查找进入运算符的大量行，但出来的行数很少。通常，索引通过限制输入的行数(这样可以节省磁盘读取)来提供帮助。
  5. 首先关注最大的成本子树。更改该子树通常可以更改整个查询计划。
  6. 我见过的常见问题有:
    • 如果有很多联接，有时 SqlServer 会选择展开联接，然后应用 WHERE 子句。通常可以通过将 WHERE 条件移动到 JOIN 子句或内联条件的派生表中来解决这个问题。视图可能导致相同的问题。
    • 次优连接(LOOP vs HASH vs MERGE)。我的经验法则是，当上一行的行数与下一行相比非常少时，使用 LOOP 联接; 当集合大致相等且有序时，使用 MERGE; 对于其他所有行，使用 HASH。添加连接提示可以测试您的理论。
    • 参数嗅探。如果您最初使用不切实际的值来运行存储过程(比如用于测试) ，那么缓存的查询计划对于您的生产值来说可能是次优的。再次运行 WITHRECOMPILE 应验证这一点。对于一些存储处理器，特别是那些处理不同大小范围的处理器(比如，今天和昨天之间的所有日期——这将需要一个索引搜索——或者，去年和今年之间的所有日期——用索引扫描会更好) ，你可能每次都必须运行 WITHRECOMPILE。
    • 错误的缩进... 好吧，所以 Sql Server 对此没有问题-但是我确实发现在我修复格式之前不可能理解查询。
小开

我寻找:

  • 展开任何 CURSOR 循环并转换为基于集的 UPDATE/INSERT 语句。
  • 寻找任何应用程序代码:
    • 调用一个返回大量记录的 SP,
    • 然后在应用程序中，遍历每条记录，并使用参数调用 SP 来更新记录。
    • 将其转换为一个 SP，它在一个事务中完成所有工作。
  • 任何执行大量字符串操作的 SP。这表明数据结构不正确/没有规范化。
  • 任何重新发明轮子的 SP。
  • 任何一个我不能在一分钟内理解它想做什么的 SP！
小开

我已经养成了总是使用绑定变量的习惯。如果 RDBMS 不缓存 SQL 语句，那么绑定变量可能不起作用。但是如果不使用绑定变量，RDBMS 就没有机会重用查询执行计划和解析后的 SQL 语句。可以节省大量资金: http://www.akadia.com/services/ora_bind_variables.html。我主要和甲骨文合作但 Microsoft SQL Server 也差不多。

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

之后，我与我们的 DBA 交谈，找出是什么导致 RDBMS 最痛苦。注意，您不应该问“为什么这个查询很慢?”这就像让你的医生切除你的阑尾。当然，您的查询可能是问题所在，但同样有可能出现其他问题。作为开发人员，我们倾向于从代码行的角度来考虑问题。如果一条线慢了，就修好它。但是 RDBMS 是一个非常复杂的系统，您的查询速度缓慢可能是一个更大问题的症状。

太多的 SQL 调优技巧都是货物的偶像。大多数情况下，问题与您使用的语法无关，或者与您使用的语法关系不大，因此通常最好使用最简洁的语法。然后，您可以开始寻找调优数据库(而不是查询)的方法。只有在失败时才调整语法。

像任何性能调优一样，总是收集有意义的统计数据。不要使用壁钟时间，除非它的用户体验，你正在调整。相反，应该查看 CPU 时间、获取的行和从磁盘读取的块。人们经常为错误的事情优化。

小开

@ Terrapin 在 isnull 和 co 之间还有一些值得一提的不同之处(除了符合 ANSI 之外，这对我来说是一个很大的不同之处)。

Coalesce vs IsNull

小开 
SET NOCOUNT ON

通常是存储过程中的第一行，除非我实际上需要使用 @@ROWCOUNT。

小开

下流内容 -

set transaction isolation level read uncommitted

防止在事务完整性不是绝对必要的情况下出现死锁(事务完整性通常是正确的)

小开

在不需要的地方移除光标。

小开

CREATE INDEX

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

如果您的环境是 数据集市或数据仓库,索引，那么对于几乎任何可以想象到的查询，都应该有大量的索引。

在 交易环境中，索引的数量应该更少，它们的定义应该更具战略性，以便索引维护不会拖累资源。(索引维护是指必须更改索引的叶以反映基础表中的更改，如 INSERT, UPDATE,和 DELETE操作。)

另外，要注意索引中字段的顺序——字段的选择性越高(基数越高) ，它出现在索引中的时间就越早。例如，假设你在查询二手车:

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

价格一般具有较高的基数。可能只有几十种颜色可供选择，但是很可能有成千上万种不同的要价。

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

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

这是因为较少的汽车将满足价格点比颜色的选择，给查询引擎更少的数据进行分析。

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

小开

第一步: 查看查询执行计划！
TableScan-> bad
嵌套循环-> meh 警告
在 NestedLoop-> DOOM! < br/>

后面进行 TableScan

设置统计输入
设置统计时间

小开

下面是我经常给那些向我询问优化问题的人的便捷列表。
我们主要使用 Sybase，但是大多数建议都适用于所有情况。

例如，SQLServer 提供了大量的性能监视/调优位，但是如果您没有类似的东西(甚至可能有) ，那么我会考虑以下内容..。

我看到的99% 的问题都是由于放入 联接中的表太多引起的。解决这个问题的方法是执行一半的连接(使用一些表) ，并将结果缓存到一个临时表中。然后将查询的其余部分连接到该临时表上。

查询优化检查表

  • 对基础表运行 UPDATESTATISTICS
    • 许多系统将此作为计划的每周作业运行
  • 从基础表中删除记录(可能存档已删除的记录)
    • 可以考虑每天或每周自动做一次。
  • 重建索引
  • 重建表(bcp 数据输出/输入)
  • 转储/重新加载数据库(极端的，但可能修复损坏)
  • 构建新的、更合适的索引
  • 运行 DBCC 查看数据库中是否存在可能的损坏
  • 锁定/死锁
    • 确保没有其他进程在数据库中运行
      • 特别是 DBCC
    • 您使用的是行级锁定还是页级锁定？
    • 在启动查询之前专门锁定表
    • 检查所有进程以相同的顺序访问表
  • 是否正确使用索引？
    • 联接只有在两个表达式完全相同的数据类型时才使用索引
    • 只有在查询中匹配了索引上的第一个字段时，才会使用索引
    • 是否适当地使用聚类索引？
      • 测距数据
      • Value1和 value2之间的 WHERE 字段
  • 小连接是好连接
    • 默认情况下，优化器一次只考虑表4。
    • 这意味着，如果连接的表超过4个，那么它很有可能选择一个非最优查询计划
  • 解散联盟
    • 你能把接头分开吗？
    • 在临时表中预选外键
    • 执行一半的连接并将结果放入临时表中
  • 您使用的是正确的临时表吗？
    • #temp表可能比大容量(数千行)的 @table变量执行得更好。
  • 维护汇总表
    • 使用基础表上的触发器进行生成
    • 建立每日/每小时/等。
    • 构建特别版本
    • 增量构建或拆卸/重建
  • 查看 SETSHOWPLANON 的查询计划是什么
  • 看看设置为 IO 时实际发生了什么
  • 使用杂注强制索引: (index: myindex)
  • 使用 SETFORCEPLANON 强制执行表顺序
  • 参数嗅探:
    • 将存储过程分成2个
    • 从 proc1调用 proc2
    • 允许优化器选择 proc2中的索引，如果@参数已被 proc1更改
  • 你能改进你的硬件吗？
  • 你什么时候跑步? 有没有比较安静的时间？
  • 复制服务器(或其他不停止进程)是否正在运行? 是否可以挂起它? 是否每小时运行一次？
小开

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

SELECT * FROM Orders (nolock) where UserName = 'momma'
小开

在我的位置，使用 WITH(NoLock)运行查询几乎是标准操作。如果发现任何人在数千兆字节的表上运行查询时没有它，就会被取出并枪毙。

小开

我最近学到的一个技巧是，SQLServer 可以在 update 语句中更新本地变量和字段。

UPDATE table
SET @variable = column = @variable + otherColumn

或者可读性更强的版本:

UPDATE table
SET
@variable = @variable + otherColumn,
column = @variable

在实现递归计算时，我使用它来替换复杂的游标/连接，并且在性能方面也有很大提高。

下面是在性能方面做出了极大改进的细节和示例代码: 林克

小开

我总是首先访问 SQL Profiler (如果它是一个具有很多嵌套级别的存储过程)或查询执行计划器(如果它是一些没有嵌套的 SQL 语句)。90% 的情况下，您可以使用这两种工具之一立即发现问题。

小开

如果可能，将 NOTIN 查询转换为左外连接。例如，如果希望查找表1中由表2中的外键未使用的所有行，可以这样做:

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

但是你可以通过这个获得更好的性能:

SELECT Table1.*
FROM Table1
LEFT OUTER JOIN Table2 ON Table1.ID = Table2.Table1ID
WHERE Table2.ID is null
小开

在 Sprocs 删除函数调用，因为很多行将调用函数。

我的同事使用函数调用(例如从 userid 获取 lastlogindate)来返回非常宽的记录集。

通过优化，我用函数代码替换了 sproc 中的函数调用: 我将许多 sproc 的运行时间从 > 20秒减少到 < 1。


提交答案