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

This article introduces the relevant knowledge of "A brief introduction to Mysql Index". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

We all know that database indexes can help us find matching data more quickly, but if we don't use indexes, Mysql will query from the first item until the matching data is found, which will also lead to a problem: if no index is added, it will take more time to query the data if the data in the table is large. At this point, we add an index to the field, and Mysql will quickly search the data, which can save a lot of time. MyISAM and InnoDB are the two most frequently used storage engines. Both MyISAM and InnoDB indexes use the data structure of B + tree, so what is the difference between B tree and B + tree?

B tree

B-tree is a kind of multi-channel search tree, which starts from the root node to search for the ordered keywords in the node, and ends the search if it hits, otherwise it enters the left and right child nodes according to the search size results to repeat the search until the search results are found.

Features:

Keywords are distributed in all nodes of the B-tree.

Keywords do not repeat on multiple nodes.

The search may end at a non-leaf node.

B + tree

The B + tree is actually a special B tree. The most obvious difference between the B tree and the B tree is that the keywords of the B + tree only appear in the leaf nodes, and the keywords are ordered in the linked list, that is, the search of the B + tree will only hit the results in the leaf nodes in the end. What role does the non-leaf node play in the B + tree? The non-leaf node is equivalent to the index of the leaf node in the B+ tree, and the leaf node is the data layer that stores the keyword data. Since the Mysql index uses the data structure of the B + tree, what is the advantage of using the B + tree as the index compared to the B tree:

Disk read and write costs are lower.

Query efficiency is more stable.

Traversing elements is efficient.

After talking about the concepts of B-tree and B + tree, we need to start talking about indexing. In fact, there are two kinds of data structures of Mysql index: B+ tree and Hash. However, only B+ trees can be used in MyISAM and InnoDB storage engines, and indexes can be divided into four categories:

Single-column index: there are three kinds of single-column index, including general index, unique index and primary key index.

Combinatorial index

Full-text index

Spatial index

Single column index

A single-column index, as its name implies, means that an index can only act on a single column, but a data table can have multiple single-column indexes at the same time. There are three types of single-column indexes: normal index, unique index, and primary key index.

General index: the basic index type, which does not impose any restrictions on the data, and also allows null or duplicate values in the data columns of the ordinary index added to the ordinary index. the purpose of adding the ordinary index is to query the data faster.

Unique index: adding a unique index to a single column means that the column can only be a unique value, for example, the user name of the user table can add a unique index, so that the user name must be a unique value, but can be null.

Primary key index: in fact, on the basis of a unique index, the column is not allowed to have null values.

Combinatorial index

Select the multi-column combination of the data table and create the index, but the combined index does not mean that all successful creation can be used, but needs to follow the leftmost prefix set. That is, the combined index will take effect only if the left field of these fields is used in the query condition. Let's give an example to explain what the leftmost prefix is.

First create a table test_10_09, and combine the three columns id, username, and sex, and then add the index.

CREATE TABLE test_10_09 (id INT NOT NULL, username VARCHAR (20) NOT NULL, idcard VARCHAR (18), sex VARCHAR (3) NOT NULL, INDEX MultiIdx (id, username, sex))

We say that for the composite index to take effect, it needs to satisfy the leftmost prefix. What is the leftmost prefix? The leftmost prefix is actually using the leftmost column in the combined index to match the data. As we can see in the above example, the leftmost column of the combined index is id, so if the condition of our query does not include id, that is, it does not meet the leftmost prefix principle, then the query operation cannot take advantage of the combined index we created. We can use the EXPLAIN directive to test the effect of query conditions with and without id:

We can see that we can query through the index with id query, but the query without id query cannot trigger the leftmost prefix principle, so the combined index does not take effect.

Full-text index

Full-text indexing actually means literally. Using full-text indexing, you can find record rows that contain fields through a keyword in a series of text. But full-text indexing has many limitations:

It is not supported in the InnoDB storage engine, only allowed in the MyISAM storage engine.

Full-text indexing can only be used in three types of data columns: char, varchar and text.

The keyword searched should be at least 4 characters by default.

The global index relies on the MATCH function.

Spatial index

Mysql 5. 7 begins to support spatial indexing. Spatial indexes are generally suitable for systems that include spatial operations, such as game development.

Spatial indexes can only be used in data columns of spatial data types such as GEOMETRY, POINT, LINESTRING, and POLYGON4. And the data column that adds the spatial index must be non-empty.

The SPATIAL keyword must be used when creating a spatial index.

Advantages of index

All data columns of the data table can be indexed.

Using a unique index or a primary key index ensures the uniqueness of the data.

Using indexes can improve the efficiency and performance of querying data.

Index shortcoming

Using an index takes up a certain amount of physical space.

Both data insertion and modification require index maintenance, which can affect performance.

Principles of index use

Tables that often require insert or update operations should not set too many indexes, because data inserts and modifications need to maintain indexes, which can affect performance.

It is not recommended to add indexes to tables with small amount of data, otherwise query efficiency and performance may be reduced.

Do not use indexes when the range of column values is small, for example, professional names have only three values, so it really doesn't make much sense to use indexes.

The composite index puts the most frequently used columns in the first column to ensure that the composite index meets the requirements of the leftmost prefix.

If the column value is unique, you can add a unique index to the field to improve query efficiency.

As far as possible, the index is added to the column with less data, for example, the retrieval efficiency of varchar (100) is certainly not as fast as that of varchar (30), so the query efficiency of adding index to the column with large amount of data will be slower.

This is the end of the brief introduction to the Mysql Index. Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.