Mysql数据类型的优化，如何选择合适的数据类型呢？——高性能Mysql（第四章笔记1）

本文链接：https://blog.csdn.net/rgbhi/article/details/114526020

咱在建表的时候，总是很疑惑，这个键值到底选哪种类型好呢？不同的类型有什么讲究吗？今天咱来掰扯掰扯。

选择数据类型的一般性偏好

1、在确保没有低估需要储存的值的范围下，选择占用越小的数据类型。
这很容易理解，谁都不喜欢啰嗦的话语，计算机也一样，能用一句话说完，为什么要说上十句话呢。

2、简单就好
如果该列需要排序，优先选用数据库里已内置的数据类型。比如储存时间，大家应该都会去使用datatime，timestamp这种，而不会使用字符串来储存。因为整型比字符操作代价更低（更快）。还有一个例子是用整型储存ip地址。

3、尽量避免 null
这指的是要排序的列里，最好不要使用null值，如果只是查询不排序，那无所谓了。不建议在可为空的列上，做索引。

知道了上述几个大前提，咱来看看具体的类型选择。

数字有两种类型：整数，实数（有小数点的）

整数类型

类型名称	储存空间 (位)
TINYINT	8
SMALLINT	16
MEDIUINT	24
INT	32
BIGINT	64

显示范围跟是否带符号有关，这个是可选的

咱们的选择，只是决定Mysql如何在内存和磁盘中储存数据。然而整数计算一般使用64位的BIGINT，即使在32位环境也是如此。（一些聚合函数除外，它们使用DECIMAL或DOUBLE进行）

实数类型

实数嘛，引入了小数点的概念。DECIMAL可以储存比BIGINT更大的数。该类型用于储存精确的小数。

FLOAT,DOUBLE这种浮点数的计算方式，跟各平台有关，就不赘述了。

CPU不支持DECIMAL的直接计算，但是支持原生浮点运算。早期Mysql版本，DECIMAL只是一种储存格式，计算中会转化为DOUBLE类型。

类型名称	储存空间 (字节)
FLOAT	4
DOUBLE	8
DECIMAL	最多64

在数据量比较大、小数位固定的情况下，可以考虑用BIGINT替代DECIMAL，比如0.01*100（倍数）=存1

字符串类型

有CHAR,VARCHAR,BLOB,TEXT这几种类型

先来看CHAR与VARCHAR

这两货在内存与磁盘中的储存方式可能是不一样的！

VARCHAR 顾名思义，可变长度字符串，因为是可变长，咱肯定要知道这个字符串到底有多长，才能把它准确的取出来吧。所以VARCHAR类型的值都会有1或者2个字节来表示值到底有多长。1字节8位，最多能表示255字符数（注意呀，不是字节数），所以字符数少于255的，就用一个字节来表示长度，超过的话，就用2个字节。咱也能看出VARCHAR的上限有多大了，2^16-1=65 535。InnoDB会把过长的VARCHAR储存为BLOB的，真是灵活呀！

咱可以想象到，因为储存的字符串的长度是可变的，如果要改变存储的字符串的值，是一件相当麻烦的事情。不同的引擎会有不同的操作，这里就不赘述了。

如果字符串的最大长度比平均长度大很多，并且更新很少的话，那么选用VARCHAR是很合适哒。

嚯嚯，VARCHAR是变长的，CHAR是定长的！

是不是有种链表跟数组的既视感，原理是相通的。

需要注意的一点是，CHAR类型会将值屁股后面的空格去除掉。比如“string3 ”会存成“string3”。这是在服务器层面做的操作，不是存储引擎干的，所以所有的引擎在这一点是一样的。

跟VARCHAR,CHAR类似的还有BINARY,VARBINARY

这两货存的是二进制字符串（字节码），不是字符。

赶紧去查了下啥是字节码。
什么是字节码
好了，知道了，不是人看的。

因为是给机器看的。所以比较起来的话，这两种类型会更快！

问题来了，为啥咱现在要抠抠搜搜的，一点也不大方？因为Mysql通常会分配固定大小的内存块来保存内部值。比如内存临时表排序。

行行行，咱以后一定勤俭持家，不浪费一点存储空间！

BLOB和TEXT

这题我会！TEXT是字符类型的，BLOB是二进制类型的！

字符类型	二进制类型
TINYTEXT	TINYBLOB
SMALLTEXT(叫TEXT也行)	SMALLBLOB(叫BLOB也行)
MEDIUMTEXT	MEDIUM BLOB
LONGTEXT	LONGBLOB

这两货太长了，还是不要跟行在一起了，一般会给它安排一个外部空间，存在那边。行这边只留一个指针！

Mysql对这两种类型进行排序的话，只对每行最前max_sort_length字节进行排序，（ORDER BY SUBSTRING(column,length)，加上这句sql也是妥妥的）用小脑袋想一想也明白，怎么可能对那么~长的值进行整体排序呢！

枚举类

虽然（ENUM关联ENUM）比（VARCHAR关联VARCHAR）快，但是平时咱都不用这个类型的啊。再快也没有整型快吧。枚举类型咱都是写在后端代码那边的，存的是整型，略过略过。

日期和时间类型

日期，很常用的数据类型呢。咱得知道，Mysql的最小时间粒度为秒，MariaDB才支持微秒级别的。

Mysql有两种日期类型，DATETIME和TIMESTAMP，这两货有啥不同呢

DATETIME	TIMESTAMP
1001年到9999年，精度为秒	1970年1月1日午夜以来的，秒数，最迟到2038-01-19 03:14:07
8字节	4字节
格式：YYYYMMDDHHMMSS的整数	秒数啊，用整型不过分吧
输入咋样输出就咋样	不同时区不同哦亲

现在2021年了，懂我的意思吧，留给TIMESTAMP的时间，不多了。

位数据类型

不是我说，这种数据类型也太抠了吧，精确到bit，一般最好不要用吧。不过有一种情况比较适合。那就是表示是否拥有权限的时候。比如一个字节01010101，1表示有这种权限，0表示没有，那是挺好的，一个值可以表示好多种权限呢，但是得约定好，每位代表啥。

选择标识符

可以和其他表产生联系的列，常见的比如订单号（order_id）,活动id什么的，最好使用整数类型，比较快，也可以自增，比较方便。

最好不要用随机值，因为这样会使得相邻的行的分步很分散。查找起来，新增起来，查找相邻行也很慢。

原文：如果存储UUID值，则应该移除“-”，更好的做法是，用UNHEX()函数转化UUID值为16字节的数字，并存储在一个BINARY(16)列中。检索时可以通过HEX()函数来格式化为十六进制格式。

听上去是个不错的方法呢。

特殊类型数据

IPv4地址不是字符唉，它们实际上是32位无符号整数。所以应该用无符号整数存储IP地址。Mysql提供了INET_ATON()函数
【功能是将一个字符串IP地址转换为一个32位的网络序列IP地址】
和INET_NTOA()函数
【功能是将网络地址转换成“.”点隔的字符串格式】来表示这两种表示方法的转换。