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

数据库设计

字符集

• 统一使用 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

• 为避免关连查询，字段允许适当冗余，以高查询性能，但必须考虑数据一致。冗余字段应遵循 1.不是频繁修改的字段 2.不是 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操作	使用非唯一索引扫描或者唯一索引的前缀扫描，返回匹配单独值的记录行	唯一索引，对于每个键值，表中只有一行记录与之匹配	单表只有一个匹配行，例如根据主键或者唯一索引记性的查询

  • 全值匹配我最爱，最左前缀要遵守；
    带头大哥不能死，中间兄弟不能断；
    索引列上少计算，范围之后全失效；
    Like百分写最右，覆盖索引不写星；
    不等空值还有or，索引失效要少用；
    VAR引号不可丢，SQL高级也不难。

分库分表

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

... 待补充

原文地址