将优化考虑在最前面-MySQL数据库设计优化：范式与反范式，主键，字符集，存储引擎

前言

优化这东西，就我个人而言，应该是在设计没有什么毛病的基础上进行的。对一个项目，如果数据库设计这一块有很大问题，这导致后面扩展需求的时候很难复用之前的数据库，而业务耦合性又很高，优化起来是一件相当痛苦的事情！ 如果在设计的时候就把该考虑的设计好，你可能会给后面的优化工作做了相当好的铺垫。 关于数据库的设计，我来从范式、反范式、主键、字符集、存储引擎等方面总结一下。

合理使用范式与反范式

什么是范式？反范式？

三范式

1. 第一范式 1NF

每一列都是不可分割的原子数据项，确保数据表中每列（字段）的原子性。

比如，如下t_user表：

像这个就不属于第一范式，因为部门字段可以分割成部门名称和部门领导两个字段，分割后：

这样就符合第一范式了。

1. 第二范式 2NF

在第一范式的基础上，不存在仅依赖于关键一部分的属性（不能存在部分依赖于主键）。 如果一个关系满足第一范式，并且除了主键之外的其他列，都依赖于该主键，则满足第二范式。

举个栗子，一个用户只有一种角色，而一个角色对应多个用户。

这种情况可以按如下方式建立数据表关系，使其满足第二范式。

user表

role表

1. 第三范式 3NF

属性不传递依赖于其他非主属性，非主键必须直接依赖于主键而不能传递依赖。

再来看一下这个表：

其实，这里有一个字段部门领导是依赖于部门的，所以它不符合第三范式。

可以再拆（加）一个表：

这样就符合第三范式了。

反范式

顾名思义，不遵照范式规则，就是反范式。 没有冗余的数据库未必是最好的数据库，有时为了提高运行效率，就必须降低范式标准，适当保留冗余数据。所以就有了反范式。

范式的优缺点

优点

• 范式化的更新操作通常比反范式要快
• 当数据较好的范式化后，很少或者没有重复的数据
• 范式化的数据比较小，可以放在内存中，操作比较快

缺点

• 通常需要进行关联join

反范式的优缺点

优点

• 所有的数据都在同一张表中，可以避免关联
• 可以设计有效的索引

缺点

• 表格内的冗余较多，删除数据时候会造成表有些有用的信息丢失

需要注意的东西

在实际工作中，很少能做到严格意义上的范式和反范式，一般需要混合使用。

1. 为了高效的获取数据

例如，在一个网站实例中，这个网站，允许用户发送消息，并且一些用户是付费用户。现在想查看付费用户最近的10条信息。 在user表和message表中都存储用户类型(account_type)而不用完全的反范式化。这避免了完全反范式化的插入和删除问题，因为即使没有消息的时候也绝不会丢失用户的信息。这样也不会把user_message表搞得太大，有利于高效地获取数据。

1. 为了排序的需要，从父表冗余一些数据到子表
2. 缓存衍生值

如果需要显示每个用户发了多少消息（类似论坛的），可以每次执行一个昂贵的自查询来计算并显示它；也可以在user表中建一个num_messages列，每当用户发新消息时更新这个值。

主键的选择

1. 代理主键

与业务无关的，无意义的数字序列。 对MySQL数据库，一般情况下我们都给每个表一个id字段，把它设为主键，自增，这个就是代理主键，也是常用的。

1. 自然主键

事物属性中的自然唯一标识。 比如，存储的人员的身份证信息，能唯一标识一条记录，那么可以把它作为主键。 但是，身份证号这个字段一般会和业务有牵扯，耦合性高。

1. 推荐使用代理主键

代理主键不与业务耦合，易于维护。 一个大多数表，最好是全部表，通用的键策略能够减少需要编写的源码数量，减少系统的总体拥有成本。比如专门写一个生成主键的东西用来生成主键，比如uuid。。。

字符集的选择

字符集直接决定了数据在MySQL中的存储编码方式，由于同样的内容使用不同字符集表示所占用的空间大小会有较大的差异，所以通过使用合适的字符集，可以帮助我们尽可能减少数据量，进而减少IO操作次数。

1. 纯拉丁字符能表示的内容，没必要选择 latin1 之外的其他字符编码，因为这会节省大量的存储空间。
2. 如果我们可以确定不需要存放多种语言，就没必要非得使用UTF8或者其他UNICODE字符类型，这会造成大量的存储空间浪费。
3. MySQL的数据类型可以精确到字段，所以当我们需要大型数据库中存放多字节数据的时候，可以通过对不同表不同字段使用不同的数据类型来较大程度减小数据存储量，进而降低 IO 操作次数并提高缓存命中率。

存储引擎

存储引擎代表数据文件的组织形式，所以存储引擎的选择对于数据的存储查询等都有影响，后面我会在索引优化、查询优化的总结中不断提到存储引擎，这里只针对MySQL的存储引擎做一个简单的对比。

看了这个表，我选InnoDB。。。

适当的数据冗余

被频繁引用且只能通过 Join 2张(或者更多)大表的方式才能得到的独立小字段。 这样的场景由于每次Join仅仅只是为了取得某个小字段的值，Join到的记录又大，会造成大量不必要的 IO，完全可以通过空间换取时间的方式来优化。 不过，冗余的同时需要确保数据的一致性不会遭到破坏，确保更新的同时冗余字段也被更新。

适当拆分

当我们的表中存在类似于 TEXT 或者是很大的 VARCHAR类型的大字段的时候，如果我们大部分访问这张表的时候都不需要这个字段，我们就该义无反顾的将其拆分到另外的独立表中，以减少常用数据所占用的存储空间。这样做的一个明显好处就是每个数据块中可以存储的数据条数可以大大增加，既减少物理 IO 次数，也能大大提高内存中的缓存命中率。 还有适当的分库分表，我们可以垂直拆分---把不同业务用到的库表放在不同的服务器；也可以水平拆分---比如按照月份把数据拆分，或者按照地市数据拆分到不同的服务器。