在 MySQL 中，表达式 (id, ext) > (1, 2) 是基于字典序的比较。这种比较方式类似于对两个元组进行逐元素比较：

在ANSI里俗称row constructor,一般都是用于子查询多列比较，像ORACLE只支持子查询等值多列比较（非等值不支持），非子查询的不支持row constructor。

注意这种非等值的，MySQL CBO无法改写为OR，导致走不了range scan,当然覆盖索引是可能走的，所以非等值不要用这个语法，可以改为or。

首先比较第一个元素 id 和 1。

1）前提是id不能小于1，可以大于等于1，小于1肯定为FALSE

如果 id > 1，则整个表达式为 TRUE。

如果 id < 1，则整个表达式为 FALSE。

2）如果 id == 1，则比较第二个元素 ext 和 2。

如果 ext > 2，则整个表达式为 TRUE。

如果 ext <= 2，则整个表达式为 FALSE。

这种比较方式类似于字典中查找单词的顺序。

也即(id, ext) > (1, 2)等价于id>1 or (id =1 and ext > 2), 这里id不会选择小于1的，也等价于id>=第一个值1 and (id, ext) > (1, 2),这样转为and可能走索引

如果是(id, ext) >= (1, 2)，则等价于id>1 or (id=1 and ext >= 2)即可，包括(1,2)

###MySQL除了等值row constructor可以改写为and,这种不会改写成or,所以一般走不了range scan，需要手动改写为or,才可以走range scan

对应地，如果是(id,ext) < (1,2)则是等价于 id <1 or (id=1 and ext <2); id不能大于1，也可以转为id<=1 and (id,ext) < (1,2)

如果是(id,ext) <= (1,2)，等价于id<1 or (id=1 and ext <=2)即可，也就是包括(id,ext) < (1,2)+(id,ext)=(1,2)

*************************** 1. row ***************************

Table: t0816

