在设计方案之前理解您的需求(如果可能的话)。

了解数据， 1)索引 2)所使用的存储类型; 3)供应商引擎或功能;即……缓存，内存功能 4)数据类型 5)桌子大小 6)查询频率 7)如果资源是共享的，相关的工作量 8)测试

A)要求会有所不同。如果硬件不能支持预期的工作负载，则应该重新评估如何在工作负载中提供需求。关于向表中添加的列。如果数据库支持视图，您可以使用特定的命名列创建特定数据的索引(?)视图(vs.选择'*')。定期检查您的数据和模式，以确保您永远不会遇到“输入垃圾”->“输出垃圾”综合征。

假设没有其他解;你可以考虑以下几点。一个问题总是有多种解决方案。

1)索引:select *将执行一个表罐。根据各种因素，这可能涉及到磁盘寻道和/或与其他查询的争用。如果表是多用途的，请确保所有查询都是高性能的，并在您的目标时间以下执行。如果有大量数据，而您的网络或其他资源没有调优;你需要考虑到这一点。数据库是一个共享环境。

2)存储类型。Ie:如果你使用SSD，磁盘或内存。I/O时间和系统/cpu上的负载会有所不同。

3) DBA是否可以调优数据库/表以获得更高的性能?假设出于某种原因，团队已经决定选择“*”是问题的最佳解决方案;可以将DB或表加载到内存中。(或者其他方法…也许反应被设计成有2-3秒的延迟?——而广告的作用是为公司赚取收入……)

4)从基线开始。了解您的数据类型，以及如何显示结果。更小的数据类型、字段数量会减少结果集中返回的数据量。这将为其他系统需求留下可用资源。系统资源通常是有限制的;“总是”工作低于这些限制，以确保稳定性和可预测的行为。

5)表/数据的大小。选择“*”在小表中很常见。它们通常适合内存，并且响应时间很快。再次……回顾您的需求。特征蠕变计划;总是为当前和未来可能的需求做计划。

6)查询/查询频率。了解系统上的其他工作负载。如果这个查询每秒发出一次，并且表很小。结果集可以设计为保留在缓存/内存中。然而，如果查询是一个频繁的批处理过程，有千兆字节/兆兆字节的数据……最好分配额外的资源以确保其他工作负载不受影响。

7) Related workloads. Understand how the resources are used. Is the network/system/database/table/application dedicated, or shared? Who are the stakeholders? Is this for production, development, or QA? Is this a temporary "quick fix". Have you tested the scenario? You'll be surprised how many problems can exist on current hardware today. (Yes, performance is fast...but the design/performance is still degraded.) Does the system need to performance 10K queries per second vs. 5-10 queries per second. Is the database server dedicated, or do other applications, monitoring execute on the shared resource. Some applications/languages; O/S's will consume 100% of the memory causing various symptoms/problems.

8)测试:测试你的理论，尽可能多地理解。你选择的“*”问题可能是一个大问题，或者它可能是你甚至不需要担心的事情。

If you name the columns in a SELECT statement, they will be returned in the order specified, and may thus safely be referenced by numerical index. If you use "SELECT *", you may end up receiving the columns in arbitrary sequence, and thus can only safely use the columns by name. Unless you know in advance what you'll be wanting to do with any new column that gets added to the database, the most probable correct action is to ignore it. If you're going to be ignoring any new columns that get added to the database, there is no benefit whatsoever to retrieving them.

即使您现在希望选择每一列，也可能不希望在某人添加一个或多个新列之后选择每一列。如果您使用SELECT *来编写查询，那么在某些时候，您可能会冒这样的风险，即有人可能会添加一列文本，从而使您的查询运行得更慢，即使您实际上并不需要该列。

如果您添加了想要的新列，这难道不意味着需要更改的代码更少吗?

如果您确实想要使用新列，那么无论如何您都必须对代码进行大量其他更改。你只保存，new_column -只有几个字符的输入。

有三个主要原因:

