SQLServer性能优化(一)

(一)、查询步骤

  1. 逻辑查询树
  2. 绑定(把逻辑树上的节点与数据库的实际对象进行绑定):索引,约束,看看都存不存在
  3. 绑定器进行优化阶段的输入:绑定树,尽可能寻找最优方案。优化方案太多了,inner join只有一种方案,三表关联有六种方案。有限的时间有限的资源它不一定会选择最优的。
  4. 执行计划会传入到执行引擎。

image

图标是运算符,将数据传递给下一个运算符直至返回结果。
口诀:从右向左、从下到上。
箭头方向指明其执行顺序,箭头的粗细表面数据的多少。

image

执行计划是基于成本选择的,sqlserver选择它认为成本较低的。成本的评估和优化的过程需要消耗很多资源。我们需要编写简单的健壮的sql去引导优化器生成更好的执行计划。

image
基于开销的优化(CBO)
阶段0: 分析出一系列执行计划(3张表)
阶段1:快速计划
阶段2:完全优化

SQLServer中有GETROW方法,层层执行之后层层返回。

  • 阻塞运算符:比如排序,等前一个数据都返回了再排序。(需要等待并获取数据,内存增加,开销大)
  • 非阻塞运算符:前面返回了马上处理数据,如top。(需要存储的数据量很少,内存较少)

(二)、优化方法论

扫描运算符:
表扫描:发生在堆表
聚集索引扫描:表中所有数据行都存在于聚集索引的叶子节点中,相当于全表扫描。
非聚集索引扫描:查询数据已经发生在已定义好的索引之中,此时非全表扫描,只需要取出索引包含的数据。此时称为覆盖索引。

*扫描操作被认为是最大性能杀手:需要等待磁盘IO、消耗内存存储数据。数据库缓存池空间不足,需要写数据到内存中,此时会运用淘汰算法,清理缓存中数据,之后再继续填充数据。当频繁出现大量数据淘汰、缓存中数据频繁交替,会造成数据库IO方面性能瓶颈。

查找运算符:一般发生在索引上,通过索引定位具体数据。
聚集索引查找:当where后的条件是主键。
非聚集索引查找

  1. 条件一样表一样。如果查询结果字段与条件一致-->100%。
  2. 如果需要查询的列不是索引字段,而查询条件是索引字段-->此时存在嵌套查询(Inner Join),其中是Index Seek与Key Lookup连表。先通过非聚集索引查找出索引字段,还需要通过标签在表中找出字段。详相当于索引和表进行连接,先通过索引字段找主键,之后通过主键查表。

*Lookup根据表类型不同,可以分为键查找(Key Lookup)和ID查找(RID Lookup)。如果有扫描出现或者存在标签查找,都会被认为有问题,但不一定要被修复。产生的多数原因是:索引缺失、索引没有被正确覆盖到语句中又或者是索引没有生效(可能是查询语句编码不规范导致)。

(三)、基本优化原则

  1. 为何要避免使用(select *):可以避免很多不必要的IO,减少字段输出,有效利用覆盖索引。
  2. 限制结果集大小:比如使用TOP字句、分页处理,避免大批量数据操作,有效使用索引,避免扫描操作带来大量IO及内存占用。不限制直接找到最后,尤其连表几十条数据会找到几百条无用数据。
  3. 对数据进行过滤优先考虑索引字段(筛选率高、重复率低于5%)。
  4. 不要在过滤字段使用任何计算(函数、逻辑计算),不要出现在等号左边,导致查询优化器无法使用索引。
  5. 模糊查询使用Like 'xx%'存在索引,而Like '%xx%'不会使用到。

(四)、Order by、Group by、Update

性能取决于参与排序操作的数据量大小。排序操作数据大于内存--->中间数据存放TempDB(磁盘)。
工作区内存(查询请求的内存空间大小),如果用完了也会丢给TempDB。
当并发量达到一定量级,TempDB比较繁忙时候,IO就会很繁忙。
eg:2亿 =》5G磁盘空间 =》物理磁盘IO写入文件,且TempDB为公共资源,会影响整个数据库实例。

观察执行计划,Group by与distinct基本差不多。

Update ==》先查询出符合条件语句再更新。SQLServer中update会优先对数据添加更新锁,当确定更新数据时再将更新锁转化为排他锁。而Select使用共享锁。所以update性能问题时容易造成阻塞。小数据量建议使用主键或唯一字段。

Delete ==》会使用排他锁,会影响表中索引,应该谨慎权衡数据查询与更新频率。不要因为过多索引而影响数据删除及更新的性能,为了保证完整性,删除时应该确保被修改的数据应该进行回滚操作的。SQLServer会把删除的整条数据记录在日志中。如果进行大批量数据删除,会记录大量到事务日志。如果日志文件存在IO瓶颈,那么可能导致阻塞,所以磁盘空间也会迅速增长。

(五)、Where

优化原则

  1. 是否有合适的索引
  2. 字段上是否有函数计算
  3. 返回结果集是否过大
  4. 是否仅查询出所需要的字段

语句优化:合理索引选择和高效使用
高效查询语句:查找、查询某个或若干个覆盖索引

(五)、子查询

  1. 子查询尽量集中在where子句中
  2. 子查询不超过3个,整个查询涉及到表不超过5个(一个子查询等于一个表)
  3. 子查询将会优先执行,作为下一个语句输入,避免在其中对大数据集进行汇总排序
  4. 使用确定性判断符:=、in、exists,避免使用any、some、all
posted @ 2024-10-29 08:16  羊徒  阅读(69)  评论(0编辑  收藏  举报