php MySql Tinytext vs Varchar vs Char

MySql Tinytext vs Varchar vs Char

提问by OneNerd

Building a system that has the potential to get hammered pretty hard with hits and traffic. It's a typical Apache/PHP/MySql setup.

构建一个系统，该系统有可能因点击率和流量而受到重创。这是典型的 Apache/PHP/MySql 设置。

Have build plenty of systems before, but never had a scenario where I really had to make decisions regarding potential scalability of this size. I have dozens of questions regarding building a system of this magniture, but for this particular question, I am trying to decide on what to use as the data type.

之前已经构建了大量系统，但从来没有遇到过我真的必须就这种规模的潜在可扩展性做出决定的场景。我有很多关于构建这种规模的系统的问题，但对于这个特定的问题，我试图决定使用什么作为数据类型。

Here is the 100ft view:

这是100英尺的视图：

We have a table which (among other things) has a descriptionfield. We have decided to limit it to 255 characters. It will be searchable(ie: show me all entries with description that contains ...). Problem: this table is likely to have millions upon millions of entriesat some point (or so we think).

我们有一个表（除其他外）有一个描述字段。我们决定将其限制为255 个字符。它将是可搜索的（即：显示所有带有包含...的描述的条目）。问题：这个表在某个时候可能有数以百万计的条目（或者我们认为）。

I have not yet figured out the strategy for the search (the MySql LIKE operator is likely to be slow and/or a hog I am guessing for such a large # records), but thats for another SO question. For this question, I am wondering what the pro's and cons are to creating this field as a tinytext, varchar, and char.

我还没有想出搜索的策略（MySql LIKE 操作符可能很慢和/或我猜测如此大的# 记录是一个猪），但那是另一个 SO 问题。对于这个问题，我想知道将这个字段创建为 tinytext、varchar 和 char 的利弊是什么。

I am nota database expert, so any and all commentary is helpful. Thanks -

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

回答by Seth

Use a CHAR.

使用一个CHAR.

BLOB's and TEXT's are stored outside the row, so there will be an access penalty to reading them. VARCHAR's are variable length, which saves storage space by could introduce a small access penalty (since the rows aren't all fixed length).

BLOB's 和TEXT's 存储在行外，因此读取它们会受到访问惩罚。 VARCHAR是可变长度的，这可以通过引入小的访问损失来节省存储空间（因为行不是全部固定长度）。

If you create your index properly, however, either VARCHARor CHARcan be stored entirely in the index, which will make access a lot faster.

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

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

请参阅：VARCHAR（255）诉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

Incidentally, in my experience the MySQL regexoperator is a lot faster than LIKEfor simple queries (i.e., SELECT ID WHERE SOME_COLUMN REGEX 'search.*'), and obviously more versatile.

顺便说一句，根据我的经验，MySQLregex运算符比LIKE简单查询（即SELECT ID WHERE SOME_COLUMN REGEX 'search.*'）要快得多，而且显然更通用。

回答by Marius Burz

In your situation all three types are bad if you'll use LIKE(a LIKE '%string%'won't use any index created on that column, regardless of its type) . Everything else is just noise.

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

I am not aware of any major difference between TINYTEXTand VARCHARup to 255 chars, and CHARis just not meant for variable length strings.

我不知道之间的主要区别的TINYTEXT，并VARCHAR以255个字符了，CHAR只是不是为可变长度的字符串。

So my suggestion: pick VARCHARor TINYTEXT(I'd personally go for VARCHAR) and index the content of that column using a full text search engine like Lucene, Sphinx or any other that does the job for you. Just forget about LIKE(even if that means you need to custom build the full text search index engine yourself for whatever reasons you might have, i.e. you need support for a set of features that no engine out there can satisfy).

所以我的建议是：选择VARCHARor TINYTEXT（我个人会选择 VARCHAR）并使用全文搜索引擎（如 Lucene、Sphinx 或任何其他可以为您完成工作的引擎）对该列的内容进行索引。只是忘记LIKE（即使这意味着您需要出于任何原因自己定制构建全文搜索索引引擎，即您需要支持一组没有任何引擎可以满足的功能）。

回答by Crack

If you want to search among millions of rows, store all these texts in a different table (which will decrease row size of your big table) and use VARCHARif your text data is short, or TEXT if you require greater length.

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

Instead of searching with LIKEuse a specialized solution like Lucene, Sphinx or Solr. I don't remember which, but at least one of them can be easily configured for real-time or near real-time indexing.

而不是LIKE使用像 Lucene、Sphinx 或 Solr 这样的专门解决方案进行搜索。我不记得是哪个，但至少其中一个可以轻松配置为实时或近实时索引。

EDIT

编辑

My proposition of storing text in different table reduces IO required for main table, but when data is inserted it requires to keep an additional index and adds join overhead in selects, so is valid only if you use your table to read a few descriptions at once and other data from the table is is used more often.

我将文本存储在不同表中的提议减少了主表所需的 IO，但是当插入数据时，它需要保留一个额外的索引并在选择中增加连接开销，因此只有当您使用您的表一次读取一些描述时才有效表中的其他数据被更频繁地使用。

回答by profitphp

I believe with varchar you've got a variable length stored in the actual database at the low levels, which means it could take less disk space, with the text field its fixed length even if a row doesn't use all of it. The fixed length string should be faster to query.

我相信使用 varchar 你已经在低级别的实际数据库中存储了一个可变长度，这意味着它可以占用更少的磁盘空间，即使一行没有使用所有文本字段，它的长度也是固定的。固定长度的字符串应该更快查询。

Edit: I just looked it up, text types are stored as variable length as well. Best thing to do would be to benchmark it with something like mysqlslap

编辑：我刚刚查了一下，文本类型也存储为可变长度。最好的办法是用诸如 mysqlslap 之类的东西对其进行基准测试

In regards to your other un-asked question, you'd probably want to build some sort of a search index that ties every useful word in the description field individually to a description, then you you can index that and search it instead. will be way way faster than using %like%.

关于您的其他未提出的问题，您可能希望构建某种搜索索引，将描述字段中的每个有用单词单独关联到描述，然后您可以对其进行索引并进行搜索。将比使用 %like% 快得多。