文件名称：唯一非聚集索引变量传入时索引失效解决方案
文件大小：52KB
文件格式：DOCX
更新时间：2018-08-10 11:10:46
索引失效
场景一: 确认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