• notice
  • Congratulations on the launch of the Sought Tech site

MySQL Index and Performance (1) Index Type

This article discusses the types of indexes supported by MySQL and their advantages and disadvantages.It should be noted that in MySQL, indexes are implemented at the storage engine layer rather than the server layer, so indexes of different storage engines work differently, and not all storage engines support all types of indexes.

B+tree index

B+ tree is a classic data structure, which is generated by the combination of balanced tree and binary search tree.It is a balanced search tree designed for disks or other direct access auxiliary devices.In B+ tree, all The record nodes are stored in the leaf nodes of the same layer in the order of key value size.The leaf nodes are connected by pointers to form a bidirectional circular linked list.The non-leaf nodes (root nodes and branch nodes) only store key values ​​and do not store actual data.Let's look at an example of a 2-level B+ tree:

Maintaining tree balance is mainly to improve query performance, but in order to maintain tree balance, the cost is also huge.When data is inserted or deleted, methods such as splitting nodes, left-hand rotation, and right-hand rotation are required.Because of its high fan-out, the B+ tree has high balance.Usually its height is 2~3 layers, which can effectively reduce the number of IOs during query.

For a B+ tree composite index, the order of the index columns is very important.If the search is not started from the leftmost column of the index, the index cannot be used, and the columns in the index cannot be skipped.Here is an example of a compound index:

alter table t add key idx_a_b(a,b);

The following figure shows its structure:

Obviously, this compound index can be used for statements like where a=xxx and b=xxx.Now look at the case of a single column, where a=xxx can also use the composite index, because the a column is also ordered in the composite index, but the composite index cannot be used for statements such as where b=xxx, because it is unordered.

Hash Index

Hash indexes are based on hash tables, and only queries that exactly match all columns of the index are valid.In MySQL, only the Memory engine explicitly supports hash indexes.

Because the index itself only needs to store the corresponding hash value, the structure of the index is very compact, which also makes the search speed of the hash index very fast, but it only supports equal value comparison, so it is only suitable for some specific occasion.

InnoDB has a special function called "Adaptive Hash Index", which monitors the usage of indexes on the table in real time.Hash index buffer" (see "MySQL-On InnoDB Architecture" for details) to create a hash index.The reason why this technology is called "adaptive" is that it is entirely up to InnoDB to decide and does not require human intervention from the DBA.It is constructed from the B+ tree in the buffer pool, and does not require a hash index on the entire table, so its data is very fast.InnoDB official documentation shows that after enabling the adaptive hash index, the read and write performance can be improved by 2 times.For the connection operation of the auxiliary index, the performance can be improved by 5 times, so it is enabled by default.We can disable this by the parameter innodb_adaptive_hash_index characteristic.

Spatial Data Index (R-Tree)

MyISAM supports spatial indexes, which can be used as geographic data storage.Unlike B-tree indexes, it indexes data from all dimensions without prefix query.When querying, you can effectively use arbitrary dimensions to combine queries.

Full-text index

A full-text index is a special type of index that looks for keywords in the text, rather than directly comparing the values ​​in the index.Full-text indexing is more similar to what search engines do, rather than simple where condition matching.

Tags

Technical otaku

Sought technology together

Related Topic

1 Comments

author

atorvastatin 10mg price & lt;a href="https://lipiws.top/"& gt;order generic lipitor 80mg& lt;/a& gt; lipitor 40mg for sale

Rzhiir

2024-03-07

Leave a Reply

+