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列应始终放在MySQL的表定义的末尾,因为它们的长度可变,因此可能会减慢查询速度。
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.
他没有具体关于存储引擎,或者它是否仅适用于不经常访问的VARCHAR列。
2 个解决方案
#1
7
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.
询问关于“MySQL”的问题没有用,因为MySQL将存储降级到存储引擎,并且它们以非常不同的方式实现存储。对任何单独的存储引擎提出这个问题都是有意义的。
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.
在MEMORY引擎中,不存在可变长度数据类型。 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.
在MyISAM引擎中,如果表没有任何可变长度数据(VARCHAR,VARBINARY或任何TEXT或BLOB类型),则它是MyISAM的FIXED变体,即记录具有固定的字节长度。这可能会影响性能,尤其是在重复删除和插入数据时(即表不是仅附加表)。只要任何可变长度数据类型是表定义的一部分,它就成为MyISAM的DYNAMIC变体,并且MyISAM在内部将除最短CHAR类型之外的任何类型更改为VARCHAR。同样,CHAR / VARCHAR的位置和甚至定义都无关紧要。
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引擎中,数据存储在16 KB大小的页面中。页面具有带校验和的页脚和页眉,其中包括页面目录。页面目录为每一行包含该行相对于页面开头的偏移量。页面还包含可用空间,所有I / O都以页面形式完成。
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就可以在适当的位置增加VARCHAR,并在页面内移动行,而不会产生任何额外的I / O.此外,由于所有行都被寻址为(页面编号,页面目录条目),因此页面内的行的移动被定位到页面并且从外部不可见。
It also means that for InnoDB too, the order of columns inside a row does not matter at all.
这也意味着对于InnoDB来说,行内的列顺序根本不重要。
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.
这些是最常用于MySQL的三个存储引擎,列的顺序对于这三个中的任何一个都无关紧要。可能存在其他更奇特的存储引擎,但事实并非如此。
#2
3
It does not matter. And some engines store varlena types in a separate area (e.g. TOAST in Postgres).
不要紧。并且一些引擎将varlena类型存储在单独的区域中(例如,Postgres中的TOAST)。
Moreover, the logical order (what you see when you select *) may actually differ from the physical order (how it's stored, which is based on the order in which you've created the actual columns using subsequent alter table statements).
此外,逻辑顺序(您在选择*时看到的内容)实际上可能与物理顺序(它是如何存储的顺序不同,这基于您使用后续alter table语句创建实际列的顺序)。
http://www.sqlskills.com/BLOGS/PAUL/post/Inside-the-Storage-Engine-Anatomy-of-a-record.aspx
#1
7
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.
询问关于“MySQL”的问题没有用,因为MySQL将存储降级到存储引擎,并且它们以非常不同的方式实现存储。对任何单独的存储引擎提出这个问题都是有意义的。
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.
在MEMORY引擎中,不存在可变长度数据类型。 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.
在MyISAM引擎中,如果表没有任何可变长度数据(VARCHAR,VARBINARY或任何TEXT或BLOB类型),则它是MyISAM的FIXED变体,即记录具有固定的字节长度。这可能会影响性能,尤其是在重复删除和插入数据时(即表不是仅附加表)。只要任何可变长度数据类型是表定义的一部分,它就成为MyISAM的DYNAMIC变体,并且MyISAM在内部将除最短CHAR类型之外的任何类型更改为VARCHAR。同样,CHAR / VARCHAR的位置和甚至定义都无关紧要。
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引擎中,数据存储在16 KB大小的页面中。页面具有带校验和的页脚和页眉,其中包括页面目录。页面目录为每一行包含该行相对于页面开头的偏移量。页面还包含可用空间,所有I / O都以页面形式完成。
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就可以在适当的位置增加VARCHAR,并在页面内移动行,而不会产生任何额外的I / O.此外,由于所有行都被寻址为(页面编号,页面目录条目),因此页面内的行的移动被定位到页面并且从外部不可见。
It also means that for InnoDB too, the order of columns inside a row does not matter at all.
这也意味着对于InnoDB来说,行内的列顺序根本不重要。
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.
这些是最常用于MySQL的三个存储引擎,列的顺序对于这三个中的任何一个都无关紧要。可能存在其他更奇特的存储引擎,但事实并非如此。
#2
3
It does not matter. And some engines store varlena types in a separate area (e.g. TOAST in Postgres).
不要紧。并且一些引擎将varlena类型存储在单独的区域中(例如,Postgres中的TOAST)。
Moreover, the logical order (what you see when you select *) may actually differ from the physical order (how it's stored, which is based on the order in which you've created the actual columns using subsequent alter table statements).
此外,逻辑顺序(您在选择*时看到的内容)实际上可能与物理顺序(它是如何存储的顺序不同,这基于您使用后续alter table语句创建实际列的顺序)。
http://www.sqlskills.com/BLOGS/PAUL/post/Inside-the-Storage-Engine-Anatomy-of-a-record.aspx