2014 Alibaba intern interview questions how MySQL achieves Indexing

This is a question on the 2014 Alibaba intern Beijing site: in MySQL, indexes belong to the storage engine level concept, and different storage engines implement indexes differently, for example

This is a question on the 2014 Alibaba intern Beijing site: in MySQL, indexes belong to the storage engine level concept, and different storage engines implement indexes differently, for example

This is an exam for 2014 Alibaba intern on the Beijing site:

In MySQL, indexes belong to the storage engine level. Different storage engines implement indexes differently, such as MyISAM and InnoDB Storage engines.

MyISAM index implementation:

The MyISAM storage engine uses B + Tree as the index structure. The data domain of the leaf node stores the data record address. The index method of MyISAM is also called "non-clustered". The reason for this is to distinguish it from the clustered index of InnoDB.

InnoDB Index implementation:

Although InnoDB uses B + Tree as the index structure, the implementation method is different from that of MyISAM.

The first major difference is that the InnoDB data file itself is an index file.

The second difference from the MyISAM index is that InnoDB's secondary index data domain stores the value of the primary key of the corresponding record rather than the address. Clustered indexes make the search by primary key very efficient. However, secondary index search requires two indexes: first, secondary indexes are retrieved to obtain the primary key, then, use the primary key to search for the record in the primary index.

In fact, the implementation of database indexes can adopt the data structure of the red and black trees and the B-Tree trees.

But why is B + Tree actually used?

This should begin with computer storage principles and operating system knowledge. Because data tables have large indexes and cannot be stored in memory, they are stored in disks as files. Therefore, I/O operations are required when querying data. The goal of efficient query is to reduce the number of I/O operations. One I/O generally reads a page (usually 4 k) of data (Locality Principle ). In this way, every time a new node is applied for in the B-tree, the page size is applied. That is to say, I/o can read data from one node (including many keys) at a time. In the red-black tree structure, physically adjacent nodes are not necessarily adjacent. That is to say, reading the same data requires multiple I/O times. Therefore, B-tree is more efficient.

So why did we finally choose B + tree?

Because the nodes in the B + tree remove the data domain, they can have a higher degree of output, that is, a node can store more internal nodes, so the I/O efficiency is higher.

Understanding the index implementation methods of different storage engines is very helpful for correct use and optimization of indexes. For example, after knowing the index Implementation of InnoDB, it is easy to understand why it is not recommended to use too long fields as the primary key, because all secondary indexes reference the primary index, too long primary index will make the secondary index too large. For example, it is not a good idea to use non-monotonous fields as the primary key in InnoDB, because the InnoDB data file itself is a B + Tree, non-monotonous primary keys will cause frequent split and adjustment of data files to maintain the features of B + Tree during the insertion of new records, which is very inefficient, using an auto-increment field as the primary key is a good choice.

Clustered index and non-clustered index:

InnoDB is a clustered index because its leaf node of the B + tree contains a complete data record. The leaf node of the MyISAM Method B + tree only stores the data address, so it is called a non-clustered index.

Index Usage Policy and Optimization

MySQL optimization mainly includes schema optimization and Query optimization ).