What is the reason for the indexing mechanism of MySQL?
What is the reason for the indexing mechanism of MySQL? many novices are not very clear about it. In order to help you solve this problem, the following editor will explain it in detail. People with this need can come and learn. I hope you can get something.
On this basis, I also posted several articles from Jian Chaoyang's blog about the different index structures of different storage engines in mysql:
1.Innodb
As the most widely used transactional storage engine in MySQL, Innodb is not only different from other storage engines in implementing data version control, but also its data structure is stored in a very characteristic way.
It can be said that the data of each Innodb table is stored in a B-Tree structure, and the data of the table and the primary key (Primary Key) together form an index structure, which is what we often call the Clustered Primary Key of Innodb. In this Clustered Primary Key, Leaf Nodes is actually the actual table record, and the index information we normally understand is all on Branch Nodes.
All indexes except Clustered Primary Key are called Secondary Index in Innodb. Secondary Index is almost the same as a normal B-Tree index, except that all Leaf Nodes in Secondary Index contain the primary key information that points to the data record, rather than directly pointing to the location information of the data record.
Therefore, in Innodb, if the primary key value takes up a large storage space, it will directly affect the physical space needed to store the Innodb table, and it will also directly affect the query performance of Innodb.
2. MyISAM storage engine
I have written an article about "understanding Innodb Index structure-Innodb Index Structure" before, and this time I will analyze the basic storage structure of MyISAM storage engine index.
In terms of the basic storage data structure of the index, whether the index of MyISAM is Primary Key or ordinary Index, the storage structure is basically the same, the basic structure is Balance Tree (abbreviated as B-Tree), and all the key value details and row "pointer" information are stored on the Leaf Nodes of B-Tree. This basic data structure is basically the same as other MySQL storage engines such as Innodb. However, the index of MyISAM is not as different from the data stored in Primary Key and Secondary Index as the Innodb storage engine. In the MyISAM storage engine, the main difference between Primary Key and other ordinary Index is that the index key of Primary Key needs to satisfy a unique value that is not empty. Another difference is that there is a difference between every ordinary index, that is, the order of key values of the whole index tree is not the same.
Because the storage of data rows in MyISAM storage engine is divided into fixed length and dynamic length, there are also two ways to locate the information needed to locate a row of data in the data file of MyISAM storage engine. One is to locate the rows of fixed-length table data directly through the line number (row number), and the other is to locate the rows of dynamic length table data through some other relative file location identification information. Here, let's call the two methods RID (Row ID).
Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.
