Mysql基础9-数据库规约

10.1 为何需要规约

• 数据库比较复杂，

10.1.1糟糕的数据库设计

• 数据冗余，浪费空间
• 程序的性能差
• 数据库插入更新都比较麻烦，异常（不使用物理外键）

10.1.2 良好的数据库设计

• 节省内存空间
• 保证数据库的完整性
• 方便开发系统

10.1.3 软件开发，数据库设计

• 分析需求：分析业务和需要处理的数据需求
• 概要设计：设计关系图E-R图
• 若字段命名有多个单词不用驼峰命名，用_隔开
• 关注：关系型数据库建关注中间表，nosql数据库redis{1111:{1,2,3}}

10.1.4 例子：设计数据库步骤（blog）

• 收集信息，分析需求
• • 用户表-登录注册注销，创建人

show CREATE TABLE `user`
CREATE TABLE `user` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'id',
  `name` varchar(60) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '姓名',
  `password` varchar(60) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '密码',
  `tellphone` varchar(15) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '电话',
  `email` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '邮箱',
  `sex` varchar(2) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '性别',
  `age` int(3) DEFAULT NULL COMMENT '年龄',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `status` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '用户状态',
  `version` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '版本号',
  `create_user_id` int(60) NOT NULL COMMENT '创建用户id',
  `open_id` varchar(500) COLLATE utf8mb4_general_ci NOT NULL COMMENT '微信id',
  `avatar` varchar(500) COLLATE utf8mb4_general_ci NOT NULL COMMENT '头像地址链接',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

• 分类表-文章分类，创建人

show CREATE TABLE `category`
CREATE TABLE `category` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '分类id',
`name` varchar(60) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '名称',
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
`status` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '用户状态',
`version` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '版本号',
`create_user_id` int(60) NOT NULL COMMENT '创建用户id',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

• 文章表-文章信息

show CREATE TABLE `blog`
CREATE TABLE `blog` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '博客id',
  `title` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '标题',
  `author_id` int(11) NOT NULL COMMENT '作者id',
  `category_id` int(11) NOT NULL COMMENT '分类id',
  `content` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '内容',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `love` int(11) DEFAULT NULL COMMENT '点赞',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

• 评论表

show CREATE TABLE `comment`
CREATE TABLE `comment` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '评论id',
  `blog_id` int(11) NOT NULL COMMENT '博客id',
  `user_id` int(11) NOT NULL COMMENT '评论人',
  `content` varchar(2000) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '评论内容',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '评论时间',
  `user_id_parent` int(11) DEFAULT NULL COMMENT '恢复人的id',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

• 链接表

show CREATE TABLE `links`
CREATE TABLE `links` (
  `id` int(11) NOT NULL COMMENT '链接标题',
  `links` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '链接标题',
  `href` varchar(2000) COLLATE utf8mb4_general_ci NOT NULL COMMENT '链接',
  `sort` int(10) NOT NULL COMMENT '排序',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

• 自定义表config（系统配置信息，关键字或主字段）字段形式：key:value

• 说说表（发表 id,content,create_time）

• 标识实体（需求落地到每个字段）

• 标识实体间关系

• • 写博客：user>blog
  • 建分类：user>category
  • 评论：user>user>blog
  • 关注：user>user_follow

CREATE TABLE `uesr_follow` (
  `id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL COMMENT '被关注人id',
  `follow_id` int(11) NOT NULL COMMENT '关注人id',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

10.2函数依赖

• 某个属性集决定另一个属性集时，称另一属性集依赖于该属性集
• 定义：设 R 为任一给定关系，如果对于 R 中属性 X 的每一个值，R 中的属性 Y 只有唯一值与之对应，则称 X 函数决定 Y 或称 Y 函数依赖于 X ，记作 X—>Y。其中，X 称为决定因素。

10.2.1 完全函数依赖

• 书上定义的意思基本是：如果存在 X 属性集（注意是集合，说明是联合主键）决定 唯一的 Y ，且 X 中的任一子集都不能决定 唯一的 Y，则 Y 完全依赖于 X。

10.2.2 完全函数依赖

• 于完全函数依赖区别是：就是 X 的属性集中任一子集可以决定唯一的 Y

10.2.2 传递函数依赖

• 定义：设 R 为任一给定关系， X Y Z 为其不同的属性子集，若 X —> Y, Y 不决定 X 且 Y —>Z，则有 X —>Z，称为 Z 传递函数依赖于 X

10.3 三大范式

• 为何需要规约?
• 信息重复
• DML更新、插入、删除异常
• 无法保证数据的一致性
• 设计关系型数据库时，遵从不同的规范要求，设计出合理的关系型数据库，这些不同的规范要求被称为不同的范式，各种范式呈递次规范，越高的范式数据库冗余越小
• 目前关系型数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式（4NF）和第五范式（5NF，又称完美范式）。一般来说，数据库只需要满足第三范式就行了

10.3.1 第一范式（1NF）

• 最基本的范式：保证每列的原子性根据系统的实际需求来定
• 数据库表中的所有字段值都是不可分解的原子值，就说明该数据库满足了第一范式

10.3.2 第二范式（2NF）

• 保证第一范式
• 保证一张表只描述一件事情
• 消除了非主属性对主属性的部分函数依赖

10.3.3 第三范式（3NF）

• 保证第一、二范式
• 保证每列都和主键直接相关，不能间接相关
• 消除了非主属性对主属性的传递地函数依赖

10.3.4 规范和性能问题

• 阿里规范关联表查询不超过3张
• 从商业化的需求和目标（用户成本体验）性能>规范性
• 在规范性能问题时，适当考虑规范性
• 给表加一些冗余字段，有多表查询变为单表查询
• 读远大于写，增加的耗时相对于读取可以忽略不计，故意加一些字段列，从大数据量减为小数据量查询
• 加索引也会有开销问题

SQL优化见本专栏下一篇