基于MySQL的數(shù)據(jù)庫查詢性能優(yōu)化技術(shù)研究

王景

（甘肅交通職業(yè)技術(shù)學(xué)院，甘肅 蘭州 730070）

1 引言

當(dāng)下局域網(wǎng)內(nèi)的數(shù)據(jù)海量化、數(shù)據(jù)庫查詢效率極低，且以SQL語句為主的數(shù)據(jù)查詢指令執(zhí)行過程中，存在著數(shù)據(jù)表索引創(chuàng)建滯后、數(shù)據(jù)遍歷方式不合理、數(shù)據(jù)類型與檢索條件不匹配、數(shù)據(jù)表聯(lián)接順序不準(zhǔn)確的問題。為達(dá)成數(shù)據(jù)信息查詢過程中后臺數(shù)據(jù)庫、磁盤I/O性能調(diào)取與優(yōu)化的目標(biāo)，本文提出依托MySQL、HBase數(shù)據(jù)庫、網(wǎng)絡(luò)微控制器、磁盤I/O等軟硬件，以MySQL關(guān)系型數(shù)據(jù)庫、數(shù)據(jù)表作為查詢語言，進(jìn)行企業(yè)內(nèi)部業(yè)務(wù)數(shù)據(jù)查詢的方案，將網(wǎng)絡(luò)業(yè)務(wù)數(shù)據(jù)的處理增加至50、100及以上的執(zhí)行線程，保證在短時間內(nèi)完成數(shù)據(jù)表索引、SQL查詢語句、分頁查詢方式、MySQL查詢緩存的操作執(zhí)行，并合理利用MySQL查詢緩存的處理方式，降低MySQL數(shù)據(jù)庫對后臺CPU、磁盤資源的消耗。

面對小范圍局域網(wǎng)內(nèi)數(shù)據(jù)總量不斷增長的發(fā)展趨勢，徐昂[1]提出優(yōu)化SQL檢索索引的方式，用于提升后臺數(shù)據(jù)庫的信息查詢效率；樂藝[2]提出改進(jìn)的布谷鳥搜索算法，設(shè)置數(shù)據(jù)查詢優(yōu)化的多個約束條件，大幅度改善數(shù)據(jù)庫查詢的效率和性能。岳彬森[3]、陳年飛[4]等針對MySQL數(shù)據(jù)庫中的數(shù)據(jù)索引查詢，提出BTree數(shù)據(jù)結(jié)構(gòu)、系統(tǒng)索引結(jié)構(gòu)、磁盤存儲的優(yōu)化策略。

本文通過以MySQL關(guān)系型數(shù)據(jù)庫、數(shù)據(jù)表形式，作為結(jié)構(gòu)化數(shù)據(jù)信息查詢的語言，設(shè)置SQL查詢語句、有效索引、字符集及用戶、分頁查詢模式、查詢緩存等功能，并在微控制器、磁盤I/O等的支持下進(jìn)行聯(lián)表查詢，優(yōu)化數(shù)據(jù)信息查詢的性能，盡可能減少信息查詢過程中的數(shù)據(jù)庫、磁盤I/O性能占用，提升外部用戶數(shù)據(jù)訪問、檢索的響應(yīng)速率和準(zhǔn)確率。

2 MySQL數(shù)據(jù)庫信息查詢的測試環(huán)境搭建

MySQL數(shù)據(jù)庫對后臺服務(wù)器的CPU、內(nèi)存、I/O等要求較高，因而本文選取IBMX3850系列處理器、IBM DS3512磁盤陣列，每臺磁盤陣列中配置8塊2T SAS硬盤，作為數(shù)據(jù)庫服務(wù)器，測試軟件使用“Windows+MySQL5.5”的關(guān)系型數(shù)據(jù)庫管理系統(tǒng)。在此基礎(chǔ)上安裝MySQL軟件包，以及MySQLdebuginfo、MySQL-devel、MySQL-server共享庫等的組件[5]，具體數(shù)據(jù)庫信息查詢的MySQL測試環(huán)境搭建流程如下：

