在设计表和字段时，对具体的细节老是不确定。这里作为经验记录帖，方便查询，随时更新

字段

1. 字段类型

尽量选择存储空间小的字段类型

  1. 整数类型分为 tinyint，smallint，int，bigint 长度分别 1，2,4,8字节
    1. BOOLEAN 在 MySQ 里的类型为 tinyint(1)
    2. int(30) 中的数字 30 表示整数值在查询结果中显示的字符宽度，不影响存储范围或分配的存储空间。
    3. int(20) 能存储数字的范围为 int 类型可以存储的范围，占用 4 个字节的存储空间。
  2. 小数使用 DECIMAL(5,2)，表示值的范围是从 -999.99 到 999.99
    1. 如果小数位存储4位，那么修改数据的时候传入的 5 位，那么第五位数字在存储的时候遵循四舍五入规则。
    2. 如果数值在其取值范围之外，则直接报 Out of range value 错误。
  3. 字符类型有两种，char 是定长字符串类型，varchar 是可变长度字符串类型
    1. char 的上限为 255 字节，varchar 的上限 65535 字节，text 的上限为 65535。同一张表中，所有 varchar 字段的长度加起来不能大于 65535。
    2. char（n）和 varchar（n）中括号中 n 代表字符的个数，并不代表实际存储的字节数或整数值的范围。汉字在 UTF-8 编码下通常占用 3 个字节，char(n) 可以存放 n / 3 个汉字。
    3. char在存储的时候会截断尾部的空格，varchar 在存储时超过范围也会插入报错。
    4. varchar 会使用 1-3 个字节来存储长度，text 不会。

2. 字段长度

Mysql 字段定义的长度分为字符长度和字段长度

在 mysql 中，varchar 和 char 类型表示字符长度，而其他类型表示的长度都表示字节长度。比如 char(10)表示字符长度是 10，而bigint（4）表示显示长度是4个字节，但是因为 bigint 实际长度是8个字节，所以 bigint（4）的实际长度就是 8 个字节。

我们在设计表的时候，只需要考虑 char 和 varchar 的字符长度，一般设置为 2 的幂次方

3. 主键设计

主键最好不要与业务逻辑有所关联。比如身份证，虽然是唯一的，但是不建议。主键最好是毫无意义的一串独立不重复的数字：

  1. UUID
  2. Auto_increment自增的主键
  3. 雪花算法生成的主键。

4. 优先考虑逻辑删除，而不是物理删除

  • 物理删除：把数据从硬盘中删除
  • 逻辑删除：给数据添加一个字段，比如 is_deleted，标记该数据已经逻辑删除。
# 物理删除
delete from account_info_tab whereaccount_no ='666';

# 逻辑删除
update account_info_tab set is_deleted = 1 where account_no ='666';

增加状态字段，更变它用来控制数据

5. 每个表都需要添加这几个通用字段

一般来说必须字段：

id：主键，一个表必须得有主键，必须
create_time：创建时间，必须
modifed_time/update_time: 修改时间，必须，更新记录时，需要更新它
version : 数据记录的版本号，用于乐观锁，非必须
remark ：数据记录备注，非必须
modified_by :修改人，非必须
creator ：创建人，非必须

6. 一张表的字段不宜过多

  • 一张表字段尽量不要超过 20 个字段。如果一张表的字段过多，表中保存的数据可能就会很大，查询效率就会很低。如果实在需要很多字段：
    1. 可以把一张大的表，拆成多张小的表，它们的主键相同即可。
    2. 将表分成两张表，一张作为条件查询表，一张作为详细内容表。

7. 尽可能使用 not null 定义字段

如果将字段默认设置成一个空字符串或常量值，并没有什么不同，且都不会影响到应用逻辑， 那就可以将这个字段设置为 NOT NULL。

  1. 首先，NOT NULL 可以防止出现空指针问题。
  2. 其次，NULL值存储也需要额外的空间的，它也会导致比较运算更为复杂，使优化器难以优化 SQL。
  3. NULL值有可能会导致索引失效

8. 设计表时，评估哪些字段需要加索引

