表结构
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
目前产生了3个执行计划
执行计划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 |
执行计划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 |
执行计划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 |
手工全量analyze table后的执行计划，仍然不正确：
+--------------------------------+---------+---------+-----------+-----------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+------+
| 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)
问题：
1、 不考虑SQL写的怎么样.，这个SQL select的常量值插入，执行计划2 生成dual 直接插入数据才是正常的，为什么还会产生执行计划1和3？ 升级前这个sql是没问题的
2、执行计划1和3 都走了错误的索引TI_O_CREDIT_STOP_OPEN_WP_5_OPEN_TRADE_ID_IDX ，即便是不能走dual的执行计划，而表中有where条件(USER_ID,IS_WP_FINISHED)的索引的，收集统计信息也不能解决问题， 即便SQL中的user_id使用正确数据类型 也是走的错误索引。
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 = 8520082061967
-> AND IS_WP_FINISHED = '0'
-> )
-> LIMIT
-> 1;
+--------------------------------+---------+---------+-----------+-----------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+--------------------------------+---------+---------+-----------+-----------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+------+
| Insert_1 | N/A | 0 | root | | time:958.3µs, loops:1, prepare: 845.3µs, insert:113µs, RU:27.213560 | N/A | 19.9 KB | N/A |
| └─Projection_18 | 1.00 | 1 | root | | time:728.9µ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:713.6µs, loops:2 | offset:0, count:1 | N/A | N/A |
| └─IndexReader_26 | 1.00 | 1 | root | | time:707.8µs, loops:1, cop_task: {num: 1, max: 711µs, proc_keys: 1, tot_proc: 227.5µs, tot_wait: 36.2µs, rpc_num: 1, rpc_time: 681.2µs, copr_cache_hit_ratio: 0.00, build_task_duration: 113.1ms, max_distsql_concurrency: 1} | index:Limit_25 | 265 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: 12µs, rocksdb: {key_skipped_count: 1, block: {cache_hit_count: 13, read_count: 1, read_byte: 7.69 KB, read_time: 10.2µs}}} | 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.12 sec)
Records: 1 Duplicates: 0 Warnings: 0
3、执行计划1和3 都走了错误的索引TI_O_CREDIT_STOP_OPEN_WP_5_OPEN_TRADE_ID_IDX ，2个执行计划的差异是 执行计划1中包含了UnionScan 是不是还是下面这个问题没有修复
