SQL SERVER優化

---

本文主要講解如何使用alter index來rebuild和reorganize索引來清除碎片，rebuild能夠完全清除碎片，但是reorganize卻不能。

Rebuild index

--1.準備實驗資料
select * into Employee from AdventureWorks2008R2.HumanResources.Employee;

--2.檢視使用空間：Employee    290            72 KB    56 KB    8 KB    8 KB
sp_spaceused Employee

--3.建立聚集索引
create clustered index IX_BusinessEntityID on Employee(BusinessEntityID);

--4.檢視使用空間：Employee    290            80 KB    56 KB    16 KB    8 KB
sp_spaceused Employee

--5.索引重建，清除fragment，並設定fillfactor為60
ALTER INDEX ALL ON Employee
REBUILD WITH (FILLFACTOR = 60, SORT_IN_TEMPDB = ON,
              STATISTICS_NORECOMPUTE = ON);

--6.檢視使用空間：Employee    290            144 KB    88 KB    16 KB    40 KB
sp_spaceused Employee

結論

1.在建立索引以後，index從8變到16，說明索引佔用物理磁碟，因此索引是一個physical object。
2.在重建索引並設定fillfactor為60以後，我們發現data空間變大，這是因為填充因子重新使得原來裝滿的data page現在只裝60%
3.fillfactor只在對已有資料create index和alter index rebuild的時候有用。對於普通的insert操作無效。
4.fillfactor的取值是1-100，msnd上說取0跟取100一樣，但是實際測試發現使用0報錯。

reorganize index

ALTER INDEX ALL ON Employee
REORGANIZE
GO

兩者的區別

Rebuilding an index drops and re-creates the index. This removes fragmentation, reclaims disk space by compacting the pages based on the specified or existing fill factor setting, and reorders the index rows in contiguous pages. When ALL is specified, all indexes on the table are dropped and rebuilt in a single transaction.

重新生成索引將會刪除並重新建立索引。 這將根據指定的或現有的填充因子設定壓縮頁來刪除碎片、回收磁碟空間，然後對連續頁中的索引行重新排序。 如果指定 ALL，將刪除表中的所有索引，然後在單個事務中重新生成。

Reorganizing an index uses minimal system resources. It defragments the leaf level of clustered and nonclustered indexes on tables and views by physically reordering the leaf-level pages to match the logical, left to right, order of the leaf nodes. Reorganizing also compacts the index pages. Compaction is based on the existing fill factor value.

重新組織索引使用最少系統資源重新組織索引。 通過對葉級頁以物理方式重新排序，使之與葉節點的從左到右的邏輯順序相匹配，進而對錶和檢視中的聚集索引和非聚集索引的葉級進行碎片整理。 重新組織還會壓縮索引頁。 壓縮基於現有的填充因子值。

Rebuilding an index can be executed online or offline. Reorganizing an index is always executed online. To achieve availability similar to the reorganize option, you should rebuild indexes online.

rebulid index既可以在online又可以在offline下執行，而reorganize index只能在online下執行的。

Difference between rebuild index online and offline(PS:2012-9-11)

既然rebuild index既可以是online模式，也可以是offline模式，那麼兩者有什麼區別呢。這個我們可以參考stackoverflow上面的一篇文章：What is the difference between OFFLINE and ONLINE index rebuild in SQL Server? 在這裡我還是簡要總結一下：

online模式下

rebuild index會複製舊索引來新建索引，此時舊的索引依然可以被讀取和修改，但是所以在舊索引上的修改都會同步更新到新索引下。中間會有一些衝突解決機制，具體參考Online Index Operations 裡面的Build Phase這一章節。然後在rebuild這個過程完整的時候，會對table上鎖一段時間，在這段時間裡會用新索引來替換舊索引，當這個過程完成以後再釋放table上面的鎖。如果索引列包含 LOB物件的話，在SQL Server 2005/2008/R2中rebuild index online會失敗。在sql server 2012中，即使索引列包含LOB物件，也可以rebuild index online了，可以參考 Online Index Operations for indexes containing LOB columns.

