石怡

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

0 引言

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

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

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

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

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

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

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

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

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

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

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

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

2 查詢效率影響因素

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

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

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

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

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

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

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

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

3 優(yōu)化方法

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

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

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

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

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

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

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

圖2 查詢結(jié)果對比

3.2SQL語句優(yōu)化

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

（1）等價(jià)運(yùn)算符轉(zhuǎn)換

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

表1 等價(jià)運(yùn)算符轉(zhuǎn)換規(guī)則

（2）條件表達(dá)式化簡

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

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

（3）子查詢消除

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

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

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

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

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

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

（5）外連接消除

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

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

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í)，連接表達(dá)式可能存在嵌套，即有括號限制了數(shù)據(jù)表的連接順序。如果連接形式只包括內(nèi)連接，可直接將括號省略，這樣做并不會(huì)影響原來的語義。如有下列嵌套連接語句：

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

3.3 合理利用MySQL查詢緩存

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

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

圖3 查看查詢緩存情況

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

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

4 結(jié)語

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