Execution Plan Issues After Upgrading from 5.2.3 to 7.5.1

Translated
1

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

Original topic: 5.2.3升级7.5.1后执行计划有问题

| username: h5n1

Table structure

CREATE TABLE `TI_O_CREDIT_STOP_OPEN_WP_5` (
`USER_ID` bigint(20) unsigned NOT NULL ,
 `IS_WP_FINISHED` char(1) DEFAULT '0',
`OPEN_TRADE_ID` bigint(20) unsigned DEFAULT NULL,
`TRADE_ID` bigint(20) unsigned NOT NULL
.........
 KEY `TI_O_CREDIT_STOP_OPEN_WP_5_USER_ID_IDX` (`USER_ID`,`IS_WP_FINISHED`),
  PRIMARY KEY (`TRADE_ID`,`USER_ID`) /*T![clustered_index] NONCLUSTERED */,
  KEY `TI_O_CREDIT_STOP_OPEN_WP_5_OPEN_TRADE_ID_IDX` (`OPEN_TRADE_ID`)
)

SQL:

INSERT INTO
  wise_cred.TI_O_CREDIT_STOP_OPEN_WP_5(
    TRADE_ID,
    USER_ID,.........
  )
SELECT
  '8524364062384',
  '852061143967',
  '0850408',
  '909394',
  '30',
  '0854',
  '85',
  1,
  202402,
  202402,
  '1000',
  '8524045362384',
  '852404357623',
  '2024-04-19 08:00:06',
  '1',
  '1'
FROM
  wise_cred.TI_O_CREDIT_STOP_OPEN_WP_5
WHERE
  NOT EXISTS (
    SELECT
      1
    FROM
      wise_cred.TI_O_CREDIT_STOP_OPEN_WP_5
    WHERE
      USER_ID = '8520082061143967'
      AND IS_WP_FINISHED = '0'
  )
LIMIT
  1

Currently, there are 3 execution plans generated

Execution Plan 1:

| id                         | estRows | estCost | actRows | task      | access object                                                                                       | execution info                                                                                                                                                                                                                                                                                                               | operator info                                                                                                                                                                                                                                                                                                                                                   | memory    | disk  |
| Insert_1                   | 0.00    | 0.00    | 0       | root      |                                                                                                     | time:2.99ms, loops:1, prepare: 2.9ms, insert:92µs, lock_keys: {time:589.2µs, region:2, keys:2, slowest_rpc: {total: 0.001s, region_id: 3281518193, store: 10.172.65.119:20160, tikv_wall_time: 185µs, scan_detail: {get_snapshot_time: 16.2µs, rocksdb: {block: {cache_hit_count: 11}}}, }, lock_rpc:987.282µs, rpc_count:2} | N/A                                                                                                                                                                                                                                                                                                                                                             | 19.9 KB   | N/A   |
| └─Projection_21            | 1.00    | 27.93   | 1       | root      |                                                                                                     | time:2.81ms, loops:2, Concurrency:OFF                                                                                                                                                                                                                                                                                        | 8524045377442160->Column#460, 8520082061143708->Column#461, 08547020197->Column#462, 90933194->Column#463, 30->Column#464, 0854->Column#465, 85->Column#466, 1->Column#467, 202402->Column#468, 202402->Column#469, 1000->Column#470, 8524045377442160->Column#471, 8524043485957675->Column#472, 2024-04-19 08:00:07->Column#473, 1->Column#474, 1->Column#475 | 380 Bytes | N/A   |
|   └─Limit_24               | 1.00    | 9.32    | 1       | root      |                                                                                                     | time:2.8ms, loops:2                                                                                                                                                                                                                                                                                                          | offset:0, count:1                                                                                                                                                                                                                                                                                                                                               | N/A       | N/A   |
|     └─UnionScan_25         | 1.00    | 9.32    | 32      | root      |                                                                                                     | time:2.8ms, loops:1                                                                                                                                                                                                                                                                                                          |                                                                                                                                                                                                                                                                                                                                                                 | N/A       | N/A   |
|       └─IndexReader_33     | 1.00    | 9.32    | 1024    | root      |                                                                                                     | time:2.32ms, loops:1, cop_task: {num: 4, max: 2.23ms, min: 854.3µs, avg: 1.41ms, p95: 2.23ms, max_proc_keys: 480, p95_proc_keys: 480, tot_proc: 2.84ms, tot_wait: 787.8µs, rpc_num: 4, rpc_time: 5.56ms, copr_cache_hit_ratio: 0.00, build_task_duration: 790µs, max_distsql_concurrency: 10}                                | index:IndexFullScan_32                                                                                                                                                                                                                                                                                                                                          | 5.94 KB   | N/A   |
|         └─IndexFullScan_32 | 1.00    | 57.00   | 1152    | cop[tikv] | table:TI_O_CREDIT_STOP_OPEN_WP_5, index:TI_O_CREDIT_STOP_OPEN_WP_5_OPEN_TRADE_ID_IDX(OPEN_TRADE_ID) | tikv_task:{proc max:0s, min:0s, avg: 0s, p80:0s, p95:0s, iters:13, tasks:4}, scan_detail: {total_process_keys: 1152, total_process_keys_size: 42624, total_keys: 1156, get_snapshot_time: 678.1µs, rocksdb: {key_skipped_count: 1152, block: {cache_hit_count: 48, read_count: 4, read_byte: 93.9 KB, read_time: 417.7µs}}}  | keep order:false                                                                                                                                                                                                                                                                                                                                                | N/A       | N/A   |

