Mysql-clustered index

Overall overview

1.Innodb’s primary index file directly stores the row of data, which is called a clustered index. The secondary index points to a reference to the primary key

2.In myisam, the primary Indexes and secondary indexes both point to physical rows (disk locations)

Haha, I said two words that I didn’t seem to understand.

one. Popular explanation

We can understand it this way:

Clustered index (innodb): The leaf node of the index is the data node, and there is actual data underneath.

Non-clustered index (myisam): The leaf node is still an index node, with a pointer pointing to the corresponding data block.

Explain with pictures:

I saw it, the types under different engines are different.

The main difference is whether there are data blocks under the leaf nodes.

(If there is no primary key, he will create one..innodb is organized by clustered index)

2. Advantages and disadvantages:

Split problem:

Since it is a tree structure, leaf nodes may be split

Then the problem comes,

For non-clustered index (myisam), the physical row address stored under the node has small content, is cached in memory, and splits quickly.

For clustered index (innodb), this problem is more serious. This "row data" is stored under the node.

Because there are data files under the node, the splitting of the node will be slower. Innodb's For the primary key, try to use integer and incremental integer. If the primary key data is irregular, it will be split when stored in the leaf node. The splitting process must be re-divided with "row data", and myisam only records one address. As long as the Just split the address and replace it.

For example:

For example, splitting is like a moving process. For clustered index (innodb), moving really requires splitting and moving all the data in the home. With the clustered index (mysiam), it is easy to move the house number of the recorded home, but the data it points to does not need to be moved.

three. Test demonstration:

Under the innodb engine, insert 1000 pieces of data in two ways: regular and irregular.

After testing, the first insertion in the form of increasing primary key order took 37 seconds; the second insertion in an out-of-order order took 42 seconds, a full 5 seconds slower. The predicted reason is that out-of-order insertion needs to be performed The splitting of leaf nodes takes time to move, which means that the time difference is between the splitting of nodes and the moving of pages; while sequential insertion rarely causes node splitting.

Mapping conclusion:

1. Use the mysiam engine for read operations

2. Select monotonic data as the index, which will be faster

More: Mysql-Index Summary

Verify it below:

show variables like "%innodb%"

show status;

You will find that there is a field->innodb_pages_written (how many pages have been written in this field)

When writing randomly, The number of times the page is written is higher than that of sequential writing. This is because of splitting and moving, so the number of writes is greater and the time is spent more, which also explains why there is a 5-second event difference between the two.

MyISAM:

This is the default type, which is based on the traditional ISAM type. ISAM is Indexed Sequential Access Method (indexed sequential access method) Abbreviation for, it is a standard method of storing records and files. Compared with other storage engines, MyISAM has most of the tools to check and repair tables. MyISAM tables can be compressed, and they support full-text search. They are not transaction-safe, and they do not support foreign keys. If things are rolled back, it will cause an incomplete rollback and is not atomic. If you execute a large number of SELECT, MyISAM is a better choice.

InnoDB:

This type is transaction safe. It has the same characteristics as the BDB type, they also support foreign keys. InnoDB tables are very fast Fast. It has richer features than BDB, so if you need If you want a transaction-safe storage engine, it is recommended to use it. If your data performs a large number of INSERT or UPDATE, for performance reasons, you should use the InnoDB table

The above is the content of Mysql-clustered index, For more related content, please pay attention to the PHP Chinese website (m.sbmmt.com)!