MySQL 数据库规范--设计篇

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: 目录1.设计阶段2.开发阶段3.调优阶段(未开发,pt_query_digest、show_slow_log、查询优化等)4.福利彩蛋1.设计阶段1.1 数据库表的设计范式(三范式&反范式)为什么需要范式优点:编程相对简单,数据量更小,更适合放入内存,更新更快,只需要更新少量的数据,更少的冗余意味着更少的需要group distinct 之类的操作。

目录


1.设计阶段
2.开发阶段
3.调优阶段(未开发,pt_query_digest、show_slow_log、查询优化等)
4.福利彩蛋

1.设计阶段

1.1 数据库表的设计范式(三范式&反范式)


为什么需要范式

优点:编程相对简单,数据量更小,更适合放入内存,更新更快,只需要更新少量的数据,
更少的冗余意味着更少的需要group distinct 之类的操作。

第一范式

数据表每一列都是不可分割的基本数据项。举例一个人有多个手机号

第二范式

数据表里的所有数据都要和该数据表里的主键有完全相依赖的关系,不能只依赖部分。
举例:用户名&用户技能 是主键,用户居住地 ,那么用户名为主键就可以了。

第三范式

缺点:非键属性都只和候选属性相关,非间属性之间没有关系。举例冠军表中冠军名&冠军生日。

范式的缺陷

查询变得相当复杂,查询时需要更多的连接join ,一些复合索引的列由于范式化的需要被分割到不同的表中,导致索引策略不佳。

反范式

优点:减少了连接,可以更好的利用索引进行筛选和排序,对查询操作可以提高性能。
缺点:要在数据一致性与查询之间找到平衡点,符合业务场景的设计才是好的设计

数据库设计准则

设计的数据库应该按照用户可能的访问路径,访问习惯进行设计,而不是严格按照数据范式来设计

1.2 存储引擎的选择


存储引擎分类
InnoDB:

1.灾难恢复性好
2.支持4中级别的事务,默认事务的隔离级别是Repeatable Read,事务支持是通过MVCC多版本并发控制来提供的。
3.使用行级锁,并发性能高。
4.使用此存储引擎的表,数据的物理组织形式是簇表,数据按主键来组织,即主键索引和数据是在一起的,B+树就是这样的
5.实现缓冲管理,能缓存索引也能缓存数据。
6.支持外键
7.支持热备份

MyISAM:

1.配合锁,实现操作系统下的复制备份,迁移
2.使用表记锁并发性差
3.支持全文索引
4.主机宕机后,表容易损坏,灾难恢复性不佳
5.无事务支持
6.只缓存索引,数据缓存利用操作系统缓冲区实现的,引发过多系统调用,性能不佳。
7.数据紧凑存储,可以获得更快的索引和更快的全表扫描性能。

存储引擎的选择:

设计阶段我们选用InnoDB存储引擎作为数据的存储模式,使用事务、且并发性高,支持外键,支持外键索引。

1.3 字符集选择


字符编码采用utf-8
字符校验采用utf-8-cgi

1.4 命名约定


规范的必要性P187

1.年前bug就是因为没有建立索引导致的一系列Bug,所以建立规范,刻不容缓。
2.命名没有强制约定,但在一个应用中建议风格统一。

命名约定

1.命名有意义,一眼知道这张表是干什么用的
2.数据库,表都用小写
   数据库形如:backend
   数据表形如:client_device_info(客户端设备信息),不要缩写,字母全小写
3.索引命名以idx_为前缀
4.命名不要过长(应尽量少于25字符)
5.不要使用保留字
6.同一字段在不同的表中也应是相同的类型和长度
7.同一数据库下有不同的模块,可以考虑对表名用不同的前缀标识
8.备份表时加上时间标识

1.5 索引设计


直接参考MySQL 数据库索引使用场景&注意事项

1.6 数据表设计与规划


表设计

1.如果没有特殊情况,建议选择InooDB索引
2.每个表都应该有主键,可选择自增字段,或整型字段。例外情况,一些应用会频繁的基于某些字段进行检索,设计人员可能认为这些字段/
  字组合更适合做主键,因为更自然、更高效。
3.(不做强制要求)尽量将字段设置为NOT NULL。因为NULL值的存储需要额外的空间,且会导致比较运算更为复杂,会使得优化器更难以
  优化sql。null 值虽然会导致比较运算更加复杂,但这比因此定义了not null带来应用逻辑异要好。
4.使用更短小的列,比如整型列。整型列的执行速度往往更快。
5.存储精确浮点数必须使用DECIMAL代替float和double。
6.建议使用unsigned类型存储非负值
7.建议使用 int unsigned存储ipv4
8.整型定义中不添加显示长度的值,使用int,而不是int(4)
9.尽可能不要使用text,blob类型
10.varchar(n) n表示字符数而不是字节数,比如varchar(255)最大可存储255个汉字,需根据实际字符长度选择n的值。
11.字符集建议选择utf-8
12.存储年时使用year类型
13.存储日期时使用date类型
14.存储时间时,建议使用timestamp类型,因为timestamp使用的是4字节,datetime使用的是8字节。
15.不要在数据库中使用varbinary或blob存储图片及文件,mysql 并不适合大量存储这类型文件
16.join 操作的字段,在不同表中的类型及命名要一致
17.如果更改表结构会影响性能,需要我司后台(有DBA尽可能找DBA)进行联合评审。

