當(dāng)前位置:首頁 > 芯聞號 > 充電吧
[導(dǎo)讀]故事開篇:你和你的團隊經(jīng)過不懈努力,終于使網(wǎng)站成功上線,剛開始時,注冊用戶較少,網(wǎng)站性能表現(xiàn)不錯,但隨著注冊用戶的增多,訪問速度開始變慢,一些用戶開始發(fā)來郵件表示抗議,事情變得越來越糟,為了留住用戶,

故事開篇:你和你的團隊經(jīng)過不懈努力,終于使網(wǎng)站成功上線,剛開始時,注冊用戶較少,網(wǎng)站性能表現(xiàn)不錯,但隨著注冊用戶的增多,訪問速度開始變慢,一些用戶開始發(fā)來郵件表示抗議,事情變得越來越糟,為了留住用戶,你開始著手調(diào)查訪問變慢的原因。

  經(jīng)過緊張的調(diào)查,你發(fā)現(xiàn)問題出在數(shù)據(jù)庫上,當(dāng)應(yīng)用程序嘗試訪問/更新數(shù)據(jù)時,數(shù)據(jù)庫執(zhí)行得相當(dāng)慢,再次深入調(diào)查數(shù)據(jù)庫后,你發(fā)現(xiàn)數(shù)據(jù)庫表增長得很大,有些表甚至有上千萬行數(shù)據(jù),測試團隊開始在生產(chǎn)數(shù)據(jù)庫上測試,發(fā)現(xiàn)訂單提交過程需要花5分鐘時間,但在網(wǎng)站上線前的測試中,提交一次訂單只需要2/3秒。

  類似這種故事在世界各個角落每天都會上演,幾乎每個開發(fā)人員在其開發(fā)生涯中都會遇到這種事情,我也曾多次遇到這種情況,因此我希望將我解決這種問題的經(jīng)驗和大家分享。

  如果你正身處這種項目,逃避不是辦法,只有勇敢地去面對現(xiàn)實。首先,我認為你的應(yīng)用程序中一定沒有寫數(shù)據(jù)訪問程序,我將在這個系列的文章中介紹如何編寫最佳的數(shù)據(jù)訪問程序,以及如何優(yōu)化現(xiàn)有的數(shù)據(jù)訪問程序。

  范圍

  在正式開始之前,有必要澄清一下本系列文章的寫作邊界,我想談的是“事務(wù)性(OLTP)SQL Server數(shù)據(jù)庫中的數(shù)據(jù)訪問性能優(yōu)化”,但文中介紹的這些技巧也可以用于其它數(shù)據(jù)庫平臺。

  同時,我介紹的這些技巧主要是面向程序開發(fā)人員的,雖然DBA也是優(yōu)化數(shù)據(jù)庫的一支主要力量,但DBA使用的優(yōu)化方法不在我的討論范圍之內(nèi)。

  當(dāng)一個基于數(shù)據(jù)庫的應(yīng)用程序運行起來很慢時,90%的可能都是由于數(shù)據(jù)訪問程序的問題,要么是沒有優(yōu)化,要么是沒有按最佳方法編寫代碼,因此你需要審查和優(yōu)化你的數(shù)據(jù)訪問/處理程序。

  我將會談到10個步驟來優(yōu)化數(shù)據(jù)訪問程序,先從最基本的索引說起吧!

  第一步:應(yīng)用正確的索引

  我之所以先從索引談起是因為采用正確的索引會使生產(chǎn)系統(tǒng)的性能得到質(zhì)的提升,另一個原因是創(chuàng)建或修改索引是在數(shù)據(jù)庫上進行的,不會涉及到修改程序,并可以立即見到成效。

  我們還是溫習(xí)一下索引的基礎(chǔ)知識吧,我相信你已經(jīng)知道什么是索引了,但我見到很多人都還不是很明白,我先給大家將一個故事吧。

  很久以前,在一個古城的的大圖書館中珍藏有成千上萬本書籍,但書架上的書沒有按任何順序擺放,因此每當(dāng)有人詢問某本書時,圖書管理員只有挨個尋找,每一次都要花費大量的時間。

  [這就好比數(shù)據(jù)表沒有主鍵一樣,搜索表中的數(shù)據(jù)時,數(shù)據(jù)庫引擎必須進行全表掃描,效率極其低下。]

  更糟的是圖書館的圖書越來越多,圖書管理員的工作變得異常痛苦,有一天來了一個聰明的小伙子,他看到圖書管理員的痛苦工作后,想出了一個辦法,他建議將每本書都編上號,然后按編號放到書架上,如果有人指定了圖書編號,那么圖書管理員很快就可以找到它的位置了。

  [給圖書編號就象給表創(chuàng)建主鍵一樣,創(chuàng)建主鍵時,會創(chuàng)建聚集索引樹,表中的所有行會在文件系統(tǒng)上根據(jù)主鍵值進行物理排序,當(dāng)查詢表中任一行時,數(shù)據(jù)庫首先使用聚集索引樹找到對應(yīng)的數(shù)據(jù)頁(就象首先找到書架一樣),然后在數(shù)據(jù)頁中根據(jù)主鍵鍵值找到目標行(就象找到書架上的書一樣)。]

  于是圖書管理員開始給圖書編號,然后根據(jù)編號將書放到書架上,為此他花了整整一天時間,但最后經(jīng)過測試,他發(fā)現(xiàn)找書的效率大大提高了。

  [在一個表上只能創(chuàng)建一個聚集索引,就象書只能按一種規(guī)則擺放一樣。]

  但問題并未完全解決,因為很多人記不住書的編號,只記得書的名字,圖書管理員無賴又只有掃描所有的圖書編號挨個尋找,但這次他只花了20分鐘,以前未給圖書編號時要花2-3小時,但與根據(jù)圖書編號查找圖書相比,時間還是太長了,因此他向那個聰明的小伙子求助。

  [這就好像你給Product表增加了主鍵ProductID,但除此之外沒有建立其它索引,當(dāng)使用Product Name進行檢索時,數(shù)據(jù)庫引擎又只要進行全表掃描,逐個尋找了。]

  聰明的小伙告訴圖書管理員,之前已經(jīng)創(chuàng)建好了圖書編號,現(xiàn)在只需要再創(chuàng)建一個索引或目錄,將圖書名稱和對應(yīng)的編號一起存儲起來,但這一次是按圖書名稱進行排序,如果有人想找“Database Management System”一書,你只需要跳到“D”開頭的目錄,然后按照編號就可以找到圖書了。

  于是圖書管理員興奮地花了幾個小時創(chuàng)建了一個“圖書名稱”目錄,經(jīng)過測試,現(xiàn)在找一本書的時間縮短到1分鐘了(其中30秒用于從“圖書名稱”目錄中查找編號,另外根據(jù)編號查找圖書用了30秒)。

  圖書管理員開始了新的思考,讀者可能還會根據(jù)圖書的其它屬性來找書,如作者,于是他用同樣的辦法為作者也創(chuàng)建了目錄,現(xiàn)在可以根據(jù)圖書編號,書名和作者在1分鐘內(nèi)查找任何圖書了,圖書管理員的工作變得輕松了,故事也到此結(jié)束。

  到此,我相信你已經(jīng)完全理解了索引的真正含義。假設(shè)我們有一個Products表,創(chuàng)建了一個聚集索引(根據(jù)表的主鍵自動創(chuàng)建的),我們還需要在ProductName列上創(chuàng)建一個非聚集索引,創(chuàng)建非聚集索引時,數(shù)據(jù)庫引擎會為非聚集索引自動創(chuàng)建一個索引樹(就象故事中的“圖書名稱”目錄一樣),產(chǎn)品名稱會存儲在索引頁中,每個索引頁包括一定范圍的產(chǎn)品名稱和它們對應(yīng)的主鍵鍵值,當(dāng)使用產(chǎn)品名稱進行檢索時,數(shù)據(jù)庫引擎首先會根據(jù)產(chǎn)品名稱查找非聚集索引樹查出主鍵鍵值,然后使用主鍵鍵值查找聚集索引樹找到最終的產(chǎn)品。

  下圖顯示了一個索引樹的結(jié)構(gòu)

  圖 1 索引樹結(jié)構(gòu)

  它叫做B+樹(或平衡樹),中間節(jié)點包含值的范圍,指引SQL引擎應(yīng)該在哪里去查找特定的索引值,葉子節(jié)點包含真正的索引值,如果這是一個聚集索引樹,葉子節(jié)點就是物理數(shù)據(jù)頁,如果這是一個非聚集索引樹,葉子節(jié)點包含索引值和聚集索引鍵(數(shù)據(jù)庫引擎使用它在聚集索引樹中查找對應(yīng)的行)。

  通常,在索引樹中查找目標值,然后跳到真實的行,這個過程是花不了什么時間的,因此索引一般會提高數(shù)據(jù)檢索速度。下面的步驟將有助于你正確應(yīng)用索引。

  確保每個表都有主鍵

  這樣可以確保每個表都有聚集索引(表在磁盤上的物理存儲是按照主鍵順序排列的),使用主鍵檢索表中的數(shù)據(jù),或在主鍵字段上進行排序,或在where子句中指定任意范圍的主鍵鍵值時,其速度都是非??斓摹?/p>

  在下面這些列上創(chuàng)建非聚集索引:

  1)搜索時經(jīng)常使用到的;

  2)用于連接其它表的;

  3)用于外鍵字段的;

  4)高選中性的;

  5)ORDER BY子句使用到的;

  6)XML類型。

  下面是一個創(chuàng)建索引的例子: 

