有三个主要原因:

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 *”，

我发现使用“SELECT *”的原因只有一个。

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

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

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

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

SELECT语句中的星号“*”是查询中涉及的表中所有列的简写。

性能

简写可能会慢一些，因为:

并不是所有的字段都被索引，这迫使对整个表进行扫描——效率较低 保存到通过线路发送SELECT *的内容可能会导致全表扫描 返回比需要的更多的数据 使用可变长度数据类型返回尾随列会导致搜索开销

维护

当使用SELECT *时:

Someone unfamiliar with the codebase would be forced to consult documentation to know what columns are being returned before being able to make competent changes. Making code more readable, minimizing the ambiguity and work necessary for people unfamiliar with the code saves more time and effort in the long run. If code depends on column order, SELECT * will hide an error waiting to happen if a table had its column order changed. Even if you need every column at the time the query is written, that might not be the case in the future the usage complicates profiling

设计

SELECT *是一个反模式:

查询的目的不那么明显;应用程序使用的列是不透明的 它打破了尽可能使用严格类型的模块化规则。Explicit几乎在所有情况下都更好。

什么时候应该使用“SELECT *”?

如果显式地需要涉及表中的每一列，而不是编写查询时存在的每一列，则使用SELECT *是可以接受的。数据库将在内部将*展开为完整的列列表—没有性能差异。

否则，显式地列出要在查询中使用的每一列—最好是在使用表别名时。

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

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

在很多情况下，SELECT *会在应用程序的运行时导致错误，而不是在设计时。它隐藏了应用程序中列更改或坏引用的信息。