offline模式下

rebuilde index會對table上鎖，所有對這個table的讀寫操作都會被阻塞，在這期間新索引根據舊索引來建立，其實就是一個複製的過程，但是新索引沒有碎片，最後使用新索引替換舊索引。當rebuild整個過程完成以後，table上面的鎖才會被釋放。

---

三、實際運用

在官方文件中涉及到二十幾列，但是實際上用不著關心那麼多列，只需要檢視幾列的資訊就足以了。

SELECT   
    dbInfo.name AS databaseName,  
    objInfo.name AS objName,  
    ixInfo.name AS ixName,  
    index_stats.avg_fragmentation_in_percent,  
    index_stats.fragment_count,  
    index_stats.page_count,  
    index_stats.avg_page_space_used_in_percent,  
    index_stats.record_count,  
    index_stats.avg_record_size_in_bytes  
FROM sys.dm_db_index_physical_stats(NULL,NULL,NULL,NULL,DEFAULT) AS index_stats  
INNER JOIN sys.databases AS dbInfo ON index_stats.database_id = dbInfo.database_id  
INNER JOIN sys.objects AS objInfo ON objInfo.object_id=index_stats.object_id  
INNER JOIN sys.indexes AS ixInfo ON index_stats.index_id = ixInfo.index_id  
WHERE 1=1  
AND dbInfo.name=N'test'  
AND objInfo.name='TB_TEST'  
AND ixInfo.name='IX_PK_TB_TEST'  

（1）、avg_fragmentation_in_percent
表示索引與堆的邏輯平均碎片百分比，
如果該值為10%-20%，碎片應該沒有什麼大問題；
如果該值為20%-40%，碎片有可能成為問題，可以通過索引重組來完成
如果該值為大於40%，可能要求索引重建
（2）、fragment_count
表示碎片數量，或者是組成索引單獨頁面的數量
（3）、page_count
組成統計的索引或資料頁面數量的統計

四、碎片處理

解除安裝並且重建索引

使用DROP_EXISTING子句重建索引

執行ALTER INDEX REBUILD語句

執行ALTER INDEX REORGANIZE

（1）、解除安裝並且重建索引

該方式是最簡單 的方法，就是先刪除在建立，但是該方式也有許多缺點

1、阻塞，該方式會造成系統大的開銷，並且可能導致阻塞，因為如果資料過大，該操作是很耗時間的，在該期間可能阻塞該表上的其他操作

2、丟失索引，因為在索引解除安裝等待重建期間，該表的查詢沒有索引可以使用，導致查詢效能下降

3、非聚簇索引，因為解除安裝重建的索引是聚簇索引，那麼其他的非聚簇索引也必須被重建，因為非聚簇索引是建立在聚簇索引之上的

4、唯一性約束，用於定義主鍵或者唯一性約束的索引不能被刪除。並且主鍵等可能被外來鍵引用，在解除安裝主鍵之前必須先解除安裝外來鍵

因此，該方式只適合在沒有什麼人訪問資料庫的的空閒時候採用，有足夠的時間慢慢倒騰

（2）、使用DROP_EXISTING子句重建索引

在重建聚簇索引的時候，使用DROP_EXISTING子句可以避免重建非聚簇索引，因為行定位器使用的索引鍵值保持不變，並且是在同一原子步驟中。
可以為聚簇索引與非聚簇索引使用DROP_EXISTING子句，甚至可以將非聚簇索引轉換為聚簇索引，但是不能將聚簇索引轉換為非聚簇索引。
當然該方式也是有缺點的

1、阻塞，與解除安裝重建方式類似，當資源緊張時也可能導致阻塞

