一个 tidb 无索引查询性能问题请教

Qiuchi · 2023 年3 月 8 日 05:55

【 TiDB 使用环境】测试
【 TiDB 版本】6.5

同表结构、数据量，oracle 在该查询时速度要显著快于 tidb，前者大概两分左右，后者查询始终无法返回，不知可以如何优化？

sql

cr_transaction 约 7 千万
cr_cust_calc_load_balance 约 6 百万
cr_customer_account 约 6 百万

select t.ta_system_code,
       t.ta_account_no,
       t.cust_name,
       t.ta_cfm_serial_no,
       t.transaction_cfm_date,
       t.product_code,
       t.transaction_type
from cr_transaction t
where t.ta_return_code = '0000'
  and t.ta_account_no
    not in (select ta_account_no from cr_cust_calc_load_balance)
  and t.ta_cfm_serial_no not in (
    select t.ta_cfm_serial_no
    from cr_transaction t
             left join cr_customer_account m on t.ta_account_no = m.ta_account_no
    where t.ta_system_code = 'ETFTA'
      and m.ta_account_no is null
);

oracle 执行计划

tidb 执行计划

id	estRows	task	access object	operator info
Projection_14	47682489.35	root		dcdb.cr_transaction.ta_system_code, dcdb.cr_transaction.ta_account_no, dcdb.cr_transaction.cust_name, dcdb.cr_transaction.ta_cfm_serial_no, dcdb.cr_transaction.transaction_cfm_date, dcdb.cr_transaction.product_code, dcdb.cr_transaction.transaction_type
└─HashJoin_16	47682489.35	root		anti semi join, equal:[eq(dcdb.cr_transaction.ta_cfm_serial_no, dcdb.cr_transaction.ta_cfm_serial_no)]
├─Selection_25(Build)	421507.82	root		isnull(dcdb.cr_customer_account.ta_account_no)
│ └─IndexHashJoin_31	526884.78	root		left outer join, inner:IndexReader_28, outer key:dcdb.cr_transaction.ta_account_no, inner key:dcdb.cr_customer_account.ta_account_no, equal cond:eq(dcdb.cr_transaction.ta_account_no, dcdb.cr_customer_account.ta_account_no)
│ ├─IndexLookUp_49(Build)	509898.74	root
│ │ ├─IndexRangeScan_47(Build)	509898.74	cop[tikv]	table:t, index:IDX_CR_TRANS_CODE_DATE_NO(ta_system_code, transaction_cfm_date, ta_cfm_serial_no)	range:[“ETFTA”,“ETFTA”], keep order:false
│ │ └─TableRowIDScan_48(Probe)	509898.74	cop[tikv]	table:t	keep order:false
│ └─IndexReader_28(Probe)	526884.78	root		index:IndexRangeScan_27
│ └─IndexRangeScan_27	526884.78	cop[tikv]	table:m, index:PRIMARY(ta_account_no, ta_system_code)	range: decided by [eq(dcdb.cr_customer_account.ta_account_no, dcdb.cr_transaction.ta_account_no)], keep order:false
└─HashJoin_17(Probe)	59603111.69	root		CARTESIAN anti semi join, other cond:eq(dcdb.cr_transaction.ta_account_no, dcdb.cr_cust_calc_load_balance.ta_account_no)
├─IndexReader_24(Build)	6078924.00	root		index:IndexFullScan_23
│ └─IndexFullScan_23	6078924.00	cop[tikv]	table:cr_cust_calc_load_balance, index:IDX_LOAD_BALANCE_KEY(calc_unit_id, ta_system_code, ta_account_no)	keep order:false
└─TableReader_20(Probe)	74503889.61	root		data:Selection_19
└─Selection_19	74503889.61	cop[tikv]		eq(dcdb.cr_transaction.ta_return_code, “0000”)
└─TableFullScan_18	74622837.00	cop[tikv]	table:t	keep order:false

tidb狂热爱好者 · 2023 年3 月 8 日 06:03

select t.ta_system_code,
t.ta_account_no,
t.cust_name,
t.ta_cfm_serial_no,
t.transaction_cfm_date,
t.product_code,
t.transaction_type
from cr_transaction t
where t.ta_return_code = ‘0000’
and t.ta_account_no
not in (select ta_account_no from cr_cust_calc_load_balance)
and t.ta_cfm_serial_no not in (
select t.ta_cfm_serial_no
from cr_transaction t
inner join cr_customer_account m on t.ta_account_no = m.ta_account_no
where t.ta_system_code = ‘ETFTA’
and m.ta_account_no is null
);

