MySQL查詢優(yōu)化與研究

王 淼

（咸陽職業(yè)技術(shù)學(xué)院 陜西 咸陽 712000）

0 引言

隨著計(jì)算機(jī)技術(shù)的快速發(fā)展，數(shù)據(jù)存儲(chǔ)、分析和應(yīng)用日益廣泛，數(shù)據(jù)采集量相應(yīng)增加，在使用數(shù)據(jù)庫工具進(jìn)行數(shù)據(jù)管理時(shí)，增／刪／改／查的操作也會(huì)越來越多，從而造成事務(wù)處理的效率大大降低[1]。利用數(shù)據(jù)分表存儲(chǔ)技術(shù)，能夠有效解決當(dāng)前數(shù)據(jù)量不斷大量增加情況下的數(shù)據(jù)存儲(chǔ)及查詢效率低下問題。在此技術(shù)背景下，當(dāng)多表聯(lián)合查詢操作頻繁時(shí)，多表關(guān)聯(lián)查詢?cè)诜猪擄@示速度還會(huì)成為制約業(yè)務(wù)處理能力的因素之一。

MySQL自身具備的高性能、高可用、易存儲(chǔ)的特點(diǎn)，分布式設(shè)計(jì)使其能夠在理想環(huán)境下訪問2000萬級(jí)別的數(shù)據(jù)量，但是局限于各種條件，比如用多表聯(lián)合查詢時(shí)使用union技術(shù)進(jìn)行數(shù)據(jù)提取，這種查詢方法在分表數(shù)量和并發(fā)訪問用戶量較小時(shí)，響應(yīng)速度較快[2]。但是當(dāng)分表數(shù)量比較大時(shí)，速度會(huì)明顯降低，經(jīng)常因業(yè)務(wù)阻塞等情況，致使用戶產(chǎn)品使用體驗(yàn)較差。因此如何提升MySQL查詢效率，提升客戶產(chǎn)品體驗(yàn)及滿意度成為當(dāng)下研究的課題之一，也是本文的重點(diǎn)研究內(nèi)容。

1 查詢效率影響因素分析

一個(gè)完整的SQL查詢過程包括語義查檢、邏輯優(yōu)化、物理優(yōu)化等多個(gè)步驟，并且隨著MySQL版本的升級(jí)，語法分析樹也會(huì)逐步加入新元素，當(dāng)一條SQL語句被解析成多種不同的執(zhí)行策略時(shí)，其所造成的CPU利用率，產(chǎn)生的I/O等待時(shí)間甚至是網(wǎng)絡(luò)傳輸效率都會(huì)成為影響查詢效率的因素。SQL查詢過程見圖1。

目前的查詢優(yōu)化器已經(jīng)可以根據(jù)數(shù)據(jù)庫的配置參數(shù)、數(shù)據(jù)字典等信息實(shí)現(xiàn)自動(dòng)化調(diào)整，但僅是通過查詢優(yōu)化模塊，很難處理數(shù)據(jù)量較大情況下的查詢性能問題，無法有效提升查詢響應(yīng)速度[3]。

以目前的技術(shù)現(xiàn)狀來看，影響MySQL查詢性能的因素主要有以下幾個(gè)方面。

1.1 非必要數(shù)據(jù)遍歷

如缺少數(shù)據(jù)表關(guān)鍵列索引或者是關(guān)鍵查詢語句沒有用到索引列，會(huì)導(dǎo)致在沒有索引的情況下，MySQL在執(zhí)行查詢時(shí)被迫完成全盤掃描，增加磁盤I/O壓力。

若SQL語句未進(jìn)行書寫優(yōu)化，包含了多余的數(shù)據(jù)行或數(shù)據(jù)列，導(dǎo)致在查詢時(shí)遍歷了非必要數(shù)據(jù)，整體訪問響應(yīng)時(shí)間增加。

在檢索中使用了可能會(huì)引起全表掃描的操作符，如or、in、not等，或者將屬性列與空值進(jìn)行判斷，導(dǎo)致索引掃描失效。

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

1.2 數(shù)據(jù)類型設(shè)計(jì)不合理

在進(jìn)行表設(shè)計(jì)時(shí)使用了不合適的數(shù)據(jù)類型，如自增式編號(hào)本應(yīng)使用int類型而定義為double類型，且數(shù)據(jù)長度設(shè)置不合理，雖然這一情況在小字段內(nèi)查詢速度會(huì)更快，但極易造成存儲(chǔ)空間上的浪費(fèi)。

在檢索條件中對(duì)不兼容的數(shù)據(jù)類型進(jìn)行匹配，導(dǎo)致在后續(xù)的查詢優(yōu)化階段無法完成進(jìn)一步優(yōu)化操作?；蛘咴谶M(jìn)行多表查詢時(shí)，數(shù)據(jù)表的連接順序不合理，由連接順序而定義的表間關(guān)系趨向于復(fù)雜，造成CPU和I/O的開銷增加。

