mysql数据库设计

已于 2022-11-20 15:30:10 修改
阅读量1.3k 收藏 1
点赞数 1
分类专栏： mysql 文章标签： 数据库 mysql 服务器
于 2022-06-21 14:04:12 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/baidu_35805755/article/details/123636907
版权

总结

数据库，存储引擎，表，字段，索引

  • 范式类型和大小默认为空和索引

要不要用数据库，用什么数据库，怎么用数据库
单体，分布式，数据类型，数据量，并发量，效率，安全
数据库只是数据的一种存储方式，也有一定的使用成本；某些情况下可以换一种数据存储方式，比如 JSON文件、Excel表格甚至txt文件
json存储和proto存储可以跨语言，json存储数据库类型是文本，proto格式存储是二进制
符合范式，加冗余（提高数据处理速度）
加索引
加视图（提高速度，表拆分后union视图）
安全：主从复制、mysql集群
效率：读写分离、分库分表、mysql集群

理论

E-R (entity-relationship)：实体、属性、关联（1:1, 1:n, m:n）
CDM (Conceptual Data Model)：概念数据模型
PDM (Physical Data Model)：物理数据模型

1NF范式：

  • 字段原子性，实体属性不可再分

2NF范式：

  • 部分依赖，通过候选键可以直接区分每个非候选键
  • 在1NF范式的基础上消除非主属性对主码的部分依赖
  • 候选键：可以唯一标识每一行数据的属性，比如，id，id_card，tel_number

3NF范式：

  • 传递依赖，任何非主属性不依赖于其它非主属性
  • 在2NF基础上消除-传递依赖

注意

不用mysql关键字做字段名，表名称
不使用外键，使用代码保证表与表之间的一致性

数据库

数据库：存储引擎、编码、排序规则

存储引擎：MyISAM与InnoDB

名称锁定事务索引
InnoDB行锁定支持没有限制
MyISAM表锁定不支持最多64个索引， 索引中字段字节总和不得大于 1000 bytes
Merge前提：MYISAM引擎&&表结构一致，支持单表唯一索引

编码：

  • utf-8mb4 (most bytes 4bytes)：英文使用1个字节，中文使用3个字节来编码；特殊字符表情，宽字符4字节；可以存储表情符号
  • utf-8：英文及符号占1个字节，中文及符号占3个字节，数字一个字节
  • unicode：中文、英文都是2个字节
  • utf-16：一般英文和中文都要占用2个字节；unicode扩展区汉字占用4个字节
  • utf-32：所有字符4个字节

排序规则：

排序规则优点缺点其他
utf8_general_ci效率较高正确性差能接受ß = s（单s）而不是ss（双ss）则使用；ß = s
utf8_unicode_ci效率较低正确性较高德语和法语，utf8_unicode_ci工作的很好；ß = ss

表

什么数据可以放到表里面
分类：基础表、扩展表、关联表
名称

参考网址
单表数据量大小控制在500万行以内

  • 500万行不是mysql数据库的限制，修改表结构，备份，恢复限制
  • 历史数据归档，解决日志数据过大
  • 分库分表，解决表过大

谨慎使用mysql分区表，谨慎选择分区键？？

  • 逻辑上表现为一个表，物理上表现为多个文件
  • 建议采用物理分表的方式管理大数据

禁止在mysql数据库中存储大的二进制文件

  • 大文件往往需要随机IO操作太耗费时间
  • mysql中存储地址URL即可
  • 图片、文件存储于专用文件服务器

禁止在线上做数据库压力测试

关系

  • 一对一：任意一方放入另一方的主键
  • 一对多：在多的这一方放入一的这一方的主键
  • 多对多：单独建表需要建立关联表

表/名称

前缀用于区分系统表和业务表
后缀代表表的分类

字段

字段

  • 分类：业务字段和非业务字段（表操作字段）
  • 字段类型和大小、默认为空和索引
  • 字段名称，类型、有无符号，长度、宽度，默认值，允许为空，索引
  • 主键、外键
  • 冗余字段

