mysql 汇总表缓存表_4.4 缓存表和汇总表

4.4 缓存表和汇总表

有时提升性能***的方法是在同一张表中保存衍生的冗余数据。然而，有时也需要创建一张完全独立的汇总表或缓存表(特别是为满足检索的需求时)。如果能容许少量的脏数据，这是非常好的方法，但是有时确实没有选择的余地(例如，需要避免复杂、昂贵的实时更新操作)。

术语“缓存表”和“汇总表”没有标准的含义。我们用术语“缓存表”来表示存储那些可以比较简单地从schema 其他表获取(但是每次获取的速度比较慢)数据的表(例如，逻辑上冗余的数据)。而术语“汇总表”时，则保存的是使用GROUP BY语句聚合数据的表(例如，数据不是逻辑上冗余的)。也有人使用术语“累积表(Roll-Up Tables)”称呼这些表。因为这些数据被“累积”了。

仍然以网站为例，假设需要计算之前24 小时内发送的消息数。在一个很繁忙的网站不可能维护一个实时精确的计数器。作为替代方案，可以每小时生成一张汇总表。这样也许一条简单的查询就可以做到，并且比实时维护计数器要高效得多。缺点是计数器并不是100% 精确。

如果必须获得过去24 小时准确的消息发送数量(没有遗漏)，有另外一种选择。以每小时汇总表为基础，把前23 个完整的小时的统计表中的计数全部加起来，***再加上开始阶段和结束阶段不完整的小时内的计数。假设统计表叫作msg_per_hr 并且这样定义：

CREATE TABLE msg_per_hr (

hr DATETIME NOT NULL,

cnt INT UNSIGNED NOT NULL,

PRIMARY KEY(hr)

);

可以通过把下面的三个语句的结果加起来，得到过去24 小时发送消息的总数。我们使用LEFT(NOW(),14) 来获得当前的日期和时间最接近的小时：

mysql>SELECT SUM(cnt) FROM msg_per_hr

->WHERE hr BETWEEN

->CONCAT(LEFT(NOW(), 14), '00:00') - INTERVAL 23 HOUR

->AND CONCAT(LEFT(NOW(), 14), '00:00') - INTERVAL 1 HOUR;

mysql>SELECT COUNT(*) FROM message

->WHERE posted>= NOW() - INTERVAL 24 HOUR

->AND posted

mysql>SELECT COUNT(*) FROM message

->WHERE posted>= CONCAT(LEFT(NOW(), 14), '00:00');

不管是哪种方法——不严格的计数或通过小范围查询填满间隙的严格计数——都比计算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，就可以在下一次重建之前一直保留旧版本的数据。如果新表有问题，则可以很容易地进行快速回滚操作。

【责任编辑：book TEL：(010)68476606】

点赞 0