MySQL index and performance (1) Index type

This article discusses the types of indexes that MySQL supports and their pros and cons. Note that in MySQL, indexes are implemented at the storage engine level rather than the server layer, so the indexes of the different storage engines work differently, and not all the storage engines support all types of indexes.

B + Tree Index

B + Tree is a classical data structure, which is produced by the combination of a balanced tree and a two-fork lookup tree. It is a balanced lookup tree designed for disk or other direct access aids, in a B + tree, all record nodes are in the same level of the key value of the order stored in the same layer of leaf nodes, the leaf nodes are connected by the pointer to form a two-way circular chain list, Non-leaf nodes (root nodes, side points) hold only the key values and do not store the actual data. Here's an example of a 2-story B + Tree:

Maintaining the tree balance is mainly to improve query performance, but in order to maintain the balance of the tree, the cost is also huge, when there is data insertion or deletion, the need to use split node, left, right and other methods. B + trees are highly balanced because of their high fan-out, usually at 2~3 levels, and can effectively reduce IO times when queried.

For a B + Tree composite index, the order of the indexed columns is important, and you cannot use the index or skip the columns in the index if you do not start looking by the leftmost column in the index. The following is an example of a composite index:

ALTER TABLE T add key idx_a_b (A,B);

The following figure is its chart:

It is obvious that this composite index is available for statements such as where a = xxx and b=xxx. Now look at the case for a single column, where a = XXX can also use the composite index, because a column is also ordered in a composite index, but for a statement such as where B =xxx, the compound index cannot be used because it is unordered.

Hash index

Hash indices are based on a hash table, and only queries that accurately match all columns of the index are valid. In MySQL, only the memory engine displays a supported hash index.

Because the index itself only needs to store the corresponding hash value, the structure of the index is very compact, which also makes the hash index lookup very fast, but it only supports equivalence comparisons, so it only applies to specific situations.

InnoDB has a special function called "Adaptive Hash Index", which monitors the use of indexes on the table in real time, and if a hash index is considered to improve query efficiency, automatically in-memory "adaptive Hash Index buffer" (see MySQL-talking about InnoDB architecture) To establish a hash index on the memory construct. The reason this technology is called "adaptive" is that it is entirely up to InnoDB to decide without the need for DBA intervention. It is constructed from a B + tree in the buffer pool and does not require a hash index of the entire table, so its data is very fast. InnoDB official documentation shows that the read and write performance can be increased by twice times after the Adaptive hash index is enabled, and that performance can be increased by 5 for connection operations of the secondary index, so by default it is turned on, and we can disable this attribute by using the parameter innodb_adaptive_hash_index.

Spatial Data Index (R-TREE)

MyISAM supports spatial indexing, which can be used as a geographic data store, and, unlike a B-tree index, it indexes data from all dimensions without having to prefix queries. queries, you can effectively use any dimension to combine queries.

Full-text indexing

A Full-text index is a special type of index that looks for keywords in text rather than directly comparing values in the index. Full-text indexing is more akin to what search engines do than simple where conditions match.

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/MySQL/