数据表规划

 查看数据表大小的脚本
 select sum(data_length+index_length) from information_schema.tables where table_schema = ‘app_backend’ and table_name = ‘client_device_info’;
其中data_length是记录大总大小,index_length 为索引的大小,table_schema 是数据库名
table_name 是数据表名。

1.7 慎用外键


外键的使用

1.外键的优点:
外键约束使得程序员更不容易将不一致性引入数据库,而且设计合适外键有助于以文档方式记录表间关系。
2.外键的缺点
但这些优点是以服务器为执行必要的检查而花费额外的开销为代价的。服务器进行额外的检查会影响性能。
其次外键对并发性能的影响很大,因每次修改数据都需要去另外一个表检查数据,需要获取额外的锁(以确保事务完成之前,父表的记录不
会被删除)高并发环境下出现性能问题,更好的办法是在应用层实现外键约束。

4.福利彩蛋

职位:腾讯OMG 广告后台高级开发工程师;
Base:深圳;
场景:海量数据,To B,To C,场景极具挑战性。
基础要求:
熟悉常用数据结构与算法;
熟悉常用网络协议,熟悉网络编程;
熟悉操作系统,有线上排查问题经验;
熟悉MySQL,oracle;
熟悉JAVA,GoLang,c++其中一种语言均可;
可内推,欢迎各位优秀开发道友私信[微笑]
期待关注我的开发小哥哥,小姐姐们私信我,机会很好,平台对标抖音,广告生态平台,类似Facebook 广告平台,希望你们用简历砸我~
联系方式 微信 13609184526

博客搬家:大坤的个人博客
欢迎评论哦~

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
1天前
|
SQL 存储 关系型数据库
MySQL 示例数据库大全
我们练习 SQL 时,总会自己创造一些测试数据或者网上找些案例来学习,其实 MySQL 官方提供了好几个示例数据库,在 MySQL 的学习、开发和实践中具有非常重要的作用,能够帮助初学者更好地理解和应用 MySQL 的各种功能和特性,特别是练习 SQL 的好帮手。
20 0
|
1天前
|
SQL 关系型数据库 MySQL
MYSQL————DDL方法使用(包含在数据库,以及具体数据库表格的一些操纵)
MYSQL————DDL方法使用(包含在数据库,以及具体数据库表格的一些操纵)
|
2天前
|
SQL 关系型数据库 MySQL
mysqldiff - Golang 针对 MySQL 数据库表结构的差异 SQL 工具
Golang 针对 MySQL 数据库表结构的差异 SQL 工具。https://github.com/camry/mysqldiff
33 7
|
2天前
|
SQL 关系型数据库 MySQL
MySQL中如何查看所有数据库的名称?
【6月更文挑战第12天】MySQL中如何查看所有数据库的名称?
13 3
|
3天前
|
关系型数据库 数据管理 数据库
数据管理DMS产品使用合集之如何极速恢复RDS(关系型数据库服务)中的数据表
阿里云数据管理DMS提供了全面的数据管理、数据库运维、数据安全、数据迁移与同步等功能,助力企业高效、安全地进行数据库管理和运维工作。以下是DMS产品使用合集的详细介绍。
|
3天前
|
NoSQL 关系型数据库 Serverless
Serverless 应用引擎产品使用合集之连接RDS、Redis等数据库时,是否需要通过安全组来控制访问权限
阿里云Serverless 应用引擎(SAE)提供了完整的微服务应用生命周期管理能力,包括应用部署、服务治理、开发运维、资源管理等功能,并通过扩展功能支持多环境管理、API Gateway、事件驱动等高级应用场景,帮助企业快速构建、部署、运维和扩展微服务架构,实现Serverless化的应用部署与运维模式。以下是对SAE产品使用合集的概述,包括应用管理、服务治理、开发运维、资源管理等方面。
|
4天前
|
SQL 关系型数据库 MySQL
Python 操作 MySQL 数据库
Python 操作 MySQL 数据库
|
4天前
|
DataWorks 监控 关系型数据库
DataWorks产品使用合集之在 DataWorks 中删除了 la_saas_rds 数据库,为什么其他数据库的同步任务无法打开或添加新表同步
DataWorks作为一站式的数据开发与治理平台,提供了从数据采集、清洗、开发、调度、服务化、质量监控到安全管理的全套解决方案,帮助企业构建高效、规范、安全的大数据处理体系。以下是对DataWorks产品使用合集的概述,涵盖数据处理的各个环节。
|
5天前
|
DataWorks API 调度
DataWorks产品使用合集之在调度配置配置了节点的上游节点输出,没办法自动生成这个flow的依赖,该怎么操作
DataWorks作为一站式的数据开发与治理平台,提供了从数据采集、清洗、开发、调度、服务化、质量监控到安全管理的全套解决方案,帮助企业构建高效、规范、安全的大数据处理体系。以下是对DataWorks产品使用合集的概述,涵盖数据处理的各个环节。
|
10天前
|
Prometheus 监控 关系型数据库
数据库同步革命:MySQL GTID模式下主从配置的全面解析
数据库同步革命:MySQL GTID模式下主从配置的全面解析
29 0