高性能MySQL实战(一):表结构

大家好,我是 方圆。最近因需求改动新增了一些数据库表,但是在定义表结构时,具体列属性的选择有些不知其所以然,索引的添加也有遗漏和不规范的地方,所以我打算为创建一个高性能表的过程以实战的形式写一个专题,以此来学习和巩固这些知识。原文还是收录在我的 Github: enthusiasm 中,欢迎Star和获取原文。

1. 实战

我使用的 MySQL 版本是 5.7,建表 DDL 语句如下所示:根据需求创建 接口调用日志 数据库表,请大家浏览具体字段的属性信息,它们有不少能够优化的点。

CREATE TABLE `service_log` (`id` bigint(100) NOT NULL AUTO_INCREMENT COMMENT '主键',`service_type` int(10) DEFAULT NULL COMMENT '接口类型',`service_name` varchar(30) DEFAULT NULL COMMENT '接口名称',`service_method` varchar(10) DEFAULT NULL COMMENT '接口方式',`serial_no` int(10) DEFAULT NULL COMMENT '消息序号',`service_caller` varchar(15) DEFAULT NULL COMMENT '调用方',`service_receiver` varchar(15) DEFAULT NULL COMMENT '接收方',`status` int(3) DEFAULT '10' COMMENT '状态 10-成功 20-异常',`error_message` varchar(200) DEFAULT NULL COMMENT '异常信息',`message` text DEFAULT NULL COMMENT '报文内容',`create_user` varchar(50) DEFAULT NULL COMMENT '创建者',`create_time` datetime NOT NULL COMMENT '创建时间',`update_user` varchar(50) DEFAULT NULL COMMENT '更新者',`update_time` datetime NOT NULL COMMENT '更新时间',`is_delete` tinyint(1) NOT NULL DEFAULT '0' COMMENT '刪除标志',`ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '时间戳',PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='接口调用日志';

我会在下文中将其中包含的问题和可以进行优化的地方一一进行解释,主要参考的书目是《高性能MySQL 第四版》,也希望大家有精力去看原书。

2. 优化和改进

慷慨不是明智的

一般来说,要尽量使用能够正确存储和表示数据的最小数据类型,更小的数据类型通常更快,因为它们占用的磁盘、内存和CPU缓存的空间更少,并且处理时需要的CPU周期也更少。但是,这也要确保没有低估需要存储的值的范围,否则会因入库失败而造成数据丢失,而且表结构修改的流程审批也很麻烦。

我们以表中 idmessage 列为例来说:

id 为主键列,它使用的是整数类型 BIGINT(64位),除此之外还有 TINYINT(8位)、SMALLINT(16位)、MEDIUMINT(24位) 和 INT(32位),可以存储的取值范围是从 -2(N - 1) 到 2(N - 1) - 1,所以 BIGINT 类型值的最大值是9223372036854775808(19位数)。

显然,主键定义100位宽度是有些“无脑的”,而且也是没有意义的:因为 它不会限制值的合法范围,即使是定义了 BIGINT(100) 也没办法存储宽度为100的数字,实际上定义 BIGINT(1) 和 BIGINT(20) 的 存储空间是相同的,宽度的定义只是规定了 MySQL 的一些交互工具(MySQL命令行客户端)用来显示字符的个数。

整数类型有可选的 UNSIGNED 属性,它表示不允许负值,这大约能使正整数的上限提高一倍。例如 TINYINT UNSIGNED 可以存储的值范围是 0 ~ 255,而 TINYINT 的值的存储范围是 -128 ~ 127。我们的ID列是从0开始递增的,所以可以选用这个属性。

那么,我们应该对 id 列的定义如下所示:

`id` bigint UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键'

message 列保存的是接口交互报文内容,定义的类型是 TEXT,它还有一些相关的类型,具体如下(L代表字符串的字节长度,数字表示存储字符串字节长度的字节数):

Data TypeStorage Required(Bytes)
TINYTEXTL + 1, L < 28
TEXTL + 2, L < 216
MEDIUMTEXTL + 3, L < 224
LONGTEXTL + 4, L < 232

若报文内容中每个字符只占用1字节的话,那么 TEXT 类型能最多存储大约 65535 个字符,而实际上报文内容远远达不到这个长度,而且 TEXT 类型是为了存储很大的数据而设计的字符串数据类型。

我们可以将其调整成 VARCHAR 类型,并根据实际的报文长度都不超过 1000 来指定它的字符数为 1000,避免发生因报文长度过长而无法保存数据的情况。通常情况下MySQL会在内容分配固定大小的内存来保存值,我们这样做节省了存储空间,对性能也有帮助。

message 的更改后的定义如下所示:

`message` varchar(1000) DEFAULT NULL COMMENT '报文内容'

VARCHAR 类型也需要额外使用 1 或 2 字节来记录字符串字节的长度:如果列的最大长度小于或等于 255 字节,则只使用 1 字节来表示;否则使用 2 字节来表示。

MySQL 字符串长度定义的不是字节数,而是字符数。像 UTF-8 这样复杂的字符集可能需要多个字节来存储一个字符。

更小的通常更好

MySQL 总是为 CHAR 类型分配所定义长度的空间,所以它是固定长度的,它相比于 VARCHAR 在面对经常修改的数据时表现更好,因为固定长度的列不容易出现内存碎片,而且对于 CHAR(1) 这种非常短的列,它要比 VARCHAR(1) 更高效,因为前者只占用 1 个字节的空间,后者占用 2 个字节(其中 1 字节记录长度)。

CHAR 类型适合存储非常短的字符串或者所有值长度都几乎相同的字符串,不过需要注意的是,MySQL 会将所有 尾随的空格移除

service_method 字段实际上保存的是接口协议,无非是 HTTP 和 TCP 这两种,我们可以将其定义修改为如下所示:

`service_method` char(4) DEFAULT NULL COMMENT '接口方式'

但是实际上,整型数据比字符数据的比较操作代价更低,如果在允许改变字段类型的情况下,我们将其修改为 TINYINT 类型,通过定义枚举值来表示不同的协议效率会更高。

`service_method` tinyint DEFAULT NULL COMMENT '接口方式 1-HTTP 2-TCP'

service_callerservice_receiver 字段也是一样的道理,这些值都是固定的枚举,最初应该也定义成 TINYINT 的形式,如下

`service_caller` tinyint DEFAULT NULL COMMENT '调用方',
`service_receiver` tinyint DEFAULT NULL COMMENT '接收方'

service_type 字段中存储的是对应接口的编码值,它们都是宽度为 4 的整型数据,最大值不会超过 9999,所以根据它的取值范围将其修改为 SMALLINT 类型会更合适,如下

`service_type` smallint DEFAULT NULL COMMENT '接口类型'

service_name 字段接口名称最长也不会超过15个字符,所以我们将它的 VARCHAR 定义字符长度修改一下:

`service_name` varchar(15) DEFAULT NULL COMMENT '接口名称'

status 字段只有 10 和 20 两种值,相比于 INT,使用 TINYINT 更合适一些

`status` tinyint DEFAULT 10 COMMENT '状态 10-成功 20-异常'

DATETIME 和 TIMESTAMP

这两种类型非常相似,对于大多数系统来说,这两种类型都可以,不过它们也有所不同。

DATETIME 可以保存的日期范围更大,从 1000 年到 9999 年,精度为 1 微秒,非小数部分 占用 5 个字节的存储空间,小数部分根据精度大小占用 0 ~ 3 个字节,并且它 与时区无关。默认情况下,MySQL 以 yyyy-MM-dd HH:mm:ss 的格式显示时间,如果需要指定精度,可以以 datetime(6) 的形式定义。

TIMESTAMP 类型存储的是自 1970 年 1 月 1 日格林尼治标准时间以来的秒数(精度也为 1 微秒),非小数部分占用 4 个字节的存储空间,小数部分与 DATETIME 类型占用空间规则一致,所以它的取值范围相比于 DATETIME 要小,只能表示从 1970 年到 2038 年 1 月 19 日的时间范围。而且该类型与MySQL服务指定的 时区相关,这就使得在查询日期时,会将时间戳转换为所在时区的时间后再显示,所以不同地区看到的同一时间戳的实际时间展示是不一样的。

MySQL 可以使用 FROM_UNIXTIME() 函数将 UNIX 时间戳转换成日期,使用 UNIX_TIMESTAMP() 函数将日期转换为 UNIX 时间戳。

使用 DATETIME 类型还是使用 TIMESTAMP 类型需要考虑以下问题:

  • 存储空间对我们来说重要吗?

  • 需要支持前后多大时间范围的日期和时间?

  • 保存的日期数据有精度要求吗?

  • 是在MySQL中处理时区还是在代码中处理时区?

拿我们的应用来说,DATETIME 类型会更合适一些:

`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
`ts` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '时间戳'

如果想要对时间戳进行记录,可以考虑使用 BIGINT 类型,它不会遇到 2038 年的问题。

避免使用 NULL

通常情况下,最好指定列为 NOT NULL,除非明确的需要存储为 NULL 值。可为 NULL 的列会使用更多的存储空间,在 MySQL 中需要特殊的处理;查询中包含可为 NULL 的列对 MySQL 来说更难优化,因为可为 NULL 的列使得索引、索引统计和值的比较更为复杂。

MySQL 默认的行格式为 DYNAMIC,它会在每行数据中记录额外信息,其中就包括对 NULL 值列表的记录,如果我们所有的列都为 NOT NULL 的话,那么这部分额外信息是不需要记录的。

了解:COMPRESSED 行格式与 DYNAMIC 不同的是,它会对存储数据的页进行压缩以节省空间;COMPACT 行格式与 DYNAMIC 和 COMPRESSED 不同的是在对溢出列的处理上,COMPACT 会存储溢出列的部分数据,剩余的数据使用其他数据页保存,并记录下保存这些数据页的指针,DYNAMIC 和 COMPRESSED 则是将该列所有数据都保存在其他数据页中,在该列数据处只保存对应溢出页的地址。

COMPACT行格式示意图.png

但是实际上将列的定义修改为 NOT NULL 带来的性能提升并不明显,所以并不会将这种优化作为首选,而是在表结构初始化时考虑到这一点。

修改好,最终初始化表结构的 DDL 语句如下:

CREATE TABLE `service_log` (`id` bigint UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',`service_type` smallint NOT NULL DEFAULT -1 COMMENT '接口类型',`service_name` varchar(30) DEFAULT '' COMMENT '接口名称',`service_method` tinyint NOT NULL DEFAULT -1 COMMENT '接口方式 1-HTTP 2-TCP',`serial_no` int DEFAULT -1 COMMENT '消息序号',`service_caller` tinyint DEFAULT -1 COMMENT '调用方',`service_receiver` tinyint DEFAULT -1 COMMENT '接收方',`status` tinyint DEFAULT 10 COMMENT '状态 10-成功 20-异常',`error_message` varchar(200) DEFAULT '' COMMENT '异常信息',`message` varchar(1000) DEFAULT '' COMMENT '报文内容',`create_user` varchar(50) DEFAULT '' COMMENT '创建者',`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',`update_user` varchar(50) DEFAULT '' COMMENT '更新者',`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',`is_delete` tinyint NOT NULL DEFAULT 0 COMMENT '刪除标志',`ts` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '时间戳',PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='接口调用日志';

TINYINT 表示 Boolean 类型

需要注意,Boolean 类型的值在 MySQL 中是通过 TINYINT 来映射的,如果在数据库中该值为 0,那么映射到 Java 对象中为 False,如下所示:

tyint.png


实数类型

实数类型因为在该表结构中使用不到我们没有介绍,所以在这里进行补充。

MySQL 既支持 精确计算 的类型(DECIMAL),也支持 近似计算 的浮点类型(FLOAT 和 DOUBLE)。

FLOAT 使用 4 个字节的存储空间,DOUBLE 使用 8 个字节的存储空间,可以指定列的精度,但是通常情况下建议 只指定数据类型,而不指定精度,否则 MySQL 会根据精度自行进行舍入,而且它们还会受到平台或实现依赖性的影响。

我们看下边这个例子:

CREATE TABLE `real_number` (`f1` float(7, 4) NOT NULL,`f2` float NOT NULL,`d1` double(7, 4) NOT NULL,`d2` double NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='实数';# 插入数据
INSERT into real_number values (3.1415926535,3.1415926535,3.1415926535,3.1415926535
);# 查询结果
select * from real_number;
f1f2d1d2
3.14163.141593.14163.1415926535

根据结果值我们可以发现,指定了精度的浮点类型进行了舍入,没有指定精度的 FLOAT 类型默认保留了小数点后 5 位小数,自行的舍入可能会引起混淆。

通常情况下,我们为了保证最大限度的实现 可移植性,需要存储近似数字数据值的代码应该使用 FLOAT 或 DOUBLE,而不指定精度或位数。

还有一种情况需要注意,如果我们要插入超过指定精度的整数范围,会导致数据入库失败,如下:

# 指定 f1 列整数宽度为 4,实际定义允许的最大宽度为 3
INSERT into real_number values (
3210.1415926535,
3.1415926535,
3.1415926535,
3.1415926535
);# 结果
SQL 错误 [1264] [22001]: Data truncation: Out of range value for column 'f1' at row 1

如果没有指定精度范围,那么则会对小数部分进行压缩,精度变小,而不是提示入库失败,如下:

# f2 列插入该值,查看结果
INSERT into real_number values (
3.1415926535,
3210.1415926535,
3.1415926535,
3.1415926535
);
f1f2d1d2
3.14163210.143.14163.1415926535

DECIMAL 与 FLOAT 和 DOUBLE 不同,在进行精确的小数计算时,需要指定它的精度,否则默认情况下为 DECIMAL(10, 0) ,只保存整数。而且它在存储相同范围的值是会占用更多的空间,所以出于对额外的空间需求和计算成本的考虑,我们只在需要对小数进行精确计算时才使用该类型。

DECIMAL 的最大位数为 65,而且当为 DECIMAL 列指定的值小数点后位数超过小数位数精度范围时,该值将舍入为精度范围。同样地,如果整数部分的宽度大于指定的精度范围,那么也会发生超出列范围的异常而导致无法正常入库,如下:

create table `decimal_t` (`d1` decimal(7, 4) NOT NULL
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='DECIMAL';INSERT INTO decimal_t values (3.1415926535);# 结果值为 3.1416INSERT INTO decimal_t values (1234.1415926535);# Data truncation: Out of range value for column 'd1' at row 1

除此之外,在一些大容量的场景下,可以考虑使用 BIGINT 代替 DECIMAL,在存储时根据小数的位数乘以相应的倍数即可。这样就可以同时避免浮点数计算不精确、 DECIMAL 精确计算代价高和数值精度范围限制的问题。


巨人的肩膀

  • 《高性能 MySQL 第四版》:第六章

  • 11.7 Data Type Storage Requirements

  • mysql的日期时间类型及精度问题

  • MySQL之DATETIME与TIMESTAMP的时间精度问题

  • 11.8 Choosing the Right Type for a Column

  • 11.1.4 Floating-Point Types (Approximate Value) - FLOAT, DOUBLE

  • B.3.4.8 Problems with Floating-Point Values

  • 《MySQL 是怎样运行的》:第四章

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处:http://www.rhkb.cn/news/78936.html

如若内容造成侵权/违法违规/事实不符,请联系长河编程网进行投诉反馈email:809451989@qq.com,一经查实,立即删除!

相关文章

Element-UI简介

目录 安装 常用组件 Container 布局容器 Button 按钮 MessageBox 弹框 Form 表单验证 element-ui是一个前端的ui框架&#xff0c;封装了很多已经写好的ui组件&#xff0c;例如表单组件&#xff0c;布局组件&#xff0c;表格组件.......是一套桌面端组件。 Element - 网站…

华为PMS API client token auth failed

对接华为pms时出现问题&#xff0c;提示华为PMS API client token auth failed 主要是权限的问题&#xff0c;创建项目的时候选择N/A

11. 使用tomcat中碰到的一些问题

文章目录 问题一&#xff1a;Tomcat的startup.bat启动后出现乱码问题二&#xff1a;一闪而退之端口占用问题三&#xff1a;非端口问题的一闪而退问题四&#xff1a;服务器的乱码和跨域问题问题五: 在tomcat\webapps\下创建文件夹为什么tomcat重启就会丢失问题六&#xff1a;Tom…

无脑入门pytorch系列(一)—— nn.embedding

本系列教程适用于没有任何pytorch的同学&#xff08;简单的python语法还是要的&#xff09;&#xff0c;从代码的表层出发挖掘代码的深层含义&#xff0c;理解具体的意思和内涵。pytorch的很多函数看着非常简单&#xff0c;但是其中包含了很多内容&#xff0c;不了解其中的意思…

Android中级——RemoteView

RemoteView RemoteView的应用NotificationWidgetPendingIntent RemoteViews内部机制模拟RemoteViews RemoteView的应用 Notification 如下开启一个系统的通知栏&#xff0c;点击后跳转到某网页 public class MainActivity extends AppCompatActivity {private static final …

layui之layer弹出层的icon数字及效果展示

layer的icon样式 icon如果在信息提示弹出层值(type为0)可以传入0-6&#xff0c;icon与图标对应关系如下&#xff1a; 如果是加载层&#xff08;type为3&#xff09;可以传入0-2&#xff0c;icon与图标对应关系如下&#xff1a;

无涯教程-Lua - 嵌套if语句函数

在Lua编程中&#xff0c;您可以在另一个if or else if语句中使用一个if or else if语句。 nested if statements - 语法 嵌套if 语句的语法如下- if( boolean_expression 1) then--[ Executes when the boolean expression 1 is true --]if(boolean_expression 2)then--[ Ex…

通过C语言设计的推箱子(控制台终端)

一、项目介绍 推箱子游戏是一款经典的益智小游戏&#xff0c;玩家需要控制主角角色将几个木箱按照要求推到指定位置。在控制台终端中&#xff0c;可以使用字符来表示不同的游戏元素&#xff0c;例如 ‘#’ 表示墙壁&#xff0c; ’ ’ 表示空地&#xff0c; ‘$’ 表示木箱&am…

在服务器上搭建gitlab

目录 1.在服务器上下载gitlab 2.编辑站点位置 3.重载配置 4.访问gitlab 最终效果展示&#xff1a; 官方文档&#xff1a; 安装部署GitLab服务 1.在服务器上下载gitlab wget https://mirrors.tuna.tsinghua.edu.cn/gitlab-ce/yum/el7/gitlab-ce-12.9.0-ce.0.el7.x86_64.r…

C. Mark and His Unfinished Essay - 思维

分析&#xff1a; 直接模拟操作会mle&#xff0c;可以每次复制记录对应源字符串的下标&#xff0c;可以记录每次字符串增加的长度的左右端点下标&#xff0c;可以发现左端点与读入的l是对应的&#xff0c;因此就可以向前移到l的位置&#xff0c;这样层层递归&#xff0c;就能找…

设计模式之适配器模式

一、概述 将一个类的接口转换成客户希望的另外一个接口。Adapter模式使得原本由于接口不兼容而不能一起工作的那些类可以一起工作。 二、适用性 1.你想使用一个已经存在的类&#xff0c;而它的接口不符合你的需求。 2.你想创建一个可以复用的类&#xff0c;该类可以与其他不…

el-popover使用自定义图标

使用el-popover实现鼠标点击或浮动到自定义图标上弹出表格弹窗&#xff0c;官方文档上使用的是按钮el-button&#xff0c;如果想换成图标或其他的组件的话直接把el-button替换掉即可。注意替换之后的组件一定要加slot“reference”&#xff0c;不然组件是显示不出来的。 代码如…

ProxyGenerator-代理类生成器

ProxyGenerator是JDK-sun包下提供的用于生成动态代理类信息的类&#xff0c;其唯一向外透出的是其静态方法-generateProxyClass(…)。 public class ProxyGenerator { ... }学习本篇文章&#xff0c;就是想学习ProxyGenerator如何生成代理类信息的过程。 一、唯一入口-公开静…

Misc取证学习

文章目录 Misc取证学习磁盘取证工具veracryto挂载fat文件DiskGenius 磁盘取证例题[RCTF2019]disk 磁盘[](https://ciphersaw.me/ctf-wiki/misc/disk-memory/introduction/#_2)内存取证工具volatility 内存取证例题数字取证赛题0x01.从内存中获取到用户admin的密码并且破解密码 …

LangChain手记 Overview

整理并翻译自DeepLearning.AILangChain的官方课程&#xff1a;Overview 综述&#xff08;Overview&#xff09; LangChain是为大模型应用开发设计的开源框架 LangChain目前提供Python和JavaScript&#xff08;TypeScript&#xff09;两种语言的包 LangChain的主攻方向是聚合和…

使用docker部署一个jar项目

简介: 通过docker镜像, docker可以在服务器上运行包含项目所需运行环境的docker容器, 在线仓库里有很多各个软件公司官方发布的镜像, 或者第三方的镜像. 如果我们需要使用docker把我们的应用程序打包成镜像, 别的机器上只要安装了docker, 就可以直接运行镜像, 而不需要再安装应…

基于ChatYuan-large-v2 语言模型 Fine-tuning 微调训练 广告生成 任务

一、ChatYuan-large-v2 ChatYuan-large-v2是一个开源的支持中英双语的功能型对话语言大模型&#xff0c;与其他 LLM 不同的是模型十分轻量化&#xff0c;并且在轻量化的同时效果相对还不错&#xff0c;仅仅通过0.7B参数量就可以实现10B模型的基础效果&#xff0c;正是其如此的…

VBA技术资料MF40:VBA_计数筛选状态的数据行数

【分享成果&#xff0c;随喜正能量】人唯有与喜欢的事物发展关系&#xff0c;不管是人或者是物还是事&#xff0c;包括喜欢自己外表、个性的部分&#xff0c;喜欢自己做的事&#xff0c;喜欢自己的创造&#xff0c;喜欢的风景……才给人带来对自己的认同。在与喜欢的事物互动关…

【Linux命令详解 | cd命令】Linux系统中用于更改当前工作目录的命令

文章标题 简介一&#xff0c;参数列表二&#xff0c;使用介绍1. 使用cd命令切换到特定目录2. 使用cd命令与路径相关的特殊字符3. 使用cd命令切换到包含空格的目录4. 使用cd命令切换到前一个和后一个目录5. 使用cd命令切换到用户的主目录6. 使用cd命令与绝对路径和相对路径 总结…

Vue day01

Vue 1.简介&#xff1a; ​ Vue是一套用于构建用户界面的渐进式框架。与其他大型框架不同的是&#xff0c;Vue被设计为可以自底向上逐层应用。Vue的核心库只关注视图层&#xff0c;不仅容易上手&#xff0c;还便于与第三方库或既有项目整合。另一方面&#xff0c;当与现代化的工…