Shulou(Shulou.com)05/31 Report--

This article introduces to you what is the MySQL index, the content is very detailed, interested friends can refer to, hope to be helpful to you.

Foreword:

Index is one of the important objects in MySQL database. The purpose of index is to improve query efficiency. You can compare the directory in the dictionary. When looking up the contents of the dictionary, you can find the location of the data according to the directory, and then get it directly. An index is the catalog of a table, and you can quickly locate the query data by looking for the index location in the catalog before looking for content. It is important to note that MySQL supports many storage engines, and each storage engine supports indexes differently. To avoid confusion, this article will focus only on B+Tree indexes under the InnoDB engine.

1. Index structure and principle

If you want to understand the principle of an index, you must first know the structure of the index. Here is a brief introduction to the structure of the B+Tree index.

First of all, we need to know that an index is a data structure. In InnoDB, each index is actually a B+ tree, which is a balanced search tree (not a binary tree) designed for disks and other storage auxiliary devices. In the B+ tree, all the data is in the leaf node, and each leaf node has a pointer to the next node, forming an ordered linked list. In general, the height of the B + tree of the database is generally 2-4 layers, which means that it takes up to 2 to 4 logical IO to find the row record of a key value. The following figure briefly shows the structure of the B+ tree index.

Cdn.nlark.com/yuque/0/2020/png/119537/1589268320343-47af018e-3e88-4095-b2c0-50982eb373a6.png ">

two。 Classification and creation method of Index

In InnoDB, tables are stored in the form of indexes according to the order of primary keys, which is called index organization tables. According to the content of the leaf node, the index type is divided into primary key index and non-primary key index. The leaf node of the primary key index stores the entire row of data. In InnoDB, a primary key index is also known as a clustered index (clustered index). The leaf node content of a non-primary key index is the value of the index column and primary key. In InnoDB, a non-primary key index is also called a secondary index (secondary index) or a secondary index

An InnoDB table must have a clustered index, and when there is a primary key, the primary key is used as the clustered index; if the primary key is not explicitly defined, InnoDB chooses a unique non-empty index instead. If there is no such index, MySQL automatically generates an implicit field as the primary key for the InnoDB table. All indexes except clustered index can be called secondary index, such as unique index, general index, federated index and so on.

As mentioned above, the leaf node of a clustered index stores the whole row of data. when a query uses a clustered index, you only need to scan a B + tree of the clustered index to get the required records. if you want to find the complete record through the secondary index, you need to find the complete record through the table operation, that is, you need to find the complete record in the clustered index after finding the primary key value through the secondary index. In other words, queries based on non-primary key indexes need to scan one more index tree. Therefore, we should try our best to use primary key query in our application.

The following describes the following index creation, deletion and other operation methods.

# specify the index CREATE TABLE `tindex` (`col1` int (11) NOT NULL AUTO_INCREMENT COMMENT 'self-increasing primary key', `col1` int (11) NOT NULL, `col2` varchar (20) NOT NULL, `col3` varchar (50) NOT NULL, `col4` int (11) NOT NULL, PRIMARY KEY (`colid`), UNIQUE KEY `uk_ col1` (`col1`), KEY `idx_ col2` (`col2`) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=' test index when creating the table # create an index (two methods) # General index alter table `tindex` add index idx_col3 (col3); create index idx_col3 on t_index (col3); # unique index alter table `tindex` add unique index uk_col4 (col4); create unique index uk_col4 on t_index (col4); # Joint index alter table `tindex` add index idx_col3_col4 (col3,col4); create index idx_col3_col4 on t_index (col3,col4) # Delete the index alter table `tindex` drop index uk_col4;DROP INDEX idx_col3_col4 on tweeds index3. Advantages and disadvantages of Index and suggestions for its use

The obvious advantage of an index is that it can speed up the query, but creating an index also comes at a cost. First of all, each index has to build a B+ tree for it, which will take up additional storage space; secondly, when the data in the table is added, deleted and modified, the index also needs dynamic maintenance, which reduces the speed of data maintenance. Therefore, there are principles in the creation and use of the index, here are some suggestions for the use of the index:

Explicitly create a primary key index, and it is recommended to use self-incrementing ID as the primary key.

Indexes are created only for columns that are used to search, sort, group, and join.

Avoid creating too many indexes on tables that are updated frequently.

When establishing a federated index, the columns with high selectivity are placed first.

Try not to index columns with poor selectivity, such as gender, status columns, etc.

Try to use the overlay index for query to avoid the performance loss caused by returning to the table. (the override index contains all the columns to query)

After select, only the fields needed for the query are written, and the fields that are not needed are removed.

Locate and delete duplicate and redundant indexes in the table.

About what is the MySQL index to share here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

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.