（1）新建用戶并以安全方式運(yùn)行安裝進(jìn)程，執(zhí)行代碼為：groupadd-r mysql；useradd-g mysql-r-s/sbin/nologin-M-d/mydata/data mysql。

（2）將MySQL5.5.28版本安裝至本地，并對MySQL軟件作初始化設(shè)置。

（3）為MySQL軟件提供主配置文件：cd/usr/local/mysql；cp support-files/my-large.cnf/etc/my.cnf。添加MySQL數(shù)據(jù)文件的存放位置：datadir=/mydata/data。

（4）為MySQL軟件提供sysv服務(wù)腳本：cd/usr/local/mysql；cp support-files/mysql.server/etc/rc.d/init.d/mysqld。

（5）添加相應(yīng)腳本至服務(wù)列表，啟動服務(wù)測試：chkconfig--add mysqld；chkconfig mysqld on。

3 基于MySQL關(guān)系型數(shù)據(jù)庫的SQL語句信息查詢執(zhí)行流程

MySQL數(shù)據(jù)庫通常以SQL查詢語句，作為后臺庫內(nèi)數(shù)據(jù)表中一系列數(shù)據(jù)分組、數(shù)據(jù)添加、字段排序的執(zhí)行語句，分為SQL輸入—詞法掃描—語法分析—語義預(yù)處理—SQL優(yōu)化執(zhí)行的流程，具體如圖1所示。

（1）根據(jù)外部用戶的數(shù)據(jù)訪問、檢索查詢業(yè)務(wù)的邏輯要求，編寫用于信息查詢的SQL語句。

（2）在SQL語法規(guī)則支持下，利用詞法掃描器、語法分析器等設(shè)備，識別出SQL語句中包含的字符、字符串、單詞、空格等操作符，判斷SQL語句的關(guān)鍵字、關(guān)鍵詞、引號等的順序及匹配是否正確，若正確則生成語法分析樹。[6]

（3）隨后由預(yù)處理器為主導(dǎo)，進(jìn)行樹中各節(jié)點語法的檢查，檢驗生成語法分析樹的合法性，并生成新的語法分析樹，并將數(shù)據(jù)庫對象重名、別名、不存在等的錯誤信號，向用戶客戶端返回報告，但整體解析樹的結(jié)構(gòu)保持不變。

（4）利用查詢優(yōu)化器、以關(guān)系代數(shù)為基礎(chǔ)，對新的語法分析樹作出邏輯、物理優(yōu)化，進(jìn)行語法分析樹中各節(jié)點的語法調(diào)整，以及信息查詢順序、掃描方式、聯(lián)接算法等調(diào)整后，生成語法查詢樹。

（5）調(diào)用存儲引擎API、依據(jù)語法查詢樹，執(zhí)行網(wǎng)絡(luò)數(shù)據(jù)的查詢指令，并將最終的查詢執(zhí)行結(jié)果返回至用戶客戶端，即完成相應(yīng)數(shù)據(jù)信息的查詢操作。

圖1 MySQL數(shù)據(jù)庫的SQL語句信息查詢執(zhí)行流程

4 MySQL數(shù)據(jù)庫查詢性能的影響因素分析