CREATEINDEX

  NCLIX_OrderDetails_ProductIDON

  dbo.OrderDetails(ProductID)

  也可以使用SQL Server管理工作臺在表上創(chuàng)建索引,如圖2所示。

  圖 2 使用SQL Server管理工作臺創(chuàng)建索引


  第二步:創(chuàng)建適當(dāng)?shù)母采w索引

  假設(shè)你在Sales表(SelesID,SalesDate,SalesPersonID,ProductID,Qty)的外鍵列(ProductID)上創(chuàng)建了一個索引,假設(shè)ProductID列是一個高選中性列,那么任何在where子句中使用索引列(ProductID)的select查詢都會更快,如果在外鍵上沒有創(chuàng)建索引,將會發(fā)生全部掃描,但還有辦法可以進一步提升查詢性能。

  假設(shè)Sales表有10,000行記錄,下面的SQL語句選中400行(總行數(shù)的4%): 

SELECTSalesDate, SalesPersonIDFROMSalesWHEREProductID=112

  我們來看看這條SQL語句在SQL執(zhí)行引擎中是如何執(zhí)行的:

  1)Sales表在ProductID列上有一個非聚集索引,因此它查找非聚集索引樹找出ProductID=112的記錄;

  2)包含ProductID = 112記錄的索引頁也包括所有的聚集索引鍵(所有的主鍵鍵值,即SalesID);

  3)針對每一個主鍵(這里是400),SQL Server引擎查找聚集索引樹找出真實的行在對應(yīng)頁面中的位置;

  SQL Server引擎從對應(yīng)的行查找SalesDate和SalesPersonID列的值。

  在上面的步驟中,對ProductID = 112的每個主鍵記錄(這里是400),SQL Server引擎要搜索400次聚集索引樹以檢索查詢中指定的其它列(SalesDate,SalesPersonID)。

  如果非聚集索引頁中包括了聚集索引鍵和其它兩列(SalesDate,,SalesPersonID)的值,SQL Server引擎可能不會執(zhí)行上面的第3和4步,直接從非聚集索引樹查找ProductID列速度還會快一些,直接從索引頁讀取這三列的數(shù)值。

  幸運的是,有一種方法實現(xiàn)了這個功能,它被稱為“覆蓋索引”,在表列上創(chuàng)建覆蓋索引時,需要指定哪些額外的列值需要和聚集索引鍵值(主鍵)一起存儲在索引頁中。下面是在Sales 表ProductID列上創(chuàng)建覆蓋索引的例子: 

