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

This article will explain in detail the example analysis of MySQL index structure for you. The editor thinks it is very practical, so I share it for you as a reference. I hope you can get something after reading this article.

Brief introduction

In addition to the data, the database system also maintains data structures that meet specific search algorithms, which refer to (point to) the data in some way, so that advanced search algorithms can be implemented on these data structures. This kind of data structure is the index.

Generally speaking, the index itself is so large that it is impossible to store it all in memory, so the index is often stored on disk in the form of an index file.

Advantages:

1. Similar to university library to build bibliographic index to improve the efficiency of data retrieval and reduce the IO cost of database.

2. Sort the data through the index column, reduce the cost of data sorting and reduce the consumption of CPU.

Disadvantages:

1. Although the index greatly improves the query speed, it slows down the speed of updating the table, such as INSERT, UPDATE, and DELETE on the table. Because when updating the table, MySQL not only saves the data, but also saves the index file. Every time you update a field that adds an index column, it adjusts the index information after the key value changes due to the update.

2. In fact, an index is also a table, which holds the primary key and index fields and points to the record of the entity table, so the index column also takes up space.

Index example: (index using tree structure)

On the left is the data table, with two columns and seven records, and the leftmost one is the physical address of the data record.

In order to speed up the search of Col2, we can maintain a binary search tree shown on the right, each node contains an index key value and a pointer to the physical address of the corresponding data record, so that we can use binary search to obtain the corresponding data within a certain complexity, so as to quickly retrieve the records that meet the conditions.

Index structure (tree)

How to speed up the query speed of database tables through indexes? For ease of explanation, we limit the database table to the following two query requirements:

1 、 select* from user where id=1234

2. Select * from user where id > 1234 and id2). For more information, please see below.

BTree index

Before we introduce the B + tree, let's take a look at the B tree.

1. Introduction to initialization

A b-tree, a light blue block we call a disk block, you can see that each disk block contains several data items (shown in dark blue) and pointers (shown in yellow), such as disk block 1 contains data items 17 and 35, contains pointers P1, P2, P3. P1 represents blocks less than 17, P2 represents blocks between 17 and 35, and P3 represents blocks greater than 35.

Note:

The real data exists only on leaf nodes, that is, 3, 5, 9, 10, 13, 15, 28, 29, 36, 60, 75, 79, 90, 99. (and it is a data interval composed of multiple data: 3 ~ 5, … ... , 90 ~ 99)

Non-leaf nodes do not store real data, but only store data items that guide the search direction, such as 17 and 35 do not really exist in the data table.

2. Search process

If you want to find data item 29, then disk block 1 will first be loaded into memory from the disk. At this time, an IO occurs, and the binary search in memory determines that 29 is between 17 and 35. Lock the P2 pointer of disk block 1. The memory time is negligible because it is very short (compared to the IO of the disk). Disk block 3 is loaded into memory from the disk through the disk address of the P2 pointer of disk block 1, and the second IO occurs. 29 between 26 and 30, lock the P2 pointer of disk block 3, load disk block 8 into memory through the pointer, and the third IO occurs. At the same time, do a binary search in memory to find 29, end the query, a total of three times IO.

B+Tree index

The B + tree is similar to the B tree, which is an improved version of the B tree. That is, the tree constructed by m-cross search tree and ordered chain table is the B + tree, that is, the tree index to be stored.

As shown in the figure: the main differences between B + trees and B trees are as follows:

1. The leaf nodes of the B+ tree are concatenated by a linked list. To find the data of a certain interval, you only need to find it in the tree with the starting value of the interval. When you locate a node in the ordered list, then traverse back along the ordered list from that node until the node data value in the ordered list is greater than the interval termination value.

2. Any node in the B+ tree does not store real data, but is used for indexing. The B-tree obtains the data directly through the leaf node, while each leaf node of the B + tree stores the key value and address information of the data row. When querying a leaf node, the real data information is found through the address of the leaf node.

