MSSQL通过手动创建统计信息优化SQL查询性能案例
Tags： MSSQLSQL Server统计信息
本质原因在于：SQL Server 统计信息只包含复合索引的第一个列的信息，而不包含复合索引数据组合的信息
来源于工作中的一个实际问题，
这里是组合列数据不均匀导致查询无法预估数据行数，从而导致无法选择合理的执行计划导致性能低下的情况
我这里把问题简单化，主要是为了说明问题
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 | 如下一张业务表，主要看两个“状态”字段，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) |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | --测试数据的特点是： --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) |
进行如下查询，就是查询那条所谓的特殊数据
1 | 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准确地知道这个条件下数据的分布请，就容易做出相对准确的执行计划了
通过如下语句，创建一个该条件的统计信息
