漫谈Mysql之数据类型优化

常用数据类型

整数类型

类型	存储空间	数据范围
TINYINT	8	-27 ~ 27-1
SMALLINT	16	-215 ~ 215-1
MEDIUMINT	24	-223 ~ 223-1
INT	32	-231 ~ 231-1
BIGINT	64	-263 ~ 263-1

UNSIGNED 属性

整数类型有可选的 UNSIGNED 属性，表示不允许负值，这可以让正数的范围提升一倍，比如，TINYINT 的范围为 -128 ~ 127，加了 UNSIGNED 属性后范围为 0 ~ 255。有符号与无符号类型使用相同的存储空间，具有相同的性能，视情况进行选择。

宽度

类型类型可以指定宽度，例如：INT(11)，对大多数据应用是没有意义的，并不能限制值的合法范围，对于存储和计算而言，INT(1) 与 INT(11) 是一样的。但在建表语句中为INT类型加上宽度，可以让用户更好地了解该列的大概长度。

---

浮点数类型

类型	存储空间
FLOAT	32
DOUBLE	64
DECIMAL	受精度影响

DECIMAL 被存储为一个二进度字符串，每 4 个字节存储 9 位数字，例如：DECIMAL(18,9)，小数点两边各存储 9 位数字，各占用 4 个字节，小数点占用一个字节，共占用 9 个字节。

尽量不要使用 FLOAT 类型

---

字符串类型

Varchar

可变长字符串，需要使用 1 个或 2 个额外字节存储字符串长度，如果列长度小于等于 255，则使用 1 个字节，否则 2 个字节。例如：varchar(10) 需要 11 个字节存储空间，而 varchar(1000) 需要 1002 个字节存储空间。

由于是变长的，在 update 时可能使行变得比原来更长，一个行占用的空间增长，并且在页内没有更多空间可以存储，则会导致额外的工作，不同存储引擎的处理方式也不一样，例如：MyISAM 会将行折成不同的片段存储，InnoDB 则需要分裂页来使行可以放进页内。

使用 Varchar(5) 与 Varchar(200) 存储 ‘hello’ 字符串的空间开销是一样的，那么使用更短的列有优势吗？

事实证明有很大的优势，更长的列会消耗更多内存，Mysql 通常会分配固定大小的内存块来保存内部值，尤其是使用内存临时表进行排序或操作时会特别糟糕，利用磁盘临时表进行排序时也非常糟糕，所以最好的策略是只分配需要的空间。

Char

定长字符串，Char 适合存储很短的字符串，或者所有值都接近同一长度，例如：存储密码的MD5码。定长的 Char 类型不容易产生碎片，对于非常短的列，Char 的存储空间也比 Varchar 更小，例如：Char(1) 只需要一个字节，而 Varchar(1) 需要两个字节，其中一个用于存储字符串长度。

Char 类型存储时，默认会删除字符串末尾的空格。

Blob

为存储很大的数据而设计，采用二进制方式存储，包括：TINYBLOB、SMALLBLOB、BLOB、MEDIUMBLOB、LONGBLOB，BLOB 与 SMALLBLOB 是同义词

Text

为存储很大的数据而设计，采用字符方式存储，包括：TINYTEXT、SMALLTEXT、TEXT、MEDIUMTEXT、LONGTEXT，TEXT 与 SMALLTEXT 是同义词

Blob 与 Text 相同之处：

Mysql 把 Blob 及 Text 当作一个独立的对象处理，当 Blob/Text 值太大时，InnoDB 会使用专门的 外部 存储区域进行存储，此时每个值在行内只需要 1~4 个字节存储一个指针，然后在外部存储区域存储实际的值。

Mysql 对 Blob 及 Text 的排序与其它类型是不同的，只对每列最前 max_sort_length 字节进行排序，而不是整个字符串。

Mysql 不能对 Blob 及 Text 列全部长度的字符串进行索引，也不能使用这些索引消除排序。

Blob 与 Text 的不同之处：

Blob 类型存储的是二进制数据，没有排序规则、字符集，而 Text 类型存储的是字符数据，有排序规则及字符集

枚举类型

Mysql 内部会将枚举类型转换为整数，所以实际存储的是整数，而不是字符串，在表的 .frm 文件中保存了 “数字-字符串” 映射关系的查找表。

对枚举列进行排序时，是按存储的整数进行排序，而不是按字面字符串，可以使用 FIELD() 函数显式指定排序顺序，但这样会导致索引失效。

枚举列不好的地方

枚举最不好的地方在于，字符串列表是固定的，往这个列表中增加或者删除一个枚举值时，都需要 alter table 进行调整表结构。另外，枚举列需要将数据转换为字符串，这会产生一些开销，通常枚举列表都比较小，这个开销是可以接受的。

---

日期时间类型

Datetime

存储 1001 年至 9999 年，精度为秒，把日期与时间封装到格式为 YYYYMMDDHHMMSS 的整数中，使用 8 个字节存储，与时区无关，

Timestamp

存储 1970 年 1 月 1 日午夜（格林尼治标准时间）以来的秒数，与 UNIX 时间戳相同，使用 4 个字节存储，因此范围比 Datetime 小很多，只能存储 1970 年至 2038 年。

Timestamp 显示的值依赖时区，Mysql 服务器、操作系统、客户端连接都可以设置时区，例如：存储值为 0 的 Timestamp，在美国东部时区显示为 1969-12-31 19:00:00，与格林尼治标准时间差 5 个小时。

---

数据类型优化原则

优化原则：

• 更小的通常更好，使用可以正确存储数据的最小数据类型。因为占用更少的磁盘、内存和CPU缓存，并且处理时需要的CPU同期也更小。
• 简单就好，简单数据类型操作通常需要更小的CPU周期，例如，整型比字符型操作代价更低，
• 尽量避免 NULL，因为可为 NULL 的列使得索引、索引统计及值比较都变得更复杂，需要更多的存储空间，当可为 NULL 的列被索引时，每个索引需要一个额外的字节。

通常把可为 NULL 修改为 NOT NULL 带来的性能提升比较小，所以这个原则的优先级不是最高的。但如果计划在列上建索引，就应该尽量避免该列可为 NULL。
但也有例外情况，InnoDB 使用单独的位(bit)存储 NULL 值，所以对于稀疏数据(只有少数行为非 NULL 值)有很好的空间效率。