Slow Query Optimization: Default Hash Join for Subqueries Leading to Full Index Scan

translator_bot · June 21, 2024, 9:30am

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

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

| username: TiDB_C罗

[TiDB Usage Environment] Production Environment / Test / Poc
[TiDB Version]
[Reproduction Path] What operations were performed to encounter the issue
[Encountered Issue: Problem Phenomenon and Impact]
[Resource Configuration]
Statement 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

Execution Plan of Statement 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)

Statement 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

Execution Plan of Statement 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)

Execution Plan of related_id_collection clause in Statement 2

+----------------------------------------+---------+-----------+----------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 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

translator_bot · June 21, 2024, 9:30am

| username: caiyfc | Original post link

Try adding a hint to make the tables customer_risk and client_info_new use an index join.

translator_bot · June 21, 2024, 9:30am

| username: TiDB_C罗 | Original post link

Added it, but it still doesn’t work.

translator_bot · June 21, 2024, 9:30am

| username: caiyfc | Original post link

Try placing the SQL inside the WITH clause directly into the IN clause, and then use a hint.
Check if the table join fields on the probe side have indexes.

translator_bot · June 21, 2024, 9:30am

| username: oceanzhang | Original post link

These are obviously two completely unrelated sentences, with sentence 2 being equivalent to a view.

translator_bot · June 21, 2024, 9:30am

| username: TiDB_C罗 | Original post link

I tried it but it still doesn’t work, and I also tried using join.
The background is like this: there is a new table and an old table, the old table is in plaintext, and the new table is desensitized. The same query on the old table can use StreamAgg, but switching to the new table results in a slow query.

translator_bot · June 21, 2024, 9:30am

| username: 小龙虾爱大龙虾 | Original post link

Take out the problematic table structure.

translator_bot · June 21, 2024, 9:30am

| username: TiDB_C罗 | Original post link

Old Table

CREATE TABLE `client_info` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'Primary key id',
  `mobile` varchar(16) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Redundant field, associated with some on-site data',
  `service_type` smallint(2) NOT NULL COMMENT 'Service type',
  `related_id` bigint(20) NOT NULL COMMENT 'Associated primary key id',
  `ip` varchar(16) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Client IP',
  `os` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Detailed description of the mobile operating system',
  `imei` text COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'IMEI number',
  `imei_md5` char(32) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT 'MD5 value of IMEI',
  `uuid` varchar(128) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT 'UUID',
  `uuid_md5` char(32) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT 'MD5 value of UUID',
  `model` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Mobile model',
  `brand` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Mobile brand',
  `app_version` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Current version of the app',
  `app_version_code` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Version number of the app in GP',
  `longitude` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Longitude',
  `latitude` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Latitude',
  `city` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT 'User's city',
  `time_zone` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Time zone',
  `network` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Network type',
  `is_simulator` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'Is it a simulator. 1: Yes; 0: No',
  `platform` varchar(16) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Values: pc, h5, iphone, ipad, android',
  `ctime` bigint(20) NOT NULL COMMENT 'Record creation time',
  `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 'Creation date',
  `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='Client on-site data'

New Table

CREATE TABLE `client_info_new` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'Primary key id',
  `service_type` smallint(2) NOT NULL COMMENT 'Service type',
  `related_id` bigint(20) NOT NULL COMMENT 'Associated primary key id',
  `ip` varchar(16) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Client IP',
  `os` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Detailed description of the mobile operating system',
  `imei` text COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'IMEI number',
  `imei_md5` char(32) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT 'MD5 value of IMEI',
  `model` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Mobile model',
  `brand` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Mobile brand',
  `app_version` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Current version of the app',
  `app_version_code` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Version number of the app in GP',
  `longitude` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Longitude',
  `latitude` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Latitude',
  `city` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT 'User's city',
  `time_zone` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Time zone',
  `network` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Network type',
  `is_simulator` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'Is it a simulator. 1: Yes; 0: No',
  `platform` varchar(16) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Values: pc, h5, iphone, ipad, android',
  `ctime` bigint(20) NOT NULL COMMENT 'Record creation time',
  `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 'Creation date',
  `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='Client on-site data'

translator_bot · June 21, 2024, 9:30am

| username: caiyfc | Original post link

Try this: NO_DECORRELATE() Optimizer Hints | PingCAP Documentation Center

translator_bot · June 21, 2024, 9:30am

| username: 小龙虾爱大龙虾 | Original post link

I have already noticed that the validation rules for your new and old tables are different . Although you didn’t provide the structures of other tables, it’s highly likely that the different validation rules for different tables within the same SQL are causing the INL_JOIN not to be used.

translator_bot · June 21, 2024, 9:30am

| username: zhanggame1 | Original post link

Manually analyze the involved tables first and then check the execution plan. For the database, it doesn’t know the amount of data you are returning, especially for string types, so the execution plan choice might be incorrect.

translator_bot · June 21, 2024, 9:30am

| username: TiDB_C罗 | Original post link

+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------+
| 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. |
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------+

translator_bot · June 21, 2024, 9:30am

| username: caiyfc | Original post link

Let’s take a look at the structure of the customer_risk table.

translator_bot · June 21, 2024, 9:30am

| username: TiDB_C罗 | Original post link

CREATE TABLE `customer_risk` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'Primary key id',
  `customer_id` bigint(20) unsigned NOT NULL COMMENT 'Customer id, corresponding to the account_id in the user system',
  `risk_item` smallint(2) NOT NULL COMMENT 'Risk item',
  `risk_type` smallint(2) NOT NULL COMMENT 'Risk type',
  `risk_value` varchar(128) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Value corresponding to the risk item, mixed type',
  `reason` int(5) NOT NULL COMMENT 'Reason for reporting/release',
  `relieve_reason` int(5) NOT NULL DEFAULT '0' COMMENT 'Reason for release',
  `report_remark` text COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Remarks for reporting operation',
  `review_remark` text COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Remarks for review operation',
  `relieve_remark` text COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Remarks for release operation',
  `op_uid` bigint(20) unsigned NOT NULL COMMENT 'Operator uid',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'Whether the record is marked as deleted',
  `status` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'Status: Pending review 0, Reviewed 1, Review rejected 2',
  `ctime` bigint(20) NOT NULL COMMENT 'Record creation time',
  `utime` bigint(20) NOT NULL COMMENT 'Record last update time',
  `review_time` bigint(20) NOT NULL DEFAULT '0' COMMENT 'Review time',
  `relieve_time` bigint(20) NOT NULL DEFAULT '0' COMMENT 'Manual release time',
  `order_ids` text COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Involved order ids, separated by commas',
  `user_account_ids` text COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Involved user ids, separated by commas',
  `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='Customer risk management'

translator_bot · June 21, 2024, 9:30am

| username: caiyfc | Original post link

It is possible that different collations are causing the index join to fail: Collation incompatibility causes INL_JOIN Hint to not take effect

translator_bot · June 21, 2024, 9:30am

| username: tidb菜鸟一只 | Original post link

Why are some fields specified with collation and some not… The default collation of the table is different from that of the fields…
aes_mobile varchar(128) NOT NULL DEFAULT ‘’ COMMENT ‘aes_mobile’,
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin

translator_bot · June 21, 2024, 9:30am

| username: zhanggame1 | Original post link

It is very likely caused by inconsistent collation.