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

石怡

（江蘇信息職業技術學院 物聯網工程學院，江蘇 無錫 214153）

0 引言

大數據時代，傳統關系型數據庫管理系統（RDBMS）在處理海量數據時經歷著嚴峻的性能考驗。數據庫性能的提升與數據查詢的執行效率密切相關。查詢操作是一個數據庫系統運行時最主要的負載。事實上，對數據的增刪改操作也常常建立在對所需數據檢索的基礎之上。因此，對查詢語句的優化是數據庫性能優化至關重要的手段之一。

MySQL數據庫因其開放源碼、運行速度快、磁盤空間占用少等優點，得到了廣泛的應用，特別是在中小型WEB網站的后臺應用。

1 MySQL查詢執行過程

SQL查詢語句在MySQL數據庫中的執行過程主要劃分為“SQL輸入->詞法掃描->語法分析->語義檢查->優化->執行”6個階段[1]，具體步驟如下：

（1）根據應用系統業務邏輯要求編寫并輸入相關SQL語句。

（2）由詞法掃描器識別出SQL語句所包含的操作符、操作字符串和空格等單詞。

（3）由語法分析器根據SQL語法規則，判斷諸如關鍵字拼寫、關鍵字出現順序、引號匹配等是否正確，生成得到一棵語法分析樹。

（4）由預處理器檢驗第（3）步語法樹的合法性。通過對樹中各節點進行邏輯判斷，以生成新的解析樹，但樹的結構保持不變。如出現所需數據庫對象不存在，或別名重名等語義錯誤將報告反饋。

（5）由查詢優化器進行SQL優化，包括邏輯和物理優化。邏輯優化以關系代數為基礎，對語法分析樹的節點調整后生成關系代數語法樹。物理優化以選取最小代價為原則，進一步對查詢的連接順序、掃描方式、連接算法等進行評估與調整，最終得到查詢樹，即查詢執行計劃。

（6）執行器根據優化器生成的執行計劃，調用合適的存儲引擎API，比如InnoDB、MyISAM等，完成查詢的執行并返回結果。

簡單執行過程如圖1所示。其中每個階段的輸入均為下一階段的輸出。

圖1 MySQL查詢執行過程

2 查詢效率影響因素

一條SQL語句可以被解析成多種不同的執行策略，MySQL查詢優化器從查詢成本的角度出發，計算并判斷包括CPU利用率、I/O等待時間、網絡傳輸等在內的查詢總開銷是否最低，最終選擇執行一個最佳的執行計劃。盡管查詢優化器通過結合數據庫系統的配置參數，數據字典等信息實現自動優化，但是DBA不應該僅僅依賴于查詢優化模塊，特別是在目標數據量較大的情況下，如在系統設計過程中忽視了下列因素都有可能影響到查詢性能，導致查詢響應變慢。

（1）沒有為數據表關鍵列創建索引，或是在WHERE、HAVING、ON及ORDER BY子句中沒有用到索引列。這將導致查詢引擎無法利用索引，被迫執行全表掃描，增加了磁盤的I/O開銷。

（2）查詢結果集中包含了多余的數據行或數據列，對非必要數據的遍歷會造成訪問響應時間的延遲。

（3）在檢索條件中使用了可能引起全表掃描的操作符，比如<>或!=、or、in、not等，或是將屬性列與空值nul l進行判斷，導致無法使用索引掃描[2]。

（4）表設計時使用了不合適的數據類型，造成存儲空間的浪費，很顯然查詢相對較小字段內的數據速度會更快[3]。尤其是字符類數據，使用var char/nvar char便優于使用char/nchar類型。

（5）在檢索條件中對不兼容的數據類型進行匹配，導致在后續的查詢優化器階段無法完成進一步優化操作。

（6）在檢索條件中使用了局部變量或是對屬性列進行函數操作時，都將導致可用索引失效而進行全表掃描。

（7）多表查詢時，數據表連接順序不合理。不同的連接順序生成的中間關系也各不相同，因此CPU和IO開銷也有所不同。

3 優化方法

3.1 創建有效索引

索引優化屬于物理查詢優化技術手段。索引是一張存儲有索引列值及該值所在行的存儲位置的簡單物理表格。使得數據庫應用程序能像書的目錄為讀者提供快速找到想看的內容一樣，不必掃描整個表而找到想要的數據。如果在搜索條件列上存在索引，那么當表數據量大時，借助索引掃描優于全表順序掃描。在數據庫設計階段，可遵循下列規則創建索引。

（1）分別為主、外鍵屬性列創建索引；

（2）為經常出現在檢索條件中的屬性列創建索引；

（3）為經常出現在or der by后需要排序的屬性列創建索引。

由于創建與維護索引有時間與存儲空間的消耗[4]，特別是聚集索引，在更新表數據時，會進行動態的維護，同步完成數據的重新排列。因此，不要在非必須的數據列上創建索引，特別是一張經常插入、更新、刪除記錄的表。

如有測試數據表t 1，由3列整型數據構成，包含10萬條數據，使用語句SELECT c1,c2,c3 FROM t 1 WHERE c1=50001;查詢第50001條記錄。未使用索引時查詢用時0.06秒，在查詢條件c1上建立主鍵索引后，查詢時間降至0.00秒，查詢速度得到了顯著的提升。如圖2所示。

