问题描述
数据库中列类型的顺序对查询时间有影响吗?
Does the order of the column types in your database have any affect on the query time?
例如，具有混合顺序(INT，TEXT，VARCHAR，INT，TEXT)的表比具有连续类型(INT，INT，VARCHAR，TEXT，TEXT)的表的查询速度慢吗?
For example, would a table with mixed ordering (INT, TEXT, VARCHAR, INT, TEXT) be slower to query than a table with consecutive types (INT, INT, VARCHAR, TEXT, TEXT)?
推荐答案
答案是肯定的，确实很重要，并且可能很重要，但通常并不多.
The answer is yes, it does matter, and it can matter a great deal, but usually not much.
所有I/O在页面级别完成(通常2K或4K，具体取决于您的操作系统).行的列数据彼此相邻存储，除非页面已满，否则在另一页(通常是下一页)上写入数据.
All I/O is done at a page level (typically 2K or 4K depending on your OS). Column data for rows are stored next to each other, except when the page becomes full, in which case the data is written on the another (usually the next) page.
(基于表定义)所选列之间的列所需的磁盘数据空间越大，所选列的数据(有时)在不同页面上的机会就越大.在其他页面上可能会导致额外的I/O操作(如果在另一页面上没有选择其他行).在最坏的情况下，您选择的每一列可能在不同的页面上.
The greater the on-disk data space required for columns between (based on the the table definition) the columns you select, the greater the chance that the data for the selected columns will (sometimes) be on different pages. Being on a different page may result in an extra I/O operation (if there are no other rows being selected on the other page). In the worst case, each column you select could be on a different page.
这是一个例子:
create table bad_layout (
num1 int,
large1 varchar(4000),
num2 int,
large2 varchar(4000),
num3 int,
large3 varchar(4000)
);
create table better_layout (
num1 int,
num2 int,
num3 int,
large1 varchar(4000),
large2 varchar(4000),
large3 varchar(4000)
);
比较:从bad_layout中选择num1，num2，num3;从Better_layout中选择num1，num2，num3;
Comparing:select num1, num2, num3 from bad_layout;select num1, num2, num3 from better_layout;
由于bad_layout的原因，每个num列基本上都将位于不同的页面上，因此每一行将需要3个i/o操作.相反，对于Better_layout，通常将num列放在同一页上.
Because for bad_layout each num column is basically going to be on a different page, each row will require 3 i/O operations. Conversely, for better_layout num columns are usually going to be on the same page.
bad_layout查询的执行时间可能大约要长3倍.
The bad_layout query is likely to take about 3 times longer to execute.
良好的表布局可以对查询性能产生很大的影响.您应该尽量使通常在一起选择的列在表布局中尽可能地彼此靠近.
Good table layout can make a large difference to query performance. You should try to keep columns that are usually selected together as close as possible to each other in the table layout.
这篇关于基于列顺序的查询速度的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！