通过手动创建统计信息优化sql查询性能案例

本文涉及的产品
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
云数据库 RDS SQL Server,基础系列 2核4GB
简介: 原文:通过手动创建统计信息优化sql查询性能案例本质原因在于:SQL Server 统计信息只包含复合索引的第一个列的信息,而不包含复合索引数据组合的信息   来源于工作中的一个实际问题, 这里是组合列数据不均匀导致查询无法预估数据行数,从而导致无法选择合理的执行计划导致性能低下的情况 我...
原文: 通过手动创建统计信息优化sql查询性能案例

本质原因在于:SQL Server 统计信息只包含复合索引的第一个列的信息,而不包含复合索引数据组合的信息

 

来源于工作中的一个实际问题,

这里是组合列数据不均匀导致查询无法预估数据行数,从而导致无法选择合理的执行计划导致性能低下的情况

我这里把问题简单化,主要是为了说明问题

如下一张业务表,主要看两个“状态”字段,BusinessStatus1 和 BusinessStatus2

create table BusinessTable
(
    Id int identity(1,1),
    Col2 varchar(50),
    Col3 varchar(50),
    Col4 varchar(50),
    BusinessStatus1 tinyint,
    BusinessStatus2 tinyint,
    CreateDate Datetime
)
GO

--向测试表中写入数据:

begin tran
    declare @i int
    set @i=0
    while @i<500000
    begin
        insert into BusinessTable values (NEWID(),NEWID(),NEWID(),1,10,GETDATE()-RAND()*1000)
        insert into BusinessTable values (NEWID(),NEWID(),NEWID(),1,20,GETDATE()-RAND()*1000)
        insert into BusinessTable values (NEWID(),NEWID(),NEWID(),1,30,GETDATE()-RAND()*1000)
        
        insert into BusinessTable values (NEWID(),NEWID(),NEWID(),2,20,GETDATE()-RAND()*1000)
        insert into BusinessTable values (NEWID(),NEWID(),NEWID(),2,30,GETDATE()-RAND()*1000)
        insert into BusinessTable values (NEWID(),NEWID(),NEWID(),2,40,GETDATE()-RAND()*1000)

        insert into BusinessTable values (NEWID(),NEWID(),NEWID(),3,30,GETDATE()-RAND()*1000)
        insert into BusinessTable values (NEWID(),NEWID(),NEWID(),3,40,GETDATE()-RAND()*1000)
        insert into BusinessTable values (NEWID(),NEWID(),NEWID(),3,50,GETDATE()-RAND()*1000)


        set @i=@i+1
    end
commit

--插入一条特殊数据,也就是实际业务场景中:
insert into BusinessTable values (NEWID(),NEWID(),NEWID(),3,10,GETDATE()-RAND()*1000)

 

 

--测试数据的特点是:

--BusinessStatus1 的分布位:1,2,3,
--BusinessStatus2 的分布位:10,20,30,40,50

--目前数据的对应关系,

--但是注意插入的一条特殊数据:
--BusinessStatus1 和 BusinessStatus2 的组合为:BusinessStatus1=3 and BusinessStatus2=10,在451W条数据中是唯一的一个组合

--创建如下索引:
Create Clustered index idx_createDate on BusinessTable(CreateDate)

Create Index idx_status on BusinessTable(BusinessStatus1,BusinessStatus2)

 

 

进行如下查询,就是查询那条所谓的特殊数据

select * 
from BusinessTable 
where BusinessStatus1=3 and BusinessStatus2=10

发现执行计划如下:走的是全表扫描,IO代价也不小,

这种情况下,明明只有一条数据,却要走全表扫描

(实际业务中类似数据也不仅只有一条这么巧,但是在千万级的表中,符合类似条件的数据很少,

打个比方好理解一点,就像订单表一样,订单是退订状态,且尚未退款,这种数据的分布是少之又少吧

只是举例,不要较真)

上面查询的IO信息

 

再通过强制索引提示的情况下,发现同样的查询,IO有一个非常大的下降

 

分析上述sql为什么不走索引?因为毕竟符合条件的数据只有一条,走全表扫描代价也过于大了,尤其是实际情况中,业务表更大,逻辑也没有这么直白

这个还要从索引统计信息说起,在符合索引中,索引统计信息只是统计前导列的,对于组合列的分布,sqlserver是无法预估到的,这一点可以通过第一个查询的执行计划发现

sqlserver只是能够预估到 BusinessStatus1 =3 的情况下的数据分布,但是无法预估到 BusinessStatus1=3 and BusinessStatus2=10这个组合情况下的数据分布情况

当然通过统计信息也可以看到,统计信息只记录了BusinessStatus1的列的数据分布情况,但是实际执行的过程中,无法预估BusinessStatus1=3 and BusinessStatus2=10的准确分布

