MySql Tinytext与Varchar与Char的区别

Question

构建一个系统，可能会因为点击和流量而遭受沉重打击。这是一个典型的Apache/PHP/MySQL设置。

之前已经搭建了很多系统，但从来没有像这个规模一样需要考虑潜在可扩展性的情况。我有很多关于构建这个规模系统的问题，但对于这个特定的问题，我正在努力决定使用什么数据类型。

以下是总体观点：

我们有一个表格，其中（除其他信息外）有一个描述字段。我们决定将其限制在255个字符内。它将是可搜索的（例如：显示所有包含描述的条目...）。问题是：这张表在某些时候可能会有数百万条以上的记录（或者我们认为会有）。

我还没有想出搜索策略（对于如此大量的记录，MySQL LIKE运算符可能会很慢或占用大量资源），但那是另一个SO问题。对于这个问题，我想知道创建这个字段作为tinytext、varchar和char类型的优缺点是什么。

我不是一个数据库专家，所以任何评论都是有帮助的。谢谢 -

- OneNerd

---

由于问题似乎已经被编辑以明确包括搜索文本字段的问题，您可能希望编辑标题以反映这一点。 - TehShrike

@tehshrike：我没有编辑它。我的问题仍然是一样的（每种数据字段的优缺点）。只是看起来每个人都关注“搜索”部分。显然，很多人对每种数据字段之间的区别感兴趣。根据我所读的所有内容，听起来对于搜索来说都差不多（只需使用您想要的任何内容，然后在需要时添加索引软件组件即可）。但仍在等待有人详细说明每种数据字段的优缺点。 - OneNerd

Answer 1

使用CHAR。

BLOB和TEXT存储在行外，因此读取它们会有访问罚款。 VARCHAR是可变长度的，可以节省存储空间，但可能会引入小的访问罚款（因为行不是所有固定长度的）。

然而，如果您正确创建索引，则VARCHAR或CHAR都可以完全存储在索引中，这将使访问速度更快。

请参见：varchar(255) v tinyblob v tinytext
和：http://213.136.52.31/mysql/540
和：http://forums.mysql.com/read.php?10,254231,254231#msg-254231
和：http://forums.mysql.com/read.php?20,223006,223683#msg-223683

顺便说一句，在我的经验中，MySQL的regex操作符比LIKE对于简单查询（即SELECT ID WHERE SOME_COLUMN REGEX 'search.*'）要快得多，显然更加灵活。

- Seth

---

谢谢。我正在研究这4篇文章。已经点赞了。不过还不确定要接受哪个答案——需要深入挖掘一下。 - OneNerd

那里有一些好的信息，但它们都与你的问题不太相关——无论它存储在哪里，或者它是否是固定宽度——如果你有数百万行，你不能使用表扫描。你需要索引，并且任何文本字段上的普通索引都无法让你搜索字段中间的文本。 - TehShrike

@tehshrike：嗯，我的问题不是关于搜索，而是关于每种数据类型的优缺点。就像我在问题中提到的那样，“搜索”部分将是一个单独的问题。实际上，我认为他给出的信息很好。 - OneNerd

@TehShrike - 实际上，CHAR或VARCHAR中的普通（B-Tree）索引可以用于LIKE搜索：“如果LIKE的参数是不以通配符字符开头的常量字符串，则该索引也可用于LIKE比较。”（来自这里）。如果您有足够的内存，对于数百万行应该可以正常工作（尽管可能不适用于数亿行）。 - Seth

@Seth：没错，只要你搜索的是字段开头，就可以使用索引。但这不是OneNerd说他要做的事情：“显示所有包含描述的条目…” - TehShrike

Answer 2

我相信使用varchar，实际数据库中存储的是可变长度，这意味着它可能占用更少的磁盘空间；而text字段是固定长度的，即使某一行没有使用完整个字段。查询固定长度字符串应该更快。 编辑：我刚查了一下，文本类型也被存储为可变长度。最好的方法是使用像mysqlslap这样的工具进行基准测试。

至于您未提出的其他问题，您可能需要构建某种搜索索引，将描述字段中的每个有用单词与描述分别绑定，然后对其进行索引和搜索。这将比使用%like%要快得多。

- profitphp

---

我正在查看http://forums.mysql.com/read.php?24,105964,105964和http://forums.mysql.com/read.php?10,254231,254581#msg-254581——似乎varchar是最佳选择，可容纳多达255个字符，出于各种原因。 - VNO

很好，看起来基准测试已经完成了，所以这就是你要的。 - profitphp

谢谢。我正在研究这些文章。已经点赞了。不确定要接受哪个答案，因为有很多东西需要挖掘。 - OneNerd

Answer 3

在你的情况下，如果使用LIKE，所有三种类型都不好（LIKE '%string%'将不使用任何在该列上创建的索引，无论其类型如何）。其他一切都只是噪音。

我不知道TINYTEXT和VARCHAR在255个字符以下有什么主要区别，而CHAR则不适用于可变长度字符串。

所以我的建议是：选择VARCHAR或TINYTEXT（我个人会选择VARCHAR），并使用全文搜索引擎（如Lucene、Sphinx或其他能够胜任该工作的引擎）对该列的内容进行索引。忘掉LIKE吧（即使这意味着你需要自己定制全文搜索索引引擎以满足某些原因而无法满足的一组功能）。

- Marius Burz

---

没错，如果你正在查看表扫描，它们都同样糟糕。旧的MyISAM引擎确实支持全文索引，但在我的经验中，它并不是一个非常有用的实现。 - TehShrike

我一秒钟都没有考虑过使用MyISAM支持的引擎（我也不喜欢这样原始的引擎）。我在考虑像Lucene或Sphinx这样的东西，或者如果有意义的话，真正制作一个自定义的全文索引。我的回答确实令人困惑，但我已经更新了它来解决这个问题。 - Marius Burz

Answer 4

如果您想在数百万行中进行搜索，请将所有这些文本存储在不同的表格中（这将减少大表的行大小），如果您的文本数据较短，请使用VARCHAR，如果需要更长的长度，则使用TEXT。

不要使用LIKE进行搜索，而是使用专业解决方案，如Lucene、Sphinx或Solr。我记不清是哪一个了，但至少其中一个可以轻松配置为实时或近实时索引。

我的建议是将文本存储在不同的表格中，从而减少主表所需的IO，但插入数据时需要保留额外的索引，并在选择时增加连接开销，因此仅在您同时从表格中读取少量描述并且其他数据更经常使用时才有效。

- piotrp

---

一列最多可达255个字符（意思是：大多数情况下可能会在100个字符或更短的范围内），这应该不是问题，因此我认为将这些列存储在外部表中并没有太多意义。 - Marius Burz

我只是举了一个表扫描的例子，以便我可以轻松计算优势。@Mariusz - 不是每个 varchar，但如果表将有数百万条目，那么像描述这样的东西可以轻松地存储在其他地方，将行大小减少50％（并允许更多行每个磁盘页面）。 - piotrp

同样的话也可以用于其他针对这个问题给出的建议。我假设所谓的“描述”很少被使用到需要进行分离，而其他人则提供适用于简单 OLTP 用例的建议。 - piotrp

我认为@Marius Burz可能高估了加入另一个表的成本，但同时，我认为将此列放入另一个表中并没有太多优势。如果使用TEXT类型，很有可能数据已经存储在另一个物理位置（取决于存储引擎）。 - TehShrike

@TehShrike 使用联接比仅使用单个表格确实会增加更高的IO成本。 - Marius Burz