你这个性能差是带了太多的not in 和笛卡尔积笛卡尔积我改了

tidb狂热爱好者 · 2023 年3 月 8 日 06:04

你试试 select distinct t.ta_system_code,
t.ta_account_no,
t.cust_name,
t.ta_cfm_serial_no,
t.transaction_cfm_date,
t.product_code,
t.transaction_type
from cr_transaction t
left join cr_cust_calc_load_balance c on t.ta_account_no = c.ta_account_no
left join cr_transaction t2 on t.ta_cfm_serial_no = t2.ta_cfm_serial_no
left join cr_customer_account m on t.ta_account_no = m.ta_account_no
where t.ta_return_code = ‘0000’
and c.ta_account_no is null
and t2.ta_cfm_serial_no is null
and t.ta_system_code = ‘ETFTA’
and m.ta_account_no is not null;

tidb狂热爱好者 · 2023 年3 月 8 日 06:05

SELECT t.ta_system_code,
t.ta_account_no,
t.cust_name,
t.ta_cfm_serial_no,
t.transaction_cfm_date,
t.product_code,
t.transaction_type
FROM cr_transaction t
WHERE t.ta_return_code = ‘0000’
AND NOT EXISTS (
SELECT 1
FROM cr_cust_calc_load_balance l
WHERE l.ta_account_no = t.ta_account_no
)
AND NOT EXISTS (
SELECT 1
FROM cr_transaction t2
INNER JOIN cr_customer_account m ON t2.ta_account_no = m.ta_account_no
WHERE t2.ta_system_code = ‘ETFTA’
AND m.ta_account_no IS NULL
AND t2.ta_cfm_serial_no = t.ta_cfm_serial_no
)

Kongdom · 2023 年3 月 8 日 06:10

改成not exists应该会好一点。

第二个not exists里的inner join是不是逻辑不对？原文应该是想找在t2不在m的记录，inner join应该是找在t2也在m的记录吧

liuis · 2023 年3 月 8 日 06:16

不知道怎么优化，就优化查询语句，哈哈

Qiuchi · 2023 年3 月 8 日 06:17

是的确实不能inner join，不过之前exists我试了，他执行计划和不改之前是一样的。。我其实比较奇怪的是，tidb和oracle对于这个查询的计划看起来似乎是一样的？但是为什么速度会差这么多

Kongdom · 2023 年3 月 8 日 07:01

我理解的是tidb至少多一步聚合，每个节点查询结果返回到owner节点，单体数据库不需要这一步。每个节点的计算能力越强，分布式数据库越强。

tidb菜鸟一只 · 2023 年3 月 8 日 07:14

我看两个执行计划选择的索引并不一样啊，这个影响也很大

人如其名 · 2023 年3 月 8 日 07:22

想不通这里为何是CARTESIAN anti semi join，一般来说没有关联条件或者关联条件非等值的情况下才有可能笛卡尔积吧，源码中也有相应的例子：

但是我自己模拟是没出来：

是6.5版本的。

Qiuchi · 2023 年3 月 8 日 10:24

我这个倒是确实是没有关联条件，跟文档是一样的，倒是奇怪你这个例子为啥不是这样，这个里面的 “other cond:” 和 “cond:” 不知道是什么区别

人如其名 · 2023 年3 月 8 日 13:15

我测试了下，好像和子查询中的关联条件是否可以为null有关。

为何会变成CARTESIAN应该是和这个PR有关：https://github.com/pingcap/tidb/pull/9051

这里operation info里面有CARTESIAN笛卡尔积的标记应该是和cr_cust_calc_load_balance.ta_account_no 定义允许null有关。但是是不是这里影响的性能还不清楚呢。你可以下次执行时候抓下连接的执行信息：explain for connection conn_id。

人如其名 · 2023 年3 月 8 日 13:48

经过测试，表象应该和这里的笛卡尔积有关，根本原因应该是和反半连接的连接字段可以为空有关：

