首页 > 数据库 > mysql教程 > 【MySQL】MySQL的数据类型优化

【MySQL】MySQL的数据类型优化

黄舟
发布: 2017-02-25 10:19:59
原创
1166 人浏览过

选择优化的数据类型

MySQL支持的数据类型非常多,选择正确的数据类型对于获得高性能至关重要。不管存储那种类型的数据,下面几个原则都有助于做出更好的选择。

更小的通常更好

一般情况下,应该尽量使用可以正确存储数据的最小数据类型(例如只需要存0-200,tinyint unsigned更好)。更小的数据类型通常更快,因为它们占用更少的磁盘、内存和CPU缓存,并且处理时需要的CPU周期也更少。

简单就好

简单数据类型的操作通常需要更少的CPU周期。例如,整型比字符操作代价更低,因为字符集和校对规则(排序规则)是字符串比较比整型比较更复杂。这里有两个例子:一个是应该用MySQL内建的类型(比如date,time,datetime)而不是字符串来存储日期时间,另外一个是应该用整型来存储IP地址。

尽量避免使用NULL

很多表都包含了可为NULL的列,即使应用程序不需要保存NULL也是如此,这是因为可为NULL是列的默认属性。通常情况下最好指定列为NOT NULL,除非真的需要存储NULL值。

如果查询中包含可为NULL的列,对MySQL来说更难优化,因为可为NULL的列使得索引、索引统计和值比较都更复杂。可为NULL的列会使用更多的存储空间,在MySQL里也需要特殊处理。当可为NULL的列被索引时,每个索引记录需要一个额外的字节,在MyISAM中甚至还可能导致固定大小的索引(例如只有一个整数列的索引)变成可变大小的索引。

通常把可为NULL的值改为NOT NULL带来的性能提升比较小,所以(调优时)没有必要首先在现有的schema中查找并修改掉这种情况,除非确定这会导致问题。但是,如果计划在列上建立索引,就应该避免设计成可为NULL的列。

当然也有例外,例如值得一提的是,InnoDB使用单独的位(bit)存储NULL值,所以对于稀疏数据(大部分值为NULL,只有少数行为非NULL的值)有良好的空间效率。但这一点不适用于MyISAM。


整数类型

如果存储整数,可以使用这几种整数类型:TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT。分别使用8,16,24,32,64位存储空间。它们的存储范围从 -2的(N-1)次方 到 2的(N-1)次方-1,其中N为存储空间的位数。

整数类型有可选的UNSIGNED属性,表示不允许负值,这大致可以使正数的上限提高一倍,例如TINYINT UNSIGNED可以存储的范围是0-255,而TINYINT 的存储范围是-128~127。

MySQL可以为整数类型指定宽度,例如INT(11),对大多数应用这是没有意义的:他不会限制值得合法范围,知识规定了MySQL的一些交互工具(例如MySQL命令行客户端)用来显示字符的个数。对于存储来说,INT(1)和INT(20)是相同的。

实数类型

实数是带有小数部分的数字。然而,它们不只是为了存储小数部分,也可以使用DECIMAL存储比BIGINT还大的整数。MySQL既支持精确类型,也支持不精确类型。

FLOAT 和 DOUBLE 类型支持使用标准的浮点运算进行近似计算。如果需要知道浮点运算时怎么计算的,则需要研究所使用的平台的浮点数的具体实现。

DECIMAL 类型用于存储精确的小数。但因为CPU不支持对DECIMAL的直接计算,所以在MySQL5.0及更高版本中,MySQL服务器自身实现了DECIMAL的高精度计算。相对而言,这比CPU直接支持原生浮点数运算要慢。

浮点和DECIMAL类型都可以指定精度。对于DECIMAL列,可以指定小数点前后所允许的最大位数。这会影响列的空间消耗。

浮点类型在存储同样范围的值时,通常比DECIMAL使用更少的空间。FLOAT使用4个字节存储。DOUBLE占用8个字节,相比FLOAT有更高的精度和更大的范围。

