MySQL-5.7-8.2.1.16 DISTINCT Optimization

DISTINCT combined with ORDER BY needs a temporary table in many cases.

与ORDER BY组合在一起的DISTINCT在很多情况下需要一个临时表。

Because DISTINCT may use GROUP BY, learn how MySQL works with columns in ORDER BY or HAVING clauses that are not part of the selected columns.

因为DISTINCT可能使用GROUP BY，学习MySQL如何处理ORDER BY或HAVING子句中不属于所选列的列。

See Section 12.20.3, “MySQL Handling of GROUP BY”.

In most cases, a DISTINCT clause can be considered as a special case of GROUP BY. For example, the following two queries are equivalent:

在大多数情况下，DISTINCT子句可以被视为GROUP BY的特殊情况。例如，下面两个查询是等价的:

Due to this equivalence, the optimizations applicable to GROUP BY queries can be also applied to queries with a DISTINCT clause.

由于这种等效性，适用于GROUP BY查询的优化也可以应用于带有DISTINCT子句的查询。

Thus, for more details on the optimization possibilities for DISTINCT queries,

因此，有关DISTINCT查询的优化可能性的更多细节

see Section 8.2.1.15, “GROUP BY Optimization”.

When combining LIMIT row_count with DISTINCT, MySQL stops as soon as it finds row_count unique rows.

当结合LIMIT row_count和DISTINCT时，MySQL一旦发现row_count唯一的行就会停止。

If you do not use columns from all tables named in a query, MySQL stops scanning any unused tables as soon as it finds the first match. In the following case, assuming that t1 is used before t2 (which you can check with EXPLAIN), MySQL stops reading from t2 (for any particular row in t1) when it finds the first row in t2:

如果您不使用查询中命名的所有表中的列，MySQL一旦发现第一个匹配项，就会停止扫描任何未使用的表。在下面的例子中，假设t1在t2之前使用(你可以用EXPLAIN检查)，MySQL在发现t2中的第一行时停止从t2中读取(对于t1中的任何特定行):