Innodb storage engine index

The Innodb storage engine has three indexes

  1. B + tree index
  2. The full text indexing
  3. The hash index

Hash indexes are adaptive in the Innobd storage engine. The storage engine automatically generates hash indexes based on the table usage. That is, when the data is queried by this storage engine, it is cached to form hash keys, and the next query will use hash indexes

B+ number index structure is similar to binary tree, according to the key to quickly find the data, in addition, B+ tree is not through the given key value to query the specific row, B+ tree can only find the specific page through the given key value, and then the database read the page into memory, and then find the desired data in memory.

B + tree index

B+ trees, like binary trees and balanced binary trees, are classical data structures, and they actually use binary search, but the difference is that binary trees tend to be unbalanced. Balanced binary trees maintain balance through left and right rotation, but because each node stores less data and has a deep index level, while B+ trees use rotation to maintain balance, and store multiple data through each leaf node and link leaf nodes through linked lists, thus reducing levels and speeding up the query speed. Compared with B tree, all nodes of B tree store data, which is relatively deeper, so B+ tree is equivalent to optimization of B tree.

B+ tree is usually 2-4 layers. The INDEX of B+ tree in database is divided into clustered index and non-clustered index

The full text indexing

Full text indexing is a technology to find the content information of the entire book or article stored in the database anywhere. Innodb did not support this technology before, but since 1.2x, InnoDB has supported this technology.

  • Inverted index

    Full-text indexes are usually implemented using inverted indexes, which store mappings between words and their positions in one or more secondary tables, usually implemented using associative arrays.

The hash index

There is a hash table in the cache pool page of Innodb storage engine, and the cache pool page has a chain pointer, which points to the page of hash function value. The hash index is created and used by the database itself. For example, select * from table where name =’ XXX ‘, the next query using name =’ XXX ‘will probably use the hash index, but if it is a range query, it will definitely not use the hash index.