-
当唯一非聚集索引变量被传入时，解决其失效的方法
资源介绍
场景一:
确认1.碰到了一个一个非常慢的SQL server语句，发现是变量查询时很慢
SQL语句:
DECLARE @SN VARCHAR(12)
SET @SN = '30F335CD0045'
SELECT [Mac2]
FROM SF_Cp_Detail
WHERE [Mac2] = @SN
确认2.查看索引是:唯一非聚集索引
CREATE UNIQUE NONCLUSTERED INDEX [IX_SF_CP_Detail_MAC2] ON [dbo].[SF_Cp_Detail]
(
[Mac2] ASC
)
WHERE ([MAC2]<>'' AND [MAC2] IS NOT NULL)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO
确认3.执行计划如下:
执行计划
SET STATISTICS IO ON ;
(0 行受影响)
Table 'SF_Cp_Detail'. Scan count 33, logical reads 1267942, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
场景二:
为什么用不到索引IX_SF_CP_Detail_MAC2]呢?
尝试1:
把唯一非聚集索引改为非聚集索引, Done,用到索引了.
Drop index …….
CREATE NONCLUSTERED INDEX [IX_SF_Cp_Detail_Mac2] ON [dbo].[SF_Cp_Detail]
(
[Mac2] 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
尝试2:
可是还需要验证唯一性怎么办呢?
试了半天未达到目标, 根据别人提示, 恢复唯一非聚集
CREATE UNIQUE NONCLUSTERED INDEX [IX_SF_CP_Detail_MAC2] ON [dbo].[SF_Cp_Detail]
(
[Mac2] ASC
)
WHERE ([MAC2]<>'' AND [MAC2] IS NOT NULL)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO
然后
修改查询:
DECLARE @SN VARCHAR(12)
SET @SN = '30F335CD0045'
SELECT [Mac2]
FROM SF_Cp_Detail
WHERE [Mac2] = @SN option (recompile)
预计查询计划
看样子不行, 但是,看一下实际查询计划:
OK ,Done,可以了.
尝试3.
但是我不可能去每个程序加option (recompile)呀,
而且随着数据量的增大,每次重新编译索引, 本身就导致SQL语句变慢.
最终解决方案:
唯一非聚集索引留着, 再添加一个 非聚集索引,保留两个索引, 终于搞定了.
CREATE UNIQUE NONCLUSTERED INDEX [IX_SF_CP_Detail_MAC2] ON [dbo].[SF_Cp_Detail]
(
[Mac2] ASC
)
WHERE ([MAC2]<>'' AND [MAC2] IS NOT NULL)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO
CREATE NONCLUSTERED INDEX [IX_SF_Cp_Detail_Mac2] ON [dbo].[SF_Cp_Detail]
(
[Mac2] 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