MySQL Indexing method

First, B-tree

B-tree is the most common type of index, and all values (columns indexed) are ordered, with each leaf node being equal to the distance from the node. So B-tree is suitable for finding data in a range and can directly support data sorting (order by)
B-tree in MyISAM in the form and InnoDB slightly different:
MyISAM the table data file and index file are detached, the index file only saves the disk address of the data record
The InnoDB table data file itself is the primary index, and the data field of the leaf node holds the complete record

Second, the hash index

1. Only "=", "in" and "<=>" are supported for exact queries and cannot use range queries:
Because the hash index comparison is the hash value after the hash operation, so it can only be used for the filtering of the equivalent, not for the range-based filtering, because after the corresponding hash algorithm processing hash
2. Sorting is not supported:
Because the hash index is stored in the hash after the hash value, and the size of the hash value is not necessarily the same as the key value before the hash operation, so the database can not use the indexed data to avoid any sort operations
3. The table scan cannot be avoided at any time:
Because the hash index compares the hash value after the hash operation, even if the number of records that satisfy a certain hash key value is obtained, the query can not be completed directly from the hash index, or the actual data in the table is accessed, and the corresponding results are obtained.
4. The retrieval efficiency is high, the index retrieval can be located at once, unlike the B-tree index need to be from the root node to the side point, and finally access to the page node so many IO access, so the hash index query efficiency is much higher than the B-tree index
5. Only the memory engine supports an explicit hash index, but its hash is nonunique, and too much conflict can affect the lookup performance. Memory Engine The default index type is the hash index, although it also supports the B-tree index

Iii. Index of R-tree

R-tree is rarely used in MySQL, only supports geometry data types, and supports this type of storage engine with only MyISAM, BDb, InnoDb, NDb, and archive.

See MORE:

MySQL optimization
MySQL each storage engine
MySQL Lock explanation
MySQL Transaction
MySQL index type

MySQL Indexing method