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

Today, I will talk to you about how to optimize the paging query in MySQL. Many people may not know much about it. In order to make you understand better, the editor has summarized the following content for you. I hope you can get something according to this article.

Paging query method:

In MySQL, paging queries are generally implemented using the limit clause, which is declared as follows:

The LIMIT clause can be used to specify the number of records returned by the SELECT statement. Pay attention to the following points:

1. The first parameter specifies the offset of the first returned record row

2. The second parameter specifies the maximum number of record rows returned.

3. If only one parameter is given: it represents the maximum number of rows of records returned

4. The second parameter-1 means to retrieve all record rows from a certain offset to the end of the recordset

5. The offset of the initial record row is 0 (not 1).

Here is an example of an application:

This statement will query the table orders_history for the 10 pieces of data after item 1000, that is, items 1001 to 1010.

Records in the data table are sorted using the primary key (usually id) by default, and the above result is equivalent to:

The time for the three queries are as follows:

3040 ms

3063 ms

3018 ms

For this query method, the following tests the impact of the number of query records on time:

The time for the three queries is as follows:

Query 1 record: 3072ms 3092ms 3002ms

Query 10 records: 3081ms 3077ms 3032ms

Query 100 records: 3118ms 3200ms 3128ms

Query 1000 records: 3412ms 3468ms 3394ms

Query 10000 records: 3749ms 3802ms 3696ms

In addition, I have done more than ten queries. From the point of view of the query time, it is basically certain that when the number of query records is less than 100, there is basically no difference in query time. As the number of query records becomes larger and larger, it will take more and more time.

Tests for query offsets:

The time for the three queries is as follows:

Query 100offset: 25ms 24ms 24ms

Query 1000 offset: 78ms 76ms 77ms

Query 10000 offset: 3092ms 3212ms 3128ms

Query 100000 offset: 3878ms 3812ms 3798ms

Query 1000000 offset: 14608ms 14062ms 14700ms

With the increase of query offset, especially when the query offset is greater than 100000, the query time increases sharply.

This paging query starts with the first record in the database, so the later, the slower the query speed, and the more data you query, the slower the overall query speed.

Using subquery optimization

This method first locates the id of the offset position, and then queries it back, which is suitable for the case where the id is incremented.

The query time of the four statements is as follows:

The first statement: 3674ms

Clause 2: 1315ms

Clause 3: 1327ms

Clause 4: 3710ms

Note that for the above query:

1. Compare the first statement with the second statement: the speed of using select id instead of select * is increased by 3 times.

2. Compare the second statement with the third statement: the speed difference is tens of milliseconds.

3. Compare the third statement with the fourth statement: thanks to the increase in the speed of select id, the query speed of the third statement is increased by three times.

This method will be several times faster than the original general query method.

Use id to qualify optimization

This method assumes that the id of the data table is continuously increasing, then we can calculate the range of the id of the query based on the number of pages and records of the query, and we can use id between and to query:

Query time: 15ms 12ms 9ms

This query method can greatly optimize the query speed and can be completed in tens of milliseconds. The limitation is that it can only be used when you clearly know id, but usually when you create a table, you add basic id fields, which brings a lot of convenience to paging queries.

There can be another way to write it:

Of course, you can also use in to query, which is often used to query when multiple tables are associated, and to use the id collection of other table queries:

Note that some versions of mysql do not support the use of limit in the in clause in this in query.

Id description of the datasheet

In general, when creating tables in the database, each table is forced to add id incremental fields, which makes it more convenient for us to query the data.

If there is a large amount of data, such as orders, it is generally recommended to split the database and table. At this point, id is not recommended as a unique identity, but should be generated using a distributed, highly concurrent unique id generator, and use additional fields in the data table to store this unique identity.

First use a range query to locate the id (or index), and then use the index to locate the data, that is, first select id, and then in select *; in this way, the query speed will be increased several times.

After reading the above, do you have any further understanding of how to optimize paging queries in MySQL? If you want to know more knowledge or related content, please follow the industry information channel, 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.