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

This article mainly explains "what is the function of MySQL index". Interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Next let the editor to take you to learn "what is the role of MySQL index"!

Why is the query faster after using the index?

I believe that many programmers are no stranger to the index of data, and the most common index is the B+ Tree index, which can speed up the retrieval of the database, but reduce the speed of new, modify and delete operations, and some incorrect writing will lead to index failure and so on.

But if asked, why does the query get faster after using the index? What is the principle of B + Tree index? At this time, many people may not know, today I will take MySQL's InnoDB engine as an example to talk about the principle of B + Tree indexing.

The basics of indexing

The basic storage structure of MySQL is pages, which looks something like this:

Here, we need to know the following (very important):

When we use MySQL's InnoDB engine to create tables, there can be and only one primary key; if we do not specify between them, MySQL will automatically generate an implied field as the primary key

Clustered index: an index created with a primary key; the leaf node of a clustered index stores data in a table

Nonclustered index: an index created by a non-primary key; a nonclustered index stores primary keys and index columns in a leaf node; using a nonclustered index to query data, the primary key on the leaf is queried, and then the data is found according to the primary key (this process is called returning to the table).

The relationship between pages and pages and between pages and data

We explain with a clustered index, and the relationship between pages and pages, and between pages and data is as follows:

A two-way linked list is formed between the data page and the data page.

The record in each data page is an one-way linked list

Each data page generates a page directory (Page directory) based on the internal records. If it is a primary key, you can quickly locate it in the page directory using dichotomy.

If we query based on a non-primary key and non-index column, we need to traverse the two-way linked list to find the page, and then iterate through the one-way linked list within the page; if the data in the table is large, such a query will be slow.

The principle of B+ Tree Index

Let's first take a look at what a B + Tree index looks like (take a clustered / primary key index as an example):

If we want to query the data with id = 16 at this time:

Query page-1, find page-2 stores data less than 30

Query page-2, find page-5 stores 10-20 data

Query page-5 to find the data with id = 16.

Obviously, when you are not using an index, you need to traverse the two-way linked list to locate the corresponding page, and with the index, you can navigate to the corresponding page through layers of "directories".

Why do B+ Tree indexes slow down the speed of adding, modifying and deleting

B + Tree is a balanced tree. If you add, modify or delete this tree, it will destroy its original structure.

When we add, modify or delete data, we need to spend extra time maintaining the index.

Because of these extra overhead, the index will slow down the speed of adding, modifying, and deleting.

At this point, I believe that you have a deeper understanding of "what is the role of the index of MySQL", you might as well come to the actual operation! Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.