慢查优化子查询默认hashjoin导致全索引扫描

TiDB_C罗 · 2023 年12 月 20 日 08:53

【 TiDB 使用环境】生产环境 /测试/ Poc
【 TiDB 版本】
【复现路径】做过哪些操作出现的问题
【遇到的问题：问题现象及影响】
【资源配置】
语句1

select 
  count(1) as is_mobile_black
from abc.customer_risk
where aes_risk_value in ( 'PuJ/d/FH1B4Y23K+7sPYMg==','mMIAL/pPIJ724OtBXXvv+w==') and risk_type=1 and risk_item = 1

语句1的执行计划

+-----------------------------+---------+---------+-----------+---------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+------+
| id                          | estRows | actRows | task      | access object                                                                                           | execution info                                                                                                                                                                            | operator info                                                                                                                                            | memory    | disk |
+-----------------------------+---------+---------+-----------+---------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+------+
| StreamAgg_17                | 1.00    | 1       | root      |                                                                                                         | time:626µs, loops:2                                                                                                                                                                       | funcs:count(Column#23)->Column#21                                                                                                                        | 5.50 KB   | N/A  |
| └─IndexReader_18            | 1.00    | 0       | root      |                                                                                                         | time:623.3µs, loops:1, cop_task: {num: 2, max: 592.1µs, min: 512.8µs, avg: 552.5µs, p95: 592.1µs, rpc_num: 2, rpc_time: 1.05ms, copr_cache_hit_ratio: 1.00, distsql_concurrency: 15}      | index:StreamAgg_9                                                                                                                                        | 319 Bytes | N/A  |
|   └─StreamAgg_9             | 1.00    | 0       | cop[tikv] |                                                                                                         | tikv_task:{proc max:3ms, min:2ms, avg: 2.5ms, p80:3ms, p95:3ms, iters:2, tasks:2}, scan_detail: {get_snapshot_time: 551µs, rocksdb: {block: {}}}                                          | funcs:count(1)->Column#23                                                                                                                                | N/A       | N/A  |
|     └─IndexRangeScan_16     | 2.03    | 0       | cop[tikv] | table:customer_risk, index:idx_aes_risk_value_risk_type_risk_item(aes_risk_value, risk_type, risk_item) | tikv_task:{proc max:3ms, min:2ms, avg: 2.5ms, p80:3ms, p95:3ms, iters:2, tasks:2}                                                                                                         | range:["mMIAL/pPIJ724OtBXXvv+w==" 1 1,"mMIAL/pPIJ724OtBXXvv+w==" 1 1], ["PuJ/d/FH1B4Y23K+7sPYMg==" 1 1,"PuJ/d/FH1B4Y23K+7sPYMg==" 1 1], keep order:false | N/A       | N/A  |
+-----------------------------+---------+---------+-----------+---------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+------+
4 rows in set (0.00 sec)

语句2

with related_id_collection as (
    select distinct
      aes_mobile
    from abc.client_info_new
    where related_id in ( 
        select related_id
        from abc.activity_join
        where activity_id=1 and join_role='MGM_BE_INVITED' and account_id=202312200123
      )
)

select  
  count(1) as is_mobile_black
from abc.customer_risk 
where aes_risk_value in (select * from related_id_collection) and risk_type=1 and risk_item = 1

语句2的执行计划

+----------------------------------------------+------------+---------+-----------+---------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+---------+
| id                                           | estRows    | actRows | task      | access object                                                                                           | execution info                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           | operator info                                                                                                                                                                                                           | memory   | disk    |
+----------------------------------------------+------------+---------+-----------+---------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+---------+
| HashAgg_18                                   | 1.00       | 1       | root      |                                                                                                         | time:1.72s, loops:2, partial_worker:{wall_time:1.718923941s, concurrency:5, task_num:0, tot_wait:8.594333898s, tot_exec:0s, tot_time:8.594335877s, max:1.71886768s, p95:1.71886768s}, final_worker:{wall_time:0s, concurrency:5, task_num:0, tot_wait:8.594411803s, tot_exec:3.568µs, tot_time:8.594418221s, max:1.718888906s, p95:1.718888906s}                                                                                                                                                                                                                         | funcs:count(1)->Column#52                                                                                                                                                                                               | 6.15 KB  | N/A     |
| └─HashJoin_20                                | 2226585.93 | 0       | root      |                                                                                                         | time:1.72s, loops:1, build_hash_table:{total:9.68ms, fetch:9.67ms, build:5.63µs}, probe:{concurrency:5, total:8.59s, max:1.72s, probe:421.3ms, fetch:8.17s}                                                                                                                                                                                                                                                                                                                                                                                                              | semi join, equal:[eq(abc.customer_risk.aes_risk_value, abc.client_info_new.aes_mobile)]                                                                                                                     | 2.46 KB  | 0 Bytes |
|   ├─HashAgg_27(Build)                        | 11.01      | 2       | root      |                                                                                                         | time:9.65ms, loops:3, partial_worker:{wall_time:9.644612ms, concurrency:5, task_num:2, tot_wait:47.775398ms, tot_exec:274.905µs, tot_time:48.057754ms, max:9.615447ms, p95:9.615447ms}, final_worker:{wall_time:9.651113ms, concurrency:5, task_num:4, tot_wait:48.103764ms, tot_exec:18.188µs, tot_time:48.124101ms, max:9.626935ms, p95:9.626935ms}                                                                                                                                                                                                                    | group by:abc.client_info_new.aes_mobile, funcs:firstrow(abc.client_info_new.aes_mobile)->abc.client_info_new.aes_mobile                                                                               | 240.2 KB | N/A     |
|   │ └─IndexHashJoin_37                       | 11.01      | 1574    | root      |                                                                                                         | time:9.47ms, loops:3, inner:{total:8.41ms, concurrency:5, task:1, construct:12.9µs, fetch:7.96ms, build:2.9µs, join:434.1µs}                                                                                                                                                                                                                                                                                                                                                                                                                                             | inner join, inner:IndexLookUp_34, outer key:abc.activity_join.related_id, inner key:abc.client_info_new.related_id, equal cond:eq(abc.activity_join.related_id, abc.client_info_new.related_id) | 67.7 KB  | N/A     |
|   │   ├─HashAgg_65(Build)                    | 1.00       | 1       | root      |                                                                                                         | time:1.01ms, loops:3, partial_worker:{wall_time:995.868µs, concurrency:5, task_num:1, tot_wait:4.735654ms, tot_exec:5.978µs, tot_time:4.746302ms, max:961.318µs, p95:961.318µs}, final_worker:{wall_time:1.011992ms, concurrency:5, task_num:1, tot_wait:4.851593ms, tot_exec:12.018µs, tot_time:4.866267ms, max:988.619µs, p95:988.619µs}                                                                                                                                                                                                                               | group by:abc.activity_join.related_id, funcs:firstrow(abc.activity_join.related_id)->abc.activity_join.related_id                                                                                     | 74.8 KB  | N/A     |
|   │   │ └─IndexLookUp_88                     | 1.08       | 1       | root      |                                                                                                         | time:928.6µs, loops:2, index_task: {total_time: 432.9µs, fetch_handle: 431.1µs, build: 838ns, wait: 953ns}, table_task: {total_time: 441.6µs, num: 1, concurrency: 5}, next: {wait_index: 477.2µs, wait_table_lookup_build: 63.8µs, wait_table_lookup_resp: 374.7µs}                                                                                                                                                                                                                                                                                                     |                                                                                                                                                                                                                         | 9.75 KB  | N/A     |
|   │   │   ├─IndexRangeScan_85(Build)         | 1.08       | 1       | cop[tikv] | table:activity_join, index:idx_account_active_rule(activity_id, join_role, account_id)                  | time:428.1µs, loops:3, cop_task: {num: 1, max: 387.1µs, proc_keys: 1, rpc_num: 1, rpc_time: 374.3µs, copr_cache_hit_ratio: 0.00, distsql_concurrency: 15}, tikv_task:{time:0s, loops:1}, scan_detail: {total_process_keys: 1, total_process_keys_size: 120, total_keys: 2, get_snapshot_time: 7.88µs, rocksdb: {key_skipped_count: 1, block: {cache_hit_count: 10}}}                                                                                                                                                                                                     | range:[1 "MGM_BE_INVITED" 202312200123,1 "MGM_BE_INVITED" 202312200123], keep order:false                                                                                                                   | N/A      | N/A     |
|   │   │   └─Selection_87(Probe)              | 1.08       | 1       | cop[tikv] |                                                                                                         | time:358.3µs, loops:2, cop_task: {num: 1, max: 320.4µs, proc_keys: 1, rpc_num: 1, rpc_time: 297.1µs, copr_cache_hit_ratio: 0.00, distsql_concurrency: 15}, tikv_task:{time:0s, loops:1}, scan_detail: {total_process_keys: 1, total_process_keys_size: 94, total_keys: 1, get_snapshot_time: 3.74µs, rocksdb: {block: {cache_hit_count: 9}}}                                                                                                                                                                                                                             | not(isnull(abc.activity_join.related_id))                                                                                                                                                                         | N/A      | N/A     |
|   │   │     └─TableRowIDScan_86              | 1.08       | 1       | cop[tikv] | table:activity_join                                                                                     | tikv_task:{time:0s, loops:1}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             | keep order:false                                                                                                                                                                                                        | N/A      | N/A     |
|   │   └─IndexLookUp_34(Probe)                | 11.01      | 1574    | root      |                                                                                                         | time:7.93ms, loops:3, index_task: {total_time: 2.01ms, fetch_handle: 2ms, build: 997ns, wait: 9.19µs}, table_task: {total_time: 10.6ms, num: 2, concurrency: 5}, next: {wait_index: 2.03ms, wait_table_lookup_build: 586.6µs, wait_table_lookup_resp: 5.24ms}                                                                                                                                                                                                                                                                                                            |                                                                                                                                                                                                                         | 124.8 KB | N/A     |
|   │     ├─IndexRangeScan_32(Build)           | 11.01      | 1574    | cop[tikv] | table:client_info_new, index:related_id(related_id)                                                     | time:1.95ms, loops:4, cop_task: {num: 3, max: 726.4µs, min: 523.1µs, avg: 629.9µs, p95: 726.4µs, max_proc_keys: 870, p95_proc_keys: 870, rpc_num: 3, rpc_time: 1.86ms, copr_cache_hit_ratio: 0.00, distsql_concurrency: 15}, tikv_task:{proc max:1ms, min:0s, avg: 333.3µs, p80:1ms, p95:1ms, iters:12, tasks:3}, scan_detail: {total_process_keys: 1574, total_process_keys_size: 72404, total_keys: 1577, get_snapshot_time: 22.7µs, rocksdb: {key_skipped_count: 1574, block: {cache_hit_count: 42, read_count: 1, read_byte: 64.0 KB, read_time: 27.6µs}}}           | range: decided by [eq(abc.client_info_new.related_id, abc.activity_join.related_id)], keep order:false                                                                                                      | N/A      | N/A     |
|   │     └─TableRowIDScan_33(Probe)           | 11.01      | 1574    | cop[tikv] | table:client_info_new                                                                                   | time:9.58ms, loops:4, cop_task: {num: 305, max: 2.89ms, min: 216.1µs, avg: 1.14ms, p95: 2.38ms, max_proc_keys: 15, p95_proc_keys: 5, tot_proc: 2ms, tot_wait: 40ms, rpc_num: 305, rpc_time: 344ms, copr_cache_hit_ratio: 0.51, distsql_concurrency: 15}, tikv_task:{proc max:90ms, min:0s, avg: 16.6ms, p80:36ms, p95:68ms, iters:306, tasks:305}, scan_detail: {total_process_keys: 316, total_process_keys_size: 93044, total_keys: 316, get_snapshot_time: 66.2ms, rocksdb: {block: {cache_hit_count: 3850, read_count: 7, read_byte: 167.2 KB, read_time: 1.55ms}}}  | keep order:false                                                                                                                                                                                                        | N/A      | N/A     |
|   └─IndexReader_26(Probe)                    | 2783232.42 | 2779713 | root      |                                                                                                         | time:1.64s, loops:2707, cop_task: {num: 461, max: 712ms, min: 188.8µs, avg: 32.9ms, p95: 94.5ms, max_proc_keys: 141280, p95_proc_keys: 47072, tot_proc: 14.2s, tot_wait: 192ms, rpc_num: 461, rpc_time: 15.1s, copr_cache_hit_ratio: 0.11, distsql_concurrency: 15}                                                                                                                                                                                                                                                                                                      | index:Selection_25                                                                                                                                                                                                      | 3.49 MB  | N/A     |
|     └─Selection_25                           | 2783232.42 | 2779713 | cop[tikv] |                                                                                                         | tikv_task:{proc max:704ms, min:0s, avg: 31.1ms, p80:49ms, p95:88ms, iters:9483, tasks:461}, scan_detail: {total_process_keys: 7740744, total_process_keys_size: 1669491051, total_keys: 7741156, get_snapshot_time: 52.2ms, rocksdb: {key_skipped_count: 7740744, block: {cache_hit_count: 5095, read_count: 25252, read_byte: 631.9 MB, read_time: 4.41s}}}                                                                                                                                                                                                             | eq(abc.customer_risk.risk_item, 1), eq(abc.customer_risk.risk_type, 1)                                                                                                                                      | N/A      | N/A     |
|       └─IndexFullScan_24                     | 7808808.00 | 7808808 | cop[tikv] | table:customer_risk, index:idx_aes_risk_value_risk_type_risk_item(aes_risk_value, risk_type, risk_item) | tikv_task:{proc max:694ms, min:0s, avg: 30.2ms, p80:48ms, p95:86ms, iters:9483, tasks:461}                                                                                                                                                                                                                                                                                                                                                                                                                                                                               | keep order:false                                                                                                                                                                                                        | N/A      | N/A     |
+----------------------------------------------+------------+---------+-----------+---------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+---------+
15 rows in set (1.72 sec)

related_id_collection子句的执行计划

+----------------------------------------+---------+-----------+----------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id                                     | estRows | task      | access object                                                                          | operator info                                                                                                                                                                                                           |
+----------------------------------------+---------+-----------+----------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| HashAgg_12                             | 11.07   | root      |                                                                                        | group by:abc.client_info_new.aes_mobile, funcs:firstrow(abc.client_info_new.aes_mobile)->abc.client_info_new.aes_mobile                                                                               |
| └─IndexHashJoin_22                     | 11.07   | root      |                                                                                        | inner join, inner:IndexLookUp_19, outer key:abc.activity_join.related_id, inner key:abc.client_info_new.related_id, equal cond:eq(abc.activity_join.related_id, abc.client_info_new.related_id) |
|   ├─HashAgg_50(Build)                  | 1.00    | root      |                                                                                        | group by:abc.activity_join.related_id, funcs:firstrow(abc.activity_join.related_id)->abc.activity_join.related_id                                                                                     |
|   │ └─IndexLookUp_73                   | 1.08    | root      |                                                                                        |                                                                                                                                                                                                                         |
|   │   ├─IndexRangeScan_70(Build)       | 1.08    | cop[tikv] | table:activity_join, index:idx_account_active_rule(activity_id, join_role, account_id) | range:[1 "MGM_BE_INVITED" 231210010003249607,1 "MGM_BE_INVITED" 231210010003249607], keep order:false                                                                                                                   |
|   │   └─Selection_72(Probe)            | 1.08    | cop[tikv] |                                                                                        | not(isnull(abc.activity_join.related_id))                                                                                                                                                                         |
|   │     └─TableRowIDScan_71            | 1.08    | cop[tikv] | table:activity_join                                                                    | keep order:false                                                                                                                                                                                                        |
|   └─IndexLookUp_19(Probe)              | 11.07   | root      |                                                                                        |                                                                                                                                                                                                                         |
|     ├─IndexRangeScan_17(Build)         | 11.07   | cop[tikv] | table:client_info_new, index:related_id(related_id)                                    | range: decided by [eq(abc.client_info_new.related_id, abc.activity_join.related_id)], keep order:false                                                                                                      |
|     └─TableRowIDScan_18(Probe)         | 11.07   | cop[tikv] | table:client_info_new                                                                  | keep order:false                                                                                                                                                                                                        |
+----------------------------------------+---------+-----------+----------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
10 rows in set (0.00 sec)

语句2中的related_id_collection查询结果就是语句1中的in值，为啥执行效率相差这么多，如何优化？

caiyfc · 2023 年12 月 20 日 09:05

加hint，让customer_risk, client_info_new这两个表走index join看看呢

TiDB_C罗 · 2023 年12 月 20 日 10:23

加了，死活不生效

caiyfc · 2023 年12 月 21 日 01:19

1、尝试把with里面的sql直接放到in里面，然后再用hint
2、检查一下probe端的表连接字段有没有索引

oceanzhang · 2023 年12 月 21 日 01:28

这明显就是两个完全不想关的语句，语句2相当于视图

TiDB_C罗 · 2023 年12 月 21 日 02:36

尝试过了还是不行，也试过join了。
背景是这样，有一个新表和旧表，旧表是明文，新表是脱敏过的，旧表同样的写法就可以使用StreamAgg，切换到新表就成了慢查询。

小龙虾爱大龙虾 · 2023 年12 月 21 日 02:39

有问题的表结构拿出来

TiDB_C罗 · 2023 年12 月 21 日 02:44

旧表

CREATE TABLE `client_info` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `mobile` varchar(16) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '冗余字段,关联一些现场数据',
  `service_type` smallint(2) NOT NULL COMMENT '服务类型',
  `related_id` bigint(20) NOT NULL COMMENT '关联到的主键id',
  `ip` varchar(16) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '客户端ip',
  `os` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '手机操作系统详细描述',
  `imei` text COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'imei号',
  `imei_md5` char(32) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT 'imei的md5值',
  `uuid` varchar(128) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT 'uuid',
  `uuid_md5` char(32) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT 'uuid的md5值',
  `model` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '手机型号',
  `brand` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '手机品牌',
  `app_version` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'app当前版本号',
  `app_version_code` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'app在GP中的版本号',
  `longitude` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '经度',
  `latitude` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '纬度',
  `city` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '用户所在城市',
  `time_zone` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '时区',
  `network` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '网络制式',
  `is_simulator` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否是模拟器. 1: 是; 0: 否',
  `platform` varchar(16) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '取值为: pc, h5, iphone, ipad, android 之一',
  `ctime` bigint(20) NOT NULL COMMENT '记录创建时间',
  `ui_version` varchar(16) COLLATE utf8mb4_unicode_ci NOT NULL,
  `stem_from` varchar(128) COLLATE utf8mb4_unicode_ci NOT NULL,
  `ctime_day` bigint(20) DEFAULT NULL COMMENT '创建日期',
  `aes_mobile` varchar(128) NOT NULL DEFAULT '' COMMENT 'aes_mobile',
  PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */,
  KEY `ip` (`ip`),
  KEY `service_type` (`service_type`),
  KEY `ctime` (`ctime`),
  KEY `related_id` (`related_id`),
  KEY `imei_md5` (`imei_md5`),
  KEY `i_uuid_md5` (`uuid_md5`),
  KEY `idx_mobile` (`mobile`),
  KEY `idx_ctime_day` (`ctime_day`),
  KEY `idx_related_id_ctime` (`related_id`,`ctime`),
  KEY `idx_ip_ctime` (`ip`,`ctime`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=1000150000 COMMENT='客户端现场数据'

新表

CREATE TABLE `client_info_new` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `service_type` smallint(2) NOT NULL COMMENT '服务类型',
  `related_id` bigint(20) NOT NULL COMMENT '关联到的主键id',
  `ip` varchar(16) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '客户端ip',
  `os` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '手机操作系统详细描述',
  `imei` text COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'imei号',
  `imei_md5` char(32) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT 'imei的md5值',
  `model` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '手机型号',
  `brand` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '手机品牌',
  `app_version` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'app当前版本号',
  `app_version_code` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'app在GP中的版本号',
  `longitude` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '经度',
  `latitude` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '纬度',
  `city` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '用户所在城市',
  `time_zone` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '时区',
  `network` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '网络制式',
  `is_simulator` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否是模拟器. 1: 是; 0: 否',
  `platform` varchar(16) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '取值为: pc, h5, iphone, ipad, android 之一',
  `ctime` bigint(20) NOT NULL COMMENT '记录创建时间',
  `ui_version` varchar(16) COLLATE utf8mb4_unicode_ci NOT NULL,
  `stem_from` varchar(128) COLLATE utf8mb4_unicode_ci NOT NULL,
  `ctime_day` bigint(20) DEFAULT NULL COMMENT '创建日期',
  `aes_mobile` varchar(128) NOT NULL DEFAULT '' COMMENT 'aes_mobile',
  PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */,
  KEY `service_type` (`service_type`),
  KEY `ctime` (`ctime`),
  KEY `related_id` (`related_id`),
  KEY `imei_md5` (`imei_md5`),
  KEY `idx_mobile` (`aes_mobile`),
  KEY `idx_ctime_day` (`ctime_day`),
  KEY `idx_related_id_ctime` (`related_id`,`ctime`),
  KEY `idx_ip_ctime` (`ip`,`ctime`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=782785469 COMMENT='客户端现场数据'

caiyfc · 2023 年12 月 21 日 02:46

用这个尝试一下： NO_DECORRELATE() Optimizer Hints | PingCAP 文档中心

小龙虾爱大龙虾 · 2023 年12 月 21 日 02:56

已经看到你的新旧表校验规则不一样的，虽然没给其他表结构，八成就是同一SQL间不同表校验规则不一样导致的走不上INL_JOIN

zhanggame1 · 2023 年12 月 21 日 03:05

涉及到的几个表先手工分析analyze再看看执行计划。
对于数据库来说并不知道你返回的数据量，尤其是字符串类型，所以执行计划选择可能是错误的

TiDB_C罗 · 2023 年12 月 21 日 03:15

+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                                 |
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1815 | NO_DECORRELATE() is inapplicable because it's not in an IN subquery, an EXISTS subquery, an ANY/ALL/SOME subquery or a scalar subquery. |
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------+

caiyfc · 2023 年12 月 21 日 03:23

看一下customer_risk这个表的结构吧

TiDB_C罗 · 2023 年12 月 21 日 03:28

CREATE TABLE `customer_risk` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `customer_id` bigint(20) unsigned NOT NULL COMMENT '客户id,对应用户系统的account_id',
  `risk_item` smallint(2) NOT NULL COMMENT '风险项',
  `risk_type` smallint(2) NOT NULL COMMENT '风险类型',
  `risk_value` varchar(128) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '风险项对应的值,混合型',
  `reason` int(5) NOT NULL COMMENT '上报/解除原因',
  `relieve_reason` int(5) NOT NULL DEFAULT '0' COMMENT '解除原因',
  `report_remark` text COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '上报操作备注',
  `review_remark` text COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '审核操作备注',
  `relieve_remark` text COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '解除操作备注',
  `op_uid` bigint(20) unsigned NOT NULL COMMENT '操作员uid',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0' COMMENT '记录是否被标记为删除',
  `status` tinyint(1) NOT NULL DEFAULT '0' COMMENT '状态: 待审核 0, 已审核 1, 审核拒绝 2',
  `ctime` bigint(20) NOT NULL COMMENT '记录创建时间',
  `utime` bigint(20) NOT NULL COMMENT '记录最后更新时间',
  `review_time` bigint(20) NOT NULL DEFAULT '0' COMMENT '审核时间',
  `relieve_time` bigint(20) NOT NULL DEFAULT '0' COMMENT '手动解除时间',
  `order_ids` text COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '涉及的订单id， 逗号分隔',
  `user_account_ids` text COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '涉及的用户id， 逗号分隔',
  `aes_risk_value` varchar(256) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT 'aes_risk_value',
  PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */,
  KEY `valid_risk_value` (`is_deleted`,`status`,`risk_value`),
  KEY `cid_ri_d` (`customer_id`,`risk_item`),
  KEY `id_risk_value` (`risk_value`),
  KEY `idx_ctime` (`ctime`),
  KEY `idx_aes_risk_value` (`aes_risk_value`),
  KEY `idx_aes_risk_value_risk_type_risk_item` (`aes_risk_value`,`risk_type`,`risk_item`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci AUTO_INCREMENT=7876500 COMMENT='客户风险管理'

caiyfc · 2023 年12 月 21 日 03:33

感觉有可能是排序规则不同导致不能index join：排序规则不兼容导致 INL_JOIN Hint 不生效

tidb菜鸟一只 · 2023 年12 月 21 日 08:35

为啥有的字段指定排序规则有的不指定。。。表的默认排序规则和字段的又不一致。。。。
aes_mobile varchar(128) NOT NULL DEFAULT ‘’ COMMENT ‘aes_mobile’,
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin

zhanggame1 · 2023 年12 月 21 日 14:54

很可能排序规则不一致引发得