找到了问题的原因,就容易解决了,既然sqlserver无法预估到BusinessStatus1=3 and BusinessStatus2=10这个组合条件的数据分布请,

那么就创建一个过滤统计信息,让sqlserver准确地知道这个条件下数据的分布请,就容易做出相对准确的执行计划了

通过如下语句,创建一个该条件的统计信息

create statistics BusinessTableFilterStatistics 
on BusinessTable(BusinessStatus1,BusinessStatus2)
where BusinessStatus1=3 and BusinessStatus2=10


--创建完统计信息之后注意要做个更新
UPDATE STATISTICS BusinessTable BusinessTableFilterStatistics with fullscan

 

 

创建完统计信息之后,发现表上会增加一个刚刚创建的统计信息

 

现在再来看这个查询的执行计划情况,发现其按照预期的走了索引

 

同时观察起IO情况,也有一个大幅度的下降

 

总结:

以上通过手动创建统计信息,来促使sqlserver在生成执行计划的时候,准确地知道数据的分布情况,做出较为优化的执行计划,在某些特殊的情况下,可以作为优化的一个考虑方向

 

后记:

或许有人认为这个问题该归结于parameter sniff的问题,其实这个问题跟parameter sniff还不太一样(当然也有一点像)

通常情况下,所说的parameter sniff问题是单列数据分布不均匀的情况下,因为执行计划重用导致性能地下的一个现象,重点是执行计划的不合理重用

这里的问题在于,由于统计信息的数据计算方式,sqlserver 压根无法预估到符合条件数据的准确分布,从而无法做出合理的执行计划的情况

当然这种情况也比较特殊,在强制索引提示以外,可以通过手动创建统计信息来达到优化的目的

相关实践学习
使用SQL语句管理索引
本次实验主要介绍如何在RDS-SQLServer数据库中,使用SQL语句管理索引。
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库,该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品:云数据库RDS&nbsp;SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能,强力支撑各种企业应用,同时也包含了微软的License费用,减少额外支出。 了解产品详情:&nbsp;https://www.aliyun.com/product/rds/sqlserver
目录
相关文章
|
11天前
|
SQL 资源调度 分布式计算
如何让SQL跑快一点?(优化指南)
这篇文章主要探讨了如何在阿里云MaxCompute(原ODPS)平台上对SQL任务进行优化,特别是针对大数据处理和分析场景下的性能优化。
|
19天前
|
SQL 监控 数据库
慢SQL对数据库写入性能的影响及优化技巧
在数据库管理系统中,慢SQL(即执行缓慢的SQL语句)不仅会影响查询性能,还可能对数据库的写入性能产生显著的不利影响
|
22天前
|
SQL 关系型数据库 PostgreSQL
遇到SQL 子查询性能很差?其实可以这样优化
遇到SQL 子查询性能很差?其实可以这样优化
67 2
|
8天前
|
SQL 数据库 开发者
8种SQL编写陷阱:性能杀手还是团队乐趣?
【10月更文挑战第17天】记住,一个好的开发者不仅要知道如何编写代码,还要知道如何编写高效的代码。
11 0
|
19天前
|
SQL 存储 数据库
慢SQL对数据库写入性能的影响及优化技巧
在数据库管理系统中,慢SQL(即执行缓慢的SQL语句)不仅会影响查询性能,还可能对数据库的写入性能产生显著的不利影响
|
21天前
|
SQL 大数据 API
大数据-132 - Flink SQL 基本介绍 与 HelloWorld案例
大数据-132 - Flink SQL 基本介绍 与 HelloWorld案例
39 0
|
SQL 数据库
sqlSQL2008如何创建定时作业(代理服务)(转)
SQL2008如何创建定时作业?此方法也适应于Sql Server2005数据库,有兴趣的可以来看下! 1.打开【SQL Server Management Studio】,在【对象资源管理器】列表中选择【SQL Server 代理】; --2.
931 0
|
2月前
|
关系型数据库 MySQL 网络安全
5-10Can't connect to MySQL server on 'sh-cynosl-grp-fcs50xoa.sql.tencentcdb.com' (110)")
5-10Can't connect to MySQL server on 'sh-cynosl-grp-fcs50xoa.sql.tencentcdb.com' (110)")
|
4月前
|
SQL 存储 监控
SQL Server的并行实施如何优化?
【7月更文挑战第23天】SQL Server的并行实施如何优化?
92 13
|
4月前
|
SQL
解锁 SQL Server 2022的时间序列数据功能
【7月更文挑战第14天】要解锁SQL Server 2022的时间序列数据功能,可使用`generate_series`函数生成整数序列,例如:`SELECT value FROM generate_series(1, 10)。此外,`date_bucket`函数能按指定间隔(如周)对日期时间值分组,这些工具结合窗口函数和其他时间日期函数,能高效处理和分析时间序列数据。更多信息请参考官方文档和技术资料。