缓存表和汇总表
有时提升性能最好的方法是在同一张表中保存衍生的冗余数据然而有时也需要创建一张完全独立的汇总表或缓存表（特别是为满足检索的需求时）如果能容许少量的髒数据这是非常好的方法但是有时确实没有选择的余地（例如需要避免复杂昂贵的实时更新操作）
术语缓存表和汇总表没有标准的含义我们用术语缓存表来表示存储那些可以比较简单地从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开发培训视频教程
数据仓库与数据挖掘培训视频教程
