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

Question

As we all know, innodb only stores the primary key value in the secondary index, which means that we need to traverse the clustered index B+ tree again to get the row records. Why not just store the row pointers in the secondary index to reduce the extra lookup work?

P粉469090753 · Answer

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.