快捷導(dǎo)航

MySQL中Stmt 預(yù)處理提高效率問(wèn)題的小研究

更新時(shí)間：2011年08月14日 23:16:02 作者：

在oracle數(shù)據(jù)庫(kù)中，有一個(gè)變量綁定的用法，很多人都比較熟悉，可以調(diào)高數(shù)據(jù)庫(kù)效率，應(yīng)對(duì)高并發(fā)等，好吧，這其中并不包括我，當(dāng)同事問(wèn)我MySQL中有沒(méi)有類(lèi)似的寫(xiě)法時(shí)，我是很茫然的，于是就上網(wǎng)查，找到了如下一種寫(xiě)法

復(fù)制代碼代碼如下:

 
DELIMITER $$ 
set @stmt = 'select userid,username from myuser where userid between ? and ?'; 
prepare s1 from @stmt; 
set @s1 = 2; 
set @s2 = 100; 
execute s1 using @s1,@s2; 
deallocate prepare s1; 
$$ 
DELIMITER ; 

用這種形式寫(xiě)的查詢，可以隨意替換參數(shù)，給出代碼的人稱(chēng)之為預(yù)處理，我想這個(gè)應(yīng)該就是MySQL中的變量綁定吧……但是，在查資料的過(guò)程中我卻聽(tīng)到了兩種聲音，一種是，MySQL中有類(lèi)似Oracle變量綁定的寫(xiě)法，但沒(méi)有其實(shí)際作用，也就是只能方便編寫(xiě)，不能提高效率，這種說(shuō)法在幾個(gè)09年的帖子中看到：
http://www.itpub.net/thread-1210292-1-1.html
http://cuda.itpub.net/redirect.php?fid=73&tid=1210572&goto=nextnewset
另一種說(shuō)法是MySQL中的變量綁定是能確實(shí)提高效率的，這個(gè)是希望有的，那到底有木有，還是自己去試驗(yàn)下吧。
試驗(yàn)是在本機(jī)進(jìn)行的，數(shù)據(jù)量比較小，具體數(shù)字并不具有實(shí)際意義，但是，能用來(lái)說(shuō)明一些問(wèn)題，數(shù)據(jù)庫(kù)版本是mysql-5.1.57-win32免安裝版。
　　本著對(duì)數(shù)據(jù)庫(kù)不是很熟悉的態(tài)度^_^,試驗(yàn)過(guò)程中走了不少?gòu)澛?，此文以結(jié)論為主，就不列出實(shí)驗(yàn)的設(shè)計(jì)過(guò)程，文筆不好，文章寫(xiě)得有點(diǎn)枯燥，寫(xiě)出來(lái)是希望有人來(lái)拍磚，因?yàn)槲业贸龅慕Y(jié)論是：預(yù)處理在有沒(méi)有cache的情況下的執(zhí)行效率都不及直接執(zhí)行…… 我對(duì)自己的實(shí)驗(yàn)結(jié)果不愿接受。。如果說(shuō)預(yù)處理只為了規(guī)范下Query，使cache命中率提高的話個(gè)人覺(jué)得大材小用了，希望有比較了解的人能指出事實(shí)究竟是什么樣子的——NewSilen
實(shí)驗(yàn)準(zhǔn)備
　　第一個(gè)文件NormalQuery.sql

復(fù)制代碼代碼如下:

 
Set profiling=1; 
Select * From MyTable where DictID = 100601000004; 
Select DictID from MyTable limit 1,100; 
Select DictID from MyTable limit 2,100; 
/*從limit 1，100 到limit 100,100 此處省略重復(fù)代碼*/ 
...... 
Select DictID from MyTable limit 100,100; 
SELECT query_id,seq,STATE,10000*DURATION FROM information_schema.profiling INTO OUTFILE 'd:/NormalResults.csv' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'; 

第二個(gè)sql文件 StmtQuery.sql

復(fù)制代碼代碼如下:

 
Set profiling=1; 
Select * From MyTable where DictID = 100601000004; 
set @stmt = 'Select DictID from MyTable limit ?,?'; 
prepare s1 from @stmt; 
set @s = 100; 
set @s1 = 101; 
set @s2 = 102; 
...... 
set @s100 =200; 
execute s1 using @s1,@s; 
execute s1 using @s2,@s; 
...... 
execute s1 using @s100,@s; 
SELECT query_id,seq,STATE,10000*DURATION FROM information_schema.profiling INTO OUTFILE 'd:/StmtResults.csv' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'; 

