MySql优化之：选择优化的数据类型

MySql支持多种不同的数据类型，选择正确的类型对于获得高性能至关重要，下面原则有助于做出选择：

 

1、更小通常更好

    一般来说，要试着使用能正确的存储和表示数据的最小类型，更小的数据类型通常会更快，因为他们使用了更小的磁盘空间，内存和CPU缓存，而且需要的CUP周期也小。

    但是要确保不低估需要被保存的值，在架构中的多个地方增加数据类型的范围是一件极其费时费力的工作。如果不确定需要什么数据类型，就选择你认为不会超出范围的最小数据类型。

 

2、简单就好

    越简单的数据类型，需要的CPU周期就越少。例如比较整数的代价小于比较字符，因为字符集和排序规则使字符比较更复杂。如应该使用MySql的内建类型保存日期和时间而不是字符串，应该使用整形来保存IP地址而不是字符串。

 

3、尽量避免NULL

    要尽可能的把字段字义为NOT NULL，除非真的要保存NULL，否则就把列定义为NOT NULL。MySql难以优化使用了可空列的查询，它会使索引、索引统计和值更加复杂。可空列需要更多的存储空间，还需要在MySql内部进行特殊处理。当可空列被索引的时候，每条记录都需要一个额外的字节，还能导致MyISAM中固定大小的索引（例如一个整数列上的索引）变成可变大小的索引。

    即使要在表中存储“没有值”的字段，还是有可能不使用NULL的，考虑使用0、特殊值或者空字符串替代。

    把NULL改成NOT NULL带来的性能提升很小，所以除非确定它引入了问题，否则就不要把当成优先的优化措施，但是如果计划某列进行索引，就要尽量避免把它设置为可空。

 

 

    决定特定列的数据类型的第一步就是大致决定数据的类型：数字、字符、时间等。这个会很直观，下面会说到些不直观的情况。

    第二步就是确定特定的数据类型，许多MySql数据类型保存同类的数据，但是存储的范围、精度或物理空间却不相同，一些数据还有特殊的行为或属性。如：DATETIME和TIMESTAMP能够保存同样的数据类型：日期和时间，精度为秒。而TIMESTAMP使用的空间只有DATETIME的一半，还能保存时区，拥有特殊的自动更新的能力。而另一方面，它允许的范围要小的多，并且在某些时候，它的自动更新功能会成为障碍。

1、整数

    MySql数据有两种类型：整数(Whole Number)和实数(Real Number)。如果存储整数，就可以使用这几种类型：TINYINT、SMALLINT、MEDIUMINT、INT或BIGINT，它们分别需要8、16、24、32和64位存储空间，取值范围为-2(n-1)到2(n-1)-1，n为所需存储空间位数。

    整数类型有可选的UNSINGED属性，表示正整数，不许为负数，并把正上限提高了一倍，有符号和无符号类型占用的存储空间是一样的，性能也一样，使用时根据实际情况采用合适的类型。

    你的选择将会决定MySql把数据保存在内存中还是磁盘中，然而整数运算通常使用64位BIGINT整数，即使是32位的架构也是如此。一些聚合函数例外，它们使用DECIMAL或者DOUBLE进行计算。

    MySql还可以对数据类型定义宽度（长度），比如INT(11)，这对于大多数应用程序都是没有意义的，它不会限制值的范围，只规定了MySql的交互工具（如命令行客户端）用来显示字符的个数，对于存储和计算，INT(1)和INT(20)是一样的。

2、实数

    实数有分数部分，然而它们并不仅仅是分数，可以使用DECIMAL保存比BIGINT还大的整数。MySql同时支持精确与非精确类型。FLOAT和DOUBLE支持使用标准的浮点运算进行近似计算。DECIMAL用于保存精确的小数，在MySql5.0以上版本，DECIMAL支持精确的数学运算，在MySql4.1及以前版本中对DECIMAL执行浮点运算，它因为丢失精度而导致奇怪的结果，在这些老版本中，DECIMAL仅仅只是“存储类型”。在5.0以上版本，服务器自身进行了DECIMAL运算，因为CPU并不支持对它进行直接计算。浮点运算会快点，因为计算直接在CPU上进行。

    可以定义浮点类型和DECIMAL类型的精度，对于DECIMAL列，可以定义小数点之前和之后的最大位数，这影响了所需要的存储空间。MySql5.0和以上版本中把数字保存到一个二进制字符串(每4个字节保存9个数字)。例如DECIMAL(18,9)将会在小数点前后都保存9位数字，总共使用9个字节：小数点前4个，小数点后4个，小数点1个。MySql5.0及以上的版本中的DECIMAL最多允许有65个数字，在较早的版本中，DECIMAL最多可以有254个数字，并且保存为未压缩的数字，一个数字占一个字节，然而这些版本中的MySql根本不能在计算中使用如此大的数字，因为DECIMAL只是一种存储格式，在运行时会被转换为DOUBLE类型。

    可以用多种方式来定义浮点数列的精度，它会导致MySql悄悄的采用不同的数据类型，或者在保存的时候进行圆整。这些精度定义符不是标准的，因此我们建议定义需要的类型，而不是精度。

    比起DECIMAL，浮点类型保存同样大的值采用更小的空间，FLOAT占4个字节，DOUBLE占8个字节，而且精度更高，范围更广。和整数一样，你选择的仅仅是存储类型，MySql内部对浮点型使用DOUBLE进行计算。

    由于需要额外的空间和计算开销，只有在对小数进行精确计算时才用DECIMAL，比如保存金融数据。

