《高性能MySQL》MySQL三大范式和反范式

作者: JONE 分类: PHPer 发布时间: 2018-09-13 10:21

1. 第一范式

确保数据表中梅列(字段)的原子性。

如果数据表中每个字段都是不可再分的最小数据单元,则满足第一范式。

例如:user用户表,包含字段 id,username,password

2. 第二范式

在第一范式的基础上更进一步,目标是确保表中的每列都和主键相关。

如果一个关系满足第一范式,并且除了组件之外的其他列,都依赖于该主键,则满足第二范式。

例如:一个用户只有一种角色,而一个角色对应多个用户,则可以按如下方式建立数据表关系,使其满足第二范式。

user用户表,字段 id,username,password,role_id
role角色表,字段 id, name
用户表通过角色id 来关联角色表。

3. 第三范式

在第二范式的基础上更进一步,目标是确保表中的列都和主键直接相关,而不是简介相关。

例如:一个用户可以对应多个角色,一个角色可以对应多个用户。则可以按如下方式建立数据表关系,使其满足第三范式。

user用户表,字段 id,username,password
role角色表,字段 id,name
user_role用户-角色中间表, id,user_id,role_id

4. 反范式

反范式化值得是通过增加荣誉或重复的数据来提高数据库的读性能。

例如:在上例中的user_role用户-角色中间表增加字段role_name。
反范式话可以减少关联查询时,join表的次数。

5. 范式的优点和缺点

当性能问题而寻求帮助是,经常会被建议对数据表进行范式化设计,尤其是写密集的场景。这通常是个好建议。因为下面这些原因,范式化通常能够带来好处。

1)范式化的更新操作通常比反范式化要快。
2)当数据较好的范式化时,就只有很少或者没有重复数据,佛衣只需要修改更少的数据。
3)范式化的表通常更小,可以更好的放在内存里,所以执行操作会更快。
4)很少有多余的数据意味着检索列表数据时更少需要DISTINCT或者GROUP BY语句。

范式化设计的数据表的缺点是通常需要关联。稍微复杂一些的查询语句在符合范式的数据表上都可能需要至少一次关联,也许更多。这不但代价行规,也可能是一些索引策略无效。例如,范式化肯讲列存放在不同的表中,而这些列如果在一个表中可以属于同一个索引。

6. 反范式的优点和缺点

反范式化的数据表因为所有数据都在一张表中,可以很好的避免关联操作

如果不需要关联表,则对于大部分查询最差的情况–即使表没有使用索引–是全表扫描。当数据比内存大时这可能比关联要快得多。因为这样避免了随机I/O。(全表扫描基本上是顺序I/O,单页不是100%的,跟引擎的实现有关。)

单独的表也能使用更有效的索引策略。假设有一个网站,允许用户发送消息,并且一些用户是付费用户,现在想查看付费用户最近的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.published 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;

7. 混用范式化和反范式化

最常见的反范式化数据的方式是在复制或者缓存,在不同的表中存储相同的特定列。在MySQL5.0和更新版本中,可以使用触发器更新缓存值,这使得实现这样的方案变得更简单。