Apache npm安装 Java程序员 datetime count angular ui router 华为路由器ipv6配置 mysql数据库名称 mysql转字符串 vim跳到文件末尾 input边框颜色 ai如何导出矢量图 kali重启网卡 数据库查询 python写脚本 python获取输入 java的泛型 java自学编程入门教程 java实现栈 java基础框架 javaabstract java操作数据库 java如何编写接口 sp5 m4a转mp3格式转换器 忧思华光玉 groupy 电脑密码查看器 迅雷去广告版 wow怎么赚钱 迅雷会员共享账号 1667 mysql时间比较 python数组赋值 国都证券官网下载 cdr字体变形 极限防守图 编程电子书 思源黑体 无线网改密码
当前位置: 首页 > 学习教程  > 编程语言

高性能MySQL学习——高性能数据库表设计

2020/11/24 9:54:40 文章标签: 测试文章如有侵权请发送至邮箱809451989@qq.com投诉后文章立即删除

高性能MySQL学习——高性能数据库表设计高性能数据库表设计范式与反范式MySQL 使用原则和设计规范总结回顾高性能数据库表设计 范式与反范式 优秀的库表设计是高性能数据库的基础。范式是基础规范,反范式是针对性设计。 范式 范式是关系数据库理论的基础&#x…

高性能MySQL学习——高性能数据库表设计

  • 高性能数据库表设计
    • 范式与反范式
    • MySQL 使用原则和设计规范
    • 总结回顾

高性能数据库表设计

范式与反范式

优秀的库表设计是高性能数据库的基础。范式是基础规范,反范式是针对性设计。

范式

范式是关系数据库理论的基础,也是我们在设计数据库结构过程中所要遵循的规则和指导方法。数据库的设计范式是数据库设计所需要满足的规范。只有理解数据库的设计范式,才能设计出高效率、优雅的数据库,否则可能会设计出低效的库表结构。

目前关系数据库有六种范式:第一范式(1NF)、第二范式(2NF)、第三范式(3NF)、巴斯-科德范式(BCNF)、第四范式(4NF)和第五范式(5NF,还又称完美范式)。

满足最低要求的叫第一范式,简称 1NF。在第一范式基础上进一步满足一些要求的为第二范式,简称 2NF。其余依此类推。各种范式呈递次规范,越高的范式数据库冗余越小。通常所用到的只是前三个范式,即:第一范式(1NF),第二范式(2NF),第三范式(3NF)。

第一范式

第一范式无重复的列,表中的每一列都是拆分的基本数据项,即列不能够再拆分成其他几列,强调的是列的原子性.。

如果在实际场景中,一个联系人有家庭电话和公司电话,那么以“姓名、性别、电话”为表头的表结构就没有达到 1NF。要符合 1NF 只需把电话列拆分,让表头变为姓名、性别、家庭电话、公司电话即可。

第二范式

第二范式属性完全依赖于主键,首先要满足它符合 1NF,另外还需要包含两部分内容:

  • 表必须有一个主键;
  • 没有包含在主键中的列必须完全依赖于主键,而不能只依赖于主键的一部分。即要求实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性。

第三范式

第三范式属性不传递依赖于其他非主属性,首先需要满足 2NF,另外非主键列必须直接依赖于主键,不能存在传递依赖。即不能存在:非主键列 A 依赖于非主键列 B,非主键列 B 依赖于主键的情况。

第二范式和第三范式的区别

  • 第二范式:非主键列是否依赖主键(包括一列通过某一列间接依赖主键),要是有依赖关系就是第二范式;
  • 第三范式:非主键列是否直接依赖主键,不能是那种通过传递关系的依赖。要是符合这种依赖关系就是第三范式。

通过对前三个范式的了解, 3NF 是 2NF 的子集,2NF 是 1NF 的子集。

设计符合 2NF 的表

接下来以订单信息表为例,讲述如何设计一个符合 2NF 的表,首先,我们看原始的订单信息表,如下图所示。
在这里插入图片描述

图中,以订单编号和商品编号作为联合主键,商品名称、单位、价格等信息不与主键相关,只与编号相关,违反了第二范式。 应该对订单信息表进行拆分,商品信息单独一张表,订单项目一张表,如下所示,拆分分成 3 张表。

  • 包含客户信息的订单信息表;
  • 包含商品详情的商品信息表;
  • 包含订单详情的订单详情表。

范式优缺点

