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

夏志君+張榮福+鄭傳生

摘 要：根據(jù)市場調(diào)查和行業(yè)分析，高通量菌落挑選儀在優(yōu)質(zhì)菌落的篩選過程中應(yīng)用越來越廣泛。通過對(duì)高通量菌落挑選儀的分析研究，設(shè)計(jì)了一個(gè)基于C/S架構(gòu)的菌落信息管理系統(tǒng)，解決高通量菌落挑選儀在工作過程中的數(shù)據(jù)管理問題。該系統(tǒng)使用C#語言，以SQL Sever 2008作為底層數(shù)據(jù)庫，建立數(shù)據(jù)庫關(guān)系模型，通過高通量菌落挑選儀上位機(jī)實(shí)現(xiàn)與其數(shù)據(jù)交互。同時(shí)從數(shù)據(jù)庫表結(jié)構(gòu)、索引及SQL語句等方面對(duì)數(shù)據(jù)庫進(jìn)行了優(yōu)化，提高系統(tǒng)存儲(chǔ)和查找效率，節(jié)省了系統(tǒng)資源開銷。

關(guān)鍵詞：菌落挑選儀；數(shù)據(jù)訪問；SQL Server數(shù)據(jù)庫；數(shù)據(jù)庫優(yōu)化；信息管理

DOIDOI：10.11907/rjdk.171956

中圖分類號(hào)：TP319

文獻(xiàn)標(biāo)識(shí)碼：A 文章編號(hào)文章編號(hào)：1672-7800（2017）008-0076-03

0 引言

隨著微生物、醫(yī)藥衛(wèi)生等行業(yè)的迅猛發(fā)展，基于菌落形態(tài)學(xué)特征的篩選技術(shù)對(duì)獲得合格菌落的重要性越來越大。高通量菌落挑選儀正是基于這一技術(shù)的自動(dòng)化菌落挑選設(shè)備，能從眾多培養(yǎng)出來的菌落中篩選出符合條件的微生物，供后續(xù)培養(yǎng)繁殖，提高優(yōu)質(zhì)菌株的培養(yǎng)效率。而菌落信息管理系統(tǒng)的設(shè)計(jì)目的是將其作為高效的數(shù)據(jù)處理系統(tǒng)輔助高通量菌落挑選儀完善其功能，實(shí)現(xiàn)對(duì)儀器收集數(shù)據(jù)的整理、存儲(chǔ)及分析，它是高通量菌落挑選儀不可或缺的重要組成部分。但隨著存儲(chǔ)數(shù)據(jù)的不斷增多，系統(tǒng)通過全表掃描來查找相關(guān)信息將會(huì)耗費(fèi)較長時(shí)間。因此，對(duì)數(shù)據(jù)庫進(jìn)行優(yōu)化是提升菌落信息管理系統(tǒng)性能的重要內(nèi)容。

1 系統(tǒng)設(shè)計(jì)

1.1 軟件結(jié)構(gòu)設(shè)計(jì)

本系統(tǒng)軟件模塊采用分層結(jié)構(gòu)設(shè)計(jì)，分為界面顯示層、邏輯關(guān)系層和數(shù)據(jù)訪問層三層結(jié)構(gòu)，它們既彼此獨(dú)立又相互聯(lián)系，構(gòu)成了系統(tǒng)軟件結(jié)構(gòu)的骨架。此結(jié)構(gòu)將系統(tǒng)的各個(gè)部分按照其調(diào)用的依賴關(guān)系分成不同的層級(jí)，且各個(gè)層級(jí)之間只能實(shí)現(xiàn)單項(xiàng)調(diào)用，不能越級(jí)調(diào)用，這樣的設(shè)計(jì)在保證系統(tǒng)運(yùn)行流暢的同時(shí)也提高了系統(tǒng)的運(yùn)行效率[1]。其中，邏輯關(guān)系層主要處理系統(tǒng)中的數(shù)據(jù)邏輯關(guān)系，如接種信息與菌落信息的匹配關(guān)系等。系統(tǒng)層級(jí)關(guān)系如圖1所示。