MySQL關(guān)系型數(shù)據(jù)庫中，使用詞法掃描器、語法分析器、預(yù)處理器、查詢優(yōu)化器等設(shè)備，以及SQL語句進(jìn)行網(wǎng)絡(luò)信息查詢時，通過采取不同的執(zhí)行策略，包括對CPU利用率、I/O通信等待時長、網(wǎng)絡(luò)數(shù)據(jù)傳輸?shù)日{(diào)用方式，作出適宜的調(diào)整設(shè)置，可有效降低數(shù)據(jù)庫信息的查詢成本。當(dāng)下有關(guān)MySQL數(shù)據(jù)庫查詢性能的影響因素，主要包含索引創(chuàng)建、數(shù)據(jù)遍歷方式、數(shù)據(jù)類型兼容性、數(shù)據(jù)表聯(lián)接順序等內(nèi)容。

4.1 數(shù)據(jù)表索引創(chuàng)建的滯后

從特定數(shù)據(jù)提取關(guān)鍵字、關(guān)鍵詞，且定義不同數(shù)據(jù)之間的對應(yīng)關(guān)系、映射關(guān)系，根據(jù)數(shù)據(jù)表索引關(guān)系進(jìn)行關(guān)鍵字、關(guān)鍵詞、符號等字段的算法排序，可輔助存儲引擎API快速找到用戶需要的數(shù)據(jù)內(nèi)容，因而MySQL索引的建立，對后臺數(shù)據(jù)庫的信息查詢速率、查詢質(zhì)量而言至關(guān)重要。

但部分MySQL數(shù)據(jù)庫的Web網(wǎng)站空間信息查詢，卻未創(chuàng)建以SQL語句為主的適合索引，存儲引擎API也不能利用數(shù)據(jù)表索引，進(jìn)行where、having、order by等子句的關(guān)鍵列、關(guān)鍵字查詢，而只能依托后臺微處理器、存儲器、I/O端口被迫執(zhí)行全表掃描，增加陣列磁盤的荷載負(fù)擔(dān)。[7]

4.2 數(shù)據(jù)遍歷方式的不合理

當(dāng)前在MySQL數(shù)據(jù)庫中結(jié)構(gòu)性數(shù)據(jù)的查詢，通常會使用到for、for...in、for...of、while、do...while等的SQL語句，進(jìn)行某一行、列數(shù)據(jù)的集中遍歷，但在對關(guān)鍵字、關(guān)鍵詞索引數(shù)據(jù)(index)遍歷的過程中，可能會由于某些錯誤(value)變量的未聲明，造成非必要數(shù)據(jù)的遍歷、重復(fù)性遍歷。[8]如以下for語句的數(shù)據(jù)查詢遍歷代碼中，部分“List的待刪除元素”實際未被遍歷，而其他非必要數(shù)據(jù)被遍歷，由此延長數(shù)據(jù)訪問的響應(yīng)時間、降低索引對字段的檢索準(zhǔn)確率。

4.3 數(shù)據(jù)類型與檢索條件的不匹配

MySQL庫內(nèi)的數(shù)據(jù)類型與檢索條件不匹配，是數(shù)據(jù)表索引檢索面臨的另一問題，如使用“<>”“in”“not”“or”等的運(yùn)算符，以及選擇smallint、int等的數(shù)據(jù)類型，則極大可能會增加全表掃描操作符的概率，增大磁盤存儲的占用空間。同時在檢索條件中引入“<>”“in”“not”“or”等的操作符，將導(dǎo)致表內(nèi)使用屬性列、空值null進(jìn)行索引判斷，以至于降低數(shù)據(jù)字符類、字段的查詢速率。而一旦非兼容數(shù)據(jù)類型、特定檢索條件匹配完成后，則難以使用查詢優(yōu)化器，作出進(jìn)一步的語法邏輯分析、物理分析優(yōu)化操作。

4.4 數(shù)據(jù)表聯(lián)接順序的不準(zhǔn)確

數(shù)據(jù)表聯(lián)接順序?qū)ySQL數(shù)據(jù)庫的查詢性能至關(guān)重要，但部分?jǐn)?shù)據(jù)表的行列聯(lián)接，并未考慮到items表、stock表等數(shù)據(jù)表中相應(yīng)for、for...in、while子句長短的差異性。如通常情況下stock表更短、數(shù)據(jù)表聯(lián)接順序也更加合理，因而無需調(diào)用過多的后臺微處理器、存儲器、I/O接口負(fù)載，就可以完成局部指定數(shù)據(jù)的屬性列函數(shù)計算、索引表掃描。若采取items表的數(shù)據(jù)聯(lián)接順序，則很大可能會造成數(shù)據(jù)查詢中傳遞的總行數(shù)增加、數(shù)據(jù)表聯(lián)接速率降低。

