Shulou(Shulou.com)06/01 Report--

In this article, the editor introduces in detail "what are the index knowledge points of MySQL", the content is detailed, the steps are clear, and the details are handled properly. I hope that this article "what are the index knowledge points of MySQL" can help you solve your doubts.

I. introduction of the index

Index is a data structure (ordered) that helps MySQL get data efficiently. In addition to the data, the database system maintains data structures that meet specific lookup algorithms, which refer to (point to) the data in some way, so that advanced lookup algorithms can be implemented on these data structures. this data structure is the index.

Second, the advantages and disadvantages of index

Advantages:

Improve the efficiency of data retrieval, reduce the io cost of the database, sort the data through index columns, reduce the cost of data sorting, and reduce the consumption of CPU.

Disadvantages:

Index columns also take up space. The index greatly improves the query efficiency, but also reduces the speed of updating the table, such as INSERT, UPDATE, DELETE, the efficiency is reduced.

III. Index structure

Usually what we call an index, which is not specifically specified, refers to an index organized by a B+ tree structure.

B+Tree index: the most common index type, most engines support B+ tree index

Hash index: the underlying data structure is implemented in a hash table. Only queries that exactly match index columns are valid. Range queries are not supported.

R-tree (spatial index): spatial index is a special index type of MyISAM engine, which is mainly used for geospatial data types and is usually used less.

Full-text (full-text index): is a way to quickly match documents by establishing an inverted index. Similar to Lucene,Solr,ES

1. Classical B + tree

The structure is similar to the B-tree. The difference between the B + tree and the B-tree is:

1. All elements appear on the leaf node, the non-leaf node mainly acts as an index, and the leaf node is used to store data.

2. In the data structure of the B + tree, the leaf nodes form an one-way linked list, and each node points to the next element through a pointer.

2. B + tree index in MySQL

The MySQL index data structure optimizes the classical B+Tree. On the basis of the original B+Tree, a linked list pointer to the adjacent leaf nodes is added to form a B+Tree with sequential pointers, which improves the performance of interval access. The leaf nodes are bidirectional linked list + connected to the end, which is convenient for range search and sorting.

3. Hash index

Hash indexing uses a certain hash algorithm to convert the key value into a new hash value, map it to the corresponding slot, and then store it in the hash table.

If two (or more) keys are mapped to the same slot, they result in hash conflicts (also known as hash collisions), which can be resolved through a linked list.

Features:

1. Hash indexes can only be used for peer-to-peer comparison (=, in), and range queries are not supported (between, >, = or

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.