Did you know that MySQL Limit has performance issues?

MySQL's paging query is usually implemented through limit.

MySQL's limitThe basic usage is very simple. limitReceives 1 or 2 integer parameters. If there are 2 parameters, the first one specifies the offset of the first returned record row, and the second one specifies the maximum number of returned record rows. The offset of the initial record row is 0.

For compatibility with PostgreSQL, limit also supports limit # offset #.

Problem:

For small offsets, there is no problem in directly using limit to query, but as the amount of data increases, , the farther back the page is paged, the greater the offset of the limit statement will be, and the speed will be significantly slower.

Optimization ideas:

Avoid scanning too many records when the amount of data is large

Solution :

Subquery paging method or JOIN paging method.

The efficiency of JOIN paging and subquery paging are basically at the same level, and the time consumed is basically the same.

Here’s an example. Generally, the primary key of MySQL is an auto-incrementing numeric type. In this case, the following method can be used for optimization.

Take a table with 800,000 pieces of data in a real production environment as an example to compare the query time before and after optimization:

 -- 传统limit，文件扫描 [SQL]SELECT * FROM tableName ORDER BY id LIMIT 500000,2; 受影响的行: 0 时间: 5.371s -- 子查询方式，索引扫描 [SQL] SELECT * FROM tableName WHERE id >= (SELECT id FROM tableName ORDER BY id LIMIT 500000 , 1) LIMIT 2; 受影响的行: 0 时间: 0.274s -- JOIN分页方式 [SQL] SELECT * FROM tableName AS t1 JOIN (SELECT id FROM tableName ORDER BY id desc LIMIT 500000, 1) AS t2 WHERE t1.id <= t2.id ORDER BY t1.id desc LIMIT 2; 受影响的行: 0 时间: 0.278s

You can see that the performance has improved by nearly 20% after optimization times.

Optimization principle:

Subquery is completed on the index, while ordinary query is completed on the data file. Generally speaking, the index file is larger than The data files are much smaller, so operations will be more efficient. Because to retrieve all the field contents, the first method needs to span a large number of data blocks and retrieve them, while the second method basically directly locates according to the index field before retrieving the corresponding content, so the efficiency is naturally greatly improved.

Therefore, the optimization of limit is not to use limit directly, but to first obtain the offset id, and then directly use limit size to get data.

In actual project use, you can use a method similar to the strategy mode to handle paging. For example, if there are 100 pieces of data per page, if it is judged to be within 100 pages, use the most basic paging method. If it is greater than 100, use The subquery pagination method.

For more MySQL related technical articles, please visit the MySQL Tutorial column to learn!

The above is the detailed content of Did you know that MySQL Limit has performance issues?. For more information, please follow other related articles on the PHP Chinese website!