CREATEINDEXNCLIX_Sales_ProductID--Index name

  ONdbo.Sales(ProductID)--Column on which index is to be created

  INCLUDE(SalesDate, SalesPersonID)--Additional column values to include

  應(yīng)該在那些select查詢中常使用到的列上創(chuàng)建覆蓋索引,但覆蓋索引中包括過多的列也不行,因為覆蓋索引列的值是存儲在內(nèi)存中的,這樣會消耗過多內(nèi)存,引發(fā)性能下降。

  創(chuàng)建覆蓋索引時使用數(shù)據(jù)庫調(diào)整顧問

  我們知道,當(dāng)SQL出問題時,SQL Server引擎中的優(yōu)化器根據(jù)下列因素自動生成不同的查詢計劃:

  1)數(shù)據(jù)量

  2)統(tǒng)計數(shù)據(jù)

  3)索引變化

  4)TSQL中的參數(shù)值

  5)服務(wù)器負載

  這就意味著,對于特定的SQL,即使表和索引結(jié)構(gòu)是一樣的,但在生產(chǎn)服務(wù)器和在測試服務(wù)器上產(chǎn)生的執(zhí)行計劃可能會不一樣,這也意味著在測試服務(wù)器上創(chuàng)建的索引可以提高應(yīng)用程序的性能,但在生產(chǎn)服務(wù)器上創(chuàng)建同樣的索引卻未必會提高應(yīng)用程序的性能。因為測試環(huán)境中的執(zhí)行計劃利用了新創(chuàng)建的索引,但在生產(chǎn)環(huán)境中執(zhí)行計劃可能不會利用新創(chuàng)建的索引(例如,一個非聚集索引列在生產(chǎn)環(huán)境中不是一個高選中性列,但在測試環(huán)境中可能就不一樣)。

  因此我們在創(chuàng)建索引時,要知道執(zhí)行計劃是否會真正利用它,但我們怎么才能知道呢?答案就是在測試服務(wù)器上模擬生產(chǎn)環(huán)境負載,然后創(chuàng)建合適的索引并進行測試,如果這樣測試發(fā)現(xiàn)索引可以提高性能,那么它在生產(chǎn)環(huán)境也就更可能提高應(yīng)用程序的性能了。

  雖然要模擬一個真實的負載比較困難,但目前已經(jīng)有很多工具可以幫助我們。

  使用SQL profiler跟蹤生產(chǎn)服務(wù)器,盡管不建議在生產(chǎn)環(huán)境中使用SQL profiler,但有時沒有辦法,要診斷性能問題關(guān)鍵所在,必須得用,在http://msdn.microsoft.com/en-us/library/ms181091.aspx有SQL profiler的使用方法。

  使用SQL profiler創(chuàng)建的跟蹤文件,在測試服務(wù)器上利用數(shù)據(jù)庫調(diào)整顧問創(chuàng)建一個類似的負載,大多數(shù)時候,調(diào)整顧問會給出一些可以立即使用的索引建議,在http://msdn.microsoft.com/en-us/library/ms166575.aspx有調(diào)整顧問的詳細介紹。


  第三步:整理索引碎片

  你可能已經(jīng)創(chuàng)建好了索引,并且所有索引都在工作,但性能卻仍然不好,那很可能是產(chǎn)生了索引碎片,你需要進行索引碎片整理。

  什么是索引碎片?

  由于表上有過度地插入、修改和刪除操作,索引頁被分成多塊就形成了索引碎片,如果索引碎片嚴重,那掃描索引的時間就會變長,甚至導(dǎo)致索引不可用,因此數(shù)據(jù)檢索操作就慢下來了。

  有兩種類型的索引碎片:內(nèi)部碎片和外部碎片。

  內(nèi)部碎片:為了有效的利用內(nèi)存,使內(nèi)存產(chǎn)生更少的碎片,要對內(nèi)存分頁,內(nèi)存以頁為單位來使用,最后一頁往往裝不滿,于是形成了內(nèi)部碎片。

  外部碎片:為了共享要分段,在段的換入換出時形成外部碎片,比如5K的段換出后,有一個4k的段進來放到原來5k的地方,于是形成1k的外部碎片。

  如何知道是否發(fā)生了索引碎片?

  執(zhí)行下面的SQL語句就知道了(下面的語句可以在SQL Server 2005及后續(xù)版本中運行,用你的數(shù)據(jù)庫名替換掉這里的AdventureWorks):

 SELECTobject_name(dt.object_id) Tablename,si.name

  IndexName,dt.avg_fragmentation_in_percentAS

  ExternalFragmentation,dt.avg_page_space_used_in_percentAS

  InternalFragmentation

  FROM

  (

  SELECTobject_id,index_id,avg_fragmentation_in_percent,avg_page_space_used_in_percent

  FROMsys.dm_db_index_physical_stats (db_id('AdventureWorks'),null,null,null,'DETAILED'

  )

  WHEREindex_id<>0)ASdtINNERJOINsys.indexes siONsi.object_id=dt.object_id

  ANDsi.index_id=dt.index_idANDdt.avg_fragmentation_in_percent>10

  ANDdt.avg_page_space_used_in_percent<75ORDERBYavg_fragmentation_in_percentDESC

  執(zhí)行后顯示AdventureWorks數(shù)據(jù)庫的索引碎片信息。

  圖 3 索引碎片信息

  使用下面的規(guī)則分析結(jié)果,你就可以找出哪里發(fā)生了索引碎片:

  1)ExternalFragmentation的值>10表示對應(yīng)的索引發(fā)生了外部碎片;

  2)InternalFragmentation的值<75表示對應(yīng)的索引發(fā)生了內(nèi)部碎片。

  如何整理索引碎片?

  有兩種整理索引碎片的方法:

  1)重組有碎片的索引:執(zhí)行下面的命令

  ALTER INDEX ALL ON TableName REORGANIZE

  2)重建索引:執(zhí)行下面的命令

  ALTER INDEX ALL ON TableName REBUILD WITH (FILLFACTOR=90,ONLINE=ON)

  也可以使用索引名代替這里的“ALL”關(guān)鍵字重組或重建單個索引,也可以使用SQL Server管理工作臺進行索引碎片的整理。

  圖 4 使用SQL Server管理工作臺整理索引碎片

  什么時候用重組,什么時候用重建呢?

  當(dāng)對應(yīng)索引的外部碎片值介于10-15之間,內(nèi)部碎片值介于60-75之間時使用重組,其它情況就應(yīng)該使用重建。

  值得注意的是重建索引時,索引對應(yīng)的表會被鎖定,但重組不會鎖表,因此在生產(chǎn)系統(tǒng)中,對大表重建索引要慎重,因為在大表上創(chuàng)建索引可能會花幾個小時,幸運的是,從SQL Server 2005開始,微軟提出了一個解決辦法,在重建索引時,將ONLINE選項設(shè)置為ON,這樣可以保證重建索引時表仍然可以正常使用。

  雖然索引可以提高查詢速度,但如果你的數(shù)據(jù)庫是一個事務(wù)型數(shù)據(jù)庫,大多數(shù)時候都是更新操作,更新數(shù)據(jù)也就意味著要更新索引,這個時候就要兼顧查詢和更新操作了,因為在OLTP數(shù)據(jù)庫表上創(chuàng)建過多的索引會降低整體數(shù)據(jù)庫性能。

  我給大家一個建議:如果你的數(shù)據(jù)庫是事務(wù)型的,平均每個表上不能超過5個索引,如果你的數(shù)據(jù)庫是數(shù)據(jù)倉庫型,平均每個表可以創(chuàng)建10個索引都沒問題。


  在前面我們介紹了如何正確使用索引,調(diào)整索引是見效最快的性能調(diào)優(yōu)方法,但一般而言,調(diào)整索引只會提高查詢性能。除此之外,我們還可以調(diào)整數(shù)據(jù)訪問代碼和TSQL,本文就介紹如何以最優(yōu)的方法重構(gòu)數(shù)據(jù)訪問代碼和TSQL。

  第四步:將TSQL代碼從應(yīng)用程序遷移到數(shù)據(jù)庫中

  也許你不喜歡我的這個建議,你或你的團隊可能已經(jīng)有一個默認的潛規(guī)則,那就是使用ORM(Object Relational Mapping,即對象關(guān)系映射)生成所有SQL,并將SQL放在應(yīng)用程序中,但如果你要優(yōu)化數(shù)據(jù)訪問性能,或需要調(diào)試應(yīng)用程序性能問題,我建議你將SQL代碼移植到數(shù)據(jù)庫上(使用存儲過程,視圖,函數(shù)和觸發(fā)器),原因如下:

  1、使用存儲過程,視圖,函數(shù)和觸發(fā)器實現(xiàn)應(yīng)用程序中SQL代碼的功能有助于減少應(yīng)用程序中SQL復(fù)制的弊端,因為現(xiàn)在只在一個地方集中處理SQL,為以后的代碼復(fù)用打下了良好的基礎(chǔ)。

  2、使用數(shù)據(jù)庫對象實現(xiàn)所有的TSQL有助于分析TSQL的性能問題,同時有助于你集中管理TSQL代碼。

  3、將TS QL移植到數(shù)據(jù)庫上去后,可以更好地重構(gòu)TSQL代碼,以利用數(shù)據(jù)庫的高級索引特性。此外,應(yīng)用程序中沒了SQL代碼也將更加簡潔。

  雖然這一步可能不會象前三步那樣立竿見影,但做這一步的主要目的是為后面的優(yōu)化步驟打下基礎(chǔ)。如果在你的應(yīng)用程序中使用ORM(如NHibernate)實現(xiàn)了數(shù)據(jù)訪問例行程序,在測試或開發(fā)環(huán)境中你可能發(fā)現(xiàn)它們工作得很好,但在生產(chǎn)數(shù)據(jù)庫上卻可能遇到問題,這時你可能需要反思基于ORM的數(shù)據(jù)訪問邏輯,利用TSQL對象實現(xiàn)數(shù)據(jù)訪問例行程序是一種好辦法,這樣做有更多的機會從數(shù)據(jù)庫角度來優(yōu)化性能。

  我向你保證,如果你花1-2人月來完成遷移,那以后肯定不止節(jié)約1-2人年的的成本。

  OK!假設(shè)你已經(jīng)照我的做的了,完全將TSQL遷移到數(shù)據(jù)庫上去了,下面就進入正題吧!


  第五步:識別低效TSQL,采用最佳實踐重構(gòu)和應(yīng)用TSQL

  由于每個程序員的能力和習(xí)慣都不一樣,他們編寫的TSQL可能風(fēng)格各異,部分代碼可能不是最佳實現(xiàn),對于水平一般的程序員可能首先想到的是編寫TSQL實現(xiàn)需求,至于性能問題日后再說,因此在開發(fā)和測試時可能發(fā)現(xiàn)不了問題。

  也有一些人知道最佳實踐,但在編寫代碼時由于種種原因沒有采用最佳實踐,等到用戶發(fā)飆的那天才乖乖地重新埋頭思考最佳實踐。

  我覺得還是有必要介紹一下具有都有哪些最佳實踐。

  1、在查詢中不要使用“select *”

  (1)檢索不必要的列會帶來額外的系統(tǒng)開銷,有句話叫做“該省的則省”;

  (2)數(shù)據(jù)庫不能利用“覆蓋索引”的優(yōu)點,因此查詢緩慢。

  2、在select清單中避免不必要的列,在連接條件中避免不必要的表

  (1)在select查詢中如有不必要的列,會帶來額外的系統(tǒng)開銷,特別是LOB類型的列;

  (2)在連接條件中包含不必要的表會強制數(shù)據(jù)庫引擎檢索和匹配不需要的數(shù)據(jù),增加了查詢執(zhí)行時間。

  3、不要在子查詢中使用count()求和執(zhí)行存在性檢查

  (1)不要使用

