1.1.1. 字符串类型

最新推荐文章于 2024-09-11 10:18:51 发布

HopingWhite

最新推荐文章于 2024-09-11 10:18:51 发布

阅读量1.4k

点赞数

分类专栏：高性能MySQL 文章标签：存储 mysql byte 引擎 table 服务器

高性能MySQL 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

1.1.1. 字符串类型

MySQL支持好几种字符串数据类型，每一种又有好些变种。这些数据类型从4.1到5.0版本变化很大，这使得使用它们变得更为复杂。从MySQL4.1开始，每种字符串列可以设置独立的字符集以及排序规则。这可以在极大程度上影响性能。

1.1.1.1. VARCHAR和CHAR

最主流的两大字符中类型是VARCHAR和CHAR，它们都用于存储字符型的值。不幸的是，很难解释这些值是如何在内存和硬盘上进行存储的，因为实现是由存储引擎来完成的（比如Falcon为几乎每种数据类型都使用了它自己的存储格式）。接下来的讲述中，我们假设你使用的是InnoDB或者MyISAM。如果不是的话，你应该去阅读一下你所使用的存储引擎的文档。

我们先来看一下VARCHAR和CHAR是如何存储在硬盘上的。首先要明白一点，存储引擎可能将VARCHAR和CHAR以不同的格式存储在内存和硬盘之上，而服务器可能又会奖这些值转换为一种其他的格式。下面是对两种类型的一个比较全面的比较：

n VARCHAR

VARCHAR存储了变长字符串，同时也是最常使用的字符串类型。它比定长类型使用更少的存储空间，因为它只使用它所需要的空间（即存储的值越少使用的空间越小）。其中有一个例外是当MyISAM表用ROW_FORMAT=FIXED来创建时，它将会把每行的存储空间固定，因此会浪费空间。

VARCHAR使用1到2个额外的byte来记录整个字符串的长度：如果字符串的最大长度不大于255，则使用1个byte，否则的话使用2个byte。假设是拉丁字符集，那么VARCHAR(10)将会最多使用11个byte。VARCHAR(1000)将会最多使用1002个byte，因为它需要2个byte来存储长度。

VARCHAR可以节省空间，因此它可以提高性能。另一方面，由于行是变长的，当你更新这些字段时，就需要额外的工作。如果一行记录的长度增长了，并且在原来的位置已经放不下的话，那么它的作为取决于存储引擎。比如说，MyISAM可能将这一行进行碎片整理，而InnoDB可能将一行拆分到两页中。其他存储引擎可能根本就在别的地方更新记录了。

当最大长度远远大于平均长度时，或者当更新很少时而碎片不是问题时，或者当你使用一种复杂的字符集如UTF-8（它每个字符的长度是不定的），你就应该使用VARCHAR。

在5.0以及更新的版本中，MySQL保底了一些额外的空间，而4.1之间的版本都直接将这些空间去除了。

n CHAR

CHAR是定长的：MySQL总是为指定的字符分配足够的空间。当存储一个CHAR类型的值得时，MySQL去除额外的空间。（在4.1及其以前的版本中，CHAR和VARCHAR在逻辑上是一致的，只是存储格式有所不同）当需要比较时，这些值以空格填充。

当你需要存储很短的字符串或者存储的字符串基本上都是等长时，CHAR是很有用的。比如说，要存储MD5值的话，CHAR就是一个很好的选择，因为它是定长的。对于写频繁的列来说，CHAR也要优于VARCHAR，因为CHAR并不会产生碎片。对于很短的列来说，CHAR也比VARCHAR效率更高。CHAR(1)只用于存储Y或者N将只使用1个byte来存储，而VARCHAR(1)将需要2个。

这个行为比较诡异，因此我们来用一个例子展示一下。首先，我们创建一个只有一列CHAR(10)的表，并且存储一些数据进去：

当获取这些数据时，开头的结尾的空格已经被删除了。

如果我们存储同样的数据到VARCHAR(10)列，我们在查询时会得到如下的结果：

注意：长度是指字符的长度，而不是byte。一个宽字符集可能需要多于一个byte来存储每个字符。

数据如何存储取决于存储引擎，并不是所有的存储引擎都按同样的方式来处理定长和变长数据。内存存储引擎使用室长的行，因此它必须为每个变量分配足够长的空间即使它是一个变长列。另一方面，Falcon使用变长列即使是一个定长的CHAR列。但是开头和结尾的空格的处理各存储引擎是一致的，因为这是由MySQL服务器来完成的。

与CHAR和VARCHAR相邻的是BINARY和VARBINARY，它们可以存储二进制字符串。二进制字符串与传统字符串很类似，但是它们以byte而不是字符来进行存储。填充也是不同的，MySQL利用/0来填充BINARY在不是空格，而且在获取时也不删除填充的数据。

当你需要存储二进制数据并且希望MySQL以byte而不是字符来比较时这些类型是很有用的。基于byte的比较的优势并不仅仅在于非大小写敏感。实际上MySQL在比较BINARY字符串时，是根据每个byte的值，一个byte一个byte的进行比较的。而事实上，二进制比较要远比字符简单，因此它也就更快。

n 慷慨有时候可能是不明智的

利用VARHCAR(5)和VARCHAR(100)来存储’hello’需要使用同样的空间。那么使用短一点的列有什么好处呢？

事实上，它有很大的好处。越长的列会使用越大的内存，因为MySQL经常分配定长的内存块来存储这些数据。这对于那些使用内存临时表的操作，如比较，排序等尤为严重。同样的问题也会出现在需要使用文件排序的使用硬盘临时表中。最好的策略是只分配你需要的大小即可。

1.1.1.2. BLOB和TEXT