设计

  1. 业务字段和非业务字段（表操作字段），id是用于操作表的字段，没有业务含义
  2. 使用程序代替外键
  3. 空间合适：
    • 类型精确，多用int、char；
    • 长度精确，固定长度；
    • 有无符号 unsigned
    • 避免使用 text，blob
    • 字段类型越大，建立索引占用空间越多，一页框能存储的索引节点就越少，遍历时磁盘IO就越多
    • 磁盘中的索引节点会载入内存中的页框
  4. 尽可能把所有列定义为not null
    • 聚集索引的生成要求唯一且非空

字段/数据类型

参考网址
数据类型

类型名存储大小字节数介绍
bit存储bit值，0或1，长度最大64位
char宽度：0~850-255 bytes定长字符，显示变长，空间占用定长；255/3=85个汉字
varchar宽度：0~5000~2万0-65535 bytes变长字符，显示变长，空间占用变长；65535/3=21845个汉字；阿里显示宽度最大5000
binary包含0或1字符
varbinary包含0或1字符
bigint8字节无符号值：0 到约 10 的 19 次方常用作主键
tinyint1字节无符号值：0 到 255常用作字典类型
int4字节无符号值：0 到约 43 亿
smallint2字节无符号值：0 到 65535
mediumint3字节无符号：0到16777215（1千600万）
double8bytes(2.22…E-308，1.79… E+308)双精度，浮点数
decimal(a,b)16bytesa最大38，b在0~a之间decimal(10, 7)：总长为10，小数长7
float4bytes(1.17…E-38，3.40…E+38)单精度，浮点数
tinytext255bytes0-65 535 bytes255/3=85个汉字
text65535/1024=64K0-65535 bytes65535/3=21845个汉字，约2万汉字
mediumtext16777215/1024/1024=16M0-16 777 215 bytes16777215/3=5592405个汉字，559万汉字
longtext4294967295/1024/1024/1024=4G0-4 294 967 295 bytes4294967295/3=1431655765个汉字，14亿汉字
TINYBLOB255bytes0-255 bytes不超过 255 个字符的二进制字符串
BLOB63.9KB0-65 535 bytes二进制形式的长文本数据
MEDIUMBLOB15.9MB0-16 777 215 bytes二进制形式的中等长度文本数据
LONGBLOB3.9GB0-4 294 967 295 bytes二进制形式的极大文本数据

注意：

  • mysql varchar(3) 中的数字描述的是字符个数不是字节数：，不论中文 英文 数字 都是3个。
    • 4.0版本以下，varchar(100)，指的是100字节，如果存放UTF8汉字时，只能存33个（每个汉字3字节）
    • 5.0版本以上，varchar(100)，指的是100字符，无论存放的是数字、字母还是UTF8汉字（每个汉字3字节），都可以存放100个。
  • BINARY 和 VARBINARY 类似于 CHAR 和 VARCHAR，前两个包含二进制字符串即包含字节字符串而不是字符字符串
  • 整数类型只要设置了类型，存储大小由类型决定不由显示宽度决定
  • navicat中整数类型后面括号中的数字，表示的是显示宽度，不表示存储长度，这点与 varchar、char 后面的数字含义是不同的。
  • 经纬度：decimal(10, 7)只需要精确到小数点后 7 位，精度就是 1CM，因此，数据库保存经纬度采用 decimal(10, 7) 即可。
  • 钱：decimal(15, 2)万亿.角分；或者直接到分的整数，
  • 可以把小数和整数分开存储
  • varchar：显示宽度最大5000
  • bigint(20)自增做主键
  • tinyint(0~255)做字典
  • text：2万汉字

日期时间：

类型名范围格式介绍字节
datetime1000-01-01 00:00:00/9999-12-31 23:59:59YYYY-MM-DD HH:MM:SS阿里规范datatime；是否需要根据当前时间更新；为null存储为null8字节
timestamp1970-01-01 00:00:00/2038YYYYMMDD HHMMSS时间戳；timestamp会将时区存储进去；为null时为当前时间4字节
time‘-838:59:59’/‘838:59:59’HH:MM:SS
year1901/2155YYYY
date1000-01-01/9999-12-31YYYY-MM-DD

注意：

  • 时间默认值：CURRENT_TIMESTAMP
  • create_time使用datetime，只在创建时使用
  • update_time使用datetime，更新时需要更新这个字段

字段/公共字段

部分

