Select * vs select column

小开

你应该 一直都是只有 select的列，你实际上需要。选择更少而不是更多从来都不会降低效率，而且您也会遇到更少的意想不到的副作用——比如在客户端逐个索引访问结果列，然后通过向表中添加一个新列使这些索引变得不正确。

[编辑] : 意味着访问。愚蠢的大脑仍然醒来。

小开

这立即让我想起我使用的一个表，其中包含 blob类型的列; 它通常包含一个 JPEG 图像，大小为几个 Mb。

不用说，我没有 SELECT的专栏，除非我真的需要它。让这些数据四处漂浮——特别是当我选择多行时——只是一个麻烦。

但是，我必须承认，否则我通常会查询表中的所有列。

小开

最佳答案

它总是拉一个元组(除非在表已经被垂直分段的情况下——分成列部分) ，因此，为了回答您提出的问题，从性能角度来看，这并不重要。但是，由于许多其他原因，(下面)您应该始终按名称专门选择所需的列。

它总是拉一个元组，因为(在我所熟悉的每个供应商的 RDBMS 中) ，所有内容(包括表数据)的底层磁盘存储结构都基于定义的 < em > I/O Pages (例如，在 SQL Server 中，每个 Page 是8 KB)。每个 I/O 的读写都是由 Page 完成的。.也就是说，每次写入或读取都是一个完整的数据页。

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

例外。Select *可以的唯一位置是在子查询中 Exists或 Not Exists谓词子句之后，如下所示:

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

编辑: 为了回应@Mike Sherer 的评论，是的，这是真的，无论是从技术上来说，还是从美学上来说，对你的特殊情况有一点定义。首先，即使请求的列集是存储在某个索引中的列的子集，查询处理器也必须获取存储在该索引中的每个列，而不仅仅是请求的列，原因是相同的——所有 I/O 必须在页中完成，索引数据像表数据一样存储在 IO 页中。因此，如果将索引页的“ tuple”定义为索引中存储的列集，则该语句仍为 true。
这个语句在美学上是正确的，因为它获取的数据是基于 I/O 页面中存储的内容，而不是基于您请求的内容，无论您是访问基表 I/O 页面还是索引 I/O 页面，都是正确的。

有关不使用 Select *的其他原因，请参阅为什么 SELECT *被认为是有害的？:

小开

在生产代码中永远不要使用 SELECT *有以下几个原因:

由于您没有给数据库任何关于所需内容的提示，因此它首先需要检查表的定义，以确定该表上的列。这种查找将花费一些时间(在单个查询中不会花费很多时间) ，但随着时间的推移，这些时间会增加
如果您只需要列的2/3，那么您就选择了需要从磁盘检索并通过网络发送的过多数据的1/3
如果您开始依赖数据的某些方面，例如返回列的顺序，那么一旦重新组织表并添加新列(或删除现有列) ，您可能会得到一个令人不快的惊喜
在 SQLServer 中(不确定是否需要其他数据库) ，如果需要列的子集，总是可能有非聚集索引覆盖该请求(包含所需的所有列)。有了 SELECT *你从一开始就放弃了这种可能性。在这种特殊情况下，数据将从索引页(如果索引页包含所有必需的列)中检索出来，因此与执行 SELECT *....查询相比，磁盘 I/O 还有内存开销要小得多。

是的，一开始需要多一点的输入(像 SQL Server 的 SQL 提示符工具甚至可以帮助你)——但是在这种情况下确实有一个没有任何例外的规则: 永远不要在你的生产代码中使用 SELECT * 。从来没有。

小开

在 SQL 选择期间，DB 总是引用表的元数据，而不管它对于 SELECT a、 b、 c 是否是 SELECT * 。.为什么？因为那是关于系统中表的结构和布局的信息所在的位置。

它必须读取这些信息有两个原因。第一，简单地编译语句。它至少需要确保您指定了一个现有的表。此外，自上次执行语句以来，数据库结构可能已经更改。

现在，很明显，数据库元数据缓存在系统中，但它仍然在处理需要完成的工作。

接下来，使用元数据生成查询计划。每次编译语句时也会发生这种情况。同样，这会针对缓存的元数据运行，但它总是这样做。

只有在数据库使用预编译查询或缓存了以前的查询时才不执行此处理。这是使用绑定参数而不是字面 SQL 的参数。“ SELECT * FROM TABLE WHERE key = 1”与“ SELECT * FROM TABLE WHERE key = ?”是不同的查询“1”在调用中被绑定。

数据库严重依赖页缓存来完成这些工作。许多现代数据库足够小，可以完全容纳在内存中(或者，也许我应该说，现代内存足够大，可以容纳许多数据库)。然后，后端的主要 I/O 成本是日志记录和页面刷新。

但是，如果您仍然需要访问数据库的磁盘，许多系统所做的主要优化是依赖于索引中的数据，而不是表本身。

如果你有:

CREATE TABLE customer (
id INTEGER NOT NULL PRIMARY KEY,
name VARCHAR(150) NOT NULL,
city VARCHAR(30),
state VARCHAR(30),
zip VARCHAR(10));


CREATE INDEX k1_customer ON customer(id, name);

然后，如果执行“ SELECT id，name FROM customer WHERE id = 1”，那么 DB 很可能从索引而不是从表中提取这些数据。

为什么？它可能会使用索引来满足查询(相对于表扫描) ，即使 where 子句中没有使用“ name”，该索引仍然是查询的最佳选项。

