擦亮自己的眼睛去看SQL Server之簡(jiǎn)單選取
這篇文章主要和大家討論幾乎所有人都熟悉，但不少人又陌生的一條select語(yǔ)句。不知道大家有沒有想過到底是什么東西讓SQLServer能理解我們寫的select。這中間到底發(fā)生了什么，是不是有過沖動(dòng)想去了解。至少我曾經(jīng)沖動(dòng)想去了解，但當(dāng)時(shí)主要在研究CLR以及webform相關(guān)知識(shí)。后來主要精力放在研究SQLserver內(nèi)部機(jī)制，今天就給大家介紹下這條語(yǔ)句。
一、范例數(shù)據(jù)庫(kù)腳本
- create database Test
- go
- alter database Test set recovery simple
- go
- use Test
- go
- create table Test
- (
- ID int identity(1,1) primary key,
- [Name] varchar(64) not null default '',
- CreatedTime datetime not null default getdate()
- )
- insert into Test([name]) values('xiaojun')
這個(gè)腳本就不介紹了，很簡(jiǎn)單。
二、語(yǔ)句分析
- select * from Test
簡(jiǎn)單吧，本來嘛標(biāo)題就是之簡(jiǎn)單語(yǔ)句。下面開始分析這條語(yǔ)句吧，假設(shè)讀者已經(jīng)知道了SQLServer整體架構(gòu)或者已經(jīng)閱讀過這個(gè)系列第一篇文章。當(dāng)這條語(yǔ)句被可靠的傳遞到關(guān)系引擎中的命令分析器，接下來就發(fā)生了：
分析：
從語(yǔ)法庫(kù)中檢查T-SQL進(jìn)行基本的語(yǔ)法檢查。如果語(yǔ)法出錯(cuò)了，那整個(gè)語(yǔ)句就立即停止，提示用戶語(yǔ)法出錯(cuò)，哪出錯(cuò)。比如錯(cuò)誤使用的關(guān)鍵字、列、表名等。如果語(yǔ)法沒有出錯(cuò)，就會(huì)生成一個(gè)分析樹傳遞給下一個(gè)步驟。
綁定：
1、名字解析：檢查所有的對(duì)象在用戶的安全上下文中存在并可見。這個(gè)步驟很好理解主要是數(shù)據(jù)庫(kù)每個(gè)對(duì)象都有權(quán)限。如果登錄的賬號(hào)沒有相應(yīng)權(quán)限，就結(jié)束這個(gè)步驟。
2、類型推導(dǎo)：確定解析樹中每個(gè)節(jié)點(diǎn)的最終類型。這個(gè)步驟主要是補(bǔ)充分析分析步驟中的分析樹，確定其最終的類型。不知道大家可想過為什么要到這一步才確定。為什么不在分析中確定呢?主要原因是效率，類型推導(dǎo)會(huì)消耗資源，沒有必要在沒有確定用戶對(duì)每個(gè)對(duì)象有權(quán)限的情況下確定。那為什么不直接先確定用戶對(duì)每個(gè)對(duì)象有權(quán)限再做分析呢。那是因?yàn)闆]做分析的時(shí)候，系統(tǒng)無法知道具體有哪些對(duì)象。我又要說了，SQLServer的設(shè)計(jì)真的可以說是很精致的，連這樣的細(xì)節(jié)和資源消耗都考慮了。值得我們學(xué)習(xí)哦。
3、聚合綁定：確定哪些地方可以進(jìn)行聚合。這個(gè)步驟主要和SQL中是否有聚合操作有關(guān)系。
4、組合綁定：將聚合綁定到正確的選擇列表中。這個(gè)步驟是把聚合操作與需要聚合的列綁定對(duì)應(yīng)起來。
這兩步操作主要是由命令分析器完成，它最終得到分析樹，傳遞給SQLServer引擎中最復(fù)雜最優(yōu)技術(shù)含量的組件，沒有之一，查詢優(yōu)化器。查詢優(yōu)化器功能概況起來很簡(jiǎn)單，就是優(yōu)化SQL。具體優(yōu)化模型如下：
優(yōu)化：
1、檢查執(zhí)行計(jì)劃緩存中是有沒對(duì)應(yīng)的執(zhí)行計(jì)劃。 如果沒有，繼續(xù)下面操作。如果有則使用緩存。SQL Server是根據(jù)SQL的哈希值比較的。想想為什么?
2、預(yù)優(yōu)化：查詢語(yǔ)句很簡(jiǎn)單，開銷足夠小，直接結(jié)束優(yōu)化。比如沒有聯(lián)接的基本查詢。屬于零開銷，稱為普通計(jì)劃。比如我們這的select語(yǔ)句預(yù)優(yōu)化就搞定了。
3、階段0：檢驗(yàn)基本規(guī)則，以及散列和嵌套聯(lián)接選項(xiàng)。這個(gè)計(jì)劃的開銷是否小于0.2，如果是，結(jié)束優(yōu)化。這里的0.2以及下面的1.0，這是SQLServer內(nèi)部的開銷值，僅供SQLServer系統(tǒng)內(nèi)部使用。
4、階段1：檢驗(yàn)更多的規(guī)則，以及變換聯(lián)接的順序。如果開銷最小的計(jì)劃的開銷小于1.0，如果是，結(jié)束優(yōu)化。如果不是，繼續(xù)判斷。如果maxdop>0且這個(gè)系統(tǒng)是SMP系統(tǒng)，以及最小開銷大于并行化的開銷臨界值，則使用并行計(jì)劃。比較并行計(jì)劃的開銷和最好的串行計(jì)劃的開銷，將開銷更小的計(jì)劃傳遞給階段2。
并行計(jì)劃是指優(yōu)化器根據(jù)情況，將恰當(dāng)?shù)牟僮鞣鸱譃閿?shù)個(gè)可以同步運(yùn)行的進(jìn)程在不同的處理器上運(yùn)行，需要多核支持。對(duì)于大數(shù)據(jù)量查詢可以提高效率。
maxdop是什么呢?這是SQLServer的一個(gè)高級(jí)配置。我們可以通過sp_configure查看。如下圖：
可能你直接運(yùn)行sp_configure，看不到這個(gè)配置。主要是這是個(gè)高級(jí)配置項(xiàng)，默認(rèn)不開啟。你運(yùn)行以下語(yǔ)句后再運(yùn)行sp_configure就能看到以上圖中信息。
- sp_configure 'show advanced options',1
- reconfigure
這里面的高級(jí)配置值，默認(rèn)情況下不需要修改。必須你對(duì)SQLServer有較深理解，以及在修改前做好修改對(duì)整個(gè)SQLServer的影響的評(píng)估后才去改動(dòng)。說說這個(gè)maxdop吧，這是說SQLServer在執(zhí)行并行計(jì)劃的最大處理器數(shù)目，0代表由SQLServer決定。否則就是按照用戶指定的最大并行度。因此上面的maxdop>0且這個(gè)系統(tǒng)是SMP系統(tǒng)的意思就是當(dāng)用戶修改了這個(gè)配置項(xiàng)，那么執(zhí)行器在評(píng)估開銷時(shí)要優(yōu)先考慮用戶修改過的最大并行度小的系統(tǒng)開銷。SMP系統(tǒng)是對(duì)稱處理器體系結(jié)構(gòu)，基于Intel處理器的服務(wù)器基本上都是SMP系統(tǒng)。在此不展開說。
不知道大家注意到?jīng)]，我上面的圖查詢優(yōu)化器輸出的是較好的執(zhí)行計(jì)劃。想想為什么? 如果想查看優(yōu)化過程的內(nèi)部情況，可以使用一下sys.dm_exec_query_optimizer_info動(dòng)態(tài)管理視圖。比如，下面演示證明我們這條select語(yǔ)句是屬于普通計(jì)劃。按照如下操作執(zhí)行：
- dbcc freeproccache --清空?qǐng)?zhí)行計(jì)劃緩存
- select * from sys.dm_exec_query_optimizer_info where counter in('optimizations','trivial plan','search 0','search 1','search 2')
結(jié)果如下：
繼續(xù)：
- select * from Test
- select * from sys.dm_exec_query_optimizer_info where counter in('optimizations','trivial plan','search 0','search 1','search 2')
結(jié)果如下：
發(fā)現(xiàn)了吧，trival plan類型計(jì)數(shù)+1，說明優(yōu)化器對(duì)select * from Test進(jìn)行優(yōu)化時(shí)是普通計(jì)劃。
5、階段2：檢驗(yàn)所有可能的計(jì)劃，并且選擇達(dá)到檢驗(yàn)的時(shí)間限制時(shí)開銷最小的計(jì)劃。
執(zhí)行：這個(gè)計(jì)劃被調(diào)度執(zhí)行，這個(gè)涉及到SQLOS不在本篇文章討論范圍，只要先簡(jiǎn)單理解為交給CPU執(zhí)行。
三、結(jié)尾
其中這個(gè)語(yǔ)句還有很多地方可以分析，比較在這條語(yǔ)句執(zhí)行時(shí)，加鎖以及如何被調(diào)度執(zhí)行。這些還是希望在放在后面的章節(jié)中解釋。這篇文章主要是談到了SQLServer如何對(duì)SQL進(jìn)行解析優(yōu)化的。仔細(xì)研究，你會(huì)發(fā)現(xiàn)SQLServer的查詢優(yōu)化器做了很多優(yōu)化措施當(dāng)然其他數(shù)據(jù)庫(kù)也類似的組件。其實(shí)你會(huì)發(fā)現(xiàn)這些對(duì)于我們大部分開發(fā)人員都是屏蔽的。屏蔽是一種進(jìn)步，java、.net的垃圾回收屏蔽了開發(fā)人員對(duì)內(nèi)存的管理，那SQLServer在這里屏蔽了什么呢，這需要研究數(shù)據(jù)庫(kù)歷史。只有研究歷史，才能站在一個(gè)較高的角度知道現(xiàn)在的數(shù)據(jù)庫(kù)為什么是現(xiàn)在的樣子。
今天分析就到此結(jié)束，文中如有描述不當(dāng)?shù)牡胤?，歡迎指出。共同進(jìn)步才是硬道理。
【編輯推薦】
