选择 * 与选择列 [英] select * vs select column

问题描述

如果我只需要 2/3 列并且我查询 SELECT * 而不是在选择查询中提供这些列，是否会因增加/减少 I/O 或内存而导致性能下降?

If I just need 2/3 columns and I query SELECT * instead of providing those columns in select query, is there any performance degradation regarding more/less I/O or memory?

如果我在不需要的情况下选择 *，可能会出现网络开销.

The network overhead might be present if I do select * without a need.

但是在选择操作中，数据库引擎是总是从磁盘中提取原子元组，还是只提取选择操作中请求的那些列?

But in a select operation, does the database engine always pull atomic tuple from the disk, or does it pull only those columns requested in the select operation?

如果它总是拉一个元组，那么 I/O 开销是一样的.

If it always pulls a tuple then I/O overhead is the same.

同时，如果提取元组，则可能会消耗内存以从元组中剥离请求的列.

At the same time, there might be a memory consumption for stripping out the requested columns from the tuple, if it pulls a tuple.

所以如果是这样的话，select someColumn 会比 select 有更多的内存开销 *

So if that's the case, select someColumn will have more memory overhead than that of select *

推荐答案

它总是拉一个元组(除非表格被垂直分割 - 分解成列块)，所以，为了回答你提出的问题，从性能的角度来看，这无关紧要.但是，出于许多其他原因，(如下)您应该始终按名称专门选择您想要的那些列.

It always pulls a tuple (except in cases where the table has been vertically segmented - broken up into columns pieces), so, to answer the question you asked, it doesn't matter from a performance perspective. However, for many other reasons, (below) you should always select specifically those columns you want, by name.

它总是拉一个元组，因为(在我熟悉的每个供应商 RDBMS 中)，所有东西(包括表数据)的底层磁盘存储结构都基于定义的 I/O 页(例如，在 SQL Server 中，每个页面为 8 KB).并且每次 I/O 读取或写入都是按页进行的.也就是说，每次写入或读取都是一个完整的数据页.

It always pulls a tuple, because (in every vendors RDBMS I am familiar with), the underlying on-disk storage structure for everything (including table data) is based on defined I/O Pages (in SQL Server for e.g., each Page is 8 kilobytes). And every I/O read or write is by Page.. I.e., every write or read is a complete Page of data.

由于这种底层结构约束，结果是数据库中的每一行数据必须始终位于一页上，而且只有一页.它不能跨越多个数据页(除了像 blob 这样的特殊事物，其中实际的 blob 数据存储在单独的页块中，然后实际的表行列只获得一个指针......).但这些例外只是，例外，一般不适用，除非在特殊情况下(针对特殊类型的数据，或针对特殊情况的某些优化)
即使在这些特殊情况下，通常情况下，实际表行数据本身(包含指向 Blob 实际数据的指针，或其他)，也必须存储在单个 IO 页面上...

Because of this underlying structural constraint, a consequence is that Each row of data in a database must always be on one and only one page. It cannot span multiple Pages of data (except for special things like blobs, where the actual blob data is stored in separate Page-chunks, and the actual table row column then only gets a pointer...). But these exceptions are just that, exceptions, and generally do not apply except in special cases ( for special types of data, or certain optimizations for special circumstances)
Even in these special cases, generally, the actual table row of data itself (which contains the pointer to the actual data for the Blob, or whatever), it must be stored on a single IO Page...

例外.Select * 唯一正常的地方是在 Exists 或 Not Exists 谓词子句之后的子查询中，如:

EXCEPTION. The only place where Select * is OK, is in the sub-query after an Exists or Not Exists predicate clause, as in:

   Select colA, colB
   From table1 t1
   Where Exists (Select * From Table2
                 Where column = t1.colA)

为了解决@Mike Sherer 的评论，是的，在技术上，对您的特殊情况和美学都有一些定义.首先，即使请求的列集是存储在某个索引中的列的子集，查询处理器也必须获取存储在该索引中的每一列，而不仅仅是请求的列，出于同样的原因 - ALLI/O必须分页进行，索引数据和表数据一样存放在IO Pages中.因此，如果您将索引页的元组"定义为存储在索引中的列集，则该语句仍然为真.
并且该语句在美学上是正确的，因为关键是它根据存储在 I/O 页中的内容而不是您请求的内容获取数据，无论您是访问基表 I/O 页还是索引，这都是正确的I/O 页面.

To address @Mike Sherer comment, Yes it is true, both technically, with a bit of definition for your special case, and aesthetically. First, even when the set of columns requested are a subset of those stored in some index, the query processor must fetch every column stored in that index, not just the ones requested, for the same reasons - ALL I/O must be done in pages, and index data is stored in IO Pages just like table data. So if you define "tuple" for an index page as the set of columns stored in the index, the statement is still true.
and the statement is true aesthetically because the point is that it fetches data based on what is stored in the I/O page, not on what you ask for, and this true whether you are accessing the base table I/O Page or an index I/O Page.

由于其他原因不使用 Select *，请参阅 为什么是SELECT * 被认为有害? :

For other reasons not to use Select *, see Why is SELECT * considered harmful? :

这篇关于选择 * 与选择列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！