数据库设计要注意的几个点

数据库设计

字符集

• 统一使用utf8mb4，MySQL在5.5.3之后增加了utf8mb4字符编码，简单的说utf8mb4才是真正的utf-8，因为它可以支持4个字节每个字符，ps：当我们要存储emoji表情就必须使用utf8mb4，性能上不会有损失

建表规范

• 字段名必须使用小写字母
• 小数类型为decimal，禁止使用float和double（会有精度损失）
• 如果存储的字符串长度几乎相等，使用char定长字符串类型（与varchar的区别，例如，存储字符串'abc'，对于char (10)，表示你存储的字符将占10个字节(包括7个空字符)，而同样的varchar (10)则只占用3个字节的长度，10只是最大值，当你存储的字符小于10时，按实际长度存储）
• varchar 是可变长字符串，不会预先分配存储空间，长度不要超过 5000，如果存储长度大于此值，定义字段类型为text，独立出来一张表，用主键来对应，避免影响其它字段索 引效率
• 表必备三字段:id, date_create, date_update

• 为避免关连查询，字段允许适当冗余，以高查询性能，但必须考虑数据一致。冗余字段应遵循

  • 不是频繁修改的字段
  • 不是 varchar 超长字段，更不能是 text 字段

• 合适的字符存储长度，无负数的情况需要使用无符号位存储，扩大表示范围

  类型 | 表示范围
  — | —
  unsigned tinyint | 无符号位 0-255
  unsigned smallint | 无符号位 0-65535
  unsigned int | 无符号位 约 0-42.9亿
  unsigned bigint | 无符号位 约 0-10的19次方

索引

• 在varchar字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据实际文本区分度决定索引长度即可
• 业务上具有唯一特性的字段，即使是多个字段的组合，也必须建成唯一索引
• 索引建立需要遵循最左匹配原则。例如：建组合索引的时候，区分度最高的在最左边

• SQL性能优化的目标：至少要达到range级别，要求是ref级别，如果可以是consts最好。

  • consts 单表中最多只有一个匹配行（主键或者唯一索引），在优化阶段即可读取到数据。
  • ref 指的是使用普通的索引（normal index） 。
  • range 对索引进行范围检索。

  • explain来解释执行计划，type列指的是MySQL在表中找到所需行的方式。常见类型如下：

    ALL | index | range |ref |eq_ref | const,system | NULL
    — | — | — | — | — | — | —
    ALL是全表扫描，MySQL要遍历全表来找到匹配的行 | 索引全扫描，MySQL遍历整个索引来找到匹配的行 | 索引范围扫描，一般用于<、>、<=、>=、between操作 | 使用非唯一索引扫描或者唯一索引的前缀扫描，返回匹配单独值的记录行 | 唯一索引，对于每个键值，表中只有一行记录与之匹配 | 单表只有一个匹配行，例如根据主键或者唯一索引记性的查询

• 范围列可以用到索引
  范围条件有：<、<=、>、>=、between等。
  范围列可以用到索引，但是范围列后面的列无法用到索引，索引最多用于一个范围列，如果查 询条件中有两个范围列则无法全用到索引。
  例如 建有(a,b,c)联合索引

  • where b>0 and a<9 只有a索引会生效
  • where b>0 and a=9 a,b 索引生效
  • where a<0 and b=9 只有a索引生效
  • where c<0 and b=9 不符合最左原则，索引不生效

分库分表

• 单表行数超过500万行或者单表容量超过2GB，才推荐进行分库分表