SQLServer 索引总结

时间:2023-12-27 17:04:07

测试案例:

SET STATISTICS IO ON
SET STATISTICS TIME ON
SET STATISTICS PROFILE ON
SELECT  count(A.CarrierTrackingNumber) FROM SALES.SALESORDERDETAIL A
WHERE A.SalesOrderDetailID>10000 AND  A.SalesOrderDetailID<10100

执行计划:

SQLServer 索引总结

测试loopup 和索引查找:

第一步 走了扫描 和通过聚集索引查找 OutputList字段;

我们这里可以添加索引 不过建议创建包含索引;复合索引快 但是维护起来要复杂些,也影响性能;

我做了下比较:

CREATE NONCLUSTERED INDEX [IX_TEST] ON [Sales].[SalesOrderDetail]
(
    [SalesOrderDetailID] ASC
)
INCLUDE (     [CarrierTrackingNumber]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

CREATE NONCLUSTERED INDEX [IX_TEST1] ON [Sales].[SalesOrderDetail]
(
    [SalesOrderDetailID],[CarrierTrackingNumber] ASC
)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

蓝色是包含索引,红色是复合索引,我们无论单独创建哪个索引他都会把第一步合成一个步骤索引查找;(就不截图了)

然后我们做下两者的速度;

DBCC FREEPROCCACHE

SQLServer 索引总结

测试包含、联合索引:

查询优化器智能走向是test1 复合索引;

我们看下io开销:

包含索引test:

SQLServer 索引总结

复合索引:

SQLServer 索引总结

复合索引逻辑读是3,这个很好解释,因为联合索引的索引叶要比包含索引叶子大,包含索引是只是存储包含字段的一个指针,而复合是实际的值;

如果数据量很大,性能差距也很明显:

我们把条件改下,返回的结果集大点,然后下面一句我们使用强制索引,强制语句选择使用包含索引:

SQLServer 索引总结

轻而易举我们发现ix_test1 联合索引 明显绝对性优势;

计算函数对索引影响:

SELECT  count(A.CarrierTrackingNumber) FROM SALES.SALESORDERDETAIL A
WHERE CONVERT(NUMERIC(9,3),A.SALESORDERDETAILID/100)=100

SQLServer 索引总结

这个字段我们上面两个索引都加过了,结果错误的走向了[IX_SalesOrderDetail_ProductID]这个索引;

一般函数在’=’’<’’>’左侧不会走索引! 这个要修改语句逻辑了!

SCAN未必是很差的选择:

PKID 列上有个非聚集索引;

pkid=100093 有2万行数据;

pkid=10094 大概有10条结果;

SQLServer 索引总结

下图得到结果是pkid=100093走了聚集索引扫描,优化器认为这样比通过索引叶查询+聚集rid look 要快;

我们可以尝试下 很简单的测试:

SELECT ANAME FROM TEST with(index=ix_pkid) WHERE PKID=100093
SELECT ANAME FROM TEST  WHERE PKID=100093

SQLServer 索引总结

很显然 全表扫描要快;

我再补上IO次数;

SQLServer 索引总结

差距是不是很明显,索引优化器还是比较很聪明;

还一点 我也顺便测试了

我把这个语句打包成存储过程,直接缓存到缓存中;我们看下效果:

CREATE PROC SCAN (@I INT)
AS
SELECT ANAME FROM TEST  WHERE PKID=@I

DBCC FREEPROCCACHE

我们先执行这个数量少的给优化器看,骗她存入缓存计划;

exec scan 100094

SQLServer 索引总结

当我们执行这个一般大概2次,sqlserer就会存入缓存当中;

开始测试第二个:

exec scan 100093

SQLServer 索引总结

SQLServer 索引总结

你看很好骗吧,太坑了!!!

我们重新编译下:

sp_recompile 'scan'

exec scan 100093

哪天优化器能识别优化这个就更厉害了!!

SQLServer 索引总结

当然我们也有解决办法:

添加包含索引如下:

USE [AdventureWorks2014]
GO
CREATE NONCLUSTERED INDEX IDX_PKIDANAME ON [dbo].[TEST] ([PKID])
INCLUDE ([ANAME])
GO

这样结果就全是seek 了;具体问题具体分析了!

一般filter记录越早越好,对筛选条件SARG(SearchArgument)条件写法注意点:

走索引的SARG:

“列运算符<常量或变量>”

“<常量或变量>列运算符”

“=”

“<”

“>“

“>=”

“<=“

“IN”

”BETWEEN“

“LIKE ‘ABC%’”

”AND“

'50000'="SALESORDERID"

不走索引SARG:

NOT

<>

NOT EXISTS

NOT IN

NOT LIKE

CONVERT,UPPER