Schema的优化和索引 - 范式和非范式

有很多方法来展现给定的数据。从完全范式到完全的非范式以及介于两者之间。在符合范式的数据库中，每个事实展现一次并且仅仅展现一次而已。相反的，在非范式的数据库，信息重复或者存储在很多的地方。

 

如果你不熟悉范式，你应该加强学习了。关于范式，你可以通过一些书和网上资源来学习。在这里，我们主要介绍这一章中你应该明白的知识。让我们看看经典的例子，那就是employee,departments,和department heads.

 

 

EMPLOYEE             DEPARTMENT                  HEAD

Jones                      Accounting                       Jones

Smith                      Engineering                     Smith

Brown                     Accounting                       Jones

Green                     Engineering                      Smith

 

这种设计问题在于当数据更改的时候，这数据模型就会变得不正常了。如果Brown接管了Accounting部门，我们必须更新多条语句来反映出这个改变，并且这些更新可能还使数据的状态不一致。如果Jones行的HEAD和Brown行的HEAD不同的话，没有方法知道到底那个HEAD是正确的。就像那句老话一样：一个人有两块表，就不会知道准确的时间。更进一步的说，没有员工的时候就不能展现DEPARTMENT.如果我们删除了全部员工，DEPARTMENT的信息也同样的被删除了。为了避免这个问题，我们把这个表分为EMPLOYEE和DEPARTMENT两个实体。结果为两张表：

 

 

EMPLOYEE_NAME        DEPARTMENT

Jones                           Accounting

Smith                           Engineering

Brown                          Accounting

Green                           Engineering

 

 

 

DEPARTMENT                 HEAD

Accounting                     Jones

Engineering                   Smith

 

 

这些表属于第二范式，对于大部分需求已经足够了。第二范式只是众多范式的其中一个。

 

一个范式数据模型的缺点和优点

那些对性能要求较高的需求，推荐用范式化的数据模型。尤其对于写操作较多的需求。使用范式化模型的好处如下：

 

• 范式化的更新速度要快于非范式化的更新。
• 当数据很好的范式化之后，就有很少或者没有重复的数据。因此也就会有很少的数据需要更新。
• 范式化的表常常很小，因此它们可以放到内存中，使性能变得更好。
• 没有多于的数据就意味着，当获取值的列表的时候，就不需要太多的DITINCT或者GROUP BY 的查询。考虑下以前的例子：如果不使用DISTINCT或者GROUP BY在非范式化的表查询唯一列表的部门是不可能的，但是如果DEPARTMENT是独立表，仅仅是个获取查询而已。

范式化数据模型的缺点就是获取数据。在很好的范式化模型上做一些复杂的查询，至少都需要连接一张表，也可能更多。这样的消耗不仅很大，并且使有些索引策略变为不可能。比如，范式化把列放到不同的表中，这些表都会从相同的索引中得到好处。

非范式化数据模型的缺点和优点

一个非范式化的模型可能工作的很好，以为任何数据都放在相同的表中，这样避免了连接。

 

如果你不需要连接表，来看个最坏情况下的查询-即使没有使用索引，那就是个全表的扫描。在数据没有在内存中的时候，这样的查询都要快于连接表的查询。因为它避免了随机的IO。

 

一个单独的表也允许使用更高效的索引策略。假使你有个用户发送消息的网站，并且一些用户是付费用户。现在你想查看来自付费用户最新的10条信息。如果你已经范式化数据模型了并且索引了message的发布时间，这个查询可能如下：

 

 

mysql> SELECT message_text, user_name

    -> FROM message

    -> INNER JOIN user ON message.user_id=user.id

    -> WHERE user.account_type='premium'

    -> ORDER BY message.published DESC LIMIT 10;

 

 

这个查询的执行是有效率的，Mysql需要会扫描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;

 

 

 

范式化和非范式化的混合使用

我们知道了范式化和非范式化的优点和缺点，那么怎样才能做到最佳的设计呢？

 

事实就是，完全范式和完全非范式的都像实验室的小白鼠：现实中，它们能做的非常少。在现实中，你需要混合这两种方式，可能使用一部分范式化模型，缓存表，和其他技术。

 

最常用的非范式化数据的方法是复制或者缓存，选择列从一张表到另一张表。在MySQL5.0以上版本，你可以使用触发器来更新缓存数据。这样实现起来也很简单。

 

在我们网站这个例子中，可以把account_type存储在user和message表中，这个方法可以替代上次说的那个完全非范式化的方法。这样做可以避免完全非范式化所引起的INSERT和DELETE的问题，因为即使没有MESSAGE也不会丢失USER的信息。它也不会使user_message表变得更大，还会使查询数据更为高效。

 

然而，这样做会使更新account_type消耗更大。因为你需要更新两张表。要知道这是否是个问题，你必须要考虑的是这些更改的频率和多久会变化，在比较SELECT查询的频率。

 

把一些数据从父表移动到子表还有一个好处就是为了排序。举个例子，如果按照author名字来排序message的话，你可以把author_name在message表中缓存和索引，这样的查询语句是非常高效的。

 

这对于一些衍生数据的缓存也非常有用。如果你需要显示每个用户有多少条信息，你可以使用子查询来计算这些数据，也可以在user表中添加一个num_message字段，当user发布一个message的时候再更新它。

 

 

缓存和汇总表(Cache and Summary tables)

有的时候提升性能最好的方法就是在相同的表中存放冗余的数据。然而，有的时候，你可能需要创建单独的汇总或缓存表来优化查询。如果你能容忍少量过时的数据，这种方法是最佳的了，但是有的时候你真的没有选择。

 

