Sql Server系列:索引设计原则及优化

时间:2022-05-31 00:36:16

1. 索引设计原则

  索引设计不合理或缺少索引都会对数据库的性能造成障碍,高效的索引对于获得良好的性能非常重要。

  设计索引时的一些原则:

  ◊ 索引并不是越多越好,一个表中如果有大量的索引,不仅占用大量的磁盘空间,而且会影响INSERT、DELETE、UPDATE等语句的性能。当表中数据更改的同时,索引也会进行调整和更新。

  ◊ 避免对经常更新的表进行过多的索引,并且索引中的列尽可能少。而对经常用于查询的字段应该创建索引,但要避免添加不必要的字段。

  ◊ 数据量小的表最好不要使用索引,由于数据较少,查询花费的时间可能比遍历索引的时间还要短,索引可能不会产生优化效果。

  ◊ 在条件表达式中经常用到的、不同值较多的列上建立索引,在不同值较少的列上不要建立索引。比如字段【性别】上只有【男】【女】两个不同值,因此无须建立索引。如果建立索引,不但不会提高查询效率,反而会严重降低更新速度。

  ◊ 当唯一性是某种数据本身的特征时,指定唯一索引。使用唯一索引能够确保定义的列的数据完整性,提供查询速度。

  ◊ 在频繁进行排序和分组(GROUP BY或ORDER BY)的列上建立索引,如果排序的列有多个,可以在这些列上建立组合索引。

2. 建立索引常用的规则

  ◊ 表的主键、外键必须有索引;

  ◊ 数据量超过300的表应该有索引;

  ◊ 经常与其他表进行连接的表,在连接字段上应该建立索引;

  ◊ 经常出现在WHERE字句中的字段,特别是大表的字段,应该建立索引;

  ◊ 索引应该建在选择性高的字段上;

  ◊ 索引应该建在小字段上,对于大的文本字段甚至超长字段,不要建索引;

  ◊ 频繁进行数据操作的表,不要建立太多的索引;

  ◊ 删除无用的索引,避免对执行计划造成负面影响。

3. 查询优化原则

  ◊ 避免对列的操作。

  任何对列的操作都可能导致全表扫描,这里所谓的操作包括数据库函数、计算表达式等,查询时要尽可能将操作移至等式的右边,甚至去掉函数。

  示例:

SELECT * FROM [dbo].[Product] WHERE [UnitPrice] / 10 > 3

  ◊ 避免不必要的类型转换

  ◊ 增加查询的范围限制,避免全范围的查询

  ◊ 尽量去掉 IN、OR

  ◊ 尽量去掉 <>

  ◊ 去掉WHERE字句中的IS NULL和IS NOT NULL。WHERE字句中的IS NULL和IS NOT NULL将不会使用索引而是进行全表搜索。

  ◊ LIKE字句尽量前段匹配

  ◊ 创建基于函数的索引。前面谈到任何对列的操作都可能导致全表扫描,但是这种查询在系统中经常需要使用,这时可以创建一个基于函数的索引。

  示例:

CREATE INDEX IX_ProductName ON [dbo].[Product] (CONVERT(VARCHAR(8), [CreateDate], 112))