Clustered index and unclustered index

Clustered index is not a single index type, but a way of data storage. The term "clustering" refers to the storage of data rows and adjacent key values in clusters.

Benefits of clustered indexing:

According to the order of clustering index, when the query displays a certain range of data, because the data are closely linked, the database does not need to extract data from multiple data blocks, so it saves a lot of io operations.

Limitations of clustered indexes:

1. For mysql database, only innodb data engine supports clustered index, but Myisam does not support clustered index.

2. Because there can be only one sort method for data physical storage, there can be only one clustered index for each Mysql table. Typically, it is the primary key of the table.

3. In order to make full use of the clustering characteristics of the clustered index, the primary key column of the innodb table chooses ordered id as far as possible, rather than unordered id, such as uuid.

As shown in the following figure, the index on the left is a clustered index, because the arrangement of data rows on disk is consistent with the index sort.

Index classification

Single-valued index

That is, an index contains only a single column, and a table can have multiple single-column indexes.

Index together with the table: CREATE TABLE customer (id INT (10) UNSIGNED AUTO_INCREMENT, customer_no VARCHAR, customer_name VARCHAR, PRIMARY KEY (id), KEY (customer_name)); separate index: CREATE INDEX idx_customer_name ON customer (customer_name); delete index: DROP INDEX idx_customer_name on customer

Unique index

The value of the index column must be unique, but null values are allowed

Index with the table: CREATE TABLE customer (id INT (10) UNSIGNED AUTO_INCREMENT, customer_no VARCHAR, customer_name VARCHAR, PRIMARY KEY (id), KEY (customer_name), UNIQUE (customer_no)); unique index separately: CREATE UNIQUE INDEX idx_customer_no ON customer (customer_no); delete index: DROP INDEX idx_customer_no on customer

Primary key index

When it is set as the primary key, the database automatically builds the index, and innodb is a clustered index.

Index with the table: CREATE TABLE customer (id INT (10) UNSIGNED AUTO_INCREMENT, customer_no VARCHAR, customer_name VARCHAR, PRIMARY KEY (id)); CREATE TABLE customer2 (id INT (10) UNSIGNED, customer_no VARCHAR, customer_name VARCHAR, PRIMARY KEY (id)); separate primary key index: ALTER TABLE customer add PRIMARY KEY customer (customer_no); delete primary key index: ALTER TABLE customer drop PRIMARY KEY Modify and build a primary key index: you must first delete (drop) the original index, and then create a new (add) index.

Composite index

That is, an index contains multiple columns.

Index together with the table: CREATE TABLE customer (id INT (10) UNSIGNED AUTO_INCREMENT, customer_no VARCHAR, customer_name VARCHAR, PRIMARY KEY (id), KEY (customer_name), UNIQUE (customer_name), KEY (customer_no,customer_name)); separate indexing: CREATE INDEX idx_no_name ON customer (customer_no,customer_name); delete index: DROP INDEX idx_no_name on customer; performance analysis index creation scenario

What are the circumstances in which an index needs to be created

1. The primary key automatically sets up a unique index

2. Fields that are frequently used as query criteria should be indexed

3. Index the fields associated with other tables and foreign key relationships in the query

4. The choice of single key / combination index, the price ratio of combination index is higher.

5. the sorted fields in the query, if the sorted fields are accessed through the index, the sorting speed will be greatly improved.

6. Statistics or grouping fields in the query

Under what circumstances do not create an index

1. Too few table records

2. Reasons for frequent addition and deletion of tables or fields: the query speed is improved, but the speed of updating the table is reduced, such as INSERT, UPDATE and DELETE to the table. Because when updating the table, MySQL not only saves the data, but also saves the index file.

3. Fields that are not used in Where conditions will not be indexed.

4. Those with poor filtering are not suitable for indexing.

This is the end of this article on "sample Analysis of MySQL Index structure". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, please 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.