mysql row constructor非等值的cbo无法改写为or，走不了索引-凯发app官方网站

在 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)

数据如下：

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



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




###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