第十五周学习笔记
第十五周学习笔记
本周主要讲解了索引的统计信息。
一、统计信息的定义
统计信息描述了表格或者索引视图中的某些列的值的分布情况，属于数据库对象。根据统计信息，查询优化器就能评估查询过程中需要读取的行数及结果集情况，同时也能创建高质量的查询计划。有了统计信息，查询优化器可以使用基数估计来选择合理的索引，而不需要耗费更多的IO资源扫描来评估哪个索引合理，能有效提供查询性能。所以，简单的说，统计信息是用来反应数据在实体表格或者视图中的分布情况。
二、统计信息的内容
可以通过sys.stats查看到统计信息的名字及基于哪一个表格，然后根据 dbcc show_statistics(<table_name>,<index_or_statistics_name>) 来查看统计信息内容。
可以看到，统计信息分为三部分内容，头信息，数据字段选择性及直方图。
直方图，用于计算数据中每个非重复值出现的频率。使用统计信息对象的第一个键列中的列值来计算直方图，可以通过抽样行或者全表扫描的形式。如果是抽样创建，那么，这里边的 存储总行数何非重复值总数则为估计值。创建直方图的时候，查询优化器对列值进行排序，同时计算每个非重复列值匹配的个数，然后将这列非重复列值 分为 1-200个连续的直方图梯级中，每个梯级包含一个列值范围，该范围介于两个边界值之间的所有可能列值，不包含边界值本身，最小的排序列值是第一个直方图梯级的上限值。
三、影响统计信息的选项
每个表格或者索引视图 何时创建统计信息、基于哪些列创建统计信息及何时更新统计信息，需要根据 AUTO_CREATE_STATISTICS 、 AUTO_UPDATE_STATISTICS、 AUTO_UPDATE_STATISTICS_ASYNC 的设定值 来确定，这三个属于 数据库级别的选项，可以通过系统视图查看，也可以通过 图形界面选择数据库的“属性”，查看“选项”。
--查看数据库统计信息选项设定值
SELECT
name dbname,
is_auto_create_stats_on,
is_auto_update_stats_on,
is_auto_update_stats_async_on
FROM sys.databases
（1）AUTO_CREATE_STATISTICS
默认为ON。自动创建统计信息选项，仅应用于 表格单列统计信息。查询优化器根据查询谓词的使用情况，在表格上单独给某一列创建统计信息（这些单列暂时未创建直方图），协助查询计划的基数估计。该选项不决定是否为索引创建统计信息，也不生产筛选统计信息。
通过该选项创建的统计信息，名称以 _WA 开头。可以通过sys.stats视图查看。
SELECT OBJECT_NAME(s.object_id) AS object_name,
COL_NAME(sc.object_id, sc.column_id) AS column_name,
s.name AS statistics_name
FROM sys.stats AS s JOIN sys.stats_columns AS sc
ON s.stats_id = sc.stats_id AND s.object_id = sc.object_id
WHERE s.name like '_WA%'
ORDER BY s.name;
（2）AUTO_UPDATE_STATISTICS
默认为ON。自动更新统计信息选项，查询优化器自动确定统计信息何时过期何时需要更新。通常情况，从上次自动更新至今，如果期间积累了较大数量的数据变更，包括插入、删除及修改，或表结构变更等，均会造成统计信息过期。该选项适用于为索引创建统计信息对象、查询谓词中的单列以及使用 create statistics 语句创建的统计信息。
（3）AUTO_UPDATE_STATISTICS_ASYNC
默认为OFF。异步自动更新统计信息选项，确定查询优化器是使用 同步统计信息更新还是异步统计信息更新。OFF则代表使用同步自动更新统计信息，这样，查询计划始终使用最新的统计信息进行编译执行，如果遇到统计信息过期，则会在查询编译前等待更新统计信息，若是异步自动更新统计信息，则在遇到统计信息过期时，直接使用现有统计信息编译然后执行，即使可能由于统计信息过期造成编译不佳，执行计划非最优，但仍按照编译结果运行。
该选项使用于适用于 为索引创建的统计信息对象、查询谓词中的单列以及使用 CREATE STATISTICS 语句创建的统计信息。通常情况下，使用 同步自动更新统计信息，则设置该选项为OFF，而在以下两种情况下，则可开启为ON（来自官网）：
1.应用程序贫富执行相同查询或者类似查询，与同步统计信息更新相比，使用异步统计信息更新查询的响应时间可以不受影响，避免出现等待最新统计信息的情况；
2.应用程序遇到了客户端请求超时，这些超时是由于一个或多个查询正在等待更新后的统计信息所导致的。 在某些情况下，等待同步统计信息可能会导致应用程序因过长超时而失败。
四、创建与更新
（1）创建：
查询优化器自动创建
1.创建索引时，查询优化器自动为表格或者视图上的索引创建统计信息。
2.在 AUTO_CREATE_STATISTICS 为 ON 时，查询优化器为查询谓词中的单列创建统计信息。
手动执行创建
CREATE STATISTICS 创建
常规情况下，查询优化器创建的统计信息就可以满足我们的大多数需求，但是如果出现以下情况，可以考虑手动创建：
1.数据库引擎优化顾问建议创建。
2.查询谓词包含尚不位于相同索引中的多个相关列。
3.查询从数据的子集中选择数据。
4.查询缺少统计信息。
（2）更新
统计信息定义在普通的表格上，当发生以下任一变化时，统计信息就会被认为是过时的，下次使用到的时候，会自动触发更新动作：
- 表格从没有数据变成大于等于1条数据；
- 对于数据量小于500行的表格，当统计信息的第一个字段数据累计变化量大于500以后；
- 对于数据量大于500行的表格，当统计信息的第一个字段数据累计变化量大于500+（20%*表格数据总量）以后。
这三种情况下，第三种情况最容易出现更新不及时的情况，比如一张100万的表格，它最近一个月的数据增长是15万左右，由于小于20%，统计信息没有更新，这就导致了有关最近一个月数据sql执行有不是很正确的信息提供，那么就需要定期去检查并及时更新统计信息。
临时表上可以有统计信息，其维护策略基本和普通表格一样，但是表变量上不能建立统计信息。
--更新指定统计信息
UPDATE STATISTICS Sales.SalesOrderDetail AK_SalesOrderDetail_rowguid;
GO
--更新表格上的所有统计信息
UPDATE STATISTICS Sales.SalesOrderDetail;
GO
--更新整个数据库上的所有统计信息
EXEC sp_updatestats;
--删除统计信息
DROP STATISTICS Purchasing.Vendor.VendorCredit
, Sales.SalesOrderHeader.CustomerTotal;
GO
--查看统计信息上一次更新时间
SELECT
OBJECT_NAME(OBJECT_ID)
FROM sys.stats
WHERE STATS_DATE(object_id, stats_id) is not null
