可变长度列是否会减慢 InnoDB 表上的选择速度？
由于 MyISAM 表上有可变长度列（varchar、blob），确实减慢了查询速度，因此我在网上遇到了将 varchar 列移动到单独表中的建议。
InnoDB 仍然有这个问题吗？我并不是指在表中引入许多 varchar 行导致页面拆分的情况。我只是说你是否应该考虑，例如，将 post_text （表中的单个 BLOB 字段）移动到另一个表中，从 InnoDB 的性能角度考虑？
With MyISAM having variable length columns (varchar, blob) on the table really slowed queries so that I encountered advices on the net to move varchar columns into separate table.
Is that still an issue with InnoDB? I don't mean cases where introducing many varchar rows into the table causes page split. I just mean should you consider, for example, move post_text (single BLOB field in the table) into another table, speaking performance-wise about InnoDB?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
据我所知，BLOB（和 TEXT）实际上存储在表外部，VARCHAR 存储在表中。
VARCHAR 不利于读取性能，因为每条记录的长度都是可变的，这使得在记录中查找字段的成本更高。
BLOB 速度很慢，因为必须单独获取值，并且很可能需要从磁盘或缓存中再次读取。
据我所知，InnoDB 在这方面没有做任何不同的事情，所以我假设性能特征保持不变。
我不认为移动 BLOB 值真的有帮助 - 除了减少整体表大小之外，这无论如何都会对性能产生积极影响。
VARCHAR 则不同。在这里你一定会受益匪浅。如果所有列都具有定义的长度（我猜这意味着您也不能使用 BLOB？），字段查找将会更快。
如果您只是“阅读” VARHCAR 和 BLOB 字段，我会说这值得一试。但是，如果您的选择查询需要比较 VARCHAR 或 BLOB 中的值，那么您就很不高兴了。
所以，是的，您绝对可以在这里获得性能，但请确保您测试您确实获得了性能，并且这种增加值得积极的非规范化。
附言。
“优化”VARCHAR 读取性能的另一种方法是简单地将它们替换为 CHAR 字段（固定长度）。只要磁盘空间的增加是可以接受的，这就可以提高读取性能。
As far as I know BLOBs (and TEXTs) are actually stored outside of the table, VARCHARs are stored in the table.
VARCHARs are bad for read performance because each record can be of variable length and that makes it more costly to find fields in a record.
BLOBs are slow because the value has to be fetched separately and it will very likely require another read from disk or cache.
To my knowledge InnoDB doesn't do anything differently in this respect so I would assume the performance characteristics hold.
I don't think moving BLOB values really helps - other than reducing overall table size which has a positive influence on performance regardless.
VARCHARs are a different story. You will definitely benefit here. If all your columns are of defined length (and I guess that means you can't use BLOBs either?) the field lookup will be faster.
If you're just 'reading' the VARHCAR and BLOB fields I'd say this is worth a shot. But if your select query needs to compare a value from a VARCHAR or a BLOB you're pretty sour.
So yes you can definitely gain performance here but make sure you test that you're actually gaining performance and that the increase is worth the aggressive denormalization.
PS.
Another way of 'optimizing' VARCHAR read performance is to simply replace them by CHAR fields (of fixed length). This could benefit read performance, so long as the increase in disk space is acceptable.
InnoDB 数据与 MyISAM 完全不同。
在 MyISAM 中，所有索引（主索引或其他索引）都存储在 MYI 文件中，并包含指向 MYD 文件中存储的数据的指针。可变长度行不应直接影响查询速度，但 MYD 文件确实往往会因可变长度行而变得更加碎片化，因为删除行时留下的空洞不一定会与您接下来插入的行一起归档。如果您更新可变长度值以使其更长，您可能必须将其移至其他位置，这意味着随着时间的推移，它往往会相对于索引变得无序，从而使范围查询变慢。 （如果您在旋转磁盘上运行它，则寻道时间很重要）。
InnoDB 将数据聚集在主键上的 B 树中的页中。只要数据适合页面，无论您使用的是 BLOB 还是 VARCHAR，数据都会存储在页面中。只要您不尝试定期插入过长的值，那么您的行是固定长度还是可变长度都没有关系。
InnoDB data completely differently than MyISAM.
In MyISAM all indexes--primary or otherwise--- are stored in the MYI file an contain a pointer to the data stored in the MYD file. Variable length rows shouldn't directly affect query speed directly, but the MYD file does tend to get more fragmented with variable length rows because the hole left behind when you delete a row can't necessarily be filed in with the row you insert next. If you update a variable length value to make it longer you might have to move it somewhere else, which means it will tend to get out-of-order with respect to the indexes over time, making range queries slower. (If you're running it on a spinning disk where seek times are important).
InnoDB stores data clustered in pages in a B-tree on the primary key. So long as the data will fit in a page it is stored in the page whether you're using a BLOB or VARCHAR. As long as you aren't trying to insert inordinately long values on a regular basis it shouldn't matter whether your rows are fixed-length or variable-length.