数据库设计

  不管是前端还是后端，在实际项目中，我们都必须参与数据库的设计，但是如何能设计出优雅、高性能的数据库呢？我准备通过我自己在项目中实战分析下如何从0到1的设计数据库

  ️以商城系统为例在分析下吧

  首先我们先不看业务，在我们设计数据库之前，我们应该具备哪些前置知识？

---

关系型数据库三大范式与反范式介绍：

  在我们设计数据库的时候，不可能一开始就想的非常全面，后期变更数据库表、更改字段都会给我们带来不少的麻烦。
  但是，人非全能，谁都有考虑不到位的时候，这个时候我们该怎么去弥补我们在设计数据库阶段留下的小小遗憾呢，俗话说的好，没有规矩、不成方圆，当然，我们在工作中也该应有我们自己的原则，所以规范要求就是可以弥补我们后期可能出现灾难的保障。只有遵从不同的规范要求，才能设计出更加完美的作品。
  So，设计数据库就应该遵循数据库方面的规范，通过自己查阅相关资料，加上我自己的理解，我们应该遵循哪些规范。

第一范式（1NF）：

数据表中的每一列(字段)，必须是不可拆分的最小单元，也就是确保每一列的原子性。

第二范式（2NF）：

是在第一范式的基础上建立起来的，即满足第二范式必须要满足第一范式，第二范式要求数据库表中的每个实例或者行必须可以被唯一区分，任意一个字段都只依赖表中的同一个字段。也就是说确保数据库表的唯一性。

第三范式（3NF）

满足第三范式必须同时满足第一范式和第二范式，即一个表中每列都与主键有直接关系，不存在传递依赖。

反范式

有时候我们一味的去追寻范式，减少冗余，但是这样有时候也会增加我们的读写压力，这个时候我们就需要利用空间换时间，也是就数据库设计反范式，适当增加冗余字段增加读写速度。

总结：

第一范式（1NF）:原子性 字段不可再分,否则就不是关系数据库。
第二范式（2NF）:唯一性 一个表只说明一个事物。
第三范式（3NF）:每列都与主键有直接关系，不存在传递依赖 。
反范式：适当增加冗余字段，增加查询速度。

---

数据库设计与开发规范：

• 库名：

【强制】库的名称必须控制在32个字符以内，相关模块的表名与表名之间尽量体现join的关系，如user表和user_login表。
强制】库的名称格式：业务系统名称_子系统名，同一模块使用的库名尽量使用统一前缀。_
【强制】一般分库名称命名格式是库通配名_编号，编号从0开始递增，比如wenda_001以时间进行分库的名称格式是“库通配名_时间”。
【强制】创建数据库时必须显式指定字符集，并且字符集只能是utf8或者utf8mb4。创建数据库SQL举例：
create database db1 default character set utf8;

• 表结构

【强制】表必须有主键，且设置id为自增主键。
【强制】表禁止使用外键，如果要保证完整下，应由程序端实现，外键使表之间相互耦合，影响update、delete等性能，有可能造成死锁，高并发环境下容易导致数据库性能瓶颈。
【强制】表和列的名称必须控制在32个字符以内，表名只能使用字母、数字和下划线，一律小写。如表名过长可以采用缩写等方式。
【强制】创建表时必须显式指定字符集为utf8或utf8mb4。
【强制】创建表时必须显式指定表存储引擎类型，如无特殊需求，一律为InnoDB。当需要使用除InnoDB/MyISAM/Memory以外的存储引擎时，必须通过DBA审核才能在生产环境中使用。因为Innodb表支持事务、行锁、宕机恢复、MVCC等关系型数据库重要特性，为业界使用最多的MySQL存储引擎。而这是其他大多数存储引擎不具备的，因此首推InnoDB。
【强制】建表必须有comment，表级别和字段级别都要有comment.
【强制】中间表用于保留中间结果集，名称必须以tmp_开头。备份表用于备份或抓取源表快照，名称必须以bak_开头。中间表和备份表定期清理。
【强制】对于线上执行DDL变更，必须经过DBA审核，并由DBA在业务低峰期执行。

• 索引设计

【强制】InnoDB表必须主键为id int/bigint auto_increment,且主键值禁止被更新。
【强制】InnoDB和MyISAM存储引擎表，索引类型必须为BTREE；MEMORY表可以根据需要选择HASH或者BTREE类型索引。
【强制】单个索引中每个索引记录的长度不能超过64KB。