id(bigint(20)，主键，自增，无符号)
create_time(datetime) ：创建时使用1次
update_time(datetime) ：根据当前时间更新，默认值CURRENT_TIMESTAMP
tenant_id(bigint(20)，无符号)：租户ID

全部

id(bigint(20)，主键，自增，无符号)

create_time(datetime) ：创建时使用1次
update_time(datetime) ：根据当前时间更新，默认值CURRENT_TIMESTAMP
delete_time(datetime)：删除时使用1次

create_by(char(30))：创建者
update_by(char(30))：更新者
delete_by(char(30))：删除者

tenant_id(bigint(20)，无符号)：租户ID
data_scope：同一租户下，数据的范围

is_enabled(tiny_int(1)，无符号)：注释：是否启用 1:true启用 0:flase停用 默认：1  
is_deleted(tiny_int(1)，无符号)：注释：是否删除 1:true删除 0:false未删除 默认：0

字段/默认值

避免字段默认值带来业务意义
直接用字段默认值设置为常用值

字段/名称

主键

环境分类：单体、分布式
生成算法：UUID、雪花算法
生成方法：mysql自增、java代码生成、ORM框架生成

innoDB数据库引擎时，使用int自增做主键

参考网址

分类说明
mysql自增主键innoDB
mysql自带uuid
ORM框架自动插入雪花算法
java生成uuid、雪花算法
  1. 设计经验：
  • 使用自增主键；不方便获取数据库生成的id，框架会返回插入的数据对象
  • 雪花算法：bigint(20)
  • uuid：varchar(36)

外键

主表的主键作为从表的外键
删除时的约束：删除主表记录时，从表中的动作

  • CASCADE（级联）：删除主键表时，从表一并删除
  • RESTRICT/NO ACTION：从表约束主表-主表中的记录在外键中有时，不可删除
  • SET NULL：从表约束主表-主表中的记录在外键中有时，设置从表中该外键值为null

索引

  1. 索引建立，建立一个好的索引
    • 联合索引左侧，放区分度高的（优先索引左侧），查询最频繁的，字段长度小的（1页能存储更多的索引节点）
    • varchar字段建立索引，必须制定长度，长度=20时区分度=90%， count(distinct left(列名, 索引长度))/count(*)
  2. 索引使用，查询尽量使用到索引
  3. 建议单张表索引不超过5个
  4. 禁止给表中的每一列都建立单独的索引
  5. 每张表必须有主键
  6. 一般开发使用 普通索引和唯一索引就可以了
  7. 手机号设置唯一索引；身份证设置成唯一索引
  8. 与数据字典对应的表sys_dict 多建立索引
  9. 关联查询join字段建立索引

索引原理

  • B+树
  • 建立B+树的过程比较耗时
  • 建立一颗好的B+树，有助于提高查询速度

索引名称：

  • 主键索引：pk_ 字段名
  • 唯一索引：uk_字段名
  • 普通索引：idx_字段名
  • 组合索引：idx_字段名1_字段名2

事务

分库分表

分库分表时机：523原则，单表行数超过 500 万行或者单表容量超过 2GB；预计三年后的数据量根本达不到这个级别，请不要在创建表时就分库分表。
分库：请求发布到不同的库处理，同一条请求单表的压力没有降低。
分表：数量合适

分表/水平拆分

参考网址

方法1和方法3结合的方式来进行分表

  1. 预先估计会出现大数据量并且访问频繁的表，将其分为若干个表：映射方法 表id-（hash算法、取余算法）-分表表名
    • 事先没有想到怎么办
    • 换用算法就破坏了对应关系
  2. merge存储引擎：TYPE=MERGE UNION=(user1,user2) INSERT_METHOD=LAST
    • 先有几张存储引擎为MYISAM的表+他们的表结构一致
    • 一个 merge 表不能在整个表上维持 unique 索引，只能在单个表上维持unique索引。当你执行一个 insert，数据进入那张分表（取决于 insert_method 选项的值）

分表/垂直拆分

按业务拆分，把业务拆分的更加细

例子

