高性能MySQL：缓存表和汇总表以下文字资料是由(历史新知网www.lishixinzhi.com)小编为大家搜集整理后发布的内容，让我们赶快一起来看一下吧！
缓存表和汇总表
有时提升性能最好的方法是在同一张表中保存衍生的冗余数据 然而 有时也需要创建一张完全独立的汇总表或缓存表(特别是为满足检索的需求时) 如果能容许少量的脏数据 这是非常好的方法 但是有时确实没有选择的余地(例如 需要避免复杂 昂贵的实时更新操作)
术语 缓存表 和 汇总表 没有标准的含义 我们用术语 缓存表 来表示存储那些可以比较简单地从schema 其他表获取(但是每次获取的速度比较慢)数据的表(例如 逻辑上冗余的数据) 而术语 汇总表 时 则保存的是使用GROUP BY语句聚合数据的表(例如 数据不是逻辑上冗余的) 也有人使用术语 累积表(Roll Up 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 BEEEN
> 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
