TiDB优化器未对关联条件中“or”进行改写

人如其名 · 2023 年1 月 23 日 13:48

对于TPCH表有如下测试语句（该语句无任何意义，仅为考察or改写能力）：
select count(a.S_NAME),sum(b.s_acctbal) from supplier a,supplier b where a.S_ADDRESS=b.S_ADDRESS or a.S_PHONE=b.S_PHONE;

查看其执行计划：

mysql> explain select count(a.S_NAME),sum(b.s_acctbal) from supplier a,supplier b where a.S_ADDRESS=b.S_ADDRESS or a.S_PHONE=b.S_PHONE;
+-------------------------------+----------------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------+
| id                            | estRows        | task      | access object | operator info                                                                                                                                   |
+-------------------------------+----------------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------+
| StreamAgg_10                  | 1.00           | root      |               | funcs:count(tpch1.supplier.s_name)->Column#15, funcs:sum(tpch1.supplier.s_acctbal)->Column#16                                                   |
| └─HashJoin_17                 | 10000000000.00 | root      |               | CARTESIAN inner join, other cond:or(eq(tpch1.supplier.s_address, tpch1.supplier.s_address), eq(tpch1.supplier.s_phone, tpch1.supplier.s_phone)) |
|   ├─TableReader_16(Build)     | 100000.00      | root      |               | data:TableFullScan_15                                                                                                                           |
|   │ └─TableFullScan_15        | 100000.00      | cop[tikv] | table:b       | keep order:false                                                                                                                                |
|   └─TableReader_14(Probe)     | 100000.00      | root      |               | data:TableFullScan_13                                                                                                                           |
|     └─TableFullScan_13        | 100000.00      | cop[tikv] | table:a       | keep order:false                                                                                                                                |
+-------------------------------+----------------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------+
6 rows in set (0.01 sec)

mysql> explain analyze select a.l_partkey from lineitem a cross join lineitem b limit 10;
+-------------------------------+-------------+----------+-----------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------+-----------+---------+
| id                            | estRows     | actRows  | task      | access object | execution info                                                                                                                                                                                                                                                                                                                                                      | operator info         | memory    | disk    |
+-------------------------------+-------------+----------+-----------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------+-----------+---------+
| Limit_12                      | 10.00       | 10       | root      |               | time:2m46.8s, loops:2                                                                                                                                                                                                                                                                                                                                               | offset:0, count:10    | N/A       | N/A     |
| └─HashJoin_13                 | 10.00       | 1024     | root      |               | time:2m46.8s, loops:1, build_hash_table:{total:17.4s, fetch:10.5s, build:6.87s}, probe:{concurrency:3, total:8m23.6s, max:2m48.7s, probe:7m31.4s, fetch:52.1s}                                                                                                                                                                                                      | CARTESIAN inner join  | 1023.6 MB | 1.34 GB |
|   ├─TableReader_18(Build)     | 59986052.00 | 59986052 | root      |               | time:10.3s, loops:58813, cop_task: {num: 2197, max: 281.3ms, min: 1.1ms, avg: 31.4ms, p95: 68.4ms, max_proc_keys: 50144, p95_proc_keys: 50144, tot_proc: 56.1s, tot_wait: 9.09s, rpc_num: 2197, rpc_time: 1m9s, copr_cache_hit_ratio: 0.00, distsql_concurrency: 5}                                                                                                 | data:TableFullScan_17 | 1.86 MB   | N/A     |
|   │ └─TableFullScan_17        | 59986052.00 | 59986052 | cop[tikv] | table:b       | tikv_task:{proc max:271ms, min:0s, avg: 25.6ms, p80:42ms, p95:58ms, iters:67264, tasks:2197}, scan_detail: {total_process_keys: 59986052, total_process_keys_size: 2159497872, total_keys: 59988249, get_snapshot_time: 204.3ms, rocksdb: {key_skipped_count: 59986052, block: {cache_hit_count: 17464, read_count: 185633, read_byte: 3.77 GB, read_time: 15.7s}}} | keep order:false      | N/A       | N/A     |
|   └─TableReader_16(Probe)     | 0.00        | 3072     | root      |               | time:10.2ms, loops:3, cop_task: {num: 9, max: 11.9ms, min: 956.6µs, avg: 6.62ms, p95: 11.9ms, max_proc_keys: 992, p95_proc_keys: 992, tot_proc: 22ms, tot_wait: 26ms, rpc_num: 9, rpc_time: 59.4ms, copr_cache_hit_ratio: 0.00, distsql_concurrency: 5}                                                                                                             | data:TableFullScan_15 | 48.4 KB   | N/A     |
|     └─TableFullScan_15        | 0.00        | 3552     | cop[tikv] | table:a       | tikv_task:{proc max:9ms, min:0s, avg: 2.78ms, p80:9ms, p95:9ms, iters:32, tasks:9}, scan_detail: {total_process_keys: 3552, total_process_keys_size: 697625, total_keys: 3561, get_snapshot_time: 18.2ms, rocksdb: {key_skipped_count: 3552, block: {cache_hit_count: 71, read_count: 14, read_byte: 750.3 KB, read_time: 10.4ms}}}                                 | keep order:false      | N/A       | N/A     |
+-------------------------------+-------------+----------+-----------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------+-----------+---------+
6 rows in set (2 min 48.75 sec)