Inefficiency in moving data to the consumer. When you SELECT *, you're often retrieving more columns from the database than your application really needs to function. This causes more data to move from the database server to the client, slowing access and increasing load on your machines, as well as taking more time to travel across the network. This is especially true when someone adds new columns to underlying tables that didn't exist and weren't needed when the original consumers coded their data access. Indexing issues. Consider a scenario where you want to tune a query to a high level of performance. If you were to use *, and it returned more columns than you actually needed, the server would often have to perform more expensive methods to retrieve your data than it otherwise might. For example, you wouldn't be able to create an index which simply covered the columns in your SELECT list, and even if you did (including all columns [shudder]), the next guy who came around and added a column to the underlying table would cause the optimizer to ignore your optimized covering index, and you'd likely find that the performance of your query would drop substantially for no readily apparent reason. Binding Problems. When you SELECT *, it's possible to retrieve two columns of the same name from two different tables. This can often crash your data consumer. Imagine a query that joins two tables, both of which contain a column called "ID". How would a consumer know which was which? SELECT * can also confuse views (at least in some versions SQL Server) when underlying table structures change -- the view is not rebuilt, and the data which comes back can be nonsense. And the worst part of it is that you can take care to name your columns whatever you want, but the next guy who comes along might have no way of knowing that he has to worry about adding a column which will collide with your already-developed names.

但这对SELECT *来说也不全是坏事。我在以下用例中大量使用它:

Ad-hoc queries. When trying to debug something, especially off a narrow table I might not be familiar with, SELECT * is often my best friend. It helps me just see what's going on without having to do a boatload of research as to what the underlying column names are. This gets to be a bigger "plus" the longer the column names get. When * means "a row". In the following use cases, SELECT * is just fine, and rumors that it's a performance killer are just urban legends which may have had some validity many years ago, but don't now: SELECT COUNT(*) FROM table; in this case, * means "count the rows". If you were to use a column name instead of * , it would count the rows where that column's value was not null. COUNT(*), to me, really drives home the concept that you're counting rows, and you avoid strange edge-cases caused by NULLs being eliminated from your aggregates. Same goes with this type of query: SELECT a.ID FROM TableA a WHERE EXISTS ( SELECT * FROM TableB b WHERE b.ID = a.B_ID); in any database worth its salt, * just means "a row". It doesn't matter what you put in the subquery. Some people use b's ID in the SELECT list, or they'll use the number 1, but IMO those conventions are pretty much nonsensical. What you mean is "count the row", and that's what * signifies. Most query optimizers out there are smart enough to know this. (Though to be honest, I only know this to be true with SQL Server and Oracle.)

一般来说，你必须匹配你的SELECT *…不同类型的数据结构。如果不指定结果到达的顺序，正确排列所有内容可能会很棘手(而且更模糊的字段更容易错过)。

通过这种方式，您可以出于各种原因向表中添加字段(甚至在表中间)，而不会破坏整个应用程序中的sql访问代码。

这里有一个重要的区别，我认为大多数答案都忽略了。

SELECT *不是问题。返回SELECT *的结果是问题所在。

举个例子，在我看来:

WITH data_from_several_tables AS (
    SELECT * FROM table1_2020
        UNION ALL
    SELECT * FROM table1_2021
    ...
)
SELECT id, name, ...
FROM data_from_several_tables
WHERE ...
GROUP BY ...
...

这避免了大多数答案中提到的使用SELECT *的所有“问题”:

读取的数据比预期的多?现代数据库中的优化器会意识到实际上并不需要所有列 源表的列顺序会影响输出吗?我们仍然选择和 显式返回数据。 消费者不能看到他们从SQL?您所操作的列在代码中是显式的。 索引可能不被使用?同样，现代优化器应该处理这个问题，就像我们没有选择*一样

这里有一个可读性/可重构性的优势——不需要重复很长的列列表或其他常见的查询子句(如过滤器)。如果在使用SELECT *和SELECT <columns>(在绝大多数情况下-显然总是在关键情况下配置运行代码)时，查询计划有任何不同，我会感到惊讶。