基于MySQL的數據庫查詢性能優化技術研究

王景

（甘肅交通職業技術學院，甘肅 蘭州 730070）

1 引言

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

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

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

2 MySQL數據庫信息查詢的測試環境搭建

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

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

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

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

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

（5）添加相應腳本至服務列表，啟動服務測試：chkconfig--add mysqld；chkconfig mysqld on。

3 基于MySQL關系型數據庫的SQL語句信息查詢執行流程

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

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

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

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

（4）利用查詢優化器、以關系代數為基礎，對新的語法分析樹作出邏輯、物理優化，進行語法分析樹中各節點的語法調整，以及信息查詢順序、掃描方式、聯接算法等調整后，生成語法查詢樹。

（5）調用存儲引擎API、依據語法查詢樹，執行網絡數據的查詢指令，并將最終的查詢執行結果返回至用戶客戶端，即完成相應數據信息的查詢操作。

圖1 MySQL數據庫的SQL語句信息查詢執行流程

4 MySQL數據庫查詢性能的影響因素分析

MySQL關系型數據庫中，使用詞法掃描器、語法分析器、預處理器、查詢優化器等設備，以及SQL語句進行網絡信息查詢時，通過采取不同的執行策略，包括對CPU利用率、I/O通信等待時長、網絡數據傳輸等調用方式，作出適宜的調整設置，可有效降低數據庫信息的查詢成本。當下有關MySQL數據庫查詢性能的影響因素，主要包含索引創建、數據遍歷方式、數據類型兼容性、數據表聯接順序等內容。

4.1 數據表索引創建的滯后

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

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

4.2 數據遍歷方式的不合理

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

4.3 數據類型與檢索條件的不匹配

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

4.4 數據表聯接順序的不準確

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

5 MySQL數據庫查詢性能的實驗測試與結果

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

圖2 三類數據庫的查詢性能的實驗測試結果

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

6 基于MySQL數據庫的網絡數據查詢性能優化策略研究

6.1 創建有效數據表索引

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

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

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

6.2 SQL語句優化

SQL語句作為網絡數據邏輯查詢技術優化的手段，主要依據SQL語法查詢規則、關系代數理論，對SQL語句作出等價轉換。如between……and、IN、OR、LIKE等的關鍵運算符，不支持條件判斷的索引掃描，因而可以用其他諸如>=、<、>=and<的運算符進行轉換。或者將未進行數據分組、數據排序的SQL語句，重寫為多數據表連接的等價語句，這樣可以將子查詢的數據過濾條件與父查詢的過濾條件形成對接，減少某一個(類)數據查詢的執行次數。

6.3 分頁查詢優化

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

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

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

6.4 合理利用MySQL查詢緩存

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

如通過使用SHOW VARIABLES LIKE'% QUERY_CACHE%'的數據查詢語句，對MySQL庫內的緩存數據進行查詢，可完成數據表內大批量、相同的數據查詢操作，但需注意在數據表結構、內容頻繁被修改的情況，若使用MySQL緩存查詢，則可能導致查詢結果的失效。

7 結語

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