Create Table: CREATE TABLE `t0816` (

`id` int DEFAULT NULL,

`ext` int DEFAULT NULL,

`ext1` int DEFAULT NULL,

KEY `idx_t0816` (`id`,`ext`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

1 row in set (0.00 sec)

数据如下：

select * from t0816 t;

+------+------+------+

| id | ext | ext1 |

+------+------+------+

| 1 | 1 | 1 |

| 2 | 1 | 1 |

| 1 | 1 | 1 |

| 2 | 2 | 1 |

| 2 | 3 | 1 |

| 1 | 3 | 1 |

| 2 | 1 | 1 |

| 0 | 2 | 1 |

| 0 | 3 | 1 |

+------+------+------+

###1.下面看看这个SQL的查询结果，是符合只查询id>1 or (id=1 and ext >2)的结果，但是执行计划是全表扫描。

select * from t0816 t where (id,ext)>(1,2);

+------+------+------+

| id | ext | ext1 |

+------+------+------+

| 2 | 1 | 1 |

| 2 | 2 | 1 |

| 2 | 3 | 1 |

| 1 | 3 | 1 |

| 2 | 1 | 1 |

+------+------+------+

###执行计划是全表扫描，可以通过show warnings查看，没有将where条件进行改写为OR，所以走不了range optimization。

explain select * from t0816 t where (id,ext)>(1,2);

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

| 1 | SIMPLE | t | NULL | ALL | NULL | NULL | NULL | NULL | 11 | 100.00 | Using where |

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

1 row in set, 1 warning (0.00 sec)

show warnings\G

*************************** 1. row ***************************

Level: Note

Code: 1003

Message: /* select#1 */ select `employees`.`t`.`id` AS `id`,`employees`.`t`.`ext` AS `ext`,`employees`.`t`.`ext1` AS `ext1`

from `employees`.`t0816` `t` where ((`employees`.`t`.`id`,`employees`.`t`.`ext`) > (1,2))

1 row in set (0.00 sec)

###2.对于row constructor,非等值的想走索引，得手动改写为or,所以非等值的，不要用row contructor

MySQL CBO没有将非等值row contructor改写为OR，所以，走不了索引，可以手动改写,则会采用range优化。

explain analyze select * from t0816 t where id >1 or (id=1 and ext >2)\G

*************************** 1. row ***************************

EXPLAIN: -> Index range scan on t using idx_t0816 over (id = 1 AND 2 < ext) OR (1 < id),

with index condition: ((t.id > 1) or ((t.id = 1) and (t.ext > 2))) (cost=2.76 rows=5) (actual time=0.031..0.048 rows=5 loops=1)

###3.等值的没有啥问题，会自动改写为and,可以走range优化

explain analyze select * from t0816 t where (id,ext)=(1,2)\G

*************************** 1. row ***************************

EXPLAIN: -> Index lookup on t using idx_t0816 (id=1, ext=2) (cost=0.35 rows=1) (actual time=0.094..0.100 rows=1 loops=1)

###4.ORACLE只是子查询等值或in才支持row construtor，其他不支持

select * from t where (object_id,data_object_id) > (select 1,2 from dual)

*

ERROR at line 1:

ORA-01796: this operator cannot be used with lists

子查询等值或in支持：

select * from t where (object_id,data_object_id) = (select 1,2 from dual);

no rows selected

select * from t where (object_id,data_object_id) in (select 1,2 from dual);

no rows selected

非子查询等值都不支持

select * from t(object_id,data_object_id)=(1,2)

*

ERROR at line 1:

ORA-00933: SQL command not properly ended

附录：

===================row constructor比较=======================

ORACLE只支持子查询的row constructor,mysql和pg支持，

mysql不能直接转为or,所以非等值的走不了索引，等值的可以转为and,可以走索引，

MySQL要走索引得改成or,然后走range优化

select * from t0816 t where (id,ext)>(1,2) 这where条件写法在ANSI里叫什么

在ANSI SQL标准中，WHERE (id, ext) > (1, 2) 这种写法被称为“元组比较”或“行值构造器比较”。这种语法允许同时比较多个列（或值），将它们视为一个单元。元组比较在处理涉及多个条件的复杂查询时特别有用。

关键点

字典序比较：

元组按字典序进行比较，即从左到右逐个元素进行比较。如果第一个元素相等，则比较第二个元素，依此类推。例如，(1, 2, 'a') < (1, 2, 'b')，因为 'a' < 'b'。

行值构造器：

SQL标准中使用“行值构造器”这个术语来表示通常所说的元组。这允许在SQL查询中直接构造元组。

比较运算符：

标准比较运算符（=, !=, <, <=, >, >=）可以用于元组。例如：(a, b) <= (x, y) 等价于 (a < x) OR ((a = x) AND (b <= y))。

查询中的使用：

元组可以在WHERE子句中使用，以基于多个列过滤结果。例如：

SELECT * FROM mytable

WHERE (First, Last, Score) >= ('John', 'Jordan', 5)

AND (First, Last, Score) <= ('Mike', 'Taylor', 50)

ORDER BY First, Last, Score

LIMIT 1;

这个查询过滤了First、Last和Score的组合在指定范围内的行。

跨数据库支持：

虽然MySQL和PostgreSQL直接支持使用(col1, col2) < (val1, val2)的元组比较，但像Oracle和SQL Server这样的数据库可能需要使用逻辑运算符重写查询。例如：

WHERE date < :date

OR ((date = :date) AND (id < :id))

ORDER BY date DESC, id DESC;

这实现了与直接元组比较相同的结果。

示例

创建和使用元组

创建元组：

SELECT (1, 'a') AS x;

在比较中使用元组：

SELECT * FROM person

WHERE (firstname, lastname) = ('Arthur', 'Dent');

SELECT * FROM person

WHERE (firstname, lastname) > ('Arthur', 'Dent');

SELECT * FROM some_table

WHERE (col_one, col_two) IN (SELECT x,y FROM other_table);

实际用例

给定一个包含First、Last和Score列的表mytable，可以进行如下元组比较：

SELECT First, Last, Score

FROM mytable

WHERE ('John', 'Jordan', 5) <= (First, Last, Score)

AND (First, Last, Score) <= ('Mike', 'Taylor', 50)

ORDER BY First, Last, Score

LIMIT 1;

这个查询将返回First、Last和Score的组合在指定元组之间的行。

结论

在ANSI SQL中，元组比较提供了一种有效处理多列条件的方法。它们在各种数据库中得到支持，尽管语法略有不同。理解如何使用这些比较可以显著增强编写复杂查询的能力。

PG转为：实际上可以改写成id<=第一个值 and (id,id1)<(1,2),如果是大于则改为id>=第一个值 and

explain select * from s1 where (id,id1)<(1,2);

QUERY PLAN

------------------------------------------------------------------

Index Scan using idx_s1 on s1 (cost=0.29..8.31 rows=1 width=46)

Index Cond: (id <= 1)

Filter: (ROW(id, id1) < ROW(1, 2))

(3 rows)

ITPub博客

MySQL row constructor非等值条件不走索引的优化

最新文章