MySQL TEXT字段性能 [英] MySQL TEXT field performance

问题描述

我的1000个MySQL表中的每个都有几个TEXT和/或MEDIUMTEXT字段.我现在知道，查询时将TEXT字段写入磁盘而不是内存中.即使在查询中未调用该字段，这也成立吗?例如，如果我有一个包含2个字段(id int(11)和注释文本)的表(tbExam)，并且运行了tbExam的SELECT id，MySQL是否仍必须在返回结果之前将其写入磁盘，否则它将运行该查询在内存中?

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?

我正在尝试确定是否需要重新配置实际的数据库表以切换到varchar(xxxx)或保留文本字段并重新配置查询.

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字段是写到磁盘而不是内存中的

I now know that TEXT fields are written to disk rather than in memory when queried

仅当查询需要一个临时表来存储多个排序或聚合操作的中间结果时，才会将

TEXT字段写入磁盘.例如，当在单个查询中的不同列上混合DISTINCT，ORDER BY和GROUP BY时，就会发生这种情况.

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.

如果TEXT列不属于此临时表，则MySQL首先将尝试使用MEMORY引擎(不支持TEXT)创建它.

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引擎.

对于这样的查询:

SELECT  id
FROM    tbExam

，根本不需要临时表.

InnoDB存储引擎插件(负责InnoDB和MySQL之间的交互)的行为在TEXT和VARCHAR字段上的行为略有不同:VARCHAR字段是通过引用将值传递到记录集缓冲区，而通过引用传递TEXT字段.

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.

在内部，InnoDB以相同的方式存储TEXT和VARCHAR字段:如果整列适合页面的一半，则为行内；如果不是，则为行外.上面的区别仅涉及InnoDB / MySQL交互问题.

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字段性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！