在你的MySQL表中列顺序是否重要？ [英] Does column order matter in your MySQL tables?

问题描述

在学习mysql时，我读到在向mysql表添加列时可以执行以下语句：

While learning mysql, I read that you can perform the following statement when adding a column to a mysql table:

ALTER TABLE contacts ADD email VARCHAR(60) AFTER name;

或

ALTER TABLE contacts ADD email VARCHAR(60) FIRST;

您要什么时候这样做？列顺序可以用于查询优化吗？ longblob是否应该是最后一列来优化空间消耗？或者，由于其他原因，这些命令是否存在？

When would you want to do this? Can column order be utilized for query optimization purposes? Should longblobs be the last column to optimize space consumption? Or do these commands exist for some other reason?

推荐答案

问题与关系模型或SQL无关。这是一个性能问题。

The question has nothing to do with the relational model or SQL. It is a performance question.

在某些数据库中，由于执行磁盘访问的方式，以特定方式排序列更为高效。是否有显着的优势是平台特定的，以及。这是与底层存储设计方式和引擎访问方式相关的低级I / O问题。专有发动机提供商通常通过其教育和培训部门提供此信息。

In some databases, it is more efficient to order the columns in a specific manner because of the way the disk access is performed. Whether there is significant advantage is platform specific, as well. It is a low-level i/o issue related to the way the underlying storage is designed and the way it is accessed by the engine. Proprietary engine providers generally provide this information via their education and training departments.

我认为你必须和那些知道你的特定平台上的MySQL存储模型和i / o方法的细节细节的人交谈，标记在您的平台上，以获得答案。

I think you would have to talk to someone who knows the nitty gritty details of the storage model and i/o methods for MySQL on your specific platform or someone who has bench-marked this on your platform in order to get an answer.

这是完全可能的，他们以优化的方式将它们放在磁盘上，并隐藏您的列排序。

It's entirely possible they lay it down on disk in an optimized manner and hide that column ordering from you.

这篇关于在你的MySQL表中列顺序是否重要？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！