高性能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周期也更少。可是，这也要确保没有低估需求存储的值的规模，不然会因入库失利而形成数据丢失，并且表结构修正的流程批阅也很费事。

咱们以表中 id 和 message 列为例来说：

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 Type	Storage Required(Bytes)
TINYTEXT	L + 1, L < 2⁸
TEXT	L + 2, L < 2¹⁶
MEDIUMTEXT	L + 3, L < 2²⁴
LONGTEXT	L + 4, L < 2³²

若报文内容中每个字符只占用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_caller 和 service_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 则是将该列一切数据都保存在其他数据页中，在该列数据处只保存对应溢出页的地址。

可是实践上将列的界说修正为 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，如下所示：

实数类型

实数类型由于在该表结构中运用不到咱们没有介绍，所以在这里进行补充。

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;

f1	f2	d1	d2
3.1416	3.14159	3.1416	3.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
);

f1	f2	d1	d2
3.1416	3210.14	3.1416	3.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.1416
INSERT INTO decimal_t values (1234.1415926535);
# Data truncation: Out of range value for column 'd1' at row 1

除此之外，在一些大容量的场景下，能够考虑运用 BIGINT 代替 DECIMAL，在存储时依据小数的位数乘以相应的倍数即可。这样就能够同时防止浮点数核算不准确、 DECIMAL 准确核算价值高和数值精度规模约束的问题。

巨人的膀子

《高性能 MySQL 第四版》：第六章
11.7Data Type Storage Requirements
mysql的日期时刻类型及精度问题
MySQL之DATETIME与TIMESTAMP的时刻精度问题
11.8Choosing the Right Type for a Column
11.1.4Floating-Point Types (Approximate Value) – FLOAT, DOUBLE
B.3.4.8Problems with Floating-Point Values
《MySQL 是怎样运转的》：第四章

高性能MySQL实战（一）：表结构

1. 实战

2. 优化和改善

慷慨不是正确的

更小的通常更好

DATETIME 和 TIMESTAMP

防止运用 NULL

TINYINT 表明 Boolean 类型

实数类型

巨人的膀子

作者信息

推广

高性能MySQL实战（一）：表结构

1. 实战

2. 优化和改善

慷慨不是正确的

更小的通常更好

DATETIME 和 TIMESTAMP

防止运用 NULL

TINYINT 表明 Boolean 类型

实数类型

巨人的膀子

相关文章

大模型搅拌机来了！把11个AI的结果融合输出，艾伦研究所华人团队出品

Redis缓存穿透/击穿/雪崩以及数据一致性的解决方案

ChatGPT！我是你的破壁人；比尔·盖茨不看好Web3与元宇宙；FIFA押中4届世界杯冠军；GitHub今日热榜 | ShowMeAI资讯日报

使用 Kotlin 委托，拆分比较复杂的 ViewModel

作者信息

推广