SQL中一些不经意隐式类型转换或者函数使用导致索引失效问题
- SQL code
--A.函数转换--使用FULL JOIN,以前碰到的环境:有时写视图时将两表合并使用FULL JOIN --示例:DECLARE @A TABLE(NAME VARCHAR(10) NOT NULL PRIMARY KEY,ID INT) INSERT @A SELECT CASE WHEN NUMBER%2=0 THEN 'A'+LTRIM(NUMBER) ELSE 'B'+LTRIM(NUMBER) END , NUMBER FROM MASTER..SPT_VALUES WHERE TYPE='P' AND NUMBER BETWEEN 1 AND 10 DECLARE @B TABLE(NAME VARCHAR(10) NOT NULL PRIMARY KEY,ID INT) INSERT @B SELECT CASE WHEN NUMBER%2=0 THEN 'A'+LTRIM(NUMBER) ELSE 'C'+LTRIM(NUMBER) END, NUMBER FROM MASTER..SPT_VALUES WHERE TYPE='P' AND NUMBER BETWEEN 1 AND 20 --1.先SUM统计后再FULL JOIN 连接（这里没有重复所以没SUM了，测试用） SELECT ISNULL(A.NAME,B.NAME) NAME,A.ID,B.ID FROM @A A FULL JOIN @B B ON A.NAME=B.NAME WHERE (A.NAME='A10' OR B.NAME='A10') SELECT ISNULL(A.NAME,B.NAME) NAME,A.ID,B.ID FROM @A A FULL JOIN @B B ON A.NAME=B.NAME WHERE ISNULL(A.NAME,B.NAME)='A10' --上面两语句逻辑相同,因函数转换导致失效(一般建立好视图后才查询SELECT * FROM V_NAME WHERE NAME=@NAME)--解决办法:使用UNION ALL(上面两个NAME 列类型相同)--2.用UNION ALL后再SUM SELECT NAME,SUM(AID) AID,SUM(BID) FROM ( SELECT NAME,ID AS AID,0 AS BID FROM @A UNION ALL SELECT NAME,0 ,ID FROM @B ) T WHERE NAME='A10' GROUP BY NAME ----------------------------------B.列类型转换--1使用UNION ALL,以前遇到的环境如上示例--示例:SET SHOWPLAN_TEXT ONGODECLARE @A TABLE(COL VARCHAR(3) NOT NULL PRIMARY KEY)INSERT @A SELECT 'A'UNION ALL SELECT 'B'--列长度 和 @A 不一致DECLARE @B TABLE(COL VARCHAR(5) NOT NULL PRIMARY KEY)INSERT @B SELECT 'ABCCC' UNION ALL SELECT 'BCFDF'--列长度 和 @A 一致DECLARE @C TABLE(COL VARCHAR(3) NOT NULL PRIMARY KEY)INSERT @C SELECT 'ADD' UNION ALL SELECT 'BAD'SELECT * FROM( SELECT COL FROM @A UNION ALL SELECT COL FROM @B)TWHERE COL LIKE 'A%'SELECT * FROM( SELECT COL FROM @A UNION ALL SELECT COL FROM @C)TWHERE COL LIKE 'A%'GOSET SHOWPLAN_TEXT ON--结果:列类型不一致会导致类型转换,低向高精度转换,最后引起索引失效GO/*StmtText ---------------------------------------------------------------- SELECT * FROM( SELECT COL FROM @A UNION ALL SELECT COL FROM @B)TWHERE COL LIKE 'A%'（所影响的行数为 1 行）StmtText -------------------------------------------------------------------- |--Concatenation |--Filter(WHERE:(like([Expr1002], 'A%', NULL))) | |--Compute Scalar(DEFINE:([Expr1002]=Convert(@A.[COL]))) | |--Clustered Index Scan(OBJECT:(@A)) |--Clustered Index Seek(OBJECT:(@B), SEEK:(@B.[COL] >= 'A' AND @B.[COL] < 'B'), WHERE:(like(@B.[COL], 'A%', NULL)) ORDERED FORWARD)（所影响的行数为 5 行）StmtText --------------------------------------------------------------- SELECT * FROM( SELECT COL FROM @A UNION ALL SELECT COL FROM @C)TWHERE COL LIKE 'A%'（所影响的行数为 1 行）StmtText -------------------------------------------------------------------- |--Concatenation |--Clustered Index Seek(OBJECT:(@A), SEEK:(@A.[COL] >= 'A' AND @A.[COL] < 'B'), WHERE:(like(@A.[COL], 'A%', NULL)) ORDERED FORWARD) |--Clustered Index Seek(OBJECT:(@C), SEEK:(@C.[COL] >= 'A' AND @C.[COL] < 'B'), WHERE:(like(@C.[COL], 'A%', NULL)) ORDERED FORWARD)（所影响的行数为 3 行）StmtText ------------------------ SET SHOWPLAN_TEXT ON*/--2使用SQL语句时数值传参数处理--示例:SET SHOWPLAN_TEXT ONGODECLARE @T TABLE(ID DECIMAL(10,2) PRIMARY KEY)INSERT @T SELECT 1.23 UNION ALL SELECT 2.3DECLARE @PARA DEC(12,2)SET @PARA=2.3SELECT * FROM @T WHERE ID=@PARASELECT * FROM @T WHERE ID=CONVERT(DEC(10,2),@PARA)GOSET SHOWPLAN_TEXT OFFGO--结果ID类型向高类型转换,导致索引失效/*SELECT * FROM @T WHERE ID=@PARA（所影响的行数为 2 行）StmtText ---------------------------- |--Clustered Index Scan(OBJECT:(@T), WHERE:(@T.[ID]=[@PARA]))（所影响的行数为 1 行）StmtText ------------------------------------------------------ SELECT * FROM @T WHERE ID=CONVERT(DEC(10,2),@PARA)（所影响的行数为 1 行）StmtText ---------------------------------------------------- |--Clustered Index Seek(OBJECT:(@T), SEEK:(@T.[ID]=Convert([@PARA])) ORDERED FORWARD)（所影响的行数为 1 行）*/--3使用SQL语句时传字符类型参数处理--示例:IF OBJECT_ID('T') IS NOT NULLDROP TABLE TCREATE TABLE T(COL CHAR(10) PRIMARY KEY )GOINSERT T SELECT 'ABCD'GODECLARE @S VARCHAR(10),@S1 NVARCHAR(10)SELECT @S='ABCD',@S1='ABCD'SELECT * FROM T WHERE COL=@SSELECT * FROM T WHERE COL=@S1 --结果:是类型存储的问题,varchar\char 存储时只占一个字节,而nchar\nvarchar存储时是占两个字节（导致类型转换，索引失效）--(这些从SQL存储引擎可以确定)具体可以参考博客：石头哥的，小麦的，影子老师的/*StmtText ------------------------------------- DECLARE @S VARCHAR(10),@S1 NVARCHAR(10)SELECT @S='ABCD',@S1='ABCD'SELECT * FROM T WHERE COL=@S（所影响的行数为 2 行）StmtText -------------------------------------------------------------------------- |--Clustered Index Seek(OBJECT:([xzdb].[dbo].[T].[PK__T__1ABFEC2C]), SEEK:([T].[COL]=[@S]) ORDERED FORWARD)（所影响的行数为 1 行）StmtText --------------------------------- SELECT * FROM T WHERE COL=@S1（所影响的行数为 1 行）StmtText --------------------------------------------------------------------- |--Clustered Index Scan(OBJECT:([xzdb].[dbo].[T].[PK__T__1ABFEC2C]), WHERE:(Convert([T].[COL])=[@S1]))（所影响的行数为 1 行）*/--以上仅学习的一点记录,如有不对之处,希望各位拍砖指导,感谢论坛各位大虾的指点,努力Ing,转载可不注明作者信息
[解决办法]
前排发言，学习
[解决办法]
技术贴，顶起，这回复还要删嘛？
支持七七！
[解决办法]
支持支持！！！
[解决办法]
前排就座 认真学习
[解决办法]
[解决办法]
这个必须要学！
[解决办法]
好文章.
[解决办法]
多谢共亨
[解决办法]
支持77，写得很好..
[解决办法]
345345345
[解决办法]
[解决办法]
学习了。。。
[解决办法]
lvgeneral
[解决办法]
这个必须要学！
[解决办法]
见识一下
[解决办法]
领教了。谢谢发帖。
[解决办法]
好东西，认真学习一下
[解决办法]
SELECT * FROM
(
SELECT COL FROM @A
UNION ALL
SELECT COL FROM @B
)T
WHERE COL LIKE 'A%'
SELECT * FROM
(
SELECT COL FROM @A
UNION ALL
SELECT COL FROM @C
)T
WHERE COL LIKE 'A%'
GO
[解决办法]
列类型不一致会导致类型转换,低向高精度转换,最后引起索引失效？这是为何？
[解决办法]
just 看看~~~~厉害啊~~·
[解决办法]
谢谢分享！
[解决办法]
[解决办法]
收藏，现在还看不懂，很期待能看懂的一天
[解决办法]
[解决办法]
楼主 还真行的
[解决办法]
文章很好，看完了！！
[解决办法]
文章很好，谢谢
[解决办法]
[解决办法]
好东东
[解决办法]
[解决办法]
好东西 可以收藏
------解决方案--------------------
很好~~这个必须顶了
[解决办法]
好帖，支持
[解决办法]
好啊.多谢了啊
[解决办法]
支持..
[解决办法]
正好要学习 先收藏
[解决办法]
学习，虽然看得有点迷糊，但是总觉得是很有用的！
[解决办法]
感谢楼主分享！