做幾點(diǎn)小說(shuō)明：
1. Set profiling=1; 執(zhí)行此語(yǔ)句之后，可以從information_schema.profiling這張表中讀出語(yǔ)句執(zhí)行的詳細(xì)信息，其實(shí)包含不少內(nèi)容，包括我需要的時(shí)間信息，這是張臨時(shí)表，每新開(kāi)一個(gè)會(huì)話都要重新設(shè)置profiling屬性才能從這張表中讀取數(shù)據(jù)
2. Select * From MyTable where DictID = 100601000004;
　　這行代碼貌似和我們的實(shí)驗(yàn)沒(méi)什么關(guān)系，本來(lái)我也是這么認(rèn)為的，之所以加這句，是我在之前的摸索中發(fā)現(xiàn)，執(zhí)行過(guò)程中有個(gè)步驟是open table，如果是第一次打開(kāi)某張表，那時(shí)間是相當(dāng)長(zhǎng)的，所以在執(zhí)行后面的語(yǔ)句前，我先執(zhí)行了這行代碼打開(kāi)試驗(yàn)用的表
3. MySQL默認(rèn)在information_schema.profiling表中保存的查詢歷史是15條，可以修改profiling_history_size屬性來(lái)進(jìn)行調(diào)整，我希望他大一些讓我能一次取出足夠的數(shù)據(jù)，不過(guò)最大值只有100，盡管我調(diào)整為150，最后能夠查到的也只有100條，不過(guò)也夠了
4. SQL代碼我沒(méi)有全列出來(lái)，因?yàn)椴樵冋Z(yǔ)句差不多，上面代碼中用省略號(hào)表示了，最后的結(jié)果是兩個(gè)csv文件，個(gè)人習(xí)慣，你也可以把結(jié)果存到數(shù)據(jù)庫(kù)進(jìn)行分析
　　實(shí)驗(yàn)步驟
重啟數(shù)據(jù)庫(kù)，執(zhí)行文件NormalQuery.sql，執(zhí)行文件StmtQuery.sql，得到兩個(gè)結(jié)果文件
再重啟數(shù)據(jù)庫(kù)，執(zhí)行StmtQuery.sql，執(zhí)行文件NormalQuery.sql，得到另外兩個(gè)結(jié)果文件
　　實(shí)驗(yàn)結(jié)果
詳細(xì)結(jié)果在最后提供了附件下載，有興趣的朋友可以看下
　　結(jié)果分析
每一個(gè)SQL文件中執(zhí)行了一百個(gè)查詢語(yǔ)句，沒(méi)有重復(fù)的查詢語(yǔ)句，不存在查詢cache，統(tǒng)計(jì)執(zhí)行SQL的平均時(shí)間得出如下結(jié)果

從結(jié)果中可以看出，無(wú)論是先執(zhí)行還是后執(zhí)行，NormalQuery中的語(yǔ)句都比使用預(yù)處理語(yǔ)句的要快一些=.=！

那再來(lái)看看每一句查詢具體的情況，Normal和Stmt的query各執(zhí)行了兩百次，每一步的詳細(xì)信息如下：

從這里面可以看出，第一個(gè)，normalquery比stmtquery少一個(gè)步驟，第二個(gè)，雖然stmt在不少步驟上是優(yōu)于normal的,但在executing一步上輸?shù)籼?，最后結(jié)果上也是落敗

最后，再給出一個(gè)查詢緩存的實(shí)驗(yàn)結(jié)果，具體步驟就不列了

在查詢緩存的時(shí)候，Normal完勝……

寫(xiě)在最后

大概情況就是這樣，我回憶了一下，網(wǎng)上說(shuō)預(yù)處理可以提高效率的，基本都是用編程的方式去執(zhí)行查詢，不知道這個(gè)有沒(méi)有關(guān)系，基礎(chǔ)有限，希望園子里的大牛能看到，幫忙解惑
實(shí)驗(yàn)結(jié)果附件

MySQL預(yù)處理實(shí)驗(yàn)結(jié)果

您可能感興趣的文章: