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

石怡

（江蘇信息職業(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í)上，對(duì)數(shù)據(jù)的增刪改操作也常常建立在對(duì)所需數(shù)據(jù)檢索的基礎(chǔ)之上。因此，對(duì)查詢語句的優(yōu)化是數(shù)據(jù)庫性能優(yōu)化至關(guān)重要的手段之一。

MySQL數(shù)據(jù)庫因其開放源碼、運(yùn)行速度快、磁盤空間占用少等優(yōu)點(diǎn)，得到了廣泛的應(yīng)用，特別是在中小型WEB網(wǎng)站的后臺(tái)應(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）由詞法掃描器識(shí)別出SQL語句所包含的操作符、操作字符串和空格等單詞。

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

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

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

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

簡(jiǎn)單執(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ù)列，對(duì)非必要數(shù)據(jù)的遍歷會(huì)造成訪問響應(yīng)時(shí)間的延遲。

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

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

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

（6）在檢索條件中使用了局部變量或是對(duì)屬性列進(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ù)手段。索引是一張存儲(chǔ)有索引列值及該值所在行的存儲(chǔ)位置的簡(jiǎn)單物理表格。使得數(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í)間與存儲(chǔ)空間的消耗[4]，特別是聚集索引，在更新表數(shù)據(jù)時(shí)，會(huì)進(jìn)行動(dòng)態(tài)的維護(hù)，同步完成數(shù)據(jù)的重新排列。因此，不要在非必須的數(shù)據(jù)列上創(chuàng)建索引，特別是一張經(jīng)常插入、更新、刪除記錄的表。

如有測(cè)試數(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é)果對(duì)比

3.2SQL語句優(yōu)化

SQL語句優(yōu)化屬于邏輯查詢優(yōu)化技術(shù)手段。以關(guān)系代數(shù)為理論基礎(chǔ)，依據(jù)查詢重寫規(guī)則，完成對(duì)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á)式化簡(jiǎn)

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

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

（3）子查詢消除

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

如有測(cè)試數(shù)據(jù)表t1和t 2，對(duì)它們進(jìn)行內(nèi)連接操 作，表 示 為σcondition1×conditon2(t1×t2)，其 中 條 件condition1只作用在t1表，條件condit ion2只作用在t 2表，可將條件下推至對(duì)應(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á)式可能存在嵌套，即有括號(hào)限制了數(shù)據(jù)表的連接順序。如果連接形式只包括內(nèi)連接，可直接將括號(hào)省略，這樣做并不會(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ì)對(duì)最終結(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í)行效率。