Question

如何优化索引：提高SQL语句查询性能

Answer 1

查询优化的统计信息是一些对象，这些对象包含与值在表或索引视图的一列或多列中的分布有关的统计信息。查询优化器使用这些统计信息来估计查询结果中的基数或行数。通过这些基数估计，查询优化器可以创建高质量的查询计划。例如，查询优化器可以使用基数估计选择索引查找运算符而不是耗费更多资源的索引扫描运算符，从而提高查询性能。
每个统计信息对象都在包含一个或多个表列的列表上创建，并且包括显示值在第一列中的分布的直方图。在多列上的统计信息对象也存储与各列中的值的相关性有关的统计信息。这些相关性统计信息（或密度）根据列值的不同行的数目派生。
使用数据库范围的统计信息选项

自动创建统计信息选项 AUTO_CREATE_STATISTICS 和自动更新统计信息选项 AUTO_UPDATE_STATISTICS 默认为 ON，我们建议使用这一默认值来用于大多数用户数据库。您可以使用以下 SELECT 语句来为所有用户数据库查看这些选项的当前值：
[sql] view plain copy
SELECT name AS "Name",
is_auto_create_stats_on AS "Auto Create Stats",
is_auto_update_stats_on AS "Auto Update Stats",
is_read_only AS "Read Only"
FROM sys.databases
WHERE database_ID > 4;
查看统计统计信息
DBCC SHOW_STATISTICS 显示表或索引视图的当前查询优化统计信息。语法结构参考DBCC SHOW_STATISTICS，这里不做过多阐述。
确定何时创建统计信息

查询优化器已通过以下方式创建统计信息：
1、在创建索引时，查询优化器为表或视图上的索引创建统计信息。这些统计信息将创建在索引的键列上。如果索引是一个筛选索引，则查询优化器将在为该筛选索引指定的行的同一子集上创建筛选统计信息。

2、在 AUTO_CREATE_STATISTICS 为 ON 时，查询优化器为查询谓词中的单列创建统计信息。

对于大多数查询，用于创建统计信息的这两个方法就可以确保高质量的查询计划；但在很少的情况下，可以通过使用 CREATE STATISTICS 语句创建附加的统计信息，改进查询计划。这些附加的统计信息可以捕获查询优化器在为索引或单列创建统计信息时并未考虑的统计关联。您的应用程序可能在表数据中具有附加的统计关联，如果在统计信息对象中计入这些关联，可能会令查询优化器改进查询计划。例如，针对数据行子集的筛选统计信息或针对查询谓词列的多列统计信息可改进查询计划。
在使用 CREATE STATISTICS 语句创建统计信息时，我们建议保持 AUTO_CREATE_STATISTICS 选项为 ON，以便查询优化器继续为查询谓词列定期创建单列统计信息。
在以下任何情况适用时，考虑使用 CREATE STATISTICS 语句创建统计信息：
① 数据库引擎优化顾问建议创建统计信息。
② 查询谓词包含尚不位于相同索引中的多个相关列。
③ 查询从数据的子集中选择数据。
④ 查询缺少统计信息。

了解上面的基础知识，那么开始本文的重点，实际操作中怎么Using Statistics to Improve Query Performance通常情况下，设置了 AUTO_CREATE_STATISTICS 和AUTO_UPDATE_STATISTICS
的话，数据库就会自动去创建管理了，但是有时候并不尽人意。尤其是它创建了统计信息，但是更新的不及时，导致查询执行时间很长。这类情况最明显的一个特征就是你修改where条件的参数值，执行时间会有非常大的差异。例如下面这个例子

[sql] view plain copy
--1
SELECT COUNT(*)
FROM a WITH (NOLOCK) ,
b WITH (NOLOCK)
WHERE a.Daytime = b.AccountDate
AND a.Siteid = b.Siteid
AND a.Prodid = b.Prodid
AND b.Daytime = '2013-2-28'
--2
SELECT COUNT(*)
FROM a WITH (NOLOCK) ,
b WITH (NOLOCK)
WHERE a.Daytime = b.AccountDate
AND a.Siteid = b.Siteid
AND a.Prodid = b.Prodid
AND b.Daytime = '2013-3-31'
两个表a ，b的数据量都在五六百万左右，结构上b表是联合主键(Daytime,Prodid,Siteid),a
表中这三个列是非聚集联合索引。这是一个比较简单的内连接语句，当更改日期参数，前者只需要1s，后者需要30+；
为什么会出现这样的差异呢，这里说下我的分析思路，先看执行计划差异很大（这里我就不贴图了）。主要差异是第一个走了哈希联接，第二个是LOOP JOIN（
LOOP JOIN和哈希联接的具体信息可以参考联机帮助上的说明），这两个表的数据量都比较大，所以LOOP JOIN是不合理的方案。按照正常来说执行计划应该是一样的，我首先想到的是索引碎片，通过脚本查看两个被使用到的索引碎片率很低，排除掉这个原因，然后想到了索引统计信息，发现a表主键上一次更新统计信息的日期是在2013-03-04 ，然后调整日期参数，这个之前的查询都很快，后面的都特慢。然后使用 UPDATE STATISTICS更新了统计信息，两个语句的执行计划都一致了，速度都正常了。
总结：对于改天查询中的一个参数或多个参数值导致查询执行时间差异很大的语句，多半是Statistics没有及时跟上，我们需要手动更新一下。在之后我们观察这个索引的自动更新统计是否为ON，如果配置正常但是Statistics还不能及时跟上的建议建立一个job定期检查统计信息更新情况并处理。