Execution Plan 2:


| id               | estRows | estCost | actRows | task | access object | execution info                        | operator info                                                                                                                                                                                                                                                                                                                                                    | memory  | disk  |
| Insert_1         | 0.00    | 0.00    | 0       | root |               | time:18.1µs, loops:1                  | N/A                                                                                                                                                                                                                                                                                                                                                              | 0 Bytes | N/A   |
| └─Projection_18  | 0.00    | 18.00   | 0       | root |               | time:2.09µs, loops:1, Concurrency:OFF | 8524045369463107->Column#460, 8520080559283353->Column#461, 18586891575->Column#462, 90356341->Column#463, 50->Column#464, 0851->Column#465, 85->Column#466, 1->Column#467, 202312->Column#468, 202402->Column#469, 29700->Column#470, 8524045369463107->Column#471, 8524043485957631->Column#472, 2024-04-19 08:00:06->Column#473, 1->Column#474, 1->Column#475 | 0 Bytes | N/A   |
|   └─TableDual_19 | 0.00    | 0.00    | 0       | root |               | time:740ns, loops:1                   | rows:0                                                                                                                                                                                                                                                                                                                                                           | N/A     | N/A   |

Execution Plan 3:


| id                         | estRows | estCost | actRows | task      | access object                                                                                       | execution info                                                                                                                                                                                                                                                                                                                                                                      | operator info                                                                                                                                                                                                                                                                                                                                                   | memory    | disk  |
| Insert_1                   | 0.00    | 0.00    | 0       | root      |                                                                                                     | time:1.28ms, loops:1, prepare: 1.18ms, insert:98.3µs, lock_keys: {time:962µs, region:2, keys:2, slowest_rpc: {total: 0.001s, region_id: 3281518193, store: 10.172.65.119:20160, tikv_wall_time: 330µs, scan_detail: {get_snapshot_time: 12.5µs, rocksdb: {block: {cache_hit_count: 10, read_count: 1, read_byte: 17.7 KB, read_time: 11.3µs}}}, }, lock_rpc:908.493µs, rpc_count:2} | N/A                                                                                                                                                                                                                                                                                                                                                             | 19.9 KB   | N/A   |
| └─Projection_18            | 1.00    | 27.93   | 1       | root      |                                                                                                     | time:1.08ms, loops:2, Concurrency:OFF                                                                                                                                                                                                                                                                                                                                               | 8524045364062384->Column#460, 8520082061143967->Column#461, 08547020408->Column#462, 90933194->Column#463, 30->Column#464, 0854->Column#465, 85->Column#466, 1->Column#467, 202402->Column#468, 202402->Column#469, 1000->Column#470, 8524045364062384->Column#471, 8524043485957623->Column#472, 2024-04-19 08:00:06->Column#473, 1->Column#474, 1->Column#475 | 136 Bytes | N/A   |
|   └─Limit_19               | 1.00    | 9.32    | 1       | root      |                                                                                                     | time:1.07ms, loops:2                                                                                                                                                                                                                                                                                                                                                                | offset:0, count:1                                                                                                                                                                                                                                                                                                                                               | N/A       | N/A   |
|     └─IndexReader_27       | 1.00    | 9.32    | 1       | root      |                                                                                                     | time:1.06ms, loops:1, cop_task: {num: 1, max: 1.05ms, proc_keys: 1, tot_proc: 593.2µs, tot_wait: 44.6µs, rpc_num: 1, rpc_time: 1.01ms, copr_cache_hit_ratio: 0.00, build_task_duration: 70.7ms, max_distsql_concurrency: 1}                                                                                                                                                         | index:Limit_26                                                                                                                                                                                                                                                                                                                                                  | 268 Bytes | N/A   |
|       └─Limit_26           | 1.00    | 57.00   | 1       | cop[tikv] |                                                                                                     | tikv_task:{time:0s, loops:1}, scan_detail: {total_process_keys: 1, total_process_keys_size: 37, total_keys: 2, get_snapshot_time: 18.6µs, rocksdb: {key_skipped_count: 1, block: {cache_hit_count: 11, read_count: 2, read_byte: 25.8 KB, read_time: 328.2µs}}}                                                                                                                     | offset:0, count:1                                                                                                                                                                                                                                                                                                                                               | N/A       | N/A   |
|         └─IndexFullScan_25 | 1.00    | 57.00   | 1       | cop[tikv] | table:TI_O_CREDIT_STOP_OPEN_WP_5, index:TI_O_CREDIT_STOP_OPEN_WP_5_OPEN_TRADE_ID_IDX(OPEN_TRADE_ID) | tikv_task:{time:0s, loops:1}                                                                                                                                                                                                                                                                                                                                                        | keep order:false                                                                                                                                                                                                                                                                                                                                                | N/A       | N/A   |

