MySQL中Stmt 預先處理提高效率問題的小研究

複製代碼 代碼如下: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 ;

用這種形式寫的查詢，可以隨意替換參數，給出代碼的人稱之為預先處理，我想這個應該就是MySQL中的變數綁定吧……但是，在查資料的過程中我卻聽到了兩種聲音，一種是，MySQL中有類似Oracle變數綁定的寫法，但沒有其實際作用，也就是只能方便編寫，不能提高效率，這種說法在幾個09年的文章中看到：
http://www.itpub.net/thread-1210292-1-1.html
http://cuda.itpub.net/redirect.php?fid=73&tid=1210572&goto=nextnewset
另一種說法是MySQL中的變數綁定是能確實提高效率的，這個是希望有的，那到底有木有，還是自己去實驗下吧。
實驗是在本機進行的，資料量比較小，具體數字並不具有實際意義，但是，能用來說明一些問題，資料庫版本是mysql-5.1.57-win32免安裝版。
　　本著對資料庫不是很熟悉的態度^_^,實驗過程中走了不少彎路，此文以結論為主，就不列出實驗的設計過程，文筆不好，文章寫得有點枯燥，寫出來是希望有人來拍磚，因為我得出的結論是：預先處理在有沒有cache的情況下的執行效率都不及直接執行…… 我對自己的實驗結果不願接受。。如果說預先處理只為了規範下Query，使cache命中率提高的話個人覺得大材小用了，希望有比較瞭解的人能指出事實究竟是什麼樣子的——NewSilen
實驗準備
　　第一個檔案NormalQuery.sql 複製代碼 代碼如下: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 此處省略重複代碼*/
......
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';

第二個sql檔案 StmtQuery.sql 複製代碼 代碼如下: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';

做幾點小說明：
1. Set profiling=1; 執行此語句之後，可以從information_schema.profiling這張表中讀出語句執行的詳細資料，其實包含不少內容，包括我需要的時間資訊，這是張暫存資料表，每新開一個會話都要重新設定profiling屬性才能從這張表中讀取資料
2. Select * From MyTable where DictID = 100601000004;
　　這行代碼貌似和我們的實驗沒什麼關係，本來我也是這麼認為的，之所以加這句，是我在之前的摸索中發現，執行過程中有個步驟是open table，如果是第一次開啟某張表，那時間是相當長的，所以在執行後面的語句前，我先執行了這行代碼開啟實驗用的表
3. MySQL預設在information_schema.profiling表中儲存的查詢曆史是15條，可以修改profiling_history_size屬性來進行調整，我希望他大一些讓我能一次取出足夠的資料，不過最大值只有100，儘管我調整為150，最後能夠查到的也只有100條，不過也夠了
4. SQL代碼我沒有全列出來，因為查詢語句差不多，上面代碼中用省略符號表示了，最後的結果是兩個csv檔案，個人習慣，你也可以把結果存到資料庫進行分析
　　實驗步驟
重啟資料庫，執行檔案NormalQuery.sql，執行檔案StmtQuery.sql，得到兩個結果檔案
再重啟資料庫，執行StmtQuery.sql，執行檔案NormalQuery.sql，得到另外兩個結果檔案
　　實驗結果
詳細結果在最後提供了附件下載，有興趣的朋友可以看下
　　結果分析
每一個SQL檔案中執行了一百個查詢語句，沒有重複的查詢語句，不存在查詢cache，統計執行SQL的平均時間得出如下結果

從結果中可以看出，無論是先執行還是後執行，NormalQuery中的語句都比使用預先處理語句的要快一些=.=！

那再來看看每一句查詢具體的情況，Normal和Stmt的query各執行了兩百次，每一步的詳細資料如下：

從這裡面可以看出，第一個，normalquery比stmtquery少一個步驟，第二個，雖然stmt在不少步驟上是優於normal的,但在executing一步上輸掉太多，最後結果上也是落敗

最後，再給出一個查詢快取的實驗結果，具體步驟就不列了

在查詢快取的時候，Normal完勝……

寫在最後

大概情況就是這樣，我回憶了一下，網上說預先處理可以提高效率的，基本都是用編程的方式去執行查詢，不知道這個有沒有關係，基礎有限，希望園子裡的大牛能看到，幫忙解惑
實驗結果附件

MySQL預先處理實驗結果