圖2 查詢結果對比

3.2SQL語句優化

SQL語句優化屬于邏輯查詢優化技術手段。以關系代數為理論基礎，依據查詢重寫規則，完成對SQL語句的等價轉換。注意在SQL編寫中要能夠充分利用索引，避免出現因語句不合理使得系統無法引用索引的情況[5]。

（1）等價運算符轉換

在MySQL數據庫中某些運算符如LIKE、BE‐TWEEN…AND、IN等不支持索引掃描，如果在條件判斷列上存在索引，可運用等價規則重寫該語句。轉換規則如表1所示。

表1 等價運算符轉換規則

（2）條件表達式化簡

可利用等式或不等式性質對查詢條件進行化簡，化簡規則如表2所示。

表2 條件表達式化簡規則

（3）子查詢消除

對于沒有分組或排序等復雜格式的SQL語句可以實現子查詢展開處理，即將子查詢重寫為等價的多表連接語句。這樣做的好處是將子查詢的連接條件和過濾條件上拉至父查詢，消除內部查詢語句的層次，減少子查詢的執行次數。有利于優化器做進一步優化，查詢效率可能會是數量級的提高。子查詢消除需要滿足外層查詢與內層查詢的結果沒有重復記錄行。如有下列子查詢語句：

SELECT*FROM t 1 WHERE id=ANY(SELECT id FROM t 2 WHERE id=10);

可重寫為：

SELECT*FROM t 1,t 2 WHERE t 1.id=t2.id AND t 2.id=10;

（4）內連接優化

如有測試數據表t1和t 2，對它們進行內連接操 作，表 示 為σcondition1×conditon2(t1×t2)，其 中 條 件condition1只作用在t1表，條件condit ion2只作用在t 2表，可將條件下推至對應的關系上，通過先完成選擇再進行連接操作，以減少中間元組的記錄數目。滿足如下等式。

σcondition1×conditon2(t1×t2)=σcondition1(t1)×σcondition2(t2)

如果僅條件condit ion1作用在t 1表，條件condition2作用在連接結果上，則條件下推后可轉換為如下等式。

σcondition1×conditon2(t1×t2)=σcondition2(σcondition1(t1)×t2)

（5）外連接消除

外連接語句的執行時間往往高于內連接。由于外連接中左右表的順序必須保持不變，因而限制了查詢優化器階段的優化方式。外連接優化的思路是將其轉換為等價內連接，這樣優化器便可更加靈活地選擇表的連接順序，加快查詢執行的速度。

在外連接查詢結果集中允許出現不匹配的數據行，由空值NULL來表示。當WHERE條件可以確保結果集中不存在值為NULL的數據行時，即在語義上等同于內連接。如有下列左外連接語句：

SELECT*FROM t 1 LEFT JOIN t 2 ON t 1.id=t 2.id WHERE t 1.id IS NOT NULL;

可重寫為：

SELECT*FROM t 1 INNER JOIN t 2 ON t 1.?

id=t 2.id;

或

SELECT*FROM t 1,t 2 WHERE t 1.id=t2.id;

（6）嵌套連接優化

在執行多表連接操作時，連接表達式可能存在嵌套，即有括號限制了數據表的連接順序。如果連接形式只包括內連接，可直接將括號省略，這樣做并不會影響原來的語義。如有下列嵌套連接語句：

SELECT*FROM t 1 JOIN(t 2 JOIN t 3 ON t 2.id=t 3.id)ON t 1.id=t 2.id WHERE t 1.id>10;

可重寫為：

SELECT*FROM t 1 JOIN t 2 ON t 1.id=t 2.id JOIN t 3 ON t 2.id=t 3.id WHERE t 1.id>10;

（7）DISTINCT優化

DISTINCT關鍵字的作用是去除重復記錄。在查詢處理完SQL列表后會對最終結果集完成一次排序，產生較高的排序成本。因此數據量大時盡量避免使用。如果是在主健列或是唯一列上執行DISTINCT操作，可直接刪除DISTINCT。

3.3 合理利用MySQL查詢緩存

MySQL查詢緩存Quer y Cache是一種有效的查詢重用優化技術。該技術能夠保存已分析并執行的查詢語句的完整結果。當相同的查詢語句再次提交后，MySQL會首先從查詢緩存中檢索結果，如有命中，便會直接返回查詢結果，省略后續的解析、優化與執行階段。

可以使用“show var iabl es l ike'%que‐r y_cache%';”查看查詢緩存參數設置情況。參數信息如圖3所示，參數含義如表3所示。

圖3 查看查詢緩存情況

表3 quer y_cache參數及含義

Quer y Cache會產生Hash計算，在檢查是否命中緩存時也有一定的資源消耗。如果表中數據，或是表結構頻繁地被修改，則會造成查詢緩存失效。因此，必須合理利用MySQL查詢緩存。根據應用程序的需求，正確設置相關參數，并在需要執行大量相同的，且結果數據不常更新的查詢語句時使用。

4 結語

MySQL數據庫查詢性能優化的目標是要減少SQL語句執行的響應時間。查詢性能的提升除了借助MySQL自身提供的優化機置之外，實踐證明建立適當的索引，并通過高效的SQL語句充分引用索引能夠得到較好的執行效率。