博客

转载至掘金社区 https://juejin.cn/post/7147135702604447758 捡田螺的小男孩,致敬

acc_no,1_acc_no,zhanghao
复制代码

account_no,account_number
复制代码

delete from account_info_tab whereaccount_no =‘666’;
复制代码

update account_info_tab set is_deleted = 1 where account_no =‘666’;
复制代码

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;
复制代码

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;
复制代码

以上这张存放商品信息的基本表。

总金额

这个字段的存在，表明该表的设计不满足第三范式，因为

总金额

可以由

单价*数量

得到，说明

总金额

是冗余字段。但是，增加

总金额

这个冗余字段，可以提高查询统计的速度，这就是以空间换时间的作法。

当然，这只是个小例子哈，大家开发设计的时候，要结合具体业务哈。

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

反例：

`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

的这一方）的主键。示意图如下:

学生表是多（

N

）的一方，会有个字段

class_id

保存班级表的主键。当然，一班不加外键约束哈，只是单纯保存这个关系而已。

有时候两张表存在

N:N

关系时，我们应该消除这种关系。通过增加第三张表，把

N:N

修改为两个

1:N

。比如图书和读者，是一个典型的多对多的关系。一本书可以被多个读者借，一个读者又可以借多本书。我们就可以设计一个借书表，包含图书表的主键，以及读者的主键，以及借还标记等字段。

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的计划