sqlexists效率

1. sqlSERVER語句 in和exists哪個效率高本人測試證明

例如下面兩個SQL語句蠢並

1 SELECT OrderNo, SiteCode, AreaCode
2 FROM SchelingProgram
3 WHERE AreaCode IN ( 'P', 'M' ) AND SiteCode IN ( SELECT SiteCode
4 FROM EnvBasicInfo
5 WHERE cityiD = 31 ) AND OrderNo NOT IN (
6 SELECT OrderNo
7 FROM KK_DeliveryinfoTmp )

上面SQL語句IN裡面有IN和NOT IN

1 SELECT OrderNo, SiteCode, AreaCode
2 FROM SchelingProgram
3 WHERE ( AreaCode IN ( 'P', 'M' ) AND SiteCode IN ( SELECT SiteCode
4 FROM EnvBasicInfo
5 WHERE cityiD = 31 )
6 ) AND NOT EXISTS ( SELECT OrderNo
7 FROM KK_DeliveryinfoTmp
8 WHERE KK_DeliveryinfoTmp.OrderNo = SchelingProgram.OrderNo )

上面的SQL語句IN裡面又有NOT EXISTS

這樣的情況很難測試同等條件下IN語句和EXISTS語句的效率

還有一個非SARG運算符

在《SQLSERVER企業級平台管理實踐》的第424頁里提到：

SQLSERVER對篩選條件（search argument/SARG）的寫法有一定的建議

對於不使用SARG運算符的表達式，索引是沒有用的，SQLSERVER對它們很難使用比較優化的做法。非SARG運算符包括

NOT、<>、NOT EXISTS、NOT IN、NOT LIKE和內部函數，例如：Convert、Upper等咐爛

所以當您的表中有索引並且SQL語句包含非SARG運帶簡跡算符，那麼當測試SQL語句的執行時間的時候肯定相差很大，

因為有些SQL語句走索引，有些SQL語句不走索引

建表腳本

注意：兩個表中都有索引！！

CT_FuelingData表

