第十二章——SQLServer统计信息(2)——非索引键上统计信息的影响

简介: 原文: 第十二章——SQLServer统计信息(2)——非索引键上统计信息的影响 前言:        索引对性能方面总是扮演着一个重要的角色,实际上,查询优化器首先检查谓词上的统计信息,然后才决定用什么索引。
原文: 第十二章——SQLServer统计信息(2)——非索引键上统计信息的影响

前言:

        索引对性能方面总是扮演着一个重要的角色,实际上,查询优化器首先检查谓词上的统计信息,然后才决定用什么索引。一般情况下,默认会在创建索引时,索引列上均创建统计信息。但是不代表在非索引键上的统计信息对性能没有用。

        如果表上的所有列都有索引,那么将会是数据库负担不起,同时也不是一个好想法,包括谓词中用到的所有列加索引同样也不是好方法。因为索引会带来负载。因为需要空间存放索引,且每个DML语句都会需要更新索引。

        一般来说,建议在where或者ON子句中出现的列上添加索引,但是由于某些情况,很难在所有的谓词上都创建索引,此时创建统计信息会是一个最起码的改进。如果Auto_Create_StatisticsON,那么优化器会帮你做这一步。

 

准备工作:

默认情况下,Auto_Create_Statistics在数据库级别是设为ON的,但是为了下面需要这里先改成OFF

ALTER DATABASE AdventureWorks2012 SET AUTO_CREATE_STATISTICS OFF
GO
ALTER DATABASE AdventureWorks2012 SET AUTO_UPDATE_STATISTICS OFF
GO


 

 

然后创建一个新表用于本文使用:

SELECT  *
INTO    SalesOrdDemo
FROM    Sales.SalesOrderHeader
GO


 

步骤:

1、  对于新表,现在是没有统计信息在上面的,可以使下面语句来验证:

 

SELECT  object_id ,
        OBJECT_NAME(object_id) AS TableName ,
        name AS StatisticsName ,
        auto_created
FROM    sys.stats
WHERE   object_id = OBJECT_ID('SalesOrdDemo')
ORDER BY object_id DESC 
GO


 

 

因为没有统计信息,所以这个查询是没有数据的。

2、  现在在新表上创建一个聚集索引:

 

 CREATE CLUSTERED INDEX idx_SalesOrdDemo_SalesOrderID ON SalesOrdDemo(SalesOrderID)
GO


 

 

3、  再次运行步骤一的脚本,可以看到已经有了数据,现在来执行下面的语句,并开启执行计划:

 

SELECT  s.salesorderid ,
        so.SalesOrderDetailID
FROM    salesordDemo AS s
        INNER JOIN Sales.SalesOrderDetail AS so ON s.salesorderid = so.SalesOrderID
WHERE   s.duedate = '2005-09-19 00:00:00.000'


 

4、  下面截图是步骤3中的执行计划,关注一下SalesOrdDemo表上有聚集索引扫描,这是合理的,因为没有WHERE子句在使用SalesOrderID列。而SalesOrderDetails表有非聚集索引扫描。还可以看到实际行数和估计行数有很大差异。

 

5、  现在是时候在新表的DueDate上创建统计信息,因为在查询中这个列并不包含在索引里面。

CREATE STATISTICS st_SaledOrdDemo_DueDate ON SalesOrdDemo(DueDate)
GO


 

6、  再次执行步骤3的脚本,不需要任何改动:

 

SELECT  s.salesorderid ,
        so.SalesOrderDetailID
FROM    salesordDemo AS s
        INNER JOIN Sales.SalesOrderDetail AS so ON s.salesorderid = so.SalesOrderID
WHERE   s.duedate = '2005-09-19 00:00:00.000'


 

 

7、  对比上面的执行计划,此时在SalesOrderDetails表上已经从非聚集索引扫描变成了聚集索引查找,且开销只有2%,更总要的是实际行数和预估行数相差无几:

 

分析:

如果优化器可以获得谓词上列的统计信息,那么相会知道将要返回的行数,并且帮助优化器选择最佳的执行方式。