1.2 數(shù)據(jù)庫基本表設(shè)計(jì)

高通量菌落挑選儀是集挑選與接種于一體的自動(dòng)化菌落優(yōu)選裝置，可以對(duì)平皿菌落進(jìn)行智能識(shí)別和自動(dòng)優(yōu)選。菌落信息管理系統(tǒng)作為高效的數(shù)據(jù)處理系統(tǒng)支撐挑選儀完成其功能，是整個(gè)菌落操作過程中的重要組成。高通量菌落挑選儀的數(shù)據(jù)流圖如圖2所示。

設(shè)計(jì)合理的系統(tǒng)相關(guān)表有利于后期對(duì)數(shù)據(jù)庫進(jìn)行優(yōu)化。因此，在本系統(tǒng)的數(shù)據(jù)庫基本表設(shè)計(jì)中，遵循的范式是第三范式，設(shè)計(jì)出來的數(shù)據(jù)管理系統(tǒng)冗余較小，減少了CRUD操作異常。通過對(duì)菌落信息管理系統(tǒng)的相關(guān)數(shù)據(jù)進(jìn)行分析，結(jié)合用戶需求，本系統(tǒng)設(shè)計(jì)了6張表，各表之間通過外鍵或中間表相連，它們分別是操作員信息表、實(shí)驗(yàn)信息表、挑選配置信息表、清洗消毒信息表、菌落信息表和接種信息表，如表1所示。

1.3 數(shù)據(jù)庫模型

數(shù)據(jù)模型的發(fā)展過程先后經(jīng)歷了層次模型、網(wǎng)狀模型和關(guān)系模型3個(gè)階段[2]，其中關(guān)系模型應(yīng)用最為廣泛。關(guān)系模型用滿足相關(guān)條件的二維表結(jié)構(gòu)對(duì)不同的實(shí)體進(jìn)行描述，并依據(jù)各實(shí)體自身情況來建立彼此聯(lián)系，再對(duì)數(shù)據(jù)進(jìn)行相關(guān)代數(shù)和邏輯關(guān)系運(yùn)算。本系統(tǒng)關(guān)系數(shù)據(jù)庫正是基于該模型建立而成。通過一組具有正式描述性的表格對(duì)數(shù)據(jù)庫中的數(shù)據(jù)進(jìn)行相關(guān)描述，并以集合代數(shù)的方法對(duì)其進(jìn)行相應(yīng)的處理，而這些建立在被不同表格所描述的數(shù)據(jù)之間關(guān)系的集合就構(gòu)成了一個(gè)關(guān)系數(shù)據(jù)庫[3]。本系統(tǒng)數(shù)據(jù)信息實(shí)體關(guān)系如圖3所示。

1.4 ADO.NET數(shù)據(jù)訪問技術(shù)

菌落信息管理系統(tǒng)通過ADO.NET數(shù)據(jù)訪問組件實(shí)現(xiàn)儀器上位機(jī)對(duì)關(guān)系數(shù)據(jù)庫中數(shù)據(jù)的訪問。ADO是一組優(yōu)化的數(shù)據(jù)庫訪問專用對(duì)象集，為ASP提供了完整的站點(diǎn)數(shù)據(jù)庫解決方案，它作用在服務(wù)器端，提供含有數(shù)據(jù)庫信息的內(nèi)容，通過執(zhí)行SQL命令，讓用戶在系統(tǒng)主界面中存儲(chǔ)、更新和刪除相關(guān)數(shù)據(jù)庫信息[4]。

（1）Connection：連接數(shù)據(jù)對(duì)象。Connection對(duì)象用于系統(tǒng)連接本地?cái)?shù)據(jù)庫?；诒镜豐QL Server 2008的高通量菌落挑選儀數(shù)據(jù)庫創(chuàng)建和打開連接的核心代碼如下：

string connectionStr=@” Integrated Security=SSPI； Data Source=localhost； Initial Catalig=SCP”；

SqlConnection conn=new SqlConnection （connectionStr）；

