分区表：主键更新，执行计划解析未走主键索引

[TiDBer_HLLbLzvt]

线上环境

TiDB 5.4

问题及影响：update语句通过主键更新时，explain显示走主键索引，dashboard执行计划却是全表扫描，性能很差

表结构：

CREATE TABLE detail (

a bigint(20) NOT NULL DEFAULT ‘0’ COMMENT ‘’,

b datetime NOT NULL DEFAULT ‘’,

c datetime NOT NULL DEFAULT ‘’,

d varchar(100) NOT NULL DEFAULT ‘’ COMMENT ‘’,

e bigint(20) DEFAULT NULL COMMENT ‘’,

f tinyint(4) NOT NULL DEFAULT ‘0’ COMMENT ‘’,

g varchar(100) NOT NULL DEFAULT ‘’ COMMENT ‘’,

…

) PRIMARY KEY (d,b,f,c,a,g) /*T![clustered_index] CLUSTERED */,

UNIQUE KEY index_1 (a,g,b)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT=‘’

PARTITION BY RANGE (TO_DAYS(b))

( PARTITION p26 VALUES LESS THAN (739282),

PARTITION p27 VALUES LESS THAN (739311),

PARTITION p28 VALUES LESS THAN (739342),

PARTITION p29 VALUES LESS THAN (739372),

PARTITION p30 VALUES LESS THAN (739403),

PARTITION p31 VALUES LESS THAN (739433),

PARTITION p32 VALUES LESS THAN (739464),

PARTITION p33 VALUES LESS THAN (739495),

PARTITION p34 VALUES LESS THAN (739525),

PARTITION p35 VALUES LESS THAN (739556),

PARTITION p36 VALUES LESS THAN (739586),

PARTITION p37 VALUES LESS THAN (739617),

PARTITION p38 VALUES LESS THAN (739648),

PARTITION p39 VALUES LESS THAN (739676)) |

执行SQL：

update detail set status=3, modified=now() where d=‘xxxxx’ and b=‘2024-06-14 14:13:58’ and f=1 and c=‘2024-05-29 07:09:17’ and a=12312432424 and g =‘xxx’ and yn=1 and status!=2 and status!=3;

explain

dashboard

[Kamner]

执行计划贴全一点吧

[TiDBer_HLLbLzvt]

抱歉，不能再截了，再截全一些就涉及表的详细信息了，这个表比较敏感，公司控制比较严格

[xfworld (魔幻之翼)]

查下健康度， estRows 都是 0 …

如果是主键获取数据，应该是 batchPointGet 和 PointGet ，不会是算子下推

d ,b ,f ,c ,a ,g 联合主键？
最好确认下，贴出来的执行计划是 TableRangeScan…

[zhaokede]

算子下推了。
已有信息还真不好分析为什么没有使用PK

[ziptoam (Ti D Ber D1q T Ff Sk)]

是不是检查一下索引

[h5n1]

执行计划再全点，敏感信息打码

[forever]

看着不太对哎，这么多列的聚簇表吗

[FutureDB]

dashboard贴出来的执行计划是TableRangeScan，并不是全表扫描，注意主键的范围查询显示的算子就是TableRangeScan。例如：

MySQL [(none)]> explain analyze select * from bookshop.books where id between 500000 and 5005500;
+------------------------+---------+---------+-----------+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------+----------+------+
| id                     | estRows | actRows | task      | access object | execution info                                                                                                                                                                                                                                                              | operator info                            | memory   | disk |
+------------------------+---------+---------+-----------+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------+----------+------+
| TableReader_6          | 1247.96 | 1048    | root      |               | time:7.47ms, loops:3, RU:3.615450, cop_task: {num: 1, max: 7.33ms, proc_keys: 1048, tot_proc: 5.27ms, tot_wait: 47.3µs, rpc_num: 1, rpc_time: 7.3ms, copr_cache_hit_ratio: 0.00, build_task_duration: 7.35µs, max_distsql_concurrency: 1}                                   | data:TableRangeScan_5                    | 120.8 KB | N/A  |
| └─TableRangeScan_5     | 1247.96 | 1048    | cop[tikv] | table:books   | tikv_task:{time:5ms, loops:6}, scan_detail: {total_process_keys: 1048, total_process_keys_size: 90723, total_keys: 1049, get_snapshot_time: 21.5µs, rocksdb: {key_skipped_count: 1048, block: {cache_hit_count: 1, read_count: 4, read_byte: 54.4 KB, read_time: 2.69ms}}}  | range:[500000,5005500], keep order:false | N/A      | N/A  |
+------------------------+---------+---------+-----------+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------+----------+------+
2 rows in set (0.02 sec)