2、使用約束的索引，該方式可以重新建立具有約束的索引，但是如果該約束是主鍵或是與外來鍵相關的唯一性約束，有可能會有問題

3、具有多個碎片化的索引的表，由於資料產生碎片，索引常常也隨之產生碎片，如果使用該碎片技術，表上所有索引必須單獨重建

（3）、使用ALTER INDEX REBUILD語句

該操作是在一個原子步驟中完成的，是物理的重建索引。但是並不是先刪除在建立而是動態的重建索引，將碎片降低到最低程度。
儘管該方式是很常用的索引重建方式，但是仍有一些缺陷：

1、阻塞，與前兩種方式類似，當資源緊張時也是有可能造成堵塞的

2、事務回滾，因為該操作是一個原子操作，如果該操作中途停止，事務將會回滾，那麼之前碎片整理操作將會丟失

（4）、使用ALTER INDEX REORGANIZE語句

該方式並不是物理重建，而是採用邏輯方式按照索引鍵的邏輯順序重新排列現有的索引葉子頁面來減少碎片。
該方式是非原子性的操作
當然該方式也是有缺點的：

1、該方式不能像REBUILD方式有效的減少碎片

2、對於高度碎片化的索引，該方式比=重建索引更耗時間

3、如果索引跨越多個檔案，該方式不能在檔案之間移動頁面

（5）、集中方式的特性比較

特性/問題	解除安裝並且重建	使用DROP_EXISTING	REBUILD	REORGANIZE
在整理聚簇索引碎片時 重建非聚簇索引	兩次	無	無	無
丟失索引	是	無	無	無
整理具有約束的索引碎片	很複雜	一般複雜	簡單	簡單
同時進行多個索引碎片整理	否	否	是	是
併發性	低	低	一般	高
中途撤銷	因為不使用事務而存在危險	程式丟失	程式丟失	程式被保留
碎片整理程度	高	高	高	中一般
應用新的填充因子	是	是	是	否
更新統計	是	是	是	否

---

內部碎片

當索引頁沒有用到最大量時就產生了內部碎片。雖然在一個有頻繁資料插入的應用程式裡這也許有幫助，然而設定一個fill factor（填充因子）會在索引頁上留下空間，伺服器內部碎片會導致索引尺寸增加，從而在返回需要的資料時要執行額外的讀操作。這些額外的讀操作會降低查詢的效能。

怎樣確定索引是否有碎片？

SQL Server提供了一個資料庫命令――DBCC SHOWCONTIG――來確定一個指定的表或索引是否有碎片。

DBCC SHOWCONTIG

資料庫平臺命令，用來顯示指定的表的資料和索引的碎片資訊。

DBCC SHOWCONTIG 許可權預設授予 sysadmin固定伺服器角色或 db_owner 和 db_ddladmin固定資料庫角色的成員以及表的所有者且不可轉讓。

語法（SQLServer2000）

DBCC SHOWCONTIG 
[ ( { table_name | table_id| view_name | view_id } 
[ , index_name | index_id ] 
) 
] 
[ WITH { ALL_INDEXES 
| FAST [ , ALL_INDEXES ] 
| TABLERESULTS [ , { ALL_INDEXES } ] 
[ , { FAST | ALL_LEVELS } ] 
}
]

語法（SQLServer7.0）

DBCC SHOWCONTIG 
[ ( table_id [,index_id ] 
) 
]

示例：
顯示資料庫裡所有索引的碎片資訊

SET NOCOUNT ON
USE pubs
DBCC SHOWCONTIG WITH ALL_INDEXES

GO

顯示指定表的所有索引的碎片資訊

SET NOCOUNT ONUSE pubs
DBCC SHOWCONTIG (authors) WITH ALL_INDEXES

GO

顯示指定索引的碎片資訊

SET NOCOUNT ON
USE pubs
DBCC SHOWCONTIG (authors,aunmind)

GO

結果集

