Why doesn't innodb store row pointer in secondary/non-clustered index?

Question

As we know, innodb stores only primary key value in its secondary index, which means we need to traverse the clustered index B+ tree again to fetch the row record.

Why not just store the row pointer in secondary index to reduce the extra finding work?

Answer 1

There is no "row pointer". The columns of the PRMARY KEY serve the function of locating the row -- in the data's BTree.

Sure, looking up via the PK is arguably slower than a "row pointer". But Updates, Deletes, block splits, etc., automatically handled. (Cf Bill's Comment.) This keeps the code simpler. And, in some situations, faster.

A trivial example of faster: Given

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

SELECT id FROM tbl WHERE foo = 123;

In this example, the index's BTree has the complete answer; no need to reach into the data's BTree. Hence, the index is called "covering" for this SELECT.