豆利
摘要：隨著網絡數據量的增大，用戶對資料庫查詢的要求也越來越高，普通的查詢有時很難滿足要求，迫切需要對於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.
【通聯編輯：王力】
摘要：隨著網絡數據量的增大，用戶對資料庫查詢的要求也越來越高，普通的查詢有時很難滿足要求，迫切需要對於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.
【通聯編輯：王力】
收藏