Execution plan after manual full analyze table is still incorrect:

+--------------------------------+---------+---------+-----------+-----------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+------+
| id                             | estRows | actRows | task      | access object                                                                                       | execution info                                                                                                                                                                                                                      | operator info                                                                                                                                                                                                                                                                                                                           | memory    | disk |
+--------------------------------+---------+---------+-----------+-----------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+------+
| Insert_1                       | N/A     | 0       | root      |                                                                                                     | time:925.7µs, loops:1, prepare: 806.7µs, insert:119µs, RU:27.480917                                                                                                                                                                 | N/A                                                                                                                                                                                                                                                                                                                                     | 19.9 KB   | N/A  |
| └─Projection_18                | 1.00    | 1       | root      |                                                                                                     | time:690.8µs, loops:2, Concurrency:OFF                                                                                                                                                                                              | 852404532384->Column#460, 85200820967->Column#461, 085408->Column#462, 909394->Column#463, 30->Column#464, 0854->Column#465, 85->Column#466, 1->Column#467, 202402->Column#468, 202402->Column#469, 1000->Column#470, 8524045362384->Column#471, 85240437623->Column#472, 2024-04-19 08:00:06->Column#473, 1->Column#474, 1->Column#475 | 136 Bytes | N/A  |
|   └─Limit_19                   | 1.00    | 1       | root      |                                                                                                     | time:671.1µs, loops:2                                                                                                                                                                                                               | offset:0, count:1                                                                                                                                                                                                                                                                                                                       | N/A       | N/A  |
|     └─IndexReader_26           | 1.00    | 1       | root      |                                                                                                     | time:665.5µs, loops:1, cop_task: {num: 1, max: 691.7µs, proc_keys: 1, tot_proc: 148.1µs, tot_wait: 40.8µs, rpc_num: 1, rpc_time: 661.5µs, copr_cache_hit_ratio: 0.00, build_task_duration: 79.2ms, max_distsql_concurrency: 1}      | index:Limit_25                                                                                                                                                                                                                                                                                                                          | 257 Bytes | N/A  |
|       └─Limit_25               | 1.00    | 1       | cop[tikv] |                                                                                                     | tikv_task:{time:0s, loops:1}, scan_detail: {total_process_keys: 1, total_process_keys_size: 37, total_keys: 2, get_snapshot_time: 13.4µs, rocksdb: {key_skipped_count: 1, block: {cache_hit_count: 14}}}                            | offset:0, count:1                                                                                                                                                                                                                                                                                                                       | N/A       | N/A  |
|         └─IndexFullScan_24     | 1.00    | 1       | cop[tikv] | table:TI_O_CREDIT_STOP_OPEN_WP_5, index:TI_O_CREDIT_STOP_OPEN_WP_5_OPEN_TRADE_ID_IDX(OPEN_TRADE_ID) | tikv_task:{time:0s, loops:1}                                                                                                                                                                                                        | keep order:false                                                                                                                                                                                                                                                                                                                        | N/A       | N/A  |
+--------------------------------+---------+---------+-----------+-----------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+------+
6 rows in set (0.10 sec)