经过前面的讲解和案例分析可知范式具备以下优点:

  • 避免数据冗余,减少维护数据完整性的麻烦;
  • 减少数据库的空间;
  • 数据变更速度快。

同时,也有如下缺点:

  • 按照范式的规范设计的表,等级越高的范式设计出来的表数量越多。
  • 获取数据时,表关联过多,性能较差。

表的数量越多,查询所需要的时间越多。也就是说所用的范式越高,对数据操作的性能越低。

反范式

范式是普适的规则,满足大多数的业务场景的需求。对于一些特殊的业务场景,范式设计的表,无法满足性能的需求。此时,就需要根据业务场景,在范式的基础之上进行灵活设计,也就是反范式设计。

反范式设计主要从三方面考虑:

  • 业务场景;
  • 相应时间;
  • 字段冗余。

反范式设计就是用空间来换取时间,提高业务场景的响应时间,减少多表关联。主要的优点如下。

  • 允许适当的数据冗余,业务场景中需要的数据几乎都可以在一张表上显示,避免关联;
  • 可以设计有效的索引。

范式与反范式异同

范式化模型:

  • 数据没有冗余,更新容易;
  • 当表的数量比较多,查询数据需要多表关联时,会导致查询性能低下。

反范式化模型:

  • 冗余将带来很好的读取性能,因为不需要 join 很多表;
  • 虽然需要维护冗余数据,但是对磁盘空间的消耗是可以接受的。

MySQL 使用原则和设计规范

讲完范式,接下来我们看看 MySQL 使用中的一些使用原则和设计规范。

MySQL 虽然具有很多特性并提供了很多功能,但是有些特性会严重影响它的性能,比如,在数据库里进行计算,写大事务、大 SQL、存储大字段等。

想要发挥 MySQL 的最佳性能,需要遵循 3 个基本使用原则。

  1. 首先是需要让 MySQL 回归存储的基本职能:MySQL数据库只用于数据的存储,不进行数据的复杂计算,不承载业务逻辑,确保存储和计算分离;
  2. 其次是查询数据时,尽量单表查询,减少跨库查询和多表关联;
  3. 还有就是要杜绝大事务、大 SQL、大批量、大字段等一系列性能杀手。
  • 大事务,运行步骤较多,涉及的表和字段较多,容易造成资源的争抢,甚至形成死锁。一旦事务回滚,会导致资源占用时间过长。
  • 大 SQL,复杂的 SQL 意味着过多的表的关联,MySQL 数据库处理关联超过 3 张表以上的 SQL 时,占用资源多,性能低下。
  • 大批量,意味着多条 SQL 一次性执行完成,必须确保进行充分的测试,并且在业务低峰时段或者非业务时段执行。
  • 大字段,blob、text 等大字段,尽量少用。必须要用时,尽量与主业务表分离,减少对这类字段的检索和更新。

下面具体讲解数据库的基本设置规则:

  1. 必须指定默认存储引擎为 InnoDB,并且禁用 MyISAM 存储引擎,随着 MySQL 8.0版本的发布,所有的数据字典表都已经转换成了 InnoDB,MyISAM 存储引擎已成为了历史。
  2. 默认字符集 UTF8mb4,以前版本的 UTF8 是 UTF8mb3,未包含个别特殊字符,新版本的 UTF8mb4包含所有字符,官方强烈建议使用此字符集。
  3. 关闭区分大小写功能。设置 lower_case_tables_name=1,即可关闭区分大小写功能,即大写字母 T 和小写字母 t一样。

这里在实践中有个小问题,如何让系统中区分大小写的库表转换为不区分大小写的库表呢?因为要修改底层数据,还是比较麻烦的,操作步骤如下。

  1. MySQL dump 导出数据库。
  2. 修改参数 lower_case_tables_name=1。
  3. 导入备份数据时,必须停止数据库,停止业务,影响非常大。
  4. 开启 per-table 表空间,开启后,每张业务表会单独创建一个独立于系统表空间的表空间,便于空间的回收,数据的迁移。

MySQL 数据库提供的功能很全面,但并不是所有的功能性能都高效。

  1. 存储过程、触发器、视图、event。为了存储计算分离,这类功能尽量在程序中实现。这些功能非常不完整,调试、排错、监控都非常困难,相关数据字典也不完善,存在潜在的风险。一般在生产数据库中,禁止使用。
  2. lob、text、enum、set。这些字段类型,在 MySQL数据库的检索性能不高,很难使用索引进行优化。如果必须使用这些功能,一般采取特殊的结构设计,或者与程序结合使用其他的字段类型替代。比如:set可以使用整型(0,1,2,3)、注释功能和程序的检查功能集合替代。