SELECTcolumn_listFROMtableWHERE0<(SELECTcount(*)FROMtable2WHERE..)

  使用

SELECTcolumn_listFROMtableWHEREEXISTS(SELECT*FROMtable2WHERE...)

  代替;

  (2)當(dāng)你使用count()時,SQL Server不知道你要做的是存在性檢查,它會計算所有匹配的值,要么會執(zhí)行全表掃描,要么會掃描最小的非聚集索引;

  (3)當(dāng)你使用EXISTS時,SQL Server知道你要執(zhí)行存在性檢查,當(dāng)它發(fā)現(xiàn)第一個匹配的值時,就會返回TRUE,并停止查詢。類似的應(yīng)用還有使用IN或ANY代替count()。

  4、避免使用兩個不同類型的列進行表的連接

  (1)當(dāng)連接兩個不同類型的列時,其中一個列必須轉(zhuǎn)換成另一個列的類型,級別低的會被轉(zhuǎn)換成高級別的類型,轉(zhuǎn)換操作會消耗一定的系統(tǒng)資源;

  (2)如果你使用兩個不同類型的列來連接表,其中一個列原本可以使用索引,但經(jīng)過轉(zhuǎn)換后,優(yōu)化器就不會使用它的索引了。例如: 

SELECTcolumn_listFROMsmall_table, large_tableWHERE

  smalltable.float_column=large_table.int_column

  在這個例子中,SQL Server會將int列轉(zhuǎn)換為float類型,因為int比float類型的級別低,large_table.int_column上的索引就不會被使用,但smalltable.float_column上的索引可以正常使用。

  5、避免死鎖

  (1)在你的存儲過程和觸發(fā)器中訪問同一個表時總是以相同的順序;

  (2)事務(wù)應(yīng)經(jīng)可能地縮短,在一個事務(wù)中應(yīng)盡可能減少涉及到的數(shù)據(jù)量;

  (3)永遠不要在事務(wù)中等待用戶輸入。

  6、使用“基于規(guī)則的方法”而不是使用“程序化方法”編寫TSQL

  (1)數(shù)據(jù)庫引擎專門為基于規(guī)則的SQL進行了優(yōu)化,因此處理大型結(jié)果集時應(yīng)盡量避免使用程序化的方法(使用游標或UDF[User Defined Functions]處理返回的結(jié)果集) ;

  (2)如何擺脫程序化的SQL呢?有以下方法:

  - 使用內(nèi)聯(lián)子查詢替換用戶定義函數(shù);

  - 使用相關(guān)聯(lián)的子查詢替換基于游標的代碼;

  - 如果確實需要程序化代碼,至少應(yīng)該使用表變量代替游標導(dǎo)航和處理結(jié)果集。


  7、避免使用count(*)獲得表的記錄數(shù)

  (1)為了獲得表中的記錄數(shù),我們通常使用下面的SQL語句:

 SELECTCOUNT(*)FROMdbo.orders

  這條語句會執(zhí)行全表掃描才能獲得行數(shù)。

  (2)但下面的SQL語句不會執(zhí)行全表掃描一樣可以獲得行數(shù):

