MySQL之数据类型优化

Posted on | In |

常见的数据库性能优化方案有优化SQL，创建索引，创建缓存等，但创建表结构时选择合适的数据类型则很容易被忽略。良好的逻辑设计和物理设计是高性能的基石，MySQL支持的数据类型很多，选择正确的数据类型对性能的影响很大。

一般情况下，应该尽量选择可以正确存储数据的最小数据类型，比如：只存储0-255之间的整数，tinyint unsigned更合适。更小的数据类型通常更快。因为它们占用更少的磁盘、内存和CPU缓存，并且处理时的CPU周期也更少。下面就简单介绍一下数据类型方面的优化。

数值类型

数值类型包括整数和实数，下面会分别介绍这两种数据的类型选择。

整数

存储整数整数可以使用下面这几种类型，占用的存储空间和取值范围如下：

类型 位数 范围
tinyint 8 -2^7 ~ 2^7
smallint 16 -2^15 ~ 2^15
mediumint 24 -2^23 ~ 2^23
int 32 -2^31 ~ 2^31
bigint 64 -2^63 ~ 2^63

整数有UNSIGNED属性，表示不允许出现负数，对于tinyint类型取值范围 -128 ~ 127，tinyint unsigned 的存储范围为 0 ~ 255。

有一点需要注意，MySQL可以为整数指定宽度，例如int(10), 但这对大多数应用来说没有意义。因为它不会限制值的范围，只指定显示长度。

实数

实数不仅可以存储带有小数的数字，还可以存储超过 bigint 范围的整数。

MYSQL中, float, double, decimal 都可以存储小数，但是float 和 double 存储的是近似值，对于money等对精度要求高的场合不适用。 它们 都可以指定精度，对于decimal列， 还可以指定小数点前后的最大位数，但这会影响列的空间消耗。

DECIMAL列的声明语法是 DECIMAL(M，D), 参数的值范围如下:

  • M是最大位数（精度）, 它的范围是 1 到 65。
  • D是小数点右边的位数（刻度）, 它的范围为 0 到 30，且不得大于M.

DECIMAL列的值使用二进制格式存储，该格式将九个十进制数字打包为4个字节。 例如，DECIMAL（18,9）在小数点的两侧各有九个数字，因此整数部分和小数部分每个需要4个字节。

float 类型在存储同样范围的值时，通常比 decimal 使用更少的空间。float 使用4个字节存储，double 使用8个字节。计算中 decimal 会转换为 double 类型。

因为需要额外的空间和计算开销，所以应该尽量只在对小数进行精确计算时才使用 decimal – 例如财务数据。 在数据量比较大的时候，可以考虑使用 bigint 代替 decimal， 将需要存储的货币单位根据小数位数乘以相应的倍数，然后将结果存储在 bigint 里。这样可以避免浮点计算不精确以及 decimal 精确计算代价高的问题。

字符串类型

varchar 和 char 类型

varchar 和 char 是两种主要的字符串类型。

  • varchar
    • varchar用于存储可变长字符串，比定长类型更节省空间，因为它仅使用必要的空间（MySQL表使用row_format = fixed 时例外）。
    • varchar 需要使用 1 或 2 个额外字节记录字符串长度。如果列长度小于或等于255字节， 则只使用 1 个字节表示，否则使用 2 个字节。
    • varchar 节省了存储空间，对性能有好处。但是由于是变长的，在 UPDATE 时可能会变得比原来更长，导致需要做额外的工作。如果一个行占用的空间增长，并且页内没有更多的空间可以存储，InnoDB 需要分裂页来使行可以放进页内， MyISAM 则会将行拆成不同的片段存储。
    • 适合场景：字符串列的最大长度比平均长度大很多，列更新很少的情况。还有使用了像 UTF-8 这样复杂的字符集，每个字符都使用了不同的字节数进行存储。
    • MySQl 5.0 以上版本，在存储和检索时会保留末尾空格。
  • char
    • char 类型是定长的，MySQL 根据定义的字符串长度分配足够的空间。
    • 适合存储很短的字符串，或者所有的值都接近同一个长度。例如， 存储密码的 MD5 值， 因为这是一个定长的值。对于经常变更的数据，char 比 varchar 更好，因为定长的char 类型不容易产生碎片。例如用 char(1) 存储单字符集只需要 1 个字节，但是 varchar(1) 需要2个字节，其中一个用来记录长度。
    • 当存储 char 值时，MySQL 会删除所有的末尾空格。

