Why doesn't innodb store row pointers in secondary/non-clustered indexes?

Question

As we all know, innodb only stores primary key values ​​in the auxiliary index, which means that we need to traverse the clustered index B tree again to obtain row records.

Why not store row pointers directly in the secondary index to reduce extra lookup work?

Answer 1

There is no "row pointer". The columns of PRMARY KEY have the functionality to locate rows in the BTree of the data.

Of course, lookup by PK is arguably slower than "row pointer". But updates, deletions, block splits, etc. are all handled automatically. (See Bill's comment.) This makes the code simpler. And, in some cases, faster.

Faster simple example: Given

PRIMARY KEY(id),
INDEX(foo)   -- effectively (foo, id), as discussed above

SELECT id FROM tbl WHERE foo = 123;

In this example, the indexed BTree has the complete answer; no need to access the data BTree. Therefore, the index is called the "cover" of this SELECT.