基于MySQL的查詢優化技術

豆利

摘要：隨著網絡數據量的增大，用戶對數據庫查詢的要求也越來越高，普通的查詢有時很難滿足要求，迫切需要對于MySQL語句實現優化，以提高查詢效率。其中最常用的是創建索引。本文介紹了常用的SQL語句優化查詢方式。

關鍵詞：查詢;優化;索引;SQL語句

中圖分類號：TP311? ? ? ? 文獻標識碼：A

文章編號：1009-3044（2021）15-0035-02

1查詢速度慢的原因

數據庫查詢的首先是客戶端發出查詢請求，服務端接受請求，服務端處理后相關數據，再把結果返回給客戶端，從而完成查詢的過程。在整個查詢過程中，涉及很多相關的參數，比如網絡速度、內存容量、I/O吞吐率等等。其中查詢的數據量比較大，查詢語句沒有設計好，查詢語句沒有優化好，返回不必要的行和列，產生死鎖等，也常常是查詢慢的原因。

2優化查詢速度

對于MySQL查詢優化最主要的是需要建立高性能的索引。索引對于查詢達到良好的性能起到關鍵的作用，尤其是當數據表中的數據量越來越大，越來越多的時候，索引的作用就尤其重要，當然在數據量比較小的時候，不恰當的索引對于查詢性能還不明顯，當數據量增大的時候，性能就會急劇下降，效果較為顯著。所以，就利用了索引，數據庫的索引有助于加快查詢速度。

為了對數據庫索引速度進行驗證，需要數據庫及相應的數據表。

首先創建數據庫educ，在educ數據庫中創建表student，其創建代碼如下：

CREATE DATABASE educ;--創建數據庫

CREATE TABLE student（sid int， sname char（8），gender char（8），email varchar（56），depid char（10））;--創建數據表student

CREATE TABLE department（depid char（10），depName varchar（40），num int）;--創建數據表department

創建存儲過程，實現批量數據的插入，代碼如下：

DELIMITER $$? ? ? ? ?--聲明存儲過程的結束符為$$

CREATE PROCEDURE proc1（）? ? --創建存儲過程

BEGIN

DECLARE i INT DEFAULT 1;

WHILE（i<=100000） DO

INSERT INTO student VALUES（i，Sophie，female，CONCAT（‘Sophie，i，@hffe.cn））;

SET i=i+1;

END WHILE;

END$$

DELIMITER;? ?---重新聲明MySQL的結束符為;

查看存儲過程：SHOW CREATE PROCEDURE proc1;

調用存儲過程：CALL proc1;可以實現批量插入數據。

（1）使用聚合函數查詢出相應的結果，添加索引后驗證執行效率

未創建索引時：SELECT MAX（sid） FROM student;

結果耗時為：1 row in set <0.02 sec>。

創建索引后 CREATE INDEX index_sid cON? student（sid）后，先使用命令SET QUERY CACHE清除緩存信息，重新執行SQL命令，結果耗時為：1 row in set <0.00 sec>。以下執行語句之前，首先用SET QUERY CACHE清除緩存信息，再重新執行命令。

（2）在分組（GROUP BY）、排序（ORDER BY）之后的字段進行添加索引。

未創建索引時：SELECT sid，COUNT（*） FROM student? GROUP BY sid;

結果耗時：5 row in set <0.03 sec>。

創建索引后：CREATE INDEX index_sid ON student（sid）;重新執行SQL命令，結果耗時：

5 row in set <0.02 sec>。

（3）多表連接優化

對于多表連接查詢，不管幾張表實現查詢，都需要在連接字段上建立索引，以加快查詢速度。

未創建索引時：SELECT sid，sname，gender，depname from student inner join department on stuent.depid=department.depid;

結果耗時：51 row in set <0.02 sec>。

創建索引后： 51 row in set <0.00 sec>。

對于子查詢中的IN子查詢會掃描整張表，需使用EXISTS子查詢代替使用，當然不是所有的索引都對查詢起顯著效果，MySQL是根據數據表中的數據進行優化的，當索引中有大量重復數據時，索引就失去了其顯著的作用，假設在gender字段中值為male和female各占一半的時候，即使在gender上建立索引也不起作用。當然索引并不是越多越好，索引在加速查詢的同時，也有其弊端。索引是以文件的形式存儲的，索引文件需要占有磁盤空間。

若數據表中的索引很多的時候，查詢會很浪費時間，索引會降低增加、刪除、修改等相關數據操作，數據表中索引越多，索引更新的時間會越長。應盡量避免更新聚集索引數據列，因為聚集索引數據列的順序和表的物理記錄保持一致，一旦聚集索引的數據進行更新，將導致整張表的列值的改變，會增加很大的系統開銷。復合索引在使用的時候，一般以索引的第一個列值作為條件查詢，如果值相等，接著選擇第二個字段列值進行排序，以此類推，否則建立的復合索引將不起作用。

如果索引定義的列值過長，這樣會讓索引變得很慢。通常以字段列值開始的部分的字符作為索引，可以大大節約索引空間，提高索引效率。其中前綴索引指的是把字段值的一部分作為索引，對于BLOB、TEXT、VARCHAR等類型必須使用前綴索引，畢竟索引需要存儲空間，索引太長，維護起來也相對困難。

對于MySQL語句中WHERE中的子查詢IN（），如希望搜索某個員工的工資情況，可以按照下面的方式實現查詢：

