MySQL row constructor非等值的CBO无法改写为OR，走不了索引

在 MySQL 中，表达式 (id, ext) > (1, 2) 是基于字典序的比较。这种比较方式类似于对两个元组进行逐元素比较：
在ANSI里俗称row constructor,一般都是用于子查询多列比较，像ORACLE只支持子查询等值多列比较（非等值不支持），非子查询的不支持row constructor。


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


首先比较{BANNED}中国第一个元素 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, 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,ext) <= (1,2)，等价于id<1 or (id=1 and ext <=2)即可，也就是包括(id,ext) < (1,2)+(id,ext)=(1,2)

samples:
*************************** 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);
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 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