当查询中未使用字段时，MySQL InnoDB 表中文本字段的大小是否会影响性能
我有一个大约 500 000 行的 InnoDB 表，该表正在增长，我正在尝试提高选择查询性能。
目前该表大约有 2GB 大，其中大部分大小来自存储大量 xml 数据的文本字段。此 xml 数据不经常被读取，并且大多数查询不会选择它。
是否有可能将这些数据移动到单独的表中，从而允许在服务器有限的内存中同时保存更多行，从而加快查询速度？
I have an InnoDB table of about 500 000 rows which is growing and I am trying to improve select query performance.
The table is about 2GB big at the moment, most of it's size comes from a text field that stores quite a bit of xml data. This xml data is not read often and most queries don't select it.
Is there a chance that moving this data into a separate table would speed up the queries by allowing more rows to be held in the servers limited memory at once?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
如果表是 InnoDB 并且您没有在 SELECT 中指定文本字段，那么应该没问题。但是，您应该确保主键尽可能小（它位于 gen_clust_index 中）。 请记住，InnoDB 对每个查询执行聚集索引查找，无论非索引查找、索引查找或主键查找。
尽管如此，您提出这个问题的事实表明，仍然需要考虑相反的情况：会有一些查询在结果集中包含文本字段。
我会确保以下几点：MySQL 数据包、InnoDB 日志文件和 InnoDB 日志缓冲区的大小正确。以及为什么要关心这些设置？ 数据行必须穿过这些结构。确保 max_allowed_packet 足够大，足以容纳数据库中最大的文本字段至少 10 倍。 InnoDB 日志文件和 InnoDB 日志缓冲区也是如此。
If the table is InnoDB and you are not specifying the text field in SELECT, you should be OK. However, you should make sure the Primary Key is as small as possible (it is housed in the gen_clust_index). Keep in mind that InnoDB performs a clustered index lookup with every query, regardless of non-Indexed Lookups, Indexed Lookups, or Primary Key Lookups.
Notwithstanding, the fact that you asked this question shows that just the opposite still needs to be considered: There will be some queries that include Text Fields in the result set.
I would make sure of the followiing: MySQL Packets, InnoDB Log Files and InnoDB Log Buffer are sized properly. and Why be concerned with these settings? Rows of Data must pass through these structures. Make sure that max_allowed_packet is large enough to hold the biggest text field you have in the database at least 10 times. Same goes with InnoDB Log Files and the InnoDB Log Buffer.