大型信息管理系統(tǒng)數(shù)據(jù)庫(kù)的設(shè)計(jì)與優(yōu)化

謝搶來(lái)

0 引言

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

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

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

2 實(shí)驗(yàn)對(duì)比

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

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

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

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

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

2.2 查詢的優(yōu)化

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

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

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

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

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

(6)避免使用游標(biāo)，因?yàn)橛螛?biāo)的效率非常差，當(dāng)游標(biāo)操作的數(shù)據(jù)大于1萬(wàn)條時(shí)，就應(yīng)該考慮改寫；

2.3 算法的優(yōu)化

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

語(yǔ)句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；

語(yǔ)句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；

語(yǔ)句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；

語(yǔ)句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ǔ)法優(yōu)化查詢結(jié)果

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

SQL SERVER 雖然只能建立一個(gè)唯一的聚集索引，但經(jīng)常會(huì)出現(xiàn)同時(shí)多個(gè)字段都需要建立聚集索引的情況，這時(shí)通常可以把他們合并一起建立一個(gè)復(fù)合索引，也并非所有的字段都合適加入到聚集索引里面，需要根據(jù)實(shí)際情況進(jìn)行權(quán)衡選擇。

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

表5 根據(jù)實(shí)驗(yàn)測(cè)試百萬(wàn)級(jí)數(shù)據(jù)查詢性能情況

(3)其它事項(xiàng)

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

3 結(jié)論

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

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

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

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

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