缓存表和汇总表(计数器表)
简述
有时提升性能最好的方法是在同一张表中保存衍生的冗余数据。然而,有时也需要创建一张完全独立的汇总表或缓存表。
缓存表和汇总表与冗余的列有异曲同工之妙,只不过实现在单独的表中。
缓存表中存储与其它的表逻辑冗余的数据,将大表中活跃的数据单独拿出来构建成一张小表,通过减少单个表的数据量来提高查询性能,即所谓的热数据分离,尤其是在大表中的某些小部分数据被频繁访问时更能体现其带来的好处。
汇总表中存储与其它的表逻辑不同的衍生数据,通过减少聚合次数来提高查询性能,同样,这些数据也经常被访问。
但两者都必须面对数据的同步问题,对于允许最终一致性的场景来说,建立缓存表或者汇总表,无疑是一个好主意,但是如果需要实时更新,那就得多花一些心思来斟酌一二了。
- 缓存表用来存储哪些每次获取速度比较慢(其他表中活跃)的数据的表
- 汇总表保存你的是使用groupby语句聚合数据的表
实际例子
仍然以网站为例,假设需要计算之前24小时内发送的消息数。在一个很繁忙的网站不可能维护一个实时精确的计数器。作为替代方案,可以每小时生成一张汇总表。这样也许一条简单的查询就可以做到,并且比实时维护计数器要高效得多。缺点是计数器并不是100%精确。
如果必须获得过去24小时准确的消息发送数量(没有遗漏),有另外一种选择。 以每小时汇总表为基础,把前23个完整的小时的统计表中的计数全部加起来,最后再加上开始阶段和结束阶段不完整的小时内的计数。
不严格的计数或通过小范围查询填满间隙的严格计数都比计算message表的所有行要有效得多。这是建立汇总表的最关键原因。实时计算统计值是很昂贵的操作,因为要么需要扫描表中的大部分数据,要么查询语句只能在某些特定的索引上才能有效运行,而这类特定索引-般会对 UPDATE操作有影响,所以一般不希望创建这样的索引。计算最活跃的用户或者最常见的“标签”是这种操作的典型例子。
缓存表则相反,其对优化搜索和检索查询语句很有效。这些查询语句经常需要特殊的表和索引结构,跟普通OLTP操作用的表有些区别。
例如,可能会需要很多不同的索引组合来加速各种类型的查询。这些矛盾的需求有时需要创建一张只包含主表中部分列的缓存表。一个有用的技巧是对缓存表使用不同的存储引擎。例如,如果主表使用InnoDB,用MyISAM作为缓存表的引擎将会得到更小的索引占用空间,并且可以做全文搜索。有时甚至想把整个表导出MySQL,插人到专门的搜索系统中获得更高的搜索效率,例如Lucene或者Sphinx搜索引擎。
在使用缓存表和汇总表时,必须决定是实时维护数据还是定期重建。哪个更好依赖于应用程序,但是定期重建并不只是节省资源,也可以保持表不会有很多碎片,以及有完全顺序组织的索引(这会更加高效)。
- 如果应用在表中保存计数器,则在更新计数器时可能遇到并发问题。
- 计数器表在Web应用中很常见。可以用这种表缓存一个用户的朋友数、文件下载次数等。
- 创建一张独立的表存储计数器通常是个好办法,这样可使计数器表小且快。使用独立的表可以帮助避免查询缓存失效