mysql数据库设计

    良好的逻辑设计和物理设计时高性能的基石，应该根据系统将要执行的查询语句来设计schema，这往往需要权衡各种因素。例如：发范式的设计可以加速某些类型的查询，但同时可能使另一些类型的查询变慢。比如添加计数表和汇总表是一种很好的查询优化查询的方式，但这些表的维护成本可能会很高。

一、数据类型的选择优化

    mysql支持的数据类型非常多，选择正确的数据类型对于获得高性能至关重要。下面是选取类型的几个指导原则：

• 更小的通常更好：一般情况下，应该尽量使用可以正确存储数据的最小数据类型。更小的数据类型通常更快，因为它占用更少的磁盘、内存和CPU缓存，并且处理时需要的CPU周期也更少。
• 简单就好：简单数据类型的操作通常需要更少的CPU周期。例如，整型比字符串代价更低，因为字符集和校队规则使字符比较更复杂。
• 尽量避免NULL：很多表都包含可以null的列，即使应用程序并不需要保存null也是如此，这是因为可为null是列的默认属性。通常情况下最好指定列为not null，这是因为查询中包含可为null的列，对mysql来说更难优化，null的列使得索引、查询统计和值比较都更为复杂。

    类型的选择过程是：①确定合适的大类型----数字、字符串、时间等；②选择具体类型，很多mysql的数据类型可以存储相同类型的数据，只是存储的长度和范围不一样、允许的精度不同，或者需要的物理空间不同。

    mysql基本数据类型：①整数类型----tinyint（8bit）、smallint（16bit）、mediumint(24bit)、int(32bit)、bigint(64bit)；②实数类型----float（32bit）、double（64bit）他两个是不精确的浮点类型，decimal精确的小数，例如，decimal（18,9）小数点两边各存储9个数字，一共使用9个字节，小数点前的数字用4个字节，小数点后的数字用4个字节，小数点本身用1个字节（最多65个数字）；③字符串类型----varchar和char。varchar需要1或2个额外字节记录字符串的长度，如果最大长度小于或等于255字节，则使用1个字节表示，否则使用2个字节。这两种类型占用的字节类型与字符编码有关；④大字段存储类型----blob和text类型，分别采用二进制和字符方式存储。⑤枚举类型，看似定义的是字符串，实际存储的是数字。⑥日期和时间类型----year、date、datetime（8字节）、timestamp（4字节）。

二、MySQL schema设计中的缺陷

    虽然有一些普遍的好或坏的设计原则，但也有一些问题是由MySQL的实现机制导致的，这意味着有可能犯一些只在MySQL下发生的特定错误，如下：

