Questions about total_keys and key_skipped_count in explain analyze?

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

Original topic: explain analyze 中的total_keys和key_skipped_count 的疑问?

| username: Raymond

Dear teachers, I have a question about testing the sbtest1 table generated by sysbench. I executed explain analyze delete from sbtest1 limit 1000 twice and have the following two questions:

  1. After executing the second explain analyze delete from sbtest1 limit 1000, why is the total_keys in the execution plan 3001? According to my understanding, it should be around 2000.
  2. After executing the second explain analyze delete from sbtest1 limit 1000, why did the key_skipped_count in the execution plan become 4000?

According to my understanding, key_skipped_count in RocksDB represents the number of keys encountered while scanning data. How did it become 4000 after just executing delete limit 1000 twice?

mysql> explain analyze delete from sbtest1 limit 1000;
+------------------------------+---------+---------+-----------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------+----------+------+
| id                           | estRows | actRows | task      | access object | execution info                                                                                                                                                                                                                                                                                                                        | operator info                  | memory   | disk |
+------------------------------+---------+---------+-----------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------+----------+------+
| Delete_4                     | N/A     | 0       | root      |               | time:14.3ms, loops:1                                                                                                                                                                                                                                                                                                                  | N/A                            | 247.5 KB | N/A  |
| └─Limit_8                    | 1000.00 | 1000    | root      |               | time:9.89ms, loops:2                                                                                                                                                                                                                                                                                                                  | offset:0, count:1000           | N/A      | N/A  |
|   └─TableReader_12           | 1000.00 | 1000    | root      |               | time:9.88ms, loops:1, cop_task: {num: 3, max: 4.11ms, min: 2.75ms, avg: 3.22ms, p95: 4.11ms, max_proc_keys: 992, p95_proc_keys: 992, tot_proc: 1ms, rpc_num: 3, rpc_time: 9.64ms, copr_cache_hit_ratio: 0.00, distsql_concurrency: 1}                                                                                                 | data:Limit_11                  | 302.7 KB | N/A  |
|     └─Limit_11               | 1000.00 | 1696    | cop[tikv] |               | tikv_task:{proc max:2ms, min:0s, avg: 1.33ms, p80:2ms, p95:2ms, iters:12, tasks:3}, scan_detail: {total_process_keys: 1696, total_process_keys_size: 379904, total_keys: 1699, get_snapshot_time: 832.6µs, rocksdb: {key_skipped_count: 1696, block: {cache_hit_count: 25, read_count: 2, read_byte: 52.2 KB, read_time: 394.1µs}}}   | offset:0, count:1000           | N/A      | N/A  |
|       └─TableFullScan_10     | 1000.00 | 1696    | cop[tikv] | table:sbtest1 | tikv_task:{proc max:2ms, min:0s, avg: 1.33ms, p80:2ms, p95:2ms, iters:12, tasks:3}                                                                                                                                                                                                                                                    | keep order:false, stats:pseudo | N/A      | N/A  |
+------------------------------+---------+---------+-----------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------+----------+------+
5 rows in set (0.10 sec)

mysql> show variables like '%paging%';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| tidb_enable_paging   | ON    |
| tidb_max_paging_size | 50000 |
| tidb_min_paging_size | 128   |
+----------------------+-------+
3 rows in set (0.01 sec)

mysql> set tidb_enable_paging = OFF;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%paging%';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| tidb_enable_paging   | OFF   |
| tidb_max_paging_size | 50000 |
| tidb_min_paging_size | 128   |
+----------------------+-------+
3 rows in set (0.00 sec)

mysql> explain analyze delete from sbtest1 limit 1000;
+------------------------------+---------+---------+-----------+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------+----------+------+
| id                           | estRows | actRows | task      | access object | execution info                                                                                                                                                                                                                                     | operator info                  | memory   | disk |
+------------------------------+---------+---------+-----------+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------+----------+------+
| Delete_4                     | N/A     | 0       | root      |               | time:9ms, loops:1                                                                                                                                                                                                                                  | N/A                            | 220.6 KB | N/A  |
| └─Limit_8                    | 1000.00 | 1000    | root      |               | time:4.68ms, loops:2                                                                                                                                                                                                                               | offset:0, count:1000           | N/A      | N/A  |
|   └─TableReader_12           | 1000.00 | 1000    | root      |               | time:4.68ms, loops:1, cop_task: {num: 1, max: 4.58ms, proc_keys: 1000, tot_proc: 1ms, rpc_num: 1, rpc_time: 4.56ms, copr_cache_hit_ratio: 0.00, distsql_concurrency: 1}                                                                            | data:Limit_11                  | 205.6 KB | N/A  |
|     └─Limit_11               | 1000.00 | 1000    | cop[tikv] |               | tikv_task:{time:2ms, loops:6}, scan_detail: {total_process_keys: 1000, total_process_keys_size: 224000, total_keys: 3001, get_snapshot_time: 781µs, rocksdb: {delete_skipped_count: 1000, key_skipped_count: 4000, block: {cache_hit_count: 14}}}  | offset:0, count:1000           | N/A      | N/A  |
|       └─TableFullScan_10     | 1000.00 | 1000    | cop[tikv] | table:sbtest1 | tikv_task:{time:2ms, loops:6}                                                                                                                                                                                                                      | keep order:false, stats:pseudo | N/A      | N/A  |
+------------------------------+---------+---------+-----------+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------+----------+------+
5 rows in set (0.09 sec)
| username: xfworld | Original post link