以上是基础规范的内容,但并不是全部,只是以点带面,进行粗略的介绍。下面我们开始讲解命名规范,统一的规范命名,可以增加可读性,减少隐式转换。

规范命名

命名规范如下,命名时的字符取值范围为:az,09 和 _(下画线)。

  1. 所有表名小写,不允许驼峰式命名;
  2. 允许使用 -(横线)和 (空格);如下图所示,当使用 -(横线),后台默认会转化成 @002d;
  3. 不允许使用其他特殊字符作为名称,减少潜在风险。
    在这里插入图片描述

数据库库名的命名规则必须遵循“见名知意”的原则,即库名规则为“数据库类型代码 + 项目简称 + 识别代码 + 序号”。

这样包含了更多的业务信息,比如:

  • 出入系统业务生产库:AOCT、AOCT1、AOCT2;
  • 出入系统业务开发库:AOCTDEV、AOCTDEV1、AOCTDEV2;
  • 出入系统业务测试库:AOCTTEST、AOCTTEST1、AOCTTEST2;
  • 只有一个数据库,则不加序号,否则末尾增加序号;
  • 生产库不加识别代码,否则需要增加识别代码 DEV 或 TEST;
  • 如果只作历史库,则只需要项目简称 +H+ 序号;
  • 图例为常用的识别代码。

表名的命名规则分为:

  1. 单表仅使用 a~z、_;
  2. 分表名称为“表名_编号”;
  3. 业务表名代表用途、内容:子系统简称_业务含义_后缀。

常见业务表类型有:

  • 临时表,tmp;
  • 备份表,bak;
  • 字典表,dic;
  • 日志表,log。

字段名精确,遵循“见名知意”的原则,格式:名称_后缀。

  • 避免普遍简单、有歧义的名称。

用户表中,用户名的字段为 UserName 比 Name 更好。

  • 布尔型的字段,以助动词(has/is)开头。

用户是否有留言 hasmessage,用户是否通过检查 ischecked 等。

常见后缀如下:

  • 流水号/无意义主键,后缀为 id,比如 task_id;
  • 时间,后缀为 time,insert_time。

程序账号与数据库名称保持一致。如果所有的程序账号都是 root@‘%’,密码也一样,很容易错连到其他的数据库,造成误操作。

索引命名格式,主要为了区分哪些对象是索引:

  • 前缀_表名(或缩写)_字段名(或缩写);
  • 主键必须使用前缀“pk_”;
  • UNIQUE 约束必须使用前缀“uk_”;
  • 普通索引必须使用前缀“idx_”。

数据库规范库表字段的命名,能够提高数据库的易读性,为数据库表设计打下基础。下面我们具体看看表设计的一些规则。

  • 显式指定需要的属性;

创建表时显示指定字符集、存储引擎、注释信息等。

  • 不同系统之间,统一规范;

不同表之间的相同字段或者关联字段,字段类型/命名要保持一致;库表字符集和前端程序、中间件必须保持一致的 UTF8mb4。

InnoDB 表的注意事项

  1. 主键列,UNSIGNED 整数,使用 auto_increment;禁止手动更新auto_increment,可以删除。
  2. 必须添加 comment 注释。
  3. 必须显示指定的 engine。
  4. 表必备三字段:id、 xxx_create、 xxx_modified。
  5. id 为主键,类型为 unsigned bigint 等数字类型;
  6. xxx_create、xxx_modified 的类型均为 datetime 类型,分别记录该条数据的创建时间、修改时间。

备份表/临时表等常见表的设计规范

备份表/临时表等常见表的设计规范如下。

  1. 备份表,表名必须添加 bak 和日期,主要用于系统版本上线时,存储原始数据,上线完成后,必须及时删除。
  2. 临时表,用于存储中间业务数据,定期优化,及时降低表碎片。
  3. 日志类表,首先考虑不入库,保存成文件,其次如果入库,明确其生命周期,保留业务需求的数据,定期清理。
  4. 大字段表,把主键字段和大字段,单独拆分成表,并且保持与主表主键同步,尽量减少大字段的检索和更新。
  5. 大表,根据业务需求,从垂直和水平两个维度进行拆分。

垂直拆分:按列关联度。

水平拆分:

  • 按照时间、地域、范围等;
  • 冷热数据(历史数据归档)。

