MySQL数据类型

选择合适的数据类型

整数类型

可以存储的值的范围为：-2(n-1) ~ 2(n-1)-1

整数类型可以选择UNSIGNED属性，表示不允许负值，这大致可以使正数的上限提高一倍，比如tinyint，正常存储范围是-128 – 127，使用无符号定义后范围是0 – 255。

需要特别注意的是，我们经常在ddl语句中看到类似INT(11)这样的定义，这种定义实际上并不影响MySQL的实际空间分配和存储，他只是规定了一些客户端工具用来显示字符的个数，对于计算和存储来说，INT(1)和INT(20)是相同的！！！

实数类型

实数是带有小数部分的数字。

DECIMAL(M,D)

M表示该数据类型的精度

D表示该数据类型小数部分的长度，M-D则表示整数部分的长度

Decimal数据类型的空间占用计算规则：

1. 整数部分和小数部分的单独计算
2. 每9位占用4个字节
3. 剩下的1-2位占用1字节，3-4占用2字节，5-6占用3字节，7-9占用4字节
4. 符号和小数点不占用额外空间

Decimal空间占用计算方式举例如下：

MySQL会使用double进行浮点数类型的内部计算。

不推荐使用以上三种数据类型，比如在业务中，常用的实践方式是使用bitint代替实数类型，避免浮点数存储和计算不精确以及decimal类型精确计算但是代价高的问题。

字符串类型

说到了字符串就少不了字符集，以及字符集的排序规则。每个字符串列支持独立的字符集和该字符集的排序规则集。

       VARCHAR

       这是在开发中最常用的字符串类型。它比固定长度的类型更节省空间，因为它仅使用必要的空间（用更少的空间存储更短的值）。

       Varchar需要用额外1或2个字节记录字符串的长度，如果列的最大长度小于或等于255则使用1字节，否则使用2字节。所以我们一般在考量ddl语句时，在业务允许的情况下，都使用varchar(255)来定义字符串数据类型。那么如果是在latin1字符集的情况下，该列最多占用255+1字节的空间，如果是utf8的编码方式则最多占用255*3+1字节。因为latin1是每一个字符占用1个字节。MySQL中的utf8实际上是阉割版的，即utf8mb3每个字符最多占用3个字节，汉字和拉丁字母占用3个字节，emoji表情需要占用4个字节，所以无法正常保存，需要在定义的时候修改为utf8mb4。

如果列的最大容量远大于存储值的平均长度；更新较少；使用了utf8这类复杂字符集，每个字符都使用不同的字节数进行存储，则推荐使用varchar字符串类型。    

       CHAR

       固定长度，总是分配固定的长度出来。当存储char值时，如果字符串末尾有空格，MySQL会自动删除所有尾随空格，如果需要比较，值会用空格填充。Char的好处是因为固定长度，所以不容易出现碎片。

       TEXT

       BLOB（二进制形式存储字符串，没有排序规则或字符集）

       当text和blob值太大时，innoDB会使用独立的“外部”存储区域，此时每个值在行内占用1-4个字节的存储空间，剩余的值完全存储在外部存储区域中。

Text和blob类型的排序方式和其他类型不同：只对这些列的最前max_sort_length字节而不是整个字符串做排序，仔细想想也可以理解，因为在某些情况下这两种数据类型下的值可能会非常大，性能压力大效率低下，当然这个配置是可以根据实际情况进行修改的。

       MySQL不能将blob和text数据类型的完整字符串放入索引，只能使用前缀索引，所以在使用order by语句排序时仍需要全表扫描和filesort。

字符串家族中还有一种特殊的类型，枚举类型，个人不建议使用，这部分定义应该由业务代码来完成，就像不推荐使用外键和联表查询一样。

思考：varchar(5)和varchar(200)存储“hello”的空间开销是一样的，那么使用更短的列有什么优势呢？