replayer_-vM9Mb0CWyalOSySYHBseg==_1713492621373669629.zip|attachment (1.9 MB)

Questions:

  1. Regardless of how the SQL is written, the correct execution plan should be Execution Plan 2, which directly inserts data using dual. Why are Execution Plans 1 and 3 generated? Before the upgrade, this SQL was working fine.

  2. Execution Plans 1 and 3 use the wrong index TI_O_CREDIT_STOP_OPEN_WP_5_OPEN_TRADE_ID_IDX. Even if the execution plan cannot use dual, the table has an index with the where condition (USER_ID, IS_WP_FINISHED). Collecting statistics does not solve the problem. Even if the correct data type is used for USER_ID in the SQL, the wrong index is still used.

mysql> explain analyze INSERT INTO
    ->   wise_cred.TI_O_CREDIT_STOP_OPEN_WP_5(
    ->     TRADE_ID,
    ->     USER_ID,
    ->     SERIAL_NUMBER,
    ->     PRODUCT_ID,
    ->     NET_TYPE_CODE,
    ->     EPARCHY_CODE,
    ->     PROVINCE_CODE,
    ->     OWE_HALFSTOP_IS_OWE,
    ->     OWE_HALFSTOP_OWE_CYCLE_BEGIN,
    ->     OWE_HALFSTOP_OWE_CYCLE_END,
    ->     OWE_HALFSTOP_OWE_FEE,
    ->     OWE_HALFSTOP_INTRADE_ID,
    ->     OWE_HALFSTOP_TRADE_ID,
    ->     OWE_HALFSTOP_EXEC_TIME,
    ->     OWE_HALFSTOP_PROCESS_TAG,
    ->     IS_HAVE_OWE_HALFSTOP
    ->   )
    -> SELECT
    ->   '852404532384',
    ->   '85200820967',
    ->   '085408',
    ->   '909394',
    ->   '30',
    ->   '0854',
    ->   '85',
    ->   1,
    ->   202402,
    ->   202402,
    ->   '1000',
    ->   '8524045362384',
    ->   '85240437623',
    ->   '2024-04-19 08:00:06',
    ->   '1',
    ->   '1'
    -> FROM
    ->   wise_cred.TI_O_CREDIT_STOP_OPEN_WP_5
    -> WHERE
    ->   NOT EXISTS (
    ->     SELECT
    ->       1
    ->     FROM
    ->       wise_cred.TI_O_CREDIT_STOP_OPEN_WP_5
    ->     WHERE
    ->       USER_ID =
2
| username: zhaokede | Original post link

Try using a hint to force the optimizer to use the specified index.

3
| username: 小龙虾爱大龙虾 | Original post link
  1. This SQL is not written very well. I think execution plan two is the execution plan without inserting data, the not exists condition returned false. Execution plans one and three should be the same, except one has a transaction with an additional union scan. Why check again? Because we need to ensure there is at least one row in the table.
  2. It doesn’t matter which index is used, as long as it can prove there is at least one row of data, and there is also a limit operator.
  3. I think there is no problem with this SQL execution plan.
4
| username: zhaokede | Original post link

You can also take a look at the SQL execution plan for the query part.

5
| username: shigp_TIDBER | Original post link

Please post the execution plan for the select part separately.

6
| username: h5n1 | Original post link
  1. I checked the affected rows, and indeed, there are 0 rows of data for dual. To determine that there are 0 rows of data, it needs to scan the index or table, and it should also show how it is scanned. Why is it displayed as dual? After all, this is not a case where the SQL condition is obviously not met.
  2. For this kind of id column, directly using the corresponding index should be less than performing an index full scan on an unrelated index, right?
7
| username: 小龙虾爱大龙虾 | Original post link
  1. Subquery preprocessing will directly determine the result of your subquery. However, this operation will not be displayed in the execution plan. If the condition is false, your execution plan will directly become dual.
  2. The execution plan also shows the table in your main query statement, which has only one condition and no ID column. Since the query itself does not query the columns of the table, it just uses any index to check if there is data in the table. An index full scan does not necessarily mean that the entire index is scanned because there is a limit operator above. In execution plan 1, it seems that the limit is not pushed down to TiKV.
8
| username: tidb菜鸟一只 | Original post link

How about looking at the execution plan for this query alone?

9
| username: h5n1 | Original post link

This looks normal on its own.

10
| username: h5n1 | Original post link
  1. The logic here is that the subquery preprocessing generates a constant to determine true or false. Is the limit 1 substituted into the subquery during subquery preprocessing? But regardless, it is more appropriate for the subquery to use (USER_ID, IS_WP_FINISHED), right? Using other indexes would at least require finding a matching user_id from the index or scanning through to find a non-matching one.
  2. The link to the other post I sent earlier is about the topn operator not being pushed down to TiKV during unionscan.
11
| username: h5n1 | Original post link

Adding a hint in a subquery is the same.

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

The subquery might be using the index you mentioned, but you can’t see it in the execution plan because it has been preprocessed.

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

The LIMIT 1 cannot be pushed down into the subquery.

14
| username: 小龙虾爱大龙虾 | Original post link
15
| username: h5n1 | Original post link

Execution plan 2 has dual, so it’s not visible. Does that mean execution plans 1 and 3 are not preprocessed? If the subquery is preprocessed and executed first, then the index used would definitely be incorrect.

If limit is not pushed into the subquery and the subquery is preprocessed, then why is the limit in execution plan 3 pushed down to the index scan? Or is it that internally, the not exists + subquery is converted into SQL, resulting in the execution plan?

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

Take a look at the execution plans for the two SQL statements below. Can you figure them out?

SQL1

INSERT INTO
  wise_cred.TI_O_CREDIT_STOP_OPEN_WP_5(
    TRADE_ID,
    USER_ID,.........
  )
SELECT
  '8524364062384',
  '852061143967',
  '0850408',
  '909394',
  '30',
  '0854',
  '85',
  1,
  202402,
  202402,
  '1000',
  '8524045362384',
  '852404357623',
  '2024-04-19 08:00:06',
  '1',
  '1'
FROM
  wise_cred.TI_O_CREDIT_STOP_OPEN_WP_5
WHERE
 true
LIMIT
  1;

SQL2

Replace the `true` in SQL1 with `false`
17
| username: h5n1 | Original post link

This can be understood. The problem is that when determining true and false, which index should be used for optimal performance? It still needs to check whether there is user_id data, right?

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

The execution plan for subquery preprocessing needs to be examined separately.

19
| username: h5n1 | Original post link

There is no problem with the index when the subquery is executed separately, and using a string also results in the correct index.

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

Yes, then there’s no problem. The execution plan for subquery preprocessing is not visible.