来自 新葡亰数据库 2019-12-05 05:36 的文章
当前位置: 澳门新葡亰app > 新葡亰数据库 > 正文

一、类型介绍,更小的数据类型通常更快

MySQL支持的数据类型很多,那么选择合适的数据类型对于获得高性能就至关重要。那么就先了解各种类型的优缺点!

选择数据类型的原则

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

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

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

InnoDB使用单独的位(bit)存储NULL值,所以对于稀疏数据有很好的空间效率。

一、类型介绍

整数类型

TINYINT,SMALLINT,MEDIUMINT,INT,BIGINT。分别使用8,16,24,32,64位存储空间。它们可以存储的值的范围从-2的(N-1)次方到2的(N-1)次方减一,其中N是存储空间的位数。

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

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

1、整型类型

实数类型

实数是带有小数部分的数字。但是不只是为了存储小数,也可以用DECIMAL存储比BIGINT还大的整数。

FLOAT和DOUBLE类型支持使用标准的浮点运算进行近似计算。DECIMAL类型用于存储精确的小数,并且它可以指定小数点前后的所允许的最大位数。这会影响列的空间消耗。在较高的版本将会把数字打包保存到一个二进制字符串中(每4个字节存9个数字)。例如DECIMAL(18,9)小数点两边将各存储9个数字,一共使用9个字节:小数点前的数字用4个字节,小数点后的数字用4个字节,小数点本身占1个字节。而DECIMAL类型允许最多65个数字,注意DECIMAL只是一种存储格式,计算时会转换为DOUBLE类型。DOUBLE是MySQL内部计算类型。

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

由于消耗问题。所以应该有小数精确计算时才用DECIMAL。另外,某些场合可以考虑用BIGINT代替DECIMAL,比如存储财务数据,可以将需要存储的货币单位根据小数的位数乘以相应的倍数,然后用BIGINT存储。

  整型类型有: TINYINT,SMALLINT,MEDIUMINT,INT,BIGINT 。他们分别占8,16,24,32,64位储存空间。可存储的整数范围为-2^(N-1)到2^(N-1)-1,其中N是存储空间的位数。

字符串类型

VARCHAR和CHAR类型

VARCHAR:VARCHAR类型用于存储可变长字符串,因此它仅使用必要的空间,它比定长类型更节省空间。VARCHAR需要使用1或2个额外字节记录字符串的长度,如果列的最大长度小于或等于255字节,则只使用1个字节表示,否则使用2个字节。

VARCHAR节省了存储空间,所以对性能也有帮助。但是,由于行是变长的,在UPDATE时可能使行变得更长,这就需要做额外的工作。如果一个行占用的空间增长,并且在页内没有更多的空间可以存储,这种情况下,不同的存储引擎的处理方式不一样。例如MyISAM会将行拆成不同的片段存储,InnoDB则需要分裂页来使行可以放进页内。这样一来就会产生很多内存碎片。

根据上面的描述可以确定VARCHAR适合的情况:

1.字符串列的最大长度比平均长度大很多;

2.列的更新很少所以碎片不是问题;

3.使用了像UTF-8这样复杂的字符集,每个字符都使用不同的字节数进行存储。

CHAR:CHAR类型是定长的,MySQL总是根据定义的字符串长度分配足够的空间。当存储CHAR值时,MySQL会删除所有的末尾空格

1.CHAR适合存储很短的字符串,或所有值都接近同一个长度。例如,CHAR非常适合存储密码的MD5值,因为这是一个定长的值。

2.对于经常变更的数据,CHAR也比VARCHAR更好,因为定长的CHAR类型不容易产生碎片。

3.对于非常短的列,CHAR也比VARCHAR在存储空间上也更有效率。例如用CHAR(1)来存储只有Y和N的值,如果采用单字符集只需一个字节,但是VARCHAR(1)却需要两个字节,因为还有一个记录长度的额外字节。

注:使用VARCHAR(5)和VARCHAR(200)存储较小的数据时,虽然空间开销一样,但是还是用VARCHAR(5)比较好。因为更长的列会消耗更多的内存,MySQL通常会分配固定大小的内存块来保存内部值。所以最好的策略就是分配真正需要的空间。

还可以将整数类型设为 UNSIGNED ,这样几乎可以是其范围增大一倍。例如TINYINT范围是-128

  • 127,而TINYINT UNSIGNED的范围是0-255。不过这两种只是在范围上有缺别,在存储空间和性能上都是一样的。

日期和时间类型

DATETIME

这个类型能保存大范围的值,从1001年到9999年,精度为秒。他把日期和时间封装到格式为YYYYMMDDHHMMSS的整数中,与时区无关。使用8个字节的存储空间。默认情况下,MySQL以一种可排序的格式显示DATETIME值。

TIMESTAMP

保存了从1970年1月1日午夜以来的秒数。它只使用4个字节的存储空间,因此它的范围比DATETIME小的多。

TIMESTAMP显示的值也依赖于时区,MySQL服务器、操作系统,以及客户端连接都有时区设置。

应该多用TIMESTAMP因为它比DATETIME空间效率更高。

澳门新葡亰官网APP, 

2、实数类型

  对于实数类型,MySQL即支持精确类型(DECIMAL),也支持不精确类型(FLOAT,DOUBLE)。

  DECIMAL类型允许最多存储65位数字,因此它可以存储比BIGINT还大的数字。而且在MySQL5.0或更高版本中,MySQL服务器自身实现的DECIMAL的高精度计算。但相比较浮点类型,因为CPU直接支持原声浮点计算,所以浮点类型计算会更快。

  通常来说,浮点类型在存储相同的范围时,比DECIMAL使用更少的空间。FLOAT占用4个字节存储,DOUBLE占用8个字节存储,但相比FLOAT有更高的精度和更大的范围。浮点类型存储时在精度上会有各种各样的问题,例如当你只把一列设为FLOAT,而没有指定精度时,在存储1234567.33会变成1234570。

  DECIMAL所占的字节比较特殊。它是在小数点前后分别使用每4个字节存储9位数字。具体看mysql手册说法:

