不使用select *的原因是什么? [英] What is the reason not to use select *?

问题描述

我看到很多人声称您应该在选择查询中明确命名您想要的每一列.

I've seen a number of people claim that you should specifically name each column you want in your select query.

假设我无论如何都会使用所有的列，为什么我不使用 SELECT *?

Assuming I'm going to use all of the columns anyway, why would I not use SELECT *?

即使考虑这个问题 *SQL 查询 - 选择* from view 或 Select col1, col2, ... colN from view*，我不认为这是完全重复的，因为我从稍微不同的角度处理这个问题.

Even considering the question *SQL query - Select * from view or Select col1, col2, … colN from view*, I don't think this is an exact duplicate as I'm approaching the issue from a slightly different perspective.

我们的原则之一是在时机成熟之前不进行优化.考虑到这一点，似乎使用 SELECT * 应该是 首选 方法，直到它被证明是资源问题或架构几乎一成不变.正如我们所知，在开发完成之前不会发生这种情况.

One of our principles is to not optimize before it's time. With that in mind, it seems like using SELECT * should be the preferred method until it is proven to be a resource issue or the schema is pretty much set in stone. Which, as we know, won't occur until development is completely done.

也就是说，不使用 SELECT * 是否存在首要问题?

That said, is there an overriding issue to not use SELECT *?

推荐答案

不要过早优化的名言的本质是编写简单明了的代码，然后然后使用分析器指出热点，然后您可以对其进行优化以提高效率.

The essence of the quote of not prematurely optimizing is to go for simple and straightforward code and then use a profiler to point out the hot spots, which you can then optimize to be efficient.

当您使用 select * 时，您将无法进行配置文件，因此您没有写清楚 &简单的代码，你违背了报价的精神.select * 是一种反模式.

When you use select * you're make it impossible to profile, therefore you're not writing clear & straightforward code and you are going against the spirit of the quote. select * is an anti-pattern.

因此选择列不是过早的优化.我头顶上的一些事情......

So selecting columns is not a premature optimization. A few things off the top of my head ....

1. 如果您在 SQL 语句中指定列，如果从表中删除该列并执行查询，则 SQL 执行引擎将出错.
2. 您可以更轻松地扫描使用该列的代码.
3. 您应该始终编写查询以带回最少的信息.
4. 正如其他人提到的，如果您使用序数列访问，则永远不应使用 select *
5. 如果您的 SQL 语句连接表，则 select * 会为您提供连接中所有表的所有列

推论是使用 select * ...

1. 应用程序使用的列是不透明的
2. DBA 及其查询分析器无法帮助您的应用程序降低性能
3. 发生变化时代码更脆弱
4. 您的数据库和网络正在受到影响，因为它们带回了过多的数据 (I/O)
5. 数据库引擎优化最少，因为您不考虑(逻辑)带回所有数据.

<小时>

编写正确的 SQL 就像编写 Select * 一样简单.所以真正的懒人会写正确的 SQL，因为他们不想重新访问代码并试图记住他们在做的时候做了什么.他们不想向 DBA 解释每一段代码.他们不想向客户解释为什么应用程序像狗一样运行.

---

Writing correct SQL is just as easy as writing Select *. So the real lazy person writes proper SQL because they don't want to revisit the code and try to remember what they were doing when they did it. They don't want to explain to the DBA's about every bit of code. They don't want to explain to their clients why the application runs like a dog.

这篇关于不使用select *的原因是什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！