如果一个表包含许多未使用/未选择的列，是否会影响性能？ [英] Does it affect the performance, if a table holds a lot of unused/unselected columns?

问题描述

背景：
我有一个叫做cars的桌子，里面有很多二手车，大约有100万行。该表有170多个列。该表仅在各个列上建立索引。大多数列是布尔值（例如has_automatic_gearbox等），其余列是字符串和数字（​​例如颜色和价格）。汽车显示在一个视图中，在总共170列中，我使用了80列。

Background: I have a table called cars that holds a lot of used cars, aprox 1 million rows. The table has a little more than 170 columns. The table is indexed on individual columns alone. Most of the columns are booleans (e.g. has_automatic_gearbox etc.) and the rest is strings and numbers (e.g. color and price). The cars are shown in a view, where i use around 80 columns out of the total 170.

我的问题：
我的问题是，是否在执行搜索时仅从表中选择80列，还是对性能产生了影响？另一方面，我仅由需要的80列组成了新表，而是总共170列中？因此，换句话说，表包含未选择的列会对性能产生影响吗？

My question: So my question is, does it make a difference to the performance, whether I select only the 80 columns out of the table when doing a search, or on the other hand I made a new table ONLY consisting of those 80 columns that I need, instead of the total 170 columns? So in other words, does it make a difference to performance, that a table holds columns, that is not selected?

推荐答案

Andomar在他的评论中，取决于是正确的。但是，如果您的问题是这样的：

Andomar is correct in his comment that "it depends". However, if you question is something like this:

表中的列数会影响选择查询吗？

Can the number of columns in a table have an effect upon select queries?

然后答案是是。它们是多余还是未使用是数据库设计的问题，与性能问题无关。

Then the answer is YES. Whether or not they're "extra" or "unused" is a question of database design, and doesn't have anything to do with the question of performance.

全部在其他条件相同的情况下，具有100列的表中的一行将比具有10列的表中的一行占用更多的空间。因为行将更大，所以服务器（相对）将不得不（相对）更努力地遍历较宽表中与较小表中相同数量的行。

All other things being equal, a row in a table with 100 columns is going to take up more space than a row in a table with 10 columns. Because the rows will be larger, your server will have to work (relatively) harder to go over an equal number of rows in the wider table than in the smaller table.

在行占据更多空间的表中，诸如页面拆分之类的事情也经常发生。

Things like page splitting will also occur more often in tables whose rows take up more space.

问题是（我认为这可能更像是您要问的问题）

If your question is (and I think this might be more along the lines of what you're asking)

选择从具有170列的表中拉出80列的查询要比从具有80列的表中拉出80列的查询慢吗？

Would, all things being equal, a select query pulling 80 columns from a table with 170 columns be slower than a query pulling 80 columns from a table with 80 columns?

然后答案应该是否。

这篇关于如果一个表包含许多未使用/未选择的列，是否会影响性能？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！