优化MySQL查询:是否始终可以优化查询，以使其不使用"ALL"查询? [英] Optimizing MySQL Queries: Is it always possible to optimize a query so that it doesn't use "ALL"

问题描述

根据有关 Optimizing Queries With Explain :

* ALL:对先前表中的每个行组合进行全表扫描.如果该表是未标记为const的第一个表，则通常不好，并且在所有其他情况下通常非常糟糕. 通常，您可以通过添加允许基于早期表中的常量值或列值从表中检索行的索引来避免ALL.

* ALL: A full table scan is done for each combination of rows from the previous tables. This is normally not good if the table is the first table not marked const, and usually very bad in all other cases. Normally, you can avoid ALL by adding indexes that allow row retrieval from the table based on constant values or column values from earlier tables.

这是否意味着可以优化使用ALL的任何查询，以便不再进行全表扫描?

Does this mean that any query that uses ALL can be optimized so that it is no longer is doing a full table scan?

换句话说，通过向表中添加正确的索引，是否有可能始终避免使用ALL?还是在某些情况下，无论您添加什么索引，都不可避免地要使用ALL?

In other words, by adding the correct indexes to the table, is it possible to always avoid using ALL? Or are there some cases where ALL is unavoidable, no matter what indexes you add?

推荐答案

几乎(在某些情况下进行全扫描实际上更便宜)总是可以优化一个查询，以通过创建适当的索引来避免全扫描.但是，如果您对同一个表运行多个查询，则可能会出现其中某些查询最终会进行完全扫描或最终获得更多索引的情况，那么您的表中就会有列:-)

It's almost (there are cases where doing full scan is actually cheaper) always possible to optimize ONE query to avoid full scan by creating appropriate indexes. However, if you run multiple queries against the same table there are scenarios when either some of them will end up doing full scan or you'll end up with more indexes then you have columns in your table :-)

这篇关于优化MySQL查询:是否始终可以优化查询，以使其不使用"ALL"查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！