如果数据量过少，就没有必要添加索引。否则设计表的时候，如果有查询条件的字段，一般就需要建立索引。但是索引不能滥用：

  1. 不要创建太多索引，一般单表索引个数不要超过5个。因为创建过多的索引，会降低写得速度。
  2. 区分度不高的字段，不能加索引，如性别等
  3. 索引创建完后，避免索引失效的情况，如使用 mysql 的内置函数，会导致索引失效的
  4. 索引过多的话，可以通过联合索引的话方式来优化。然后的话，索引还有一些规则，如覆盖索引，最左匹配原则等等
下表很可能会有根据 user_id 或者 name 查询用户信息，给 user_id 加上唯一索引，name 加上普通索引
CREATE TABLE user_info_tab (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `age` int(11) DEFAULT NULL,
  `name` varchar(255) NOT NULL,
  `create_time` datetime NOT NULL,
  `modifed_time` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`) USING BTREE,
  UNIQUE KEY un_user_id (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

9. 不需要严格遵守 3NF，通过业务字段冗余来减少表关联

数据库三范式（3NF）：

第一范式：对属性的原子性，要求属性具有原子性，不可再分解

第二范式：对记录的唯一性，要求记录有唯一标识，即实体的唯一性，即不存在部分依赖

第三方式：对字段的冗余性，要求任何字段不能由其他字段派生出来，它要求字段没有冗余，即不存在传递依赖

设计表及其字段之间的关系, 应尽量满足第三范式。但是有时候，可以适当冗余，来提高效率。比如以下这张表：

商品名称 商品型号 单价 数量 总金额
手机 华为 8000 5 40000

总金额这个字段的存在，表明该表的设计不满足第三范式，因为总金额可以由单价 * 数量得到，说明总金额是冗余字段。但是，增加总金额这个冗余字段，可以提高查询统计的速度，这就是以空间（增加列）换时间的作法。

10. 选择合适统一的字符集

MySQL 支持的字符集有 utf8、utf8mb4、GBK、latin1 等：

  1. latin1：MySQL 默认字符集，1 个字节长度。(MySQL 8.0 之前默认)

  2. utf8：支持中英文混合场景，国际通过，3 个字节长度

  3. utf8mb4: 完全兼容 utf8，4 个字节长度，一般存储emoji 表情需要用到它。(MySQL 8.0 之后默认)

  4. GBK ：支持中文，但是不支持国际通用字符集，2 个字节长度

11. 时间的类型选择

我们设计表的时候，一般都需要加通用时间的字段，如 create_time、modified_time 等等。

优先使用 datetime 类型来保存日期和时间，因为存储范围更大，且跟时区无关。

12. 大字段

设计表的时候，我们尤其需要关注一些大字段，即占用较多存储空间的字段。比如用来记录用户评论的字段，又或者记录博客内容的字段。如果直接把表字段设计成 text 类型的话，就会浪费存储空间，查询效率也不好。

这种非常大的数据，可以保存到mongodb中，然后，在业务表保存对应 mongodb 的 id 即可。这种设计思想类似于，我们表字段保存图片时，为什么不是保存图片内容，而是直接保存图片 url 即可。

13. SQL 编写的一些优化经验

一些遇到的 SQL 优化经验：

  1. 查询 SQL 尽量不要使用 select *，而是 select 具体字段

  2. 如果知道查询结果只有一条或者只要最大/最小一条记录，建议用 limit 1

  3. 应尽量避免在 where 子句中使用 or 来连接条件

  4. 注意优化 limit 深分页问题

  5. 使用 where 条件限定要查询的数据，避免返回多余的行

  6. 尽量避免在索引列上使用 mysql 的内置函数

  7. 应尽量避免在 where 子句中对字段进行表达式操作

  8. 应尽量避免在 where 子句中使用 != 或 <> 操作符

  9. 使用联合索引时，注意索引列的顺序，一般遵循最左匹配原则。

  10. 对查询进行优化，应考虑在 where 及 order by 涉及的列上建立索引

  11. 如果插入数据过多，考虑批量插入

  12. 在适当的时候，使用覆盖索引

  13. 使用 explain 分析你 SQL 的计划