基于MySQL 的數據庫查詢性能優化

宋永鵬

（山東氣象信息中心，山東濟南 250031）

MySQL 是現今最流行的開源關系型數據庫，MySQL+PHP 的開發環境是使用最廣泛的Web 應用開發組合，文中測試環境使用山東省氣象部門圖片資料云平臺的MySQL 數據庫。數據庫的查詢操作越來越成為整個應用的性能瓶頸，對于Web 應用尤其明顯[1]，一個應用的吞吐量瓶頸往往出現在數據庫的處理速度上。隨著應用程序的使用，數據逐漸增多，數據庫的查詢壓力也逐漸增大。查詢語句的性能體現在數據庫的響應時間上，過多的重復查詢以及耗時過長的操作會影響數據庫的性能。而數據庫的性能無法只依靠數據庫管理員的日常維護來提升，同樣是程序員需要去關注的。優秀的庫表設計結構和數據庫操作（尤其是查詢數據表的SQL 語句）可提高數據庫的響應速度，進而提高應用的用戶體驗度，縮短Web 應用的響應時間并避免對其他應用組件的影響[2]。

1 測試環境

測試硬件為Dell 一體機+4G 內存；測試軟件為Win7 操作系統+MySQL5.5+山東省氣象部門圖片資料云平臺Pic 數據表（表1）。為了測試4 種查詢優化技術在不同數據量下的影響，利用數據庫存儲過程對Pic 表分批次插入海量的數據，分別是3 000 條、3 萬條和30 萬條。

表1 山東氣象部門圖片資料云平臺Pic表

2 索引的使用

索引是從數據中提取的具有標識性的關鍵字，并且包含對應數據的映射關系，為特定的數據庫字段進行算法排序，能夠幫助存儲引擎快速找到記錄[3]。MySQL 索引的建立對于數據庫的高效運行很重要，類似通過漢語字典的目錄頁按拼音和部首查字的功能，查詢語句通過對字段的索引能夠大大提高檢索速度[4]。

2.1 數據庫響應

開啟MySQL 性能分析功能后，對Pic 表使用Show Profile 語句，分別計算Pic 表包含索引和不包含索引時的數據庫響應時間。分別在3 000 條、3 萬條和30 萬條的測試數據背景下，通過查找date 字段特定值數據的查詢語句測試不包含索引和包含索引時數據庫的響應時間。圖1 所示為數據表中包含3 000條數據時date 字段不帶索引和帶索引的查詢語句以及數據庫響應時間。

圖1 索引對3 000條數據量的查詢影響

圖1 的黑色背景是MySQL 自帶命令行的截圖，白色背景是由文中整理所得結果。當查找date 字段值是2019-11-11 的數據時，不帶索引與帶索引的數據庫響應時間分別是0.011 4 s 和0.000 636 s，帶索引的響應速度是不帶索引的18 倍。使用存儲過程依次向Pic 表中插入3 萬條和30 萬條數據，分別進行條件為date 字段值是2019-11-11 的查詢測試，結果如表2 所示。

表2 索引在不同數據量下的查詢性能影響

由表2 可知，Pic 表有3 萬條數據量時，不帶索引與帶索引的數據庫響應時間分別是0.138 671 s 和0.000 701 5 s，帶索引的響應速度是不帶索引的197倍；表中包含30 萬條數據量時，不帶索引與帶索引的數據庫響應時間分別是0.876 840 5 s 和0.000 663 s，帶索引的響應速度是不帶索引的1 323 倍。將數據量和數據庫響應時間分別作為X、Y軸，作出帶索引和不帶索引的excel 對比折線圖，如圖2 所示。

圖2 索引對于數據量搜索的影響圖

通過圖2 分析可知，帶索引的搜索和不帶索引的區別是，帶索引的數據表搜索在3 000 條至30 萬條的數據量之間數據庫的響應時間接近0，而且幾乎不變；不帶索引的搜索隨著數據量的增多會延長數據庫的響應時間。

2.2 頁面加載

