1. 樹表設計,建議在樹表中增加字段TreePath
2. 記錄超過一百萬的表要考慮是否需要使用分區(qū)表,。對于能夠明確確定分區(qū)字段,,并且經(jīng)常通過分區(qū)訪問記錄的。分區(qū)表會提高查詢性能,。 3. 反范式 在第三范式設計的基礎上,,針對具體的數(shù)據(jù)情況和應用訪問數(shù)據(jù)情況進行的設計,使用適當?shù)臄?shù)據(jù)冗余和數(shù)據(jù)不一致風險,,提供更高的數(shù)據(jù)訪問性能,,以保障應用的性能要求。 4. 避免使用Select * 5. 使用Set代替Select進行賦值 6. 必要的時候,,使用關鍵字”NOLOCK”避免鎖定 7. 不需要排序的時候,,不要使用關鍵字”O(jiān)rder 8. 字符列字節(jié)長度小于等于10時,使用char或者nchar 9. 字符列字節(jié)長度大于10時,,使用varchar或者nvarchar(實際長度設置需要根據(jù)業(yè)務需要仔細規(guī)劃,,長度關系到存儲空間,關系到緩存占用) 10. 需要使用小數(shù)時,,不要使用數(shù)據(jù)類型float,,而使用decimal 11. 日期存儲時,使用datetime,,不要使用字符類型 12. 請不要使用ntext,,text,image數(shù)據(jù)類型,,而使用nvarchar(max),,varchar(max),varbinary(max)類型 13. 考慮自增長列的長度(例如:部件關系表數(shù)據(jù)量會溢出int類型數(shù)據(jù)的范圍) 14. 索引*組合或者引用關系的子表(數(shù)據(jù)量較大的時候),,需要在關聯(lián)主表的列上建立非聚集索引(如訂單明細表中的產(chǎn)品ID字段,、訂單明細表中關聯(lián)的訂單ID字段)
*索引鍵的大小不能超過900個字節(jié),當列表的大小超過900個字節(jié)或者若干列的和超過900個字節(jié)時,,數(shù)據(jù)庫將報錯,。
*表中如果建有大量索引將會影響INSERT,、UPDATE和DELETE語句的性能,因為在表中的數(shù)據(jù)更改時,,所有的索引都將必須進行適當?shù)恼{(diào)整,。需要避免對經(jīng)常更新的表進行過多的索引,并且索引應保持較窄,,就是說:列要盡可能的少,。
*為經(jīng)常用于查詢的謂詞創(chuàng)建索引,如用于下拉參照快速查找的code,、name等,。在平臺現(xiàn)有下拉參照的查詢sql語句中的like條件語句要改成不帶前置通配符。還有需要關注Order By和Group By謂詞的索引設計,,Order By和Group By的謂詞是需要排序的,,某些情況下為Order By和Group By的謂詞建立索引,會避免查詢時的排序動作,。
*對于內(nèi)容基本重復的列,,比如只有1和0,禁止建立索引,,因為該索引選擇性極差,,在特定的情況下會誤導優(yōu)化器做出錯誤的選擇,導致查詢速度極大下降,。
*當一個索引有多個列構成時,,應注意將選擇性強的列放在前面。僅僅前后次序的不同,,性能上就可能出現(xiàn)數(shù)量級的差異,。
*對小表進行索引可能不能產(chǎn)生優(yōu)化效果,,因為查詢優(yōu)化器在遍歷用于搜索數(shù)據(jù)的索引時,,花費的時間可能比執(zhí)行簡單的表掃描還長,設計索引時需要考慮表的大小,。記錄數(shù)不大于100的表不要建立索引,。頻繁操作的小數(shù)量表不建議建立索引(記錄數(shù)不大于5000條) 阻塞原因在默認事務隔離情況下,數(shù)據(jù)庫事務越長,,一方面獨占鎖被持有的時間越長,,寫操作阻塞讀操作的機會就越多;另一方面,,在默認的讀提交隔離模式下,,讀操作使用共享鎖與獨占鎖不兼容,讀操作也會阻塞寫操作,。 阻塞也是死鎖產(chǎn)生的基本條件,,改善了阻塞就能有效減少死鎖,。 在軟件開發(fā)后期,在對大數(shù)據(jù)量的集成測試工程中,,通過活動查看器可以觀察到阻塞情況,,主要產(chǎn)生阻塞的原因就是讀和寫相互阻塞在對同一個大表的操作上。因此對于讀寫阻塞問題需要加以足夠考慮,。 減少阻塞一些指導原則整體原則*歸結起來也依賴于代碼,、sql的優(yōu)化,一方面要使邏輯代碼優(yōu)化到最快,。另一方面,,一個耗時較長的sql語句將會阻塞全部用戶等待幾十秒甚至幾分鐘,針對查詢sql語句的優(yōu)化也是最重要的,。 *修改批量操作的需求,,批量操作耗時和記錄數(shù)量是成正比的。為此設計時要避免在同一個自動事務服務方法中做批量的循環(huán)操作,,可以將循環(huán)操作放到UI控制端,,這一就使一個長事務變成多個短小的事務,將減少阻塞的機會,。 *減少讀寫操作使用鎖的數(shù)量,,比如減少批更新操作中修改行的數(shù)量,保證行鎖定少,,同時減少鎖升級至表鎖的機會,。 *一些耗時大、鎖定數(shù)據(jù)多的操作需要避免和正常業(yè)務操作沖突,,可以使用調(diào)度計劃在系統(tǒng)閑置的時候來運行,,或者使用互斥機制來保證其它用戶暫退出操作獨立運行,視業(yè)務情況而定,。 讀寫鎖阻塞的處理原則*減少讀操作需要的共享鎖
*在sqlserver2005中使用基于行版本的快照隔離模式 在快照模式下,,讀取數(shù)據(jù)不再使用共享鎖,阻塞的現(xiàn)象能大大減少,。 方法:在建立數(shù)據(jù)庫后執(zhí)行下面命令: ALTER DATABASE 替換的數(shù)據(jù)庫名 SET READ_COMMITIED_SNAPSHOT ON; ALTER DATABASE 替換的數(shù)據(jù)庫名 SET ALLOW_SNAPSHOT_ISOLATION ON; 注意:sqlserver2005和with(no lock)語句,,這兩種方案都能夠避免阻塞,但是這兩種方式是有區(qū)別的,。 舉個小例子說明一下:比如數(shù)據(jù)庫表T1中有兩個字段Col1且其默認值為1.此時恰好有個A事務通過Update語句修改表T1的Col字段值為2,,但還未提交,如下:
============================================================================================== 死鎖原因:死鎖是由兩個相互阻塞的線程組成,,它們互相等待對方完成,,一般死鎖情況下兩個數(shù)據(jù)庫事務之間存在著相反的操作。sqlserver中死鎖監(jiān)視器定時檢查死鎖,,如果發(fā)現(xiàn)死鎖,,將選擇其中回滾消耗最小的任務,這時候發(fā)生1025數(shù)據(jù)庫錯誤,??梢酝ㄟ^啟用sqlserver2005快照模式,避免一些讀/寫的逆向阻塞造成的死鎖.但是對于一些寫/寫阻塞的死鎖可能無法解決,,很多時候需要從業(yè)務的角度來避免一些寫/寫的逆向操作阻塞情況,。
死鎖問題的解決很困難,但是可以通過一些手段來使死鎖最小化,。
死鎖最小化方法:從理論上講,,數(shù)據(jù)庫死鎖無法避免,但是可以遵循一定原則使死鎖發(fā)生的概率盡量降低,。
寫/寫死鎖*用相同的順序訪問對象,如果涉及到多于一張表的操作,,要保證事務中都按照相同的順序訪問這些表,。
*減少一個事務中的大批量更新的操作,大批量操作寫操作涉及記錄獨占鎖太多而且一直到事務結束才能釋放,更容易與其它事務造成死鎖,。
讀/寫死鎖(原則上與前面提到的減少讀/寫阻塞方式一致)*去掉讀操作的共享鎖
最佳方式是使用sql2005的快照模式,,其次方式是使用讀未提交隔離模式或使用NOLock提示,需要平臺和業(yè)務設計時依據(jù)情況進行sql組織的設計,。
按照相同的順序訪問對象可以避免相互持有對方請求資源的情況發(fā)生,。例如一個操作主從表的處理流程,涉及查詢和修改兩個步驟,。如果查詢時是先查主表再查從表,,則修改也應先修改主表再修改從表。
另一個降低事務大小的一個主要手段,,是將查詢操作盡可能地提前(包括使用一些中間變量記錄下查詢結果提供后續(xù)使用),,而把插入、修改等操作集中在方法靠后的部分,。這樣,,可以讓一個事務需要持有獨占鎖的時間盡可能縮短,減少死鎖的發(fā)生概率,。 ============================================================================================== 1,、參數(shù)化sql 對于一般簡單查詢,數(shù)據(jù)庫能自動參數(shù)啊以重用計劃緩存,,如: 在sqlserver內(nèi)部能自動參數(shù)化這個查詢,,SELECT * FROM table WHERE id=@1 但是一旦sql語句中帶有join、union,、top……等關鍵字,,sqlserver內(nèi)部將不會自動參數(shù)化。 在sql2005中,,通過alter database XXX set paramenterization forced的強制參數(shù)化命令能夠將所有sql中的常量參數(shù)化,,但是強制參數(shù)化會因為常量類型不一致造成查詢結果誤差。 2,、使用查詢中的索引有效 a),、單列索引使用原則單列索引能響應大部分的簡單比較,包括等價和不等價,。對于like操作無前置通配符也是有效的,。如:
b)、避免在WHERE字句中對字段進行函數(shù)或表達式操作看一下下面效率低下的例子和其解決方法
下面是SQLServer2005的優(yōu)化報告
任何對列的操作都將導致表掃描,,它包括數(shù)據(jù)庫函數(shù),、計算表達式等等,查詢時要盡可能將操作移至等號右邊,。 避免使用,!=或<>,、is null 或is not null、 in,、not in等這樣的操作符,,因為這會是系統(tǒng)無法使用索引,而只能直接搜索表中數(shù)據(jù),。 例如: select id from employee where id!=’B%’ 優(yōu)化器將無法通過索引來確定將要命中的行數(shù),,因此需要搜索該表的所有行。 c),、多列索引使用原則則應考慮列的順序,。用于等于(=)、大于(>),、小于(<)或between搜索條件的where 字句或者參與聯(lián)接的列應該放在最前面,。其它列應該基于其非重要級別進行排序,就是說,,從最不重復的列到最重復的列,。 例如: 如果表中存在索引定義為LastName、FirstName,則該索引在搜索條件為where LastName=’Smith’或where LastName=Smith and FirstName like ’j%’時將很有用,。不過,,查詢優(yōu)化器不會將此索引用于基于FirstName(where FirstName=’Jane’)而搜索的查詢。 ============================================================================================== 一,、SQL拼寫建議 1,、查詢時不返回不需要的行、列業(yè)務代碼要根據(jù)實際情況盡量減少對表的訪問行數(shù),,最小化結果集,,在查詢時,不要過多地使用通配符如:select * from table1語句,,要用到幾列就選擇幾列,,如:select col1,col2 from table1;在可能的情況下盡量限制結果集行數(shù)如:select top 100 col1,col2,col3 from talbe2,因為某些情況下用戶是不需要那么多的數(shù)據(jù)的。 2,、合理使用EXISTS, NOT EXISTS字句如下所示: SELECT SUM(T1.C1) FROM T1 WHERE ((SELECT COUNT(*) FROM T2 WHERE T2.C2=T1.C2)>0) SELECT SUM(T1.C1) FROM T1 WHERE EXISTS(SELECT * FROM T2 WHERE T2.C2=T1.C2) 兩種產(chǎn)生相同的結果,,但是后者的效率顯然要高過于前者。銀行后者不會產(chǎn)生大量鎖定的表掃描或是索引掃描,。 經(jīng)常需要些一個T_SQLL語句比較一個父結果集和子結果集,,從而找到是否存在在父結果集中有而在子結果集中乜嘢的記錄,如: SELECT _a.hdr_key FROM hdr_tb1 a -----------tb1 a 表示tb1用別名a代替 WHERE NOT EXISTS (SELECT * FROM dt1_tb1 b WHERE a.hdr_key = b.hdr_key) SELECT _a.hdr_key FROM hdr_tb1 a -----------tb1 a 表示tb1用別名a代替 LEFT JION dt1_tb1 b ON a.hdr_key = b.hdr_key WHERE b.hdr_key IS NULL SELECT hdr_key FROM hdr_tb1 WHERE hdr_key NOT IN (SELECT hdr_key FROM dt1_tb1) 三種寫法都可以得到同樣的結果集,,但是效率是依次降低 3,、充分利用連接條件在某種情況下,兩個表之間可能不止一個的連接條件,,這時在where 字句中將諒解條件完整的寫上,,有可能大大提高查詢速度,。 例: a),、SELECT SUM(A.AMOUNT) FROM ACCOUNT A left jion CARD B on A.CARD_NO = B.CARD_NO b),、SELECT SUM(A.AMOUNT) FROM ACCOUNT A left jion CARD B on A.CARD_NO = B.CARD_NO AND A.ACCOUNT_NO = B.ACCOUNT_NO 第二句將比第一句執(zhí)行快得多。 4,、WHERE 字句中關系運算符的選擇a),、在關系運算中,盡量使用=,,盡量不要使用<>,。 b)、WHERE字句中盡量不要使用NOT運算符,,如:NOT IN ,NOT EXISTS, NOT>,、NOT<等等NOT運算符一般可以去除。如NOT SALARY >10000K可以改為:salary<=100,如避免使用NOT IN,可以使用 left outer jion代替它,。 c),、where 字句中條件表達式間邏輯關系為AND時,將條件為假的概率高的放在前面,,概率相同,、條件計算簡單的放在前面。 d),、盡可能不要用Order by字句,。使用Order by時,盡量減少列數(shù),、盡量減少排序數(shù)據(jù)行數(shù),、排序字段盡量是數(shù)字型(盡量不要是字符型)。GROUP BY,、 SELECT DITINCT,、UNION等字句,也經(jīng)常導致Order運算,。 e),、不要使用Select count(*)方式來判斷記錄是否存在,建議使用Select top 1 from table1 where ……,。 f),、不要使用Group by而沒有聚合列。 g),、避免Select 語句的Where 字句條件用于假,。如:where 1=0; h)、如果有多表連接時,,應該有主從表之分,,并盡量從一個表讀取數(shù),,如select a.col1,a.col2 from a jion b on a.col3=b.col4 where b.col5=’a’. i)、在where 字句中,,如果有多個過濾條件,,應將所有列或過濾記錄數(shù)量最多的條件應該放在前面。 二,、使用Truncate清空表 Truncate會將表中記錄全部清空,,而不能有選擇性的刪除指定記錄。而DELETE可以指定刪除的記錄,。由于Truncate操作在TransactionLog中只記錄被Truncate的頁號,,而DELETE需要記載被刪除記錄的詳細內(nèi)容,因此Truncate會比DELETE更迅速,。對大數(shù)據(jù)表使用Truncate,,效果更加明顯。Truncate Table只會刪除表中記錄,。而不會對表的索引和結構造成影響,。 三、Union和Union all Union將兩個結果集合并后,,會消除重復記錄,,而Union all不會消除重復記錄,而是直接將兩個結果集直接合并,。明確得知兩個結果集中沒有重復記錄或者重復記錄不影響使用,,建議使用Union all 代替Union。因為Union在消除重復記錄的過程中需要進行排序過濾操作,,對大結果集這種排序操作會非常影響性能,。下面是Union 和Union all的簡單性能比較: ---------------Union select * from table1 where code=’01’ Union select * from table1 where code=’02’ ---------------Union all select * from table1 where code=’01’ union all select * from talbe1 where code=’02’ ============================================================================================== |
|
來自: 賈朋亮博客 > 《性能優(yōu)化》