在使用 IndexRangeScan 单一值扫表后，tidb 通过什么判断是否可以省略 isnull 筛选

Qiuchi · 2025 年9 月 5 日 06:50

【TiDB 使用环境】生产环境
【TiDB 版本】6.5.0
【操作系统】centos8
【问题复现路径】
同一个查询在 mysql jdbc 客户端和 navicat explain 时发现 navicat 返回执行计划中在使用固定值对索引扫描后，会在回表之前添加对索引列的 selection not(isnull()) 判断，jdbc 客户端 explain 无此情况，该 sql 在 dashboard 里归属于同一条目，sql text 完全相同，无 binding，非 prepared，请教是什么因素导致两者的不同？

explain analyze
select *
from cr_customer_position p
where p.pos_date is not null
  and p.pos_date = '20250803'
  and p.ta_system_code = 'ZJTA'

# navicat 17 mysql
	id                       	task     	estRows    	operator info                                                                                                                                   	actRows	execution info                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     	memory 	disk
	IndexLookUp_10           	root     	13101885.09	                                                                                                                                                	9082737	time:1m36.6s, loops:8871, index_task: {total_time: 1m36.3s, fetch_handle: 595.2ms, build: 1.33ms, wait: 1m35.7s}, table_task: {total_time: 1m36.6s, num: 448, concurrency: 1}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      	95.6 MB	N/A
	├─IndexRangeScan_8(Build)	cop[tikv]	13101885.09	table:p, index:idx_cust_pos_data_code_no(pos_date, ta_system_code, ta_account_no), range:["20250803" "ZJTA","20250803" "ZJTA"], keep order:false	9082737	time:22.7ms, loops:8899, cop_task: {num: 277, max: 256.7ms, min: 939.5µs, avg: 44.3ms, p95: 96ms, max_proc_keys: 50144, p95_proc_keys: 50144, tot_proc: 10.6s, tot_wait: 713ms, rpc_num: 277, rpc_time: 12.3s, copr_cache_hit_ratio: 0.00, distsql_concurrency: 45}, tikv_task:{proc max:231ms, min:0s, avg: 38.2ms, p80:56ms, p95:73ms, iters:9971, tasks:277}, scan_detail: {total_process_keys: 9082737, total_process_keys_size: 826529067, total_keys: 9083014, get_snapshot_time: 525.2ms, rocksdb: {delete_skipped_count: 3, key_skipped_count: 9082740, block: {cache_hit_count: 3720, read_count: 7955, read_byte: 148.9 MB, read_time: 2.25s}}}                         	N/A    	N/A
	└─TableRowIDScan_9(Probe)	cop[tikv]	13101885.09	table:p, keep order:false                                                                                                                       	9082737	time:1m29.5s, loops:9332, cop_task: {num: 61387, max: 1.81s, min: 651.4µs, avg: 38.1ms, p95: 126.9ms, max_proc_keys: 388, p95_proc_keys: 221, tot_proc: 24m4.4s, tot_wait: 10m17.3s, rpc_num: 61387, rpc_time: 39m0.2s, copr_cache_hit_ratio: 0.00, distsql_concurrency: 45}, tikv_task:{proc max:1.79s, min:0s, avg: 23.2ms, p80:20ms, p95:83ms, iters:179278, tasks:61387}, scan_detail: {total_process_keys: 9082737, total_process_keys_size: 6392255185, total_keys: 11230480, get_snapshot_time: 5m35.5s, rocksdb: {delete_skipped_count: 92060, key_skipped_count: 8471039, block: {cache_hit_count: 117027534, read_count: 63277, read_byte: 465.4 MB, read_time: 11.3s}}}	N/A    	N/A