blob 和 text 类型

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

与其他类型不同，MySQL 把每个blob 和 text 值当做一个独立的对象处理。存储引擎在存储时通常会做特殊处理。当blob 和 text 值太大时，InnoDB 会使用专门的 “外部”存储区域来存储，此时每个值在行内需要 1 ~ 4 个字节存储指针。

blob 和 text 不同之处是 blob 存储的是二进制数据，没有排序规则或字符集，而 text 类型有字符集和排序规则。

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

日期和时间类型

MySQL支持5中时间和日期类型，它们的类型、占用空间和取值范围如下：

类型 字节 格式 范围
YEAR 1 YYYY 1901 ~ 2155
TIME 3 HH:MM:SS -838:59:59 ~ 838:59:59
DATE 4 YYYY-MM-DD 1000-01-01 ~ 9999-12-31
TIMESTAMP 4 YYYY-MM-DD HH:MM:SS 1970-01-01 00:00:00 ~ 2038 某个时刻
DATETIME 8 YYYY-MM-DD HH:MM:SS 1000-01-01 00:00:00 ~ 9999-12-31 23:59:59
  • DATETIME
    • 能保存大范围的值，从1001 到 9999年， 占用 8 个字节。
  • TIMESTAMP
    • 保存了从 1970年1月1日午夜以来的秒数。 占用 4 个字节。但是范围比较小，只能表示从 1970 到 2038 年。
    • 显示的值依赖于时区。日期需要国际化处理时可以使用此类型。
    • 支持更新数据时，MySQL自动将系统的当前时间设置到TIMESTAMP 列，

与 DATETIME 相比， 通常应该尽量使用 TIMESTAMP 类型， 因为空间利用率高。

标识列（identifier）

标识列是指可以不用手动的插入值，系统提供默认的序列值。

为标识列选择合适的数据类型非常重要。一般来说很有可能使用标识列与其他列值进行比较，获取通过标识列查找其他列。也有可能在另外的表中作为外键使用，所以应该选择跟关联表中对应列一样的类型。

整数通常是标识列最好的选择，因为它们很快并且可以使用 AUTO_INCREMENT 。如果有可能，尽量避免使用字符串类型作为标识列，因为它们很消耗空间，并且通常比数字类型慢。

如果存储 UUID 值，则应该移除 “-“ 符号。或者更好的做法是，用 UNHEX () 函数将移除 “-“ 符号后的 UUID 转换为16进制的数字，存储在 BINARY(16) 列中。检索时通过 HEX() 函数来格式化为16进制格式。

示例：

1
2
create table users(id_bin binary(16), name varchar(20));
insert into users values(unhex(replace(uuid(),'-','')), 'zhansan');

特殊类型

特殊类型并不与 MySQL 内置类型一致。比如存储比秒级更小粒度的日期和时间值时，MySQL 并没有提供合适的数据类型。可以使用 bigint 类型存储微秒级别的时间戳。

比如 IPV4 地址，很多人使用 varchar(15) 来存储。然而，它其实是一个 32 位无符号整数，不是字符串，使用小数点分隔开只是为了方便阅读。所以可以使用无符号整数来存储 IP 地址。MySQL 提供了 INET_ATON() 和 INET_NTOA() 函数进行转换。

  • INET_ATON(): 将字符串的网络地址，转换为整数。

  • INET_NTOA(): 将整数类型网络地址，转换为字符串地址。