Select * sql 查询 vs Select 特定列 sql 查询
可能的重复：
为什么 SELECT * 被认为是有害的？
可能是一个数据库新手问题。
我们的应用程序有一个如下所示的表
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、children、status、visible、weight、root、mfr。
哪种方法的数据检索速度更快？
1) Select * from WF where w_id = 1 AND status = 1;
我将剥离应用层中不必要的列。
2) Select Children,f_id,status,visible,weight,root,mfr from WF where w_id = 1 AND status = 1;
不需要删除不必要的列，因为它在询问。
有没有人有一个现实生活中的基准来判断哪个更快。我知道有人说 Select * 是邪恶的，但是在尝试获取整个块而不是检索选择性列时，MySQL 的响应速度会更快吗？
我使用MySQL版本：5.1.37-1ubuntu5（Ubuntu），应用程序是Rails3应用程序。
Possible Duplicate:
Why is SELECT * considered harmful?
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 | |
+--------------------+-------------+------+-----+---------+----------------+
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 where w_id = 1 AND status = 1;
I will strip the unnecessary columns in the application layer.
2) Select children,f_id,status,visible,weight,root,mfr from WF where w_id = 1 AND status = 1;
There is no need to strip the unnecessary columns as its pre-selected in the query.
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?
I am using MySQL version: 5.1.37-1ubuntu5 (Ubuntu) and the application is Rails3 app.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
作为包含列子集的 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 * 时数据库往往会变慢，因为数据库必须弄清楚列是什么，从而比您指定时做更多的工作。如果结构发生显着变化，进一步的 select * 通常会返回不好的结果。它可能最终会显示您不希望他们看到的用户字段，或者如果有人愚蠢地重新排列列，那么应用程序实际上可能会以错误的顺序显示内容，或者如果从数据中进行插入，则将他们在错误的列中。在生产代码中使用 selct * 几乎是一种糟糕的做法。
If you return fewer columns there is less data to go across the network and less data for the database to process and it will almost always return faster. Databases also tend to be slower using select * because the database then has to go figure out what the columns are and thus do more work than when you specify. Further select * will often return bad results if the structure changes significantly. It may end up showing the user fields you don;t want them to see or if someone is silly enough to rearrange the columns, then the application may actually appear to show things in the wrong order or if doing an insert from the data, put them in the wrong column. It is almost alawys a poor practice to use selct * in production code.