【MySQL 进阶】 选择优化的数据类型

推荐阅读

• 学习笔记 《 深入理解 Java 虚拟机》
• 学习笔记 《 后端架构设计》
• 学习笔记 《 Java 基础知识进阶》
• 学习笔记 《 Nginx 学习笔记》
• 学习笔记 《 前端开发杂记》
• 学习笔记 《 设计模式学习笔记》
• 学习笔记 《 DevOps 最佳实践指南》
• 学习笔记 《 Netty 入门与实战》
• 学习笔记 《 高性能MYSQL》
• 学习笔记 《 JavaEE 常用框架》
• 学习笔记 《 Java 并发编程学习笔记》
• 学习笔记 《 分布式系统》
• 学习笔记 《 数据结构与算法》

---

MySQL 支持的类型非常多，选择正确且合适的类型对于获得高性能非常重要。下面有简单的几个原则可以对选择合适的数据类型留下参考依据。

1. 更小的通常更好

一般情况下，应该尽量使用可以正确存储数据的最小数据类型，例如只需要存储0-200，使用tinyint unsigned 比int是更好地选择。更小的数据类型通常更快，占用内存和CPU资源更少，以及处理数据时间更短。

2. 简单更好

简单的数据类型通常更快地处理，同时占用更少的CPU。例如整型数据比字符串数据操作的代价更低。因为字符集和排序规则的存在，使得处理字符串比整理更加的复杂。
例如：使用MySQL的内建类型 date，time等时间类型就比使用字符串能够获取到更高的性能，另外使用整型数据存储IP地址，往往比使用字符串存储形如xxx.xxx.xxx.xxx 格式的IP地址效果更好。

3. 尽量避免使用NULL

在我们创建数据表的时候，通过有些列是默认为NULL的(TimeStamp例外)，但是通常从业务上来说这些字段资源不会保存NULL的状态，这时候指定列为NOT NULL通常是更好地选择，除非真的需要存储NULL。
如果查询的列中包含NULL，MySQL更难对其作出优化。可为NULL的列，通常索引，索引统计以及和值都比较复杂，可为NULL的列会使用更多的存储空间，NULL值在MySQL中也需要被特殊处理。

同时将列设置为NULL，对于性能的提升不是特别的大，在调优的时候没有必要首先排除这种情况的，除非确认其会导致异常情况。如果计划在列上创建索引，那么就应该避免将此列设置为允许为NULL。

4. 总结

在为列选择数据类型的时候，第一步需要确定合适的大类型，数字，字符串，时间等。然后下一步在选择合适的数据类型很多MySQL的数据类型可以允许存储相同的数据类型和只是存储的精度和以及范围不一致，或者需要的物理空间不同。
另外MySQL提供了很多类型的别名，如INTEGER、BOOL以及NUMERIC类型，他们都是别名，但是创建过之后，使用SHOW CREATE TABLE 命令之后检查，会发现 MySQL 报告的是基本类型而非别名。例如下面的例子:

DROP TABLE IF EXISTS `alias_example`;
CREATE TABLE IF NOT EXISTS `alias_example`
(
    `id`       BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '主键',
    `age`      INTEGER NOT NULL,
    `is_adult` BOOL DEFAULT FALSE
);

创建完成之后，使用show creat table 查询语句，可以看到其类型为基本类型，而非别名。

SHOW CREATE TABLE `alias_example`;

-- 输出结果

CREATE TABLE `alias_example` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `age` int(11) NOT NULL,
  `is_adult` tinyint(1) DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci