Hash index and BTree index, and Hash index BTree Index

Source: Internet
Author: User
Tags mysql index

Hash index and BTree index, and Hash index BTree Index

An index is a data structure that helps mysql obtain data. The most common indexes are Btree indexes and Hash indexes.

Different engines have different support for indexes: Innodb and MyISAM default indexes are Btree indexes, while Mermory default indexes are Hash indexes.


Hash Index

The so-called Hash index, when we want to add an index to a column in a table, we will calculate the Hash algorithm for this column in this table, get the Hash value, and sort it in the Hash array. Therefore, the Hash index can be located at a time, which is highly efficient. The Btree index requires multiple disk IO operations, but innodb and myisam do not use it because it has many disadvantages:

1. Because Hash indexes compare Hash-calculated values, they can only be used for Equality comparison and cannot be used for range queries.

1. Full table scan is required each time.

2. Hash values are arranged in order, but the real data mapped by Hash value is not necessarily arranged in order in the Hash table. Therefore, the Hash index cannot be used to accelerate any sorting operation.

3. partial index keys cannot be used for search, because composite indexes are calculated together when calculating hash values.

4. When a large number of hash values are repeated and the data volume is very large, the retrieval efficiency is not as high as that of the Btree index.


Btree Index

As for the Btree index, it is implemented based on the B + tree storage structure.

However, the storage structure of the Btree index is very different between Innodb and MyISAM.

In MyISAM, if we want to create a Btree index for a column in a table,



Therefore, we often say that the data files in MyISAM are separated from the index files.

Therefore, MyISAM indexes are also called non-clustered indexes. Innodb indexes become clustered indexes.

Secondary indexes are similar to primary indexes. The only difference between secondary indexes and secondary indexes is that the values of primary indexes cannot be repeated.



Therefore, when we search for a key based on the Btree index, if the key exists, find its address in the data domain, and then search for the data records in the table based on the address.

As for Innodb, it is quite different from above. Its leaf node does not store table addresses but data.




We can see that the address is not put into the leaf node, but directly into the corresponding data. This is what we usually say, the Innodb Index file is the data file,

Therefore, the secondary index structure of Innodb is significantly different from that of the primary index,



We can find that the leaf node stores the primary key information, so we retrieve the primary key information when using the secondary index, then, you can use the primary key to locate the table data in the primary index. This shows that the primary key in Innodb is not suitable for using too long fields. Because all secondary indexes contain the primary index, therefore, it is easy to make secondary indexes huge.

We can also find that the auto-increment primary key should be used in Innodb as much as possible, so that each time you add data, you only need to add it later, non-monotonous primary keys need to maintain the B + tree feature during insertion for split adjustment, which is very inefficient.


The leftmost matching principle in the Btree index:

B-tree creates a search tree from left to right. For example, if the index is (name, age, sex), the name field is checked first. If the name field is the same, the last two fields are checked.

So when the data of the last two fields (age, sex) is passed in, because the search tree is created based on the first field, therefore, you must know where to query the next field based on the name field.

Therefore, when the parameter (name, sex) is passed in, the search direction is specified according to the name first, but the second field is missing. Therefore, after the name field is found correctly, then match the sex data.


Index creation rules:

1. Use the leftmost Prefix: Mysql always searches right until it encounters a range operation (>,<, like, between) to stop matching. For example, a = 1 and B = 2 and c> 3 and d = 6; if an index (a, B, c, d) is created, then the d index is useless at all. It can be used when it is changed to (a, B, d, c.

2. No excessive indexing: when modifying table content, indexes must be updated or reconstructed. Therefore, if there are too many indexes, more time will be consumed.

3. Try to expand the index instead of creating a new index.

4. The most suitable index column is the column that appears in the where clause or specified in the join clause.

5. You do not need to create an index (gender) for columns with fewer values ).






References:

What is the difference between B-tree indexes and hash indexes in MySQL?

MySQL index principle and slow Query Optimization

Data Structure and algorithm principles behind MySQL Indexes

Image Source: Data Structure and algorithm principle behind MySQL Indexes


Copyright Disclaimer: This article is an original article by the blogger. For more information, see the source.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.