VARCHAR 列应该放在 MySQL 表定义的末尾吗? [英] Should VARCHAR columns be put at the end of table definitions in MySQL?

问题描述

我听说(从一位同事那里听说过，他从另一位开发人员那里听说过)VARCHAR 列应该始终放在 MySQL 中表定义的末尾，因为它们的长度是可变的，因此可能会减慢查询速度.

I've heard (from a colleague, who heard it from another developer) that VARCHAR columns should always be put at the end of a table definition in MySQL, because they are variable in length and could therefore slow down queries.

我对堆栈溢出所做的研究似乎与此相矛盾，并表明列顺序很重要，而对于 VARCHAR 的适用程度存在不同的共识.

The research I've done on stack overflow seems to contradict this however and suggests that column order is important, while there is varying agreement on how much this applies to VARCHARs.

他没有具体说明存储引擎，也没有说明这是否仅适用于不常访问的 VARCHAR 列.

He wasn't specific about storage engines, or about whether this only applied to VARCHAR columns which are infrequently accessed.

推荐答案

提出有关MySQL"的问题没有帮助，因为 MySQL 将存储降级为存储引擎，并且它们以非常不同的方式实现存储.对任何单个存储引擎提出这个问题都是有意义的.

Asking that question about "MySQL" is not helpful, as MySQL relegates storage to storage engines, and they implement storage in very different ways. It makes sense to ask this question for any individual storage engine.

在 MEMORY 引擎中，不存在可变长度数据类型.VARCHAR 被悄悄地更改为 CHAR.在您的问题的上下文中:将 VARCHAR 放在表定义中的位置无关紧要.

In the MEMORY engine, variable length data types do not exist. A VARCHAR is silently changed into a CHAR. In the context of your question: It does not matter where in a table definition you put your VARCHAR.

在 MyISAM 引擎中，如果表没有任何可变长度数据(VARCHAR、VARBINARY 或任何 TEXT 或 BLOB 类型)，则它是 MyISAM 的 FIXED 变体，即记录具有固定的字节长度.这可能会对性能产生影响，特别是如果数据被重复删除和插入(即表不是仅附加的).一旦任何可变长度数据类型成为表定义的一部分，它就会成为 MyISAM 的 DYNAMIC 变体，并且 MyISAM 在内部将除最短的 CHAR 类型以外的任何类型更改为 VARCHAR.同样，CHAR/VARCHAR 的位置甚至定义都无关紧要.

In the MyISAM engine, if a table has no variable length data whatsoever (VARCHAR, VARBINARY or any TEXT or BLOB type) it is of the FIXED variant of MyISAM, that is, records have a fixed byte length. This can have performance implications, especially if data is deleted and inserted repeatedly (i.e. the table is not append only). As soon as any variable length data type is part of a table definition it becomes the DYNAMIC variant of MyISAM, and MyISAM internally changes any but the shortest CHAR type internally to VARCHAR. Again, position and even definition of CHAR/VARCHAR do not matter.

在 InnoDB 引擎中，数据存储在 16 KB 大小的页面中.一个页面有一个带有校验和的页脚和一个页眉，还有一个页面目录.页目录为每一行包含该行相对于页开头的偏移量.一个页面也包含空闲空间，所有的 I/O 都是在页面中完成的.

In the InnoDB engine, data is stored in pages of 16 KB size. A page has a page footer with a checksum, and a page header, with among other things a page directory. The page directory contains for each row the offset of that row relative to the beginning of the page. A page also contains free space, and all I/O is done in pages.

因此，只要页面中有可用空间，InnoDB 就可以原地增长 VARCHAR，并在页面内移动行，而不会产生任何额外的 I/O.此外，由于所有行都被寻址为 (pagenumber, page directory entry)，页面内行的移动被本地化到页面并且从外部不可见.

Hence InnoDB can, as long as there is free space in a page, grow VARCHAR in place, and move rows around inside a page, without incurring any additional I/O. Also, since all rows are being addressed as (pagenumber, page directory entry), movement of a row inside a page is localized to the page and not visible from the outside.

这也意味着对于 InnoDB 来说，行内列的顺序根本无关紧要.

It also means that for InnoDB too, the order of columns inside a row does not matter at all.

这是 MySQL 最常用的三个存储引擎，列的顺序对这三个引擎中的任何一个都无关紧要.可能存在其他更奇特的存储引擎，但事实并非如此.

These are the three storage engines that are most commonly used with MySQL, and order of columns does not matter for any of these three. It may be that other, more exotic storage engines exist for which this is not true.

这篇关于VARCHAR 列应该放在 MySQL 表定义的末尾吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！