'Is InnoDB index constructed in cache or disk?

I'm trying to understand InnoDB indices, but have some confusion.

Are InnoDB indices (Clustered and Secondary) constructed only in cache or constructed in disk and loaded in cache?

And since Clustered index uses a B+ tree, are the leaf pages in cache or disk? Do they contain actual row data or pointers to row data?

Feel free to refer to any relevant documentation or articles:-)

mysqlindexingmemory-managementinnodbb-tree


Solution 1:[1]

You might like to read Jeremy Cole's series of blog articles about the internals of InnoDB page structure: https://blog.jcole.us/innodb/

That's the best description I've seen for that subject. He also developed some free Ruby scripts with which he can inspect InnoDB data structures.

Here are some brief answers to your specific questions:

InnoDB indexes, both clustered and secondary are stored on disk. They are organized into pages of uniform size. The pages are copied from disk into RAM, called the innodb buffer pool. The pages in the buffer pool are exactly the same as they are on disk. Indexes do contain leaf nodes both in RAM and on disk.

Primary indexes have leaf nodes that are basically one row; this includes all the columns for the respective row, except for long VARCHAR/BLOB/TEXT content, which might overflow to other pages.

Secondary indexes have leaf nodes that include the data value, and also the primary key values of rows where the indexed value is found.

So a lookup using a secondary index has to search two B+tree structures: first the tree for the secondary index, which locates the primary key value(s). Then subsequently it searches the tree for the primary or clustered index. This may seem expensive, but it can be mitigated by the adaptive hash index and the change buffer.

Solution 2:[2]

So a lookup using a secondary index has to search two B+tree structures: first the tree for the secondary index, which locates the primary key value(s). Then subsequently it searches the tree for the primary or clustered index. This may seem expensive, but it can be mitigated by the adaptive hash index and the change buffer.

Just to complement, i believe that such behavior is not always true.

If query search only for columns that are covered by secondary indexes, such query don't have to lookup primary index nor clustered index to find data.

Such behavior could be find here under Secondary indexes section. https://medium.com/@genchilu/a-brief-introduction-to-cluster-index-and-secondary-index-in-innodb-9b8874d4da6a

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Bill Karwin
Solution 2 Vitor C

Related Questions