5 MySQL數(shù)據(jù)庫查詢性能的實驗測試與結(jié)果

通過基于某一企業(yè)的內(nèi)部業(yè)務(wù)數(shù)據(jù)，圍繞MySQL、HBase、OpenTSDB等類型的數(shù)據(jù)庫，開展MySQL數(shù)據(jù)庫查詢性能的實驗測試，比較不同類別數(shù)據(jù)庫的查詢性能差異。在以上三類數(shù)據(jù)庫總數(shù)據(jù)條數(shù)、線程數(shù)存在差異的情況下，要求其進(jìn)行企業(yè)內(nèi)部業(yè)務(wù)數(shù)據(jù)的1000次查詢實驗，5次實驗查詢的數(shù)據(jù)量分別為1000、10000、39000、50000和100000，啟動的數(shù)據(jù)庫線程條數(shù)分別為1、10、50和100條，得出的數(shù)據(jù)庫查詢性能結(jié)果如圖2所示。[9]

圖2 三類數(shù)據(jù)庫的查詢性能的實驗測試結(jié)果

從圖2中不同類型數(shù)據(jù)庫的查詢性能測試結(jié)果可以得出：當(dāng)所需要查詢的內(nèi)部業(yè)務(wù)數(shù)據(jù)量較小情況下，使用MySQL數(shù)據(jù)庫查詢操作的吞吐量處于較低水平，不如HBase、OpenTSDB數(shù)據(jù)庫的數(shù)據(jù)查詢吞吐量。但當(dāng)網(wǎng)絡(luò)業(yè)務(wù)數(shù)據(jù)處理的執(zhí)行線程，增加至50、100個及以上的狀況下，MySQL數(shù)據(jù)庫的數(shù)據(jù)查詢吞吐量將快速增長，且基本與HBase、OpenTSDB數(shù)據(jù)庫的吞吐量持平，但MySQL數(shù)據(jù)庫對后臺CPU、磁盤資源的消耗更為嚴(yán)重，仍舊存在進(jìn)一步的性能優(yōu)化空間。

6 基于MySQL數(shù)據(jù)庫的網(wǎng)絡(luò)數(shù)據(jù)查詢性能優(yōu)化策略研究

6.1 創(chuàng)建有效數(shù)據(jù)表索引

MySQL數(shù)據(jù)庫中數(shù)據(jù)表索引的創(chuàng)建，是采用語法分析法開展物理查詢技術(shù)優(yōu)化的重中之重。通過創(chuàng)建包含數(shù)據(jù)行列值、存儲位置的索引表格，可輔助MySQL數(shù)據(jù)庫應(yīng)用程序，利用掃描索引數(shù)據(jù)表方式，包括主/外鍵屬性行、屬性列掃描，搜索外部用戶想快速找到的數(shù)據(jù)內(nèi)容，而無需對全表進(jìn)行全盤掃描。[10]

如針對MySQL數(shù)據(jù)庫內(nèi)的c1、c2、c3共10萬條數(shù)據(jù)，使用SELECT c1,c2,c3 FROM t1 WHERE c1(c2,c3)=50001的查詢語句，查詢測試數(shù)據(jù)表t1中的第50001條數(shù)據(jù)記錄，得出未建立索引、建立主鍵索引后的查詢用時(如圖3所示)，可以看出在c1(c2,c3)查詢條件中建立索引后，查詢用時縮短至0.001s。

圖3 未建立索引、建立索引后的查詢用時

6.2 SQL語句優(yōu)化

