有什么理由担心表中的列顺序吗?

2021-11-20 00:00:00 sql mysql database-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.

相关文章