DBCC SHOWCONTIG將返回掃描頁數、掃描擴充套件盤區數、遍歷索引或表的頁時，DBCC 語句從一個擴充套件盤區移動到其它擴充套件盤區的次數、每個擴充套件盤區的頁數、掃描密度（最佳值是指在一切都連續地連結的情況下，擴充套件盤區更改的理想數目）。

DBCC SHOWCONTIG 正在掃描 'authors' 表...
表: 'authors'（1977058079）；
索引 ID: 1，資料庫 ID: 5
已執行 TABLE 級別的掃描。
- 掃描頁數.....................................: 1
- 掃描擴充套件盤區數...............................: 1
- 擴充套件盤區開關數...............................: 0
- 每個擴充套件盤區上的平均頁數.....................: 1.0
- 掃描密度［最佳值:實際值］....................: 100.00%［1:1］
- 邏輯掃描碎片.................................: 0.00%
- 擴充套件盤區掃描碎片.............................: 0.00%
- 每頁上的平均可用位元組數.......................: 6010.0
- 平均頁密度（完整）...........................: 25.75%

DBCC 執行完畢。如果DBCC 輸出了錯誤資訊，請與系統管理員聯絡。

尋找什麼

掃描頁數：如果你知道行的近似尺寸和表或索引裡的行數，那麼你可以估計出索引裡的頁數。看看掃描頁數，如果明顯比你估計的頁數要高，說明存在內部碎片。

掃描擴充套件盤區數：用掃描頁數除以8,四捨五入到下一個最高值。該值應該和DBCC SHOWCONTIG返回的掃描擴充套件盤區數一致。如果DBCC SHOWCONTIG返回的數高，說明存在外部碎片。碎片的嚴重程度依賴於剛才顯示的值比估計值高多少。

擴充套件盤區開關數：該數應該等於掃描擴充套件盤區數減1。高了則說明有外部碎片。

每個擴充套件盤區上的平均頁數：該數是掃描頁數除以掃描擴充套件盤區數，一般是8。小於8說明有外部碎片。

掃描密度［最佳值:實際值］：DBCC SHOWCONTIG返回最有用的一個百分比。這是擴充套件盤區的最佳值和實際值的比率。該百分比應該儘可能靠近100％。低了則說明有外部碎片。

邏輯掃描碎片：無序頁的百分比。該百分比應該在0％到10％之間，高了則說明有外部碎片。

擴充套件盤區掃描碎片：無序擴充套件盤區在掃描索引葉級頁中所佔的百分比。該百分比應該是0％，高了則說明有外部碎片。

每頁上的平均可用位元組數：所掃描的頁上的平均可用位元組數。越高說明有內部碎片，不過在你用這個數字決定是否有內部碎片之前，應該考慮fill factor（填充因子）。

平均頁密度（完整）：每頁上的平均可用位元組數的百分比的相反數。低的百分比說明有內部碎片。

備註

DBCC SHOWCONTIG實際上僅對那些大表有用。小表顯示的結果根本不符合正常標準，因為他們也許沒有由多於8個的頁面組成。你在檢視小表上執行DBCC SHOWCONTIG的結果時應該忽略一些結果。在處理小表時只需關心擴充套件盤區開關數、邏輯掃描碎片、每頁上的平均可用位元組數、平均頁密度（完整）。

DBCC SHOWCONTIG預設輸出的結果是：掃描頁數、掃描擴充套件盤區數、擴充套件盤區開關數、每個擴充套件盤區上的平均頁數、掃描密度［最佳值:實際值］、邏輯掃描碎片、擴充套件盤區掃描碎片、每頁上的平均可用位元組數、平均頁密度（完整）。可以用FAST和TABLERESULTS選項來控制這個輸出結果。

FAST選項指定執行索引的快速掃描，輸出結果是最小的，該選項不讀索引的葉或資料頁且只返回掃描頁數、掃描擴充套件盤區數、掃描密度［最佳值:實際值］、邏輯掃描碎片。