SQL語句作為網(wǎng)絡(luò)數(shù)據(jù)邏輯查詢技術(shù)優(yōu)化的手段，主要依據(jù)SQL語法查詢規(guī)則、關(guān)系代數(shù)理論，對SQL語句作出等價轉(zhuǎn)換。如between……and、IN、OR、LIKE等的關(guān)鍵運(yùn)算符，不支持條件判斷的索引掃描，因而可以用其他諸如>=、<、>=and<的運(yùn)算符進(jìn)行轉(zhuǎn)換?；蛘邔⑽催M(jìn)行數(shù)據(jù)分組、數(shù)據(jù)排序的SQL語句，重寫為多數(shù)據(jù)表連接的等價語句，這樣可以將子查詢的數(shù)據(jù)過濾條件與父查詢的過濾條件形成對接，減少某一個(類)數(shù)據(jù)查詢的執(zhí)行次數(shù)。

6.3 分頁查詢優(yōu)化

當(dāng)某一局域網(wǎng)內(nèi)的業(yè)務(wù)數(shù)據(jù)信息較多時，僅僅采用全表查詢檢索方式，其本身的數(shù)據(jù)查詢效率、查詢質(zhì)量非常低，且向用戶客戶端一次性展示過多數(shù)據(jù)，將造成查詢頁面的出錯或卡死。[11]因此，利用多次查詢顯示的“分頁查詢”方式，包括選用數(shù)據(jù)ID限定、數(shù)據(jù)條數(shù)限定的方法，設(shè)置Pic表查詢的id字段長度、數(shù)據(jù)條數(shù)長度。

如分別從某一數(shù)據(jù)表的100行、1000行、10000行處，查詢前10行、200~300行的數(shù)據(jù)，可提高多次查詢的響應(yīng)速度、縮短查詢時間，具體在數(shù)據(jù)ID限定、條數(shù)限定狀況下的分頁查詢性能如圖4所示。因而可以看出，數(shù)據(jù)ID限定查詢的響應(yīng)時間控制效果，要明顯優(yōu)于數(shù)據(jù)條數(shù)限定的查詢方案。

圖4 MySQL數(shù)據(jù)庫在數(shù)據(jù)ID限定、條數(shù)限定下的分頁查詢性能

6.4 合理利用MySQL查詢緩存

Query Cache查詢緩存重用的優(yōu)化技術(shù)，是MySQL數(shù)據(jù)庫中數(shù)據(jù)信息分析、保存的重要技術(shù)，其主要針對SQL查詢語句的重復(fù)性提交情況，由MySQL數(shù)據(jù)庫應(yīng)用程序直接從查詢緩存中檢索并返回需要的信息查詢結(jié)果，這樣既能夠減少某一個(類)數(shù)據(jù)的重復(fù)性查詢，又將最大程度降低后臺數(shù)據(jù)庫的工作負(fù)載。

如通過使用SHOW VARIABLES LIKE'% QUERY_CACHE%'的數(shù)據(jù)查詢語句，對MySQL庫內(nèi)的緩存數(shù)據(jù)進(jìn)行查詢，可完成數(shù)據(jù)表內(nèi)大批量、相同的數(shù)據(jù)查詢操作，但需注意在數(shù)據(jù)表結(jié)構(gòu)、內(nèi)容頻繁被修改的情況，若使用MySQL緩存查詢，則可能導(dǎo)致查詢結(jié)果的失效。

7 結(jié)語

MySQL是當(dāng)下應(yīng)用最廣泛的關(guān)系型開源數(shù)據(jù)庫，其主要具有源代碼可移植性、磁盤空間占用少、運(yùn)行速度快等優(yōu)勢，可被用于中小型網(wǎng)站的Web后臺數(shù)據(jù)信息查詢、增刪、修改操作。因此，不同企事業(yè)單位網(wǎng)絡(luò)數(shù)據(jù)的查詢，要在后臺數(shù)據(jù)庫硬件設(shè)備支持下，合理設(shè)置優(yōu)化SQL查詢語句、數(shù)據(jù)索引創(chuàng)建、遍歷方式、數(shù)據(jù)類型兼容與匹配等要素，提升MySQL數(shù)據(jù)庫的網(wǎng)絡(luò)查詢性能。