菜鸟必知的 MySQL 知识（二）—— 数据类型优化

篇2介绍了 MySQL 几种常用的数据类型，并介绍了同种数据类型下具体类型的区别，并在最后总结了应当如何根据具体的业务场景选择最合适的数据类型来建立 MySQL 数据表。

1 - MySQL 数据类型

（一） 整数

整数类型	存储空间	字节长度
TinyInt	8	1
SmallInt	16	2
MediumInt	24	3
Int	32	4
BigInt	64	8

它们可以存储的值的范围从 -2^(N-1) 到 2^(N-1) - 1，N为存储空间的位数。整数类型有可选的 Unsigned 属性，表示不允许负值，这大致可以使正数的上限提高一倍。例如 TinyInt Unsigned 可以存储的范围是 0 ~ 255，而 TINYINT 的存储范围是 -128 ~ 127。

注意点：
　int(M) M指示最大显示宽度（最大有效显示宽度是255），显示宽度与存储大小或类型包含的值的范围无关。对于存储和计算来说，Int(2) 和 Int(4)是相同的，它们均占用4个字节，存储范围（-2^31 到 2^31- 1），只是在表示数字25时，int(2)是25，int(4)是0025，即不足宽度会用在左边用0补充。

（二） 浮点数

浮点数类型	字节长度	用法
Float(M,D)	4	单精度浮点数，D<=24为默认Float，D>24则转化为Double
Double(M,D)	8	双精度浮点数
Decimal(M,D)	M+1/M+2	未打包的浮点数,计算中Decimal会转换为Double

• 单精度和双精度的区别：
  　单精度 Float，一般在计算机中存储占用4字节，也32位，有效位数为7位；双精度 Double 在计算机中存储占用8字节，64位，有效位数为16位。原因：不管float还是double 在计算机上的存储都遵循IEEE规范，使用二进制科学计数法，都包含三个部分：符号位，指数位和尾数部分。其中float的符号位，指数位，尾数部分分别为1, 8, 23。 双精度分别为1, 11, 52。　
  　精度主要取决于尾数部分的位数，float为23位，除去全部为0的情况以外，最小为2的-23次方，约等于1.19乘以10的-7次方，所以float小数部分只能精确到后面6位，加上小数点前的一位，即有效数字为7位。类似，double 尾数部分52位，最小为2的-52次方，约为2.22乘以10的-16次方，所以精确到小数点后15位，有效位数为16位。

• Decimal的用法：　
  　因为 Decimal 相比于 Float 和 Double 需要额外的空间和计算开销，所以尽量只在对小数进行精确计算时才使用，例如存储财务数据。但在数据量比较大的时候，可以考虑使用 BigInt 代替 Decimal，将需要存储的货币单位根据小数的位数乘以相应的倍数即可。

（三） 字符串类型

• VarChar

VarChar 用于存储可变长字符串，比定长类型更节省空间。VarChar 需要使用1或2个额外字节记录字符串的长度。VarChar 节省了存储空间，对性能也有帮助，但是由于行是变长的，在Update时可能使行变得比原来更长，这就导致需要做额外的工作，对此不同的存储引擎的有着自己的处理方式。

VarChar 的适合应用于这样的场景：字符串列的最大长度比平均长度大很多；列的更新很少，所以碎片不是问题；使用了UTF-8这样复杂的字符集，每个字符都使用不同的字节数进行存储。

• Char

Char 类型是定长的，根据定义的字符串长度分配足够的空间。Char 适合存储很短的字符串，或者所有值都接近同一个长度。

例如，存储密码的MD5值，因为这是一个定长的值；经常变更的数据，Char 也比 VarChar 更好，因为定长的 Char 类型不容易产生碎片；对于非常短的列，Char 比 VarChar 在存储空间上也更有效率，比如用 char(1) 来存储只有Y和N的值，如果采用单字节字符集只需要一个字节，但是 varchar(1) 却需要两个字节，因为还有一个记录长度的额外字节。

使用 varchar(5) 和 varchar(200) 存储 'hello' 的空间开销是一样的，那么使用更短的列有什么优势吗？
　事实证明有很大的优势。更长的列会消耗更多的内存，因为 MySQL 通常会分配固定大小的内存块来保存内部值。尤其是使用内存临时表进行排序或操作时会特别糟糕。在利用磁盘临时表进行排序时也同样糟糕。所以最好的策略是只分配真正需要的空间。

（四） 日期和时间类型

• DataTime
  　能保存大范围的值，从 1001 年到 9999 年，精度为秒。它把日期和时间封装到格式为 YYYYMMDDHHMMSS 的整数中，与时区无关。使用 8 个字节的存储空间，例如 “2017-03-31 10:46:37”。这是 ANSI 标准定义的日期和时间表示方法。

• TimeStamp
  　TimeStamp 只使用 4 个字节的存储空间，因此范围比 DataTime 小，只能表示从 1970 年到 2038 年。如果在多个时区存储或访问数据，TimeStamp 和 DataTime 的行为将很不一样。前者提供的值与时区有关系，后者则保留文本表示的日期和时间。
  　TimeStamp 也有 DataTime 没有的特殊属性。默认情况下，如果插入时没有指定第一个 TimeStamp 列的值，MySQL 则设置这个列的值为当前时间。TimeStamp 列默认为 NOT NULL，这也和其他数据类型不一样。

通常应该尽量使用 TimeStamp，因为它比 DataTime 空间效率更高。

以上只是介绍了笔者在日常开发中最经常使用的一些数据类型，相信这些类型应该可以满足你最基本的业务开发需求。

---

2 - 数据类型优化策略

（一） 尽量使用最小数据类型

更小的数据类型通常更快，因为其占用更少的磁盘、内存和 CPU 缓存，并且处理时需要的 CPU 周期也更少。但是要确保没有低估需要存储的值的范围，因为表中多个地方增加数据类型的范围是一个非常耗时的操作。如果无法确定哪个数据类型是最好的，就选择你认为不会超过范围的最小类型。

（二） 简单就好

简单数据类型的操作需要更少的 CPU 周期。例如，整型比字符操作代价更低，因为字符集和校对规则使字符比较比整型比较更复杂。有两个点值得注意，一是应该使用 MySQL 内建的类型而不是字符串来存储日期和时间，二是应该用整型存储 IP 地址。

（三） 尽量避免 NULL

通常情况下最好指定列为 NOT NULL，除非真的需要存储 NULL 值。因为如果查询中包含可为 NULL 的列，对 MySQL 来说更难优化，因为可为 NULL 的列使得索引、索引统计和值比较都更复杂。可为 NULL 的列会使用更多的存储空间，在MySQL里也需要特殊处理。当可为 NULL 的列被索引是，每个索引记录需要一个额外的字节。

---

大家好，我是彬彬酱，目前在腾讯从事Web后端开发。
菜鸟必知的 MySQL 知识专题整理了关于 MySQL 的基础知识，适合大家进行入门级学习，这个专题现包含下列文章：
菜鸟必知的 MySQL 知识（一）—— 基础知识
菜鸟必知的 MySQL 知识（二）—— 数据类型优化
菜鸟必知的 MySQL 知识（三）—— 索引优化

---