因为需要额外的空间和计算开销,所以应该尽量只在对小数进行精确计算时才使用DECIMAL——例如存储财务数据。但数据量比较大的时候,可以考虑使用BIGINT代替DECIMAL,将需要存储的货币单位根据小数点的位数乘以相应的倍数即可。假设要存储财务数据精确到万分之一分,则可以把所有金额乘以100W,然后将结果存储在BIGINT里,这样可以同时避免浮点存储计算不精确和DECIMAL精确计算代价高的问题。

字符串类型

下面的描述假设使用的存储引擎是InnoDB/或者MyISAM。如果不是这两种存储引擎的,请参考所使用的存储引擎的文档。

VARCHAR和CHAR

VARCHAR:它比定长类型更节省空间,因为它仅使用必要的空间。VARCHAR节省了空间,所以对性能也有帮助。但是由于行是变长的,在UPDATE时可能使行变得比原来更长,这就导致需要做额外的工作。

下面的情况使用VARCHAR是合适的:字符串最大长度比平均长度大很多;列的更新少,所以碎片不是问题;使用了像UTF-8这样复杂的字符集,每个字符使用不同的字节数。

在5.0或更高的版本中,MySQL在存储和检索时会保留末尾空格。InnoDB则更灵活,它可以把长的VARCHAR存储为BLOB

CHAR: 定长,当存储CHAR值时,MySQL会删除所有的末尾空格。定长的CHAR类型不容易产生碎片,对于非常短的列,CHAR比VARCHAR在存储空间上也更有效率,VACHAR还有一个或两个记录长度的额外字节。CHAR适合存储很短的字符串,或者所有值都接近同一个长度。例如:CHAR非常适合存储密码的MD5值,因为这是一个定长的值。CHAR会根据需要采用空格填充以方便比较。

与CHAR和VARCHAR类似的类型还有BINARY和VARBINARY,它们存储的是二进制字符串。二进制字符串中存储的是字节码而不是字符。

二进制比较的优势并不仅仅体现在大小写敏感上。MySQL比较BINARY字符串是,每次按一个字节,并且根据该字节的数值进行比较。因此,二进制比字符比较简单的多,所以也就更快。

BLOB 和 TEXT类型

BLOB和TEXT类型:BLOB和TEXT都是为了存储很大的数据而设计的字符串数据类型,分别采用二进制和字符方式存储。当BLOB和TEXT值太大时,InnoDB会使用专门的”外部”存储区域来进行存储。原表字段存储指针指向外部存储区域。

MySQL对BLOB和TEXT列进行排序与其他类型是不同的:它只对列最前max_sort_length 字节而不是整个字符串做排序。如果只需要排序前面一小部分字符,则可以减小max_sort_length 的配置,或者使用ORDER BY SUSTRING(column, length)

MySQL不能将BLOB和TEXT列全部长度的字符串进行索引,也不能使用这些索引消除排序。

使用枚举(ENUM)代替字符串类型

可以使用枚举(ENUM)代替字符串类型。很多时候建议使用枚举列代替常用的字符串类型。

(1)枚举列可以把一些不重复的字符串存储成一个预定义的集合。
(2)Mysql在存储枚举时非常紧凑,会根据列表值的数量压缩到一到两个字节中。  
(3)Mysql在内部会将每个值在列表中的位置保存为整数,并且在表的.frm文件中保存“数字-字符串”映射关系的“查找表”。

注意:有一个令人吃惊的地方是,枚举字段是按照内部存储的整数而不是定义的字符串进行排序的。

注意:枚举最不好的地方是:字符串列表是固定的,添加或者删除字符串必须使用ALTER TABLE,因此对于一系列未来可能会改变的字符串,使用枚举并不是一个好主意,除非接受只能在列表末尾添加元素。

注意:由于Mysql把每个枚举值保存为整数,并且必须进行查找才能转换为字符串,所以枚举列有一些开销。

日期时间类型

数据类型及用法详见 : //m.sbmmt.com/

Mysql有很多类型可以保存日期和时间值,比如YEAR和DATE。

Mysql能存储的最小时间粒度为秒(MariaDB支持微秒级别的事件类型)。但是Mysql也可以使用微秒级别的粒度进行临时运算。

大部分时间类型都没有替代品,因此没有什么是最佳选择的问题。

