Detailed explanation of the reasons and optimizations for the large offset affecting performance during MySQL query

Preface

MySQL query uses the select command, and with the limit and offset parameters, you can read records in a specified range. This article will introduce the reasons why offset is too large and affects performance when querying MySQL and how to optimize it.

Prepare test data sheets and data

1. Create a table

CREATE TABLE `member` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `name` varchar(10) NOT NULL COMMENT 'Name',
 `gender` tinyint(3) unsigned NOT NULL COMMENT 'Gender',
 PRIMARY KEY (`id`),
 KEY `gender` (`gender`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

2. Insert 1,000,000 records

<?php
$pdo = new PDO("mysql:host=localhost;dbname=user","root",'');

for($i=0; $i<1000000; $i++){
 $name = substr(md5(time().mt_rand(000,999)),0,10);
 $gender = mt_rand(1,2);
 $sqlstr = "insert into member(name,gender) values('".$name."','".$gender."')";
 $stmt = $pdo->prepare($sqlstr);
 $stmt->execute();
}
?>

mysql> select count(*) from member;
+----------+
| count(*) |
+----------+
| 1000000 |
+----------+
1 row in set (0.23 sec)

3. Current database version

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.6.24 |
+-----------+
1 row in set (0.01 sec)

Analyze why large offsets affect performance

1. When the offset is small

mysql> select * from member where gender=1 limit 10,1;
+----+------------+--------+
| id | name | gender |
+----+------------+--------+
| 26 | 509e279687 | 1 |
+----+------------+--------+
1 row in set (0.00 sec)

mysql> select * from member where gender=1 limit 100,1;
+-----+------------+--------+
| id | name | gender |
+-----+------------+--------+
| 211 | 07c4cbca3a | 1 |
+-----+------------+--------+
1 row in set (0.00 sec)

mysql> select * from member where gender=1 limit 1000,1;
+------+------------+--------+
| id | name | gender |
+------+------------+--------+
| 1975 | e95b8b6ca1 | 1 |
+------+------------+--------+
1 row in set (0.00 sec)

When the offset is small, the query speed is fast and the efficiency is high.

2. Large offset

mysql> select * from member where gender=1 limit 100000,1;
+--------+------------+--------+
| id | name | gender |
+--------+------------+--------+
| 199798 | 540db8c5bc | 1 |
+--------+------------+--------+
1 row in set (0.12 sec)

mysql> select * from member where gender=1 limit 200000,1;
+--------+------------+--------+
| id | name | gender |
+--------+------------+--------+
| 399649 | 0b21fec4c6 | 1 |
+--------+------------+--------+
1 row in set (0.23 sec)

mysql> select * from member where gender=1 limit 300000,1;
+--------+------------+--------+
| id | name | gender |
+--------+------------+--------+
| 599465 | f48375bdb8 | 1 |
+--------+------------+--------+
1 row in set (0.31 sec)

When the offset is large, efficiency problems will arise. As the offset increases, the execution efficiency decreases.

Analyze the reasons affecting performance

select * from member where gender=1 limit 300000,1;

Because the data table is InnoDB, according to the structure of InnoDB index, the query process is:

• Find the primary key value through the secondary index (find all ids with gender=1).
• Then find the corresponding data block through the primary key index based on the found primary key value (find the corresponding data block content based on the id).
• According to the value of offset, query the data of the 300001th primary key index, discard the previous 300000 records, and take out the last one.

However, since the secondary index has already found the primary key value, why do we need to use the primary key index to find the data block first, and then perform offset processing based on the offset value?

If after finding the primary key index, you first perform offset processing, skip 300,000, and then read the data block through the primary key index of the 300,001th record, this will improve efficiency.

If we only query the primary key, see what is different

mysql> select id from member where gender=1 limit 300000,1;
+--------+
|id|
+--------+
|599465|
+--------+
1 row in set (0.09 sec)

Obviously, if only the primary key is queried, the execution efficiency is greatly improved compared to querying all fields.

Speculation

Query only the primary key

Because the secondary index has found the primary key value, and the query only needs to read the primary key, MySQL will first perform the offset operation and then read the data block based on the subsequent primary key index.

When you need to query all fields

Because the secondary index only finds the primary key value, but the values ​​of other fields need to read the data block to obtain. Therefore, MySQL will first read the data block content, then perform the offset operation, and finally discard the previous data that needs to be skipped and return the subsequent data.

Confirmation

InnoDB has a buffer pool that stores recently accessed data pages, including data pages and index pages.

For testing purposes, restart MySQL first and then check the contents of the buffer pool.

mysql> select index_name,count(*) from information_schema.INNODB_BUFFER_PAGE where INDEX_NAME in('primary','gender') and TABLE_NAME like '%member%' group by index_name;
Empty set (0.04 sec)

It can be seen that no data pages have been accessed after the restart.

Query all fields and then view the contents of the buffer pool

mysql> select * from member where gender=1 limit 300000,1;
+--------+------------+--------+
| id | name | gender |
+--------+------------+--------+
| 599465 | f48375bdb8 | 1 |
+--------+------------+--------+
1 row in set (0.38 sec)

mysql> select index_name,count(*) from information_schema.INNODB_BUFFER_PAGE where INDEX_NAME in('primary','gender') and TABLE_NAME like '%member%' group by index_name;
+------------+----------+
| index_name | count(*) |
+------------+----------+
| gender | 261 |
| PRIMARY | 1385 |
+------------+----------+
2 rows in set (0.06 sec)

It can be seen that at this time, there are 1385 data pages and 261 index pages for the member table in the buffer pool.

Restart MySQL to clear the buffer pool and continue testing to query only the primary key

mysql> select id from member where gender=1 limit 300000,1;
+--------+
|id|
+--------+
|599465|
+--------+
1 row in set (0.08 sec)

mysql> select index_name,count(*) from information_schema.INNODB_BUFFER_PAGE where INDEX_NAME in('primary','gender') and TABLE_NAME like '%member%' group by index_name;
+------------+----------+
| index_name | count(*) |
+------------+----------+
| gender | 263 |
| PRIMARY | 13 |
+------------+----------+
2 rows in set (0.04 sec)

It can be seen that at this time, there are only 13 data pages and 263 index pages for the member table in the buffer pool. Therefore, multiple I/O operations of accessing data blocks through primary key indexes are reduced, thereby improving execution efficiency.

Therefore, it can be confirmed that the reason why the offset is too large and affects the performance during MySQL query is the I/O operation of accessing the data block through the primary key index multiple times . ( Note that only InnoDB has this problem, and the MYISAM index structure is different from InnoDB. The secondary indexes all point directly to the data blocks, so there is no such problem ).

Comparison of InnoDB and MyISAM engine index structures

Write the picture description here

Optimization Methods

Based on the above analysis, we know that querying all fields will cause I/O operations caused by multiple accesses to data blocks by the primary key index.

Therefore, we first find out the offset primary key, and then query all the contents of the data block based on the primary key index to achieve optimization.

mysql> select a.* from member as a inner join (select id from member where gender=1 limit 300000,1) as b on a.id=b.id;
+--------+------------+--------+
| id | name | gender |
+--------+------------+--------+
| 599465 | f48375bdb8 | 1 |
+--------+------------+--------+
1 row in set (0.08 sec)

Appendix: MYSQL limit, offset difference

SELECT
  keyword
FROM
  keyword_rank
WHERE
  advertiserid='59'
order by
  keyword
LIMIT 2 OFFSET 1;

For example, in this SQL statement, the limit is followed by two records, and the offset is followed by the first record.

SELECT
  keyword
FROM
  keyword_rank
WHERE
  advertiserid='59'
ORDER BY
  keyword
LIMIT 2 ,1;

In this SQL, the limit is followed by reading from the second item, and reading 1 piece of information.

Don't confuse these two.

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. If you have any questions, you can leave a message to communicate. Thank you for your support for 123WORDPRESS.COM.