mysql> show create table customer_bak;
+--------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table        | Create Table                                                                                                                                                                                                                                                                                                                                                                            |
+--------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| customer_bak | CREATE TABLE `customer_bak` (
  `C_CUSTKEY` bigint(20) DEFAULT NULL,
  `C_NAME` varchar(25) NOT NULL,
  `C_ADDRESS` varchar(40) NOT NULL,
  `C_NATIONKEY` bigint(20) NOT NULL,
  `C_PHONE` char(15) NOT NULL,
  `C_ACCTBAL` decimal(15,2) NOT NULL,
  `C_MKTSEGMENT` char(10) NOT NULL,
  `C_COMMENT` varchar(117) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+--------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.02 sec)

mysql> show create table customer_bak1;
+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table         | Create Table                                                                                                                                                                                                                                                                                                                                                                         |
+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| customer_bak1 | CREATE TABLE `customer_bak1` (
  `C_CUSTKEY` bigint(20) NOT NULL,
  `C_NAME` varchar(25) NOT NULL,
  `C_ADDRESS` varchar(40) NOT NULL,
  `C_NATIONKEY` bigint(20) NOT NULL,
  `C_PHONE` char(15) NOT NULL,
  `C_ACCTBAL` decimal(15,2) NOT NULL,
  `C_MKTSEGMENT` char(10) NOT NULL,
  `C_COMMENT` varchar(117) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.02 sec)

mysql> 

mysql> explain analyze select * from orders where o_custkey not in (select c_custkey from customer_bak1);
+-----------------------------+-------------+----------+-----------+---------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------+----------+---------+
| id                          | estRows     | actRows  | task      | access object       | execution info                                                                                                                                                                                                                                                                                                                                                        | operator info                                                                   | memory   | disk    |
+-----------------------------+-------------+----------+-----------+---------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------+----------+---------+
| HashJoin_8                  | 29862297.60 | 36607771 | root      |                     | time:15.5s, loops:35753, build_hash_table:{total:124.3ms, fetch:110.8ms, build:13.5ms}, probe:{concurrency:5, total:1m17.8s, max:15.6s, probe:13.5s, fetch:1m4.3s}                                                                                                                                                                                                    | anti semi join, equal:[eq(tpch.orders.o_custkey, tpch.customer_bak1.c_custkey)] | 6.80 MB  | 0 Bytes |
| ├─TableReader_12(Build)     | 100000.00   | 100000   | root      |                     | time:111.3ms, loops:100, cop_task: {num: 10, max: 17.2ms, min: 9.71ms, avg: 12.1ms, p95: 17.2ms, max_proc_keys: 33760, p95_proc_keys: 33760, tot_proc: 109ms, rpc_num: 10, rpc_time: 120.4ms, copr_cache: disabled, build_task_duration: 4.63µs, max_distsql_concurrency: 1}                                                                                          | data:TableFullScan_11                                                           | 482.5 KB | N/A     |
| │ └─TableFullScan_11        | 100000.00   | 100000   | cop[tikv] | table:customer_bak1 | tikv_task:{proc max:17ms, min:9ms, avg: 11.3ms, p80:15ms, p95:17ms, iters:137, tasks:10}, scan_detail: {total_process_keys: 100000, total_process_keys_size: 21056033, total_keys: 100010, get_snapshot_time: 93.1µs, rocksdb: {delete_skipped_count: 843488, key_skipped_count: 943488, block: {cache_hit_count: 6704}}}                                             | keep order:false, stats:pseudo                                                  | N/A      | N/A     |
| └─TableReader_10(Probe)     | 37327872.00 | 37438464 | root      |                     | time:12s, loops:36662, cop_task: {num: 1259, max: 539.4ms, min: 825.2µs, avg: 170.5ms, p95: 398.9ms, max_proc_keys: 50144, p95_proc_keys: 50144, tot_proc: 2m41.2s, tot_wait: 36.3s, rpc_num: 1259, rpc_time: 3m34.6s, copr_cache: disabled, build_task_duration: 57µs, max_distsql_concurrency: 15}                                                                  | data:TableFullScan_9                                                            | 25.4 MB  | N/A     |
|   └─TableFullScan_9         | 37327872.00 | 37438464 | cop[tikv] | table:orders        | tikv_task:{proc max:490ms, min:0s, avg: 118.3ms, p80:211ms, p95:300ms, iters:41559, tasks:1259}, scan_detail: {total_process_keys: 37438464, total_process_keys_size: 5686354813, total_keys: 37439723, get_snapshot_time: 28.3ms, rocksdb: {key_skipped_count: 37438464, block: {cache_hit_count: 64349, read_count: 136302, read_byte: 1.79 GB, read_time: 6.48s}}} | keep order:false                                                                | N/A      | N/A     |
+-----------------------------+-------------+----------+-----------+---------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------+----------+---------+
5 rows in set (15.56 sec)