TABLERESULTS選項將用行集的形式顯示資訊，將返回擴充套件盤區開關數、掃描密度［最佳值:實際值］、邏輯掃描碎片、擴充套件盤區掃描碎片、每頁上的平均可用位元組數、平均頁密度（完整）。

如果既指定FAST選項又指定TABLERESULTS選項，那麼將返回物件名、物件ID、索引名、索引ID，頁數、擴充套件盤區開關數、掃描密度［最佳值:實際值］和邏輯掃描碎片。

ALL_INDEXES選項將顯示指定表和試圖的所有索引的結果，即使指定了一個索引。

ALL_LEVELS選項指定是否為所處理的每個索引的每個級別產生輸出（預設只輸出索引的頁級或表資料級的結果），並且只能與 TABLERESULTS 選項一起使用。

解決碎片問題

一旦你確定表或索引有碎片問題，那麼你有4個選擇去解決那些問題：

• 刪除並重建索引
• 使用DROP_EXISTING子句重建索引
• 執行DBCC DBREINDEX
• 執行DBCC INDEXDEFRAG

儘管每一個技術都能達到你整理索引碎片的最終目的，但各有各的優缺點。

刪除並重建索引

用DROP INDEX和CREATE INDEX或ALTER TABLE來 刪除並重建索引有些缺陷包括在刪除重建期間索引會消失。在索引刪除重建時，對於查詢它不在可用，查詢效能也許會受到明顯的影響，直到重建索引為止。另一個 潛在的缺陷是當都請求索引的時候會引起阻塞，直到重建索引為止。通過其他的處理也能解決阻塞，就是索引被使用的時候不刪除索引。另一個主要的缺陷是在用DROP INDEX和CREATE INDEX重建聚集索引時會引起非聚集索引重建兩次。刪除聚集索引時非聚集索引的行指標會指向資料堆，聚集索引重建時非聚集索引的行指標又會指回聚集索引的行位置。

刪除並重建索引的確有一個好處就是通過重新排序索引頁，使索引頁緊湊並刪除不需要的索引頁來完全重建索引。你也許需要考慮那些內部和外部碎片都很高的情況下才使用，以使那些索引回到它們應該在的位置。

使用DROP_EXISTING子句重建索引

為了避免在重建聚集索引時表上的非聚集索引重建兩次，可以使用帶DROP_EXISTING子句的CREATE INDEX語句。這個子句會保留聚集索引鍵值，以避免非聚集索引重建兩次。和刪除並重建索引一樣，該方法也可能會引起阻塞和索引消失的問題。該方法的另一個缺陷是也強迫你去分別發現和修復表上的每一個索引。

除了和上一個方法一樣的好處之外，該方法的好處是不必重建非聚集索引兩次。這樣可以對那些帶約束的索引提供正確的索引定義以符合約束的要求。

執行DBCC DBREINDEX

DBCC DBREINDEX類似於第二種方法，但它物理地重建索引，允許SQLServer給索引分配新頁來減少內部和外部碎片。DBCC DBREINDEX也能動態的重建帶約束的索引，不象第二種方法。

DBCC DBREINDEX的缺陷是會遇到或引起阻塞問題。DBCC DBREINDEX是作為一個事務來執行的，所以如果在完成之前中斷了，那麼你會丟失所有已經執行過的碎片。

執行DBCC INDEXDEFRAG

DBCC INDEXDEFRAG（在SQLServer2000中可用）按照索引鍵的邏輯順序，通過重新整理索引裡存在的葉頁來減少外部碎片，通過壓縮索引頁裡的行然後刪除那些由此產生的不需要的頁來減少內部碎片。它不會遇到阻塞問題但它的結果沒有其他幾個方法徹底。這是因為DBCC INDEXDEFRAG跳過了鎖定的頁且不使用任何新頁來重新排序索引。如果索引的碎片數量大的話你也許會發現DBCC INDEXDEFRAG比重建索引花費的時間更長。DBCC INDEXDEFRAG比其他方法的確有好處的是在其他過程訪問索引時也能進行碎片整理，不會引起其他方法的阻塞問題。

