1.命名标准

数据库表名、字段名、索引名等都须要命名标准，可读性高(个别要求用英文)，让他人一看命名，就晓得这个字段示意什么意思。

比方一个表的账号字段，反例如下：

acc_no,1_acc_no,zhanghao

正例：

account_no,account_number
  • 表名、字段名必须应用小写字母或者数字，禁止应用数字结尾，禁止应用拼音，并且个别不应用英文缩写。
  • 主键索引名为pk_字段名；惟一索引名为uk_字段名；一般索引名则为idx_字段名。

2.抉择适合的字段类型

设计表时，咱们须要抉择适合的字段类型，比方：

  • 尽可能抉择存储空间小的字段类型，就如同数字类型的，从tinyint、smallint、int、bigint从左往右开始抉择
  • 小数类型如金额，则抉择 decimal，禁止应用 float 和 double。
  • 如果存储的字符串长度简直相等，应用 char 定长字符串类型。
  • varchar是可变长字符串，不事后调配存储空间，长度不要超过5000。
  • 如果存储的值太大，倡议字段类型批改为text，同时抽出独自一张表，用主键与之对应。
  • 同一表中，所有varchar字段的长度加起来，不能大于65535. 如果有这样的需要，请应用TEXT/LONGTEXT 类型。

3. 主键设计要正当

主键设计的话，最好不要与业务逻辑有所关联。有些业务上的字段，比方身份证，尽管是惟一的，一些开发者喜爱用它来做主键，然而不是很倡议哈。主键最好是毫无意义的一串独立不反复的数字，比方UUID，又或者Auto_increment自增的主键，或者是雪花算法生成的主键等等;

4. 抉择适合的字段长度

先问大家一个问题，大家晓得数据库字段长度示意字符长度还是字节长度嘛？

其实在mysql中，varchar和char类型示意字符长度，而其余类型示意的长度都示意字节长度。比方char(10)示意字符长度是10，而bigint（4）示意显示长度是4个字节，然而因为bigint理论长度是8个字节，所以bigint（4）的理论长度就是8个字节。

咱们在设计表的时候，须要充分考虑一个字段的长度，比方一个用户名字段（它的长度5~20个字符），你感觉应该设置多长呢？能够思考设置为 username varchar（32）。字段长度个别设置为2的幂哈（也就是2的n次方）。’;

5，优先思考逻辑删除，而不是物理删除

什么是物理删除？什么是逻辑删除？

  • 物理删除：把数据从硬盘中删除，可开释存储空间
  • 逻辑删除：给数据增加一个字段，比方is_deleted，以标记该数据曾经逻辑删除。

物理删除就是执行delete语句，如删除account_no =‘666’的账户信息SQL如下：

delete from account_info_tab whereaccount_no ='666';

逻辑删除呢，就是这样：

update account_info_tab set is_deleted = 1 where account_no ='666';

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

  • 为什么不举荐应用物理删除，因为复原数据很艰难
  • 物理删除会使自增主键不再间断
  • 外围业务表 的数据不倡议做物理删除，只适宜做状态变更。

6. 每个表都须要增加这几个通用字段如主键、create_time、modifed_time等

表必备一般来说，或具备这几个字段：

  • 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;

10. 不须要严格遵守 3NF，通过业务字段冗余来缩小表关联

什么是数据库三范式（3NF），大家是否还有印象吗？

  • 第一范式：对属性的原子性，要求属性具备原子性，不可再合成；
  • 第二范式：对记录的唯一性，要求记录有惟一标识，即实体的唯一性，即不存在局部依赖；
  • 第三形式：对字段的冗余性，要求任何字段不能由其余字段派生进去，它要求字段没有冗余，即不存在传递依赖；

咱们设计表及其字段之间的关系, 应尽量满足第三范式。然而有时候，能够适当冗余，来提高效率。比方以下这张表

商品名称商品型号单价数量总金额
手机华为8000540000

以上这张寄存商品信息的根本表。总金额这个字段的存在，表明该表的设计不满足第三范式，因为总金额能够由单价*数量失去，阐明总金额是冗余字段。然而，减少总金额这个冗余字段，能够进步查问统计的速度，这就是以空间换工夫的作法。

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

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

个性INNODBMyISAMMEMORY
事务平安反对无无
存储限度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的这一方）的主键。示意图如下:

学生表是多（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的打算