SELECTrowsFROMsysindexes

  WHEREid=OBJECT_ID('dbo.Orders')ANDindid<2

  8、避免使用動態(tài)SQL

  除非迫不得已,應(yīng)盡量避免使用動態(tài)SQL,因為:

  (1)動態(tài)SQL難以調(diào)試和故障診斷;

  (2)如果用戶向動態(tài)SQL提供了輸入,那么可能存在SQL注入風(fēng)險。

  9、避免使用臨時表

  (1)除非卻有需要,否則應(yīng)盡量避免使用臨時表,相反,可以使用表變量代替;

  (2)大多數(shù)時候(99%),表變量駐扎在內(nèi)存中,因此速度比臨時表更快,臨時表駐扎在TempDb數(shù)據(jù)庫中,因此臨時表上的操作需要跨數(shù)據(jù)庫通信,速度自然慢。

  10、使用全文搜索搜索文本數(shù)據(jù),取代like搜索

  全文搜索始終優(yōu)于like搜索:

  (1)全文搜索讓你可以實現(xiàn)like不能完成的復(fù)雜搜索,如搜索一個單詞或一個短語,搜索一個與另一個單詞或短語相近的單詞或短語,或者是搜索同義詞;

  (2)實現(xiàn)全文搜索比實現(xiàn)like搜索更容易(特別是復(fù)雜的搜索);

  11、使用union實現(xiàn)or操作

  (1)在查詢中盡量不要使用or,使用union合并兩個不同的查詢結(jié)果集,這樣查詢性能會更好;

  (2)如果不是必須要不同的結(jié)果集,使用union all效果會更好,因為它不會對結(jié)果集排序。

  12、為大對象使用延遲加載策略

  (1)在不同的表中存儲大對象(如VARCHAR(MAX),Image,Text等),然后在主表中存儲這些大對象的引用;

  (2)在查詢中檢索所有主表數(shù)據(jù),如果需要載入大對象,按需從大對象表中檢索大對象。

  13、使用VARCHAR(MAX),VARBINARY(MAX) 和 NVARCHAR(MAX)

  (1)在SQL Server 2000中,一行的大小不能超過800字節(jié),這是受SQL Server內(nèi)部頁面大小8KB的限制造成的,為了在單列中存儲更多的數(shù)據(jù),你需要使用TEXT,NTEXT或IMAGE數(shù)據(jù)類型(BLOB);

  (2)這些和存儲在相同表中的其它數(shù)據(jù)不一樣,這些頁面以B-Tree結(jié)構(gòu)排列,這些數(shù)據(jù)不能作為存儲過程或函數(shù)中的變量,也不能用于字符串函數(shù),如REPLACE,CHARINDEX或SUBSTRING,大多數(shù)時候你必須使用READTEXT,WRITETEXT和UPDATETEXT;

  (3)為了解決這個問題,在SQL Server 2005中增加了VARCHAR(MAX),VARBINARY(MAX) 和 NVARCHAR(MAX),這些數(shù)據(jù)類型可以容納和BLOB相同數(shù)量的數(shù)據(jù)(2GB),和其它數(shù)據(jù)類型使用相同的數(shù)據(jù)頁;

  (4)當(dāng)MAX數(shù)據(jù)類型中的數(shù)據(jù)超過8KB時,使用溢出頁(在ROW_OVERFLOW分配單元中)指向源數(shù)據(jù)頁,源數(shù)據(jù)頁仍然在IN_ROW分配單元中。

  14、在用戶定義函數(shù)中使用下列最佳實踐

  不要在你的存儲過程,觸發(fā)器,函數(shù)和批處理中重復(fù)調(diào)用函數(shù),例如,在許多時候,你需要獲得字符串變量的長度,無論如何都不要重復(fù)調(diào)用LEN函數(shù),只調(diào)用一次即可,將結(jié)果存儲在一個變量中,以后就可以直接使用了。


  15、在存儲過程中使用下列最佳實踐

  (1)不要使用SP_xxx作為命名約定,它會導(dǎo)致額外的搜索,增加I/O(因為系統(tǒng)存儲過程的名字就是以SP_開頭的),同時這么做還會增加與系統(tǒng)存儲過程名稱沖突的幾率;

  (2)將Nocount設(shè)置為On避免額外的網(wǎng)絡(luò)開銷;

  (3)當(dāng)索引結(jié)構(gòu)發(fā)生變化時,在EXECUTE語句中(第一次)使用WITH RECOMPILE子句,以便存儲過程可以利用最新創(chuàng)建的索引;

  (4)使用默認的參數(shù)值更易于調(diào)試。

  16、在觸發(fā)器中使用下列最佳實踐

  (1)最好不要使用觸發(fā)器,觸發(fā)一個觸發(fā)器,執(zhí)行一個觸發(fā)器事件本身就是一個耗費資源的過程;

  (2)如果能夠使用約束實現(xiàn)的,盡量不要使用觸發(fā)器;

  (3)不要為不同的觸發(fā)事件(Insert,Update和Delete)使用相同的觸發(fā)器;

  (4)不要在觸發(fā)器中使用事務(wù)型代碼。

  17、在視圖中使用下列最佳實踐

  (1)為重新使用復(fù)雜的TSQL塊使用視圖,并開啟索引視圖;

  (2)如果你不想讓用戶意外修改表結(jié)構(gòu),使用視圖時加上SCHEMABINDING選項;

  (3)如果只從單個表中檢索數(shù)據(jù),就不需要使用視圖了,如果在這種情況下使用視圖反倒會增加系統(tǒng)開銷,一般視圖會涉及多個表時才有用。

  18、在事務(wù)中使用下列最佳實踐

  (1)SQL Server 2005之前,在BEGIN TRANSACTION之后,每個子查詢修改語句時,必須檢查@@ERROR的值,如果值不等于0,那么最后的語句可能會導(dǎo)致一個錯誤,如果發(fā)生任何錯誤,事務(wù)必須回滾。從SQL Server 2005開始,Try..Catch..代碼塊可以處理TSQL中的事務(wù),因此在事務(wù)型代碼中最好加上Try…Catch…;

  (2)避免使用嵌套事務(wù),使用@@TRANCOUNT變量檢查事務(wù)是否需要啟動(為了避免嵌套事務(wù));

  (3)盡可能晚啟動事務(wù),提交和回滾事務(wù)要盡可能快,以減少資源鎖定時間。

  要完全列舉最佳實踐不是本文的初衷,當(dāng)你了解了這些技巧后就應(yīng)該拿來使用,否則了解了也沒有價值。此外,你還需要評審和監(jiān)視數(shù)據(jù)訪問代碼是否遵循下列標準和最佳實踐。

  如何分析和識別你的TSQL中改進的范圍?

  理想情況下,大家都想預(yù)防疾病,而不是等病發(fā)了去治療。但實際上這個愿望根本無法實現(xiàn),即使你的團隊成員全都是專家級人物,我也知道你有進行評審,但代碼仍然一團糟,因此需要知道如何治療疾病一樣重要。

  首先需要知道如何診斷性能問題,診斷就得分析TSQL,找出瓶頸,然后重構(gòu),要找出瓶頸就得先學(xué)會分析執(zhí)行計劃。


  理解查詢執(zhí)行計劃

  當(dāng)你將SQL語句發(fā)給SQL Server引擎后,SQL Server首先要確定最合理的執(zhí)行方法,查詢優(yōu)化器會使用很多信息,如數(shù)據(jù)分布統(tǒng)計,索引結(jié)構(gòu),元數(shù)據(jù)和其它信息,分析多種可能的執(zhí)行計劃,最后選擇一個最佳的執(zhí)行計劃。

  可以使用SQL Server Management Studio預(yù)覽和分析執(zhí)行計劃,寫好SQL語句后,點擊SQL Server Management Studio上的評估執(zhí)行計劃按鈕查看執(zhí)行計劃,如圖1所示。

  圖 1 在Management Studio中評估執(zhí)行計劃

  在執(zhí)行計劃圖中的每個圖標代表計劃中的一個行為(操作),應(yīng)從右到左閱讀執(zhí)行計劃,每個行為都一個相對于總體執(zhí)行成本(100%)的成本百分比。

  在上面的執(zhí)行計劃圖中,右邊的那個圖標表示在HumanResources表上的一個“聚集索引掃描”操作(閱讀表中所有主鍵索引值),需要100%的總體查詢執(zhí)行成本,圖中左邊那個圖標表示一個select操作,它只需要0%的總體查詢執(zhí)行成本。

  下面是一些比較重要的圖標及其對應(yīng)的操作:

  圖 2 常見的重要圖標及對應(yīng)的操作

  注意執(zhí)行計劃中的查詢成本,如果說成本等于100%,那很可能在批處理中就只有這個查詢,如果在一個查詢窗口中有多個查詢同時執(zhí)行,那它們肯定有各自的成本百分比(小于100%)。

  如果想知道執(zhí)行計劃中每個操作詳細情況,將鼠標指針移到對應(yīng)的圖標上即可,你會看到類似于下面的這樣一個窗口。

  圖 3 查看執(zhí)行計劃中行為(操作)的詳細信息

  這個窗口提供了詳細的評估信息,上圖顯示了聚集索引掃描的詳細信息,它要查找AdventureWorks數(shù)據(jù)庫HumanResources方案下Employee表中 Gender = ‘M’的行,它也顯示了評估的I/O,CPU成本。

  查看執(zhí)行計劃時,我們應(yīng)該獲得什么信息

  當(dāng)你的查詢很慢時,你就應(yīng)該看看預(yù)估的執(zhí)行計劃(當(dāng)然也可以查看真實的執(zhí)行計劃),找出耗時最多的操作,注意觀察以下成本通常較高的操作:

  1、表掃描(Table Scan)

  當(dāng)表沒有聚集索引時就會發(fā)生,這時只要創(chuàng)建聚集索引或重整索引一般都可以解決問題。

  2、聚集索引掃描(Clustered Index Scan)

  有時可以認為等同于表掃描,當(dāng)某列上的非聚集索引無效時會發(fā)生,這時只要創(chuàng)建一個非聚集索引就ok了。

  3、哈希連接(Hash Join)

  當(dāng)連接兩個表的列沒有被索引時會發(fā)生,只需在這些列上創(chuàng)建索引即可。

  4、嵌套循環(huán)(Nested Loops)

  當(dāng)非聚集索引不包括select查詢清單的列時會發(fā)生,只需要創(chuàng)建覆蓋索引問題即可解決。

  5、RID查找(RID Lookup)

  當(dāng)你有一個非聚集索引,但相同的表上卻沒有聚集索引時會發(fā)生,此時數(shù)據(jù)庫引擎會使用行ID查找真實的行,這時一個代價高的操作,這時只要在該表上創(chuàng)建聚集索引即可。

  TSQL重構(gòu)真實的故事

