列舉一些關於 SQL 語句的謠言，避免一些生瓜蛋子面試的時候被另外一群生瓜蛋子的 SQL 優化寶典給坑了。
以下所有內容都是 SQL Server 中的，其他資料庫只能參考和借鑑
一、全表掃描
全表掃描: 這種說法是有問題的，因為不夠精確，或者就是錯的
掃描在 SQL Server 分為三種情況
Heap：Table scan （全表掃描）
Clustered index ：Clustered index scan （聚集索引掃描）
Nonclustered index ：Index scan （索引掃描）
準備測試資料
DROP TABLE Org_User DROP TABLE Org_User1 -- 建立測試表 CREATE TABLE Org_User(Id INT,UserName NVARCHAR(50),Age INT) CREATE TABLE Org_User1(Id INT,UserName NVARCHAR(50),Age INT) -- 建立聚集索引和非聚集索引 CREATE CLUSTERED INDEX Index_Org_User_Id ON Org_User(Id) CREATE NONCLUSTERED INDEX NoNIndex_Org_User_Name ON Org_User(UserName) CREATE TABLE #Temp(Id INT) INSERT INTO #Temp VALUES(1) INSERT INTO #Temp VALUES(2) INSERT INTO #Temp VALUES(3) INSERT INTO #Temp VALUES(4) INSERT INTO #Temp VALUES(5) INSERT INTO #Temp VALUES(6) INSERT INTO #Temp VALUES(7) INSERT INTO #Temp VALUES(8) INSERT INTO #Temp VALUES(9) INSERT INTO #Temp VALUES(10) -- 批量插入10W條測試資料 SELECT T1.Id, 'UserName_' + CONVERT(NVARCHAR(20), T1.Id) AS 'UserName', T1.Id + 10 AS 'Age' INTO #Temp1 FROM ( SELECT TOP 100000 Id = ROW_NUMBER() OVER (ORDER BY T1.Id) FROM #Temp AS T1 CROSS JOIN #Temp AS T2 CROSS JOIN #Temp AS T3 CROSS JOIN #Temp AS T4 CROSS JOIN #Temp AS T5 ORDER BY T1.Id ) AS T1 INSERT INTO dbo.Org_User SELECT * FROM #Temp1 INSERT INTO dbo.Org_User1 SELECT * FROM #Temp1 SELECT * FROM dbo.Org_User1 SELECT * FROM dbo.Org_User SELECT UserName FROM dbo.Org_User WHERE UserName LIKE '%Name%'
因此可以理解成，只要表中有聚集索引就不可能有全表掃描，只能是聚集索引掃描
-- 在 Age 列上建立一個非聚集索引 CREATE NONCLUSTERED INDEX NoNIndex_Org_User_Age ON Org_User(Age)
-- 執行這個 SQL SET STATISTICS TIME ON SET STATISTICS IO ON WITH Temp AS ( SELECT Id,Age FROM Org_User WHERE Age > 100 AND Age < 1000 ) SELECT * FROM Org_User AS T1 INNER JOIN Temp AS T2 ON T1.Id = T2.Id /* (899 行受影響) 表 'Worktable'。掃描計數 0，邏輯讀取 0 次，物理讀取 0 次，預讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預讀 0 次。 表 'Org_User'。掃描計數 2，邏輯讀取 14 次，物理讀取 0 次，預讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預讀 0 次。 (1 行受影響) SQL Server 執行時間: CPU 時間 = 0 毫秒，佔用時間 = 136 毫秒。 */
上面這個例子，執行計劃中顯示的是 Clustered index scan（聚集索引掃描），但是通過IO統計可以看到他其實是範圍掃描（邏輯讀取 14 次）
最後：並不是索引掃描的效率就一定低，合理的運用索引掃描（範圍掃描）反而會提高查詢速度（下面的臨時表會給出一個例子）
在其他資料庫中有自己特有的掃描比如說分割槽掃描等
二、IN 和 EXISTS
先說結論：無所謂誰好誰壞，想用哪個就用那個
準備測試資料
DROP TABLE IdTable DROP TABLE Temp CREATE TABLE Temp(Id INT) CREATE CLUSTERED INDEX Index_Temp_Id ON Temp(Id) CREATE TABLE IdTable(Id INT, Id_Index INT, Id_NonIndex INT) CREATE CLUSTERED INDEX Index_Id_Index ON IdTable(Id_Index) CREATE NONCLUSTERED INDEX NonIndex_Id_Index ON IdTable(Id_NonIndex) INSERT INTO Temp VALUES(1) INSERT INTO Temp VALUES(22) INSERT INTO Temp VALUES(33) INSERT INTO Temp VALUES(44) INSERT INTO Temp VALUES(55) INSERT INTO Temp VALUES(66) INSERT INTO Temp VALUES(77) INSERT INTO Temp VALUES(88) INSERT INTO Temp VALUES(99) INSERT INTO Temp VALUES(110) INSERT INTO dbo.IdTable SELECT T1.Id, T1.Id AS 'Id_Index', T1.Id AS 'Id_NonIndex' FROM ( SELECT TOP 100000 Id = ROW_NUMBER() OVER (ORDER BY T1.Id) FROM Temp AS T1 CROSS JOIN Temp AS T2 CROSS JOIN Temp AS T3 CROSS JOIN Temp AS T4 CROSS JOIN Temp AS T5 ORDER BY T1.Id ) AS T1 SET STATISTICS IO ON SET STATISTICS TIME ON
進行四組對比
對比的時候，所有的SQL執行前都執行一次 DBCC DROPCLEANBUFFERS 避免記憶體對查詢造成影響
第一組
/* 沒有索引 */ --清除所有快取 DBCC DROPCLEANBUFFERS SELECT * FROM dbo.IdTable WHERE Id IN (SELECT Id FROM Temp) --清除所有快取 DBCC DROPCLEANBUFFERS SELECT * FROM dbo.IdTable AS T1 WHERE EXISTS (SELECT Id FROM Temp AS T2 WHERE T1.Id = T2.Id)
各種掃描，這一組沒有什麼意義，實際工作中就不應該出現這種情況
第二組
/* 使用聚集索引 */ --清除所有快取 DBCC DROPCLEANBUFFERS SELECT * FROM dbo.IdTable WHERE Id_Index IN (SELECT Id FROM Temp) --清除所有快取 DBCC DROPCLEANBUFFERS SELECT * FROM dbo.IdTable AS T1 WHERE EXISTS (SELECT Id FROM Temp AS T2 WHERE T1.Id_Index = T2.Id)
執行計劃一模一樣
IO 統計也一模一樣
TIME IN 遠遠的好於 EXISTS （CPU資源和更多的時間）
第三組
/* 使用非聚集索引 */ --清除所有快取 DBCC DROPCLEANBUFFERS SELECT * FROM dbo.IdTable WHERE Id_NonIndex IN (SELECT Id FROM Temp) --清除所有快取 DBCC DROPCLEANBUFFERS SELECT * FROM dbo.IdTable AS T1 WHERE EXISTS (SELECT Id FROM Temp AS T2 WHERE T1.Id_NonIndex = T2.Id)
執行計劃一模一樣
IO 統計也一模一樣
TIME 基本一樣，多次執行幾次觀察輸出內容得出 IN 要比 EXISTS 慢個 1 - 6 毫秒左右
第四組
CREATE TABLE IdTable1(Id INT, Id_Index INT, Id_NonIndex INT) CREATE CLUSTERED INDEX Index_Id_Index ON IdTable1(Id_Index) CREATE NONCLUSTERED INDEX NonIndex_Id_Index ON IdTable1(Id_NonIndex) INSERT INTO dbo.IdTable1 SELECT T1.Id, T1.Id AS 'Id_Index', T1.Id AS 'Id_NonIndex' FROM ( SELECT TOP 100000 Id = ROW_NUMBER() OVER (ORDER BY T1.Id) FROM Temp AS T1 CROSS JOIN Temp AS T2 CROSS JOIN Temp AS T3 CROSS JOIN Temp AS T4 CROSS JOIN Temp AS T5 ORDER BY T1.Id ) AS T1 --清除所有快取 DBCC DROPCLEANBUFFERS SELECT * FROM dbo.IdTable WHERE Id_Index IN (SELECT Id FROM IdTable1 WHERE (Id_Index > 1000 AND Id_Index < 2000) OR (Id_Index > 10000 AND Id_Index < 12000) OR (Id_Index > 20000 AND Id_Index < 22000)) --清除所有快取 DBCC DROPCLEANBUFFERS SELECT * FROM dbo.IdTable AS T1 WHERE EXISTS (SELECT Id FROM IdTable1 AS T2 WHERE (Id_Index > 1000 AND Id_Index < 2000 AND T1.Id_Index = T2.Id_Index) OR (Id_Index > 10000 AND Id_Index < 12000 AND T1.Id_Index = T2.Id_Index) OR (Id_Index > 20000 AND Id_Index < 22000 AND T1.Id_Index = T2.Id_Index))
這是一組很極端的SQL語句
兩個 OR 連結了三個查詢條件，每個條件都是查詢一個範圍內的資料
EXISTS 對 CPU 的消耗更大
IN 對聚集索引的一部分進行了掃描 （邏輯讀只有 60 次，從第一組測試結果中可以得出對整個聚集索引進行掃描應該是 262 次）
總結：
首先這是一次並不是很嚴謹的測試，只對 INT 這種型別的欄位進行了四組測試，沒有包含其他型別的欄位，測試的資料也都很簡單，實際情況會更加的複雜。
這四種測試中
第一和第四種，都是有問題的
第一種進行了各種掃描，一定要避免這種情況
第四種我基本上沒有寫過這種SQL，類似的需求我會用 UNION ALL 代替。
使用 UNION ALL 主要是因為用 UNION ALL 寫的 SQL 清晰，一眼看下去就知道這段SQL幹了什麼，便於維護
而效能上 UNION ALL 也很快，並且把一個複雜的SQL拆分成多個簡單的SQL也是SQL優化的一個點
第二種和第三種代表了大多數的 IN 和 EXISTS
不管業務怎麼變，不管資料怎麼不一樣，他們的底層索引執行的邏輯都是一致的
執行計劃和邏輯讀大致的邏輯（第三組測試結果）
表 'IdTable'。掃描計數 10，邏輯讀取 40 次，物理讀取 1 次，預讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預讀 0 次。 表 'Temp'。掃描計數 1，邏輯讀取 2 次，物理讀取 1 次，預讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預讀 0 次。
物理讀就是把硬碟上的資料讀取到記憶體中
掃描計數可以簡單粗暴的理解成呼叫索引的次數
1、對 Temp 表進行聚集索引掃描
2、對 1 的結果進行遍歷
3、通過 Temp.Id 欄位的值到 NonIndex_Id_Index 這個非聚集索引中查詢資料，把結果返回給 2
4、第 2、3執行完成之後，臨時的結果集中只有 Id_Index 和 Id_NonIndex 這兩個欄位，缺少 Id 欄位，所以對該結果集進行遍歷
5、通過 NonIndex_Id_Index.Id_Index 欄位的值 Index_Id_Index 這個聚集索引中查詢資料，把結果返回給 4
6、返回最終結果
最後
雖然 IN 和 EXISTS 有些差別
但是 不應該糾結使用 IN 還是 EXISTS
因為：
一、他們索引執行的邏輯基本上是差不多的，兩條等價的SQL多次執行有可能會得到不同的結果（第三組SQL）。
二、把 IN 換成 EXISTS 或者把 EXISTS 換成 IN 的收益能有多大心裡面要有譜。
三、很難說到底誰好誰壞，IN有些情況下效率更高，EXISTS 會額外的消耗CPU資源。交給優化器就好了
三、OR 會導致XX掃描
OR 會導致 XX掃描這是一個極其離譜的謠言
上面第四組的兩條這麼極端的SQL 一個對聚集索引進行了範圍掃描，一個是索引搜尋
四、關於用不用臨時表的問題
SET STATISTICS TIME ON SET STATISTICS IO ON DROP TABLE #Temp; --清除所有快取 DBCC DROPCLEANBUFFERS; WITH Temp AS ( SELECT Id,Age FROM Org_User WHERE Age > 100 AND Age < 1000 ) SELECT * FROM Org_User AS T1 INNER JOIN Temp AS T2 ON T1.Id = T2.Id --清除所有快取 DBCC DROPCLEANBUFFERS SELECT Id,Age INTO #Temp FROM Org_User WHERE Age > 100 AND Age < 1000 SELECT * FROM Org_User AS T1 INNER JOIN #Temp AS T2 ON T1.Id = T2.Id
上面兩個SQL返回的結果是等價的
SQL Server 分析和編譯時間: CPU 時間 = 0 毫秒，佔用時間 = 0 毫秒。 (899 行受影響) 表 'Worktable'。掃描計數 0，邏輯讀取 0 次，物理讀取 0 次，預讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預讀 0 次。 表 'Org_User'。掃描計數 2，邏輯讀取 14 次，物理讀取 2 次，預讀 629 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預讀 0 次。 (1 行受影響) SQL Server 執行時間: CPU 時間 = 0 毫秒，佔用時間 = 84 毫秒。 SQL Server 分析和編譯時間: CPU 時間 = 0 毫秒，佔用時間 = 1 毫秒。 表 'Org_User'。掃描計數 1，邏輯讀取 4 次，物理讀取 0 次，預讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預讀 0 次。 (899 行受影響) (1 行受影響) SQL Server 執行時間: CPU 時間 = 0 毫秒，佔用時間 = 1 毫秒。 SQL Server 分析和編譯時間: CPU 時間 = 3 毫秒，佔用時間 = 3 毫秒。 (899 行受影響) 表 'Org_User'。掃描計數 899，邏輯讀取 3431 次，物理讀取 3 次，預讀 16 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預讀 0 次。 表 '#Temp_______________________________________________________________________________________________________________00000000000E'。掃描計數 1，邏輯讀取 2 次，物理讀取 0 次，預讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預讀 0 次。 (1 行受影響) SQL Server 執行時間: CPU 時間 = 0 毫秒，佔用時間 = 173 毫秒。
通過執行計劃，和系統資源的統計
類似的SQL我一定是用 WITH 而不是臨時表
臨時表是儲存在 TempDB 中的，使用臨時表就會給TempDB造成壓力（新增、刪除）
使用臨時表的時候他是兩條SQL，需要進行兩次分析和編譯，最後一個SQL不會進行快取
大量的邏輯讀（相較於 WITH而言）
使用 WITH
從IO統計開看預讀 629 次，但是後面在執行的時候就都是 0 了
使用 WITH 把一個結果集存到記憶體中不會對 TempDB 造成壓力（相較於臨時表不會佔用多餘的資源）
從執行計劃來看，索引範圍掃描的造成的邏輯讀取遠遠低於 索引搜尋（Clustered Index Seek）
最後我在寫SQL的時候想起來用 WITH 就用 WITH，想不起來就用臨時表。在SQL Server中他們還是有差別的。
在其他的資料庫中 使用臨時表後 DELETE 臨時表的時候需要注意回滾段的問題
最後的最後：
上面給出的例子，我都寫了相關的邏輯，搞明白索引執行的邏輯後，再去看別人寫的SQL優化寶典、乾貨等就知道自己面試的時候，死在了哪一個環節
還有就是上面的索引都只有一列，生產環境中大多數都是複合索引
--再寫一些我認為是謠言的乾貨，這些謠言我不寫DEMO去驗證了，思路上面的DEMO都有 /* 謠言 一、儘量避免使用in 會導致引擎走全表掃描。 二、儘量避免進行null值的判斷，會導致資料庫引擎放棄索引進行全表掃描。 三、多表關聯查詢時，小表在前，大表在後。 四、where條件有些欄位要放前面，有些欄位要放後面 一、我上面寫的SQL已經對他進行闢謠了 二、搞明白索引的結構，在一個可為null 的欄位上建一個索引，自個試試就知道了 三和四、資料庫中有一個東西叫做優化器，這些東西優化器會做，自個寫個DEMO試試就知道了 */ /* 看情況 一、儘量避免在where條件中等號的左側進行表示式、函式操作，會導致資料庫引擎放棄索引進行全表掃描。 二、查詢條件不能用 <> 或者 != 、NOT IN 、NOT EXISTS 這兩個呢不全對，因為 WHERE 條件中如果只有上面的條件一定會有問題的 但是，下面這個例子中 SELECT Id,Age FROM Org_User WHERE Age > 100 AND Age < 1000 AND Age != 200 我用了 != 這種寫法，並沒有進行索引掃描 原因就是在執行 Age > 100 AND Age < 1000 AND 的時候 先找到 Age = 100 的這條資料，再根據資料頁進行掃描，一直掃描到 Age = 1000 這條資料， 在掃描的過程中會排除掉 Age = 200 的資料 */
在不考慮硬體等外部環境的情況下
SQL優化的思路是：
會用資料庫中各種統計工具，會用執行計劃、知道索引執行的邏輯
一、減少資料訪問
更少的掃描計數
更少的邏輯讀
沒有物理讀（物理讀就是把硬碟上的資料讀取到記憶體中，硬碟IO和記憶體IO誰高誰低就不用多說了）
二、減少CPU和記憶體的開銷
減少 排序、合併這些操作
知道那些語句會造成CPU和記憶體的額外開銷。UNION、DISTINCT等
SQL語句怎麼寫
連線的表越多，效能越差
優先執行可顯著減少資料量的連線，既降低了複雜度，也能夠容易按照預期執行