謝搶來

0 引言

通過對信息管理系統(tǒng)數(shù)據(jù)庫產(chǎn)生瓶頸的原因進行反復研究分析，主要存在不同量級的數(shù)據(jù)優(yōu)化的思路不同，數(shù)據(jù)的量級是隨著時間的推移而提高的。絕大部分系統(tǒng)分析師只會對遇到的當前量級數(shù)據(jù)逐步提出優(yōu)化方案，例如：1萬級無需優(yōu)化、10萬級排查數(shù)據(jù)結(jié)構(gòu)的合理性、100 萬級建立合理的索引。這種優(yōu)化思路形成了反復給性能修復補丁，并沒有一次性解決問題，每個量級的數(shù)據(jù)性能修復補丁變得更加艱難。

1 基于不同量級數(shù)據(jù)優(yōu)化的改進

為了確保數(shù)據(jù)庫結(jié)構(gòu)的統(tǒng)一原則，在邏輯設(shè)計階段表與表之間經(jīng)常會設(shè)計過多的關(guān)聯(lián)，盡可能的減少數(shù)據(jù)冗余。但實際應用當中，雖然數(shù)據(jù)冗余低會使數(shù)據(jù)的完整性得到保證，提高了數(shù)據(jù)吞吐率，能夠清晰地表述出數(shù)據(jù)屬性之間的關(guān)系；然而當數(shù)據(jù)庫足夠龐大的時候，多表之間關(guān)聯(lián)頻繁會降低查詢的性能，同時也加大了客戶端程序編程的難度；因此，在物理設(shè)計階段需要折中考慮，根據(jù)實際業(yè)務(wù)需求，確定相互存在關(guān)聯(lián)數(shù)據(jù)表的最大數(shù)據(jù)容量和字段屬性的訪問頻次，對此類數(shù)據(jù)表做頻繁關(guān)聯(lián)查詢應該適當并合理的提高數(shù)據(jù)冗余，為了提高查詢性能、系統(tǒng)響應速度，合理的提高數(shù)據(jù)冗余是必須的。真實系統(tǒng)的數(shù)據(jù)庫設(shè)計階段應該根據(jù)字段類型、查詢語句、算法、索引等多方面進行權(quán)衡考慮。

2 實驗對比

2.1 數(shù)據(jù)表設(shè)計的優(yōu)化

(1)數(shù)據(jù)庫表命名將業(yè)務(wù)表與基礎(chǔ)表區(qū)分，采用集成基礎(chǔ)庫分布式數(shù)據(jù)庫設(shè)計思路；

(2)字段的類型選擇優(yōu)先級數(shù)字、浮點、字符、文本、二進制，能夠使用基本類型的盡量選擇基本類型，如果強行選擇其它優(yōu)先級低的數(shù)據(jù)類型會增加存儲開銷，降低查詢和連接的性能；

(3)謹慎區(qū)分char 和nvarchar 兩種字符類型，不可變長字符類型char 查詢速度快，但會增加硬盤的存儲空間，可變長字符類型nvarchar查詢速度雖然相對慢一點，但是節(jié)省硬盤的存儲空間；在設(shè)計字段的時候可以靈活選擇，針對內(nèi)容固定長度的數(shù)據(jù)選擇char，例如性別、身份證號字段；內(nèi)容長度變化差距很大數(shù)據(jù)選擇nvarchar，例如地址、標題；

(4)字段長度設(shè)計時，應該根據(jù)實際業(yè)務(wù)需求的最大限度前提下盡可能的簡短，滿足需求即可，這種做法可以大大的提高查詢性能，并且在建立字段索引時也能減少資源的消耗。

2.2 查詢的優(yōu)化

(1)程序在確保功能實現(xiàn)的基礎(chǔ)上，對數(shù)據(jù)庫訪問建立的連接次數(shù)盡可能的少，并且每次數(shù)據(jù)庫連接使用結(jié)束之后必須關(guān)閉連接，做到建立連接和關(guān)閉連接一一對應；

(2)盡量避免向用戶端返回過多的數(shù)據(jù)量，如果數(shù)據(jù)量較大，應該考慮業(yè)務(wù)需求分析當中是否合理，通過查詢條件，盡可能縮小對數(shù)據(jù)表的訪問行數(shù)和結(jié)果集，從而降低網(wǎng)絡(luò)傳輸過程的壓力；

(3)盡量避免使用select*from Table，一定要用具體的字段名的列表來代替“*”，無需返回業(yè)務(wù)邏輯中用不上的任何字段；

