SQL 查询的性能主要取决于 JOIN、WHERE 子句、GROUP BY 和 ORDER BY,而不取决于检索的列。仅当检索到更多数据(可能必须通过网络才能由编程语言处理)时,这些列才会对查询速度产生显着影响。这里情况不同。
简短的回答:两种提议的设置之间的性能差异可能非常小。
为了获得良好的速度,您的post_id
列应该有一个(唯一的)索引。您无需按任何其他列进行选择、排序或分组,因此数据可以直接来自表,这是一个非常快的过程。
你在这里谈论的是“页面”,所以我猜这些将呈现给用户 - 你似乎不太可能想在同一页面上向人类显示包含数千篇博客文章的表格,因此你可能会这样做实际上,您的陈述中有 ORDER BY 和/或 LIMIT 子句,但您的问题中没有包含这些子句。
但让我们更深入地研究一下整个事情。假设我们实际上直接从硬盘读取大量 TEXT 列,难道我们不会达到驱动器的最大读取速度吗?仅检索 VARCHAR(250) 不是更快吗,尤其是因为它节省了额外的 LEFT() 调用?
我们可以很快地将 LEFT() 调用从表中删除。字符串函数确实很快——毕竟,CPU 只是切断了一些数据,这是一个非常快的过程。它们产生明显延迟的唯一时间是在 WHERE 子句、JOIN 等中使用它们时,但这并不是因为这些函数很慢,而是因为它们必须运行很多次(可能是数百万次)才能甚至会产生一行结果,甚至更多,因为这些使用通常会阻止数据库正确使用其索引。
所以最终归结为:MySQL 从数据库读取表内容的速度有多快。这又取决于您正在使用的存储引擎及其设置。 MySQL可以使用多种存储引擎,包括(但不限于)InnoDB和MyISAM。这两个引擎都为大型对象(例如 TEXT 或 BLOB 列)提供不同的文件布局(但有趣的是,还有 VARCHAR)。如果 TEXT 列存储在与行的其余部分不同的页中,则存储引擎必须为每行检索两个页。如果它与其他内容一起存储,那么它就只是一页。对于顺序处理来说,这可能是性能的重大变化。
以下是一些相关背景阅读:
- InnoDB 中的 Blob 存储 http://www.mysqlperformanceblog.com/2010/02/09/blob-storage-in-innodb/
- MyISAM 动态与压缩数据文件布局 http://dev.mysql.com/doc/internals/en/myisam.html
长答案:这取决于:)
您必须在自己的硬件上进行大量基准测试,才能真正确定哪种布局实际上更快。鉴于第二种设置通过附加列引入了冗余,因此在大多数情况下它的性能可能会更差。当且仅当表结构允许较短的 VARCHAR 列适合磁盘上的同一页,而长的 TEXT 列位于另一页上时,它的性能会更好。
编辑:有关 TEXT 列和性能的更多信息
关于 BLOB 和内存中处理似乎存在一个常见的误解。相当多的页面(包括 StackOverflow 上的一些答案 - 我将尝试找到它们,并给出附加评论)指出 TEXT 列(以及所有其他 BLOB)无法由 MySQL 在内存中处理,因此总是性能猪。那不是真的。真正发生的事情是这样的:
如果您运行涉及 TEXT 列的查询and该查询需要一个临时表来处理,thenMySQL 必须在磁盘上而不是在内存中创建临时表,因为 MySQL 的MEMORY
存储引擎无法处理 TEXT 列。看这个相关问题 https://stackoverflow.com/questions/2883867/mysql-text-field-performance.
The MySQL 文档 http://dev.mysql.com/doc/refman/5.5/en/blob.html声明如下(该段落对于 3.2 到 5.6 的所有版本都是相同的):
查询结果中 BLOB 或 TEXT 列的实例
使用临时表进行处理会导致服务器使用以下表:
磁盘而不是内存中,因为 MEMORY 存储引擎不
支持这些数据类型(请参见第 8.4.3.3 节,“MySQL 如何使用
内部临时表”)。使用磁盘会导致性能损失,
因此,仅在查询结果中包含 BLOB 或 TEXT 列
真的需要。例如,避免使用 SELECT *,它会选择所有
列。
最后一句话让人困惑——因为那只是一个坏例子。一个简单的SELECT *
will not受到此性能问题的影响,因为不会使用临时表。例如,如果相同的选择按非索引列排序,则它would必须使用临时表并且会受到影响通过这个问题。使用EXPLAIN
MySQL 中的命令可查明查询是否需要临时表。
顺便说一句:这些都不会影响缓存。 TEXT 列可以像其他任何列一样被缓存。即使查询需要临时表并且必须存储在磁盘上,如果系统有足够的资源,结果仍然可以被缓存,并且缓存不会失效。在这方面,TEXT 列就像其他任何列一样。
编辑 2:有关 TEXT 列和内存要求的更多信息...
MySQL 使用存储引擎从磁盘检索记录。然后它将缓冲结果并按顺序将它们传递给客户端。以下假设该缓冲区最终位于内存中而不是磁盘上(请参阅上面的原因)
对于 TEXT 列(和其他 BLOB),MySQL 将缓冲指向实际 BLOB 的指针。这样的指针仅使用几个字节的内存,但需要在将行传递给客户端时从磁盘检索实际的 TEXT 内容。
对于 VARCHAR 列(以及除 BLOB 之外的所有其他列),MySQL 将缓冲实际数据。这通常会使用更多内存,因为大多数文本都不仅仅是几个字节。
对于计算列,MySQL 还将缓冲实际数据,就像 VARCHAR 一样。
对此有几点说明:从技术上讲,BLOB 在移交给客户端时也会被缓冲,但一次只能缓冲一个 - 对于大型 BLOB 可能不会全部缓冲。由于该缓冲区在每行之后都会被释放,因此这不会产生任何重大影响。另外,如果 BLOB 实际上与行的其余部分存储在同一页中,则它最终可能会被视为 VARCHAR。说实话,我已经never有回国要求lots单个查询中的 BLOB,所以我从未尝试过。
现在让我们实际回答(现已编辑)问题:
第 1 页。用户概述和简短的博客文章片段。
您的选择几乎就是这些查询
SELECT userid, post_title, LEFT(post_description, 250) FROM `table_method_1` <-- calculated based on a VARCHAR column
SELECT userid, post_title, LEFT(post_description, 250) FROM `table_method_2` <-- calculated based on the TEXT column
SELECT userid, post_title, post_brief FROM `table_method_2` <-- precalculated VARCHAR column
SELECT userid, post_title, post_description FROM `table_method_2` <-- return the full text, let the client produce the snippet
前三个的内存要求是完全相同的。第四个查询将需要less内存(TEXT 列将作为指针进行缓冲)但是more到客户端的流量。由于流量通常通过网络进行(就性能而言昂贵),因此这往往比其他查询慢 - 但您的里程可能会有所不同。 TEXT 列上的 LEFT() 函数可以通过告诉存储引擎使用内联表布局来加速,但这将取决于所存储文本的平均长度。
第2页。一篇博文
SELECT userid, post_title, post_description FROM `table_method_1` WHERE post_id=... <-- returns a VARCHAR
SELECT userid, post_title, post_description FROM `table_method_2` WHERE post_id=... <-- returns a TEXT
一开始,内存要求就很低,因为只有一行会被缓冲。由于上述原因,第二个方法将需要少量的内存来缓冲行,但需要一些额外的内存来缓冲单个 BLOB。
在任何一种情况下,我很确定您不关心仅返回一行的选择的内存要求,因此这并不重要。
Summary
如果您有任意长度的文本(或任何需要超过几千字节的文本),则应使用 TEXT 列。这就是他们存在的目的。 MySQL 处理这些列的方式是有益的大多数时候.
日常使用时只需记住两件事:
- 如果您实际上不需要它们,请避免选择 TEXT 列、BLOB 列以及可能包含大量数据的所有其他列(是的,其中包括 VARCHAR(10000))。当您只需要几个值时,“SELECT * FROM无论什么”的习惯会给数据库带来很多不必要的压力。
- 当你are选择 TEXT 列或其他 BLOB,请确保选择不使用临时表。使用
EXPLAIN
语法有疑问时。
当您遵守这些规则时,您应该从 MySQL 获得相当不错的性能。如果您需要进一步优化,则必须查看更精细的细节。这将包括存储引擎和相应的表布局、实际数据的统计信息以及有关所涉及硬件的知识。根据我的经验,我通常可以摆脱性能消耗,而不必深入挖掘。