我在每个1000个MySQL表中有几个TEXT和/或MEDIUMTEXT字段。 我现在知道TEXT字段在查询时写入磁盘而不是内存。 即使该字段没有在查询中被调用，情况也是如此吗？ 例如，如果我有一个包含2个字段（id int（11）和注释文本）的表（tbExam），并且我运行SELECT id FROM tbExam，MySQL是否仍然必须在返回结果之前将其写入磁盘，或者是否会运行该查询在记忆中？
我试图找出是否需要重新配置我们的实际数据库表来切换到varchar（xxxx）或保留文本字段并重新配置查询。
I have several TEXT and/or MEDIUMTEXT fields in each of our 1000 MySQL tables. I now know that TEXT fields are written to disk rather than in memory when queried. Is that also true even if that field is not called in the query? For example, if I have a table (tbExam) with 2 fields (id int(11) and comment text) and I run SELECT id FROM tbExam, does MySQL still have to write that to disk before returning results or will it run that query in memory?
I am trying to figure out if I need to reconfigure our actual db tables to switch to varchar(xxxx) or keep the text fields and reconfigure the queries.
最满意答案
我现在知道TEXT字段在查询时写入磁盘而不是内存
仅当查询需要临时表来存储多个排序或集合操作的中间结果时， TEXT字段才写入磁盘。 例如，当您在单个查询中的不同列上混合使用DISTINCT ， ORDER BY和GROUP BY时，会发生这种情况。
如果你的TEXT列不是这个临时表的一部分， MySQL将首先尝试使用MEMORY引擎（它不支持TEXT ）来创建它。
只有当这个表的大小超过@@tmp_table_size或者有些列不支持时才会选择MyISAM引擎。
对于像这样的查询：
SELECT id FROM tbExam，根本不需要临时表。
InnoDB存储引擎插件（它负责InnoDB和MySQL之间的交互）在TEXT和VARCHAR字段方面有一些细微差别： VARCHAR字段按值传递到记录集缓冲区，而TEXT字段通过引用传递。
在内部， InnoDB以相同的方式存储TEXT和VARCHAR字段：如果整列适合页面的一半，则行内行，如果不行则不行。 上面的区别只涉及InnoDB / MySQL交互问题。
如果你不查询这些字段，那么根本没有区别。
I now know that TEXT fields are written to disk rather than in memory when queried
TEXT fields are written to disk only when the query requires a temporary table to store intermediate results of multiple sort or aggregate operations. This, for instance, happens when you mix DISTINCT, ORDER BY and GROUP BY on different columns within a single query.
If your TEXT column is not a part of this temporary table, MySQL will first try to create it using MEMORY engine (which does not support TEXT).
MyISAM engine for a temporary table will only be chosen if the size of this table will exceed @@tmp_table_size or there are some columns MEMORY does not support.
For a query like this:
SELECT id FROM tbExam, a temporary table will not be needed at all.
There is a slight difference in how InnoDB storage engine plugin (which is responsible for interaction between InnoDB and MySQL) behaves with respect to TEXT and VARCHAR fields: a VARCHAR field is passed to the recordset buffer by value while a TEXT field is passed by reference.
Internally, InnoDB stores TEXT and VARCHAR fields in a same way: in-row if the whole column fits into half of a page, out-of-row if not. The difference above only concerns InnoDB / MySQL interaction issues.
If you don't query for these fields, then there is no difference at all.
MySQL TEXT字段的性能(MySQL TEXT field performance)我在每个1000个MySQL表中有几个TEXT和/或MEDIUMTEXT字段。 我现在知道TEXT字段在查询时写入磁盘而不是内存。 即使该字段没有在查询中被调用，情况也是如此吗？ 例如，如果我有一个包含2个字段（id int（11）和注释文本）的表（tbExam），并且我运行SELECT id FROM tbExam，MySQL是否仍然必须在返回结果之前将其写入磁盘，或者是否会运行该查询在记忆中？
我试图找出是否需要重新配置我们的实际数据库表来切换到varchar（xxxx）或保留文本字段并重新配置查询。
I have several TEXT and/or MEDIUMTEXT fields in each of our 1000 MySQL tables. I now know that TEXT fields are written to disk rather than in memory when queried. Is that also true even if that field is not called in the query? For example, if I have a table (tbExam) with 2 fields (id int(11) and comment text) and I run SELECT id FROM tbExam, does MySQL still have to write that to disk before returning results or will it run that query in memory?
I am trying to figure out if I need to reconfigure our actual db tables to switch to varchar(xxxx) or keep the text fields and reconfigure the queries.
最满意答案
我现在知道TEXT字段在查询时写入磁盘而不是内存
仅当查询需要临时表来存储多个排序或集合操作的中间结果时， TEXT字段才写入磁盘。 例如，当您在单个查询中的不同列上混合使用DISTINCT ， ORDER BY和GROUP BY时，会发生这种情况。
如果你的TEXT列不是这个临时表的一部分， MySQL将首先尝试使用MEMORY引擎（它不支持TEXT ）来创建它。
只有当这个表的大小超过@@tmp_table_size或者有些列不支持时才会选择MyISAM引擎。
对于像这样的查询：
SELECT id FROM tbExam，根本不需要临时表。
InnoDB存储引擎插件（它负责InnoDB和MySQL之间的交互）在TEXT和VARCHAR字段方面有一些细微差别： VARCHAR字段按值传递到记录集缓冲区，而TEXT字段通过引用传递。
在内部， InnoDB以相同的方式存储TEXT和VARCHAR字段：如果整列适合页面的一半，则行内行，如果不行则不行。 上面的区别只涉及InnoDB / MySQL交互问题。
如果你不查询这些字段，那么根本没有区别。
I now know that TEXT fields are written to disk rather than in memory when queried
TEXT fields are written to disk only when the query requires a temporary table to store intermediate results of multiple sort or aggregate operations. This, for instance, happens when you mix DISTINCT, ORDER BY and GROUP BY on different columns within a single query.
If your TEXT column is not a part of this temporary table, MySQL will first try to create it using MEMORY engine (which does not support TEXT).
MyISAM engine for a temporary table will only be chosen if the size of this table will exceed @@tmp_table_size or there are some columns MEMORY does not support.
For a query like this:
SELECT id FROM tbExam, a temporary table will not be needed at all.
There is a slight difference in how InnoDB storage engine plugin (which is responsible for interaction between InnoDB and MySQL) behaves with respect to TEXT and VARCHAR fields: a VARCHAR field is passed to the recordset buffer by value while a TEXT field is passed by reference.
Internally, InnoDB stores TEXT and VARCHAR fields in a same way: in-row if the whole column fits into half of a page, out-of-row if not. The difference above only concerns InnoDB / MySQL interaction issues.
If you don't query for these fields, then there is no difference at all.
发布评论