条件中同列使用函数后导致同列关联不能使用索引

[h5n1]

【TiDB 版本】 5.0.3
【问题】 SQL中 条件a.user_id=xxxx and b.user_id=a.user_id,同时包含条件b.partition_id=mod(p.user_id,4)时导致1. 2表不能使用indexjoin 2. b表不能使用索引或小范围table range scan
CREATE TABLE test1 (
TRADE_ID bigint(16) NOT NULL,
SUBSCRIBE_ID bigint(16) NOT NULL,
USER_ID bigint(16) DEFAULT NULL,
ACCEPT_MONTH int(2) NOT NULL,
CANCEL_TAG char(1) NOT NULL,
PRIMARY KEY (TRADE_ID,ACCEPT_MONTH,CANCEL_TAG) /*T![clustered_index] NONCLUSTERED */,
KEY idx1 (USER_ID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin

CREATE TABLE test2 (
USER_ID bigint(16) unsigned NOT NULL,
CUST_ID bigint(16) NOT NULL,
partition_id int(11) DEFAULT NULL,
PRIMARY KEY (USER_ID) /*T![clustered_index] CLUSTERED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin

SQL: select * from test1 a, test2 b where a.trade_id=3621052674110830 and b.user_id = a.user_id and b.partition_id=mod(b.user_id,4);

强制索引不起作用

mysql上b表走索引

show warnings;
±--------±-----±------------------------------------------------------------------------------------------+
| Level | Code | Message |
±--------±-----±------------------------------------------------------------------------------------------+
| Warning | 1105 | Scalar function ‘mod’(signature: ModIntUnsignedSigned) can not be pushed to storage layer |
±--------±-----±------------------------------------------------------------------------------------------+
感觉上述现象跟不能动态分区裁剪类似。

[h5n1]

大佬们帮看下，谢谢

[Lucien]

@h5n1

| Warning | 1105 | Scalar function ‘mod’(signature: ModIntUnsignedSigned) can not be pushed to storage layer |

如果单独增加 mod 函数可以走到索引么 ？

[h5n1]

单独增加什么意思

[Lucien]

@h5n1

SQL: select * from test1 a, test2 b where a.trade_id=3621052674110830 and b.user_id = a.user_id and b.partition_id=mod(b.user_id,4);

这样可以走到执行计划里面 ？
SQL: select * from test1 a, test2 b where a.trade_id=3621052674110830 and b.partition_id=mod(b.user_id,4);

[h5n1]

没有user_id关联条件，使用Mod函数更不能走索引了

[Lucien]

@h5n1

partition_id int(11) DEFAULT NULL,
PRIMARY KEY ( USER_ID ) /*T![clustered_index] CLUSTERED */

这个应该是走不上索引的，目前是这样的。现在这个需求预期的查询延迟和实际相差什么样 ？

[h5n1]

为什么不能走索引？实际的生产表b表数据量2亿左右，[0,+inf]的table range scan就是全表扫描了。 理论应该是这样的处理处理逻辑： 1. 根据a.trade_id=3621052674110830 获取记录，根据统计信息预估返回行数 只有几条。2. 根据a表或的数据获取user_id，根据user_id和统计信息评估b表的返回数量，评估选择访问方式 3. 因为b.user_id是int 主键可以使用点查，走少数据量的table range scan.
现在感觉是a.user_id=b.user_id关联是不能实现动态根据获取的值做关联表的评估，有点类似分区表关联动态裁剪后使用index join。而且这个SQL在Mysql上是没问题的

[zhangyuanjia]

不好意思回复晚了，这个加上 mod(…) 的条件后，就不能用 IndexJoin 的原因是目前 TiDB 的短板；
目前 TiDB 的 IndexJoin 的被驱动侧，只能是 TableReader/IndexReader/IndexLookUp 这一类数据访问算子；
如果是其他的算子，比如 Selection，HashAgg 之类的，就不能用 IndexJoin 了；

具体在这个 case 中，因为 mod 还不能支持下推到 TiKV，如果有 mod(…) 条件，那么在 IndexJoin 被驱动侧（也就是 b 表）下面会有个 Selection，用来处理 mod；
如果没有 mod 而是 b.partition_id=44，则能将这个条件下推到 TiKV，从而 IndexJoin 被驱动侧是一个 TableReader；

目前的解决办法只能改写 SQL 了；
可以把 a 和 b 的 join 弄成一个子查询，然后把 mod(…) 放到最外面来，大概是：
select * from (select * from a, b where …) tab where mod(…)
@h5n1

[h5n1]

感谢，改写后还是不能走index join，有没有什么hint 强制子查询先执行 生产一个临时表

[Lucien]

@h5n1

感谢，改写后还是不能走index join，有没有什么hint 强制子查询先执行 生产一个临时表

目前没有哈，目前可以考虑使用中间结果，拆分 SQL 。