字段设计要求

  1. 根据业务场景需求,选择合适的类型,最短的长度;确保字段的宽度足够用,但也不要过宽。所有字段必须为 NOT NULL,空值则指定default 值,空值难以优化,查询效率低。比如:人的年龄用 unsigned tinyint(范围 0~255,人的寿命不会超过255 岁);海龟就必须是 smallint,但如果是太阳的年龄,就必须是 int;如果是所有恒星的年龄都加起来,那么就必须使用bigint。
  2. 表字段数少而精,尽量不加冗余列。
  3. 单实例表个数必须控制在 2000 个以内。
  4. 单表分表个数必须控制在 1024 个以内。
  5. 单表字段数上限控制在 20~50 个。

禁用 ENUM、SET 类型。

  • 兼容性不好,性能差。

解决方案:使用 TINYINT,在 COMMENT 信息中标明被枚举的含义。is_disable TINYINT UNSIGNED DEFAULT ‘0’ COMMENT '0:启用 1:禁用 2:异常’。

禁用列为 NULL。

  • MySQL 难以优化 NULL 列;
  • NULL 列加索引,需要额外空间;
  • 含 NULL 复合索引无效。

解决方案:在列上添加 NOT NULL DEFAULT 缺省值。

禁止 VARBINARY、BLOB 存储图片、文件等。

  • 禁止在数据库中存储大文件,例如照片,可以将大文件存储在对象存储系统中,数据库中存储路径。

不建议使用 TEXT/BLOB:

  • 处理性能差;
  • 行长度变长;
  • 全表扫描代价大。

解决方案:拆分成单独的表。

存储字节越小,占用空间越小。尽量选择合适的整型,如下图所示。
在这里插入图片描述

  1. 主键列,无负数,建议使用 INT UNSIGNED 或者 BIGINT UNSIGNED;预估字段数字取值会超过 42 亿,使用BIGINT 类型。
  2. 短数据使用 TINYINT 或 SMALLINT,比如:人类年龄,城市代码。
  3. 使用 UNSIGNED 存储非负数值,扩大正数的范围。

int(3)/int(5) 区别

int(3)/int(5) 的区别,如下图所示。
在这里插入图片描述

  • 正常显示没有区别。
  • 3 和 5 仅是最小显示宽度而已。
  • 有 zerofill 等扩展属性时则显示有区别。

浮点数与定点数区别

浮点数与定点数区别,如下图所示。
在这里插入图片描述

  1. 浮点数:float、double(或 real)。
  2. 定点数:decimal(或 numberic)。

从上图中可以观察到:

  • 浮点数存在误差问题;
  • 尽量避免进行浮点数比较;
  • 对货币等对精度敏感的数据,应该使用定点数。

N 解释

字符集都为 UTF8mb4,中文存储占三个字节,而数据或字母,则只占一个字节。

下面看一下字符类型中 N 的解释。

  • CHAR(N) 和 VARCHAR(N) 的长度 N,不是字节数,是字符数。
  • username 列可以存多少个汉字,占用多少个字节
  • username 最多能存储 40 个字符,占用 120 个字节。

Char 与 Varchar 类型

存储字符串长度相同的全部使用 Char 类型;字符长度不相同的使用 Varchar 类型,不预先分配存储空间,长度不要超过 255。

Char 和 Varchar 占用空间的对比,如下图所示。
在这里插入图片描述

Varchar 值存储为 1 字节或 2 字节长度前缀加数据。如果值不超过 255 个字节,则列使用一个字节长度;如果值可能需要超过 255 个字节,则列使用两个字节长度。

为什么超过 255 个字节时,必须使用两个字节长度。

  • 2^8=256,1 个字节是 8 位;
  • 2^16=65535,2 个字节是 16 位。

案例

学习了范式和反范式、命名规则、表设计规范,下面通过几个具体的案例,进一步巩固这些知识。

IP 处理

  1. 一般使用 Char(15) 进行存储,但是当进行查找和统计时,字符类型不是很高效。
  2. MySQL 数据库内置了两个 IP 相关的函数 INET_ATON()、INET_NTOA(),可以实现 IP 地址和整数的项目转换。

因此,我们使用 INT UNSIGNED(占用 4 个字节)存储 IP,非 Char(15)。占 15 个字节。

下图所示,IP:192.168.0.1 与整数之间的转换。
在这里插入图片描述

将 IP 的存储从字符型转换成整形,转化后数字是连续的,提高了查询性能,使查询更快,占用空间更小。

TIMESTAMP 处理

