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

【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 |
±--------±-----±------------------------------------------------------------------------------------------+
感觉上述现象跟不能动态分区裁剪类似。

1赞

大佬们帮看下,谢谢

2赞

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

1赞

单独增加什么意思

1赞

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

1赞


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

1赞

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

1赞

为什么不能走索引?实际的生产表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上是没问题的

1赞

不好意思回复晚了,这个加上 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

1赞

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

1赞

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