Why does the Key of a non-unique secondary index include RowID?

Translated
1

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

Original topic: 非唯一二级索引的 Key 中为什么包括 RowID？

| username: KaiNiao

[TiDB Usage Environment] Test
[TiDB Version] 6.5.0
[Reproduction Path] 3.1 表数据与 Key-Value 的映射关系 | tidb-in-action
[Encountered Issues: Phenomenon and Impact]
The document shows:
For a normal secondary index that does not need to meet uniqueness constraints, one key value may correspond to multiple rows. We need to query the corresponding RowID based on the key value range. Therefore, encode it into (Key, Value) key-value pairs according to the following rules:

Key: tablePrefix{TableID}_indexPrefixSep{IndexID}indexedColumnsValue{RowID}
Value: null

There are several questions:

  1. What does it mean to query the corresponding RowID based on the key value range?
  2. indexedColumnsValue does not have curly braces {}, so does the key not include the actual value of the index column?
  3. Why is the Value of a non-unique secondary index null, and how is the index used? Do we extract the RowID from the key and then go back to the table to get the entire row of data?

[Resource Configuration]

image
image1287×541 46.1 KB

[Attachment: Screenshot/Log/Monitoring]

2
| username: h5n1 | Original post link
  1. Find the corresponding rowid based on the index key value.
  2. The index contains the column values, mapping the column values to the row addresses.
  3. For non-unique secondary indexes, if the key only uses the index column values, duplicate values will cause duplicate keys. Therefore, the rowid is added to the key, and the key’s value does not need to store any values. When used, the rowid is directly extracted from the key to access the table.
3
| username: KaiNiao | Original post link

Thank you, boss.

4
| username: system | Original post link

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.