MySQL21个表设计的经验准则(下)

11.避免使用MySQL保留字

如果库名、表名、字段名等属性含有保留字时，SQL语句必须用反引号来引用属性名称，这将使得SQL语句书写、SHELL脚本中变量的转义等变得非常复杂。

因此，我们一般避免使用MySQL保留字，如select、interval、desc等等

12.不搞外键关联，一般都在代码维护

什么是外键呢？

外键，也叫FOREIGN KEY，它是用于将两个表连接在一起的键。FOREIGN KEY是一个表中的一个字段（或字段集合），它引用另一个表中的PRIMARY KEY。它是用来保证数据的一致性和完整性的。

阿里的Java规范也有这么一条：

【强制】不得使用外键与级联，一切外键概念必须在应用层解决。

我们为什么不推荐使用外键呢？

• 使用外键存在性能问题、并发死锁问题、使用起来不方便等等。每次做DELETE或者UPDATE都必须考虑外键约束，会导致开发的时候很难受,测试数据造数据也不方便。

• 还有一个场景不能使用外键，就是分库分表。

13.一般都选择INNODB存储引擎 

建表是需要选择存储引擎的，我们一般都选择INNODB存储引擎，除非读写比率小于1%, 才考虑使用MyISAM 。

有些小伙伴可能会有疑惑，不是还有MEMORY等其他存储引擎吗？什么时候使用它呢？其实其他存储引擎一般除了都建议在DBA的指导下使用。

我们来复习一下这MySQL这三种存储引擎的对比区别吧：

特性	INNODB	MyISAM	MEMORY
事务安全	支持	无	无
存储限制	64TB	有	有
空间使用	高	低	低
内存使用	高	低	高
插入数据速度	低	高	高
是否支持外键	支持	无	无

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

数据库库、表、开发程序等都需要统一字符集，通常中英文环境用utf8。

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

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

• utf8mb4:   完全兼容utf8，4个字节长度，一般存储emoji表情需要用到它。

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

• latin1：MySQL默认字符集，1个字节长度

15.如果你的数据库字段是枚举类型的，需要在comment注释清楚

如果你设计的数据库字段是枚举类型的话，就需要在comment后面注释清楚每个枚举的意思，以便于维护

正例如下：

`session_status` varchar(2) COLLATE utf8_bin NOT NULL COMMENT 'session授权态 00：在线-授权态有效 01：下线-授权态失效 02：下线-主动退出 03：下线-在别处被登录'

反例：

`session_status` varchar(2) COLLATE utf8_bin NOT NULL COMMENT 'session授权态'

并且，如果你的枚举类型在未来的版本有增加修改的话，也需要同时维护到comment。

16.时间的类型选择

我们设计表的时候，一般都需要加通用时间的字段，如create_time、modified_time等等。那对于时间的类型，我们该如何选择呢？

对于MySQL来说，主要有date、datetime、time、timestamp 和 year。

• date ：表示的日期值, 格式yyyy-mm-dd,范围1000-01-01 到 9999-12-31，3字节

• time ：表示的时间值，格式 hh:mm:ss，范围-838:59:59 到 838:59:59，3字节

• datetime：表示的日期时间值，格式yyyy-mm-dd hh:mm:ss，范围1000-01-01 00:00:00到9999-12-31 23:59:59```,8字节，跟时区无关

• timestamp：表示的时间戳值，格式为yyyymmddhhmmss，范围1970-01-01 00:00:01到2038-01-19 03:14:07，4字节，跟时区有关

• year：年份值，格式为yyyy。范围1901到2155，1字节

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

17.不建议使用Stored procedure(包括存储过程，触发器) 

什么是存储过程

已预编译为一个可执行过程的一个或多个SQL语句。

什么是触发器

触发器，指一段代码，当触发某个事件时，自动执行这些代码。使用场景：

• 可以通过数据库中的相关表实现级联更改。

• 实时监控某张表中的某个字段的更改而需要做出相应的处理。

• 例如可以生成某些业务的编号。

• 注意不要滥用，否则会造成数据库及应用程序的维护困难。

对于MYSQL来说，存储过程、触发器等还不是很成熟， 并没有完善的出错记录处理，不建议使用。

18.1:N 关系的设计

日常开发中，1对多的关系应该是非常常见的。比如一个班级有多个学生，一个部门有多个员工等等。这种的建表原则就是：在从表（N的这一方）创建一个字段，以字段作为外键指向主表（1的这一方）的主键。示意图如下:

19.大字段 

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

在MySQl中，这种方式保存的设计方案，其实是不太合理的。这种非常大的数据，可以保存到mongodb中，然后，在业务表保存对应mongodb的id即可。

这种设计思想类似于，我们表字段保存图片时，为什么不是保存图片内容，而是直接保存图片url即可。

20.考虑是否需要分库分表

什么是分库分表呢？

• 分库：就是一个数据库分成多个数据库，部署到不同机器。

• 分表：就是一个数据库表分成多个表。

我们在设计表的时候，其实可以提前估算一下，是否需要做分库分表。比如一些用户信息，未来可能数据量到达百万设置千万的话，就可以提前考虑分库分表。

为什么需要分库分表: 数据量太大的话，SQL的查询就会变慢。如果一个查询SQL没命中索引，千百万数据量级别的表可能会拖垮整个数据库。即使SQL命中了索引，如果表的数据量超过一千万的话，查询也是会明显变慢的。这是因为索引一般是B+树结构，数据千万级别的话，B+树的高度会增高，查询就变慢啦。

分库分表主要有水平拆分、垂直拆分的说法，拆分策略有range范围、hash取模。而分库分表主要有这些问题：

• 事务问题

• 跨库关联

• 排序问题

• 分页问题

• 分布式ID

21.sqL 编写的一些优化经验

最后的话，跟大家聊来一些写SQL的经验吧：

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

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

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

• 注意优化limit深分页问题

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

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

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

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

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

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

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

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

• 使用explain 分析你SQL的计划