0

So not sure If I am confused or not, but based on these websites :

https://docs.oracle.com/cd/E17952_01/mysql-5.1-en/innodb-table-and-index.html

http://www.mysqltutorial.org/mysql-index/mysql-clustered-index/

Every InnoDB table has a special index called the clustered index where the data for the rows is stored.

the actual table data is stored in the clustered Index

but i don't understand, i thought indexing was a key-pointer pair stored in a tree for faster lookups, and doesn't store the actual data hence the name Index, like a book index.

if the entire data is in the index then why call it Index?! and wouldn't this slow down a lot of things?

if the actual table is in a b+tree index then just adding a simple tuple or deleting one is logn, is this true or am i mistaking something here?

Considering .IBD files contain the actual data in the tables and .frm files are some sort of metadata (correct me If Im wrong), then which one of these is the clustered index aka the actual table?

and i couldn't find anything useful regarding this in their own documentation.

2

Let's approach it from two other perspectives...

  • The data needs to be stored somehow. Perhaps unordered, perhaps based on a ROWNUM, or InnoDB's approach of ordering it according to the PRIMARY KEY.

  • In InnoDB the data structure and an INDEX structure are virtually identical -- both are BTrees of 16KB blocks with the 'items' ordered according to some KEY. (I suggest that the documentation has confused you because it is hinting at the symmetry without enough background.)

Ordering the data according to one of the indexes (the PK) is actually a performance advantage for some queries. In particular, if you want to look up a row by the PK, you can't get any faster. If you want to scan a subset of the rows and do it in PK, order, you can do it any faster.

I assume you understand a BTree? (See Wikipedia.) InnoDB actually uses a B+Tree, very similar to a BTree, but adds links to make range scans more efficient.

Aside from nodes arranged in a tree, what is in the "leaf" nodes?

For the data / PRIMARY KEY, the leaf nodes contain all the columns of each row. This is what is meant by "clustered".

For a secondary key (any index other than the PK), the leaf nodes contain a copy of the columns of the PK. So, to look up a row via a secondary key, first you dig into its BTree to find the PK of the actual row, then you dig into the data to find the row.

One optimization is to use a "covering" index. This is when all the columns needed anywhere in a SELECT are in a single index. Guess what? Now a Secondary index acts like a "table". You don't need to bounce over to the data/PK BTree.

Each BTree for one table has its nodes stored in the .ibd file (assuming innodb_file_per_table=ON). Each BTree is separate, aside from the PK linking. (Think of ibdata1 and "tablespaces" are collections of tables thrown into a single disk file.)

You mentioned "pointer". Mostly InnoDB gets away from pointers. In the PK case, you arrive at the data; no need for a "pointer". In the case of a secondary key, the copy of the PK serves as the "pointer".

As a Rule of Thumb, the nodes of either type of BTree have about 100 subnodes. This implies that the BTree for a million-row table or index is about 3 levels deep. (Think "log-base-100".)

Since fetching blocks is far more costly than rummaging through a block, counting disk hits is the main performance metric in an I/O-bound InnoDB table.

(MyISAM is radically different.)

(From comments:)

An InnoDB with just a PRIMARY KEY contains a single BTree. The data is sorted according to the PK. Lookups by PK are optimal. That is, 'entire table and its data is in the cluster index structure'.

Inserting into such a table has multiple situations:

If you are 'randomly' inserting into the table, then the operation is technically Order(log n), such as 3 for a million-row table. For all practical purposes, you can pretty much ignore drilling down the BTree.

A typical case is having id INT AUTO_INCREMENT PRIMARY KEY. In this case, inserts are (normally) at the 'end' of the table. (I do not happen to know if there is any optimization to avoid the repeated drill-down, but since this is a very common situation, I expect the developers have done a good job.)

Is an insert-heavy table inefficient? Not really. INSERTs have to go somewhere. Granted, the typical table is read more than it is written, but still, "don't worry".

If you are doing thousands of INSERTs per second, there are various techniques to speed things up. However, they focus on transactions, parsing, scattering, etc, not on the structure of the PRIMARY KEY. One notable exception: if the PK is a UUID, and the table is huge, you will be I/O-bound and probably have big problems.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.