conn.Open （）；

（2）Command：操作數(shù)據(jù)對(duì)象；DataReader：讀取數(shù)據(jù)對(duì)象。Command對(duì)象以一個(gè)SQL語句的字符串為命令操作數(shù)據(jù)庫，而DataReader對(duì)象可以接收命令執(zhí)行結(jié)果。以查詢高通量菌落挑選儀在2017年1月1日所有的接種信息為例，代碼如下：

string cmdStr=”Select * From tb_PickingInformation Where Datatime=2017-01-01”；

SqlCommand dbCmd=new SqlCommand （cmdStr，conn）；

SqlDataReader dbReader=new dbCmd.ExcuteReader （）；

2 功能模塊

本系統(tǒng)通過C#進(jìn)行可視化Winform窗體界面設(shè)計(jì)，根據(jù)高通量菌落挑選儀的功能需求，主要分為3個(gè)功能模塊，分別為信息存儲(chǔ)模塊、信息管理模塊和備份恢復(fù)模塊，并通過相關(guān)控件實(shí)現(xiàn)對(duì)應(yīng)功能。

2.1 信息存儲(chǔ)模塊

信息存儲(chǔ)模塊是菌落信息管理系統(tǒng)的重要組成部分。作為對(duì)高通量菌落優(yōu)選儀進(jìn)行數(shù)據(jù)處理的信息管理系統(tǒng)，本模塊會(huì)在儀器工作過程中采集相應(yīng)數(shù)據(jù)，通過與數(shù)據(jù)庫進(jìn)行交互完成信息整理和存儲(chǔ)。系統(tǒng)信息采集流程如圖4所示，其中操作員信息、過程設(shè)置信息和清洗消毒信息在儀器挑選前完成存儲(chǔ)，菌落信息在儀器探針回到原點(diǎn)后進(jìn)行采集存儲(chǔ)。此外，系統(tǒng)會(huì)根據(jù)操作員設(shè)置決定是否將基本參數(shù)信息和挑選接種信息保存至數(shù)據(jù)庫。以系統(tǒng)存儲(chǔ)菌落信息為例，相關(guān)代碼如下：endprint

string sqlstr = "insert into tb_ColonyInformation（孔板號(hào)，接種孔位置，質(zhì)心坐標(biāo)X，質(zhì)心坐標(biāo)Y，面積，周長，長徑，短徑，長短徑比，R，G，B，保存日期，保存時(shí)間） values（'" + RecordList[i - 1].NoInDic.ToString（） + "'，'" + RecordList[i - 1].IndexOfProbe.ToString（） + "'，'" + Chara [i].Centroid.X.ToString（） + "'，'" + Chara [i].Centroid.Y.ToString（） + "'，'" + Chara [i].Area.ToString（） + "'，'" + Chara [i].Perimeter.ToString（） + "'，'" + Chara [i].MajorAxis.ToString（） + "'，'" + Chara [i].MinorAxis.ToString（） + "'，'" + Chara [i].MajToMinAxisRatio.ToString（） + "'，'" + Chara [i].CentreAcerageColor.R.ToString（） + "'，'" + Chara [i].CentreAcerageColor.G.ToString（） + "'，'" + Chara [i].CentreAcerageColor.B.ToString（） + "'，'" + DateTime.Now.ToString（"yyyy-MM-dd"） + "'，'" + DateTime.Now.ToLongTimeString（）.ToString（） + "'）"；

dataOperate.DataCom（sqlstr）；

2.2 信息管理模塊

信息管理模塊的主要功能是對(duì)菌落來源信息進(jìn)行跟蹤、回溯，即在后期菌落繁殖中，通過本模塊可以對(duì)繁殖快、抗污染的優(yōu)質(zhì)菌株追根溯源，提高優(yōu)質(zhì)菌株的培養(yǎng)效率。本模塊管理與儀器有關(guān)的操作員信息、配置信息、過程設(shè)置信息、清洗消毒信息、接種信息和菌落特征信息，能夠?qū)π畔⑦M(jìn)行檢索查詢、刪除清空、數(shù)據(jù)輸出等操作，其中數(shù)據(jù)輸出是將上位機(jī)選中的數(shù)據(jù)庫表格導(dǎo)出成Excel。以查詢挑選配置表為例，相關(guān)代碼如下：

