石怡

（江蘇信息職業(yè)技術學院 物聯(lián)網工程學院，江蘇 無錫 214153）

0 引言

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

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

1 MySQL查詢執(zhí)行過程

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

（1）根據(jù)應用系統(tǒng)業(yè)務邏輯要求編寫并輸入相關SQL語句。

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

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

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

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

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

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

圖1 MySQL查詢執(zhí)行過程

2 查詢效率影響因素

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

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

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

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

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

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

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

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

3 優(yōu)化方法

3.1 創(chuàng)建有效索引

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

（1）分別為主、外鍵屬性列創(chuàng)建索引；

（2）為經常出現(xiàn)在檢索條件中的屬性列創(chuàng)建索引；

（3）為經常出現(xiàn)在or der by后需要排序的屬性列創(chuàng)建索引。

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

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

圖2 查詢結果對比

3.2SQL語句優(yōu)化

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

（1）等價運算符轉換

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

表1 等價運算符轉換規(guī)則

（2）條件表達式化簡

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

表2 條件表達式化簡規(guī)則

（3）子查詢消除

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

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）內連接優(yōu)化

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

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

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

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

（5）外連接消除

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

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

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）嵌套連接優(yōu)化

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

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優(yōu)化

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

3.3 合理利用MySQL查詢緩存

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

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

圖3 查看查詢緩存情況

表3 quer y_cache參數(shù)及含義

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

4 結語

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