接下来唯一的问题是保存日期和时间的时候需要做什么。

DATETIME

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

TIMESTAMP

(1)TIMESTAMP类型保存了从1970年1月1日午夜以来的秒数,它和UNIX时间戳相同。 (2)TIMESTAMP只使用4个字节的存储空间,因此它的范围比DATETIME小得多。 (3)TIMESTAMP显示的值依赖时区。

DATETIME和TIMESTAMP的对比:

(1)默认情况下,如果插入时没有指定第一个TIMESTAMP列的值,Mysql则设置这个列的值为当前时间。(这是DATETIME没有的特性) (2)在插入一行记录时,Mysql默认也会更新第一个TIMESTAMP列的值。 (3)TIMESTAMP列默认为NOT NULL,这与其他的数据类型不一样。

总结

(1)除了特殊行为之外,通常也应该尽可能使用TIMESTAMP,因为它比DATETIME空间效率更高。 (2)一般来讲不建议把UNIX时间戳保存为整数值,这不会带来任何收益,用整数保存时间戳格式通常不方便处理。 (3)如果需呀存储比秒更小粒度的日期和时间值,可以使用BIGINT类型存储微秒级别的时间戳,或者使用DOUBLE存储秒之后的小数部分,也可以用MariaDB替代Mysql。

位数据类型

MySQL有少数几种存储类型使用紧凑的位存储数据。所有这些位类型,不管底层存储格式和处理方式如何,从技术上来说都是字符串类型的。

BIT

可以使用BIT列在一列中存储一个或多个true/false值。BIT(1)定义了一个包含单个位的字段,BIT(2)存储2个位,依次类推。BIT列的最大长度是64位。

如果想在一个bit的存储空间中存储一个true/false值,另一个方法是创建一个可以为空的CHAR(0)列。该列可以保存空值(NULL)或者长度为零的字符串(空字符串)。

SET

如果需要保存很多true/false 值,可以考虑合并这些列到一个SET 数据类型,它在MySQL 内部是以一系列打包的位的集合来表示的。这样就有效地利用了存储空间,并且MySQL 有像FIND_IN_SET() 和FIELD() 这样的函数,方便地在查询中使用。它的主要缺点是改变列的定义的代价较高:需要ALTER TABLE,这对大表来说是非常昂贵的操作。一般来说,也无法在SET 列上通过索引查找。

一种替代SET 的方式是使用一个整数包装一系列的位。例如,可以把8 个位包装到一个TINYINT 中,并且按位操作来使用。可以在应用中为每个位定义名称常量来简化这个工作。

比起SET,这种办法主要的好处在于可以不使用ALTER TABLE 改变字段代表的”枚举”值,缺点是查询语句更难写,并且更难理解(当第5 个bit 位被设置时是什么意思?)。一些人非常适应这种方式,也有一些人不适应,所以是否采用这种技术取决于个人的偏好。

选择标识符(identifier)

为identifier(标识列)选择合适的数据类型非常重要。

一般来讲更有可能用标识列与其他值进行比较,或者通过标识列寻找其他列。

当选择标识列的类型时,不仅仅需要考虑存储类型,还需要考虑Mysql对这种类型怎么执行计算和比较。

一旦选定了一种类型,要确保在所有关联表中都使用同样的类型。

在可以满足值的范围需求,并且预留未来增长空间的前提下,应该选择最小的数据类型。

  • 整数通常是标识列最好的选择,因为它们很快而且可以使用AUTO_INCREMENT

  • ENUM和SET是最糟糕的选择了;

  • 如果可能也尽可能避免使用字符串作为标识列,因为它们很消耗空间并且通常比数字类慢。

特殊类型数据

某些类型的数据并不直接与内置类型一致。低于秒级精度的时间戳就是一个例子。

另一个例子是人们通常使用VARCHAR(15)来存储IP地址。然而,它们实际是32位无符号整数,不是字符串。用小数点将字段分割成四段是为了阅读方便。所以应该用无符号整数存储IP地址。MySQL提供INET_ATON()INET_NTOA()函数在这两种表示方法之间转换。

选择优化的数据类型

