2023年7月27日发(作者：)

⼀篇⽂章告诉你如何设计数据库表0.三⼤范式及反范式◆ 第⼀范式（1NF）：强调的是列的原⼦性，即列不能够再分成其他⼏列。

◆ 第⼆范式（2NF）：⾸先是 1NF，另外包含两部分内容，⼀是表必须有⼀个主键；⼆是没有包含在主键中的列必须完全依赖于主键，⽽不能只依赖于主键的⼀部分。

◆ 第三范式（3NF）：⾸先是 2NF，另外⾮主键列必须直接依赖于主键，不能存在传递依赖。即不能存在：⾮主键列 A 依赖于⾮主键列B，⾮主键列 B 依赖于主键的情况。

第⼆范式（2NF）和第三范式（3NF）的概念很容易混淆，区分它们的关键点在于，2NF：⾮主键列是否完全依赖于主键，还是依赖于主键的⼀部分；3NF：⾮主键列是直接依赖于主键，还是直接依赖于⾮主键列。

范式的优点：1）范式化的数据库更新起来更加快；2）范式化之后，只有很少的重复数据，只需要修改更少的数据；3）范式化的表更⼩，可以在内存中执⾏；4）很少的冗余数据，在查询的时候需要更少的distinct或者group by语句。范式的缺点：1）范式化的表，在查询的时候经常需要很多的关联，因为单独⼀个表内不存在冗余和重复数据。这导致，稍微复杂⼀些的查询语句在查询范式的schema上都可能需要较多次的关联。这会增加让查询的代价，也可能使⼀些索引策略⽆效。因为范式化将列存放在不同的表中，⽽这些列在⼀个表中本可以属于同⼀个索引。

反范式的优点：1）可以避免关联，因为所有的数据⼏乎都可以在⼀张表上显⽰；2）可以设计有效的索引；反范式的缺点：3）表格内的冗余较多，删除数据时候会造成表有些有⽤的信息丢失。所以在设计数据库时，要注意混⽤范式化和反范式化。

下⾯是表单设计中⼀些经常要注意的地⽅：

1.适度冗余， 让query尽量减少join虽然optimizer会对query进⾏⼀定的优化，但有时候遇见复杂的join，优化效果并不令⼈满意，再加上本来join的性能开销，所以需要尽量的减少join，⽽需要通过冗余来实现。⽐如：有两个数据表分别为⽤户信息表和⽤户发帖表，在展⽰发帖列表时，如果没有冗余的话，两个表要join以取得想要的发帖信息和⽤户昵称，但如果考虑冗余，⽤户昵称占⽤空间不⼤，如果在发帖表⾥增加这么⼀个字段的话，在展⽰列表时就不⽤做join操作了，性能会得到很⼤的改善。但冗余也会带来⼀些问题，⽐如在发帖表⾥增加了⽤户昵称字段，就得维护两份⽤户昵称数据，为了保证数据的⼀致性，在⽤户昵称发⽣改变时，就得向两个表做更新操作，程序中就得做更多的处理。但相⽐的话，更新频率显然不及查询频率，这样通过增加少量的更新操作会换来更⼤的性能提升，这也是在项⽬中经常采⽤的优化⼿段。

2. ⼤字段垂直分拆所谓的⼤字段，没有⼀个很严格的标准，常⽤的是如果⼀个字段的⼤⼩占整条记录的50%以上，我们就视为其为⼤字段。⼤字段垂直分拆相⽐适度冗余是完全相反的操作，适度冗余是将别的表中的字段放进⼀个表中，⽽⼤字段分拆是将⾃⾝的⼤字段拆分出去放进另⼀个表中。这两个优化策略貌似是⽭盾的，但要根据具体的应⽤场景来分析，适度冗余是因为在频率较⾼的查询中要使⽤该字段，为了减少join的性能开销。⽽⼤字段垂直分拆是将在查询中不使⽤的⼤字段拿出去，虽然不使⽤该字段但mysql在查询时并不是只需要访问需要查询的那⼏个字段，⽽是读取所有的字段，所以即使不使⽤字段，mysql也会读取该字段，为了节省IO开销，所以将查询中不常使⽤的⼤字段分拆出去。⽐如：拿博客系统为例，常⽤的作法是将博客内容从博客列表⾥分拆出去建⽴⼀个博客内容表，因为访问博客列表时并不需要读取博客内容，分拆出去之后，访问博客列表的性能将会⼤⼤的提升。但同时访问博客内容时就得做⼀次join操作了，性能对⽐的话，join操作两个表是⼀对⼀的关系，性能开销会很低。

3. ⼤表⽔平分拆举例说明：在⼀个论坛系统⾥，管理员经常会发⼀些帖⼦，这些帖⼦要求在每个分类列表⾥都要置顶。设计⽅案⼀：在发帖表⾥增加⼀列⽤来标⽰是否是管理员发帖，这样在每个分类列表展⽰时就需要对发帖表查询两次，⼀次是置顶帖，⼀次是普通帖，然后将两次结果合并。如果发帖表内容较⼤时，查询置顶帖的性能开销会⽐较⼤。设计⽅案⼆：将置顶帖存放在⼀个单独的置顶表⾥。因为置顶帖数量相⽐会很少，但访问频率很⾼，这样从发帖表⾥分拆开来，访问的性能开销会少很多。

4.选择合适的数据类型要选择合适的数据类型必须要先了解不同数据类型间的差异。数字类型有整数类型和浮点数类型，还有⼀类是通过⼆进制格式以字符串来存放的数字类型，如DECIMAL(size,d)，其存放长度主要通过定义的size决定，size定义多⼤，则实际存放就有多长。默认的size为10，d为0。这种类型的存放长度较长⽽且完全可以⽤整形来代替实现，所以不推荐使⽤。

时间类型主要使⽤DATE，DATETIME和TIMESTAMP三种类型，TIMESTAMP占⽤存储空间最少，只要4个字节，其它两种类型都要占⽤8个字节。从存储内容来看，TIMESTAMP只能存储1970年之后的时间，另外两种都能存储从1001开始的时间。特别要说明的是varchar类型，varchar(size)，在mysql5.0.3之前size表⽰的是字节数，mysql5.0.3之后size表⽰的是字符数。这⾥我们只关注mysql5.0.3之后的表⽰，size表⽰的字符数最⼤限制和字符集有关，如果是gbk编码，最⼤长度为(65535-1-2)/2=32766，减1的原因是实际⾏存储从第⼆个字节开始，减2的原因是varchar头部的2个字节表⽰长度，除2因为是gbk编码；如果是utf8编码，最⼤长度为(65535-1-2)/3=21844。

如果数据量⼀样，但数据类型更⼩的话，数据存放同样的数据就会占⽤更少的空间，这样检索同样的数据所带来的IO消耗⾃然会降低，性能也就很⾃然的得到提升。此外，mysql对不同类型的数据，处理⽅式也不⼀样，⽐如在运算或者排序操作中，越简单的数据类型操作性能越⾼，所以对于要频繁进⾏运算或者排序的字段尽量选择简单的数据类型。