MySQL [(none)]> explain analyze select id from bookshop.books where id between 500000 and 5005500;
+------------------------+---------+---------+-----------+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------+---------+------+
| id                     | estRows | actRows | task      | access object | execution info                                                                                                                                                                                                                                 | operator info                            | memory  | disk |
+------------------------+---------+---------+-----------+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------+---------+------+
| TableReader_6          | 1247.96 | 1048    | root      |               | time:1.64ms, loops:3, RU:1.044381, cop_task: {num: 1, max: 1.56ms, proc_keys: 1048, tot_proc: 412.9µs, tot_wait: 46.9µs, rpc_num: 1, rpc_time: 1.54ms, copr_cache_hit_ratio: 0.00, build_task_duration: 7.33µs, max_distsql_concurrency: 1}    | data:TableRangeScan_5                    | 8.50 KB | N/A  |
| └─TableRangeScan_5     | 1247.96 | 1048    | cop[tikv] | table:books   | tikv_task:{time:0s, loops:6}, scan_detail: {total_process_keys: 1048, total_process_keys_size: 28296, total_keys: 1049, get_snapshot_time: 22.4µs, rocksdb: {key_skipped_count: 1048, block: {cache_hit_count: 5}}}                            | range:[500000,5005500], keep order:false | N/A     | N/A  |
+------------------------+---------+---------+-----------+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------+---------+------+
2 rows in set (0.00 sec)

MySQL [(none)]> show create table bookshop.books\G;
*************************** 1. row ***************************
       Table: books
Create Table: CREATE TABLE `books` (
  `id` bigint(20) NOT NULL,
  `title` varchar(100) NOT NULL,
  `type` enum('Magazine','Novel','Life','Arts','Comics','Education & Reference','Humanities & Social Sciences','Science & Technology','Kids','Sports') NOT NULL,
  `published_at` datetime NOT NULL,
  `stock` int(11) DEFAULT '0',
  `price` decimal(15,2) DEFAULT '0.0',
  PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.00 sec)

[dba-kit (张天师)]

可以贴一下下面的执行时间和Cop读取的相关信息，这里是肯定没有业务数据信息的，也能大概看出来时间是消耗在哪里了。
还有就是性能差具体是什么量级的差？ms？s？还是分钟级？

[Kongdom]

执行计划里有没有 pseudo这个关键字？如果有代表需要重新收集统计信息

[TiDBer_HLLbLzvt]

是的，聚簇表

[TiDBer_HLLbLzvt]

是滴，您的描述更准去，是主键的范围查询；最好应该走Point_Get这个应该比范围查询快很多；另外不能理解的地方是，我用客户端去查询的时候解析的执行计划就是Point_Get

[TiDBer_HLLbLzvt]

没有这个关键字

[TiDBer_HLLbLzvt]

都是ms级的，
sql分析里的

慢日志里的sql分析

[tidb菜鸟一只 (小菜一颗)]

你程序里面的sql应该并没有所有的PRIMARY KEY吧，所以才会走到tablerangescan

[TiDBer_HLLbLzvt]

包含全部了，把执行的sql拿出来，用客户端执行就可以走主键索引

[h5n1]

@TiDBer_HLLbLzvt

都是ms级的，

聚簇索引tablerangescan 就是走的主键，tidb explain 和dashboar的执行计划展示其实是有差异的，dashboard里的展示大概是把某种存储下来的数据格式解析然后再套一些关键字，类似是这样的，太标准的描述我也说不上来了，之前有这样的帖子

[tidb菜鸟一只 (小菜一颗)]

实际上tablerangescan算是一种多条数据的point_get，可能dashboard将这种单条point_get和多条的统一展示成了tablerangescan，而不再分类展示了，你可以测试一条单字段的主键表，主键字段=一条的时候和in几条的时候，执行计划就会分别是point_get和tablerangescan。