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

This article will explain in detail how to achieve indexing and locking in MySQL. The content of the article is of high quality. Therefore, Xiaobian shares it with you as a reference. I hope you have a certain understanding of relevant knowledge after reading this article.

index

Several common types of index

Common types of index are hash index, ordered array index, binary tree index, skip table and so on. This article focuses on the index structure of MySQL's default storage engine, InnoDB.

Index structure of InnoDB

In InnoDB, the index structure is implemented through a multi-path search tree, the B+ tree. In a B+ tree, only leaf nodes store data, and all leaf nodes form a linked list. What is maintained in InnoDB is a doubly linked list.

You may have a question, why use B+ trees instead of binary trees or B trees?

First of all, we know that accessing the disk requires accessing the specified block, and accessing the specified block requires disk rotation and magnetic arm movement, which is a time-consuming process. If you increase the tree height, it means that you need to make more disk accesses, so you will use an n-fork tree.

The B+ tree is used because if you use a B tree, you will retrieve it every time you perform a range lookup, and in B+ trees you can make full use of the list of leaf nodes.

You may add multiple indexes while building a table, and InnDB will create a B+ tree for each index to store the index.

For example, at this point, we built a simple checklist

create table test( id int primary key, a int not null, name varchar, index(a) )engine = InnoDB;

At this point InnDB will create two B+ index trees for us

One is the clustered index of the primary key, and the other is the auxiliary index of the ordinary index. Here I directly paste MySQL (index, lock) on the map above this article (because I am lazy to draw..)

As you can see, the leaf node above the secondary index stores only the primary key value, while the leaf node above the clustered index of the primary key stores the entire record value.

return statement

So here will be derived a concept called back to the table, for example, this time we perform a query operation

select name from test where a = 30;

We know that MySQL will go to the index of a because of the condition, but the index of a does not store the value of name. At this time, we need to get the primary key value on the corresponding a, and then go to the cluster index through this primary key value and finally get the name value. This process is called back to the table.

Let's summarize what the answer table is. MySQL finds the corresponding primary key value on the secondary index and looks up the desired data on the clustered index through the primary key value.

index maintenance

We know that the index is required to take up space, although the index can improve our query speed but also can not be abused.

For example, if we use ID number as primary key in user table, then each leaf node of secondary index takes about 20 bytes, while if integer is used as primary key, it only needs 4 bytes, and if it is long integer (bigint), it is 8 bytes. That is, if I maintain an index list of 4 g's followed by integers, then I'll have 20 g's with my ID card.

So we can reduce the index space by reducing the index size.

Of course, B+ trees do some maintenance during deletions and insertions in order to maintain the order of the index (deletions in InnoDB mark nodes as "reusable" to reduce changes to the structure).

For example, when adding a node, you may encounter a situation where the data page is full. At this time, you need to split the page. This is a time-consuming job, and the page split will also lead to the utilization rate of the data page becoming lower. For example, when adding another data to the data page that originally stored three data, you need to split the page. At this time, the existing four data will be allocated to two data pages, thus reducing the utilization rate of the data page.

coverage indices

The above mentioned back to the table, and sometimes when we look up the auxiliary index, we have already satisfied the data we need to look up. At this time, InnoDB will perform an operation called overwriting the index to improve efficiency and reduce back to the table.

For example, at this time, we perform a select operation

select id from test where a = 1;

At this time, it is obvious that we can get the id value directly by taking the index of a. At this time, we do not need to go back to the table. We use the coverage index at this time.

Simply put, an overlay index is an operation that does not need to go back to the table again when we can get the data we need when we walk through the auxiliary index.

CODIS

At this point we create a new student table

