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?
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
.