是否有任何理由担心表中的列顺序? [英] Is there any reason to worry about the column order in a table?

问题描述

我知道您可以使用FIRST和AFTER更改MySQL中的列顺序，但是为什么要麻烦呢?由于良好的查询会在插入数据时显式命名列，因此真的有任何理由要关心列在表中的顺序吗?

I know you can ALTER the column order in MySQL with FIRST and AFTER, but why would you want to bother? Since good queries explicitly name columns when inserting data, is there really any reason to care what order your columns are in in the table?

推荐答案

列顺序对我调整过的某些数据库(包括Sql Server，Oracle和MySQL)产生了很大的性能影响.该帖子具有好的经验法则:

Column order had a big performance impact on some of the databases I've tuned, spanning Sql Server, Oracle, and MySQL. This post has good rules of thumb:

• 主键列优先
• 接下来是外键列.
• 接下来经常搜索的列
• 以后经常更新的列
• 最后是空列.
• 在更常用的可空列之后，最少使用可空列

性能差异的一个示例是索引查找.数据库引擎根据索引中的某些条件查找行，然后获取行地址.现在说您正在寻找SomeValue，它在此表中:

An example for difference in performance is an Index lookup. The database engine finds a row based on some conditions in the index, and gets back a row address. Now say you are looking for SomeValue, and it's in this table:

 SomeId int,
 SomeString varchar(100),
 SomeValue int

引擎必须猜测SomeValue从哪里开始，因为SomeString的长度未知.但是，如果您将顺序更改为:

The engine has to guess where SomeValue starts, because SomeString has an unknown length. However, if you change the order to:

 SomeId int,
 SomeValue int,
 SomeString varchar(100)

现在，引擎知道可以在行开始后的4个字节处找到SomeValue.因此，列顺序会对性能产生重大影响.

Now the engine knows that SomeValue can be found 4 bytes after the start of the row. So column order can have a considerable performance impact.

Sql Server 2005在行的开头存储定长字段.并且每一行都有对varchar开头的引用.这完全否定了我上面列出的效果.因此，对于最近的数据库，列顺序不再有任何影响.

Sql Server 2005 stores fixed-length fields at the start of the row. And each row has a reference to the start of a varchar. This completely negates the effect I've listed above. So for recent databases, column order no longer has any impact.

这篇关于是否有任何理由担心表中的列顺序?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！