选择所有列对性能不利吗? [英] Is it bad for performance to select all columns?

问题描述

一次 SELECT 所有列是不是很糟糕，即使您可能不需要所有列?但是，您可能在另一个任务中需要它们，但是您懒得为每个任务编写查询.

Is it bad to SELECT all columns at once even though you probably don't neeed all of them? However you might need them in another task but you are to lazy to write queries for every task.

您是否应该只在SELECT 只需要列的地方执行查询，如果需要另一列，则再次执行此查询?

Should you only do queries where you SELECT only columns you need and do this query again if you need another column?

所以基本上问题是:SELECT 一列与多列对性能有什么影响吗?

So basically the question is: Does it has any effect on performance to SELECT one column vs multiple columns?

查询非常简单(没有函数、连接等)例如:

The query is very simple (no functions, joins etc.) For example:

SELECT
id, name, status, date
FROM user_table
WHERE user_id = :user_id

推荐答案

这里的问题与其说是数据库服务器的问题，不如说是网络通信问题.通过一次选择所有列，您告诉服务器立即返回给您，所有列.至于对 IO 和所有这些的关注，这些问题在问题和答案@Karamba 中得到了很好的解决发表评论:select * vs select column.但是对于大多数实际应用程序(我在任何意义上都使用应用程序")，主要关注的只是网络流量以及序列化、传输和反序列化数据所需的时间.虽然实际上，答案是相同的.

The issue here isn't so much a matter of the database server, as just the network communication. By selecting all columns at once, you're telling the server to return to you, all columns at once. As for concerns over IO and all that, those are addressed nicely in the question and answer @Karamba gave in a comment: select * vs select column. But for most real-world applications (and I use "applications" in every sense), the main concern is just network traffic and how long it takes to serialize, transmit, then deserialize the data. Although really, the answer is the same either way.

因此，如果您打算全部使用它们，那么撤回所有列是很好的，但这可能需要大量额外的数据传输，特别是如果您在列中存储了很长的字符串.当然，在许多情况下，差异是无法察觉的，而且大多只是一个原则问题.不是全部，而是绝大多数.

So pulling back all the columns is great, if you intend to use them all, but that can be a lot of extra data transfer, particularly if you store, say, lengthy strings in your columns. In many cases, of course, the difference will be undetectable and is mostly just a matter of principle. Not all, but a significant majority.

这实际上只是您现在提到的懒惰(相信我，我们都有这种感觉)与性能真正的重要性之间的权衡.

It's really just a trade-off between your aforementioned laziness (and trust me, we all feel that way) now and how important performance really is.

总之，如果您确实打算使用所有列值，那么最好一次性将它们全部拉回来，然后提交一堆查询.

That all said, if you do intend to use all the column values, you're much better off pulling them all back at once then you are filing a bunch of queries.

把它想象成一个网络搜索:你搜索，你找到你的页面，你只需要一个细节.您可以阅读整个页面并了解有关该主题的所有信息，或者您可以直接跳到有关您正在寻找和完成的部分.如果这就是你想要的，后者要快得多，但是如果你那时必须了解其他方面，那么第一次阅读它们比不得不再次搜索并找到要好得多谈论它的网站.

Think of it like doing a web search: you do your search, you find your page, and you only need one detail. You could read the entire page and know everything about the subject, or you could just jump to the part about what you're looking for and be done. The latter is a lot faster if that's all you ever want, but if you're then going to have to learn about the other aspects, you'd be way better off reading them the first time than having to do your search again and find the site to talk about it.

如果您不确定将来是否需要其他列值，那么这是您作为开发人员的决定，哪种情况更有可能发生.

If you aren't sure whether you'll need the other column values in the future, then that's your call to make as the developer for which case is more likely.

这完全取决于您的应用程序是什么、您的数据是什么、您如何使用它以及性能对您真正有多重要.

It all depends on what your application is, what your data is, how you're using it, and how important performance really is to you.

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