2 基于MySQL的查詢優(yōu)化方法

2.1 索引優(yōu)化

SQL語句的分析分為詞法分析與語法分析，MySQL的詞法分析由MySQLLex完成，語法分析由Bison生成。除了Bison外，Java當(dāng)中也有開源的詞法結(jié)構(gòu)分析工具，例如Antlr4。ANTLR從語法生成一個(gè)解析器，可以構(gòu)建和遍歷解析樹。圖2為SQL語句詞法分析圖，為后邊索引優(yōu)化打下基礎(chǔ)[4]。

索引的優(yōu)化是MySQL優(yōu)化中最重要的方面之一。對(duì)于當(dāng)前互聯(lián)網(wǎng)大廠來說，在多數(shù)業(yè)務(wù)中索引優(yōu)化有著舉足輕重的地位，下面就索引優(yōu)化進(jìn)行研究。

（1）最左前綴法則。如果索引了多列，要遵守最左前綴法則，指的是查詢從索引的最左前列開始并且不跳過索引中的列，例如SQL：EXPLAIN SELECT*FROW employees WHERE position=manager。執(zhí)行結(jié)果可以看出，本次查詢并未使用索引查詢，效率較低[5]。所以，索引優(yōu)化中要首先遵守最左前綴法則，盡量用覆蓋索引進(jìn)行SQL書寫，以使SQL執(zhí)行時(shí)能盡量使用索引，提升查詢效率。

（2）不在索引列上做任何操作。這里的操作主要包括：計(jì)算、函數(shù)、（自動(dòng)or手動(dòng)）類型轉(zhuǎn)換等情況，會(huì)導(dǎo)致索引失效而轉(zhuǎn)向全表掃描，如SQL：EXPLAIN SELECT*FROM employees WHERE left(name，3)=LILEI。執(zhí)行結(jié)果可以看出，雖然在條件中的字段使用了索引，但是實(shí)際的執(zhí)行結(jié)果并未采用索引進(jìn)行檢索，效率較低，因此在日常查詢中要盡量避開索引上進(jìn)行操作[6-7]。

（3）盡量使用覆蓋索引（只訪問索引的查詢<索引列包含查詢列>），減少select*語句。

（4）MySQL在使用不等于（！=或者<>）、not in、not exists的時(shí)候無法使用索引會(huì)導(dǎo)致全表掃描<小于、>大于、<=、>=這些MySQL內(nèi)部優(yōu)化器會(huì)根據(jù)檢索比例、表大小等多個(gè)因素整體評(píng)估是否使用索引。

（5）is null，is not null一般情況下也無法使用索引，所以盡量減少這些字段的使用。

（6）減少or或in的使用，原因是在查詢時(shí)，MySQL不一定使用索引，MySQL內(nèi)部優(yōu)化器會(huì)根據(jù)檢索比例、表大小等多個(gè)因素整體評(píng)估是否使用索引。

（7）范圍查詢優(yōu)化。無法引用索引的原因?yàn)椋篗ySQL內(nèi)部優(yōu)化器會(huì)根據(jù)檢索比例、表大小等多個(gè)因素整體評(píng)估是否使用索引，有時(shí)會(huì)由于單次數(shù)據(jù)量查詢過大導(dǎo)致優(yōu)化器最終選擇不走索引優(yōu)化方法，可以將大的范圍拆分成多個(gè)小范圍。假設(shè)索引為a、b、c，歸納總結(jié)優(yōu)化技巧見表1。

表1 索引優(yōu)化技巧匯總表

2.2 SQL語句優(yōu)化

SQL語句優(yōu)化是邏輯查詢優(yōu)化的主要技術(shù)手段之一，是以關(guān)系代數(shù)為理論基礎(chǔ)，根據(jù)查詢目標(biāo)來重寫規(guī)則，完成對(duì)SQL語句的等價(jià)轉(zhuǎn)換。MySQL中的索引幾乎是最有效的查詢效率提升手段，在進(jìn)行SQL編寫時(shí)應(yīng)注意避免因語句不合理而造成系統(tǒng)無法正常引用索引[8]。

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

等價(jià)運(yùn)算符轉(zhuǎn)換的目的是使用支持索引功能的算法符來代替不支持索引掃描的運(yùn)算符，比如在MySQL中LIKE、BETWEEN……AND運(yùn)算均不支持索引掃描，如果在確定存在條件判斷索引的情況下，可使用等價(jià)運(yùn)算符對(duì)SQL語句重寫，實(shí)現(xiàn)引用索引的目的。

2.2.2 條件表達(dá)式化簡

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

表2 化簡規(guī)則

2.2.3 子查詢消除

