? ? ? ?最近面試當(dāng)中被問到很多SQL的問題，有些正好記得概念，有些對概念比較模糊，因此我重新復(fù)習(xí)了一下sql的知識，同時(shí)在這里做一些記錄權(quán)當(dāng)加深記憶。
1.什么是執(zhí)行計(jì)劃么？如何在語句執(zhí)行的同時(shí)，看到執(zhí)行計(jì)劃？
A：SQL Server 在執(zhí)行語句的時(shí)候右鍵附加實(shí)際執(zhí)行計(jì)劃；MySQL 在語句前加?EXPLAIN 或者Navicat 添加解釋
2.索引有幾種？有什么區(qū)別么？
A：索引分為聚集索引和非聚集索引，非聚集索引和聚集索引的區(qū)別在于， 通過聚集索引可以查到需要查找的數(shù)據(jù)， 而通過非聚集索引可以查到記錄對應(yīng)的主鍵值 ， 再使用主鍵的值通過聚集索引查找到需要的數(shù)據(jù)。（一般來說聚集索引就是主鍵）
還有組合索引，組合索引很有意思，舉個(gè)例子：有一個(gè)組合索引是index_age_name，select name from people where age = 22，會(huì)先查詢age這個(gè)平衡樹，最終查到的節(jié)點(diǎn)包含主鍵id和name，此時(shí)可以直接返回name，不需要用id從聚集索引再次查找了。
索引以平衡樹的方式存儲于磁盤上。
3.有索引和沒索引，語句執(zhí)行的區(qū)別么？
A：沒有索引，查詢語句會(huì)逐個(gè)比對磁盤存儲上的表數(shù)據(jù)，有聚集索引會(huì)從平衡樹進(jìn)行查找，數(shù)據(jù)量大的情況下比對情況大幅減少。
另外在插入新的數(shù)據(jù)時(shí)，因?yàn)樾枰貥?gòu)平衡樹的結(jié)構(gòu)，所以有索引會(huì)讓插入操作變慢。
枚舉值不建議加索引的原因是，查詢優(yōu)化的好處小于插入時(shí)影響的壞處。
4.什么是統(tǒng)計(jì)信息么？
A：數(shù)據(jù)庫的統(tǒng)計(jì)信息一般包括表的數(shù)據(jù)行、頁，主鍵的長度，取值范圍等，語句的最優(yōu)執(zhí)行計(jì)劃取決于統(tǒng)計(jì)信息。
5.什么是臨時(shí)表，表變量，CTE？有什么區(qū)別？
臨時(shí)表包括：以#開頭的局部臨時(shí)表，以##開頭的全局臨時(shí)表，都存儲在tempdb中，局部臨時(shí)表會(huì)在連接斷開后刪除，全局臨時(shí)表會(huì)在所有的連接不再引用后刪除。
表變量是 declare table @{*}，儲存在tempdb中，可以有主鍵不能加索引，在一次語句執(zhí)行完成后自動(dòng)清除，適用于小數(shù)據(jù)的情況。另外表變量不受事務(wù)影響。
CTE是 with as 寫法的，存儲于內(nèi)存中，實(shí)際上運(yùn)行過程中還是一段sql語句，一般適用于遞歸。
6.什么是事務(wù)？什么是隔離級別？
A：事務(wù)是一個(gè)工作單元，包含查詢／修改數(shù)據(jù)以及修改數(shù)據(jù)定義的多個(gè)活動(dòng)的組合，事務(wù)具有原子性（要么成功要么失敗）、一致性（事務(wù)未提交數(shù)據(jù)無變化）、隔離性（事務(wù)互不干擾）、持久性（事務(wù)完成后數(shù)據(jù)不會(huì)回滾）
事務(wù)的隔離級別控制并發(fā)用戶的讀取和寫入的行為，隔離級別主要分為兩種類型：悲觀并發(fā)控制和樂觀并發(fā)控制
例： SET TRANSACTION ISOLATION LEVEL {隔離級別}
隔離級別分別如下：
READ UNCOMMITTED：最低隔離級別，無需鎖，并發(fā)性好，但數(shù)據(jù)容易不一致
READ COMMITTED：會(huì)話默認(rèn)的隔離級別，語句查詢會(huì)帶有共享鎖，當(dāng)事務(wù)進(jìn)行時(shí)會(huì)等待事務(wù)中對該條數(shù)據(jù)更新后才能讀到數(shù)據(jù)。
REPEATABLE READ：上一級別的提升，會(huì)在事務(wù)完成提交后進(jìn)行讀取。
SERIALIZABLE:再次提升隔離，犧牲并行效率。
SNAPSHOT：不加鎖，直接讀取快照，以事務(wù)為單位共享快照。
READ COMMITTED SNAPSHOT：語句為單位的快照讀取，同一事務(wù)中每個(gè)語句返回最新快照的結(jié)果。
7.什么是邏輯讀，什么是物理讀，什么是預(yù)讀么？怎么查看你執(zhí)行消耗的IO資源？
A：邏輯讀，預(yù)讀，和物理讀的單位是頁，一頁為8k，數(shù)據(jù)庫以平衡樹方式存儲頁。
一次查詢的基本過程是：SQL SERVER會(huì)走第一步，分別為生成執(zhí)行計(jì)劃(占用CPU和內(nèi)存資源),同步地用估計(jì)的數(shù)據(jù)去磁盤中取得需要取的數(shù)據(jù)(占用IO資源，這就是預(yù)讀),注意，兩個(gè)第一步是并行的，SQL SERVER通過這種方式來提高查詢性能.
然后查詢計(jì)劃生成好了以后去緩存讀取數(shù)據(jù).當(dāng)發(fā)現(xiàn)緩存缺少所需要的數(shù)據(jù)后讓緩存再次去讀硬盤（物理讀）
最后從緩存中取出所有數(shù)據(jù)（邏輯讀）
8.什么是等待？怎么查看你運(yùn)行的語句是否在等待？等待反應(yīng)出的問題是什么？
A:語句查詢過程中需要請求鎖會(huì)進(jìn)入等待，或語句查詢時(shí)間過長會(huì)進(jìn)入等待。
SELECT request_session_id,resource_type,resource_database_id,DB_NAME(resource_database_id) AS dbname,resource_associated_entity_id,request_mode,request_status FROM sys.dm_tran_locks
可以查詢會(huì)話的狀態(tài)，如果會(huì)話需要等待鎖資源釋放會(huì)進(jìn)入wait狀態(tài)。
等待說明數(shù)據(jù)庫需要性能優(yōu)化，更合理地安排鎖及事務(wù)隔離級別。
9.SQL的鎖機(jī)制么？
A：MSSQL提供以下六種鎖：
共享 (S) 用于不更改或不更新數(shù)據(jù)的操作（只讀操作）
更新 (U) 用于可更新的資源中。防止當(dāng)多個(gè)會(huì)話在讀取、鎖定以及隨后可能進(jìn)行的資源更新時(shí)發(fā)生常見形式的死鎖。
排它 (X) 用于數(shù)據(jù)修改操作，例如 INSERT、UPDATE 或 DELETE。確保不會(huì)同時(shí)同一資源進(jìn)行多重更新。
意向鎖 用于建立鎖的層次結(jié)構(gòu)。意向鎖的類型為：意向共享 (IS)、意向排它 (IX) 以及與意向排它共享 (SIX)。
架構(gòu)鎖 在執(zhí)行依賴于表架構(gòu)的操作時(shí)使用。架構(gòu)鎖的類型為：架構(gòu)修改 (Sch-M) 和架構(gòu)穩(wěn)定性 (Sch-S)。
大容量更新 (BU) 向表中大容量復(fù)制數(shù)據(jù)并指定了 TABLOCK 提示時(shí)使用。
10.什么是TempDB么？什么樣的語句會(huì)使用TempDB？
A：Tempdb系統(tǒng)數(shù)據(jù)庫是一個(gè)全局資源，可供連接到 SQL Server 實(shí)例的所有用戶使用，并可用于保存下列各項(xiàng)：
顯式創(chuàng)建的臨時(shí)用戶對象，例如全局或局部臨時(shí)表、臨時(shí)存儲過程、表變量或游標(biāo)。
SQL Server 數(shù)據(jù)庫引擎創(chuàng)建的內(nèi)部對象，例如，用于存儲假脫機(jī)或排序的中間結(jié)果的工作表。
由使用已提交讀（使用行版本控制隔離或快照隔離事務(wù)）的數(shù)據(jù)庫中數(shù)據(jù)修改事務(wù)生成的行版本。
由數(shù)據(jù)修改事務(wù)為實(shí)現(xiàn)聯(lián)機(jī)索引操作、多個(gè)活動(dòng)的結(jié)果集 (MARS) 以及 AFTER 觸發(fā)器等功能而生成的行版本。
11.SQL編譯與重編譯？
A：當(dāng)SQLSERVER收到任何一個(gè)指令，包括查詢（query）、批處理（batch）、存儲過程、觸發(fā)器（trigger）、預(yù)編譯指令（prepared statement）和動(dòng)態(tài)SQL語句（dynamic SQL Statement）要完成語法解釋、語句解釋，然后再進(jìn)行“編譯（compile）”，生成能夠運(yùn)行的“執(zhí)行計(jì)劃（execution plan）”。
在編譯的過程中，SQLSERVER會(huì)根據(jù)所涉及的對象的架構(gòu)（schema）、統(tǒng)計(jì)信息以及指令的具體內(nèi)容，估算可能的執(zhí)行計(jì)劃，以及他們的成本（cost），最后選擇一個(gè)SQLSERVER認(rèn)為成本最低的執(zhí)行計(jì)劃來執(zhí)行。
執(zhí)行計(jì)劃生成之后，SQLSERVER通常會(huì)把他們緩存在內(nèi)存里，術(shù)語統(tǒng)稱他們叫“plan cache”以后同樣的語句執(zhí)行，SQLSERVER就可以使用同樣的執(zhí)行計(jì)劃，而無須再做一次編譯。
這種行為叫“重用（reuse）或者叫重用執(zhí)行計(jì)劃”。但是有時(shí)候，哪怕是一模一樣的語句，SQL下次執(zhí)行還是要再做一次編譯。
這種行為叫“重編譯（recompile）”。執(zhí)行計(jì)劃的編譯和重編譯都是要消耗資源的。
如果執(zhí)行計(jì)劃能夠重用，那么SQLSERVER就不需要再執(zhí)行上面的過程，加快執(zhí)行指令的速度，很多語句調(diào)優(yōu)的文章里提到數(shù)據(jù)庫重用執(zhí)行計(jì)劃就是指這個(gè)意思