同样的方法,我们使用 MySQL 内置的函数(FROM_UNIXTIME(),UNIX_TIMESTAMP()),可以将日期转化为数字,用 INT UNSIGNED 存储日期和时间。

下图示所示,时间 2007-11-30 10:30:19 与整数之间的转换,转化后数字是连续的,占用空间更小,并且可以使用索引提升查询性能。
在这里插入图片描述

本案例展示的是,不当的数字类型,导致表无法插入新数据,如下图所示。
在这里插入图片描述

当我们使用 load data 进行批量加载数据时,会导致 1467 错误。根据分析,导致 1467 错误是由于 auto_increment 的值,超过了 int 类型的取值范围。

原因分析部分显示,max(seq_id) 为 2147477751,而 int 的范围为 -2147483648~ 2147483647,还剩余空间 5896,而程序需要导入 1 万行,所以报错。

解决办法

将 int 改为 bigint 或者将数据分表。

表大小及使用频率

设计表时,必须考虑表的大小和使用频率,避免由于取值范围过小,导致程序运行失败。

对于 InnoDB 表,要求创建一个与业务无关的主键,比如:每张表以 id 列为主键。但是 id 列非常常见,完全无法表达更深层次的意思,特别是在做两张表的联合查询时,它们都有相同的 id 主键的情况下。

如果你的程序用的是列名,如何区分 Accounts 表的 id 和 Bugs 的 id,如下图所示,列名 id 并不会使查询变得更加清晰。但如果列名叫作 bug_id 或者 account_id,事情就会变得更加简单。
在这里插入图片描述

我们使用主键来定位唯一一条记录,因此主键的列名就应该更加便于理解,如下图所示。
在这里插入图片描述

  1. 在缺陷跟踪数据库中,我们使用 Products 表中的 product_id 主键列来关联产品和对应的联系人。每个账号可能对应很多产品,每个产品又引用了一个联系人,因此产品和帐号之间是多对一的关系
  2. 随着项目日趋成熟,一个产品可能会有多个联系人,除了多对一的关系外,还需要支持产品到账号的一对多的关系。Products表中的一行数据必须要存储多个联系人。
  3. 为了把数据库表结构的改动控制在最小范围内,我们决定将 account_id 的类型修改为 Varchar,这样可以在该列中存储多个账号id,每个账号 id 之间用逗号分隔。
  4. 这样的设计似乎是可行的,没有创建额外的表和列,仅仅改变了一个字段的数据类型。然而,我们来看看这样的设计所必须承受的性能和数据完整性问题。所有外键都合并在一个单元格内,查询会变成异常困难。只能通过正则表达式进行模糊匹配,不但可能会返回错误的结果,而且无法使用索引提高性能。例如:查询指定产品的账号时,联合两张表将不能使用任何索引。这样的查询必然会对两张表进行全表扫描,并创建一个交叉结果集,然后使用正则表达式遍历每一行联合后的数据进行匹配。
  5. 出于性能优化方面的考虑,可能在数据库的结果中需要使用反范式的设计。上述 Products表中将列表存储为以逗号分隔的字符串,就是反范式的一个实例。这个设计只是简化了存储,但是性能低下。因此你需要谨慎使用反范式的数据库设计。尽可能地使用规范化的数据库设计。
  6. 根据业务需求,我们如何设计合理的反范式,解决方案是:创建一个交叉表。将 account_id 存储在一张单独的表中,而不是存储在Products 表中,从而确保每个独立的 account 值都可以占据一行。

这张新表 Contacts,实现了 Products 和 Accounts 的多对多关系。当一张表有指向两张表的外键时,称这种表为交叉表,它实现了两张表之间的多对多关系。这意味着每个产品都可以通过交叉表和多个账号关联;同样地,一个账号也可以通过交叉表和多个产品关联。当我们“查询指定产品的账号”时,就可以直接使用下面的联合查询语句高效实现。

总结回顾

  1. 以高性能为目标,库表设计以范式为主,根据特殊业务场景使用反范式,允许必要的空间换时间。
  2. 规范数据库的使用原则,统一规范命名,减少性能隐患,减少隐式转换。
  3. 高性能表设计的原则:合适的字段、合适的长度、NOT NULL。
  4. 从不同角度思考 IP、timestamp 的转换,拓宽设计思路。
  5. 规范的命名可提高可读性,反范式设计可提高查询性能。

本文链接: http://www.dtmao.cc/news_show_400170.shtml

附件下载

相关教程

    暂无相关的数据...

共有条评论 网友评论

验证码: 看不清楚?