Partitioned Table: Primary Key Update, Execution Plan Analysis Does Not Use Primary Key Index

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

Original topic: 分区表:主键更新,执行计划解析未走主键索引

| username: TiDBer_HLLbLzvt

Online Environment

TiDB 5.4

Issue and Impact: When using the update statement to update via the primary key, explain shows it uses the primary key index, but the dashboard execution plan shows a full table scan, resulting in poor performance.

Table Structure:

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)
);

Executing 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:

| username: Kamner | Original post link

Please provide a more complete execution plan.

| username: TiDBer_HLLbLzvt | Original post link

Sorry, I can’t provide more details. If I include more, it will involve detailed information about the table, which is quite sensitive. The company has strict controls over this.

| username: xfworld | Original post link

Check the health status, estRows are all 0…

If it’s fetching data by primary key, it should be batchPointGet and PointGet, not operator pushdown.

Is d, b, f, c, a, g a composite primary key?
It’s best to confirm, the execution plan posted is TableRangeScan…

| username: zhaokede | Original post link

The operator has been pushed down.
With the existing information, it’s really difficult to analyze why the PK was not used.

| username: ziptoam | Original post link

Maybe check the index.

| username: h5n1 | Original post link

The execution plan is more comprehensive, with sensitive information redacted.

| username: forever | Original post link

It doesn’t look right, is there really a clustered table with so many columns?

| username: FutureDB | Original post link

The execution plan posted on the dashboard is TableRangeScan, not a full table scan. Note that the operator displayed for range queries on the primary key is TableRangeScan. For example:

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)
| username: dba-kit | Original post link

Could you please provide the execution time and related information about Cop reads? There is definitely no business data information here, and we can roughly see where the time is being consumed. Also, what is the specific magnitude of the performance difference? Milliseconds? Seconds? Or minutes?

| username: Kongdom | Original post link

Is there a pseudo keyword in the execution plan? If there is, it means that statistics need to be re-collected.

| username: TiDBer_HLLbLzvt | Original post link

Yes, clustered tables.

| username: TiDBer_HLLbLzvt | Original post link

Yes, your description is more accurate; it is a range query for the primary key. Ideally, it should use Point_Get, which should be much faster than a range query. Another thing I don’t understand is that when I use the client to query, the execution plan parsed is Point_Get.

| username: TiDBer_HLLbLzvt | Original post link

There is no such keyword.

| username: TiDBer_HLLbLzvt | Original post link

Both are in milliseconds,
In SQL analysis

SQL analysis in slow logs

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

Your program’s SQL probably doesn’t have all the PRIMARY KEYs, which is why it ends up using TableRangeScan.

| username: TiDBer_HLLbLzvt | Original post link

It includes everything. Take out the executed SQL and execute it with the client to use the primary key index.

| username: h5n1 | Original post link

Clustered index tablerangescan uses the primary key. There are actually differences between the execution plans shown in TiDB explain and the dashboard. The display in the dashboard roughly parses some stored data formats and then adds some keywords. It’s something like that. I can’t give a very standard description, but there were posts like this before.

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

In fact, tablerangescan is a kind of multi-row point_get. It is possible that the dashboard has unified the display of single-row point_get and multi-row into tablerangescan, and no longer categorizes them separately. You can test a single-field primary key table, where the execution plan will be point_get when the primary key field equals one row, and tablerangescan when it is in multiple rows.