上一節內容已說明索引對數據庫響應速度的影響，對30 萬條數據進行查詢的數據庫響應時間比不帶索引縮短0.88 s 左右，文中利用Google Chrome 開發者工具對頁面加載時間進行對比分析。創建索引時，需要的索引應用在SQL 查詢語句的條件中，一般作為where 子句的條件，索引就像漢語字段的目錄頁，可以按照拼音和偏旁筆畫快速查到需要的字[5]。實際上，索引也是一張表，該表保存了主鍵與索引字段，并指向實體表的記錄，所以在進行表的插入、更新和刪除操作時，MySQL 不僅要操作數據，還要操作索引[6]。

不帶索引的搜索頁面加載時間為2.5 s，帶索引的頁面加載時間為1.5 s?，F代快節奏的生活使得web 應用自然傾向于加載更快和使用更便捷的趨勢，1 s 的性能提升對用戶友好度有著巨大的影響。

索引的有效使用需要查詢語句的配合，當查詢語句的條件以%開頭時，引擎會跳過索引進行全表掃描，導致索引失效，對索引列的＜＞、not in、not exist和!=的操作會產生同樣的效果[7]。數據唯一性差的字段(比如性別)只有兩種可能性，無異于全表掃描。對于同樣頻繁更新的字段(例如Logincount 登陸次數)，頻繁的數值變化也導致索引頻繁變化，這兩種情況下的索引反而會增大數據庫的工作量[8]。

3 SQL優化

一般在項目上線初期，由于業務數據量相對較少，一些SQL 的執行效率對程序運行效率的影響不太明顯。而隨著時間的積累，業務數據量逐漸增多，SQL 的執行效率對應用程序運行效率的影響逐漸增大[9]，而且優化并不總是在一個單純的環境進行，還很可能是一個復雜的已投產系統，業務的穩定性和可持續性通常比性能更重要，因此在開發初期對SQL 的優化很有必要[10]。開發初期針對SQL 語句優化的重要兩點是在Select 子句中避免使用“*”和對查詢結果的記錄使用limit 進行限定。

3.1 select子句中避免使用“*”

MySQl 在解析的過程中，會將查詢語句中的“*”依次轉換成所有的列名，這個工作是通過查詢數據字典完成的，這意味著會耗費更多的時間，應盡力避免對全部字段進行列表，而應只列出所需的字段名[11]。當SQL 的查詢功能是搜索Pic 表特定用戶上傳的圖片名時，分別對select * from pic2 where username=′testname′和select filename from pic where username=′testname′進行Show Profile 分析，對30 萬條數據使用“*”的全部字段名搜索，耗時為0.901 688 75 s，對特定字段名搜索，耗時為0.403 017 5 s，速度提高了124%。為避免全列名搜索，可只將業務需要的字段在select 語句中列出，從而提高查詢語句的效率。頁面加載時，由于where 語句限定條件而不會對全表進行檢索，因此速度也提高了50%左右。

3.2 使用limit對查詢結果的記錄進行限定

使用查詢語句時，經常要返回前幾行或者中間幾行數據，limit 子句就是被用于強制select 語句返回指定的記錄數[12]。當數據量很大時，如果只需要查詢一部分數據，那么就要避免全表掃描，才能提高查詢效率。當搜索Pic 表特定用戶上傳的一個圖片信息時，分別對select * from pic2 where username=′testname1′limit 1和select*from pic2 where username=′testname1′進行Show Profile分析，不帶limit和帶limit的查詢語句的數據庫響應時間分別是2.338 571 25 s和0.405 127 25 s，速度提高了478%。不帶limit 的查詢語句為了搜到這條數據會進行全表掃描，加上limit 1 后，只要找到對應的一條數據，就不會繼續向下掃描，效率就會大大提高，此外，limit 還應用于分頁查詢功能。

4 分頁查詢

當業務需要對全表進行檢索而表中數據較多時，一次性全表查詢的效率會變得很低，查詢效率的降低隨著數據量的增加更加明顯，客戶端一次性展示過多的數據會導致頁面卡死，這時需要使用分頁查詢，一次只顯示一部分數據正是分頁查詢功能的本質[13]。分頁查詢包括數據限定和id 限定兩種使用方法。

