MySQL-5.7-8.2.1.19 Row Constructor Expression Optimization(行构造函数表达式优化)

Row constructors permit simultaneous comparisons of multiple values. For example, these two statements are semantically equivalent:

行构造函数允许同时比较多个值。例如，这两个语句在语义上是等价的:

In addition, the optimizer handles both expressions the same way.

此外，优化器以相同的方式处理这两个表达式。

The optimizer is less likely to use available indexes if the row constructor columns do not cover the prefix of an index.

如果行构造函数列不包含索引的前缀，优化器就不太可能使用可用索引。

Consider the following table, which has a primary key on (c1, c2, c3):

虑下表，它有一个主键在(c1, c2, c3)上:

In this query, the WHERE clause uses all columns in the index. However, the row constructor itself does not cover an index prefix, with the result that the optimizer uses only c1 (key_len=4, the size of c1):

在这个查询中，WHERE子句使用索引中的所有列。然而，行构造函数本身不包含索引前缀，结果优化器只使用c1 (key_len=4, c1的大小):

In such cases, rewriting the row constructor expression using an equivalent nonconstructor expression may result in more complete index use. For the given query, the row constructor and equivalent nonconstructor expressions are:

在这种情况下，使用等效的非构造函数表达式重写行构造函数表达式可能会导致更完整的索引使用。对于给定的查询，行构造函数和等效的非构造函数表达式是:

Rewriting the query to use the nonconstructor expression results in the optimizer using all three columns in the index (key_len=12):

重写查询以使用非构造函数表达式会导致优化器使用索引中的所有三列(key_len=12):

Thus, for better results, avoid mixing row constructors with AND/OR expressions. Use one or the other. 

因此，为了获得更好的结果，请避免将行构造函数与AND/OR表达式混合使用。用一个或另一个。

Under certain conditions, the optimizer can apply the range access method to IN() expressions that have row constructor arguments.

在某些条件下，优化器可以将范围访问方法应用于具有行构造函数参数的IN()表达式。

See Range Optimization of Row Constructor Expressions.