For your reference:
delete_skipped_count records the number of such skipped delete operations.
key_skipped_count records the number of such skipped keys.
It needs to scan the entire table’s data, and total_keys records the total number of keys scanned in the process.

delete_skipped_count records those marked for deletion that will be skipped.
key_skipped_count records those that have become invalid (marked for deletion but not yet released) and will be skipped.

The above is for your reference.

| username: redgame | Original post link

First. When performing a scan operation, you may encounter some additional keys, such as index keys or other auxiliary keys, which may exceed expectations.

| username: Kongdom | Original post link

I think this is a multi-version mechanism. When retrieving data, it will first get the timestamp, then find the corresponding version according to the timestamp, and ignore the versions before the timestamp.

| username: Raymond | Original post link

key_skipped_count records the number of keys that have been skipped.

The key_skipped_count records keys that have become invalid (marked for deletion but not yet released) and will be skipped.

When deleting from this table for the first time (no data has been deleted before), the key_skipped_count is 1696. How should this be understood?

mysql> explain analyze delete from sbtest1 limit 1000;
+------------------------------+---------+---------+-----------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------+----------+------+
| id                           | estRows | actRows | task      | access object | execution info                                                                                                                                                                                                                                                                                                                        | operator info                  | memory   | disk |
+------------------------------+---------+---------+-----------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------+----------+------+
| Delete_4                     | N/A     | 0       | root      |               | time:14.3ms, loops:1                                                                                                                                                                                                                                                                                                                  | N/A                            | 247.5 KB | N/A  |
| └─Limit_8                    | 1000.00 | 1000    | root      |               | time:9.89ms, loops:2                                                                                                                                                                                                                                                                                                                  | offset:0, count:1000           | N/A      | N/A  |
|   └─TableReader_12           | 1000.00 | 1000    | root      |               | time:9.88ms, loops:1, cop_task: {num: 3, max: 4.11ms, min: 2.75ms, avg: 3.22ms, p95: 4.11ms, max_proc_keys: 992, p95_proc_keys: 992, tot_proc: 1ms, rpc_num: 3, rpc_time: 9.64ms, copr_cache_hit_ratio: 0.00, distsql_concurrency: 1}                                                                                                 | data:Limit_11                  | 302.7 KB | N/A  |
|     └─Limit_11               | 1000.00 | 1696    | cop[tikv] |               | tikv_task:{proc max:2ms, min:0s, avg: 1.33ms, p80:2ms, p95:2ms, iters:12, tasks:3}, scan_detail: {total_process_keys: 1696, total_process_keys_size: 379904, total_keys: 1699, get_snapshot_time: 832.6µs, rocksdb: {key_skipped_count: 1696, block: {cache_hit_count: 25, read_count: 2, read_byte: 52.2 KB, read_time: 394.1µs}}}   | offset:0, count:1000           | N/A      | N/A  |
|       └─TableFullScan_10     | 1000.00 | 1696    | cop[tikv] | table:sbtest1 | tikv_task:{proc max:2ms, min:0s, avg: 1.33ms, p80:2ms, p95:2ms, iters:12, tasks:3}                                                                                                                                                                                                                                                    | keep order:false, stats:pseudo | N/A      | N/A  |
+------------------------------+---------+---------+-----------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------+----------+------+
5 rows in set (0.10 sec)
| username: 人如其名 | Original post link

key_skipped_count represents the total data scanned by RocksDB for this request (total scanning overhead). The larger this value, the higher the scanning cost of RocksDB.
deleted_skipped_count represents the records that have been marked and written into RocksDB (tombstone state) after the GC time during the scan. It can be understood as invalid data filtered at the RocksDB layer. The larger this value, the more data needs to be merged during compaction.
total_keys is the total number of records returned by RocksDB to TiKV that meet the conditions within the GC range. The larger the difference between this value and total_process_keys, the more data versions have accumulated within the GC time (for example, if you frequently delete data that has not yet been GC’d, it will all be scanned and sent to TiKV).
total_process_keys are the truly valid records after filtering through the MVCC version. The larger this value, the more records are actually needed.

Additionally, I’m not sure why your total_keys: 1699 is greater than key_skipped_count: 1696. Looking forward to an expert’s explanation.

| username: 裤衩儿飞上天 | Original post link

  • Total_keys: Indicates the number of keys scanned by the Coprocessor.
  • Process_keys: Indicates the number of keys processed by the Coprocessor. Compared to total_keys, processed_keys does not include old versions of MVCC. If there is a large difference between processed_keys and total_keys, it indicates that there are many old versions.
  • Rocksdb_delete_skipped_count: The number of deleted keys scanned during RocksDB data reading.
  • Rocksdb_key_skipped_count: The number of deleted (tombstone) keys encountered during RocksDB data scanning.
| username: 裤衩儿飞上天 | Original post link

Not only delete operations generate MVCC historical data, but update operations do as well.

| username: Raymond | Original post link

  1. key_skipped_count is the total data scanned by RocksDB for this request (total scan overhead). The larger this value, the higher the scanning cost of RocksDB.
    Why is this key_skipped_count 4000 when I execute the second delete limit 1000? I deleted 1000 rows the first time and 1000 rows the second time.

  2. deleted_skipped_count is the number of records that were scanned and marked in RocksDB (tombstone state) after the GC time. It can be understood as the invalid data filtered by the RocksDB layer. The larger this value, the more data needs to be merged during compaction.
    When I execute the second delete … limit 1000, GC has not yet occurred at this time.