• 一个规范的建表语句示例

CREATE TABLE user (
  id bigint(20) NOT NULL AUTO_INCREMENT,
  user_id bigint(20) NOT NULL default ‘0’ COMMENT ‘用户id’,
  username varchar(45) NOT NULL default ‘’ COMMENT ‘真实姓名’,
  email varchar(30) NOT NULL default ‘’COMMENT ‘用户邮箱’,
  nickname varchar(45) NOT NULL default ‘’ COMMENT ‘昵称’,
  avatar int(11) NOT NULL default ‘0’ COMMENT ‘头像’,
  birthday date NOT NULL default ‘0000-00-00’ COMMENT ‘生日’,
  sex tinyint(4) not null DEFAULT ‘0’ COMMENT ‘性别’,
  short_introduce varchar(150) not null DEFAULT ‘’COMMENT ‘一句话介绍自己，最多50个汉字’,
  user_resume varchar(200) NOT NULL default ‘’COMMENT ‘用户提交的简历存放地址’,
   user_register_ip int NOT NULL COMMENT ‘用户注册时的源ip’,
  create_time datetime NOT NULL default current_timestamp COMMENT ‘用户记录创建的时间’,
  update_time datetime default current_timestamp on update current_timestamp NOT NULL COMMENT ‘用户资料修改的时间’,
  user_review_status tinyint NOT NULL default ‘1’ COMMENT ‘用户资料审核状态，1为通过，2为审核中，3为未通过，4为还未提交审核’,
  PRIMARY KEY (id),
  UNIQUE KEY uq_user_id (user_id),
  KEY idx_username(username),
  KEY idx_create_time(create_time,user_review_status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=‘网站用户基本信息’;

• SQL编写DML语句

【强制】SELECT语句必须指定具体字段名称，禁止写成*。因为select *会将不该读的数据也从MySQL里读出来，造成网卡压力。且表字段一旦更新，但程序端没有来得及更新的话，系统会报错。
【强制】insert语句指定具体字段名称，不要写成insert into t1 values(…)，道理同上。
【强制】事务涉及的表必须全部是innodb表。否则一旦失败不会全部回滚，且易造成主从库同步中断。
【强制】写入和事务发往主库，只读SQL发往从库，即程序端实现读写分离。
【强制】DML语句必须有where条件，且使用索引查找。
【强制】生产环境禁止使用hint，如sql_no_cache，force index，ignore key，straight join等。因为hint是用来强制SQL按照某个执行计划来执行，但随着数据量变化我们无法保证自己当初的预判是正确的，我们要尽量让MySQL优化器自己选择执行计划。
【强制】where条件里等号左右字段类型必须一致，否则无法利用索引。
【强制】生产数据库中强烈不推荐大表上发生全表扫描，但对于100行以下的静态表可以全表扫描。查询数据量不要超过表行数的25%，否则不会利用索引。
【强制】WHERE 子句中禁止只使用全模糊的LIKE条件进行查找，如果要使用like，请使用like ‘xxxx%’的方式，必须有其他等值或范围查询条件，否则无法利用索引。</br

• 多表连接

【强制】禁止跨db的join语句。因为这样可以减少模块间耦合，为数据库拆分奠定坚实基础。
【强制】禁止在业务的更新类SQL语句中使用join，比如update t1 join t2…。

• 事务

【强制】程序设计必须考虑“数据库事务隔离级别”带来的影响，包括脏读、不可重复读和幻读。线上建议事务隔离级别为repeatable-read。

• 线上禁止使用的SQL语句

【高危】禁用update|delete t1 … where a=XX limit XX; 这种带limit的更新语句。如果是非row格式的binlog格式，会导致主从不一致，导致数据错乱。建议加上order by PK。
【高危】禁止使用关联子查询，如update t1 set … where name in(select name from user where…);效率极其低下。
【强制】禁用procedure、function、trigger、views、event、外键约束。因为他们消耗数据库资源，降低数据库实例可扩展性。推荐都在程序端实现。
【建议】禁用insert into …on duplicate key update…、replace into等语句，在高并发环境下，极容易导致死锁。
【强制】禁止联表更新语句，如update t1,t2 where t1.id=t2.id…。

---

当然，还有很多需要我们遵循了，这里只介绍了强制必须执行的，规范摘自MySQL数据库设计规范，大家有其他的也可以进行评论添加。