2014 Ali intern face question--mysql How to implement the index

This is the two faces of the 2014 Beijing station. Ali intern problems are torn together:

in the MySQL the. Indexes are the concept of storage engine level, and different storage engines implement indexes differently, such as the MyISAM and InnoDB storage engines.

MyISAM Index Implementation:

MyISAM Storage Engine uses B+tree as the index structure, the leaf node Data The domain holds the address of the data record.

MyISAM The index is also called " non-Ju set " the. The reason for this is to differentiate it from the InnoDB 's Ju set index.

InnoDB Index Implementation:

despite InnoDB also use B+tree as an index structure, but the details are implemented in the same way MyISAM very different.

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

the second one MyISAM the different indexes are: the secondary index of the InnoDB data field stores the value of the corresponding record primary key instead of the address. Ju Set Index this way makes search by primary key very efficient, but secondary index search needs to retrieve two times index: first retrieve secondary index to get primary key. The primary key is then retrieved from the primary index to get the record.

in fact, the implementation of a database index can be implemented with a red-black tree. b-tree tree data structure.

But why is it actually used B+tree it?

This should start with the computer storage principle and operating system-related knowledge.

Because the index of the data table is relatively large. Cannot reside in memory, so it is stored as a file on disk. Therefore, I/O operations are required when querying data .

The goal of high-efficiency queries is fewer I/O times.

One I/O typically reads a page (typically 4k) of data ( Local principle ).

So, in the b - tree, whenever a new node is requested, it is applied by the size of the page. This means that one time I/O can read data from one node (including very many keys). In the structure of red and black trees. Logically adjacent nodes are physically not necessarily contiguous, meaning that reading the same data requires multiple I/O.

So choosing B -tree is more efficient.

then why did you finally pick it? B + Where's the tree?

because B + the nodes in the tree are removed. Data so that a node can store a lot of other internal nodes, so it can have a greater degree of I/O Higher efficiency.

Understanding how indexes are implemented for different storage engines can be very helpful for proper use and optimization of indexes, such as knowing InnoDB after the implementation of the index, it is very easy to understand why it is not recommended to use a long field as the primary key, since all secondary indexes refer to the primary index. A long primary index can cause the secondary index to become too large. For example, using non-monotonic fields as primary keys is not a good idea in InnoDB. Since the InnoDB data file itself is a b+tree, a non-monotonic primary key causes the data file to be inserted in order to maintain the b+tree when inserting a new record characteristics and frequent split adjustment, very inefficient, and using the self-increment field as the primary key is a very good choice.

The index of the Ju-set index and the non-Ju set:

InnoDB is the Ju set index, because of its B + The tree's leaf nodes include a complete data record.

The leaf node of the MyISAM-B + Tree is simply the address where the data is stored. Therefore, it is called non-Ju set index.

Index usage policy and optimization

MySQL optimization is mainly divided into structural optimization ( Scheme Optimization ) and query optimization ( Query Optimization ). See this article for details:

the The data structure and algorithm principle behind MySQL index "

Note: Poly, are replaced with Ju, Ju set unexpectedly is a sensitive word. It's too much pit. Find a way to crack

Copyright notice: This article blog original article. Blogs, without consent, may not be reproduced.

2014 Ali intern face question--mysql How to implement the index