MySQL数据类型优化

MySQL数据类型优化

良好的逻辑设计和物理设计是高性能的基础，MySQL 支持的数据类型非常多，选择正确的数据类型对于获得高性能至关重要。不管存储哪种类型的数据，下面几个简单的原则都有助于做出更好的选择。

• 选择最小数据类型

  一般情况下。尽量使用可以正确存储数据的最小数据类型。更小的数据类型通常更快，因为它们占用更少磁盘、内存和 CPU 缓存，并且处理时需要的 CPU 周期也更少

• 选择简单数据类型

  简单数据类型操作通常需要更少的 CPU 周期，例如，整型比字符操作代价更低，因为字符集和校对规则（排序规则）使字符比较比整型比较更复杂，例子：存储日期应该使用 MySQL 内建的类型，而不是字符串来存储日期和时间

• 尽量避免 NULL

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

常用数据类型介绍

整数类型

5种类型： tinyint 1字节，smallint 2字节，mediumint 3字节，int 4字节，bigint 8字节

存储范围为：-2(n-1)至2(n-1)-1，n 是存储空间的位数

整数类型有可选的 UNSIGNED 属性，表示不允许为负值，这大致可以使正数的上限提高一倍。

实数类型

2种类型： float 4字节，double 8字节

它们都支持使用标准浮点运算进行近似计算，如果想更精准的计算小数，可以使用decimal，浮点类型在存储同样范围的值时，通常比 decimal 使用更少的空间。float 使用4个字节存储。double 占用8个字节，相比 float 有更高的精度和更大的范围，因为需要额外的空间和计算开销，所以尽量只在对小数进行精确计算时才使用 decimal ------ 例如存储财务数据，但在数据量比较大的时候，可以考虑使用 bigint 代替 decimal, 将需要存储的货币单位根据小数的位数乘以相应的倍数即可。

字符串类型

主要是varchar和char两种类型，不同的存储引擎对它们在磁盘和内存的存储实现不同，下面主要介绍InnoDB和MyISAM。

varchar : 用于存储可边长字符串，是最常见的数据类型，它比定长类型更节省空间，因为它仅使用必要的空间（例如，越短的字符串使用越少的空间）如果表使用 ROW_FORMAT =FIXED 创建的话，每一行都会使用定长存储，这会很浪费空间

varchar 需要使用 1或2个额外字节记录字符串长度：如果列的最大长度小于或等于255字节，则只使用1个字节表示，否则使用2个字节，假设采用 latinl 字符集，一个 varchar(10) 需要 11个字节存储空间，varchar(1000) 则需要 1002个字节。

varchar 节省了存储空间，所以堆性能也有帮助。但是由于行是变长的，在update 时可能使行编的比原来更长，这就导致了额外的工作。如果一个行占用的空间增长，并且在业内没有更多的空间可以存储，在这种情况下，不同的储存引擎处理方式不一样，例如， MyISAM 会将行拆成不同的片段存储，InnoDB 则需要分裂页来使行可以放进页内。

什么情况使用 varchar ?

• 字符串列的最大长度比平均长度大很多
• 列很少更新，所以碎片不是问题
• 使用UTF-8这样复杂的字符集，每个字符使用的字节数不同

char char 是定长的，MySQL 总是根据定义的字符串长度分配足够的空间。当存储 char 时，MySQL 会删除所有的末尾空格，char非常适合存储密码的MD5值，因为这是一个定长的值，对于经常变更的数据，char也比varchar更好，因为定长的char类型不容易产生碎片。对于非常短的列，char比varchar在存储空间上也更有效率，例如char(1)来存储只有Y和N的值，如果采用单字节字符集只需要一个字节，但是varchar(1)却需要两个字节，因为还有一个记录长度的额外字节

blob 和 text 类型

blob 和 text 都是为存储很大的数据而设计的字符串类型，分别采用二进制和字符串方式存储

MySQL 把每个 blob 和 text 当作一个独立的对象进行处理。当他们的值太大的时候，InnoDB会使用专门的“外部”存储区域来进行存储， 此时每个值在行内需要1 ~ 4个字节存储一个指针，然后再外部存储实际的值

blob 和 text 家族之间仅有的不同是 blob 存储的是二进制，没有排序规则或字符集，而 text 类型有字符集和排序规则

MySQL 对 blob 和 text 列进行排序与其他类型时不同的：他只对每个列的最前 max_sort_lenght 字节而不是整个字符串做排序。如果只需要排序前面一小部分字符，则可减少 max_sort_length 配置，或者使用order by sustring(column,length)。Mysql也不能将他们的全部长度的字符串进行索引。如果没有必要，应该尽量避免使用blob和text。

枚举（enum）

枚举列可以把一些不重复的字符串存储成一个预定义的集合。MySQL 在存储枚举时非常紧凑，会根据列表值的数量压缩到一个或者两个字节中。MySQL 在内部会将每个值在列表中的位置保存为整数，并且在表的.frm文件中保存”数字-字符串”映射关系。例如：

create table t_enum(

     e enum('fish','apple','dog') not null

);

这三行数据实际存储的为整数，而不是字符串

枚举不好的地方是，字符串列表是固定的，添加或删除字符串必须使用 ALTER TABLE。因此，对于一系列未来可能会改变的字符串，使用枚举不是一个好主意，除非能接受只在列表末尾添加元素，这样在 MySQL 5.1 中就可以不用重建整个表来完成修改

由于 MySQL 把每个枚举值保存为整数，并且必须进行查找才能转换为字符串，所以枚举列有些开销。通常枚举的列表都比较小，所以开销还可以控制，但也不能保证一直如此。在特定情况下，把 char/varchar 列与枚举进行关联可能会比直接关联 char/varchar 列更慢。

日期和时间类型

MySQL 提供两种相似的日期类型：datetime 和 timestamp

datetime： 这个类型能保存大范围的值，从 1001 到 9999 年，精度为秒。他把日期和时间封装到格式为 YYYYMMDDHHMMSS 整数中，与时区无关，使用8个字节的存储空间

timestamp： timestamp类型类型保存了从1970 年 1 月 1日午夜（格林尼标准时间）以来的秒数，它和 UNIX 时间戳相同。 timestamp 只使用4个字节的存储空间，因此它的范围比 datetime 小很多，只能表示从 1970 年 到 2038年 。MySQL 提供了 from_unixtime() 函数把 Unix 时间戳

timestamp 提供的值与时区有关， datetime 则保留文本表示的日期和时间