1 USE [GPOSDB]
2 GO
3 /****** 對象: Table [dbo].[CT_FuelingData] 腳本日期: 08/24/2013 11:00:34 ******/
4 SET ANSI_NULLS ON
5 GO
6 SET QUOTED_IDENTIFIER ON
7 GO
8 SET ANSI_PADDING ON
9 GO
10 CREATE TABLE [dbo].[CT_FuelingData](
11 [RecordNO] [int] IDENTITY(1,1) NOT NULL,
12 [I_FD_StationNo] [int] NOT NULL,
13 [VC_FD_No] [varchar](50) NOT NULL,
14 [VC_FD_Cardno] [varchar](50) NOT NULL,
15 [I_FD_CardStatus] [int] NULL,
16 [LI_FD_CTC] [bigint] NOT NULL,
17 [I_FD_TypeCode] [int] NULL,
18 [I_FD_PumpID] [int] NOT NULL,
19 [VC_FD_OilType] [varchar](50) NULL,
20 [DE_FD_Volume] [decimal](18, 2) NULL,
21 [DE_FD_Price] [decimal](18, 2) NULL,
22 [DE_FD_Amount] [decimal](18, 2) NULL,
23 [I_FD_Point] [decimal](10, 2) NULL,
24 [D_FD_DateTime] [datetime] NOT NULL,
25 [VC_FD_GroupNo] [varchar](50) NULL,
26 [D_FD_GroupDate] [datetime] NULL,
27 [DE_FD_CardAmount] [decimal](18, 2) NULL,
28 [DE_FD_VolumeTotals] [decimal](18, 2) NULL,
29 [DE_FD_AmountTotals] [decimal](18, 2) NULL,
30 [I_FD_ISSend] [int] NULL,
31 [VC_FD_CardMoneyauthFile] [varchar](50) NULL,
32 [D_Month] [datetime] NULL,
33 CONSTRAINT [PK_CT_FuelingData_1] PRIMARY KEY CLUSTERED
34 (
35 [VC_FD_No] ASC
36 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
37 ) ON [PRIMARY]
38
39 GO
40 SET ANSI_PADDING OFF

CT_InhouseCard表
1 USE [GPOSDB]
2 GO
3 /****** 對象: Table [dbo].[CT_InhouseCard] 腳本日期: 08/24/2013 10:59:58 ******/
4 SET ANSI_NULLS ON
5 GO
6 SET QUOTED_IDENTIFIER ON
7 GO
8 SET ANSI_PADDING ON
9 GO
10 CREATE TABLE [dbo].[CT_InhouseCard](
11 [RecordNO] [int] IDENTITY(1,1) NOT NULL,
12 [VC_IC_CardNO] [varchar](50) NOT NULL,
13 [VC_IC_PhysicalNO] [varchar](50) NULL,
14 [I_IC_CardType] [int] NULL,
15 [VC_IC_UserName] [varchar](50) NULL,
16 [VC_IC_JobNO] [varchar](50) NULL,
17 [VC_IC_UserID] [varchar](50) NULL,
18 [VC_IC_Password] [varchar](50) NULL,
19 [DE_IC_CardAmount] [decimal](18, 2) NULL,
20 [DE_IC_AppendAmount] [decimal](18, 2) NULL,
21 [DE_IC_ConsumerAmount] [decimal](18, 2) NULL,
22 [I_IC_ISLost] [int] NULL,
23 [D_IC_UsedDateTime] [datetime] NULL,
24 [D_IC_UselifeDateTime] [datetime] NULL,
25 [I_IC_IssueStationNO] [int] NULL,
26 [VC_IC_IssuerNO] [varchar](50) NULL,
27 [D_IC_IssueDateTime] [datetime] NULL,
28 [D_IC_LastUpdateDateTime] [datetime] NULL,
29 [I_IC_CardStatus] [int] NULL,
30 [VC_IC_Remark] [varchar](256) NULL,
31 CONSTRAINT [PK_CT_InhouseCard] PRIMARY KEY CLUSTERED
32 (
33 [VC_IC_CardNO] ASC
34 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
35 ) ON [PRIMARY]
36
37 GO
38 SET ANSI_PADDING OFF

2. 在sql語句多表連接中，in、exists、join哪個效率更高一點

EXISTS、IN與JOIN，都可以用來實現形如「查詢A表中在（或不在）B表中的記錄」的查詢邏輯。x0dx0ax0dx0a在查詢的兩個表大小相當的情況下，3種查詢方式的執行時間通常是：x0dx0aEXISTS <= IN <= JOINx0dx0aNOT EXISTS <= NOT IN <= LEFT JOINx0dx0a只有當表中欄位允許NULL時，NOT IN的方式最慢：x0dx0aNOT EXISTS <= LEFT JOIN <= NOT INx0dx0ax0dx0a但是如果兩個表中一個較小，一個較大，則子查詢表大的用exists，子查詢表小的用in，因為in 是把外表和內表作hash 連接，而exists是對外表作loop循環，每次loop循環再對內表進行查詢。而無論那個表大，用not exists都比not in要快。這是因為如果查詢語句使用了not in 那麼內外表都進行全表掃描，沒有用到索引；而not extsts 的子查詢依然能用到表上的索引。x0dx0ax0dx0aIN的好處是邏輯直觀簡單（通常是獨立子查詢）；缺點是只能判斷單欄位，並且當NOT IN時效率較低，而且NULL會導致不想要的結果。x0dx0aEXISTS的好處是效率高，可以判斷單欄位和組合欄位，並不受NULL的影響；缺點是邏輯稍微復雜（通常是相關子查詢）。x0dx0aJOIN用在這種場合，往往是吃力不討好。JOIN的用途是聯接兩個表，而不是判斷一個表的記錄是否在另一個表。

3. 資料庫sql語句中為什麼exists關鍵字 要比 in 關鍵字執行效率高

exists是根據子查詢有無記錄返回確定條件是否成立，如果有記錄返回，條件成立，如果子查詢未返回記錄，條件不成立，不對子查詢結果進行匹配，所以速度較快；in要對子查詢返回的結果用in關鍵字前面的值逐個進行匹配，如果匹配成功條件成立，匹配不成功條件不成立，所以速度較慢。

4. sql exist和in的區別及查詢效率比較

SQL查詢中in和exists的區別分析

select * from A where id in (select id from B);

select * from A where exists (select 1 from B where A.id=B.id);

對於以上兩種情況，in是在內存里遍歷比較，而exists需要查詢資料庫，所以當B表數據量較大時，exists效率優於in。

1、IN()語句內部工作原理

IN()只執行一次，它查出B表中的所有id欄位並緩存起來。之後，檢查A表的id是否與B表中的id相等，如果相等則將A表的記錄加入結果集中，直到遍歷完A表的所有記錄。
它的查詢過程類似於以下過程：

List resultSet={};

Array A=(select * from A);

Array B=(select id from B);for(int i=0;i<A.length;i++)

{

for(int j=0;j<B.length;j++) {

if(A[i].id==B[j].id) {
resultSet.add(A[i]); break;
}
}
}return resultSet;

可以看出，當B表數據較大時不適合使用in()，因為它會B表數據全部遍歷一次

• 例1：A表有10000條記錄，B表有1000000條記錄，那麼最多有可能遍歷10000*1000000次，效率很差。

• 例2：A表有10000條記錄，B表有100條記錄，那麼最多有可能遍歷10000*100次，遍歷次數大大減少，效率大大提升。

結論：IN()適合B表比A表數據小的情況

2、EXISTS()語句內部工作原理

exists()會執行A.length次，它並不緩存exists()結果集，因為exists()結果集的內容並不重要，重要的是其內查詢語句的結果集空或者非空，空則返回false，非空則返回true。
它的查詢過程類似於以下過程：

• List resultSet={};

• Array A=(select * from A);

• for(int i=0;i<A.length;i++)

• { if(exists(A[i].id) { //執行select 1 from B where B.id=A.id是否有記錄返回



• resultSet.add(A[i]);


• }


• }return resultSet;



當B表比A表數據大時適合使用exists()，因為它沒有那麼多遍歷操作，只需要再執行一次查詢就行。

• 例1：A表有10000條記錄，B表有1000000條記錄，那麼exists()會執行10000次去判斷A表中的id是否與B表中的id相等。

• 例2：A表有10000條記錄，B表有100000000條記錄，那麼exists()還是執行10000次，因為它只執行A.length次，可見B表數據越多，越適合exists()發揮效果。

• 例3：A表有10000條記錄，B表有100條記錄，那麼exists()還是執行10000次，還不如使用in()遍歷10000*100次，因為in()是在內存里遍歷比較，而exists()需要查詢資料庫，我們都知道查詢資料庫所消耗的性能更高，而內存比較很快。

結論：EXISTS()適合B表比A表數據大的情況

3、使用情況分析

當A表數據與B表數據一樣大時，in與exists效率差不多，可任選一個使用。

在插入記錄前，需要檢查這條記錄是否已經存在，只有當記錄不存在時才執行插入操作，可以通過使用 EXISTS 條件句防止插入重復記錄。

• insert into A (name,age) select name,age from B

• where not exists (select 1 from A where A.id=B.id);




EXISTS與IN的使用效率的問題，通常情況下採用exists要比in效率高，因為IN不走索引。但要看實際情況具體使用：

• IN適合於外表大而內表小的情況；

• EXISTS適合於外表小而內表大的情況。

4、關於EXISTS：

EXISTS用於檢查子查詢是否至少會返回一行數據，該子查詢實際上並不返回任何數據，而是返回值True或False。

EXISTS 指定一個子查詢，檢測行的存在。

語法： EXISTS subquery

參數： subquery 是一個受限的 SELECT 語句 (不允許有 COMPUTE 子句和 INTO 關鍵字)。

結果類型： Boolean 如果子查詢包含行，則返回 TRUE ，否則返回 FLASE 。

結論：

• select * from A where exists (select 1 from B where A.id=B.id)



EXISTS(包括 NOT EXISTS )子句的返回值是一個boolean值。 EXISTS內部有一個子查詢語句(SELECT ... FROM...),我將其稱為EXIST的內查詢語句。其內查詢語句返回一個結果集, EXISTS子句根據其內查詢語句的結果集空或者非空，返回一個布爾值。

一種通俗的可以理解為：將外查詢表的每一行，代入內查詢作為檢驗，如果內查詢返回的結果取非空值，則EXISTS子句返回TRUE，這一行行可作為外查詢的結果行，否則不能作為結果。

分析器會先看語句的第一個詞，當它發現第一個詞是SELECT關鍵字的時候，它會跳到FROM關鍵字，然後通過FROM關鍵字找到表名並把表裝入內存。接著是找WHERE關鍵字，如果找不到則返回到SELECT找欄位解析，如果找到WHERE，則分析其中的條件，完成後再回到SELECT分析欄位。最後形成一張我們要的虛表。

WHERE關鍵字後面的是條件表達式。條件表達式計算完成後，會有一個返回值，即非0或0，非0即為真(true)，0即為假(false)。同理WHERE後面的條件也有一個返回值，真或假，來確定接下來執不執行SELECT。

分析器先找到關鍵字SELECT，然後跳到FROM關鍵字將STUDENT表導入內存，並通過指針找到第一條記錄，接著找到WHERE關鍵字計算它的條件表達式，如果為真那麼把這條記錄裝到一個虛表當中，指針再指向下一條記錄。如果為假那麼指針直接指向下一條記錄，而不進行其它操作。一直檢索完整個表，並把檢索

java">

出來的虛擬表返回給用戶。EXISTS是條件表達式的一部分，它也有一個返回值(true或false)。




作者：IronM
鏈接：https://www.jianshu.com/p/f212527d76ff
來源：簡書
著作權歸作者所有。商業轉載請聯系作者獲得授權，非商業轉載請註明出處。