將子查詢重寫為等價(jià)的多表連接語句，能夠?qū)⒆硬樵兊倪B接條件和過濾條件上拉至父查詢，用以消除內(nèi)部查詢語句的層次，減少在查詢過程中子查詢的執(zhí)行次數(shù)，進(jìn)而優(yōu)化查詢效率。子查詢消除并非適用于任何條件，通過是針對(duì)沒有分組或排序等復(fù)雜格式的SQL語句，同時(shí)需要滿足外層查詢與內(nèi)層查詢的結(jié)果沒有重復(fù)記錄行這一條件[9-10]。

例如針對(duì)查詢語句：

SELETE * FROM t_student WHERE id =ANY (SELETE id FROM t_user WHERE id=3);

可重寫為SELECT * FROM t_student，t_user WHERE t_student.id=t_user.id AND t_user.id=3;

2.2.4 外連接消除

外連接的執(zhí)行時(shí)間往往比內(nèi)連接要長得多，并且查詢優(yōu)化器在面對(duì)外連接時(shí)起到的效果會(huì)降低，通過外連接消除的方式，能夠?qū)⑵滢D(zhuǎn)變?yōu)榈葍r(jià)的內(nèi)連接，從而提升查詢優(yōu)化器的優(yōu)化效果。

例如左外連語句：

SELECT * FROM t_student LEFT JOIN t_user ON t_student.id = t_user.id WHERE t_student.id IS NOT NULL;

可重寫為：

SELECT * FROM t_student INNER JOIN t_user ON t_student.id=t_user.id;

需要注意的是，允許在外連接的查詢結(jié)果集中出現(xiàn)不匹配的數(shù)據(jù)庫，以空值表示，當(dāng)在WHERE查詢條件下判斷出結(jié)果集中不存在NULL數(shù)據(jù)行時(shí)，外連接就可以被視為內(nèi)連接。

2.3 合理利用MySQL查詢緩存

MySQL中的查詢緩存技術(shù)Query Cache能夠提供非常強(qiáng)大的查詢效果，其基本原理是將過往的查詢事件完整保存下來，當(dāng)重復(fù)相同查詢事件時(shí)，Query Cache可直接調(diào)用保存的查詢結(jié)果而無需再次執(zhí)行查詢指令，并且查詢緩存技術(shù)還可判斷數(shù)據(jù)庫變化情況，保證功能的可靠性。

使用MySQL查詢緩存技術(shù)時(shí)應(yīng)先利用“show variables like‘%query_cache%’;”指令查看緩存參數(shù)設(shè)置情況，查詢結(jié)果列表會(huì)顯示出所有的參數(shù)名稱（variable_name）及當(dāng)前值（value）。

2.4 分頁優(yōu)化

MySQL查詢?cè)谶M(jìn)行分頁也會(huì)消耗大量時(shí)間，比如在使用如下語句實(shí)現(xiàn)查詢分頁時(shí)：select * from t1 limit 10 000，10，系統(tǒng)會(huì)從數(shù)據(jù)表t1中取出起始于10 001行的10行記錄。在這一過程中，不僅查詢了10行目標(biāo)記錄，還完成了前10 010條記錄的遍歷，但是前10 000條記錄被拋棄。因此利用這一語句在查詢數(shù)據(jù)表較大的靠后數(shù)據(jù)時(shí)，執(zhí)行效率非常低下。

基于此，經(jīng)過研究總結(jié)有以下優(yōu)化方法，即根據(jù)非主鍵字段排序的分頁查詢。如語句：select * from t1 ORDE R BY name limit 90 000，5，發(fā)現(xiàn)并沒有使用name字段的索引（key字段對(duì)應(yīng)的值為null），這是由于掃描整個(gè)索引并查找到?jīng)]索引的行（可能要遍歷多個(gè)索引樹）的成本比掃描全表的成本更高，所以優(yōu)化器放棄使用索引[11-12]。其實(shí)關(guān)鍵是讓排序時(shí)返回的字段盡可能少，所以讓排序和分頁操作先查出主鍵，然后根據(jù)主鍵查到對(duì)應(yīng)的記錄，SQL改寫如下：

select * from t1 e inner join (select id from t 1 order by name limit 90 000，5) ed on e.id = ed.id

由此操作可知，查詢及分頁結(jié)果與原語句一致，但執(zhí)行時(shí)間減少了一半以上，從執(zhí)行計(jì)劃上看，原SQL使用的是FILESORT排序，而優(yōu)化后的SQL使用的是索引排序。

3 結(jié)語

在數(shù)據(jù)庫表中，索引是提高查詢速度的一個(gè)關(guān)鍵因素，如果數(shù)據(jù)表中的數(shù)據(jù)記錄很少，索引提升的查詢速度并不是很明顯，數(shù)據(jù)量越大，查詢優(yōu)化的性能越明顯，在寫SQL語句時(shí)，要注意上述提到的查詢語句的優(yōu)化方法，以提升數(shù)據(jù)檢索的速度。