高性能MySQL(三):Schema与数据类型优化

高性能MySQL(三):Schema与数据类型优化_第1张图片

文章目录

    • 选择优化的数据类型
      • 整数类型
      • 字符串类型
      • BLOG 和 TEXT 类型
      • 使用枚举(ENUM)代替字符串
    • MySQL schema设计中的问题

选择优化的数据类型

MySQL支持的数据类型非常多,选择正确的数据类型对于获得高性能至关重要。

下面几个简单的原则有助于做出更好的选择:

更小的通常更好
简单就好
避免NULL

本篇默认存储引擎是InnoDB


整数类型

有两种类型的数字:整数和实数。
如果存储整数,可以使用这几种整数类型:TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT。分别使用8,16,24,32,64位存储空间。它们可以存储的范围从-2^(N-1)到2^(N-1)-1

整数类型有可选的UNSIGNED属性,表示不允许负值,这大致可以使整数的上限提高一倍。有符号和无符号类型使用相同的存储空间,并具有相同的性能,因此可以根据实际情况选择合适的类型。

实数的话,DECIMAL。


字符串类型

VARCHAR和CHAR是主要的字符串类型。

VARCHAR:
通常用于存储可变长字符串,是最常见的字符串数据类型。它比定长类型更节省空间,因为它仅使用必要的空间。
VARCHAR会使用一个或两个字节来存储空间的大小,但是,由于行是变长的,在UPDATE的时候就比较麻烦了。如果一个行占用的空间增长,但是这一页没有更多的空间可以使用了,InnoDB会需要分裂页来使行可以放进页内。

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

CHAR:
CHAR类型是定长的,当存储CHAR值时,MySQL会删除所有的末位空格。CHAR值会根据需要采用空格进行填充以方便比较。

CHAR适合存储很短的字符串,或者所有的值都接近一个长度。对于经常变更的值,CHAR 也比VARCHAR要好,因为定长的CHAR类型不容易产生碎片。对于非常短的列,CHAR也比VARCHAR更有效率,例如就存一个字符的时候,VARCHAR还要有一个字节来记录长度。


再次重申:数据如何存储取决于存储引擎,而本篇我们只讲InnoDB


BLOG 和 TEXT 类型

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

它们分别属于两组不同的数据类型家族:
TINYTEXT、SMALLTEXT、TEXT、MEDIUMTEXT、LONGTEXT
TINYBLOG、SMALLBLOG、BLOG、MEDIUMBLOG、LONGBLOG


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

有时候可以使用枚举列代替常用的字符串类型。
枚举列可以把一些不重复的字符串存储成一个预定义的集合。MySQL在存储枚举时非常紧凑,会根据列表值的数量压缩到一个或者两个字节中,MySQL会在内部将每个值在列表中的位置保存成整数,并且在表的.frm文件中保存 “数字 - 字符串”映射关系的查找表。

下面有一个栗子:
在这里插入图片描述
高性能MySQL(三):Schema与数据类型优化_第2张图片

尽量避免使用数字作为ENUM枚举常量。


MySQL schema设计中的问题

虽然有一些好的或换的的设计原则,但也有一些问题是由MySQL的实现机制导致的,这意味着有可能犯一些只在MySQL下发生的特定错误。

1、太多的列
从行缓冲中将编码过的列转换成数据结构的操作代价是非常高的。
如果计划使用数千个字段,必须意识到服务器的性能运行特征会有一些不同。

2、太多的关联
如果希望查询执行的快速且并发性好,单个查询最好在12个表以内做关联。

3、全能的枚举
应避免过过度使用枚举。


高性能MySQL(三):Schema与数据类型优化_第3张图片

你可能感兴趣的