现在数据库已经拥有了满足查询所需的所有数据，因此没有理由访问表页本身。使用索引可以减少磁盘流量，因为索引中的行密度高于一般的表。

这是对一些数据库使用的特定优化技术的简单说明。许多公司都有一些优化和调优技术。

最后，SELECT * 对于必须手工输入的动态查询非常有用，我绝不会用它来输入“真正的代码”。单个列的标识为 DB 提供了更多信息，可以用来优化查询，并使您能够在代码中更好地控制模式更改等等。

小开

我认为你的问题没有确切的答案，因为你有思考的性能和设施，维护您的应用程序。Select column比 select *更具有性能，但是如果你正在开发一个面向对象系统，那么你会喜欢使用 object.properties，你可以在应用程序的任何部分需要一个属性，那么如果你不使用 select *并填充所有的属性，那么你将需要编写更多的方法来获得特殊情况下的属性。您的应用程序需要有一个良好的性能使用 select *，在某些情况下，您将需要使用选择列，以提高性能。然后你将会拥有两个世界中更好的一个，当你需要性能的时候，你可以编写和维护应用程序和性能。

小开

除非您存储了大的斑点，否则性能不是问题。不使用 SELECT * 的一个重要原因是，如果使用返回的行作为元组，那么列将按照模式指定的顺序返回，如果这种情况发生变化，则必须修复所有代码。

另一方面，如果使用字典样式的访问，那么列的返回顺序并不重要，因为您总是通过名称访问它们。

小开

这里公认的答案是错误的。当另一个问题被关闭时，我偶然发现了这个问题(当时我还在写我的答案-grr-因此下面的 SQL 引用了另一个问题)。

应该始终使用 SELECT 属性、属性... . NOT SELECT *

主要是为了解决性能问题。

从用户中选择名称 WHERE name = ‘ John’;

这不是一个非常有用的例子。相反，考虑一下:

SELECT telephone FROM users WHERE name='John';

如果在(姓名，电话)上有一个索引，那么查询就可以解析，而不必从表中查找相关的值-有一个掩护索引。

此外，假设该表有一个 BLOB，其中包含用户的图片、上传的简历和电子表格..。使用 SELECT * 将把所有这些信息拉回到 DBMS 缓冲区(强制从缓存中取出其他有用的信息)。然后，它将全部发送到客户端使用网络上的时间和客户端上的内存的数据是冗余的。

如果客户端以枚举数组的形式检索数据(比如 PHP 的 MYSQL _ get _ array ($x，MYSQL _ NUM)) ，也会导致函数问题。也许当代码写成“ phone”时，SELECT * 返回的第三列是“ phone”，但是有人过来决定在表中添加一个电子邮件地址，位于“ phone”之前。所需的字段现在转移到第4列。

小开

不管怎样，做事情都是有原因的。我在 PostgreSQL 中经常使用 SELECT * ，因为在 PostgreSQL 中有很多事情可以使用 SELECT * 来做，但是在显式列列表中无法做到，特别是在存储过程中。类似地，在 Informix 中，继承的表树上的 SELECT * 可以提供参差不齐的行，而显式列列表不能提供参差不齐的行，因为子表中的其他列也会被返回。

我在 PostgreSQL 中这样做的主要原因是它确保我得到特定于表的格式良好的类型。这允许我获取结果并将它们用作 PostgreSQL 中的表类型。与严格的列列表相比，这还允许在查询中使用更多的选项。

另一方面，严格的列列表可以在应用程序级别检查 db 模式是否在某些方面发生了变化，这会很有帮助。(我在另一个层面上进行这种检查。)

至于性能，我倾向于使用 VIEW 和存储过程返回类型(然后在存储过程中使用列列表)。这使我能够控制返回哪些类型。

但请记住，我使用的是 SELECT * ，通常针对的是抽象层，而不是基表。

小开

摘自本文的参考文献:

没有精选 * : 当您使用“ SELECT *”时，您正在从数据库中选择更多的列，其中一些列可能不会被应用程序使用。这将增加数据库系统的成本和负载，并增加网络上的数据传输。

精选 * : 如果您有特殊要求，并创建了动态环境时添加或删除列自动处理的应用程序代码。在这种特殊情况下，您不需要更改应用程序和数据库代码，这将自动影响生产环境。在这种情况下，您可以使用“ SELECT *”。

小开

只是为了给这个讨论添加一点我在这里没有看到的细微差别: 就 I/O 而言，如果您使用的是带有面向列的存储器面向列的存储器的数据库，那么如果您只查询某些列，则可以减少大量的 I/O 操作。随着我们转向 SSD，与面向行的存储相比，好处可能会稍微小一些，但是 a)只读取包含您关心的列的块 b)压缩，这通常会大大减少磁盘上数据的大小，从而减少从磁盘读取的数据量。

如果您不熟悉面向列的存储，那么 Postgres 的一个实现来自 Citus Data，另一个实现来自 Greenplum，另一个 Paraccel，还有一个(宽松地说)来自 Amazon Redshift。对于 MySQL，有 Infobright，即现在几乎不存在的 InfiniDB。其他商业产品包括惠普的 Vertica，Sybase IQ，Teradata..。

小开

select * from table1 INTERSECT  select * from table2

平等

select distinct t1 from table1 where Exists (select t2 from table2 where table1.t1 = t2 )