文章介绍

本文为[忙鱼]项目衍生文章, 学习如何建表

@故里-手把手教 | 如何设计高性能数据库表

范式与反范式

1. 如果要依照范式建表, 至少需要满足到2NF, NF是normal form-范式的 缩写, 1NF, 2NF, 3NF, BCNF(巴斯科德范式), 4NF, 5NF共六大范式, 越往后

   	内容
   优点	减少数据冗余, 减少维护数据完整性的麻烦 减少数据库占用的空间 数据变更速度快
   缺点	范式满足的越多, 设计出来的表数量越多 过多的表关联会使性能变差, (一般建议最多三表)

2. 反范式即不遵守范式规则, 灵活建表, 表内有冗余字段, 但是用空间换时间, 可以设计有效的索引, 避免了多表查询对查询性能的拖累.

   模型	优点	缺点
   范式化模型	数据没有冗余, 更新容易	需要多表查询, 性能差
   反范式化模型	简化查询, 带来的空间消耗可以接受

把握MySQL的使用原则

1. MySQL用于存储数据, 不要进行复杂的数据运算, 不承载业务逻辑, 确保存储和计算分离

2. 进行数据查询时, 尽量单表查询, 减少跨库查询和多表关联

3. 杜绝大事务(运行步骤多, 涉及字段和表多, 回滚消耗大), 大SQL(关联超过3张表以上的SQL), 大批量(多条SQL一次性执行完成, 必须在业务低峰或非业务时段执行), 大字段(少用blob, text等大字段, 必须要用的时候, 尽量与主业务表分离, 减少对这类字段的检索和更新)

4. 大表的拆分:
   垂直拆分:
   按列关联度拆分.

   水平拆分:
   按照时间, 地域, 范围等拆分;
   冷热数据(历史数据归档)

一些优化设置

1. 必须指定默认存储引擎为InnoDB, 禁用MyISAM.
2. 默认字符集使用UTF8mb4, 这个字符集包含了所有字符.
3. 关闭大小心敏感lowe_case_tables_names=1.
4. 生产数据库中不使用存储过程, 触发器, 试图, event等功能.
5. 尽量不使用lob, text, enum, set等字段, 这些字段的检索性能不高, 很难使用索引进行优化, 如果必须使用这些技能, 一般采取特殊的结构设计, 或者与程序结合使用其他的字段类型替代.
6. 主键列使用UNSIGNED整数, 自增auto_increment
7. 给全部字段加comment注释
8. 必备三个字段: id, xxx_create, xxx_modified
9. 回到二值世界: 所有字段必须NOT-NULL, 空值指定default
10. 用TINYINT+注释取代ENUM和SET
11. IP的处理: MySQL内置了两个IP相关的函数INET_ATON(), INET_NTOA(), 可以实现IP地址与整数的项目转换, 因此使用INT UNSIGNED四个字节就能存储IP, 查询性能更高
12. 时间的处理: 用MySQL内置的FROM_UNIXTIME()和UNIX_TIMESTAMP()将日期转化为数字, 提升性能.

命名规范

1. 库名规则为数据库类型代码 + 项目简称 + 识别代码 + 序号, 识别代码如: DEV, TEST, 生产环境不加.
2. 表名字符取值范围为: a~z, 0~9, _, -(只允许小写, 不允许驼峰式大小写)
3. 索引命名: 主键使用pk_, UNIQUE使用uk_, 普通索引使用idx_

物理删除与逻辑删除的选择

逻辑删除, 保留了"无价"的数据, 但是必定造成数据冗余, 增加每个SQL的复杂度,
建议对价值比较高的数据, 如订单进行逻辑删除.