基于MySQL的查詢優(yōu)化技術

豆利

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

關鍵詞：查詢;優(yōu)化;索引;SQL語句

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

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

1查詢速度慢的原因

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

2優(yōu)化查詢速度

對于MySQL查詢優(yōu)化最主要的是需要建立高性能的索引。索引對于查詢達到良好的性能起到關鍵的作用，尤其是當數(shù)據(jù)表中的數(shù)據(jù)量越來越大，越來越多的時候，索引的作用就尤其重要，當然在數(shù)據(jù)量比較小的時候，不恰當?shù)乃饕龑τ诓樵冃阅苓€不明顯，當數(shù)據(jù)量增大的時候，性能就會急劇下降，效果較為顯著。所以，就利用了索引，數(shù)據(jù)庫的索引有助于加快查詢速度。

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

首先創(chuàng)建數(shù)據(jù)庫educ，在educ數(shù)據(jù)庫中創(chuàng)建表student，其創(chuàng)建代碼如下：

CREATE DATABASE educ;--創(chuàng)建數(shù)據(jù)庫

CREATE TABLE student（sid int， sname char（8），gender char（8），email varchar（56），depid char（10））;--創(chuàng)建數(shù)據(jù)表student

CREATE TABLE department（depid char（10），depName varchar（40），num int）;--創(chuàng)建數(shù)據(jù)表department

創(chuàng)建存儲過程，實現(xiàn)批量數(shù)據(jù)的插入，代碼如下：

DELIMITER $$? ? ? ? ?--聲明存儲過程的結(jié)束符為$$

CREATE PROCEDURE proc1（）? ? --創(chuàng)建存儲過程

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的結(jié)束符為;

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

調(diào)用存儲過程：CALL proc1;可以實現(xiàn)批量插入數(shù)據(jù)。

（1）使用聚合函數(shù)查詢出相應的結(jié)果，添加索引后驗證執(zhí)行效率

未創(chuàng)建索引時：SELECT MAX（sid） FROM student;

結(jié)果耗時為：1 row in set <0.02 sec>。

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

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

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

結(jié)果耗時：5 row in set <0.03 sec>。

創(chuàng)建索引后：CREATE INDEX index_sid ON student（sid）;重新執(zhí)行SQL命令，結(jié)果耗時：

5 row in set <0.02 sec>。

（3）多表連接優(yōu)化

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

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

結(jié)果耗時：51 row in set <0.02 sec>。

創(chuàng)建索引后： 51 row in set <0.00 sec>。

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

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

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

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

msql>SELECT *FROM? employees

->WHERE? 員工編號 IN（

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

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

mysql->SELECT *FROM employees

->WHERE EXISTS（

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

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

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

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

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

mysql->SELECT *FROM employees

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

->WHERE 員工編號=‘2020002;

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

mysql->SELECT *FROM employees

->WHERE EXISTS（

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

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

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

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

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

*FROM? employees WHERE 姓名=張宏。

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

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

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

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

3結(jié)束語

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

參考文獻：

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

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

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

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

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

[6] 王珊，薩師煊.數(shù)據(jù)庫系統(tǒng)概論[M].4版.北京：高等教育出版社，2006.

【通聯(lián)編輯：王力】