SQLServer 索引总结

2024-04-05 16:14:09

测试案例：

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

执行计划：

测试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

测试包含、联合索引：

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

我们看下io开销：

包含索引test：

复合索引：

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

如果数据量很大，性能差距也很明显：

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

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

计算函数对索引影响：

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

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

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

SCAN未必是很差的选择：

PKID 列上有个非聚集索引；

pkid=100093 有2万行数据；

pkid=10094 大概有10条结果；

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

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

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

很显然全表扫描要快；

我再补上IO次数;

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

还一点我也顺便测试了

我把这个语句打包成存储过程，直接缓存到缓存中；我们看下效果：

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

DBCC FREEPROCCACHE

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

exec scan 100094

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

开始测试第二个：

exec scan 100093

你看很好骗吧，太坑了！！！

我们重新编译下：

sp_recompile 'scan'

exec scan 100093

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

当然我们也有解决办法：

添加包含索引如下：

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

最新文章

热门文章