MySQL之Schema与数据类型优化(六)

Schema与数据类型优化

反范式的优点和缺点

反范式化的schema因为所有数据都在一张表种，可以很好地避免关联。
如果不需要关联表，则对大部分查询最差地情况——即使表没有使用索引——是全表扫描。当数据比内存大时这可能比关联要快得多，因为这样避免了随机IO.单独得表也能使用更有效得索引策略。假设有一个网站，允许用户发送消息，并且一些用户是付费用户。现在想查看付费用户最近的10条信息。如果是范式化的结构并且索引了发送日期字段published,这个查询也许看起来像这样:

mysql>SELECT message_text,user_name FROM message INNER JOIN user ON message.user_id =user.id WHERE user.account_type='premiumv' ORDER BY message.publised DESC LIMIT 10;

要更有效地执行这个查询，MySQL需要扫描message表的published字段的索引。对于每一行找到的数据，将需要到user表里检查这个用户是不是付费用户。如果只有一小部分用户是付费账户，那么这是效率低下的做法。另一种可能的执行计划是从user表开始，选择所有的付费用户，获得它们所有的信息，并且排序。但这可能更加糟糕。主要问题是关联，使得需要在一个索引中又排序又过滤，如果采用反范式化组织数据，将两张表的字段合并一下，并且增加一个索引(account_type,published)，就可以不通过关联写出这个查询。这将非常高效:

mysql>SELECT message_text,user_name FROM user_messages WHERE account_type='premium' ORDER BY published DESC LIMIT 10;

混用范式化和反范式化

范式化和反范式化的schema各有优劣，怎么选择最佳的设计？
事实是，完全的范式化和完全的反范式化schema都是实验室里才有的东西:在真实世界中很少会这么极端地使用。在实际应用中经常需要混用，可能使用部分范式化地schema、缓存表以及其他技巧。最常见地反范式化数据地方法是复制或者缓存，在不同地表中存储相同地特定列。在MySQL5.0和更新版本中，可以使用触发器更新缓存值，这使得实现这样地方案变得更简单。
在网站实例中，可以在user表和message表中都存储account_type字段，而不用完全地范式化。这避免了完全反范式化地插入和删除问题，因为即使没有消息地时候也绝不会丢失用户地信息。这样也不会把user_message表搞得太大，有利于高效地获取数据。但是现在更新用户的账户类型的操作代价就高了，因为需要同时更新两张表。至于这会不会是一个问题，需要考虑更新的频率以及更新的时长，并和执行SELECT查询的频率进行比较。
另一个从附表冗余一些数据到子表的理由是排序的需要，例如，在范式化的schema里通过作者的名字对消息排序的代价将会非常高，但是如果在message表中缓存author_name字段并且建好索引，则可以非常高效地完成排序。
缓存衍生值也是有用的。如果需要显示每个用户发了多少消息(像很多论坛做的)，可以没执行一个昂贵的子查询来计算并显示它；也可以在user表中建一个num_messages列，每当用户发新消息时更新这个值。

缓存表和汇总表

有时提升性能最好的方法是在同一张表中保存衍生的冗余数据。然而，有时也需要创建一张完全独立的汇总表或缓存表(特别是为满足检索的需求时)。如果能容许少量的脏数据，这是非常好的方法，但是有时确实没有选择的余地(例如，需要避免复杂、昂贵的实时更新操作)。
术语"缓存表"和"汇总表"没有标准的含义。我们用术语"缓存表"来标识存储那些可以比较简单地从schema其他表获取(但是每次获取的速度比较慢)数据的表(例如，逻辑上冗余的数据)。而术语"汇总表"是，则保存的是使用GROUP BY 语句聚合数据的表(例如，数据不是逻辑上冗余的)。也有人使用术语"累积表(Roll-Up Table)"称呼这些表，因为这些数据被"累计"了。
仍然以网站为例，假设需要计算之前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 < CONCAT(LEFT(NOW(), 14), '00:00') - INTERVAL 23 HOUR;
mysql> SELECT  COUNT(*) FROM message WHERE posted >= CONCAT(LEFT(NOW(), 14), '00:00');

不管是哪种方法——不严格的计数或通过小范围查询填满间隙的严格计数——都比计算message表的所有行要有效得多。这是建立汇总表的最关键原因，实时计算统计值是很昂贵的操作，因为要么需要扫描表中的大部分数据，要么查询语句只能在没某些特定的索引上才能有效运行，而这类特定索引一般会对UPDATE操作有影响，所以一般不希望创建这样的索引。计算最活跃的用户或者最常见的"标签"是折中操作的典型例子。

缓存表则相反，其对优化搜索和检索查询语句很有效。这些查询语句经常需要特殊的表和素银结构，跟普通的OLTP操作用的表有些区别。
例如，可能会需要很多不同的索引组合来加速各种类型的查询。这些矛盾的需求有时需要创建一张只包含主表中部分列的缓存表。一个有用的技巧是对缓存表使用不同的存储引擎。例如，如果主表使用InnoDB，用MyISAM作为缓存表的引擎将会得到更小的索引占用空间，并且可以做全文搜索。有时甚至想把整个表导出MySQL，插入到专门的搜索系统中获得更高的搜索效率，例如Lcene或者Sphinx搜索引擎。

在使用缓存表和汇总表时，必须决定是实时维护数据还是定期重建。哪个更好依赖于应用程序，但是定期重建并不只是节省资源，也可以保持表不会有很多碎片，以及有完全顺序组织的索引(这回更加高效)
当重建汇总表和缓存表时，通常需要保证数据在操作时依然可用。这就需要通过使用"影子表"来实现，"影子表"指的时一张在真实表"背后"创建的表。当完成了建表操作后，可以通过一个原子的重命名操作切换影子表和原表。例如，如果需要重建my_summary，则可以先创建my_summary_new，然后填充好数据，最后和真实表做切换

mysql> CREATE TABLE my_summary_new LIKE my_summary;
mysql> RENAME TABLE my_summary TO my_summary_old, my_summary_new TO my_summary;

如果像上面的例子一样，在将my_summary这个名字分配给新键的表之前将原始的my_summary表重命名为my_summary_old，就可以在下一次重建之前一直保留旧版本的数据，如果新表有问题，则可以很容易地进行快速回滚操作。