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

对于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改写则在此场景下效率会快很多。
因此是否应该增强基于代价的执行计划改写,来优化此类问题?

1 个赞

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

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

还是应了那句话,只要想到的早晚会遇到,果然在目前生产上就遇到类似的问题了。在其它老牌数据库中迁移过来的存在较多这种关联字段中包含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来代替(感谢@小龙虾爱上大龙虾 大佬的协助)。

1 个赞

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

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

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

索引合并功能开启了吗?

索引合并对这个有影响吗

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

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

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

1 个赞

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

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

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

感同身受,哈哈