---

總結

如何在SQLServer中處理億萬級別的資料(歷史資料)，可以按以下方面進行：

1. 去掉表的所有索引
2. 用BulkCopy進行插入
3. 分表或者分割槽，減少每個表的資料總量
4. 在某個表完全寫完之後再建立索引
5. 正確的指定索引欄位
6. 把需要用到的欄位放到包含索引中(在返回的索引中就包含了一切)

---

SQL Server提供了一些函式返回連線值(這裡可不是當前連線數喲!),個人覺得,很容易產生誤解.

系統變數

@@CONNECTIONS 返回自上次啟動 Microsoft SQL Server 以來連線或試圖連線的次數。

@@MAX_CONNECTIONS 返回 Microsoft SQL Server 上允許的同時使用者連線的最大數。返回的數不必為當前配置的數值。

系統儲存過程

SP_WHO

提供關於當前 Microsoft SQL Server 使用者和程式的資訊。可以篩選返回的資訊，以便只返回那些不是空閒的程式。

列出所有活動的使用者:SP_WHO ‘active’

列出某個特定使用者的資訊:SP_WHO ‘sa’

系統表

Sysprocesses

sysprocesses 表中儲存關於執行在 Microsoft SQL Server 上的程式的資訊。這些程式可以是客戶端程式或系統程式。sysprocesses 只儲存在 master 資料庫中。

Sysperfinfo

包括一個 Microsoft SQL Server 表示法的內部效能計數器，可通過 Windows NT 效能監視器顯示.

有人提議說為了獲取SQL Server的當前連線數:使用如下SQL:

SELECT COUNT(*) AS CONNECTIONS FROM master..sysprocesses

個人認為不對,看看.sysprocesses的login_time列就可看出.

另外一個方面是程式不能和連線相提並論,他們是一對一的關係嗎,也就是說一個程式就是一個連線?一個連線應該有多個程式的,所以連線和程式之間的關係應該是1:n的.

因為sysprocesses列出的程式包含了系統程式和使用者程式,為了得到使用者連線,可以使用如下SQL:

SELECT cntr_value AS User_Connections FROM master..sysperfinfo as p
WHERE p.object_name = 'SQLServer:General Statistics' And p.counter_name = 'User Connections'

個人還是認為不對,因為它是一個計數器,可能會累加的.

還有一種方案是利用如下SQL:

select connectnum=count(distinct net_address)-1 from master..sysprocesses

理由是net_address是訪問者機器的網路卡值,這個總該是唯一的吧.但是看起來得到的是所有時間內的連線數.

---

SQL語句設定最大連線數

下面的 T-SQL 語句可以配置 SQL Server 允許的併發使用者連線的最大數目。

exec sp_configure ‘show advanced options’, 1
exec sp_configure ‘user connections’, 100

第一句用以表示顯示 sp_configure 系統儲存過程高階選項，使用 user connections 時，要求 show advanced options 值為 1。

第二句配置最大連線數為 100，0 表示不限制，但不表示無限，後面將談談。

也可以在企業管理器中配置，在企業管理器中，可以在例項上點右鍵->“屬性”->“連線”裡面更改。

需要重新啟動 SQL Server，該值才會生效。

---

@@max_connections

select @@max_connections

它總是返回 32767，它並不是指上面設定的 user connections，實際上它表示 user connections 最大可設定為多少。由於它的最大值是 32767，那麼 user connections 為 0 時，最大連線數也就是 32767 了，並不是無限。

預設情況下 user connections 值是 0，也就是說預設情況下 SQL Server 的最大連線數是 32767。

---

