《SQL Server性能调优实战》知识点汇总

时间:2022-12-16 22:58:53

2.4数据库结构的设计

  1. 好的性能出自好的设计
    • 尽可能添加数据完整约束(非空约束、默认值约束、CHECK约束、唯一约束、外键约束)等,这些约束的添加将有助于数据库关系引擎分析执行计划。
    • 尽可能小的字段类型,特别是大表,尽量小的空间将可以带来更佳的性能。
    • 表结构的设计应考虑业务需求带来的操作(查询、更新、删除)及频率,尽可能的使业务逻辑实现简洁,使用简单的SQL语句,可避免过多的表关联。
  1. 约束对性能的影响
    • 默认值约束:它只会影响新增的行,对数据库引擎来说,它的影响可以忽略不计。
    • check约束:check约束的引入,相当于告诉查询优化器:“这个字段的值只有某些枚举值”,而不必对数据进行大量检索操作。
    • 唯一约束:创建一个唯一约束会默认创建一个唯一索引,唯一索引是筛选效率非高的一种索引结构,对于查询优化有极大的帮助(通常在一个表中,无论是无意义的自增值,还是有意义的业务字段,至少应该有一个唯一约束存在)。
    • 外键约束:
      • 子表进行增、删、改操作时,会校验主表数据完整性,这个过程会有主表加锁进行查询,这时主表的的关联字段一定是主键或者唯一键,它的校验速度非常快,不会有太大的问题。
      • 当主表进行删除操作的时候,外键约束会校验子表的关联字段是否存在,此时,如果子表的关联字段没有索引,将会扫描整个子表,会使开销剧增(如果子表非常大),所以在添加外键关联的时候,需要同时查检子表中的关联字段是否有索引,如果没有请手工创建
      • 在某些情况下,比如子表是日志表或者历史表,对于数据完整性没有要求,此时不要加外键关系约束,因为这只是给系统带来额外开销
      • 也有些观点并不推荐使用外键约束,而是在应用层解决,具体请查看《数据库中为什么不推荐使用外键约束
  2. 适当的冗余
    • 字段冗余是避免过多表关联的常用手段:假如一个查询每天的访问率是10万次,而字段的更新只是每天甚至几天才会修改一次,这时完全可以考虑使用冗余提高查询的效率。

3.1编写健壮的T-SQL语句

  1. SELECT语句的性能:只查询出需要的字段、限定好查询的结果集、合理、有效的使用索引。以下为容易忽略的要点
    • 不要在过滤字段上使用任何的计算:包括函数、逻辑计算、普通计算(容易造成查询优化器无法使用相应字段的索引)
    • 尽量使用有索引的字段进行排序:特别数据量比较大时,很大程度上降低开销
    • 填写查询表时,尽量使用JOIN关键字连接表,不容易造成笛卡尔积。
  2. 排序子句(ORDER BY)
    • ORDER BY子句的性能取决于参与排序操作的数据量的大小。
    • 排序操作会在数据筛选完以后,对筛选选出的结果集进行排序,因此对需要排序的查询,应尽量将排序操作的数据量控制在较小结果集查询中
    • 大数据量排序:工作内存不足时会将排序中间数据存在Tempdb数据库中,速度将低,服务器压力增大。
    • 尽量避免对大批量数据进行排序操作,这样可以避免不必要的CPU资源消耗,不得以时可以考虑将排序字段,也就是ORDER BY子句后的字段添加在索引中。
  3. 使用T-SQL汇总
    • 从本质上来说,DISTINCT就是一个全字段的GROUP BY。
    • GROUP BY分组统计需要先对数据进行分类,分类也需要使用排序算法因此对排序的优化,对GROUP BY同样有效
    • GROUP BY和DISTINCT需要进行哈希或者排序计算,与排序相同,哈希计算也需要使用大量的内存空间,当工作区内存不足时,会将一部分中间数据存放到Tempdb因此同样需要注意数据量的控制,如果有必要可以在相应字段上建立索引,以避免哈希计算
  4. UPDATE语句
    • UPDATE语句存在性能问题时,将对SELECT操作造成阻塞,建议使用主键或者唯一字段来过滤需要更新的数据。
    • 默认事务级别中,UPDATE语句进行查询时会添加更新锁(U锁),若找到了需要更新数据,就会将更新锁转为排他锁(X锁),由于这2个锁的兼容性弱,较长执行时间的更新语句事务中,容易造成阻塞,为避免这种情况,应尽量保证更新语句的效率,适当建立索引
  5. DELETE语句
    1. 大批量删除数据时,会有大量日志生成,TRUNCATE TABLE操作可删除整张表的数据,并且不会对所删除数据生成记录日志,删除一个大表的数据时,此命令可以提高效率,减少日志的记录,是个不错的选择。