数据库设计的原则（MySQL）

数据库表的设计师日常开发中必不可少的，一个设计合理的数据库表可以让我们的查询效率更高，加快网站的访问速度，提升用户体验，并且方便我们查询数据，同一个合理的数据库表也会使我们的代码更加的优雅。

为了建立冗余较小、结构合理的数据库，设计数据库时必须遵循一定的规则。在关系型数据库中这种规则就称为范式。范式是符合某一种设计要求的总结。要想设计一个结构合理的关系型数据库，必须满足一定的范式。

第一范式(确保每列保持原子性)

id	student _id	student_name	gender	province	city	address
1	202003001	托儿索	男	陕西	西安	未央路122号
2	202003002	璐璐	女	广州	广东	白云区111号

第二大范式(确保表中的每列都和主键相关)

第二大范式是建立在第一大范式的基础上的，第二大范式要求表中只具有一个业务主键，即符合第二大范式的表不能存在非主键列只对部分主键的依赖关系。

例如：订单表、产品表

订单表ID(主键)	订单创建时间	产品ID
1	2020-05-06	2
2	2020-05-07	3

产品表ID	产品名称
2	挖掘机
3	飞机

一个订单有多个产品，所以的名单主键[订单ID]和[产品ID]组成的联合主键，这样两个组件不符合第二范式，而且产品ID和订单ID没有强关联，故把订单表进行拆分为订单表与订单与商品的中间表。

订单ID	订单创建时间
2	2020-05-07

订单-商品中间表ID	订单ID	产品ID
1	2	3

产品ID	产品名称
2	挖掘机
3	飞机

第三大范式(确保每列都和主键列直接相关,而不是间接相关)

第三大范式：数据不能存在传递关系，即每个属性都跟主键有直接关系而不是间接关系

例如

订单ID	订单创建时间	客户账号	客户名称
2	2020-05-06	0012	亚索
3	2020-05-07	0102	瑞雯

在这张表中

客户账号 和 订单编号 关联

客户姓名 和 订单编号 关联

客户账号 和 客户名称 关联

一旦客户账号发生变化，用户姓名也会变，不符合第三大范式应该把客户姓名这一列删除

反范式设计

反范式设计是相对于范式设计而言的，反范式设计就是为了性能和读取效率的考虑而适当的对数据库设计范式的要求进行违反，允许少量存在少量的冗余，即：使用空间来换取时间

范式化设计的优缺点：

优点：

可以尽量得减少数据冗余

范式化的更新操作比反范式化更快

范式化的表通常比反范式化的表更小

缺点：

对于查询需要对多个表进行关联

更难进行索引优化

反范式化设计的优缺点：

优点：

可以减少表的关联

可以更好的进行索引优化

缺点：

存在数据冗余及数据维护异常

对数据的修改需要更多的成本

所以不能完全按照范式化设计的要求进行设计，在设计表时还需考虑如何使用表。

物理设计

以下部分内容参考【阿里Java开发手册(泰山版)】

1.表名、字段名必须使用小写字母或数字， 禁止出现数字开头，禁止两个下划线中间只出现数字。数据库字段名的修改代价很大，因为无法进行预发布，所以字段名称需要慎重考虑。
说明： MySQL 在 Windows 下不区分大小写，但在 Linux 下默认是区分大小写。因此，数据库名、表名、字段名，都不允许出现任何大写字母，避免节外生枝

2.禁用保留字，mysql保留字参考官方：MySQL官方保留字与关键字
当然如果不小心使用了关键字解决方法是加 反引号 输入法切换到英文状态下：键盘上Esc键下面，数字键1左边，Tab键上面那个键。当然最好还是避免关键字。

3.小数使用数据类型为 decimal，禁止使用float和double，在存储的时候， float 和 double 都存在精度损失的问题，很可能在比较值的时候，得到不正确的结果。如果存储的数据范围超过 decimal 的范围，建议将数据拆成整数和小数并分开存储。(涉及到money最好还是用decimal，一分qian的问题也是个大问题)

4.varchar 是可变长字符串，不预先分配存储空间，长度不要超过 5000，如果存储长度大于此值，定义字段类型为 text，独立出来一张表，用主键来对应，避免影响其它字段索引效率

5.表必备三字段： id, gmt_create, gmt_modified

其中 id 必为主键，类型为 bigint unsigned、单表时自增、步长为 1。 gmt_create, gmt_modified的类型均为 datetime 类型，前者现在时表示主动式创建，后者过去分词表示被动式更新 。

6.表的命名最好是遵循“业务名称_表的作用” 。

7.字段允许适当冗余，以提高查询性能，但必须考虑数据一致。冗余字段应遵循：
1） 不是频繁修改的字段。
2） 不是唯一索引的字段。
3） 不是 varchar 超长字段，更不能是 text 字段。
正例： 各业务线经常冗余存储商品名称，避免查询时需要调用 IC 服务获取

8.单表行数超过 500 万行或者单表容量超过 2GB，才推荐进行分库分表。
说明： 如果预计三年后的数据量根本达不到这个级别，请不要在创建表时就分库分表

9.合适的字符存储长度，不但节约数据库表空间、节约索引存储，更重要的是提升检索
速度。
正例： 无符号值可以避免误存负数， 且扩大了表示范围

对象	年龄区间	类型	字节	表示范围
人	150岁以内	tinyint unsigned	1	无符号值：0到255
龟	数百岁	smallint unsigned	2	无符号值：0到65535
恐龙化石	数千万年	int unsigned	4	无符号值：0到43亿
太阳	约50亿年	bigint unsigned	8	无符号值：0到约10的19次方

存储引擎选择

对比项	MyISAM	InnoDB
主外键	不支持	支持
事务	不支持	支持
行表锁	表锁，即使操作一条记录也会锁住整个表不适合高并发的操作	行锁，操作时只锁某一行，不对其它行有影响，适合高并发的操作
缓存	只缓存索引，不缓存真实数据	不仅缓存索引还要缓存真实数据，对内存要求较高，而且内存大小对性能有决定性的影响
表空间	小	大
关注点	性能	事务
默认安装	是	是

数据类型选择

当一个列可以选择多种数据类型时

优先考虑数字类型

其次是日期、时间类型

最后是字符类型

对于相同级别的数据类型，应该优先选择占用空间小的数据类型