阿里雲RDS資料庫 建立索引 來優化

CREATE INDEX IDX_STATUS ON [xxx].[dbo].[Orders](STATUS) WITH(ONLINE=ON)

CREATE INDEX IDX_NU ON [xxx].[dbo].[DeliveryStatus](NU) WITH(ONLINE=ON)

CREATE INDEX IDX_DID_LDTIME ON [xxx].[dbo].[LD](DID,LDTIME) WITH(ONLINE=ON)
CREATE INDEX IDX_NU ON [xxx].[dbo].[DeliveryStatus](NU) WITH(ONLINE=ON)

CREATE INDEX IDX_SKEY ON [XXX].[dbo].[SysManager](SKEY) WITH(ONLINE=ON)

CREATE INDEX IDX_STATUS_ANUM ON [XXX].[dbo].[Orders](STATUS,ANUM) WITH(ONLINE=ON)
CREATE INDEX IDX_ANUM ON [XXX].[dbo].[Inventory](ANUM) WITH(ONLINE=ON)

CREATE INDEX IDX_DI_LD_CO ON [XXX].[dbo].[LD](DID,LDTIME) INCLUDE (CONTEXT) WITH(ONLINE=ON)

CREATE INDEX IDX_MID ON [XXX].[dbo].[Orders](MID) WITH(ONLINE=ON)

CREATE INDEX IDX_MID_ORDERSDT ON [XXX].[dbo].[Orders](MID,ORDERSDT) WITH(ONLINE=ON)

CREATE INDEX IDX_AREAKEY ON [xxx].[dbo].[AreaC](AREAKEY) WITH(ONLINE=ON)

CREATE INDEX IDX_ANUM_MID ON [XXX].[dbo].[Orders](ANUM,MID) WITH(ONLINE=ON)

CREATE INDEX IDX_ANUMCSTOCK ON [XXX].[dbo].[Inventory](ANUM,CURRENTSTOCK) WITH(ONLINE=ON)

CREATE INDEX IDX_ICCO ON [XXX].[dbo].[Coupon](IS_DELETE,IS_USED,GROUPID,COUPONCODE,COUPONID) WITH(ONLINE=ON)

CREATE INDEX IDX_ONUM ON [XXX].[dbo].[Coupon](ONUM) WITH(ONLINE=ON)

CREATE INDEX IDX_ANUM ON [XXX].[dbo].[Specification](ANUM) WITH(ONLINE=ON)

CREATE INDEX IDX_NCP ON [XXX].[dbo].[AccessLog](ANUM,ACCESSDT) INCLUDE (OPENID) WITH(ONLINE=ON)

---

exists 和 in的區別和效能比較

in和exists
in 是把外表和內表作hash 連線，而exists是對外表作loop迴圈，每次loop迴圈再對內表進行查詢。
一直以來認為exists比in效率高的說法是不準確的。
如果查詢的兩個表大小相當，那麼用in和exists差別不大。
如果兩個表中一個較小，一個是大表，則子查詢表大的用exists，子查詢表小的用in：

例如：表A（小表），表B（大表）
1：

select * from A where cc in (select cc from B)

效率低，用到了A表上cc列的索引；

select * from A where exists(select cc from B where cc=A.cc)

效率高，用到了B表上cc列的索引。
相反的
2：

select * from B where cc in (select cc from A)

效率高，用到了B表上cc列的索引；

select * from B where exists (select cc from A where cc=B.cc)

效率低，用到了A表上cc列的索引。

not in和not exists

如果查詢語句使用了not in 那麼內外表都進行全表掃描，沒有用到索引；而not extsts 的子查詢依然能用到表上的索引。所以無論那個表大，用not exists都比not in要快。

in 與 = 的區別

select name from student where name in ('zhang','wang','li','zhao');

與

select name from student where name ='zhang' or name='li' or name='wang' or name='zhao'

的結果是相同的。