# idea mysql-connector-j-8.0.31
	id                       	task     	estRows    	operator info                                                                                                                                   	actRows	execution info                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              	memory 	disk
	IndexLookUp_11           	root     	13101885.09	                                                                                                                                                	9082737	time:1m41.3s, loops:8871, index_task: {total_time: 1m41s, fetch_handle: 594.9ms, build: 1.26ms, wait: 1m40.4s}, table_task: {total_time: 1m41.3s, num: 448, concurrency: 1}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 	95.1 MB	N/A
	├─Selection_10(Build)    	cop[tikv]	13101885.09	not(isnull(dcdb.cr_customer_position.pos_date))                                                                                                 	9082737	time:34.1ms, loops:8898, cop_task: {num: 277, max: 1.06s, min: 1.54ms, avg: 54.5ms, p95: 125.1ms, max_proc_keys: 50144, p95_proc_keys: 50144, tot_proc: 13.3s, tot_wait: 888ms, rpc_num: 277, rpc_time: 15.1s, copr_cache_hit_ratio: 0.00, distsql_concurrency: 45}, tikv_task:{proc max:979ms, min:0s, avg: 47.8ms, p80:61ms, p95:108ms, iters:9971, tasks:277}, scan_detail: {total_process_keys: 9082737, total_process_keys_size: 826529067, total_keys: 9083014, get_snapshot_time: 728.2ms, rocksdb: {delete_skipped_count: 3, key_skipped_count: 9082740, block: {cache_hit_count: 3507, read_count: 8082, read_byte: 153.3 MB, read_time: 2.3s}}}                   	N/A    	N/A
	│ └─IndexRangeScan_8     	cop[tikv]	13101885.09	table:p, index:idx_cust_pos_data_code_no(pos_date, ta_system_code, ta_account_no), range:["20250803" "ZJTA","20250803" "ZJTA"], keep order:false	9082737	tikv_task:{proc max:973ms, min:0s, avg: 44.6ms, p80:56ms, p95:105ms, iters:9971, tasks:277}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 	N/A    	N/A
	└─TableRowIDScan_9(Probe)	cop[tikv]	13101885.09	table:p, keep order:false                                                                                                                       	9082737	time:1m34.4s, loops:9332, cop_task: {num: 61387, max: 1.77s, min: 1.04ms, avg: 41.3ms, p95: 128.6ms, max_proc_keys: 393, p95_proc_keys: 221, tot_proc: 28m42.2s, tot_wait: 9m25s, rpc_num: 61387, rpc_time: 42m15s, copr_cache_hit_ratio: 0.00, distsql_concurrency: 45}, tikv_task:{proc max:1.7s, min:0s, avg: 27.8ms, p80:31ms, p95:94ms, iters:179309, tasks:61387}, scan_detail: {total_process_keys: 9082737, total_process_keys_size: 6392255185, total_keys: 11229492, get_snapshot_time: 5m11.7s, rocksdb: {delete_skipped_count: 91735, key_skipped_count: 8462671, block: {cache_hit_count: 113561725, read_count: 861820, read_byte: 6.49 GB, read_time: 4m1s}}}	N/A    	N/A

# ddl 省略 50 余列
create table cr_customer_position
(
    id             bigint(19) auto_increment
        primary key,
    pos_date       char(8),
    ta_account_no  varchar(100),
    ta_system_code varchar(20) default 'ZJTA' not null
);

create index idx_cust_pos_data_code_no
    on cr_customer_position (pos_date, ta_system_code, ta_account_no)

Billmay表妹 · 2025 年9 月 5 日 07:03

不同客户端的兼容性问题？

Billmay表妹 · 2025 年9 月 5 日 07:04

一般以 dashboard 的展示为准

Qiuchi · 2025 年9 月 5 日 07:06

dashboard会在同一条sql下面展示这俩个不同的执行计划

TiDBer_HErMeXDz · 2025 年9 月 5 日 07:20

抓包分析一下。

Qiuchi · 2025 年9 月 5 日 07:53

抓了一下，但感觉没啥区别，控制指令没看到有什么可能会有影响的，除了收集信息的指令就只有下面这些了，提交的 sql 也是一样的

# idea mysql-connector-j-8.0.31
SET autocommit=1;
set session transaction read write;
use dcdb;
SET net_write_timeout=600;
explain
select *
from cr_customer_position p
where p.pos_date is not null
  and p.pos_date = '20250803'
  and p.ta_system_code = 'ZJTA';

# navicat 17 mysql
SET NAMES utf8mb4;
SET NAMES utf8mb4;
use dcdb;
explain
select *
from cr_customer_position p
where p.pos_date is not null
  and p.pos_date = '20250803'
  and p.ta_system_code = 'ZJTA'

Miracle · 2025 年9 月 5 日 10:29

感觉像是这个sql的写法导致的，因为这个pos_date即写了is not null 又写了pos_date=xxx。
dashboard里 is not null 这个null是一个变量，因此生成了两种执行计划，是null时可以合并，不是null时就不合并。

Qiuchi · 2025 年9 月 8 日 01:26

但是我这两个客户端稳定地复现产生对应的那一种执行计划，同时提交的sql也是一样的

cchouqiang · 2025 年9 月 11 日 02:15

Navicat和 MySQL JDBC 客户端可能有不同的设置或默认行为，这可能会影响查询的执行计划。

独善其身 · 2026 年2 月 2 日 01:09

试试ifnull函数看看