<p style="margin:10px auto; paddi

本站聲明: 本文章由作者或相關(guān)機構(gòu)授權(quán)發(fā)布,目的在于傳遞更多信息,并不代表本站贊同其觀點,本站亦不保證或承諾內(nèi)容真實性等。需要轉(zhuǎn)載請聯(lián)系該專欄作者,如若文章內(nèi)容侵犯您的權(quán)益,請及時聯(lián)系本站刪除。
換一批
延伸閱讀

9月2日消息,不造車的華為或?qū)⒋呱龈蟮莫毥谦F公司,隨著阿維塔和賽力斯的入局,華為引望愈發(fā)顯得引人矚目。

關(guān)鍵字: 阿維塔 塞力斯 華為

加利福尼亞州圣克拉拉縣2024年8月30日 /美通社/ -- 數(shù)字化轉(zhuǎn)型技術(shù)解決方案公司Trianz今天宣布,該公司與Amazon Web Services (AWS)簽訂了...

關(guān)鍵字: AWS AN BSP 數(shù)字化

倫敦2024年8月29日 /美通社/ -- 英國汽車技術(shù)公司SODA.Auto推出其旗艦產(chǎn)品SODA V,這是全球首款涵蓋汽車工程師從創(chuàng)意到認證的所有需求的工具,可用于創(chuàng)建軟件定義汽車。 SODA V工具的開發(fā)耗時1.5...