mysql> explain for connection 6313024131759604271;
+-----------------------------+-------------+---------+-----------+--------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------+--------+------+
| id                          | estRows     | actRows | task      | access object      | execution info                                                                                                                                                                                                                                                                                                                                   | operator info                                                                               | memory | disk |
+-----------------------------+-------------+---------+-----------+--------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------+--------+------+
| HashJoin_8                  | 29862297.60 | 229376  | root      |                    | time:11m53.2s, loops:224                                                                                                                                                                                                                                                                                                                         | CARTESIAN anti semi join, other cond:eq(tpch.orders.o_custkey, tpch.customer_bak.c_custkey) | N/A    | N/A  |
| ├─TableReader_12(Build)     | 100000.00   | 100000  | root      |                    | time:60.9ms, loops:100                                                                                                                                                                                                                                                                                                                           | data:TableFullScan_11                                                                       | N/A    | N/A  |
| │ └─TableFullScan_11        | 100000.00   | 100000  | cop[tikv] | table:customer_bak | tikv_task:{proc max:14ms, min:0s, avg: 5.1ms, p80:12ms, p95:14ms, iters:137, tasks:10}, scan_detail: {total_process_keys: 100000, total_process_keys_size: 21053008, total_keys: 100010, get_snapshot_time: 1ms, rocksdb: {key_skipped_count: 100000, block: {cache_hit_count: 161}}}                                                            | keep order:false                                                                            | N/A    | N/A  |
| └─TableReader_10(Probe)     | 37327872.00 | 239840  | root      |                    | time:19.1ms, loops:237                                                                                                                                                                                                                                                                                                                           | data:TableFullScan_9                                                                        | N/A    | N/A  |
|   └─TableFullScan_9         | 37327872.00 | 241888  | cop[tikv] | table:orders       | tikv_task:{proc max:14ms, min:0s, avg: 2.56ms, p80:4ms, p95:8ms, iters:601, tasks:97}, scan_detail: {total_process_keys: 241888, total_process_keys_size: 36738824, total_keys: 241985, get_snapshot_time: 34.6ms, rocksdb: {key_skipped_count: 241888, block: {cache_hit_count: 1779, read_count: 313, read_byte: 2.85 MB, read_time: 2.05ms}}} | keep order:false                                                                            | N/A    | N/A  |
+-----------------------------+-------------+---------+-----------+--------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------+--------+------+
5 rows in set (0.02 sec)

从上面可以看出对于customer_bak1表（关联条件非空）可以15秒就出结果。但是customer_bak表（关联条件可以为空），一直执行了11分钟还未执行完毕，估计还遥遥无期。
因此应该是tidb对于反半连接情况下的非空字段性能上存在问题。具体为何性能有问题，如果要继续分析就得通过pprof里面找关键路径扣代码去了。。。

Qiuchi · 2023 年3 月 8 日 13:52

有点奇怪，这个pr我看说是in操作的操作数区分null非null，可是这个hash join的外表就是那个cr_transaction，这个表的ta_account_no是允许null，内表load_balance反而不允许。。不过你那既然复现了应该确实有关系，明天跑了我再看眼，感谢

tiancaiamao · 2023 年4 月 12 日 04:19

https://docs.pingcap.com/tidb/dev/explain-subqueries#null-aware-anti-semi-join-not-in-and--all-subqueries

是否带 null 会对 (anti) semi join 产生很大的影响，因为 join 的 eq 条件正常是 true or false
但是 null 的存在，eq 判断会存在 true or false or null 三种状态，null 的时候会需要特殊的处理
导致最后的查询计划是走笛卡尔积+filter 的形式，性能就会比较差

tidb 6.3 的时候对这块有一些优化，做了 null aware anti join，https://github.com/pingcap/tidb/issues/37525

对于 null aware anti join 这个话题，oracle 应该是有一个这样的专利的
https://patents.google.com/patent/US7676450B2/en

system · 2023 年6 月 11 日 04:20

此话题已在最后回复的 60 天后被自动关闭。不再允许新回复。