mysql数据类型优化

一、选择优化的数据类型

良好的逻辑设计和物理设计是高性能的基础，而选择正确的数据类型对于高性能至关重要。在数据库优化方面，数据类型优化只是一方面，还有查询语句优化，主键选择，索引建立，命名规则等。本文只要探讨数据类型方面的选择。一般在选择数据类型的时候，下面的几条原则有助于做出更好的选择：

• 选择可以正确存储数据的最小数据类型

　　更小的数据类型通常处理更快，占用更小的磁盘，内存容量，如果需要网络传输的时候，也占用更小的带宽。

• 选择简单的数据类型

　　简单类型需要更少的cpu处理时间，例如整型比字符串操作代价更低，因为字符集和校对规则使得字符比较比整型比较更麻烦，例如，使用mysql内建的时间类型存储日期和时间，而不是选择字符串。存ip地址的时候，我们可以使用无符号整型进行存储。在mysql中，有2个函数可以把ip地址和整数互相转换。例如：select inet_aton('255.255.255.255') 的结果是4294967295，select inet_ntoa(4294967295) 的结果是255.255.255.255，它的底层是这样算的，255 * 2²⁴ + 255 * 2¹⁶ + 255 * 2⁸ + 255 = 4294967295，整型的处理速度要比字符串快。

• 避免设置null列

　　null是列的默认属性，设置可为null的列会占用更多的空间，而且使得索引和值比较更复杂。

二、常用数据类型介绍

•  整数类型：5种类型，tinyint 1字节，smallint 2字节，mediumint 3字节，int 4字节，bigint 8字节，存储范围为：-2^(n-1)至2^(n-1)-1，n为数据类型位数。当存储的整数不包含负数的时候（无符号整数），可以使用unsinged属性，这样可以使得正数的上限提高一倍。
• 实数类型：float 4字节，double 8字节，它们都是使用标准的浮点运算进行近似计算。如果想更精准的计算小数，可以使用decimal，例如存储一些财务数据。因为cpu不支持对decimal的直接运算，所以只是mysql本身实现了decimal的高精度计算而已，相比而言，原生浮点类型计算更快，但是不精准，而decimal计算精准，但是计算代价高，并且存储同样范围值的时候，空间占用也更多。如果没有特殊要求，建议采用原生浮点类型。如果是涉及到财务数据需要精准计算，那么可以使用bigint代替decimal，只要根据小数的位数乘以相应的倍数即可，这样既避免了浮点存储计算的不精确，又避免了decimal计算代价高的问题。
• 字符串类型：主要是varchar和char两种类型，不同的存储引擎对它们在磁盘和内存的存储实现不同，下面主要介绍InnoDB和MyISAM。
  

  • varchar：存储可变长字符串，它比定长类型更节约空间，因为它仅仅使用必要的空间（例如越短的字符串使用越少的空间，也有例外，如果表使用row_format=fixed创建的话，每一行都会使用定长存储，这会浪费空间）。varchar需要1或者2个字节来存储字符串长度：如果最大长度小于或者255字节，则需要1个字节，否则使用2个字节。对于单字节字符集，varchar(10)的列需要11个字节的存储空间，varchar(300)的列需要302个字节，因为需要2字节存储长度信息。虽然varchar节省了空间，对性能有帮助，但是由于是变长的，一旦使用update更新可能导致行变得比原来长，那么如果一个行占用的空间增长，并且在页内没有更多的空间进行存储。对于InnoDB来说，则需要页分裂来使行可以放进页内，MyISAM会把行进行拆成不同的片段存储，另外在存储和检索的时候会保存字段值末尾的空格。什么情况下使用varchar：

    ①字符串列的最大长度比平均长度大很多

    ②列很少更新，所以碎片不是问题

    ③使用UTF-8这样复杂的字符集，每个字符使用的字节数不同。

  • char：存储定长字符串，会删除字段值末尾的空格，例如存的字段s='aaa   '，那么存入的时候其实存的是'aaa'，后面的空格会去掉。它适合存储很短的字符串，或者所有的值都接近同一长度，char非常适合存储密码的MD5值，因为这是一个定长的值。对于经常变更的数据，char也比varchar更好，因为定长的char类型不容易产生碎片。对于非常短的列，char比varchar在存储空间上也更有效率，例如char(1)来存储只有Y和N的值，如果采用单字节字符集只需要一个字节，但是varchar(1)却需要两个字节，因为还有一个记录长度的额外字节。

• blob和text：都是用来存储很大的数据而设计的字符串数据类型，分别采用二进制和字符方式存储。Mysql把每个blob和text当作一个独立的对象进行处理。当他们的值太大的时候，InnoDB会使用专门的“外部”存储区域来进行存储，此时每个值在行内需要1-4个字节存储一个指针，然后在外部存储区域存储实际的值。对他们进行排序的规则也与其他类型不同，mysql只对每个列的最前max_sort_length字节而不是整个字符串做排序，如果只需要排序前面小部分字符，则可以减少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

　　　　);

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

• 时间和日期类型：datetime和timestamp，datetime能保存从1001年至9999年，精度为秒。它把日期和时间封装到格式为YYYYMMDDHHMMSS的整数中，使用8字节的存储空间。timestamp保存了从1970-1-1 00：00：00开始至今的秒数，它和unix时间戳相同，它使用4字节的存储空间，范围比datetime小的多，只能表示从1970年到2038年。Mysql提供了from_unixtime()函数把unix时间戳转为日期，并提供了unix_timestamp()函数把日期转为unix时间戳。