關(guān)鍵字: 汽車 人工智能 智能驅(qū)動 BSP

北京2024年8月28日 /美通社/ -- 越來越多用戶希望企業(yè)業(yè)務(wù)能7×24不間斷運行,同時企業(yè)卻面臨越來越多業(yè)務(wù)中斷的風(fēng)險,如企業(yè)系統(tǒng)復(fù)雜性的增加,頻繁的功能更新和發(fā)布等。如何確保業(yè)務(wù)連續(xù)性,提升韌性,成...

關(guān)鍵字: 亞馬遜 解密 控制平面 BSP

8月30日消息,據(jù)媒體報道,騰訊和網(wǎng)易近期正在縮減他們對日本游戲市場的投資。

關(guān)鍵字: 騰訊 編碼器 CPU

8月28日消息,今天上午,2024中國國際大數(shù)據(jù)產(chǎn)業(yè)博覽會開幕式在貴陽舉行,華為董事、質(zhì)量流程IT總裁陶景文發(fā)表了演講。

關(guān)鍵字: 華為 12nm EDA 半導(dǎo)體

8月28日消息,在2024中國國際大數(shù)據(jù)產(chǎn)業(yè)博覽會上,華為常務(wù)董事、華為云CEO張平安發(fā)表演講稱,數(shù)字世界的話語權(quán)最終是由生態(tài)的繁榮決定的。

