MySQL 表设计的经验准则

1、命名规范

数据库表名、字段名、索引名等都需要命名规范，可读性高（一般要求用英文），一眼便知其意。

比如：一个表的账号字段，使用：account_no,account_number

表名、字段名必须使用小写字母或者数字，禁止使用数字开头，禁止使用拼音，并且一般不使用英文缩写。

主键索引名为pk_字段名；唯一索引名为uk_字段名；普通索引名为idx_字段名。

2、选择合适的字段类型

2.1 尽可能选择存储空间小的字段类型，比如数字类型的tinyint、smallint、int、bigint，要从左往右选择。

2.2 小数类型，比如金额选择decimal，禁止使用float，double。

2.3 如果存储的字符串长度几乎相等，使用char定长字符串类型。

2.4 varchar是可变长字符串，不预先分配存储空间，长度不要超过5000。

2.5 如果存储的值太大，建议字段类型修改为text，同时抽出单独的一张表，用主键与之对应。

2.6 同一表中，所有varchar字段的长度加起来不能大于65535，如果有这样的需求，请使用text/longText类型。

3、主键设计要合理

主键最好是毫无意义的一串独立不重复的数字，比如UUID，又或者Auto_increment自增的主键，或者是雪花算法生成的主键等等。

4、选择合适的字段长度

在mysql中，varchar和char类型表示字符长度，而其他类型的都表示字节长度。

比如char(10)表示字符长度是10，而bigint(4)表示显示长度是4个字节，但是因为bigint实际长度是8个字节，所有bigint(4)的实际长度就是8个字节。

字段长度一般设置为2的幂（即2的n次方）。

5、优先考虑逻辑删除而不是物理删除

物理删除：把数据从硬盘中删除，可释放存储空间。（使用delete语句）

逻辑删除：给数据添加一个字段，比如is_deleted，以标记该数据已经逻辑删除。

为什么推荐用逻辑删除，不推荐物理删除呢？

• 数据恢复很困难
• 物理删除会使自增主键不在连续
• 核心业务表的数据不建议做物理删除，只适合做状态变更

6、每个表都需要添加几个通用字段

一般来说，每个表都会具备以下这几个字段：

• id：主键，一个表必须得有主键，必须。

• create_time：创建时间，必须。

• modifed_time/update_time：修改时间，必须，更新记录时，需要更新它。

• version：数据记录的版本号，用于乐观锁，非必须。

• remark ：数据记录备注，非必须。

• modified_by：修改人，非必须。

• creator ：创建人，非必须。

7、一个表的字段不宜过多

一个表的字段尽量不要超过20个字段。

如果一张表的字段过多，表中保存的数据可能就会很大，查询效率就会很低。

因此，一张表不要设计太多字段，如果业务需求，实在需要很多字段，可以把一张大的表，拆成多张小的表，它们的主键相同即可。

当表的字段数非常多时，可以将表分成两张表，一张作为条件查询表，一张作为详细内容表（主要是为了性能考虑）。

8、尽可能使用not null定义字段

一般都建议将字段定义为NOT NUll。

• 首先，NOT NULL 可以防止出现空指针问题。

• 其次，NULL值存储也需要额外的空间，它也会导致比较运算更为复杂，使优化器难以优化 SQL。

• NULL值有可能会导致索引失效。

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

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

• 索引也不要建得太多，一般单表索引个数不要超过5个。因为创建过多的索引，会降低写的速度。

• 区分度不高的字段，不能加索引，如性别等。

• 索引创建完后，还是要注意避免索引失效的情况，如使用 mysql 的内置函数，会导致索引失效。

• 索引过多的话，可以通过联合索引的方式来优化。然后，索引还有一些规则，如覆盖索引，最左匹配原则等等。

假设你新建一张用户表，如下：

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`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 对于这张表，很可能会有根据user_id或者name查询用户信息，并且，user_id是唯一的。因此，你可以给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;

Hash索引和BTree索引的区别

1、范围区别

        hash索引的底层是哈希表，只支持等值比较查询；数据存储在哈希表中的顺序是没有关联的，因此不适合范围查找；（简单的来说就是hash索引适合等值查找，不适合范围查找）

        BTree树索引的叶子节点形成有序链表，便于范围查询。

2.排序

        hash索引无法进行排序

3.哈希碰撞问题

        如果存在重复键值对，哈希索引效率就会很低；

        BTree所有的查询都需要找到叶子节点，性能稳定。

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

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

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

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

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这三种存储引擎的对比区别：

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：下线-在别处被登录'

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、不建议使用存储过程、触发器

什么是存储过程？

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

什么是触发器？

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

使用场景：

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

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

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

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

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

18、1:N 关系的设计

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

有时候两张表存在N:N关系时，我们应该消除这种关系。通过增加第三张表，把N:N修改为两个 1:N。比如图书和读者，是一个典型的多对多的关系。一本书可以被多个读者借，一个读者又可以借多本书。我们就可以设计一个借书表，包含图书表的主键，以及读者的主键，以及借还标记等字段。

19、大字段的存储

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

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

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

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

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

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

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

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

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

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

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

1.2 如果查询结果只有一条或者只要最大/最小的一条记录，建议使用limit 1。

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

1.4 注意优化limit深分页问题。

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

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

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

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

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

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

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

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

1.13 使用 explain 分析 SQL 的计划。