MySQL查詢優化與研究

王 淼

（咸陽職業技術學院 陜西 咸陽 712000）

0 引言

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

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

1 查詢效率影響因素分析

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

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

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

1.1 非必要數據遍歷

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

若SQL語句未進行書寫優化，包含了多余的數據行或數據列，導致在查詢時遍歷了非必要數據，整體訪問響應時間增加。

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

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

1.2 數據類型設計不合理

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

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

2 基于MySQL的查詢優化方法

2.1 索引優化

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

索引的優化是MySQL優化中最重要的方面之一。對于當前互聯網大廠來說，在多數業務中索引優化有著舉足輕重的地位，下面就索引優化進行研究。

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

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

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

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

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

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

（7）范圍查詢優化。無法引用索引的原因為：MySQL內部優化器會根據檢索比例、表大小等多個因素整體評估是否使用索引，有時會由于單次數據量查詢過大導致優化器最終選擇不走索引優化方法，可以將大的范圍拆分成多個小范圍。假設索引為a、b、c，歸納總結優化技巧見表1。

表1 索引優化技巧匯總表

2.2 SQL語句優化

SQL語句優化是邏輯查詢優化的主要技術手段之一，是以關系代數為理論基礎，根據查詢目標來重寫規則，完成對SQL語句的等價轉換。MySQL中的索引幾乎是最有效的查詢效率提升手段，在進行SQL編寫時應注意避免因語句不合理而造成系統無法正常引用索引[8]。

2.2.1 等價運算符轉換

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

2.2.2 條件表達式化簡

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

表2 化簡規則

2.2.3 子查詢消除

將子查詢重寫為等價的多表連接語句，能夠將子查詢的連接條件和過濾條件上拉至父查詢，用以消除內部查詢語句的層次，減少在查詢過程中子查詢的執行次數，進而優化查詢效率。子查詢消除并非適用于任何條件，通過是針對沒有分組或排序等復雜格式的SQL語句，同時需要滿足外層查詢與內層查詢的結果沒有重復記錄行這一條件[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 外連接消除

外連接的執行時間往往比內連接要長得多，并且查詢優化器在面對外連接時起到的效果會降低，通過外連接消除的方式，能夠將其轉變為等價的內連接，從而提升查詢優化器的優化效果。

例如左外連語句：

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;

需要注意的是，允許在外連接的查詢結果集中出現不匹配的數據庫，以空值表示，當在WHERE查詢條件下判斷出結果集中不存在NULL數據行時，外連接就可以被視為內連接。

2.3 合理利用MySQL查詢緩存

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

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

2.4 分頁優化

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

基于此，經過研究總結有以下優化方法，即根據非主鍵字段排序的分頁查詢。如語句：select * from t1 ORDE R BY name limit 90 000，5，發現并沒有使用name字段的索引（key字段對應的值為null），這是由于掃描整個索引并查找到沒索引的行（可能要遍歷多個索引樹）的成本比掃描全表的成本更高，所以優化器放棄使用索引[11-12]。其實關鍵是讓排序時返回的字段盡可能少，所以讓排序和分頁操作先查出主鍵，然后根據主鍵查到對應的記錄，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

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

3 結語

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