关于缓存表和汇总表都是不标准的说法。如果我们提到缓存表就意味着表中包含的数据可以很容易的获取（数据是逻辑上的冗余）。当我们说道汇总表的时候，意思就是通过GROUP BY来聚合数据（数据不是逻辑上的冗余）。一些人也叫这些表为“roll-up tables”。因为数据是不断累积的。

 

继续回到上一个web站点的例子，假使你要计算24小时之前的message之和。在一个访问量很大的网站，实时精确的值是不太可能的。我们可以每小时生成一个汇总表。这样完成这个需求只需要一个查询语句，并且这也比维护一个累加字段更高效。缺点就是这个数值并不是100%的精确。

 

如果你需要一个精确的数值，还有另一个选择。你可以把那个时间段中的23小时的的，开始时间段的部分小时的，还有时间段结束的部分小时的message数相加来得到准确的值。假使你的汇总表叫做msg_per_hr并且定义如下

 

CREATE TABLE msg_per_hr (
   hr DATETIME NOT NULL,
   cnt INT UNSIGNED NOT NULL,
   PRIMARY KEY(hr)
);

 

你可以把下列三个查询求和来计算过去24小时message的数。

 

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表的行数要高效很多。关键原因就在于创建了汇总表。这种统计如果实时统计消耗非常大，因为需要扫描的数据太多了，再就是查询所需要的索引，你可能也不会添加，因为这样太影响INSERT和UPDATE了。计算最活跃的用户和频率最高的TAG都是这样的操作。

 

来说说缓存表，它对于优化搜索和查询的语句非常有用。这些查询一般都需要特定的表和索引结构，这索引结构和应用于一般的OLTP操作的索引结构是不同的。

 

举个例子，你可能需要不同的索引组合来提升不同种类查询的性能。这些相互冲突的需求有的时候需要你创建一个包含主表一些列的缓存表。一个有用的技术就是对缓存表使用不同的存储引擎。如果主表使用的是INNODB，而对缓存表使用的是MyISAM，那么就可以使索引更小，并且可以做全文搜索的查询。有的时候你可能想让表完全脱离MySQL，可以使用特殊的系统来提升查询性能，比如Lucene或者Sphinx搜索引擎。

 

当使用缓存和汇总表，必须要决定是否要实时维护数据或者周期性重建数据。哪种更好完全取决与你的应用程序，但是周期性重建数据不仅仅可以节省资源，还可以使结果存放在更有效率的表中，这些表不会碎片并且含有完全排序的索引。

 

当你重建汇总和缓存表的时候，在操作的时候你常常需要它们的数据保持可见。你可以使用“shadow table”（影像表）来实现。当你已经创建它之后，你可以使用原子性的重命名来交换这些表。举个例子，如果你需要重建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，你就可以保存这个表老的版本直到下次这个表的重建。如果新表出现问题，你也可以用旧的来替换。

计数表（Counter tables）

一个应用程序在一个表中存放计数字段，当更新计数字段的时候，会遇到并发问题。这些表在web应用程序中很常见。你能使用它们来缓存用户的好友数，文件的下载数等等。一般来说，创建一个单独的表来存放这些计数是个好的主意，这样做能使它更小更快。使用单独的表，可以避免无效的查询缓存并且让你使用更多的高级技术。

 

为了让例子更简单，假使你有个包含一列的计数表。来记录你网站的点击量。

 

mysql> CREATE TABLE hit_counter (
    ->    cnt int unsigned not null
    -> ) ENGINE=InnoDB;

 

每次点击都更新计数列。

 

mysql> UPDATE hit_counter SET cnt = cnt + 1;

 

这样做问题在于这个单独的行是对于任意更新计数的事物是全局互斥的。它会序列化这些事物。为了获得更高的并发，可以保存更多的行并且随机更新这些行。表的修改如下

 

mysql> CREATE TABLE hit_counter (
    ->    slot tinyint unsigned not null primary key,
    ->    cnt int unsigned not null
    -> ) ENGINE=InnoDB;

 

预先给这个表添加100行，现在这个查询就随机更新计数了。

mysql> UPDATE hit_counter SET cnt = cnt + 1 WHERE slot = RAND( ) * 100;

 

为了获取统计信息，可以使用聚合查询

 

mysql> SELECT SUM(cnt) FROM hit_counter;

 

还有一个一般需求就是需要每个周期的计数。（比如，一天一次）。如果你需要这么做，你可以修改下数据模型。

 

mysql> CREATE TABLE daily_hit_counter (
    ->    day date not null,
    ->    slot tinyint unsigned not null,
    ->    cnt int unsigned not null,
    ->    primary key(day, slot)
    -> ) ENGINE=InnoDB;

 

你不想提前生成一些行，你可以使用ON DUPLICATE KEY UPDATE:

 

mysql> INSERT INTO daily_hit_counter(day, slot, cnt)
    ->    VALUES(CURRENT_DATE, RAND( ) * 100, 1)
    ->    ON DUPLICATE KEY UPDATE cnt = cnt + 1;

 

 

如果你想减少一些行来使表变得更小，你可以写一个周期性的任务来把合并所有的结果到slot()并且上除其他的slot.

 

mysql> UPDATE daily_hit_counter as c
    ->    INNER JOIN (
    ->       SELECT day, SUM(cnt) AS cnt, MIN(slot) AS mslot
    ->       FROM daily_hit_counter
    ->       GROUP BY day
    ->    ) AS x USING(day)
    -> SET c.cnt  = IF(c.slot = x.mslot, x.cnt, 0),
    ->     c.slot = IF(c.slot = x.mslot, 0, c.slot);
mysql> DELETE FROM daily_hit_counter WHERE slot <> 0 AND cnt = 0;