1. Index Type 1. B+ TreeWhy B+ tree instead of B tree? First, let's look at the structural differences between B-tree and B+ tree. B-tree structure: B+ Tree: You can see:
2. What are the differences between MyISAM and InnoDB's B+ tree index implementations (clustered index and non-clustered index)?First you need to understand clustered indexes and non-clustered indexes. Clustered index: In a clustered index, the leaf pages contain all the data for the row, and the node pages contain the index columns. InnoDB clusters data by primary key. If no primary key is defined, a unique non-empty index column is selected instead. If there is no such index, InnoDB implicitly defines a primary key as the clustered index. Data distribution of clustered index: In a clustered index, in addition to the primary key index, there is also a secondary index. The leaf nodes in the secondary index do not store "row pointers" but primary key values, which are used as "pointers" to the rows. This means that when searching for a row through a secondary index, the storage engine needs to find the leaf node of the secondary index to obtain the corresponding primary key value, and then search for the corresponding row in the clustered index based on this value, which is also called "back to the table". Of course, you can avoid table repetition by using covering indexes or
3. Non-clustered indexThe primary key index and secondary index of a non-clustered index are no different in structure, both of which store "row pointers" pointing to the physical address of the data on the leaf nodes. Primary key index and secondary index of clustered index: Primary key index and secondary index of non-clustered index: 4. Advantages and disadvantages of clustered indexadvantage: Store related data together (for example, group all the user's emails together by user ID), otherwise each data read may cause a disk IO shortcoming: If all data can be stored in memory, sequential access is no longer necessary, and clustered indexes have no advantage. Insertion speed depends on the insertion order. Random insertion can cause page splits and holes. Use OPTIMIZE TABLE to rebuild the table. Every insertion, update, and deletion requires maintenance of index changes, which is very expensive. Secondary indexes may be larger than expected because the primary key columns of the referenced rows are included in the node. 5. Hash IndexHash indexes are implemented based on hash tables. Only queries that exactly match all columns of the index are valid, which means that hash indexes are suitable for equal value queries.
In MySQL, only the
6. Adaptive Hash Index When This is the end of this article about the details of the underlying data structure of MySQL indexes. For more information about the underlying data structure of MySQL indexes, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future! You may also be interested in:
|
>>: Solution for using Baidu share on Https page
mysql permissions and indexes The highest user of...
front end css3,filter can not only achieve the gr...
max_allowed_packet is a parameter in MySQL that i...
1. Use the df command to view the overall disk us...
System environment: centos7.4 1. Check whether th...
1. Download First of all, I would like to recomme...
Table of contents 1. IDEA downloads the docker pl...
Table of contents 1. Direct assignment 2. Shallow...
Table of contents Review of Object.defineProperty...
Redis Introduction Redis is completely open sourc...
Today, the company project needs to configure doc...
Table of contents Preface Implementation ideas Im...
Technical Background Latex is an indispensable to...
The requirement is to pass in the rating data for...
This article shares the specific code of js to ac...