数据库查询、索引性能优化的一些整理

数据库优化的目标

通过一些列的手段，使系统能够协调、平衡的运作，合理的相应外部请求，实现利用资源最大化。说白了就是优化后可能不是最快的，但一定是最稳定的，要保证系统能够稳定的运行而不是隔三岔五CPU就爆了。

常见的性能影响因素

数据库结构设计要保持以下原则：

1.了解业务，性能只是附属属性；

2.优先考虑第三范式设计；

3.表的关联尽可能少，及在可能的情况虾适当的使用一些冗余字段；

4.坚持最小原则：说白了就是表字段的大小要设计的刚刚好；

5.在适当的地方使用约束。

T-SQL语句编写的原则：

1.完全了解业务需求，知道表的用途和用法；

2.要学会判断过滤字段能否使用索引；

3.不要对有索引的字段做任何计算，否则会使索引失效；

4.小表操作有限，查询时以小表驱动大表；

5.只查询有效字段，避免查询全部字段；

6.尽量使用简单的SQL语句来实现业务功能（表关联不超过4个，过滤条件2-3个并且有一个过滤条件可以明确使用索引查找）。

7.学会使用执行计划进行性能对比查询。

SQL语句会造成索引失效的几种情况：

1.查询条件中含有or关键字；

2.where条件中使用标量函数操作；

3.like条件左侧有%；

4.where条件中含有运算；

5.where条件中有类型转换

6.where条件中IS NULL /IS NOT NULL。

建立索引的原则：

1.定义主键的数据列一定要建立索引。

2.定义有外键的数据列一定要建立索引。

3.对于经常查询的数据列最好建立索引。

4.对于需要在指定范围内的快速或频繁查询的数据列;

5.经常用在WHERE子句中的数据列。

6.经常出现在关键字order by、group by、distinct后面的字段，建立索引。如果建立的是复合索引，索引的字段顺序要和这些关键字后面的字段顺序一致，否则索引不会被使用。

索引优化：

1.索引不是越多越好，索引过多，会干扰查询优化器分析，存储空间大，维护空间大。要适当的做优化合并，单表索引总数控制在5个以内。

2.以复合索引代替单列索引。

3.合理设置索引的首列：

1）首列的选择度尽可能高，就是首列的唯一值尽可能多；

2）首列的类型尽可能小，少用字符串。

选择首列公式：

select (select count (discount 列)*1.0 from 表)/(select count (1) from 表 ) --越小代表选择度越高

4.合理使用include包含配合复合索引使用；

5.数据唯一性差（一个字段的取值只有几种时）的字段不要使用索引。比如性别，只有两种可能数据。意味着索引的二叉树级别少，多是平级。这样的二叉树查找无异于全表扫描；

6.对于那些查询中很少涉及的列不建议使用索引；

7.对于定义为text、image、guid和bit的数据类型的列不建议建立索引；

8.对于频繁数据操作的列不要使用索引，因为每次操作都会导致索引重新排序，导致维护性能降低；

9.首列相同的索引，可以根据实际情况适当合并；

使用DMV查询索引缺失情况，性能占比：

以下是具体的sql和说明：

select 
d.statement,
d.equality_columns,
d.included_columns,d.inequality_columns,
gs.user_seeks,
gs.user_scans,
gs.avg_total_user_cost,
gs.avg_user_impact
from sys.dm_db_missing_index_details as d
--这个DMV记录了当前数据库下所有的missing index的信息,他针对的是SQLSERVER从启动以来所有运行的语句，而不是针对某一个查询。
--以下是这个DMV的各个字段的解释：
--1、index_handle:标识特定的缺失索引。该标识符在服务器中是唯一的。index_handle 是此表的密钥
--2、database_id :标识带有缺失索引的表所驻留的数据库
--3、object_id :标识索引缺失的表
--4、equality_columns:构成相等谓词的列的逗号分隔列表 即哪个字段缺失了索引会在这里列出来（简单来讲就是where 后面的筛选字段），谓词的形式如下：table.column =constant_value
--5、inequality_columns :构成不等谓词的列的逗号分隔列表，例如以下形式的谓词：table.column > constant_value “=”之外的任何比较运算符都表示不相等。
--6、included_columns:用于查询的涵盖列的逗号分隔列表（简单来讲就是 select 后面的字段）。
--7、statement:索引缺失的表的名称
 
join sys.dm_db_missing_index_groups as g
--返回有关特定缺失索引组中包含的缺失索引（不包括空间索引）的信息
on d.index_handle=g.index_handle
join sys.dm_db_missing_index_group_stats as gs
--返回缺失索引组的摘要信息，不包括空间索引

--这个视图说白了就是预估有这麽一个索引，他的性能能提高多少

--avg_user_impact: 实现此缺失索引组后，用户查询可能获得的平均百分比收益。该值表示如果实现此缺失索引组，则查询成本将按此百分比平均下降。

--就是说，增加了这个缺失索引，性能可以提高的百分比
on g.index_group_handle=gs.group_handle

--sys.dm_db_missing_index_columns(index_handle)

--返回与缺少索引（不包括空间索引）的数据库表列有关的信息，sys.dm_db_missing_index_columns 是一个动态管理函数

--index_handle：唯一地标识缺失索引的整数。

 

查询后的结果：

​

我们可以根据实际情况来对缺失索引的查询进行监控及优化。