• 太多的列：MySQL的存储引擎API工作时需要在服务器层和存储引擎之间通过缓冲格式拷贝数据，然后在服务器层将缓冲内容解码成各个列。从行缓冲中将编码过的列转换成行数据结构的操作代价都很高的。MyISAM的定长行结构实际上与服务器层的行结构正好匹配，所以不需要转换。然而，MyISAM的变长行结构和InnoDB的行结构则总是需要转换。转换的代价依赖于列的数量。当CPU占用非常高时，发现客户使用了非常宽的表，然而只有一小部分列会实际用到，这时转换的代价就非常高。
• 太多的关联：所谓“实体-属性-值”设计模式是一个常见的糟糕设计模式，尤其是在MySQL下不能靠谱的工作。MySQL限制了每个关联操作最多只能有61张表，但是eav数据库需要许多自关联，事实上在许多关联少于61张表的情况下，解析和优化查询的代价也会成为MySQL的问题。如果希望查询执行的快速并且并发性好，单个查询最好在12个表以内做关联。
• 全能的枚举：注意防止过度使用枚举（ENUM）。例如：create table...(country enum('','1','2',...,'31'),这种模式的schema设计非常凌乱。这里该用整数作为外键关联到字典表或者查找表来查找具体值。
• 变相的枚举：枚举（ENUM）允许在列中存储一组定义值中的单个值，集合（set)列则允许在列中存储一组定义值中的一个或多个值。有时候这可能比较容易导致混乱。例如：create table...(is_default set('Y','N') not null default 'N'。如果这里真和假两种情况不会同时出现，那么毫无疑问应该使用枚举代替集合列。
• 非此发明的NULL：之前讲过避免NULL，建议尽可能考虑替代方案。但是遵守这个原则也不要走极端。当确实需要表示未知值时也不要害怕使用NULL。例如用-1代表一个未知的整数，这可能导致代码复杂很多，并容易引入bug。处理NULL确实不容易，但有时候会比它的替代方案更好。

三、数据库范式

1、第一范式1NF

   实体所有属性的值都是单一的，这样的实体称为符合第一范式。对实体应用1NF，必须要验证实体的每个属性对应于实体的实例都有单一的值。如果任何属性有重复的值，他就不符合1NF，就可能需要定义一个新的实体，新的实体由重复的属性构成，新实体与原实体之间为一对多关系。

    映射到数据库，则数据库表中的所有字段值都是不可分解的原子值， 这样的数据库表满足第一范式。对于数据库表的设计应用1NF，指数据库表的每一列都是不可分割的基本数据项，同一列中不能有多个值。

   如学生信息表，不能将学生信息都放在一列中显示，也不能将其中的两列或多列在一列中显示。简而言之，第一范式就是无重复的列。

    本来直接将“地址”属性设计成一个数据库表的字段就行。但是如果系统经常会访问“地址”属性中的“城市”部分，那么就非要将“地址”这个属性重新拆分为省份、城市、详细地址等多个部分进行存储，这样在对地址中某一部分操作的时候将非常方便。这样设计才算满足了数据库的第一范式，如下表所示：

2、第二范式2NF

    如果实体符合第一范式，并且其所有非标识属性都完全依赖于实体的唯一标识符，则称实体符合第二范式。如果任何属性不完全依赖于实体的唯一标识符，那么这个属性必定是放错了位置，必须去除。（唯一标识符必须满足：①在实体的所有实例中是唯一的；②在实体的每个实例的整个生命周期中都有非NULL值；③在实例的整个生命周期中，他的值不会改变）

    对应于数据库，就是需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）。也就是说在一个数据库表中，一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中。

    比如要设计一个订单信息表，因为订单中可能会有多种商品，所以要将订单编号和商品编号作为数据库表的联合主键，如下表所示：

    这样就产生一个问题：这个表中是以订单编号和商品编号作为联合主键。这样在该表中商品名称、单位、商品价格等信息不与该表的主键相关，而仅仅是与商品编号相关。所以在这里违反了第二范式的设计原则。而如果把这个订单信息表进行拆分，把商品信息分离到另一个表中，把订单项目表也分离到另一个表中，就非常完美了。如下所示：

3、第三范式3NF

    如果实体符合2NF并且其所有标识属性均不依赖于其他任何非标识属性，则称这个实体符合第三范式。非标识属性是不作为实体标识符的一部分的任何属性。对于依赖于其他非标识属性的属性，可以将依赖属性和他所依赖的属性都移到新实体中从而实现标准化。

   对应于数据库，数据表中的每一列数据都和主键直接相关，而不能间接相关。

   比如在设计一个订单数据表的时候，可以将客户编号作为一个外键和订单表建立相应的关系。而不可以在订单表中添加关于客户其它信息（比如姓名、所属公司等）的字段。如下面这两个表所示的设计就是一个满足第三范式的数据库表。

四、范式与反范式

1、范式的优点和缺点

    当为性能问题而寻求帮助时，经常会被建议对schema进行范式化设计，尤其是写密集的场景。这通常是一个好建议。范式化通常能带来的好处：

• 范式化的更新操作通常比反范式化更快；
• 当数据较好的范式化时，就只有很少或者没有重复数据，所以只需要修改更少的数据。
• 范式化的表通常更小，可以更好地放在内存里，所以执行操作会更快。
• 很少有多余的数据意味着检索列表数据更少需要distinct或者group by语句。

   范式化设计的schema的缺点是通常需要关联。稍微复杂一些的查询语句在符合范式的schema上都可能需要至少一次关联，也许更多。但不但代价恩贵，也可能使一些索引策略无效。

2、反范式的优点和缺点

    反范式化的schema因为所有数据都在一张表中，可以很好的避免关联。如果不需要关联表，则对大部分查询最差的情况----即使表没有使用索引----是全表扫描。当数据内存大时这可能要比关联要快的多，因为这样避免了随机I/O。并且单独的表也能使用更有效的索引策略。

3、混用范式化和反范式化

   完全的范式化和完全的反范式化，在真实的世界中很少会这么极端的使用。在实际应用中经常需要混用，可能使用部分范式化的schema、缓存表，以及其他技巧。

    最常见的反范式化数据的方法是复制或者缓存，在不同的表中存储相同的特定列。另一个从父表冗余一些数据到字表的理由是排序的需要。

五、缓存表和汇总表

    有时提升性能最好的方法是在同一张表中保存衍生的冗余数据。然而，有时也需要创建一张完全独立的汇总表或缓存表。如果能容许少量的脏数据，这是非常好的方法，但是有时确实没有选择的余地。

    通常“缓存表”表示存储那些可以比较简单的从schema其他表获取（但是每次获取的速度比较慢）数据的表（例如:逻辑上冗余的数据）。它对优化搜索和检索查询语句很有效。这些查询语句经常需要特殊的表和索引结构，跟普通OLTP操作用的表有些区别。

    通常“汇总表”时，则保存的是使用group by语句聚合数据的表（例如：数据不是逻辑上冗余的）。建立汇总表的最关键原因是，实时计算统计值是很昂贵的操作，因为要么需要扫描表中的大部分数据，要么查询语句只能在某些特定的索引上才能有效运行，而这类特定索引一般会对update操作有影响，所以一般不希望创建这样的索引。

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

六、汇总

    良好的schema设计原则是普遍适用的，但MySQL有他自己的实现细节需要注意。概括起来说，尽可能保持任何东西小而简单总是好的。MySQL喜欢简单，需要使用数据库的人应该也同样会喜欢简单的原则：

• 尽量避免过度设计，例如会导致及其复杂查询的schema设计，或者有很多列的表设计。
• 使用小而简单的合适数据类型，除非真实数据模型中确切的需要，否则应该尽可能避免使用NULL值。
• 尽量使用相同的数据类型存储相似或相关的值，尤其是在要在关联条件中使用的列。
• 注意可变长字符串，其在临时表和排序时可能导致悲观的按最大长度分配内存。
• 尽量使用整形定义标识符。
• 避免使用MySQL已经遗弃的特性，例如指定浮点数的精度，或者整数的显示宽度。
• 小心使用ENUM和set。虽然他们用起来很方便，但是不要滥用，否则有时候会变成陷阱。最好避免使用set。
• 范式是好的，但是反范式有时也是必须要的，并且能带来好处。