宋永鵬

（山東氣象信息中心，山東濟(jì)南 250031）

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

1 測(cè)試環(huán)境

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

表1 山東氣象部門圖片資料云平臺(tái)Pic表

2 索引的使用

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

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

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

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

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

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

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

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

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

2.2 頁面加載

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

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

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

3 SQL優(yōu)化

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

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

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

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

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

4 分頁查詢

當(dāng)業(yè)務(wù)需要對(duì)全表進(jìn)行檢索而表中數(shù)據(jù)較多時(shí)，一次性全表查詢的效率會(huì)變得很低，查詢效率的降低隨著數(shù)據(jù)量的增加更加明顯，客戶端一次性展示過多的數(shù)據(jù)會(huì)導(dǎo)致頁面卡死，這時(shí)需要使用分頁查詢，一次只顯示一部分?jǐn)?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 語句實(shí)現(xiàn)，表示搜索Pic 表中id 字段大于1 000 的前100 行數(shù)據(jù)。由于id 字段的默認(rèn)值是由1 開始并逐1 遞增，所以兩者實(shí)現(xiàn)的功能相同，都是從表中1 000 開始取前100 行數(shù)據(jù)。

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

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

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

表3 兩種分頁查詢功能的效率對(duì)比

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

5 查詢緩存

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

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

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

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

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

6 結(jié)束語

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