(4)構(gòu)建SQL 查詢語句時，盡可能把要求使用的索引放在where條件的首列；

(5)where條件語句中的等于（=）運算不要在左邊進行函數(shù)、算術(shù)或表達式運算，否則數(shù)據(jù)庫索引可能會失效；

(6)避免使用游標，因為游標的效率非常差，當游標操作的數(shù)據(jù)大于1萬條時，就應該考慮改寫；

2.3 算法的優(yōu)化

SQL 語句中經(jīng)常需要融合復雜的算法來解決業(yè)務(wù)邏輯問題，數(shù)據(jù)庫越大算法的瓶頸越容易暴漏出來，在此針對不同的分頁語句在不同的數(shù)據(jù)量級別進行測試分析，優(yōu)化實驗結(jié)果如下：。

語句1：not in/top

select top 20字段列表 from Table where TableKey not in (select top 8800 TableKey from Table order by TableKey)order by TableKey；

語句2：not exists

select top 20字段列表from Table where not exists (select 1 from (select top 8800 TableKey from Table order by TableKey) a where a.id= Table. TableKey) order by TableKey；

語句3：max/top

select top 20字段列表 from Table where TableKey ＞(select max(TableKey) from (select top 8800 TableKey from Table order by TableKey) a) order by TableKey；

語句4：row_number()

select 字段列表from(select row_number()over(order by TableKey) r_number, 字段列表from Table) a where r_number ＞8800 and r_number ＜8821；

表1 SQL語法優(yōu)化查詢結(jié)果

2.4 合理建立高效的索引進行優(yōu)化

SQLServer 數(shù)據(jù)庫建立索引有兩個目的：確保索引字段的唯一性、實現(xiàn)快速查詢數(shù)據(jù)的目的，企業(yè)級數(shù)據(jù)庫系統(tǒng)都包括聚集索引和非聚集索引兩種索引，非聚集索引的表的數(shù)據(jù)是根據(jù)Heap 結(jié)構(gòu)存儲的數(shù)據(jù)，將全部的數(shù)據(jù)添加在表的尾部，聚集索引的表的數(shù)據(jù)是根據(jù)索引字段的順序存儲，并且數(shù)據(jù)表的聚集索引獨有唯一性。

聚集索引：數(shù)據(jù)庫表的數(shù)據(jù)是根據(jù)索引字段的順序存儲，索引項的順序與表中記錄的物理存儲順序必須保持一致；對于聚集索引不需要再有另外單獨的數(shù)據(jù)頁，因此，每張數(shù)據(jù)表中最多只能創(chuàng)建唯一的一個聚集索引。

非聚集索引：數(shù)據(jù)庫表的數(shù)據(jù)記錄存儲順序與索引字段順序無關(guān)，非聚集索引采用葉結(jié)點的數(shù)據(jù)頁和數(shù)據(jù)行中邏輯指針指向索引字段值，因此，邏輯行數(shù)量與數(shù)據(jù)表行數(shù)據(jù)量完成保持一致。

(1)建立高效索引的思路

表2 聚集索引或非聚集索引建立的思路

(2)結(jié)合實際情況淺談索引使用過程中的誤區(qū)

理論的目的是應用，應用的次數(shù)越多，經(jīng)驗也將越豐富，上述簡單羅列出何時使用聚集索引或非聚集索引，但在現(xiàn)實數(shù)據(jù)庫設(shè)計規(guī)則的時候很容易被忽視，不能完全根據(jù)實際情況進行合理運用。下面將根據(jù)在現(xiàn)實系統(tǒng)應用當中遇到的問題來詳細分析索引使用存在的誤區(qū)。

誤區(qū)一：主鍵就是聚集索引

通常習慣在每個數(shù)據(jù)表中都建立一個自動增長的TableKey 列或以Gid 為值的列為主鍵，像SQL SERVER數(shù)據(jù)庫系統(tǒng)就會將它默認為聚集索引，類似于這樣的聚集索引并不能完全發(fā)揮最大的性能優(yōu)勢；要想使用聚集索引的達到最大性能優(yōu)勢,應該是根據(jù)查詢中的條件縮小范圍和避免全表掃描，某種情況下使用TableKey主鍵作為聚集索引是一種資源浪費。