可以看到还是使用了hashjoin+内部cross join的方式导致hashjoin过程非常缓慢。
如果可以进行union改写则在此场景下效率会快很多。
因此是否应该增强基于代价的执行计划改写，来优化此类问题？

tidb菜鸟一只 · 2023 年1 月 28 日 01:29

只能说数据库的优化器真的很难，像oracle这种驰骋江湖几十年的老牌数据库也没做到尽善尽美。。。

h5n1 · 2023 年1 月 28 日 01:49

人无我有，人有我强，以此为目标

人如其名 · 2024 年6 月 22 日 15:17

还是应了那句话，只要想到的早晚会遇到，果然在目前生产上就遇到类似的问题了。在其它老牌数据库中迁移过来的存在较多这种关联字段中包含or条件的，在发帖子当时测试OB时候它的优化器就能做到改写。我们目前也只能通过人工改写的方式来进行优化，改写逻辑大体如下：
select a.c1,b.c2 from a,b where a.c1=b.c1 or a.c2=b.c2
改为方式为：

select a.pk,b.pk,a.c1,b.c2 from a,b where a.c1=b.c1
union
select a.pk,b.pk,a.c1,b.c2 from a,b where a.c2=b.c2

这里加上a.pk,b.pk主要用于避免union对重复数据去重，违反原来的语义。

如果表中没有主键就需要用隐式_tidb_rowid来代替（感谢@小龙虾爱上大龙虾大佬的协助）。

zhaokede · 2024 年6 月 23 日 01:13

or这个要自己优化，编译器不做这种优化，Oracle,mysql都是如此

我是吉米哥 · 2024 年6 月 23 日 01:54

把uion换成union all，可以不去重，这时是否可以不用增加pk列？

小龙虾爱大龙虾 · 2024 年6 月 23 日 12:56

Oracle 我记得这场景是可以的 Oracle 优化器还是:+1:

像风一样的男子 · 2024 年6 月 24 日 00:19

索引合并功能开启了吗？

zhaokede · 2024 年6 月 24 日 00:55

索引合并对这个有影响吗

zhaokede · 2024 年6 月 24 日 00:56

找了资料看到了。
Oracle数据库从版本8i开始就引入了更高级的查询优化技术，其中包括对Disjunctive Normal Form (DNF)的支持。
许多现代关系型数据库管理系统（RDBMS）都实现了Disjunctive Normal Form (DNF)优化，以提高查询性能。以下是一些实现了DNF优化的数据库系统：

PostgreSQL - PostgreSQL的查询优化器能够识别并优化包含OR条件的查询，将其转换为更有效的执行计划。
Oracle Database - Oracle的优化器也支持将复杂的OR条件转换为多个子查询，然后使用UNION ALL来合并结果。
Microsoft SQL Server - SQL Server的查询优化器同样能够处理OR条件，通过将其转换为多个子查询来优化执行路径。
MySQL - MySQL的InnoDB存储引擎在某些情况下也能优化OR条件，尤其是在有适用索引的情况下。
IBM DB2 - DB2的查询优化器也支持DNF优化，能够有效地处理包含OR逻辑的复杂查询。
Google BigQuery - BigQuery作为一款云原生的数据仓库，其优化器也能够处理和优化包含OR条件的查询。
Amazon Redshift - Amazon Redshift的查询优化器能够识别并优化包含OR条件的查询，提高查询效率。

这些数据库系统通过内部优化器的智能分析，能够自动将包含OR条件的查询转换为更高效的形式，比如使用UNION ALL来代替，从而避免了不必要的重复计算和数据扫描，提高了查询速度和资源利用率。不过，具体优化策略和效果可能因数据库版本和配置的不同而有所差异。

TiDBer_QKDdYGfz · 2024 年6 月 24 日 05:19

大佬nb，很有帮助，没有对比就没有伤害

Raymond · 2024 年6 月 29 日 14:45

不知道为什么 hash join 后要形成笛卡尔积，这种在Oracle 上好像不需要形成笛卡尔集合

时空旅行者 · 2024 年9 月 23 日 10:00

ORACLE 优化器也是一样包含OR语句也是很慢需要自己改写union方式

residentevil · 2024 年9 月 24 日 02:21

感同身受，哈哈