msql>SELECT *FROM? employees

->WHERE? 員工編號 IN（

->SELECT 員工編號 FROM salary WHERE 員工編號=2020002）;

對于IN子查詢，我們一般認為，存儲引擎是先執行里面的子查詢，把查詢的結果返回，作為外查詢的條件，即外層查詢后執行。但是實際情況不是這樣的，MySQL會把外層表直接壓入子查詢中，MySQL認為這樣執行的效率會更高，也就是說MySQL實際把查詢改寫了這樣的形式：

mysql->SELECT *FROM employees

->WHERE EXISTS（

->SELECT *FROM salary WHERE 員工編號=2020002

->AND employees.員工編號=salary.員工編號）;

這時子查詢需要根據員工編號來關聯外部表employees。通過explain 可以看出來，MySQL對employees表進行全表掃描，然后根據返回的員工編號逐一執行子查詢。

如果外部表是個很小的表，結果可能不會引起注意，但是如果外層的表是一個非常大的表，那么這個in子查的性能就會比較糟糕。

當然我們還可以用下面的查詢重新改寫這個查詢：

mysql->SELECT *FROM employees

->INNER JOIN salary USING（員工編號）

->WHERE 員工編號=‘2020002;

另一個優化辦法是通過連接函數GROUP_CONCAT（）在IN（）子查詢語句中構造一個由逗號分隔開來的列表序列，當然有的時候比使用關聯寫的效率更好些。一般情況下，不建議使用IN子查詢，畢竟執行效率相對低些，建議使用EXISTS（）子查詢獲取更高的執行效率。下面是對于IN子查詢改寫的情況：

mysql->SELECT *FROM employees

->WHERE EXISTS（

->SELECT *FROM salary WHERE 員工編號=2020002

->AND employees.員工編號=salary.員工編號）;

（4）需要在經常搜索的條件WHERE中涉及查詢的字段中添加索引

在查詢的時候，盡量避免使用SELECT *FROM? abc;這樣的語句，盡量不要使用*，需要使用具體的字段來顯示結果，這樣可以提高查詢效率。在查詢語句中，盡量避免在WHERE中使用空值進行判斷，比如SELECT *FROM abc WHERE 備注 IS NULL，如果執行這個查詢，就需要搜索abc整張表，可以修改為：把備注的空值利用默認值（DEFAULT）修改為0，即查詢語句為：SELECT *FROM abc WHERE 備注=0。

在WHERE條件中避免使用！=或<>等操作符，否則的話，就會導致存儲引擎掃描整張數據表。在WHERE子句中盡量避免使用OR進行連接條件，否則，也會導致存儲引擎掃描整張數據表。例如：SELECT *FROM employees WHERE 姓名=章三 OR 姓名=張宏，可以使用UNION聯合查詢，改進代碼如下：SELECT *FROM employees WHERE 姓名=章三UNION? SELECT

*FROM? employees WHERE 姓名=張宏。

在WHERE 條件查詢中，對于模糊查找，會導致掃描整張數據表，例如：SELECT id? FROM employees WHERE 姓名 LIKE‘%adb%，若要考慮提高效率，可以用全文索引實現。

在SQL語句中也要避免局部變量的使用，會掃描整張數據表。因為局部變量的解析是在查詢的時候才進行的。例如：SELECT num FROM? a WHERE name=@name 就可以進行轉換為：SELECT num FROM a WITH（index（索引名字）） WHERE name=@name。

在查詢中，避免在WHERE條件中使用函數，函數的使用也會掃描整張數據表，例如：SELECT sid FROM a WHERE SUBSTRING（名稱，1，4）=abdc，字段名稱的列值以abdc開頭的sid應改寫為：SELECT sid FROM a WHERE 名稱 LIKE ‘abdc%，執行這樣的操作，查詢效率會相對提高些。

對于查詢優化，最根本的是表的結構設計要合理，在設計表中字段時，若某些字段能使用數值型的話，就盡量避免使用字符類型，若該字段的列值中只含有數字信息，該字段的數據類型就不要設計為字符型，這樣會降低查詢和連接的性能，增加開銷成本，這是因為存儲引擎在處理查詢連接時，會逐個字符進行比較，而對于數字型的，比較一次就足夠了。

3結束語

索引是加快查詢的重要方法，如果數據量很小，索引的作用不大，當數據量很大的時候，尤其涉及多個表連接時，索引的作用更大。當然索引也有弊端，就是占用磁盤空間，但這些弊端并不妨礙索引的應用，索引在數據庫中通常是必不可少的。

參考文獻：

[1] 周德偉，覃國蓉.MySQL數據庫技術[M].2版.北京：高等教育出版社，2019.

[2] Baron Scbwartz，Peter Zaitsev，Vadim Tkacbenko，等.高性能MySQL[M]. 北京：電子工業出版社，2013.

[3]韓兵，王照清，廖聯軍，等.基于MySQL多表分頁查詢優化技術[J].計算機系統應用，2016，25（8）：171-175.

[4] 武洪萍，馬桂婷，等.MySQL數據庫原理及應用[M].北京：人民郵電出版社，2014.

[5] 張士軍，陸海倫，等. 索引在MySQL查詢優化中的應用[J]. 計算機與數字工程，2007，35（1）：37-39，8.

[6] 王珊，薩師煊.數據庫系統概論[M].4版.北京：高等教育出版社，2006.

【通聯編輯：王力】