MySQL支持的数据类型非常多,选择正确的数据类型对于获得高性能至关重要。不管存储那种类型的数据,下面几个原则都有助于做出更好的选择。

更小的通常更好

一般情况下,应该尽量使用可以正确存储数据的最小数据类型(例如只需要存0-200,tinyint unsigned更好)。更小的数据类型通常更快,因为它们占用更少的磁盘、内存和CPU缓存,并且处理时需要的CPU周期也更少。

简单就好

简单数据类型的操作通常需要更少的CPU周期。例如,整型比字符操作代价更低,因为字符集和校对规则(排序规则)是字符串比较比整型比较更复杂。这里有两个例子:一个是应该用MySQL内建的类型(比如date,time,datetime)而不是字符串来存储日期时间,另外一个是应该用整型来存储IP地址。

尽量避免使用NULL

很多表都包含了可为NULL的列,即使应用程序不需要保存NULL也是如此,这是因为可为NULL是列的默认属性。通常情况下最好指定列为NOT NULL,除非真的需要存储NULL值。

如果查询中包含可为NULL的列,对MySQL来说更难优化,因为可为NULL的列使得索引、索引统计和值比较都更复杂。可为NULL的列会使用更多的存储空间,在MySQL里也需要特殊处理。当可为NULL的列被索引时,每个索引记录需要一个额外的字节,在MyISAM中甚至还可能导致固定大小的索引(例如只有一个整数列的索引)变成可变大小的索引。

通常把可为NULL的值改为NOT NULL带来的性能提升比较小,所以(调优时)没有必要首先在现有的schema中查找并修改掉这种情况,除非确定这会导致问题。但是,如果计划在列上建立索引,就应该避免设计成可为NULL的列。

当然也有例外,例如值得一提的是,InnoDB使用单独的位(bit)存储NULL值,所以对于稀疏数据(大部分值为NULL,只有少数行为非NULL的值)有良好的空间效率。但这一点不适用于MyISAM。


整数类型

如果存储整数,可以使用这几种整数类型:TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT。分别使用8,16,24,32,64位存储空间。它们的存储范围从 -2的(N-1)次方 到 2的(N-1)次方-1,其中N为存储空间的位数。

整数类型有可选的UNSIGNED属性,表示不允许负值,这大致可以使正数的上限提高一倍,例如TINYINT UNSIGNED可以存储的范围是0-255,而TINYINT 的存储范围是-128~127。

MySQL可以为整数类型指定宽度,例如INT(11),对大多数应用这是没有意义的:他不会限制值得合法范围,知识规定了MySQL的一些交互工具(例如MySQL命令行客户端)用来显示字符的个数。对于存储来说,INT(1)和INT(20)是相同的。

实数类型

实数是带有小数部分的数字。然而,它们不只是为了存储小数部分,也可以使用DECIMAL存储比BIGINT还大的整数。MySQL既支持精确类型,也支持不精确类型。

FLOAT 和 DOUBLE 类型支持使用标准的浮点运算进行近似计算。如果需要知道浮点运算时怎么计算的,则需要研究所使用的平台的浮点数的具体实现。

DECIMAL 类型用于存储精确的小数。但因为CPU不支持对DECIMAL的直接计算,所以在MySQL5.0及更高版本中,MySQL服务器自身实现了DECIMAL的高精度计算。相对而言,这比CPU直接支持原生浮点数运算要慢。

浮点和DECIMAL类型都可以指定精度。对于DECIMAL列,可以指定小数点前后所允许的最大位数。这会影响列的空间消耗。

浮点类型在存储同样范围的值时,通常比DECIMAL使用更少的空间。FLOAT使用4个字节存储。DOUBLE占用8个字节,相比FLOAT有更高的精度和更大的范围。

因为需要额外的空间和计算开销,所以应该尽量只在对小数进行精确计算时才使用DECIMAL——例如存储财务数据。但数据量比较大的时候,可以考虑使用BIGINT代替DECIMAL,将需要存储的货币单位根据小数点的位数乘以相应的倍数即可。假设要存储财务数据精确到万分之一分,则可以把所有金额乘以100W,然后将结果存储在BIGINT里,这样可以同时避免浮点存储计算不精确和DECIMAL精确计算代价高的问题。

