MS SqlServer 性能优化

最新推荐文章于 2023-01-12 10:56:42 发布

kufeiyun

最新推荐文章于 2023-01-12 10:56:42 发布

阅读量889

点赞数

分类专栏： SqlServer

SqlServer 专栏收录该内容

25 篇文章 0 订阅

订阅专栏

硬件：
- CPU 由32位更换为64位，使sqlserver有更大的内存管理能力
- 使用RAID系统
- 使用更快的磁盘驱动器
软件

- 创建多个文件和文件组：在一个多处理器系统中，Sqlserver能够在数据库文件中执行多个并行扫描，从而利用多个文件和文件组。因为一个文件中的表被顺序访问，所以创建一个线程来读取相关的数据文件，如果一个文件有4个数据文件组成，那么将创建4个线程并行读取数据，如果数据文件放置在四个不同的物理磁盘，那么所有的磁盘主轴都可以同时工作，因此，在多磁盘子系统中，文件组中有多个文件通常是有利的。
- 将表和索引放在不同的磁盘上
- 将日志文件保存到独立的物理磁盘
- 表分区（水平分区:将一个表数据根据某种条件【时期/使用】进行拆分为两个或者多个表，每个表有相同数目的列和较少的行；垂直分区：将一个表分段为多个表，每个表包含较少的列）
- 手工收缩日志文件
- 利用TimeStamp字段确保在多用户更新间进行高效并发检查
- 优化索引设计
  - 考虑where条件
  - 如果一个表的数据总量非常小以至于可以放到一个单独的页面(8KB)中时，表扫描可能比索引查找工作的更好。
  - 避免在where子句列中使用算术运算
  - 避免在where子句列上使用函数
  - 避免不可参数化的搜索条件（<> ,!= ,!>, !<, Not Exists,Not In ,in,Not Like in,Or, 以及一些like条件，如like '%<kuterak>）
  - 使用可参数化搜索条件(=,>,>=,<,<=,between 以及Like'<literal>%)
  - 使用窄索引，可以在表中的一个列组合上创建索引。为了更好的性能，尽量在索引中使用较少的列。还应该避免在索引中使用宽数据类型的列，字符串数据类型（char，varchar，nchar,nvarchar）的列有时候可能和二进制类型一样大。除非绝对必须，在索引中要把大尺寸的宽数据类型列的使用降到最少。窄索引可以再8kb的索引页面中容纳比宽索引更多的行
  - 在一个具有很小范围的可能值的列（如性别）上创建索引对性能没有好处，因为查询优化器将不能使用索引有效的减少返回的行，使where子句中的列具有大量的唯一行（或者高选择性）以限制访问的行数始终是首选方案，应该在这些列上使用索引来帮助优化器访问小的结果集，而且，在创建多个列上的一个索引时（也称为复合索引），列的顺序是有关系的，在某些情况下，先使用最有选择性的列将使索引行的列更有效率。所以，这样的列，作为地选择行的列，可以作为覆盖索引中的列
  - 索引的数据类型是重要的，在一个整数键值上的索引查询非常快，也可以使用整数数据类型的其他变种（Bigint,smallint ,tinyint）
  - 考虑列顺序，如where c1=12 and c2=11；where c1=12 两个查询语句条件，那么在(C1,c2)上的索引，就比（C2,c1）上的索引更有利，所以，如果数据库设计时已经以(C2,C1)为顺序建立了索引，那么在使用时，查询条件最好改为C2=11在前，这个是综合调整，看哪个是最有选择性的（更多使用），哪个就在前。
  - 考虑索引类型
    - 聚簇索引：聚簇索引的叶子页面和表的数据页面相同，因此，表行物理上按照聚簇索引列排序，因为表数据只能有一种物理顺序，所以一个表只能有一个聚簇索引。（当创建一个表主键时，如不存在聚簇索引。Sqlserver会自动将其创建为聚簇索引，这不是必须的，是默认行为，可改）
    - 堆表，就是数据列没任何特别的序列，访问开销一般比较大
    - 非聚簇索引：索引表中索引行包含指向表的对应数据行的指针（行定位器），如果一个表除了这个非聚簇索引，没有其他索引，那么指针指向的就是数据行的RID（RowID），如果还有聚簇索引，那么这个指针值指向的就是聚簇索引的键值
    - 聚簇索引建议：
    1. 1. 首先创建聚簇索引，因为非聚簇索引对其有依赖性
      2. 保持窄索引
      3. 何时使用一个聚簇索引
        检索一定范围的数据
        读取预先排列的数据：聚簇索在数据读取需要排序时特别有效，因为行将被按顺序物理排列，这消除了数据读取之后排序的开销
        因此对于读取大范围的行和/或排序输出的查询，聚簇索引通常是比非聚簇索引更好的选择。
      4. 何时不用聚簇索引
        频繁更新的列，除了本身索引表更新开销，导致此表上非聚簇索引指针的更新
        宽的关键字
        太多并行的顺序插入，如果行按照聚簇索引相同顺序插入，那么所有插入操作都在表的最后一个页面上进行，可能在磁盘上的对应扇区造成一个巨大热点，为避免这点，可通过创建在另外一列上的索引来将插入随机分布到整个表
    - 非聚簇索使用建议：
      1. 频繁更新的列
      2. 宽关键字。这两种情况情况下，可以使用非聚簇索引，因为和聚簇索引不同，非聚簇索引不影响表中的其他索引，频繁更新列上的非聚簇所用开销不像聚簇索引那么大，不影响表上任何其他非聚簇索引，但也要小心使用，因为可能增加操作查询的开销。
      3. 何时不用非聚簇索引：
        不适合检索大量行的查询，因为除了非聚簇索引页面上的逻辑读，还要有数据行上的逻辑读。如果需要从表上读取大的数据集，那么在过滤条件列上有个非聚簇索引将没有帮助，除非使用特殊类型的非聚簇索引---覆盖索引
    - 覆盖索引：通俗讲，就是表列不管在哪里被引用，都被包含在非聚簇索引中，这就是覆盖索引，覆盖索引的好处是查询所需的数据列可以从非聚簇索引页面中得到，不必通过指针跳到表的数据页面。
      - 覆盖索引是伪聚簇索引，因为覆盖索引物理上顺序的组织所有索引列，索引列包含了所需数据，因此，从磁盘IO角度看，没有使用包含列的覆盖索引技术变成一种聚簇索引
      - 建议：为了利用覆盖所有，要注意select语句中的列清单，应尽可能使用较少的列来保持小的覆盖索引尺寸，覆盖索引也能帮助解决阻塞和死锁
    - 索引交叉：交叉索引就是拆分了覆盖索引。如果由于某种原因不能修改现有覆盖索引或者现有非聚簇索引键已经相当宽，那就在需要的列上建立一个非聚簇索引，这是相当于一表有多个索引，sqlserver可以利用多个索引，根据每个索引选择小的数据子集，然后执行两个子集的交叉。如：select A.* from a where a.orderid=12 and a.orderdate between '7/1/2004' and '7/3/2004'，在表a上orderid有一个非聚簇索引，但orderdate上没，最终执行结果是，优化器没选择使用orderid上的聚簇索引，因为也需要orderdate列的值，所以优化器可能选择其他列上的聚簇索引以读取所有列上的值。要增加这个性能，可将orderdate包含在orderid的非聚簇索引列中。假如不能更能orderid非聚簇索引，可以通过你以下方法解决，在orderdate上建立新的非聚簇索引