基于MySQL的查詢(xún)優(yōu)化技術(shù)

豆利

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

關(guān)鍵詞：查詢(xún);優(yōu)化;索引;SQL語(yǔ)句

中圖分類(lèi)號(hào)：TP311? ? ? ? 文獻(xiàn)標(biāo)識(shí)碼：A

文章編號(hào)：1009-3044（2021）15-0035-02

1查詢(xún)速度慢的原因

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

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

對(duì)于MySQL查詢(xún)優(yōu)化最主要的是需要建立高性能的索引。索引對(duì)于查詢(xún)達(dá)到良好的性能起到關(guān)鍵的作用，尤其是當(dāng)數(shù)據(jù)表中的數(shù)據(jù)量越來(lái)越大，越來(lái)越多的時(shí)候，索引的作用就尤其重要，當(dāng)然在數(shù)據(jù)量比較小的時(shí)候，不恰當(dāng)?shù)乃饕龑?duì)于查詢(xún)性能還不明顯，當(dāng)數(shù)據(jù)量增大的時(shí)候，性能就會(huì)急劇下降，效果較為顯著。所以，就利用了索引，數(shù)據(jù)庫(kù)的索引有助于加快查詢(xún)速度。

為了對(duì)數(shù)據(jù)庫(kù)索引速度進(jìn)行驗(yàn)證，需要數(shù)據(jù)庫(kù)及相應(yīng)的數(shù)據(jù)表。

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

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

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)建存儲(chǔ)過(guò)程，實(shí)現(xiàn)批量數(shù)據(jù)的插入，代碼如下：

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

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

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é)束符為;

查看存儲(chǔ)過(guò)程：SHOW CREATE PROCEDURE proc1;

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

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

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

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

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

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

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

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

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

5 row in set <0.02 sec>。

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

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

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

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

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

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

若數(shù)據(jù)表中的索引很多的時(shí)候，查詢(xún)會(huì)很浪費(fèi)時(shí)間，索引會(huì)降低增加、刪除、修改等相關(guān)數(shù)據(jù)操作，數(shù)據(jù)表中索引越多，索引更新的時(shí)間會(huì)越長(zhǎng)。應(yīng)盡量避免更新聚集索引數(shù)據(jù)列，因?yàn)榫奂饕龜?shù)據(jù)列的順序和表的物理記錄保持一致，一旦聚集索引的數(shù)據(jù)進(jìn)行更新，將導(dǎo)致整張表的列值的改變，會(huì)增加很大的系統(tǒng)開(kāi)銷(xiāo)。復(fù)合索引在使用的時(shí)候，一般以索引的第一個(gè)列值作為條件查詢(xún)，如果值相等，接著選擇第二個(gè)字段列值進(jìn)行排序，以此類(lèi)推，否則建立的復(fù)合索引將不起作用。

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

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

msql>SELECT *FROM? employees

->WHERE? 員工編號(hào) IN（

->SELECT 員工編號(hào) FROM salary WHERE 員工編號(hào)=2020002）;

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

mysql->SELECT *FROM employees

->WHERE EXISTS（

->SELECT *FROM salary WHERE 員工編號(hào)=2020002

->AND employees.員工編號(hào)=salary.員工編號(hào)）;

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

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

當(dāng)然我們還可以用下面的查詢(xún)重新改寫(xiě)這個(gè)查詢(xún)：

mysql->SELECT *FROM employees

->INNER JOIN salary USING（員工編號(hào)）

->WHERE 員工編號(hào)=‘2020002;

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

mysql->SELECT *FROM employees

->WHERE EXISTS（

->SELECT *FROM salary WHERE 員工編號(hào)=2020002

->AND employees.員工編號(hào)=salary.員工編號(hào)）;

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

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

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

*FROM? employees WHERE 姓名=張宏。

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

在SQL語(yǔ)句中也要避免局部變量的使用，會(huì)掃描整張數(shù)據(jù)表。因?yàn)榫植孔兞康慕馕鍪窃诓樵?xún)的時(shí)候才進(jìn)行的。例如：SELECT num FROM? a WHERE name=@name 就可以進(jìn)行轉(zhuǎn)換為：SELECT num FROM a WITH（index（索引名字）） WHERE name=@name。

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

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

3結(jié)束語(yǔ)

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

參考文獻(xiàn)：

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

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

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

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

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

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

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