Is there/has somebody any comparison, personal experience, or guideline when to use the text type instead of a large varchar in MySQL?
在MySQL中使用文本类型而不是大型varchar时,是否存在任何比较,个人经验或指南?
While most of the entries in my database will be less than 1000 characters, some might take up to 4000 characters or more. What is the limiting length of varchar which makes text a better variant?
虽然我的数据库中的大多数条目将少于1000个字符,但有些可能最多需要4000个字符或更多。 varchar的限制长度是什么使文本成为更好的变体?
I do not need to index those fields.
我不需要索引这些字段。
2 个解决方案
#1
5
I don't have personal experience, but this guy does:
我没有亲身经历,但是这个人做了:
VARCHAR vs. TEXT - some performance numbers
VARCHAR与TEXT - 一些性能数字
Quick answer: varchar was a good bit faster.
快速回答:varchar更快一点。
Edit - no, it wasn't. He was indexing them differently - he had a full index on the varchar (255 chars) but a 255-char prefix index on the text. When he removed that, they performed more or less the same.
编辑 - 不,它不是。他对它们进行了不同的索引 - 他在varchar(255个字符)上有一个完整的索引,但在文本上有一个255个字符的前缀索引。当他删除它时,他们或多或少地表现相同。
Later in the thread is this interesting tidbit:
后来在线程中是这个有趣的花絮:
When a tmp table is needed for a SELECT, the first choice is to use MEMORY, which will be RAM-only, hence probably noticeably faster. (Second choice is MyISAM.) However, TEXT and BLOB are not allowed in MEMORY, so it can't use it. (There are other reasons why it might skip MEMORY.)
当SELECT需要一个tmp表时,第一个选择是使用MEMORY,它只是RAM,因此可能明显更快。 (第二个选择是MyISAM。)但是,MEMORY中不允许使用TEXT和BLOB,因此无法使用它。 (还有其他原因可能会跳过MEMORY。)
Edit 2 - some more relevant info, this time comparing the way different indices deal with the various types.
编辑2 - 一些更相关的信息,这次比较不同指数处理各种类型的方式。
MyISAM puts TEXT and BLOB 'inline'. If you are searching a table (range scan / table scan), you are 'stepping over those cow paddies' -- costly for disk I/O. That is, the existence of the inline blob hurts performance in this case.
MyISAM把TEXT和BLOB'内联'。如果你正在搜索一个表(范围扫描/表扫描),你正在“踩过那些牛圈” - 磁盘I / O成本很高。也就是说,在这种情况下,内联blob的存在会损害性能。
InnoDB puts only 767 bytes of a TEXT or BLOB inline, the rest goes into some other block. This is a compromise that sometimes helps, sometimes hurts performance.
InnoDB只将767字节的TEXT或BLOB内联,其余的进入其他块。这是一种妥协,有时会有所帮助,有时会伤害性能。
Something else (Maria? Falcon? InnoDB plugin?) puts TEXTs and BLOBs entirely elsewhere. This would make a noticeable difference in performance when compared to VARCHAR. Sometimes TEXT would be faster (eg, range scan that does not need the blob); sometimes the VARCHAR would be faster (eg, if you need to look at it and/or return it).
其他东西(Maria?Falcon?InnoDB插件?)将TEXT和BLOB完全放在其他地方。与VARCHAR相比,这会在性能上产生明显的差异。有时TEXT会更快(例如,不需要blob的范围扫描);有时VARCHAR会更快(例如,如果你需要查看和/或返回它)。
#2
1
Of course the best way to know is to run some tests yourself with your real dataset, or at least a simulated equivalent. Just write some scripts to populate the data and run your selects. Test with varchar at different sizes, then text, and measure both the timing and overall system utilization (cpu/load, memory, disk i/o).
当然,最好的方法是使用您的真实数据集自己运行一些测试,或者至少是模拟等效项。只需编写一些脚本来填充数据并运行您的选择。使用不同大小的varchar进行测试,然后进行文本测量,并测量时序和整体系统利用率(cpu / load,memory,disk i / o)。
If you are going to have enough load that this will matter then you ought to have automated tests anyway.
如果你有足够的负载,这将很重要,那么你应该进行自动化测试。
#1
5
I don't have personal experience, but this guy does:
我没有亲身经历,但是这个人做了:
VARCHAR vs. TEXT - some performance numbers
VARCHAR与TEXT - 一些性能数字
Quick answer: varchar was a good bit faster.
快速回答:varchar更快一点。
Edit - no, it wasn't. He was indexing them differently - he had a full index on the varchar (255 chars) but a 255-char prefix index on the text. When he removed that, they performed more or less the same.
编辑 - 不,它不是。他对它们进行了不同的索引 - 他在varchar(255个字符)上有一个完整的索引,但在文本上有一个255个字符的前缀索引。当他删除它时,他们或多或少地表现相同。
Later in the thread is this interesting tidbit:
后来在线程中是这个有趣的花絮:
When a tmp table is needed for a SELECT, the first choice is to use MEMORY, which will be RAM-only, hence probably noticeably faster. (Second choice is MyISAM.) However, TEXT and BLOB are not allowed in MEMORY, so it can't use it. (There are other reasons why it might skip MEMORY.)
当SELECT需要一个tmp表时,第一个选择是使用MEMORY,它只是RAM,因此可能明显更快。 (第二个选择是MyISAM。)但是,MEMORY中不允许使用TEXT和BLOB,因此无法使用它。 (还有其他原因可能会跳过MEMORY。)
Edit 2 - some more relevant info, this time comparing the way different indices deal with the various types.
编辑2 - 一些更相关的信息,这次比较不同指数处理各种类型的方式。
MyISAM puts TEXT and BLOB 'inline'. If you are searching a table (range scan / table scan), you are 'stepping over those cow paddies' -- costly for disk I/O. That is, the existence of the inline blob hurts performance in this case.
MyISAM把TEXT和BLOB'内联'。如果你正在搜索一个表(范围扫描/表扫描),你正在“踩过那些牛圈” - 磁盘I / O成本很高。也就是说,在这种情况下,内联blob的存在会损害性能。
InnoDB puts only 767 bytes of a TEXT or BLOB inline, the rest goes into some other block. This is a compromise that sometimes helps, sometimes hurts performance.
InnoDB只将767字节的TEXT或BLOB内联,其余的进入其他块。这是一种妥协,有时会有所帮助,有时会伤害性能。
Something else (Maria? Falcon? InnoDB plugin?) puts TEXTs and BLOBs entirely elsewhere. This would make a noticeable difference in performance when compared to VARCHAR. Sometimes TEXT would be faster (eg, range scan that does not need the blob); sometimes the VARCHAR would be faster (eg, if you need to look at it and/or return it).
其他东西(Maria?Falcon?InnoDB插件?)将TEXT和BLOB完全放在其他地方。与VARCHAR相比,这会在性能上产生明显的差异。有时TEXT会更快(例如,不需要blob的范围扫描);有时VARCHAR会更快(例如,如果你需要查看和/或返回它)。
#2
1
Of course the best way to know is to run some tests yourself with your real dataset, or at least a simulated equivalent. Just write some scripts to populate the data and run your selects. Test with varchar at different sizes, then text, and measure both the timing and overall system utilization (cpu/load, memory, disk i/o).
当然,最好的方法是使用您的真实数据集自己运行一些测试,或者至少是模拟等效项。只需编写一些脚本来填充数据并运行您的选择。使用不同大小的varchar进行测试,然后进行文本测量,并测量时序和整体系统利用率(cpu / load,memory,disk i / o)。
If you are going to have enough load that this will matter then you ought to have automated tests anyway.
如果你有足够的负载,这将很重要,那么你应该进行自动化测试。