问题描述
可能重复：
为什么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查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！