最近因需要改变新增了一些数据库表，然而在定义表构造时，具体列属性的抉择有些不知其所以然，索引的增加也有脱漏和不标准的中央，所以我打算为创立一个高性能表的过程以实战的模式写一个专题，以此来学习和坚固这些常识。
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代表字符串的字节长度，数字示意存储字符串字节长度的字节数）：
若报文内容中每个字符只占用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，理论定义容许的最大宽度为 3INSERT 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.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 是怎么运行的》：第四章
作者：京东物流 王奕龙
起源：京东云开发者社区 自猿其说Tech 转载请注明起源