mysql-数据类型优化

数据类型优化

更小的通常更好

一般情况下,应该尽量使用可以正确存储数据的最小数据类型。更小的数据类型通常更快,因为占用更小的磁盘、内存和CPU缓存。

简单就好

简单数据类型的操作通常需要更少的CPU周期。如整型比字符串操作代价更低。

尽量避免NULL

如果查询中包含可为NULL的列,对MySQL来说,优化很难。通常将为NULL的列设置为 NOT NULL带来的提升效果比较小,如果计划在此列上建立索引,就尽量避免NULL。

整数类型

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

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

有符号和无符号使用相同的存储空间,并具有相同的性能。

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

实数类型

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

FLOAT和DOUBLE类型支持使用标准的浮点运算进行近似计算。

DECIMAL类型用于存储精确的小数。支持精确计算。

CPU不支持DECIMAL的直接计算,所以MySQ服务器自身实现了 DECIMAL 的高精度计算。相对而言,CPU直接支持原生浮点计算,所以浮点运算明显更快。

浮点和DECIMAL都支持指定精度。对于DECIMAL来说,支持指定小数点前后所允许的最大位数。

因为需要额外的空间和计算开销,所以应该尽量只对小数进行精确计算时才使用DECIMAL。

字符串类型

MySQL支持多种字符串类型,每种类型还有很多变种。

VARCHAR

VARCHAR类型用于存储可变长字符串,是最常见的字符串类型。

VARCHAR需要使用1或2个额外字节记录字符串的长度:如果列的最大长度小于或等于255字节,则只使用1个字节表示,否则使用2个字节。

VARCHAR节省了存储空间,所以对性能也有帮助。但是行是变长的,在UPDATE时可能使行变得比原来更长,这就导致需要做额外的工作。如果一个行占用空间增长,并且在页内没有更多的空间可以存储,在这种情况下,InnoDB则需要分裂页来使行可以放进页内。

适用情况:字符串列的最大长度比平均长度大很多;列的更新很少,所以碎片不是问题。

CHAR

CHAR类型是定长的:MySQL总是根据定义的字符串长度分配足够的空间。

CHAR类型适合存储很短的字符串,或者所有值都接近一个长度。如,CHAR非常适合存储密码的MD5值,因为这是一个定长的值。对于经常变更的数据,CHAR也比VARCHAR更好,因为定长的CHAR类型不容易产生碎片。对于非常短的类,CHAR比VARCHAR更好,因为存储空间上更有效率。如CHAR(1)用来存储只有Y和N的值。

BLOB和TEXT

BLOB和TEXT都是为了存储很大的数据而设计的字符串数据类型 ,分别采用二进制和字符方式存储。

实际上,它们分别属于两组不同的数据类型家族:字符串类型是:TINYTEXT、SMALLTEXT、TEXT、MEDIUMTEXT、LONGTEXT。对应的二进制类型是 TINYBLOB、SMALLBLOB、BLOB、MEDIUMBLOB、LONGBLOB。BLOB是SMALLBLOB的同义词,TEXT是SMALLTEXT的同义词。

MySQL对BLOB和TEXT列进行排序与其他类型是不同的:它只对每个列最前面的 max_sort_length 字节而不是整个字符串进行排序。

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

日期和时间类型

MySQL可以使用许多类型来保存日期和时间值。大部分时间类型都没有替代品,因此没有什么是最佳选择的问题。

DATETIME

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

TIMESTAMP

TIMESTAMP保存了从1970年1月1日以来的秒数,它和UNIX时间戳相同。TIMESTAMP只使用4个字节的存储空间,因此它的范围比DATETIME小的多:只能从1970年到2038年。

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

默认情况下,如果插入时没有指定第一个TIMESTAMP列的值,MySQL则设置这个列的值为当前时间。在插入一行记录时,MySQL默认也会更新第一个TIMESTAMP列的值(除非在UPDATE语句中明确指定了值)。

TIMESTAMP默认为NOT NULL。

如果需要存储比秒更小粒度的日期和时间值该怎么办?MySQL没有提供合适的数据类型。但可以使用BIGINT类型存储微秒级别的时间戳。

主键标识

为主键列选择一个合适的数据类型非常重要。一般来说更有可能使用标识列与其他值进行比较。

整数类型

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

字符串类型

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

对于完全“随机”的字符串来说,会导致INSERT以及一些SELECT语句变的很慢。

  • 因为插入值会随机地写到索引的不同位置,所以使得INSERT语句变慢。这会导致页分裂、磁盘随机访问,以及对于聚簇存储引擎产生聚簇索引碎片。
  • SELECT语句会变慢,因为逻辑上相邻的行会分布在磁盘和内存的不同地方。
  • 随机值导致缓存对所有类型的查询语句效果都很差。

如果存储UUID值,则应该移除 “-“ 符号;或者更好的做法是,用 UNHEX() 函数转换UUID值为16字节的数字,并且存储在一个 BINARY(16)列中。检索时可以通过 HEX() 函数来格式化为十六进制格式。

ALTER TABLE

一般而言,大部分 ALTER TABLE操作将导致MySQL服务中断。对于常见的场景:一种是先在一台不提供服务的机器上执行 ALTER TABLE操作,然后和提供服务的主库进行切换。

不是所有的 ALTER TABLE都会引起表重建。有两种方式可以改变或者删除一个列的默认值。

下面是很慢的方式:

1
ALTER TABLE table modify column c1 tinyint(3) not null defalut 5;

SHOW STATUS显示这个语句做了1000次读和1000次插入操作。它拷贝了整张表到一张新表,甚至列的类型、大小和可否为NULL的属性都没有改变。

下面是很快的方式:

1
ALTER TABLE table alter column c2 set default 5;

这个语句会直接修改.frm文件而不涉及表数据。

0%