緩存表和匯總表
有時提升性能最好的方法是在同一張表中保存衍生的冗余數據然而有時也需要創建一張完全獨立的匯總表或緩存表（特別是為滿足檢索的需求時）如果能容許少量的髒數據這是非常好的方法但是有時確實沒有選擇的余地（例如需要避免復雜昂貴的實時更新操作）
術語緩存表和匯總表沒有標准的含義我們用術語緩存表來表示存儲那些可以比較簡單地從schema 其他表獲取（但是每次獲取的速度比較慢）數據的表（例如邏輯上冗余的數據）而術語匯總表時則保存的是使用GROUP BY語句聚合數據的表（例如數據不是邏輯上冗余的）也有人使用術語累積表（RollUp Tables）稱呼這些表因為這些數據被累積了
仍然以網站為例假設需要計算之前 小時內發送的消息數在一個很繁忙的網站不可能維護一個實時精確的計數器作為替代方案可以每小時生成一張匯總表這樣也許一條簡單的查詢就可以做到並且比實時維護計數器要高效得多缺點是計數器並不是% 精確
如果必須獲得過去 小時准確的消息發送數量（沒有遺漏）有另外一種選擇以每小時匯總表為基礎把前 個完整的小時的統計表中的計數全部加起來最後再加上開始階段和結束階段不完整的小時內的計數假設統計表叫作msg_per_hr 並且這樣定義
CREATE TABLE msg_per_hr （
hr DATETIME NOT NULL
cnt INT UNSIGNED NOT NULL
PRIMARY KEY（hr）
）
可以通過把下面的三個語句的結果加起來得到過去 小時發送消息的總數我們使用LEFT（NOW（）） 來獲得當前的日期和時間最接近的小時
mysql> SELECT SUM（cnt） FROM msg_per_hr
> WHERE hr BETWEEN
> CONCAT（LEFT（NOW（） ） :） INTERVAL HOUR
> AND CONCAT（LEFT（NOW（） ） :） INTERVAL HOUR;
mysql> SELECT COUNT（*） FROM message
> WHERE posted >= NOW（） INTERVAL HOUR
> AND posted < CONCAT（LEFT（NOW（） ） :） INTERVAL HOUR;
mysql> SELECT COUNT（*） FROM message
> WHERE posted >= CONCAT（LEFT（NOW（） ） :）
不管是哪種方法不嚴格的計數或通過小范圍查詢填滿間隙的嚴格計數都比計算message 表的所有行要有效得多這是建立匯總表的最關鍵原因實時計算統計值是很昂貴的操作因為要麼需要掃描表中的大部分數據要麼查詢語句只能在某些特定的索引上才能有效運行而這類特定索引一般會對UPDATE 操作有影響所以一般不希望創建這樣的索引計算最活躍的用戶或者最常見的標簽是這種操作的典型例子緩存表則相反其對優化搜索和檢索查詢語句很有效這些查詢語句經常需要特殊的表和索引結構跟普通OLTP 操作用的表有些區別
例如可能會需要很多不同的索引組合來加速各種類型的查詢這些矛盾的需求有時需要創建一張只包含主表中部分列的緩存表一個有用的技巧是對緩存表使用不同的存儲引擎例如如果主表使用InnoDB用MyISAM 作為緩存表的引擎將會得到更小的索引占用空間並且可以做全文搜索有時甚至想把整個表導出MySQL插入到專門的搜索系統中獲得更高的搜索效率例如Lucene 或者Sphinx 搜索引擎
在使用緩存表和匯總表時必須決定是實時維護數據還是定期重建哪個更好依賴於應用程序但是定期重建並不只是節省資源也可以保持表不會有很多碎片以及有完全順序組織的索引（這會更加高效）
當重建匯總表和緩存表時通常需要保證數據在操作時依然可用這就需要通過使用影子表來實現 影子表指的是一張在真實表背後創建的表當完成了建表操作後可以通過一個原子的重命名操作切換影子表和原表例如如果需要重建 my_summary則可以先創建 my_summary_new然後填充好數據最後和真實表做切換
mysql> DROP TABLE IF EXISTS my_summary_new my_summary_old;
mysql> CREATE TABLE my_summary_new LIKE my_summary;
populate my_summary_new as desired
mysql> RENAME TABLE my_summary TO my_summary_old my_summary_new TO my_summary;
如果像上面的例子一樣在將my_summary 這個名字分配給新建的表之前將原始的my_summary 表重命名為 my_summary_old就可以在下一次重建之前一直保留舊版本的數據如果新表有問題則可以很容易地進行快速回滾操作
返回目錄高性能MySQL
編輯推薦
ASPNET MVC 框架揭秘
Oracle索引技術
ASP NET開發培訓視頻教程
數據倉庫與數據挖掘培訓視頻教程
From:http://tw.wingwit.com/Article/program/MySQL/201311/29675.html
