王 淼

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

0 引言

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

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

1 查詢效率影響因素分析

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

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

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

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

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

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

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

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

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

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

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

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

2.1 索引優(yōu)化

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

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

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

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

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

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

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

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

（7）范圍查詢優(yōu)化。無法引用索引的原因為：MySQL內(nèi)部優(yōu)化器會根據(jù)檢索比例、表大小等多個因素整體評估是否使用索引，有時會由于單次數(shù)據(jù)量查詢過大導(dǎo)致優(yōu)化器最終選擇不走索引優(yōu)化方法，可以將大的范圍拆分成多個小范圍。假設(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ù)查詢目標來重寫規(guī)則，完成對SQL語句的等價轉(zhuǎn)換。MySQL中的索引幾乎是最有效的查詢效率提升手段，在進行SQL編寫時應(yīng)注意避免因語句不合理而造成系統(tǒng)無法正常引用索引[8]。

2.2.1 等價運算符轉(zhuǎn)換

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

2.2.2 條件表達式化簡

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

表2 化簡規(guī)則

2.2.3 子查詢消除

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

例如針對查詢語句：

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í)行時間往往比內(nèi)連接要長得多，并且查詢優(yōu)化器在面對外連接時起到的效果會降低，通過外連接消除的方式，能夠?qū)⑵滢D(zhuǎn)變?yōu)榈葍r的內(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ù)庫，以空值表示，當在WHERE查詢條件下判斷出結(jié)果集中不存在NULL數(shù)據(jù)行時，外連接就可以被視為內(nèi)連接。

2.3 合理利用MySQL查詢緩存

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

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

2.4 分頁優(yōu)化

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

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

3 結(jié)語

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