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

Preface

As we all know, mysql query uses select command with limit,offset parameters to read records in a specified range, but the reason why offset affects query performance too much and how to optimize it

It is inevitable that we need paging in our business system. When you think of paging, you will definitely think of using LIMIT in SQL. However, if you use LIMIT incorrectly, it will cause performance problems (SQL executes slowly and may bring down the server), and will also be approved by the leader; so let's take a look at how to use LIMIT correctly.

I won't say much. Let's take a look at the detailed introduction.

LIMIT OFFSET, ROW_COUNT to achieve paging

Ways in which there are performance problems

SELECT * FROM myTable ORDER BY `id` LIMIT 1000000, 30

The person who wrote the SQL statement must have thought that the MySQL database would navigate directly to the 1000000th bit that meets the criteria and then fetch another 30 pieces of data.

In practice, however, MySQL doesn't work this way.

LIMIT 1000000, 30 means: scan the 1000030 rows that meet the criteria, throw away the first 1000000 rows, and then return the last 30 rows.

A better way

SELECT t.*FROM (SELECT id FROM myTable ORDER BY id LIMIT 1000000, 30) qJOIN myTable tON t.id = q.id

The general principle is:

The subquery only uses index columns and does not take the actual data, so it does not involve disk IO, so even if it is a larger offset, the query speed will not be too bad.

Friends who are interested in specific principle analysis can take a look at this article: MySQL ORDER BY / LIMIT performance: late row lookups

Postscript

to be continued.

references

Why does MYSQL higher LIMIT offset slow the query down?MySQL ORDER BY / LIMIT performance: late row lookups

Summary

The above is the whole content of this article, I hope that the content of this article has a certain reference and learning value for your study or work, if you have any questions, you can leave a message and exchange, thank you for your support.

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.