MySQL SELECT 效率
我正在使用 PHP 与 MySQL 数据库交互，我想知道使用“SELECT * FROM...”查询 MySQL 是否比“SELECT id FROM...”效率更高或更低。
I am using PHP to interact with a MySQL database, and I was wondering if querying MySQL with a "SELECT * FROM..." is more or less efficient than a "SELECT id FROM...".
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
效率较低。
如果您考虑一下，SQL 将发送您选择的每一行的所有数据。
想象一下，您有一个名为
MassiveTextBlock的列 - 当您SELECT *时，该列将被包含在内，因此 SQL 必须在您实际不需要时发送所有这些数据它。相比之下，SELECT id只是获取数字的集合。Less efficient.
If you think about it, SQL is going to send all the data for each row you select.
Imagine that you have a column called
MassiveTextBlock- this column will be included when youSELECT *and so SQL will have to send all of that data over when you may not actually require it. In contrast,SELECT idis just grabbing a collection of numbers.它的效率较低，因为您获取的信息远多于 SELECT id。此外，第二个问题更有可能仅使用索引来解决。
It is less efficient because you are fetching a lot more information than just SELECT id. Additionally, the second question is much more likely to be served using just an index.
这取决于您选择数据的原因。如果您正在编写像 phpMySQL 这样的原始数据库接口，那么这可能是有意义的。
如果您使用相同的条件和串联操作对同一个表执行多个查询，则
SELECT col1, col2 FROM table可能比使用两个独立的SELECT col1 FROM table 更有意义和 SELECT col2 FROM table 来获取相同数据的不同部分，因为这在同一查询中执行这两个操作。但是，一般来说，您应该只从表中选择所需的列，以尽量减少 DBMS 挖掘出不必要的数据。如果您的数据库与客户端服务器位于不同的服务器上，或者您的数据库服务器很旧和/或速度很慢，那么这种方法的好处会大大增加。
没有任何条件
SELECT *是不可避免的，但如果有的话，那么您的数据模型可能存在一些严重的设计缺陷。It would depend on why you are selecting the data. If you are writing a raw database interface like phpMySQL, then it may make sense.
If you are doing multiple queries on the same table with the same conditions and concatenation operations, then a
SELECT col1, col2 FROM tablemay make more sense to do than using two independentSELECT col1 FROM tableand aSELECT col2 FROM tableto get different portions of the same data, as this performs both operations in the same query.But, in general, you should only select the columns you need from the table in order to minimize unnecessary data from being dredged up by the DBMS. The benefits of this increase greatly if your database is on a different server from the client server, or if your database server is old and/or slow.
There is NO CONDITION in which a
SELECT *is unavoidable, but if there is, then your data model probably has some serious design flaws.这取决于你的索引。
如果您仅选择 MySQL 用于获取结果的索引中存在的列，那么选择较少的列有时可以节省大量时间。
例如，如果您在
id列上有索引，并且执行以下查询：那么MySQL只需要读取索引，甚至不需要读取表行。另一方面，如果您选择其他列，如下所示：
那么 MySQL 将需要从索引中读取 id，然后转到表行读取名称列。
但是，如果您正在读取的列不在您选择的索引中，那么读取更多的列实际上不会产生那么大的差异，尽管它会产生很小的差异。如果某些列包含大量数据，例如大型 TEXT 或 BLOB 列，那么明智的做法是在不需要它们时排除它们。
It depends on your indexes.
Selecting fewer columns can sometimes save a lot of time, if you select only columns that exist in the index that MySQL has used to fetch the results.
For example, if you have an index on the column
id, and you perform this query:Then MySQL only needs to read the index, and does not need to even read the table row. If on the other hand, you select additional columns, as on:
Then MySQL will need to read the id from the index, then go to the table row to read the name column.
If, however, you are reading columns that aren't in the index you're selecting on anyway, then reading more columns really won't make nearly as much difference, though it will make a small difference. If some columns contain a large amount of data, such as large TEXT or BLOB columns, then it'd be wise to exclude them if you don't need them.