是否在同一表中存储TEXT/BLOB? [英] store TEXT/BLOB in same table or not?

问题描述

在搜索低谷时，我发现了两个矛盾的答案(甚至有一条评论指出了这一点)，但没有明确的答案:

问题是:如果将TEXT/BLOB字段存储在表的外部，会有什么性能上的好处?

我们假设:

• 您正确选择(如果需要，仅选择TEXT/BLOB，不选择*)
• 在适当的位置对表进行正确的索引(因此，与是否建立索引"无关)
• 数据库设计并不重要.这是在这种特殊情况下确定MySQL行为的问题，而不是解决某些数据库设计问题.假设此数据库只有一个表(如果TEXT/BLOB被分开，则为两个表)
• 使用的引擎:innoDB(其他的如果获取不同的结果也会很有趣)

---

这篇文章指出，将TEXT/BLOB放在单独的表中，仅在您已经以错误的方式进行选择(仅在没有必要时始终选择TEXT/BLOB的情况下)时才有帮助-基本上是这样说明的: 在同一张表中的TEXT/BLOB基本上是更好的解决方案(更少的复杂性，不会降低性能等)，因为TEXT/BLOB还是单独存储的

将TEXT列移到另一个表中的唯一好处是，如果有这种趋势，通常会从表中选择所有列.这仅仅是引入第二种不良做法来弥补第一种不良做法.不言而喻，两个错误与三个左错误并不相同.

带有TEXT列的MySQL表

---

但是，该帖子指出:

当表具有TEXT或BLOB列时，该表无法存储在内存中

这是否意味着在表中拥有TEXT/BLOB已经足以提高性能?

MySQL varchar(2000)vs text?

---

我的问题基本上是:正确的答案是什么?

如果正确地SELECT将TEXT/BLOB存储到单独的表中真的有关系吗?

或者甚至在表中包含TEXT/BLOB都会对性能产生潜在的影响吗?

解决方案

如果在您的MySQL版本上可用，请使用InpDB Barracuda文件格式，

innodb_file_format=barracuda

在您的MySQL配置中，并使用ROW_FORMAT=Dynamic(或Compressed)设置表以实际使用它.

这将使InnoDB在行页面之外存储BLOB，TEXT和更大的VARCHAR，从而使其效率更高.有关更多信息，请参见此MySQLperformanceblog.com博客文章.

据我了解，使用梭子鱼格式将使TEXT/BLOB/VARCHARs存储在单独的表中由于性能原因不再有效.但是，我认为牢记适当的数据库规范化总是很好.

While searching trough SO, I've found two contradicting answers (and even a comment that stated that) but no definitive answer:

The problem is: is there any performance benefit, if you store a TEXT/BLOB field outside of a table?

We assume:

• You SELECT correctly (only selection the TEXT/BLOB if required, no SELECT *)
• Tables are indexed properly, where it makes sense (so it's not a matter of 'if you index it')
• The database design doesnt really matter. This is a question to identify the MySQL behaviour in this special case, not to solve certain database design problems. Let's assume this Database has only one table (or two, if the TEXT/BLOB gets separated)
• used engine: innoDB (others would be interesting too, if they fetch different results)

---

This post states, that putting the TEXT/BLOB into a separate table, only helps if you're already SELECTing in a wrong way (always SELECTing the TEXT/BLOB even when it's not necessary) - basically stating, that TEXT/BLOB in the same table is basically the better solution (less complexity, no performance hit, etc) since the TEXT/BLOB is stored seprately anyway

The only time that moving TEXT columns into another table will offer any benefit is if there it a tendency to usually select all columns from tables. This is merely introducing a second bad practice to compensate for the first. It should go without saying the two wrongs is not the same as three lefts.

MySQL Table with TEXT column

---

This post however, states that:

When a table has TEXT or BLOB columns, the table can't be stored in memory

Does that mean that it's already enough to have a TEXT/BLOB inside a table, to have a performance hit?

MySQL varchar(2000) vs text?

---

My Question basically is: What's the correct answer?

Does it really matter if you store TEXT/BLOB into a separate table, if you SELECT correctly?

Or does even having a TEXT/BLOB inside a table, create a potential performance hit?

解决方案

If available on your MySQL version, use the InnoDB Barracuda file format using

innodb_file_format=barracuda

in your MySQL configuration and set up your tables using ROW_FORMAT=Dynamic (or Compressed) to actually use it.

This will make InnoDB to store BLOBs, TEXTs and bigger VARCHARs outside the row pages and thus making it a lot more efficient. See this MySQLperformanceblog.com blog article for more information.

As far as I understand it, using the Barracuda format will make storing TEXT/BLOB/VARCHARs in separate tables not valid anymore for performance reasons. However, I think it's always good to keep proper database normalization in mind.

这篇关于是否在同一表中存储TEXT/BLOB?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！