在無紙化網(wǎng)絡(luò)辦公系統(tǒng)的公文、會議、督辦等模塊中，無論是首頁提示用戶待簽收的公文、會議提醒、督辦提醒，還是用戶進行已辦公文、會議、督辦等查詢操作，只要是按需進行數(shù)據(jù)查詢都將離不開字段的是“時間”和用戶的“人員id”。雖然where 語句可以限制當前用戶尚未簽收的數(shù)量情況，但如果一個辦公系統(tǒng)使用的時間較長，并且數(shù)據(jù)量較大，甚至上升至百萬級、千萬級數(shù)據(jù)量；這個時候首頁的待辦提醒完全不需要進行全表掃描，因為絕大多數(shù)的用戶可能1 個月前的公文都已經(jīng)簽收完成了；事實上，根據(jù)業(yè)務(wù)實際情況，完全可以讓用戶訪問首頁的時候，只查詢近3個月的未簽收公文即可，可以通過“時間”這個字段建立聚集索引來限制全表掃描，提高查詢性能與速度。

表3 主鍵作為聚集索引的檢索性能情況

表4 時間作為聚集索引的檢索性能情況

誤區(qū)二：建立索引就一定能夠提高數(shù)據(jù)查詢的性能與速度

兩條完全相同的SQL 語句：select TableKey from Table where 時間＞’2022-01-20’and 時間＜’2022-01-21’，并且針對同一個date 字段建立索引；索引區(qū)別在于第一種方案是對“時間”字段建立非聚集索引，第二種方案是對“時間”字段建立聚集索引，但兩種方案的查詢速度卻有著很大的差距。所以，并不是所有字段上只要建立索引就一定能夠提高查詢性能與速度。

如何才能建立合適的索引應該根據(jù)數(shù)據(jù)的分布情況加以分析，例如：像無紙化網(wǎng)絡(luò)辦公系統(tǒng)公文表中有著百萬級數(shù)據(jù)量的“時間”字段有著上千條不同日期的記錄，同一個日期又存在若干條公文記錄，根據(jù)建立高效索引的思路得出在此字段上建立聚集索引是最佳的選擇。

誤區(qū)三：只要提高數(shù)據(jù)查詢性能與速度的字段就全部加聚集索引

SQL SERVER 雖然只能建立一個唯一的聚集索引，但經(jīng)常會出現(xiàn)同時多個字段都需要建立聚集索引的情況，這時通?？梢园阉麄兒喜⒁黄鸾⒁粋€復合索引，也并非所有的字段都合適加入到聚集索引里面，需要根據(jù)實際情況進行權(quán)衡選擇。

復合索引查詢性能的主要體現(xiàn)是查詢條件中是否用到了索引中的全部列。比如：根據(jù)無紙化網(wǎng)絡(luò)辦公系統(tǒng)公文中的“人員id”和“時間”字段，通過分析這兩個字段都非常重要，并且基本上都會同時出現(xiàn)在查詢條件當中，那么就可以將它們合并建立一個復合的聚集索引，并且“時間”為起始列、“人員id”排在后列。

表5 根據(jù)實驗測試百萬級數(shù)據(jù)查詢性能情況

(3)其它事項

只有建立合理的索引才有利于提高數(shù)據(jù)查詢的性能，如果過多或者不當?shù)慕⑺饕龝е孪到y(tǒng)此產(chǎn)生更嚴重的瓶頸，因為每一個索引都會導致存儲空間的增加和數(shù)據(jù)庫會做更多復雜的工作，并且產(chǎn)生大量的索引碎片；所以，要想建立一個合理的索引體系，需要融合更多的實戰(zhàn)應用分析，結(jié)合調(diào)優(yōu)結(jié)果進行精益求精建立索引，才能使數(shù)據(jù)庫的性能達到最佳的狀態(tài)。

3 結(jié)論

綜上所述，并且在大型信息管理系統(tǒng)中的數(shù)據(jù)庫設(shè)計和優(yōu)化進行反復論證，本文針對數(shù)據(jù)庫設(shè)計和優(yōu)化提出如下幾點思路：

(1)數(shù)據(jù)表中每一個字段的設(shè)計都必須非常嚴謹，比如數(shù)據(jù)類型選擇、長度設(shè)計等；

(2)查詢語句的優(yōu)化是SQL效率優(yōu)化的一個方式,可以通過優(yōu)化sql 語句來盡量使用已有的索引,避免全表掃描,從而提高查詢效率；

(3)不斷優(yōu)化復雜的算法來解決數(shù)據(jù)量大的業(yè)務(wù)邏輯問題；

(4)建立最合理的索引體系可以大大提高系統(tǒng)的性能。