这条sql为什么就跨表查询了呢

Apache ShardingSphere
#1 
select * from `AAAA` where aiui_id in (select caseId from task_case_202203 where taskId = ? and caseTable = ? ) and aiui_id NOT IN (select caseId FROM **case_result_202203** WHERE taskId = ? and tableName = ? ) ORDER BY aiui_id ASC

case_result_202203表的分表规则为根据taskId进行hash_mod

#2

没太明白你想探讨的问题？可以说的详细点吗，具体什么问题？

#3

您好，表名“case_result_202203”根据字段taskId进行分表。

  • 现在有一条sql：select * from AAAA where aiui_id in (select caseId from task_case_202203 where taskId = ? and caseTable = ? ) and aiui_id NOT IN (select caseId FROM case_result_202203 WHERE taskId = ? and tableName = ? ) ORDER BY aiui_id ASC
    查询日志发现case_result_202203进行了全表查询。

  • 将这调sql中的子sql“select caseId FROM case_result_202203 WHERE taskId = ? and tableName = ? ”单独执行查询具体的子表case_result_202203_7。

我很疑惑，为什么那条长sql会出现跨表查询的情况。

#4

麻烦开启下 sql-show，然后提供下逻辑 sql 和真实 sql。

#5

  • 逻辑sql
    select * from AAA where aiui_id in (select caseId from task_case_202203 where taskId = ? and caseTable = ? ) and aiui_id NOT IN (select caseId FROM case_result_202203 WHERE taskId = ? and tableName = ? ) ORDER BY aiui_id ASC

  • 物理sql
    select * from AAA where aiui_id in (select caseId from task_case_202203 where taskId = ? and caseTable = ? ) and aiui_id NOT IN (select caseId FROM case_result_202203_0 WHERE taskId = ? and tableName = ? ) ORDER BY aiui_id ASC;

    select * from AAA where aiui_id in (select caseId from task_case_202203 where taskId = ? and caseTable = ? ) and aiui_id NOT IN (select caseId FROM case_result_202203_1 WHERE taskId = ? and tableName = ? ) ORDER BY aiui_id ASC;

    select * from AAA where aiui_id in (select caseId from task_case_202203 where taskId = ? and caseTable = ? ) and aiui_id NOT IN (select caseId FROM case_result_202203_2 WHERE taskId = ? and tableName = ? ) ORDER BY aiui_id ASC
    。。。。。
    select * from AAA where aiui_id in (select caseId from task_case_202203 where taskId = ? and caseTable = ? ) and aiui_id NOT IN (select caseId FROM case_result_202203_31 WHERE taskId = ? and tableName = ? ) ORDER BY aiui_id ASC

#6

好的，感谢反馈，我看下这个问题

#7

@施华洛 我使用 5.1.1 测试了下这个问题，未复现你的这个现象。

[INFO ] 2022-04-02 17:21:13.968 [ShardingSphere-Command-0] ShardingSphere-SQL - Logic SQL: select * from t_user where user_id in (select user_id from t_order where order_id = 1 and user_id = 1)
[INFO ] 2022-04-02 17:21:13.968 [ShardingSphere-Command-0] ShardingSphere-SQL - SQLStatement: MySQLSelectStatement(table=Optional.empty, limit=Optional.empty, lock=Optional.empty, window=Optional.empty)
[INFO ] 2022-04-02 17:21:13.968 [ShardingSphere-Command-0] ShardingSphere-SQL - Actual SQL: ds_1 ::: select * from t_user where user_id in (select user_id from t_order_1 where order_id = 1 and user_id = 1)

t_single 是单表，子查询里面是 t_order 表并且带了分片条件，结果显示路由到了正确的节点上。

京ICP备2021015875号