sqlstr = "select*from tb_PickingInformation"；

tb = " tb_PickingInformation"；

SqlDataAdapter sqldataadapter = new SqlDataAdapter（sqlstr，sqlconn）；

DataSet mydataset = new DataSet（）；

sqldataadapter.Fill（mydataset，tb）；

this.dataGridView1.DataSource = mydataset.Tables[tb]

2.3 備份恢復(fù)模塊

備份恢復(fù)模塊分為數(shù)據(jù)備份和數(shù)據(jù)恢復(fù)兩部分，主要用于預(yù)防突發(fā)情況造成系統(tǒng)重要信息的破壞。本模塊默認(rèn)數(shù)據(jù)備份路徑為D：＼＼ Conoly，可手動(dòng)備份或自動(dòng)備份。自動(dòng)備份SQL語句："backup database MyDatabase to disk='D：＼＼ Conoly＼＼MyDatabase.bak'？"。而數(shù)據(jù)還原會(huì)掃描備份路徑下的全部子文件。自動(dòng)還原SQL語句："restore database MyDatabase from？disk=' D：＼＼ Conoly＼＼MyDatabase.bak' "。

3 數(shù)據(jù)庫性能優(yōu)化

3.1 優(yōu)化需求

對(duì)數(shù)據(jù)的處理和查詢速度已成為衡量系統(tǒng)性能的重要指標(biāo)。隨著高通量菌落挑選儀的不斷工作，數(shù)據(jù)庫各表中的記錄會(huì)持續(xù)增加，系統(tǒng)響應(yīng)速度也會(huì)越來越慢，因此有必要對(duì)數(shù)據(jù)庫進(jìn)行性能優(yōu)化，提高系統(tǒng)工作效率?；赟QL Server數(shù)據(jù)庫的性能優(yōu)化方法有很多種，例如選擇性能優(yōu)越的服務(wù)器、合理使用事務(wù)和批處理以及在應(yīng)用程序開發(fā)時(shí)使用合適的工具等。本系統(tǒng)除了規(guī)范基本表結(jié)構(gòu)外還通過索引優(yōu)化和SQL語句優(yōu)化兩種方法提升系統(tǒng)性能。

3.2 性能優(yōu)化方法

3.2.1 索引優(yōu)化

使用索引能大大加快數(shù)據(jù)庫查詢速度[5]。查詢執(zhí)行的大部分開銷是I/O ，索引優(yōu)化能提升系統(tǒng)性能的主要原因是避免了全表掃描。全表掃描會(huì)從磁盤讀取表的所有數(shù)據(jù)頁，若建立索引指向數(shù)據(jù)值，查詢只需讀較少次的磁盤。但索引不能建立過多，會(huì)占用磁盤空間和增加數(shù)據(jù)庫維護(hù)開銷。本系統(tǒng)對(duì)接種信息表中pickNum建立索引，以執(zhí)行SQL語句"SELECT * FROM tb_PickingInformation WHERE pickNum= 250000；"為例查詢一條記錄，結(jié)果如表2所示，對(duì)比發(fā)現(xiàn)，建立索引后系統(tǒng)查詢時(shí)間明顯縮短。

3.2.2 語句優(yōu)化

對(duì)于菌落信息管理系統(tǒng)而言，SQL語句獨(dú)立于應(yīng)用程序設(shè)計(jì)，比優(yōu)化系統(tǒng)源代碼的風(fēng)險(xiǎn)小、成本低。此外，SQL語句是對(duì)數(shù)據(jù)庫中數(shù)據(jù)進(jìn)行操作的唯一方式，因此優(yōu)化SQL語句對(duì)提升系統(tǒng)性能起到重要作用。本系統(tǒng)從WHERE子句、避免使用游標(biāo)和分頁查詢?nèi)矫鎸?duì)SQL語句進(jìn)行優(yōu)化：

