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

This article is about ways to improve the performance of MySQL Limit queries. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

In MySQL database operations, when we do some queries, we always want to avoid full table scans by the database engine, because full table scans take a long time, and most of them are meaningless to the client. In fact, we can use the Limit keyword to avoid full table scanning, thus improving efficiency.

There is a table on MySQL 5.0.x with tens of millions of records, and now we have to read out hundreds of millions of records. Common methods, cycle in turn:

Select * from mytable where index_col = xxx limit offset, limit

Lesson: if there is no blob/text field, the single-line record is relatively small, you can make the limit larger, which will speed up.

Problem: the first tens of thousands of entries are read very fast, but the speed decreases linearly, while mysql server cpu 99%, the speed is unacceptable.

Call explain select * from mytable where index_col = xxx limit offset, and limit; displays type = ALL

Write an explanation of "All" in the MySQL optimization document

A full table scan is done for each combination of rows from the previous tables. This is normally not good if the table is the first table not marked const, and usually very bad in all other cases. Normally, you can avoid ALL by adding indexes that allow row retrieval from the table based on constant values or column values from earlier tables.

It seems that mysql uses a stupid method for all, so use range instead? Because id is incremental, it is also easy to modify sql.

Select * from mytable where id > offset and id

< offset + limit and index_col = xxx explain 显示 type = range，结果速度非常理想，返回结果快了几十倍。 Limit语法： SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset LIMIT子句可以被用于强制 SELECT 语句返回指定的记录数。LIMIT接受一个或两个数字参数。参数必须是一个整数常量。 如果给定两个参数，第一个参数指定第一个返回记录行的偏移量，第二个参数指定返回记录行的最大数目。初始记录行的偏移量是 0(而不是 1)。 为了与 PostgreSQL 兼容，MySQL 也支持句法：LIMIT # OFFSET #。 mysql>

SELECT * FROM table LIMIT 5FROM table LIMIT 10; / retrieve record rows 6-15 LIMIT / in order to retrieve all record rows from a certain offset to the end of the recordset, you can specify the second parameter as-1mysql > SELECT * FROM table LIMIT 95 last//; / / retrieve record lines 96-FROM table LIMIT if given only one parameter, it represents the maximum number of record rows returned, in other words, record n is equivalent to LIMIT 0ql > SELECT * FROM table LIMIT 5 / / retrieve the first five record lines

MySQL's limit brings great convenience to paging, but when the amount of data is large, the performance of limit drops sharply. With the same 10 pieces of data, the following two sentences are not of the same quantitative level.

Select * from table limit 10000 Magazine 10 select * from table limit 0Magol 10

In this paper, instead of using limit directly, we first get the id of offset and then directly use limit size to get the data. According to his data, it is obviously better than using limit directly.

Here I use the data to test in two cases.

1. When offset is relatively small:

Select * from table limit 10 run 10 / / multiple times, time keeps between 0.0004-0.0005 Select * From table Where vid > = (Select vid From table Order By vid limit 10 ~ 1) limit 10 / / runs multiple times, time remains between 0.0005-0.0006, mainly 0.0006

Conclusion: when the offset offset is small, it is better to use limit directly. This is obviously the reason for the subquery.

2. When offset is big:

Select * from table limit 10000 From table Where vid 10 / / run many times, the time is about 0.0187 Select * From table Where vid > = (Select vid From table Order By vid limit 10000Magneol 1) limit 10pm / multiple times, the time is about 0.0061, only the first one is 3. It can be expected that the larger the offset, the better the latter. Thank you for reading! This is the end of this article on "how to improve the performance of MySQL Limit queries". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it out for more people to see!

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.