字符串类型

下面的描述假设使用的存储引擎是InnoDB/或者MyISAM。如果不是这两种存储引擎的,请参考所使用的存储引擎的文档。

VARCHAR和CHAR

VARCHAR:它比定长类型更节省空间,因为它仅使用必要的空间。VARCHAR节省了空间,所以对性能也有帮助。但是由于行是变长的,在UPDATE时可能使行变得比原来更长,这就导致需要做额外的工作。

下面的情况使用VARCHAR是合适的:字符串最大长度比平均长度大很多;列的更新少,所以碎片不是问题;使用了像UTF-8这样复杂的字符集,每个字符使用不同的字节数。

在5.0或更高的版本中,MySQL在存储和检索时会保留末尾空格。InnoDB则更灵活,它可以把长的VARCHAR存储为BLOB

CHAR: 定长,当存储CHAR值时,MySQL会删除所有的末尾空格。定长的CHAR类型不容易产生碎片,对于非常短的列,CHAR比VARCHAR在存储空间上也更有效率,VACHAR还有一个或两个记录长度的额外字节。CHAR适合存储很短的字符串,或者所有值都接近同一个长度。例如:CHAR非常适合存储密码的MD5值,因为这是一个定长的值。CHAR会根据需要采用空格填充以方便比较。

与CHAR和VARCHAR类似的类型还有BINARY和VARBINARY,它们存储的是二进制字符串。二进制字符串中存储的是字节码而不是字符。

二进制比较的优势并不仅仅体现在大小写敏感上。MySQL比较BINARY字符串是,每次按一个字节,并且根据该字节的数值进行比较。因此,二进制比字符比较简单的多,所以也就更快。

BLOB 和 TEXT类型

BLOB和TEXT类型:BLOB和TEXT都是为了存储很大的数据而设计的字符串数据类型,分别采用二进制和字符方式存储。当BLOB和TEXT值太大时,InnoDB会使用专门的”外部”存储区域来进行存储。原表字段存储指针指向外部存储区域。

MySQL对BLOB和TEXT列进行排序与其他类型是不同的:它只对列最前max_sort_length 字节而不是整个字符串做排序。如果只需要排序前面一小部分字符,则可以减小max_sort_length 的配置,或者使用ORDER BY SUSTRING(column, length)

MySQL不能将BLOB和TEXT列全部长度的字符串进行索引,也不能使用这些索引消除排序。

使用枚举(ENUM)代替字符串类型

可以使用枚举(ENUM)代替字符串类型。很多时候建议使用枚举列代替常用的字符串类型。

(1)枚举列可以把一些不重复的字符串存储成一个预定义的集合。
(2)Mysql在存储枚举时非常紧凑,会根据列表值的数量压缩到一到两个字节中。  
(3)Mysql在内部会将每个值在列表中的位置保存为整数,并且在表的.frm文件中保存“数字-字符串”映射关系的“查找表”。

注意:有一个令人吃惊的地方是,枚举字段是按照内部存储的整数而不是定义的字符串进行排序的。

注意:枚举最不好的地方是:字符串列表是固定的,添加或者删除字符串必须使用ALTER TABLE,因此对于一系列未来可能会改变的字符串,使用枚举并不是一个好主意,除非接受只能在列表末尾添加元素。

注意:由于Mysql把每个枚举值保存为整数,并且必须进行查找才能转换为字符串,所以枚举列有一些开销。

日期时间类型

数据类型及用法详见 : //m.sbmmt.com/

Mysql有很多类型可以保存日期和时间值,比如YEAR和DATE。

Mysql能存储的最小时间粒度为秒(MariaDB支持微秒级别的事件类型)。但是Mysql也可以使用微秒级别的粒度进行临时运算。

大部分时间类型都没有替代品,因此没有什么是最佳选择的问题。

接下来唯一的问题是保存日期和时间的时候需要做什么。

DATETIME

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

TIMESTAMP