相关实践学习
使用SQL语句管理索引
本次实验主要介绍如何在RDS-SQLServer数据库中,使用SQL语句管理索引。
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库,该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品:云数据库RDS SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能,强力支撑各种企业应用,同时也包含了微软的License费用,减少额外支出。 了解产品详情: https://www.aliyun.com/product/rds/sqlserver
目录
相关文章
|
4月前
|
开发框架 .NET 数据库
asp.net企业费用报销管理信息系统VS开发sqlserver数据库web结构c#编程Microsoft Visual Studio
asp.net 企业费用报销管理信息系统是一套完善的web设计管理系统,系统具有完整的源代码和数据库,系统主要采用B/S模式开发。开发环境为vs2010,数据库为sqlserver2008,使 用c#语言开发 应用技术:asp.net c#+sqlserver 开发工具:vs2010 +sqlserver
33 0
|
10月前
|
索引
SqlServer 事物与索引
SqlServer 事物与索引
45 0
|
11月前
|
开发框架 监控 前端开发
云LIS平台源码,基于B/S架构的实验室信息系统,技术架构:Asp.NET CORE 3.1 MVC + SQLserver + Redis
支持Westguard,Gubbuss+T(n)等多种质控规则,自动判断是否失控,可自动计算靶值、SD,多个质控品可列于一个图表上;每个质控品每天可多达7次结果,可使用平均值、最后一次结果,最好一次结果画图等;靶值可自动计算,免疫等支持按季度或者自定义日期画图
云LIS平台源码,基于B/S架构的实验室信息系统,技术架构:Asp.NET CORE 3.1 MVC + SQLserver + Redis
|
SQL 索引 存储
sql server 索引阐述系列五 索引参数与碎片
原文:sql server 索引阐述系列五 索引参数与碎片 -- 创建聚集索引 create table [dbo].[pub_stocktest] add constraint [pk_pub_stocktest] primary key clustered ( [sid] asc...
834 0
|
SQL 存储 索引
sql server 索引阐述系列四 表的B-Tree组织
原文:sql server 索引阐述系列四 表的B-Tree组织 一.概述     说到B-tree组织,就是指索引,它可以提供了对数据的快速访问。索引使数据以一种特定的方式组织起来,使查询操作具有最佳性能。
1142 0
|
SQL 存储 索引
sql server 索引阐述系列三 表的堆组织
原文:sql server 索引阐述系列三 表的堆组织 一.   概述   这一节来详细介绍堆组织,通过讲解堆的结构,堆与非聚集索引的关系,堆的应用场景,堆与聚集索引的存储空间占用,堆的页拆分现象,最后堆的使用建议 ,这几个维度来描述堆组织。
1105 0
|
SQL 存储 Go
sql server 索引阐述系列二 索引存储结构
原文:sql server 索引阐述系列二 索引存储结构 一.概述、   "流光容易把人抛,红了樱桃,绿了芭蕉“ 转眼又年中了,感叹生命的有限,知识的无限。在后续讨论索引之前,先来了解下索引和表数据的内部结构,这一节将介绍页的存储,页分配单元类型,区的存储, 最后简要介绍下系统页存储类型,页中的数据结构。
808 0
|
SQL 索引 数据库
sql server 索引阐述系列八 统计信息
原文:sql server 索引阐述系列八 统计信息 一.概述     sql server在快速查询值时只有索引还不够,还需要知道操作要处理的数据量有多少,从而估算出复杂度,选择一个代价小的执行计划,这样sql server就知道了数据的分布情况。
957 0
|
SQL 存储 索引
sql server 索引阐述系列六 碎片查看与解决方案
原文:sql server 索引阐述系列六 碎片查看与解决方案 一 . dm_db_index_physical_stats 重要字段说明   1.1 内部碎片:是avg_page_space_used_in_percent字段。
942 0
|
SQL 存储 索引
sql server 索引阐述系列七 索引填充因子与碎片
原文:sql server 索引阐述系列七 索引填充因子与碎片 一.概述     索引填充因子作用:提供填充因子选项是为了优化索引数据存储和性能。 当创建或重新生成索引时,填充因子的值可确定每个叶级页上要填充数据的空间百分比,以便在每一页上保留一些剩余存储空间作为以后扩展索引的可用空间,例如:指定填充因子的值为 80 表示每个叶级页上将有 20% 的空间保留为空,以便随着向基础表中添加数据而为扩展索引提供空间。
1049 0