本文章拿來學慣用||參考資料:http://www.2cto.com/database/201308/236361.html 對MySql查詢緩存及SQL Server過程緩存的理解及總結 一、MySql的Query Cache 1、Query Cache MySQL Query Cache是用來緩 ...
本文章拿來學慣用||參考資料:http://www.2cto.com/database/201308/236361.html
對MySql查詢緩存及SQL Server過程緩存的理解及總結 一、MySql的Query Cache 1、Query Cache MySQL Query Cache是用來緩存我們所執行的SELECT語句以及該語句的結果集。MySql在實現Query Cache的具體技術細節上類似典型的KV存儲，就是將SELECT語句和該查詢語句的結果集做了一個HASH映射並保存在一定的記憶體區域中。當客戶端發起SQL查詢時，Query Cache的查找邏輯是，先對SQL進行相應的許可權驗證，接著就通過Query Cache來查找結果。它不需要經過Optimizer模塊進行執行計劃的分析優化，更不需要發生同任何存儲引擎的交互，減少了大量的磁碟IO和CPU運算，所以有時候效率非常高。 2、Query Cache設置參數 可以通過調整 MySQL的參數打開並設置它的Query Cache功能，主要有以下5個參數： （1）、query_cache_limit：允許緩存的單條查詢結果集的最大容量，預設是1MB，超過此參數設置的查詢結果集將不會被緩存； （2）、query_cache_min_res_unit：設置查詢緩存Query Cache每次分配記憶體的最小空間大小，即每個查詢的緩存最小占用的記憶體空間大小； （3）、query_cache_size：設置 Query Cache 所使用的記憶體大小，預設值為0，大小必須是1024的整數倍，如果不是整數倍，MySQL 會自動調整降低最小量以達到1024的倍數； （4）、query_cache_type：控制 Query Cache 功能的開關，可以設置為0、1、2三種，意義分別如下： a、0(OFF)：關閉 Query Cache 功能，任何情況下都不會使用 Query Cache； b、1(ON)：開啟 Query Cache 功能，但是當SELECT語句中使用SQL_NO_CACHE提示後，將不使用Query Cache； c、2(DEMAND)：開啟Query Cache 功能，但是只有當SELECT語句中使用了SQL_CACHE 提示後，才使用Query Cache。 （5）、query_cache_wlock_invalidate：控制當有寫鎖定發生在表上的時刻是否先失效該表相關的Query Cache，如果設置為 1(TRUE)，則在寫鎖定的同時將失效該表相關的所有Query Cache，如果設置為0(FALSE)則在鎖定時刻仍然允許讀取該表相關的Query Cache。 3、Query Cache和性能 任何事情過猶不及，尤其對於某些寫頻繁的系統，開啟Query Cache功能可能並不能讓系統性能有提升，有時反而會有下降。原因是MySql為了保證Query Cache緩存的內容和實際數據絕對一致，當某個數據表發生了更新、刪除及插入操作，MySql都會強制使所有引用到該表的查詢SQL的Query Cache失效。對於密集寫操作，啟用查詢緩存後很可能造成頻繁的緩存失效，間接引發記憶體激增及CPU飆升，對已經非常忙碌的資料庫系統這是一種極大的負擔。 4、其他 Query Cache因MySql的存儲引擎不同而實現略有差異，比如MyISAM，緩存的結果集存儲在OS Cache中，而最流行的InnoDB則放在Buffer Pool中。 二、SQL Server的Procedure Cache SQL Server沒有類似MySql的Query Cache機制，但是它有自己的緩存機制。SQL Server不會簡單直接地緩存SQL查詢結果集，而是緩存它所讀取過的查詢數據頁（數據緩存Data Buffer），同時它還緩存執行計劃（過程緩存Procedure Cache)，下麵就談談我們所熟知的過程緩存。 1、SQL執行過程 SQL語句在執行前首先需要被編譯，接著需要通過SQL Server查詢引擎進行優化，然後得到優化後的執行計劃，最後SQL按照執行計劃被執行。 2、過程緩存（Procedure Cache） 創建執行計劃會占用CPU資源,當執行計劃被創建後，SQL Server查詢引擎預設會自動緩存執行計劃。 對於整體相似，僅僅是參數不同的SQL語句，SQL Server可以重用緩存的執行計劃。 但對於不同的SQL語句，SQL Server並不能重覆使用以前的執行計劃，而需要重新編譯出一個新的執行計劃，因為SQL Server查詢引擎會自動緩存執行計劃，每一個新的執行計劃都會占用SQL Server的記憶體。 在SQL Server可用記憶體足夠使用的情況下，查詢引擎並不主動清除以前保存的查詢計劃。所以，某些情況下，一條相似的SQL語句，僅僅因為寫法不同，而憑空多出了很多執行計劃，對於相似的SQL，這些多餘的執行計劃白白地占據著記憶體，大大影響SQL Server中緩存的查詢計劃數目。 對於上面這種情況，如果限定了SQL Server最大可用記憶體，它將導致SQL Server可用記憶體減少，從而在執行查詢時尤其是大的數據查詢時與磁碟發生更多的記憶體頁交換；如果沒有設置最大可用記憶體，則SQL Server由於緩存了太多執行計劃，從而使記憶體占用過大。 3、如何減少過程緩存 對於減少過程緩存的占用，主要是可以通過使用參數化查詢。 參數化查詢的關鍵是查詢優化器將創建一個可以重用的緩存計劃（SQL Server查詢優化器將查詢重新編寫為一個參數化SQL語句），這個可重用的緩存計劃消除了對這些類似SQL語句的每一次執行都創建一個緩存計劃的需求。通過創建一個可重用計劃，SQL Server就減少了存放類似的執行計劃所需的記憶體使用。 對於開發人員，我們一般可以通過下麵兩種方式實現參數化查詢： （1）、使用存儲過程執行SQL語句； （2）、使用sp_executesql 方式執行SQL語句。 關於使用存儲過程執行SQL，再說句題外話：對於存儲過程一直以來有頗多爭議，比如ORM派認為存儲過程是完全面向過程的不易擴展不易維護的等等等等。根據我個人的開發經驗，簡單的幾乎沒有邏輯的存儲過程我建議多用，但是複雜的存儲過程一直以來都是BUG集中營，而且後期維護成本奇高（聽我司架構師講過，某重要業務系統的資料庫有個八千多行的存儲過程，兩百多個變數，沒有人敢動），邏輯最好通過應對劇烈變化的業務邏輯層來寫。現在我們有了成熟的ORM，還有分層，開發中要絕對避免寫過長且邏輯複雜的存儲過程，否則面對變化，日積月累再出現幾個八千行的存儲過程也不是沒有可能。
對MySql查詢緩存及SQL Server過程緩存的理解及總結 一、MySql的Query Cache 1、Query Cache MySQL Query Cache是用來緩存我們所執行的SELECT語句以及該語句的結果集。MySql在實現Query Cache的具體技術細節上類似典型的KV存儲，就是將SELECT語句和該查詢語句的結果集做了一個HASH映射並保存在一定的記憶體區域中。當客戶端發起SQL查詢時，Query Cache的查找邏輯是，先對SQL進行相應的許可權驗證，接著就通過Query Cache來查找結果。它不需要經過Optimizer模塊進行執行計劃的分析優化，更不需要發生同任何存儲引擎的交互，減少了大量的磁碟IO和CPU運算，所以有時候效率非常高。 2、Query Cache設置參數 可以通過調整 MySQL的參數打開並設置它的Query Cache功能，主要有以下5個參數： （1）、query_cache_limit：允許緩存的單條查詢結果集的最大容量，預設是1MB，超過此參數設置的查詢結果集將不會被緩存； （2）、query_cache_min_res_unit：設置查詢緩存Query Cache每次分配記憶體的最小空間大小，即每個查詢的緩存最小占用的記憶體空間大小； （3）、query_cache_size：設置 Query Cache 所使用的記憶體大小，預設值為0，大小必須是1024的整數倍，如果不是整數倍，MySQL 會自動調整降低最小量以達到1024的倍數； （4）、query_cache_type：控制 Query Cache 功能的開關，可以設置為0、1、2三種，意義分別如下： a、0(OFF)：關閉 Query Cache 功能，任何情況下都不會使用 Query Cache； b、1(ON)：開啟 Query Cache 功能，但是當SELECT語句中使用SQL_NO_CACHE提示後，將不使用Query Cache； c、2(DEMAND)：開啟Query Cache 功能，但是只有當SELECT語句中使用了SQL_CACHE 提示後，才使用Query Cache。 （5）、query_cache_wlock_invalidate：控制當有寫鎖定發生在表上的時刻是否先失效該表相關的Query Cache，如果設置為 1(TRUE)，則在寫鎖定的同時將失效該表相關的所有Query Cache，如果設置為0(FALSE)則在鎖定時刻仍然允許讀取該表相關的Query Cache。 3、Query Cache和性能 任何事情過猶不及，尤其對於某些寫頻繁的系統，開啟Query Cache功能可能並不能讓系統性能有提升，有時反而會有下降。原因是MySql為了保證Query Cache緩存的內容和實際數據絕對一致，當某個數據表發生了更新、刪除及插入操作，MySql都會強制使所有引用到該表的查詢SQL的Query Cache失效。對於密集寫操作，啟用查詢緩存後很可能造成頻繁的緩存失效，間接引發記憶體激增及CPU飆升，對已經非常忙碌的資料庫系統這是一種極大的負擔。 4、其他 Query Cache因MySql的存儲引擎不同而實現略有差異，比如MyISAM，緩存的結果集存儲在OS Cache中，而最流行的InnoDB則放在Buffer Pool中。 二、SQL Server的Procedure Cache SQL Server沒有類似MySql的Query Cache機制，但是它有自己的緩存機制。SQL Server不會簡單直接地緩存SQL查詢結果集，而是緩存它所讀取過的查詢數據頁（數據緩存Data Buffer），同時它還緩存執行計劃（過程緩存Procedure Cache)，下麵就談談我們所熟知的過程緩存。 1、SQL執行過程 SQL語句在執行前首先需要被編譯，接著需要通過SQL Server查詢引擎進行優化，然後得到優化後的執行計劃，最後SQL按照執行計劃被執行。 2、過程緩存（Procedure Cache） 創建執行計劃會占用CPU資源,當執行計劃被創建後，SQL Server查詢引擎預設會自動緩存執行計劃。 對於整體相似，僅僅是參數不同的SQL語句，SQL Server可以重用緩存的執行計劃。 但對於不同的SQL語句，SQL Server並不能重覆使用以前的執行計劃，而需要重新編譯出一個新的執行計劃，因為SQL Server查詢引擎會自動緩存執行計劃，每一個新的執行計劃都會占用SQL Server的記憶體。 在SQL Server可用記憶體足夠使用的情況下，查詢引擎並不主動清除以前保存的查詢計劃。所以，某些情況下，一條相似的SQL語句，僅僅因為寫法不同，而憑空多出了很多執行計劃，對於相似的SQL，這些多餘的執行計劃白白地占據著記憶體，大大影響SQL Server中緩存的查詢計劃數目。 對於上面這種情況，如果限定了SQL Server最大可用記憶體，它將導致SQL Server可用記憶體減少，從而在執行查詢時尤其是大的數據查詢時與磁碟發生更多的記憶體頁交換；如果沒有設置最大可用記憶體，則SQL Server由於緩存了太多執行計劃，從而使記憶體占用過大。 3、如何減少過程緩存 對於減少過程緩存的占用，主要是可以通過使用參數化查詢。 參數化查詢的關鍵是查詢優化器將創建一個可以重用的緩存計劃（SQL Server查詢優化器將查詢重新編寫為一個參數化SQL語句），這個可重用的緩存計劃消除了對這些類似SQL語句的每一次執行都創建一個緩存計劃的需求。通過創建一個可重用計劃，SQL Server就減少了存放類似的執行計劃所需的記憶體使用。 對於開發人員，我們一般可以通過下麵兩種方式實現參數化查詢： （1）、使用存儲過程執行SQL語句； （2）、使用sp_executesql 方式執行SQL語句。 關於使用存儲過程執行SQL，再說句題外話：對於存儲過程一直以來有頗多爭議，比如ORM派認為存儲過程是完全面向過程的不易擴展不易維護的等等等等。根據我個人的開發經驗，簡單的幾乎沒有邏輯的存儲過程我建議多用，但是複雜的存儲過程一直以來都是BUG集中營，而且後期維護成本奇高（聽我司架構師講過，某重要業務系統的資料庫有個八千多行的存儲過程，兩百多個變數，沒有人敢動），邏輯最好通過應對劇烈變化的業務邏輯層來寫。現在我們有了成熟的ORM，還有分層，開發中要絕對避免寫過長且邏輯複雜的存儲過程，否則面對變化，日積月累再出現幾個八千行的存儲過程也不是沒有可能。