(1)TIMESTAMP类型保存了从1970年1月1日午夜以来的秒数,它和UNIX时间戳相同。 (2)TIMESTAMP只使用4个字节的存储空间,因此它的范围比DATETIME小得多。 (3)TIMESTAMP显示的值依赖时区。

DATETIME和TIMESTAMP的对比:

(1)默认情况下,如果插入时没有指定第一个TIMESTAMP列的值,Mysql则设置这个列的值为当前时间。(这是DATETIME没有的特性) (2)在插入一行记录时,Mysql默认也会更新第一个TIMESTAMP列的值。 (3)TIMESTAMP列默认为NOT NULL,这与其他的数据类型不一样。

总结

(1)除了特殊行为之外,通常也应该尽可能使用TIMESTAMP,因为它比DATETIME空间效率更高。 (2)一般来讲不建议把UNIX时间戳保存为整数值,这不会带来任何收益,用整数保存时间戳格式通常不方便处理。 (3)如果需呀存储比秒更小粒度的日期和时间值,可以使用BIGINT类型存储微秒级别的时间戳,或者使用DOUBLE存储秒之后的小数部分,也可以用MariaDB替代Mysql。

位数据类型

MySQL有少数几种存储类型使用紧凑的位存储数据。所有这些位类型,不管底层存储格式和处理方式如何,从技术上来说都是字符串类型的。

BIT

可以使用BIT列在一列中存储一个或多个true/false值。BIT(1)定义了一个包含单个位的字段,BIT(2)存储2个位,依次类推。BIT列的最大长度是64位。

如果想在一个bit的存储空间中存储一个true/false值,另一个方法是创建一个可以为空的CHAR(0)列。该列可以保存空值(NULL)或者长度为零的字符串(空字符串)。

SET

如果需要保存很多true/false 值,可以考虑合并这些列到一个SET 数据类型,它在MySQL 内部是以一系列打包的位的集合来表示的。这样就有效地利用了存储空间,并且MySQL 有像FIND_IN_SET() 和FIELD() 这样的函数,方便地在查询中使用。它的主要缺点是改变列的定义的代价较高:需要ALTER TABLE,这对大表来说是非常昂贵的操作。一般来说,也无法在SET 列上通过索引查找。

一种替代SET 的方式是使用一个整数包装一系列的位。例如,可以把8 个位包装到一个TINYINT 中,并且按位操作来使用。可以在应用中为每个位定义名称常量来简化这个工作。

比起SET,这种办法主要的好处在于可以不使用ALTER TABLE 改变字段代表的”枚举”值,缺点是查询语句更难写,并且更难理解(当第5 个bit 位被设置时是什么意思?)。一些人非常适应这种方式,也有一些人不适应,所以是否采用这种技术取决于个人的偏好。

选择标识符(identifier)

为identifier(标识列)选择合适的数据类型非常重要。

一般来讲更有可能用标识列与其他值进行比较,或者通过标识列寻找其他列。

当选择标识列的类型时,不仅仅需要考虑存储类型,还需要考虑Mysql对这种类型怎么执行计算和比较。

一旦选定了一种类型,要确保在所有关联表中都使用同样的类型。

在可以满足值的范围需求,并且预留未来增长空间的前提下,应该选择最小的数据类型。

  • 整数通常是标识列最好的选择,因为它们很快而且可以使用AUTO_INCREMENT

  • ENUM和SET是最糟糕的选择了;

  • 如果可能也尽可能避免使用字符串作为标识列,因为它们很消耗空间并且通常比数字类慢。

特殊类型数据

某些类型的数据并不直接与内置类型一致。低于秒级精度的时间戳就是一个例子。

另一个例子是人们通常使用VARCHAR(15)来存储IP地址。然而,它们实际是32位无符号整数,不是字符串。用小数点将字段分割成四段是为了阅读方便。所以应该用无符号整数存储IP地址。MySQL提供INET_ATON()INET_NTOA()函数在这两种表示方法之间转换。

 以上就是【MySQL】MySQL的数据类型优化的内容,更多相关内容请关注PHP中文网(m.sbmmt.com)!


相关标签:
来源:php.cn
本站声明
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系admin@php.cn
热门教程
更多>
最新下载
更多>
网站特效
网站源码
网站素材
前端模板