澳门新葡亰官网APP 1

所以我们使用最多的DECIMAL(10,2)所占的字节数为1+4+1+1=7个字节(小数点占一个字节)。

因为需要额外的计算开销和存储空间,所以应该尽量只在对小数进行精确计算时才使用DECIMAL--例如存储财务数据。当你的数据量比较大的时候,为了避免浮点存储计算不精确和DECIMAL精确计算代价高的问题,可以使用BIGINT代替DECIMAL,只需将原来需要存储的小数乘以相应的倍数即可(BIGINT的范围满足你的需求)。

 

3、字符串类型

澳门新葡亰app,  VARCHAR类型用于存储可变长的字符串,所以它需要1或2个额外的字节记录字符串的长度:如果列的长度小于或等于255个字节,则只使用1个字节表示,否则使用2个字节表示。例如varchar(10)就需要11个字节,varchar(1000)则需要1002个字节。

  VARCHAR节省了存储空间,所以对性能有所帮助。但由于行是变长的,在UPDATE时可能是原来的行更长,这就会导致需要做一些额外的工作。如果一个行占用的空间曾长,并且在页内没有更多的空间可以存储,这是INNODB就会分裂当前页来使行可以放进页内。

  下面这些情况使用VARCHAR是合适的:

  • 字符串列的最大长度比平均长度大很多
  • 列的更新很少
  • 使用了UTF-8这样的字符集,每个字符都是用不同的字节存储

 

  CHAR类型是定长的:MySQL总是根据定义字符串的长度分配足够空间。因为CHAR会根据需要采用空格填充到字符串末尾,而且当你检索时,CHAR会删除末尾的空格。所以会有一个很有趣的事情发生,当你存储一个"Johnson  "到char(10)时,检索出来的结果却是"Johnson",因为MySQL并不知道这空格是你存的还是系统自动填充的。

  CHAR很适合存储很短的字符串或所有值都接近同一个长度。例如密码的MD5值。

 

  BLOB和TEXT都是为了存储很大的数据类型而设计的字符串数据类型,分别采用二进制和字符方式存储。而且当它们存储的数据过大时,INNOSB会使用专门的‘外部’空间来存储数据,此时每个值的行内仅存储一个1 ~ 4个字节的指针,然后在外部区域存储真实的指。当需要对BLOB和TEXT排序时,它只对每个列的最前 max_sort_length 进行排序。这个值是可以配置的。

 

4、 枚举类型

  有时候可以使用枚举类型代替常用的字符串类型。MySQL在内部会将每个值在列表中的位置保存为整数,并且在表的.frm文件中保存“数字-字符串”的映射关系。比如性别列,就可以用enum(男,女,未知),这里有些人可能用TINYINT代替枚举,实际我感觉这并不能带来性能的优化,只不过你把“数字-字符串”的映射关系搬到你的业务逻辑中处理,如果你的注释写的不清晰,反而会给新人带来困惑。

  对于弱类型语言来说,枚举并不是狠友好。举个栗子:select id,name from users where id = 1; 和 select id,name from users where id = ‘1’; 得到的结果是一样的。因为ENUM内部存储是用的整型,所以在检索ENUM类型时也可以用整数,例如 select id,name from users where sex = 1; 和 select id,name from users where sex = '男'; 可以得到相同的结果。但

select id,name from users where sex = ‘1’;

就检索不到任何值。但如果你设计和使用的好,依然可以用。

 

5、日期和时间类型

  日期和时间类型共有:DATE、TIME、YEAR、DATETIME和TIMESTAMP。其中DATE、TIME、YEAR分别占4,3,1字节,并且存储的时间格式为YYYY-MM-DD,HH:MM:SS,YYYY。这三种日期和时间类型相对用的比较少。这里主要介绍DATETIME和TIMESTAMP的区别。

  DATETIME存储的范围大,从1001到9999年,精度为秒,存储格式为YYYY-MM-DD HH:MM:SS,占8个字节的存储空间。

  TIMESTAMP存的范围要小很多,从1970年到2038年(快超出范围了),精度为秒,存储格式也为YYYY-MM-DD HH:MM:SS,但只占4个字节的存储空间。TIMESTAMP默认为NOT NULL,并且当插入时没有指定该列值时,会默认把MySQL当前时间插入进去。除了特殊行为,否则应该尽量使用TIMESTAMP,因为它比DATETIME占更少的存储空间。

  这里要特殊说明的是,有时候有的人会将Unix时间戳存储为整数(我们公司就是这么干滴),但这不会带来任何收益。反而用整数保存时间戳的格式通常不方便处理,所以不推荐这样做。

 

二、选择优化的数据类型

  • 更小的更长更好

    一般情况下,应该尽量使用可以正确存储数据的最小数据类型。例如只需要存0~200的值,显然TINYINT UNSIGNED就足够了。更小的数据类型更快,因为它们占用更少的磁盘、内存和CPU,并且处理时需要的CPU周期也更少。

  • 简单就好

    简单的数据类型的操作通常需要更少的CPU周期。例如应该用整型存储IP而不是字符串。

  • 尽量避免NULL

    可为NULL的列会使用更多的存储空间,在MySQL里也需要特殊的处理。但可为NULL的列被索引时,每个索引记录需要一个额外的字节。

本文由澳门新葡亰app发布于新葡亰数据库,转载请注明出处:一、类型介绍,更小的数据类型通常更快

关键词: