宋永鵬

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

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

1 測試環(huán)境

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

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

2 索引的使用

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

2.1 數(shù)據(jù)庫響應(yīng)

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

圖1 索引對3 000條數(shù)據(jù)量的查詢影響

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

表2 索引在不同數(shù)據(jù)量下的查詢性能影響

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

圖2 索引對于數(shù)據(jù)量搜索的影響圖

通過圖2 分析可知，帶索引的搜索和不帶索引的區(qū)別是，帶索引的數(shù)據(jù)表搜索在3 000 條至30 萬條的數(shù)據(jù)量之間數(shù)據(jù)庫的響應(yīng)時間接近0，而且?guī)缀醪蛔?；不帶索引的搜索隨著數(shù)據(jù)量的增多會延長數(shù)據(jù)庫的響應(yīng)時間。

2.2 頁面加載

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

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

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

3 SQL優(yōu)化

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

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

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

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

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

4 分頁查詢

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

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

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

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

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

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

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

5 查詢緩存

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

圖4 查詢緩存執(zhí)行過程

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

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

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

6 結(jié)束語

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