（1）WHERE子句。在數(shù)據(jù)查詢時(shí)，通過WHERE子句限定查詢條件能夠避免查詢大量無用數(shù)據(jù)，在WHERE涉及的列上建立索引，可以提高查詢效率。同時(shí)，避免在WHERE子句中對(duì)字段進(jìn)行null 值判斷，否則將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描。此外，盡量減少在WHERE子句中對(duì)字段進(jìn)行表達(dá)式操作或函數(shù)操作，這也會(huì)導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描。

（2）避免使用游標(biāo)。游標(biāo)的使用能讓系統(tǒng)對(duì)查詢返回結(jié)果集的每行進(jìn)行處理，能基于游標(biāo)位置對(duì)表中數(shù)據(jù)刪除或更新。但隨著菌落挑選儀的不斷工作，數(shù)據(jù)庫中存儲(chǔ)的信息會(huì)越來越龐大，如果建立游標(biāo)，在處理數(shù)據(jù)時(shí)相關(guān)信息會(huì)被鎖定，直至數(shù)據(jù)遍歷完成。鑒于使用游標(biāo)處理大量數(shù)據(jù)時(shí)， 效率低下、占用內(nèi)存大，因此避免使用游標(biāo)。

（3）分頁查詢。數(shù)據(jù)庫中各表的記錄數(shù)很大，滿足查詢條件的記錄可能會(huì)很多，若將查詢結(jié)果一次性顯示出來會(huì)降低查詢效率。通過row_number（）解析函數(shù)完成分頁查詢能解決此類問題，即根據(jù)查詢條件返回前n條記錄，需要時(shí)再返回n+1條以后的記錄。

4 結(jié)語

本文介紹了菌落信息管理系統(tǒng)的軟件結(jié)構(gòu)、功能模塊和數(shù)據(jù)訪問技術(shù)等內(nèi)容，并通過數(shù)據(jù)庫優(yōu)化提升系統(tǒng)性能。通過實(shí)際測(cè)試，整個(gè)系統(tǒng)運(yùn)行穩(wěn)定，功能達(dá)到設(shè)計(jì)要求。系統(tǒng)較數(shù)據(jù)庫優(yōu)化前能快速、準(zhǔn)確地存儲(chǔ)和查詢相關(guān)信息，能通過上位機(jī)控件操作數(shù)據(jù)庫中各表數(shù)據(jù)，具備良好的平臺(tái)無關(guān)性及拓展性。當(dāng)菌落后期繁殖培養(yǎng)出優(yōu)質(zhì)菌株時(shí)，系統(tǒng)能準(zhǔn)確回溯實(shí)驗(yàn)相關(guān)信息，加快優(yōu)質(zhì)菌株的培養(yǎng)速度，很好地促進(jìn)了高通量菌落挑選儀功能的完善。

參考文獻(xiàn)：

[1] 梁紅碩.存儲(chǔ)過程在C#數(shù)據(jù)庫編程中的應(yīng)用[J].科技風(fēng)，2014（1）：83-83.

[2] 陳國紅.數(shù)據(jù)庫技術(shù)及其發(fā)展趨勢(shì)[J].紡織高?；A(chǔ)科學(xué)學(xué)報(bào)，2001，14（3）：244-248.

[3] 王曉鶴.數(shù)據(jù)融合中的數(shù)據(jù)庫技術(shù)[D].西安：西北工業(yè)大學(xué)，2004.

[4] 莫停飛，白振興，鄧善.C#環(huán)境下利用ADO與SQL Server 2000數(shù)據(jù)庫的鏈接[J].現(xiàn)代電子技術(shù)，2004，27（9）：39-41.

[5] 傅向華，馮博琴，王小民，等.一種基于數(shù)據(jù)訪問流的數(shù)據(jù)庫索引優(yōu)化方法[J].計(jì)算機(jī)工程，2007，33（12）：99-101.endprint