This is the 12th day of my participation in the August Text Challenge.More challenges in August
About the author: Wu Kong, 8 years of experience in Internet development and architecture, explains distributed, architecture design, Java core technology with stories. Author of “JVM Performance Optimization Practice” column, open source “Spring Cloud Practice PassJava” project, public account: Wukong chat architecture. This article is available at www.passjava.cn
Hello everyone, I’m Brother Wukong. Today, I will bring you the interview questions of Dfactory:
Question: what about MySQL clustered index and non-clustered index?
Interviewee empty after hearing this question, secretly pleased, this question is simple, without thinking to answer: internal are B+ tree structure.
The interviewer deadpan: what else?
Small empty support hemming and hawing of did not answer come up.
InnoDB storage engine supports the following common indexes: B+ tree index, full-text index, hash index. The most common index is B+ tree index, which can be divided into clustered index and non-clustered index. Non-clustered indexes can also be called secondary indexes, secondary indexes.
The two indexes have something in common: they are both B+ trees inside, highly balanced, and leaf nodes hold all the data.
Difference: The leaf node of the clustered index holds a whole row of information. Clustered index A table can have only one clustered index, but a non-clustered index a table can have multiple clustered indexes. Clustered indexes store records that are physically contiguous, whereas non-clustered indexes are logically contiguous and the physical storage is not contiguous. The data query speed of clustered index is fast, but the data insertion speed is slow. Non-clustered indexes do the opposite. Clustered index range queries fast.
Clustered index: InnoDB storage engine table is an index organization table, table data is stored in the order of primary key, and clustered index is constructed a B+ number according to the primary key of each table. At the same time, the leaf node stores the row record data of the whole table, and the leaf node of clustered index is called data page. Each table can have only one clustered index. Query optimizers tend to use clustered indexes.
Non-clustered index: The leaf node does not contain all the data for the record. The leaf node also contains a bookmark in the index row that tells the InnoDB storage engine where to find the row data corresponding to the index. This bookmark is the clustered index key for the corresponding row data. You can have multiple non-clustered indexes. When using a non-clustered index to find data, the primary key to the primary key index is obtained through a leaf level pointer, and a complete row record is found through the primary key index.
After listening to the small empty, sighed, today is a day by the interviewer.
Next question:
Question 1: Does a non-clustered index count as a return to a table? Question 2: What is the relationship between the union index, index coverage, common index, unique index and the above two indexes?