CREATE TABLE `stu` ( `id` int(11) NOT NULL, `class` int(11) DEFAULT NULL, `name` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`), KEY `class_name` (`class`,`name`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8

We use class and name to make a joint index, and you might ask what this joint index is for. We can combine the above coverage index to understand, for example, at this time we have a requirement, we need to find the corresponding student name through the class number.

select name from stu where class = 102;

At this point we can look up the student's name directly on the auxiliary index without having to go back to the table again.

In general, a well-designed index, make full use of coverage index can greatly improve the retrieval speed.

leftmost prefix principle

This is based on union index, which is a matching rule for union index.

At this time, we will change the above requirements slightly. At this time, we have a student who is late, but he only wrote his name Zhang San and did not write the class when the guard recorded the information, so we need to find the corresponding class number through the student name.

select class from stu where name = '';

At this point we will not go to our joint index, but to perform a full table scan.

Why not? Because of the leftmost matching principle. We can draw a simple picture to understand.

We can see that the whole index design is designed like this, so we need to follow this rule when we need to find, if we use name directly, then InnoDB does not know what we need to do.

Of course, the leftmost matching principle and these rules

The optimizer will change the order when you match all values, that is, the order when you match all values is not consistent with the original joint index order, and the optimizer will help you adjust it.

Index matching starts at the far left, if not then full table scan, for example you design a joint index of (a,b,c), then you can use (a),(a,b),(a,b,c) and you use (b),(b, c) without index.

Range matches are deindexed. For example, at this time, you perform such a select operation

select * from stu where class > 100 and name = '';

At this time, InnoDB will abandon the index and perform a full table scan, because at this time InnoDB will not know how to traverse the index, so perform a full table scan.

index push down

I dug you a hole. Before MySQL version 5.6, the operation just now needed to go back to the table, but after version 5.6, an optimization called index push down was done.

select * from stu where class > 100 and name = '';

How is it optimized? Because of the leftmost matching principle just now, we give up the index, and then we will judge the name by going back to the table. At this time, what we have to do should be like this.

However, after the index is pushed down, it becomes like this. At this time,"Li Si" and "Xiaoming" will not go back to the table.

Because this matches the name = Zhang San, that is, if the leftmost matching principle terminates because of the range query, InnoDB will still push down the index to optimize performance.

some practice

In what cases do you need to create an index?

Fields frequently used as query criteria should be indexed.

For multi-table associative queries, the associated fields should be indexed.

Sorted fields in queries should be indexed.

Statistics or grouping fields require indexing.

When do you not need to create an index?

The table records less.

A table that is frequently added, deleted and revisited.

Frequently updated fields.

Where conditions use fields that are not high.

When the field is large.

other

Try to select columns with high discrimination as indexes.

Don't perform some functional operations on indexes, and be aware of implicit type conversions and character encoding conversions.

Expand the index as much as possible, do not create a new index. For example, the table already has an index of a, and now you want to add the index of (a,b), then you only need to modify the original index.

Consider coverage index, index push down, leftmost match.

lock

global lock

MySQL provides a method for adding global read locks with the command Flush tables with read lock (FTWRL). Use this command when you need to keep the entire library read-only, after which the following statements from other threads will be blocked: data update statements (add, delete, change data), data definition statements (including table creation, table structure modification, etc.), and commit statements for update transactions.

It is generally used when performing a full database logical backup, so as to ensure that other threads cannot update the database.

The operation of getting consistent view is provided in MVCC to make backup very simple. If you want to know MVCC, please refer to

https://juejin.im/post/5da8493ae51d4524b25add55

table locks

MDL(Meta Data Lock)

The MDL lock is used to ensure that only one thread can make table structure changes to the table.

What can I say? MDL is divided into MDL write lock and MDL read lock. The locking rules are as follows:

When a thread performs CRUD operations on a table, MDL read locks are added

When a thread makes a table structure change operation on a table, it adds an MDL write lock

Write lock and read lock, write lock and write lock mutually exclusive, read lock is not mutually exclusive

lock tables xxx read/write;

This is a command to set read and write locks on a table. If lock tables t1 read, t2 write; is executed in a thread A, the statements of other threads writing t1 and reading and writing t2 will be blocked. At the same time, thread A can only perform operations such as reading t1 and reading and writing t2 before executing unlock tables. Even writing t1 is not allowed, and naturally you cannot access other tables.

This type of table lock is one way to handle concurrency, but row locks are common in InnoDB.

row locks

We know that MySQL's default storage engine before version 5.5 is MyISAM, and the biggest difference between MyISAM and InnoDB is two.

Affairs

row locks

One line lock is our theme today, if you do not understand the affairs can go to make up for it.

The implementation lock is two locks, which you can understand as write lock (exclusive lock X lock) and read lock (shared lock S lock).

Shared lock (S-lock): An exclusive lock that allows one transaction to read a row and prevents other transactions from acquiring the same data set. Read locks are shared, and multiple clients can read the same resource at the same time, but other clients are not allowed to modify it.

Exclusive locks (X-locks): Transactions that allow exclusive locks to be acquired update data, preventing other transactions from acquiring shared read locks and exclusive write locks on the same data set. Also called write lock: write locks are exclusive, and write locks block other write locks and read locks.

Line locks also cause a headache, which is deadlocks.

If transaction A has a write lock on row 100 and transaction B has a write lock on row 101, transaction A wants to modify row 101 and transaction B wants to modify row 100, then hold and wait leads to deadlock problems, and only detection and prevention are available to deal with deadlock problems.

next-key lock

MVCC and row lock cannot solve the problem of phantom reading. At this time, InnoDB uses something called GAP lock, which forms next-key lock with row lock to solve the problem of phantom reading.

However, because of its locking rules, it also leads to some expansion of the scope of locking and thus reduces database concurrency. Specific locking rules are as follows:

The basic unit of locking is next-key lock, which is the combination of row lock and GAP lock.

Objects accessed during the search are locked.

For equivalent queries on indexes, when locking unique indexes, next-key locks degenerate into row locks.

For equivalent queries on the index, when traversing to the right and the last value does not satisfy the equivalence condition, next-key lock degenerates into gap lock.

Range queries on unique indexes access up to the first value that does not satisfy the condition.

About MySQL how to achieve index and lock to share here, I hope the above content can be of some help to everyone, you can learn more knowledge. If you think the article is good, you can share it so that more people can see it.

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.