關(guān)鍵字: 華為 12nm 手機 衛(wèi)星通信

要點: 有效應(yīng)對環(huán)境變化,經(jīng)營業(yè)績穩(wěn)中有升 落實提質(zhì)增效舉措,毛利潤率延續(xù)升勢 戰(zhàn)略布局成效顯著,戰(zhàn)新業(yè)務(wù)引領(lǐng)增長 以科技創(chuàng)新為引領(lǐng),提升企業(yè)核心競爭力 堅持高質(zhì)量發(fā)展策略,塑強核心競爭優(yōu)勢...

關(guān)鍵字: 通信 BSP 電信運營商 數(shù)字經(jīng)濟

北京2024年8月27日 /美通社/ -- 8月21日,由中央廣播電視總臺與中國電影電視技術(shù)學(xué)會聯(lián)合牽頭組建的NVI技術(shù)創(chuàng)新聯(lián)盟在BIRTV2024超高清全產(chǎn)業(yè)鏈發(fā)展研討會上宣布正式成立。 活動現(xiàn)場 NVI技術(shù)創(chuàng)新聯(lián)...

關(guān)鍵字: VI 傳輸協(xié)議 音頻 BSP

北京2024年8月27日 /美通社/ -- 在8月23日舉辦的2024年長三角生態(tài)綠色一體化發(fā)展示范區(qū)聯(lián)合招商會上,軟通動力信息技術(shù)(集團)股份有限公司(以下簡稱"軟通動力")與長三角投資(上海)有限...

關(guān)鍵字: BSP 信息技術(shù)
關(guān)閉
關(guān)閉