3、字符串

    MySql支持多种字符串类型，它们之前有很多种不同，从MySql4.1起，每个字符串列都有自己的字符集和排序规则。

 

VARCHAR和CHAR类型

    两种主要的字符串类型VARCHAR和CHAR，很难确切的说这两种类型是如何被保存到磁盘或内存中的，因为具体的实现要依赖于存储引擎，这里假设你使用的是InnoDB和MyISAM引擎。

 

VARCHAR

    VARCHAR保存了可变长度的字符串，是使用得最多的字符串类型，它比固定长度类型占用更少的存储空间，它只占用了需要到的空间，例外的情况是使用ROW_FORMAT=FIXED创建的MyISAM表，它为每行使用固定的长度空间，可能会造成浪费。

    VARCHAR使用额外的1到2个字节存储值的长度。如果列的最大长度小于或等于255，则使用1个字节，否则就使用2个字节。假设使用latin1字符集，VARCHAR(10)将会占用11个字节的存储空间，VARCHAR(1000)占用1002字节的存储空间，2个字节用于保存长度信息。

    VARCHAR能节省空间，所以对性能有帮助，然后，由于列的长度是可变的，它们在更新的时候可能会发生变化，这会引起额外的工作，如果行的长度增加并不再适合于原始的位置时，具体的行业则会和存储引擎相关，例如：MyISAM会把行拆开，InnoDB会进行分页。当最大长度远大于平均长度，并且很少发生更新的时候通常适合使用VARCHAR，这时候碎片不会成为问题，当你使用复杂的字符集，比如UTF-8时，它的每个字符都可能占用不同的存储空间。

    在MySql5.0及以后版本中，无论是保存还是取值，MySql都会保留字符串末尾的空格，但在4.1版本之前，这些空格都会被去掉的。

 

CHAR

    CHAR是固定长度的，MySql总是为特定数量的字符分配足够的空间，当保存CHAR值的时候，MySql会去掉末尾的空格(只是末尾而不是两端)。在比较的时候，空格会被填充到字符串末尾。

    CHAR在存储长度很短或长度近似相同的字符串的时候很有用。例如：CHAR很适合存储用户密码的MD5哈希值，它的长度总是一样的，对于经常改变的值，CHAR也好于VARCHAR，因为固定长度不易产生碎片。对于很短的列，CHAR也好于VARCHAR，CHAR(1)会占用1个字节，而VARCHAR(1)则会占用两个字节，1个字节用于保存长度。

    CHAR和VARCHAR的兄弟类型为BINARY和VARBINARY，它们用于保存二进制字符串，二进行字符串和传统的字符串相似，但是它们保存的是字节，而不是字符。填充也有所不同：MySql使用\0(0)字节填充BINARY值，而不是空格，并且在获取数值的时候把填充值截掉。它们在必须存储二进制数据的时候很有用，字节比较的优势并不仅仅体现在大小写敏感上，MySql按照字节的数值进行比较比字符比较简单的多，效率也高。

 

4、BLOB类型和TEXT类型

    BLOG和TEXT分别以二进制和字符形式保存大量的数据。它们有各自的数据家族：字符类型有TINYTEXT、SMALLTEXT、TEXT、MEDIUMTEXT和LONGTEXT，二进制的有TINYBLOB、SMALLBLOB、BLOB、MEDIUMBLOG和BIGBLOB，BLOG等于SMALLBLOB，TEXT等于SMALLTEXT。和其它类型不同，MySql把BLOB和TEXT类型当成实体的对象来处理。InnoDB在它们特别大的时候会使用单独的外部存储区域来保存他们，每个值在行里都需要1到4个字节，并且还需要足够的存储空间来保存实际的值。

    BLOB和TEXT的唯一区别是BLOB是用来保存二进制数据的，没有字符集和排序规则，但是TEXT有字符集和排序规则。MySql对BLOB和TEXT列的排序和其它类型的排序不一样，它不会按照完整长度进行排序，而只按照max_sort_length规定的前若干个字节进行排序。如果只按照开始的几个字符进行排序，就可以减少max_sort_length或使用ORDER BY SUBSTRING(column,length)。MySql不能索引这类型的数据的完整长度，也不能为排序使用索引。

5、日期和时间类型

MySql 可以使用多种类型来保存日期和时间值，比如 YEAR 和 DATE 。 MySql 能存储的最细的时间粒度是秒，然而绕过这种限制也可以使用毫秒来进行短暂的运算。

 

DATETIME

这个类型能保存大的范围值，从 1001 年到 9999 年，精度为秒，它把日期和时间封装到一个格式为 YYYYMMDDHHSSMM 的整数中，与时区无关，它使用了 8 个字节的存储空间。在默认情况下， MySql 以一种可排序的、清楚的格式来显示 DATETIME 值，例如 2012-05-22 22:35:01

 

TIMESTAMP

TIMESTAMP 只保存了自 1970 年 1 月 1 日以来的秒数，它只使用了 4 个字节的存储空间，因此它的范围比 DATTIME 小的多，只能表示 1970 年到 2038 年。较新版本的 MySql 按照 DATETIME 格式化 TIMESTAMP 的值，但是较老的 MySql 不会在各个部分之间显示标点符号，这仅仅只是显示上的区别，存储格式在所有版本中都是一样的。

在默认情况下，如果插入的行没有设置 TIMESTAMP 列的值， MySql 会把它设置为当前系统时间，在更新的时候，如果没有显示的更新 TIMESTAMP 列的值， MySql 也会以当前时间来自动更新。 TIMESTAMP 默认为 NOT NULL ，这和其它数据类型不一样。

 

MySQL时间格式长度