BLOB和TEXT是字符型数据类型，被用于分别存储大量的二进制数据和字符型数据。事实上它们都有各自的数据类型组合：比如字符型的有TINYTEXT，SMALLTEXT，TEXT，MEDIUMTEXT以及LONGTEXT；而二进制类型有TINYBLOG，SMALLBLOB，BLOB，MEDIUMBLOGB以及LOGNBLOB。BLOG是SMALLBLOB的别名，TEXT是SMALLTEXT的别名。

如果你希望你存入的数据和你取出来的一致的话，那么使用二进制类型时需要额外注意。因为MySQL可能因为对齐的需要而补0。

与所有其他数据类型不同的是，MySQL在处理BLOB和TEXT时，将它们有一唯一标识的对象来看待。存储引擎按照各自不同的试来存储它们，当它们很大时，InnoDB可能会有一个独立的外部存储区域来存储它们。每个值需要1到4个字节的行存储空间以及足够大的外部存储空间来实际存储这些数据。

BLOG和TEXT的唯一区别在于，BLOB一般用于存储没有字符集的二进制数据，而TEXT类型有字符集和排序方式。

MySQL在存储BLOB和TEXT列时与其他类型不同：它不会去将整个字符串排序，而只是将前max_sort_length个byte的字符进行排序，你既可以减少max_sort_length这个服务器变量，也可以使用ORDER BY SUBSTRING(column, length)。

MySQL不能将整个数据进行索引，因此也就不能使用这些索引进行排序。（在本章的后续章节你可以看到更多关于索引的内容）。

如何避免磁盘临时表？

因为内存存储引擎不支持BLOB和TEXT类型，因此如果查询需要使用BLOB和TEXT列，并且需要一个隐式的临时表的话，就可能会使用磁盘MyISAM临时表，即使只有少数几行。这将会导致严重的性能损耗。即使你配置了让MySQL将临时表存储在RAM盘上，许多其他昂贵的系统调用仍然需要。（Maria存储引擎可以减少这种问题，因为它将所有的东西都缓存到了内存，不只是索引）。

最好的解决方案是避免使用BLOB和TEXT类型，除非你真的别无选择。如果你无法避免，你可能需要使用ORDER BY SUBSTRING(column, length)来骗过MySQL把它当成可以在内存临时表中使用的字符串类型。你需要确定一点的是，你所使用的字符串要足够小，使得它不能超过max_heap_table_size或者tmp_table_size的规定，否则MySQL还是会把它转成磁盘MyISAM表。

如果EXPLAIN命令的Extra列包括了“Using temporary”，那么这个查询将会使用隐式临时表。

1.1.1.3. 用ENUM代替字符串

有时候你需要使用ENUM类型的列而不是传统的字符串类型。一个ENUM列可以存储65535个不同的字符串值。MySQL在存储这些值时是很紧密的，根据它们所代表的数值的量会打包成一个到两个字节。MySQL将会按照变量在定义表中的位置来在内部存储一个数字，同时将这个数字与其名字之间的映射关系存储到表的frm文件中。下面是一个例子：

这三行实际上存储的是数字而不是字符串。你通过在一个数字型的环境里获取这些变量可以看到这些值的两重性。

这个两重性在你为ENUM常量规定了数字时将会变得很难理解，如ENUM(‘1’, ‘2’, ‘3’)。我们建议你最好不要这样做。

另外一个神奇之处在于，一个ENUM列是通过它的内部数值来进行排序的，而不是字符串本身。

你可以将这些字符串按照你需要的排序顺序来定义ENUM。你可以使用FIELD()函数在你的查询中来显式指定一个排序顺序，但是这样将不能使用MySQL的索引排序。

ENUM的最大劣势在于字符串列表是固定的，当你需要添加或者删除一个字符串时，你需要使用ALTER TABLE命令。因此，如果你的列在将来可能会发生变动的情况下，最好还是不要使用ENUM。MySQL在它的自己内部的权限表中使用了ENUM来存储Y和N 。

因为MySQL在存储ENUM里时一个数字，因此它需要通过一个查询来将数字转换成所代表的字符串，因此ENUM列会有一些额外的消耗。一般来说，是通过偏移来完成的，但是也不总是如此。特别是，在需要连接一个CHAR或者VARCHAR类型到一个ENUM列时，会比连接到一个CHAR或者VARCHAR麻烦。

为了展示这一点，我们来测试一下MySQL来执行一个这样的连接操作的效率。这个表有一个相当宽的主键，它是一个联合主键：

这个表包括110000行数据，大约有10M左右，因此它可以完全放入内存。Service列存储了5个平均长度在4个字符的值，而method列存储了平均长度为20个字符的71个值。

我们拷贝上述的一个表，并且将service和method列转成ENUM类型如下：

然后我们来测试一下这两个表的连接效率，下面是我们使用的查询：

我们通过对VARCHAR以及ENUM列的不同连接组合来进行测试，如下表所示：

当列被转成ENUM之后连接的效率提高了，但是将一个ENUM列连接到一个VARCHAR列会慢一些。从上面这些测试用例来看，只要不是连接到一个VARCHAR类型，ENUM还是很好使的。

另外将这些列转换成ENUM还有一个好处：通过SHOW TABLE STATUS中Data_length列来看，通过将这两列转换成ENUM可以节省大约1/3的数据存储空间。在某些情况下，这种空间上的节省甚至比连接到VARCHAR比效率的提高都好。另一方面主键的大小也减少了。因为是InnoDB的缘故，如果这个表有其他的索引，那么减少主键索引的大小意味着同时减少了其他索引的大小。关于这一点我们会在后面的章节中进行讨论。