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

What is the role of MRR in MySQL? I believe many inexperienced people don't know what to do about it. Therefore, this paper summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

Execute a scope query:

Mysql > explain select * from stu where age between 10 and 20 +-- + | id | select_type | table | type | key | key_len | ref | rows | Extra | | +-+ | 1 | SIMPLE | stu | range | age | 5 | NULL | 960 | Using index condition | + | -- +-

When the sql is executed, MySQL reads the data to disk as shown below (assuming the data is not in the data buffer pool):

In the picture, the red line is the whole query process, and the blue line is the movement route of the disk.

This picture is drawn according to the index structure of Myisam, but it also applies to Innodb.

For Myisam, on the left is the secondary index of the field age, and on the right is the place where the full row of data is stored.

First go to the secondary index on the left and find the first record that meets the criteria (in fact, each node is a page, a page can have many records, here we assume that there is only one record per page), then go to the right to read the complete record of this data.

After reading, go back to the left, continue to find the next qualified record, find it, and then read it on the right, and then find that this data follows the last piece of data, which is far away from the physical storage location!

What to do, there is no way, but to let the disk and the head do mechanical motion together to read this data for you.

The third and the fourth are all the same, each time the data is read, the disk and head have to run a long way.

The simplified structure of the disk can be seen as follows:

You can imagine that in order to execute your sql statement, the disk has to rotate constantly, the head has to keep moving, these mechanical movements are very time-consuming.

10000 RPM (Revolutions Per Minute) mechanical hard drives can perform about 167disk reads per second, so in extreme cases, MySQL can only return 167pieces of data per second, not counting the CPU queue time.

It's all about mechanical hard drives, tuhao of SSD, please make yourself at home.

The same is true for Innodb. Innodb is a clustered index (cluster index), so you just need to replace the right side with a B + tree with complete data on the leaf node.

Sequential Reading: a Storm Revolution

Now that you know how extravagant random disk access is, it's obvious to turn random access into sequential access:

Mysql > set optimizer_switch='mrr=on'; Query OK, 0 rows affected (0.06 sec) mysql > explain select * from stu where age between 10 and 20 +-+ | id | select_type | table | type | key | key_len | ref | rows | Extra | + -+ + | 1 | SIMPLE | tbl | range | age | 5 | NULL | 960 | Using MRR | +-- +

We opened MRR, re-executed the sql statement, and found that there was an extra "Using MRR" in Extra.

Now the query process for MySQL will look like this:

For Myisam, before going to the disk to get the complete data, the disk will be sorted according to rowid, and then read the disk sequentially.

For Innodb, the cluster index is sorted according to the key values of the cluster index, and then the cluster index is read sequentially.

Sequential reading brings several benefits:

1. Disks and heads no longer need to move back and forth mechanically

2. Can make full use of disk pre-reading

For example, when the client requests one page of data, the data of the next few pages can also be returned and put into the data buffer pool, so that if the next page of data is needed next time, it will no longer need to be read to disk. The theoretical basis for this is the famous locality principle in computer science:

When a data is used, the data near it is usually used immediately.

3. In a query, the data on each page is only read from disk once.

After MySQL reads the data of the page from the disk, it will put the data into the data buffer pool. Next time, if the page is still used, it does not need to be read to disk, but directly from memory.

But if you don't sort, maybe after you read the data on page 1, you will read the data on page 2, 3, and 4, and then you have to read the data on page 1, and then you find that the data on page 1 has been removed from the cache. so you have to go to the disk to read the data on page 1.

After being converted to sequential reading, you will continuously use the data on page 1. According to MySQL's cache elimination mechanism, the cache of this page will not expire until you have finished using the data of this page. Because it is read sequentially, you are sure that you will not use the data of this page for the rest of this query, so you can say goodbye to this page of data.

Sequential reading optimizes the reading of the index through these three aspects.

Don't forget that the index itself is to reduce disk IO and speed up queries, while MRR is to further magnify the role of indexes in reducing disk IO.

Some configuration about this revolution

There are two configurations related to MRR:

Mrr: on/off

Mrr_cost_based: on/off

The first is the switch used in the above demonstration to turn on MRR:

Mysql > set optimizer_switch='mrr=on'

If you don't open it, you won't use MRR.

The other is to tell the optimizer whether or not to consider whether it is worth using MRR based on the cost of using MRR (cost-based choice) to decide whether to use MRR in specific sql statements.

Obviously, for a query that returns only one row of data, MRR is not necessary, and if you set mrr_cost_based to off, then the optimizer will all use MRR, which is very stupid in some cases, so it is recommended that this configuration be set to on. After all, the optimizer is correct in most cases.

There is also a configuration read_rnd_buffer_size that sets the amount of memory used to sort the rowid.

Obviously, MRR is essentially an algorithm that trades space for time. MySQL cannot give you unlimited memory to sort. If the read_rnd_buffer is full, it will first sort the full rowid to disk to read, then empty it, and then continue to put rowid in it until the read_rnd_buffer reaches the upper limit of read_rnd_buffe configuration, and so on.

After reading the above, have you mastered the role of MRR in MySQL? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

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.