传递依赖：​employee表中有depart_code,depart_name；depart表中有depart_code,depart_name；如果depart表中的depart_name改变了，employee表中depart_name怎么办
对象数据库：mysql是结构化数据库，图片和文件等二进制文件实体放到对象数据库中，mysql中放入链接地址即可
主键：mybatis-plus雪花算法默认int类型19位，所以设计数据库时bigint(20)
字段：树形目录所需字段，id parent

例子/数据字典

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for sys_dict_type
-- ----------------------------
DROP TABLE IF EXISTS `sys_dict_type`;
CREATE TABLE `sys_dict_type`  (
  `dict_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '字典主键',
  `dict_name` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '' COMMENT '字典名称',
  `dict_type` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '' COMMENT '字典类型',
  `status` char(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '0' COMMENT '状态（0正常 1停用）',
  `create_by` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '' COMMENT '创建者',
  `create_time` datetime NULL DEFAULT NULL COMMENT '创建时间',
  `update_by` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '' COMMENT '更新者',
  `update_time` datetime NULL DEFAULT NULL COMMENT '更新时间',
  `remark` varchar(500) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`dict_id`) USING BTREE,
  UNIQUE INDEX `dict_type`(`dict_type`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 12 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '字典类型表' ROW_FORMAT = DYNAMIC;
-- ----------------------------
-- Table structure for sys_dict_data
-- ----------------------------
DROP TABLE IF EXISTS `sys_dict_data`;
CREATE TABLE `sys_dict_data`  (
  `dict_code` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '字典编码',
  `dict_type` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '' COMMENT '字典类型',
  `dict_label` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '' COMMENT '字典标签',
  `dict_value` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '' COMMENT '字典键值',
  `is_default` char(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT 'N' COMMENT '是否默认（Y是 N否）',
  `dict_sort` int(11) NULL DEFAULT 0 COMMENT '字典排序',
  `status` char(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '0' COMMENT '状态（0正常 1停用）',
  `create_by` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '' COMMENT '创建者',
  `create_time` datetime NULL DEFAULT NULL COMMENT '创建时间',
  `update_by` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '' COMMENT '更新者',
  `update_time` datetime NULL DEFAULT NULL COMMENT '更新时间',
  `remark` varchar(500) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`dict_code`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 40 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '字典数据表' ROW_FORMAT = DYNAMIC;

SET FOREIGN_KEY_CHECKS = 1;

注意

  1. dict_type加上唯一前缀，区分不同业务的字典

参考文档

《阿里巴巴泰山版Java开发手册》

Jason Yien
关注
  • 1
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
MySQL 数据库设计规范.zip
01-26
数据库MySQL 数据库设计规范.zip
mysql 数据库设计
09-11
大家都知道mysql的myisam表适合读操作大，写操作少;表级锁表
MySQL数据库设计、优化.pptx
06-16
MySQL数据库设计、优化 叶金荣 微博： @yejinrong 微信公众号： MySQL中文网 QQ群: 125572178、272675472 2013.08.20 MySQL数据库设计、优化全文共24页，当前为第1页。 提纲 规范 基础规范 命名规范 库表规范 字段规范 索引规范 开发环境 优化 MySQL数据库设计、优化全文共24页，当前为第2页。 规范 基础规范 全部使用InnoDB引擎，MyISAM适用场景非常少 字符集：latin1 => utf8 => gbk 用数据库来持久化存储以及保证事务一致性，不是运算器 读写分离，主库只写和少量实时读取请求 采用队列方式合并多次写请求，持续写入，避免瞬间压力 超长text/blob进行垂直拆分，并先行压缩 冷热数据进行水平拆分，LRU原则 快速更新大数据表禁止直接运行count(*)统计 MySQL数据库设计、优化全文共24页，当前为第3页。 规范 基础规范 单表行记录数控制在1000万以内，行平均长度控制在16KB以内，单表20GB以内 单实例下数据表数量不超过2000个，单库下数据表数量不超过500个 禁止开发环境直连线上生
Web端后台MySQL数据库设计1
08-08
4.4.2 数据库/表设计数据库名称为：uploadfileinfo数据表1：feedback 用以存储用户的反馈信息数据表2：filedownloadreco
MySQL 数据库设计
weixin_53395092的博客
02-15 1005
数据库设计概念 数据库设计就是根据业务系统的具体需求，结合我们所选用的DBMS，为这个业务系统构造出最优的数据存储模型。 建立数据库的表结构以及表与表之间的关联关系的过程。 数据库设计步骤 需求分析（数据是什么？数据具有哪些属性？数据与属性的特点是什么？） 逻辑分析（通过ER图对数据库进行逻辑建模，不需要考虑我们所选用的数据库管理系统） 物理设计（根据数据库自身的特点把逻辑设计转换为物理设计） 维护设计（1、对新的需求进行建表；2、表优化） 表关系 1、一对一：如用户和用户详情 一对一关
mysql——数据库设计
从零开始的博客
06-30 1195
之前我们已经了解了 mysql 的基本增删改查现在我们系统的进行一遍数据库的设计来个例子：下面我们将围绕这个例子来进行数据库的设计我们就来简单的模拟 大学教务处的选课 系统 中的 选课功能注意：简单的模拟，功能可能想到的没那么完善。
Mysql数据库设计
Mr.xing的博客
03-12 3855
数据库设计
关于MySQL 数据库设计
m0_71376929的博客
06-07 497
在创建数据库时，需要指定数据库名称、字符集、排序规则等选项，并使用 CREATE DATABASE 语句来创建。在创建表格的时候，需要定义表格的结构和字段，包括字段名、数据类型、约束条件等，可以使用 CREATE TABLE 语句来创建。需要考虑表格的设计范式、索引的设计、字段数据类型的选择等。进行数据库优化：在完成数据库设计之后，需要对数据库进行优化，以提高查询性能、降低系统负载等。确定需求：在进行数据库设计之前，需要确定应用系统的数据需求，包括数据对象、数据关系、数据操作等方面的需求。
Mysql 数据库设计讲解和案例分析 | Mysql 入门
洛阳泰山的博客
03-16 2614
数据库设计是指在数据库系统开发过程中，根据用户需求，通过对数据进行分析、抽象和建模，设计出一个合理、高效的数据库结构的过程。在物理设计的过程中，需要对表的存储方式进行规划和设计，以确定表的存储方式和索引。同时，还需要收集和整理用户的需求，并对这些需求进行分类和归纳，以便于后续的数据库设计。因此，在设计数据库时，应该考虑到数据的存储方式、索引、分区和备份等问题，并进行合理的优化和设计。因此，在设计数据库时，应该考虑到数据的安全性、访问控制和审计等问题，并进行合理的设计和管理。
MySQL 数据库设计复习笔记及项目实战
09-11
参考的数据库文档主要有：目前国内的常见的PHP系统的数据库
MySQL数据库设计规范
03-06
MySQL数据库与oracle、sql...本规范旨在帮助或指导RD、QA、OP等技术人员做出适合线上业务的数据库设计。在数据库变更和处理流程、数据库表设计、SQL编写等方面予以规范，从而为公司业务系统稳定、健康地运行提供保障。
MYSQL数据库设计规范.docx
09-10
MYSQL数据库设计规范，借鉴了阿里和其他大佬，分为强制、高危、建议三个规范指标。文档后面附有参考链接
Mysql数据库设计规范
05-07
在使用Mysql数据库时，首先要对MySQL数据库的设计规范有所了解，有了深刻的了解之后才能设计出更加规范和文档的数据库
Mysql数据库设计标准
11-07
MySQL数据库设计规范和标准 本规范采用以下术语描述： ★规则：也称为强规范是编程时必须强制遵守的原则 ★建议：编程时必须加以考虑的原则 ★说明：对此规则或建议进行必要的解释 ★示例：对此规则或建议从正、反两...
MySQL数据库设计总结
09-22
一些工作时常用的数据库经验和使用规则,能够提高数据库的运行效率
MySQL 数据库设计实践
11-22
MySQL 数据库设计实践 ，不错的好资料，可以下来看看。
MySQL数据库设计、优化
06-02
MySQL数据库设计、优化
打车系统mysql数据库设计.zip
09-13
打车系统mysql数据库设计
MySQL数据库设计
最新发布
12-13
MySQL数据库设计需要考虑以下几个方面： 1.表的设计：需要考虑表的字段、数据类型、索引、主键、外键等。在设计表时，需要遵循范式化原则，尽量避免数据冗余和数据不一致的情况。 2.索引的设计：索引可以提高查询...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值