SELECT * SQL查询VS选择特定的列SQL查询 [英] Select * sql query vs Select specific columns sql query

问题描述

可能重复：
  为什么SELECT *认为是有害的？

可能是一个数据库noob问题。

Probably a database nOOb question.

我们的应用程序有一个表像下面的

Our application has a table like the following

TABLE WF

Field              | Type        | Null | Key | Default | Extra          |
+--------------------+-------------+------+-----+---------+----------------+
| id                 | int(11)     | NO   | PRI | NULL    | auto_increment | 
| children           | text        | YES  |     | NULL    |                | 
| w_id               | int(11)     | YES  |     | NULL    |                | 
| f_id               | int(11)     | YES  |     | NULL    |                | 
| filterable         | tinyint(1)  | YES  |     | 1       |                | 
| created_at         | datetime    | YES  |     | NULL    |                | 
| updated_at         | datetime    | YES  |     | NULL    |                | 
| status             | smallint(6) | YES  |     | 1       |                | 
| visible            | tinyint(1)  | YES  |     | 1       |                | 
| weight             | int(11)     | YES  |     | NULL    |                | 
| root               | tinyint(1)  | YES  |     | 0       |                | 
| mfr                | tinyint(1)  | YES  |     | 0       |                | 
+--------------------+-------------+------+-----+---------+----------------+

此表预计是向上千万记录。该方案预计不会有太大变化。我需要的f_id检索的列，孩子们，状态，可见，重，根，MFR。

This table is expected to be upwards of ten million records. The schema is not expected to change much. I need to retrieve the columns f_id, children, status, visible, weight, root, mfr.

哪种方法更快速的数据检索？

Which approach is faster for data retrieval?

1） SELECT * FROM WF，其中w_id = 1和STATUS = 1;

我将剥离在应用层的不必要的列。

I will strip the unnecessary columns in the application layer.

2）选择孩子，F_ID，状态，可见，权重，根，WF MFR，其中w_id = 1和STATUS = 1;

没有必要剥去不必要的列作为其$ P $在查询对选定的

There is no need to strip the unnecessary columns as its pre-selected in the query.

没有任何一个有一个现实生活中的基准为这是更快。我知道有人说选择*是邪恶的，但MySQL的响应速度更快，同时试图让整个块，而不是选择性的检索列？

Does any one have a real life benchmark as to which is faster. I know some say Select * is evil, but will MySQL respond faster while trying to get the whole chunk as opposed to retrieving selective columns?

我使用的MySQL版本：5.1.37-1ubuntu5（Ubuntu的）和应用程序是Rails3应用程序

I am using MySQL version: 5.1.37-1ubuntu5 (Ubuntu) and the application is Rails3 app.

推荐答案

作为一个select语句，包括列的子集怎么能显著更快的一个例子，它可以使用覆盖索引只包括那些列的表，可能会导致更好的查询性能。

As an example of how a select statement that includes a subset of columns can be significantly faster, it can use a covering index on the table that includes just those columns, potentially resulting in much better query performance.

这篇关于SELECT * SQL查询VS选择特定的列SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！