SQL中WHERE變量IS NULL條件導(dǎo)致全表掃描問題的解決方法
更新時(shí)間：2013年09月26日 15:34:33 作者：
今天在評審接手的項(xiàng)目中的存儲過程時(shí)，發(fā)現(xiàn)存在大量的在條件里判斷變量是否NULL的寫法
復(fù)制代碼 代碼如下:
SET @SQL = 'SELECT * FROM Comment with(nolock) WHERE 1=1
And (@ProjectIds Is Null or ProjectId = @ProjectIds)
And (@Scores is null or Score =@Scores)'
印象中記得，以前在做Oracle開發(fā)時(shí)，這種寫法是會導(dǎo)致全表掃描的，用不上索引，不知道Sql Server里是否也是一樣呢，于是做一個(gè)簡單的測試
1、建立測試用的表結(jié)構(gòu)和索引：
復(fù)制代碼 代碼如下:
CREATE TABLE aaa(id int IDENTITY, NAME VARCHAR(12), age INT)
go
CREATE INDEX idx_age ON aaa (age)
GO
2、插入1萬條測試數(shù)據(jù)：
復(fù)制代碼 代碼如下:
DECLARE @i INT;
SET @i=0;
WHILE @i<10000
BEGIN
INSERT INTO aaa (name, age)VALUES(CAST(@i AS VARCHAR), @i)
SET @i=@i+1;
END
GO
3、先開啟執(zhí)行計(jì)劃顯示：
在SQL Server Management Studio的查詢窗口里，右擊窗口任意位置，選擇“包含實(shí)際的執(zhí)行計(jì)劃”：
4、開始測試，用下面的SQL進(jìn)行測試：
復(fù)制代碼 代碼如下:
DECLARE @i INT;
SET @i=100
SELECT * FROM aaa WHERE (@i IS NULL OR age = @i)
SELECT * FROM aaa WHERE (age = @i OR @i IS NULL)
SELECT * FROM aaa WHERE age=isnull(@i, age)
SELECT * FROM aaa WHERE age = @i
測試結(jié)果如下：
可以看到，即使@i有值，不管@i IS NULL是放在前面還是放在后面，都無法用到age的索引，另外age=ISNULL(@i,age)也用不上索引
最終結(jié)論，SQL Server跟ORACLE一樣，如果條件里加了 變量 IS NULL，都會導(dǎo)致全表掃描。
建議SQL改成：
復(fù)制代碼 代碼如下:
DECLARE @i INT;
SET @i=100
DECLARE @sql NVARCHAR(MAX)
SET @sql = 'SELECT * FROM aaa'
IF @i IS NOT NULL
SET @sql = @sql + ' WHERE age = @i'
EXEC sp_executesql @sql, N'@i int', @i
當(dāng)然，如果只有一個(gè)條件，可以設(shè)計(jì)成2條SQL，比如：
復(fù)制代碼 代碼如下:
DECLARE @i INT;
SET @i=100
IF @i IS NOT NULL
SELECT * FROM aaa WHERE age = @i
ELSE
SELECT * FROM aaa
但是，如果條件多了，SQL數(shù)目也變得更多，所以建議用EXEC的方案
您可能感興趣的文章:
- MySQL Where 條件語句介紹和運(yùn)算符小結(jié)
- MYSQL WHERE語句優(yōu)化
- MySQL 存儲過程傳參數(shù)實(shí)現(xiàn)where id in(1,2,3,...)示例
- MySQL左聯(lián)多表查詢where條件寫法示例
- MySQL 聯(lián)合索引與Where子句的優(yōu)化 提高數(shù)據(jù)庫運(yùn)行效率
- sql語句中where 1=1的作用
- MySQL中union和join語句使用區(qū)別的辨析教程
- SQL語句的并集UNION 交集JOIN(內(nèi)連接，外連接)等介紹
- 解析sql語句中l(wèi)eft_join、inner_join中的on與where的區(qū)別
- 解析mysql left( right ) join使用on與where篩選的差異
- SQL中JOIN和UNION區(qū)別、用法及示例介紹
- sql join on 用法
- SQL where條件和jion on條件的詳解及區(qū)別
相關(guān)文章
數(shù)據(jù)庫性能優(yōu)化一：數(shù)據(jù)庫自身優(yōu)化提升性能
數(shù)據(jù)庫自身優(yōu)化包括：增加次數(shù)據(jù)文件，設(shè)置文件自動增長、表分區(qū)，索引分區(qū)、分布式數(shù)據(jù)庫設(shè)計(jì)、整理數(shù)據(jù)庫碎片等等.需要了解的朋友可以參考下2013-01-01
SQL中函數(shù) replace 的參數(shù)1的數(shù)據(jù)類型ntext無效的解決方法
SQL中函數(shù) replace 的參數(shù) 1 的數(shù)據(jù)類型 ntext 無效。找了半天找到了解決辦法2010-06-06
windows無法停止SQLserver 服務(wù)的強(qiáng)制關(guān)閉進(jìn)程方案(強(qiáng)制重啟sqlserver Pol
今天在配置sqlserver需要重啟的時(shí)候,提示其它服務(wù)也需要重啟,但SQL Server PolyBase 數(shù)據(jù)移動 (MSSQLSERVER)無法重啟了,這里就給出終極解決方法,強(qiáng)制關(guān)閉PolyBase 數(shù)據(jù)移動進(jìn)程,再重啟sqlserver就可以了2024-03-03
在數(shù)據(jù)庫方面，對于非DBA的程序員來說，事務(wù)與鎖是一大難點(diǎn)，針對該難點(diǎn)，本篇文章試圖采用圖文的方式來與大家一起探討sqlserver事務(wù)與鎖問題2018-02-02
數(shù)據(jù)庫日常練習(xí)題,每天進(jìn)步一點(diǎn)點(diǎn)(2)
下面小編就為大家?guī)硪黄獢?shù)據(jù)庫基礎(chǔ)的幾道練習(xí)題(分享)。小編覺得挺不錯(cuò)的，現(xiàn)在就分享給大家，也給大家做個(gè)參考。一起跟隨小編過來看看吧，希望可以幫到你2021-07-07
SQL?Server數(shù)據(jù)表模糊查詢(like用法)以及查詢函數(shù)詳解
使用SQL Server查詢時(shí)經(jīng)常會使用模糊查詢,需要查詢包含的指定字符串內(nèi)容,這篇文章主要給大家介紹了關(guān)于SQL?Server數(shù)據(jù)表模糊查詢(like用法)以及查詢函數(shù)的相關(guān)資料,需要的朋友可以參考下2024-05-05
使用 TOP 子句限制UPDATE 語句更新的數(shù)據(jù)
這篇文章主要介紹了使用 TOP 子句限制UPDATE 語句更新的數(shù)據(jù),需要的朋友可以參考下2014-08-08
在SQL中,更新數(shù)據(jù)是數(shù)據(jù)庫管理中常見且重要的操作之一,本文主要介紹了Sql中Update的From語句及常見更新操作方式,具有一定的參考價(jià)值,感興趣的可以了解一下2024-04-04
Insus.NET嘗試寫了程序并做了測試，可以得到預(yù)期的結(jié)果，SQL代碼可參考和學(xué)習(xí)之用。2011-09-09
sqlserver數(shù)據(jù)庫導(dǎo)入數(shù)據(jù)操作詳解(圖)
本文主要介紹的是怎么使用Microsoft SQL Server Management Studio導(dǎo)入數(shù)據(jù),大家參考使用吧2014-01-01