Select * from pic limit 1000,100 語句完成了基本的數據限定分頁查詢功能，搜索Pic 表中從第1000條數據開始之后的100 條數據；id 限定分頁查詢由Select * from pic where id＞1000 limit 100 語句實現，表示搜索Pic 表中id 字段大于1 000 的前100 行數據。由于id 字段的默認值是由1 開始并逐1 遞增，所以兩者實現的功能相同，都是從表中1 000 開始取前100 行數據。

文中針對兩種分頁查詢功能進行4 次測試，分別從1 000 行、1 萬行、10 萬行和20 萬行開始查詢前100 行數據的響應時間，結果如表3 所示。

通過表3 可知，對于數據限定分頁查詢方式，隨著開始查詢行數的增大，查詢時間急劇增加，特別是10 萬行之后，這種分頁查詢方式會從數據庫第一條記錄開始掃描，所以查詢的數據越多，查詢速度越慢。對于id 限定分頁查詢方式，由于數據表的id 字段默認是連續自增的，所以使用id 限定優化的方式能夠優化分頁查詢速度。文中根據查詢的頁數和查詢的記錄數可以算出id 的范圍。將起始行和數據庫響應時間分別作為X、Y軸，作出對比分頁和id 限定分頁的excel 折線圖，如圖3 所示。

圖3 id限定分頁查詢的影響圖

表3 兩種分頁查詢功能的效率對比

通過圖3 分析可知，數據限定分頁查詢和id 限定分頁查詢的區別是，數據分頁查詢隨著起始行數的增多會延長數據庫的響應時間；id 限定分頁查詢隨著起始行數的增多產生的數據庫響應時間的變化微乎其微。對于頁面的加載速度，由于包含了圖片的加載時間，雖然沒有像數據庫的響應速度一樣呈現幾何級數的增長，但是也大大提升了用戶的體驗。

5 查詢緩存

查詢緩存是MySQL 在內存中建立的一個存儲空間，用于保存Select 語句的返回結果。當同一個Select 語句再次查詢時，會直接返回之前的結果，而跳過解析、優化和執行的階段[14]。由于無須經過數據庫的檢索，而是直接將已有結果返回，所以對應用程序查詢性能的提升是不言而喻的。圖4 是開啟數據庫查詢緩存功能后Select 查詢語句的執行過程。

圖4 查詢緩存執行過程

通過SHOW VARIABLES LIKE′%query_cache%′命令來查詢是否開啟，該功能將新Select 語句和該查詢語句的結果集做了一個HASH 映射，并保存在一定的內存區域中。當客戶端發起SQL 查詢時，查詢緩存的查找邏輯先對SQL 進行相應的權限認證，接著進行緩存查找，該查找對SQL 語句嚴格限定，包括字母的大小寫、字符集以及空格。當相同的SQL 語句在緩存中被找到時，將保存數據集結果并返回應用程序；反之，將新的SQL 語句寫入緩存并搜索數據庫返回應用程序[15]。

查詢緩存功能不需要經過Optimizer 模塊進行執行計劃的分析優化，更不需要與任何搜索引擎的交互，減少了大量的磁盤I/O 操作和CPU 運算，所以效率是非常高的。

當表結構或者數據發生改變時，也就是Insert、Update、Truncate、Alter Table 或Drop Table 等操作導致緩存數據失效，那么該表相關的所有緩存數據都將失效，而且數據表發生變化時有可能對應的查詢結果并未發生變更，所以雖然查詢緩存的機制看起來效率較低，但是代價是很小的，對于一個非常繁忙的系統是非常重要的[16-18]，所以查詢緩存功能適用于有大量查詢的應用而不適用于大量數據更新的應用。

6 結束語

常見的數據庫查詢優化方法包括由運維人員完成的數據庫所在服務器的內核優化、分表以及MySQL 配置參數的優化（進行壓力測試來進行參數的調整）。而索引優化、SQL 優化、分頁查詢和查詢緩存優化是程序員在開發過程中直接面對的問題，是數據庫查詢性能、應用程序響應速度和用戶體驗的關鍵。