因为MySQL通常会在内存中分配固定大小的内存块来保存值，这对于使用内存临时表的排序或其他操作来说尤其糟糕，在利用磁盘临时表进行文件排序时也同样糟糕。

日期和时间

       YEAR

       DATE

       DATETIME

       从1000年到9999年，精度为1微秒。使用YYYYMMDDHHMMSS格式存储压缩成整数的日期和时间，与时区无关，因为它记录的是日期和时间的文本表示，需要8个字节。

TIMESTAMP

存储自1970年1月1日格林尼治标准时间以来经过的秒数—与unix时间戳相同。只使用4字节的存储空间，它的范围比datetime小得多，只能表示从1970-1-1到2038-1-19范围的日期和时间。MySQL提供了unix时间戳和日期互相转换的函数：FROM_UNIXTIME()和UNIX_TIMESTAMP()。值得注意的是unix时间戳是基于时区的，因为它记录的是一个相对值，那么它的实际时间会随着参考的时区的变化而发生变化。

当然还可以使用毫秒数，它和timestamp的区别是：一个是毫秒数一个是秒钟数；当然他们都和时区有关系，显示的实际时间与随着时区的不同而不同。

结论，优先使用datetime类型存储时间，如果需要记录时区信息则使用timestamp。

位压缩数据类型

       BIT

       最大可以存储64位的，在检索的时候，默认返回该值对应的ASCII码值对应的字符，但是在数字上下文，则得到的是数字。下图中二进制b’00111001’对应的十进制数字为57，在检索的时候，返回这个ASCII码值对应的字符9，而数字上下文则返回ASCII码值，这会让人非常困惑（这实际上并不重要，因为不推荐使用该类型），建议谨慎使用，最好避免。

       SET

       一组打包的位的集合，MySQL支持FIND_IN_SET()和FIELD()等函数，使得查询很容易。

JSON数据类型

       该特性是MySQL8.0支持的。

       可以通过SHOW TABLE STATUS\G命令查看每张表占用的空间大小，其中的Data_length表述占用空间大小，单位为字节Byte。比如下表中该表占用1606字节。

设计基本原则

如何选择key

       注意类型确定后，要确保在所有相关表中使用相同的数据类型，包括UNSIGNED等属性，因为可能存在潜在的性能问题，或者数据类型转换产生的难以发现的问题。

       尽量选择整数类型作为key，他们速度快且可以递增。不过要注意范围，防止范围过小导致整数耗尽。

       避免使用字符串类型作为key，他们很消耗空间且通常比整数慢。

       对于完全随机的字符串要非常小心，如MD5()、SHA1()或UUID()生成的字符串。这些函数新生成的新值会任意分布在很大的空间内，这会减慢insert和某些类型的select查询的速度：

1. 因为插入的值会写到索引的随机位置，所以会使得insert变慢。这会导致页分裂、磁盘随机访问、以及对于聚簇索引产生碎片。
2. Select查询也会变慢，因为逻辑上相邻的行会广泛分布在磁盘和内存中，这破坏了计算机领域中的局部性原理

特殊数据类型

       比如存储ipv4地址，ipv4地址最大占用15个字符，因为都是数字和点号，所以最大也是占用15字节，加上varchar额外存储的长度1字节，就是16字节。如果将去转为无符号int，则仅占用4字节。

       这虽然降低了空间，但是牺牲了可读性，不过可以通过MySQL的视图特性作为弥补，最坏的情况是冗余存储，同时满足排序和可读性需求。

尽量避免

        在符合业务条件的情况下，尽量选择更小的数据类型

        尽量选择简单数据类型

        尽量避免存储NULL

        太多的列

        太多的联接，不建议联表查询

        建议是避免存储null值，但是这取决于你权衡业务和性能的结果。还有一个细节，MySQL会对NULL值进行索引，而Oracle则不会。（在MySQL中，IS NULL条件可以利用索引快速查找，在Oracle中，同样的查询可能需要全表扫描）