MySQL之数据类型优化

mysql

mysql 数据类型优化

一、原则

1. 选择存储空间更小的类型

更小的数据类型，使用更少的磁盘、内存、CPU资源，处理起来更快，但需要确保没有低估存储值得范围，选择不超过范围的最小类型。

2. 选择更简单的类型

更简单的类型往往需要更少的计算，例如：

• 如果可以，使用整数代替字符串
• 如果可以，使用 MySQL 自带的日期类型代替字符串
• 如果可以，使用整数代替 ip 地址字符串

3. 列申明使用 not null 避免 null

在可能包含 null 值得列上，进行计算、索引都更复杂，也使得查询变得复杂。IS_NULL 函数。

二、整数类型

MySQL中用于存储整数的类型有：

• tinyint，占用：1 字节，范围：-2^8 到 2^8-1
• smallint，占用：2 字节，范围：-2^16 到 2^16-1
• mediumint，占用：3 字节，范围：-2^24 到 2^24-1
• int，占用：4 字节，范围：-2^32 到 2^32-1
• bigint，占用：8 字节，范围：-2^64 到 2^64-1
  此外，可以在列申明中，使用 unsigned 指定：值为正数，其范围可以增加到原来的 2 倍。

MySQL中对列指定宽度，如：int(11)，对于应用程序是没有意义的，它只是规定了与MySQL交互的工具，如：datagrip，在展示数据时，显示几个字符。

三、小数类型

MySQL中存储小数的类型有：float、double、decimal，但使用这些类型存在两个方面的问题：

• 运算时需要更多的空间和计算
• 运算结果的精度问题
  更合理的方法是：将值乘以十的倍数使值变为整数，然后使用整数类型存储。

四、字符串类型

MySQL支持的字符串类型是 varchar 和 char，具体在磁盘和内存中的存储方式因存储引擎的不同而不同。

1. varchar

• 优点：varchar 类型用于存储 变长字符串，相对于 char 来说，使用更少的空间。
• 问题：varchar 类型在更新时，如果长度变得很长且在页内没有更多的空间，将使得存储变得很复杂。InnoDB 使用分裂页的方式存储，MyISAM 则将值拆分成不同的片段存储。
• 何时选择 varchar：
  • 当字符串的最大长度比平均长度大很多。这意味着：能够最大程度发挥 变长 的优点。
  • 基本不更新或更新几率很小。这意味着：不需要太多的分裂页或者片段存储来解决页内空间不够的问题。
  • 使用了像 utf8 这样的复杂字符集，每个字符都使用不同的字节数存储。

2. char

• 优点：char 的一个重点特征是 定长，这意味着：不会产生页内存储不下的情况，即：不会有分裂页或者分段存储的情况。
• 缺点：char 的一个明显缺点是：当存储 ^string^^（^为空格）时，在获取值时，会发现后面的两个空格不见了！
• 何时选择 char：当所有值得长度都接近一个固定长度时，使用 char 使得空间利用效率更高，处理起来也更简单和高效，如：md5值。

3. char 和 varchar 长度选择

一般来说，我们使用一个：大于所有值的长度的数，作为字符串的长度。但：过分的慷慨会使得空间利用率不高，且在使用临时内存表进行排序时占用更多内存。

4. blob 系列类型和 text 系列类型

• blob 和 text 是为很长的字符串设计的存储类型，InnoDB 会使用专门的其他区域存储这些值，在行内值保留这个 外部区域 的指针。
• blob 和 text 的区别在于：blob 以二进制进行存储，没有字符集和排序规则，而 text 则有。
• 在对 blob 和 text 进行排序时，其实只对 max_sort_length 进行排序，并不会将整个值都进行排序。
• MySQL 不能将 blob 和 text 类型的全部长度进行索引。
• 当查询了 blob 和 text 类型同时又需要隐士临时表时（ explain: use temporary），MySQL 将使用磁盘临时表，这会造成严重的性能问题。一个可行的优化方式是：使用 substring(col, length) 来代替列的整体查询，此时将会使用 内存临时表，但此时需要考虑临时表不能超过 max_heap_table_size 和 tmp_table_size 的大小。

五、枚举

• 对于选择性很低的字符串列（distinct(filed)/count(1)）可以使用枚举来代替字符串，使得空间使用量更少且更紧凑。
• 枚举类型在行内使用整数存储，然后 .frm 文件中则存储了 整数 - 枚举值 的映射关系，这使得：枚举的排序是根据整数而非枚举值排序的。如：enum('b', 'a') 在升序排序时，b 在 a 前（b=1, a=2）。此时可以使用 field 指定排序规则。如：order by filed(enum, 'a', 'b')。当然：在声明枚举时，使用有序的枚举值，将避开这个问题。
• 缺点：
  • 在修改（增、删、改）枚举类型时，必须使用 alter table 来重建整个表，除非是：只在枚举最后进行追加。
  • 因为枚举存在整数和字符串的转化，使得：枚举和字符串关联时的效率还不如字符串和字符串关联。

六、时间类型

MySQL 用于存储时间的有：datetime 和 timestamp。其中：

• datetime 封装了一个 YYYYMMDDHHMMSS 格式的字符串到整数中，与时区无关，使用 8 个字节。
• timestamp 使用 4 个字节的整数存储距离 1970-01-01 00:00:00 的秒数，只能表示 1970-2038，且与时区有关。
• 此外：
  • 使用整数代替 timestamp 存储时间戳，没有任何收益，不仅不方便处理，还丢失了时区特性。
  • 使用字符串代替 datetime 存储 YYYYMMDDHHMMSS，将使用更多的存储空间，同时一个更小的整数在排序等操作时，必然比更大的字符串类型，更高效。