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

This article mainly introduces "how to optimize the problem of large amount of data and slow paging query". In daily operation, I believe many people have doubts about how to optimize the problem of large amount of data and slow paging query. The editor consulted all kinds of data and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the question of "how to optimize the problem of large amount of data and slow paging query"! Next, please follow the editor to study!

Preparatory work

General paging query

Using subquery optimization

Use id to qualify optimization

Using temporary table optimization

Id description of the datasheet

When there are tens of thousands of records in a table that needs to be queried from the database, querying all the results at once will become very slow, especially as the amount of data increases, so you need to use a paged query. For database paging queries, there are also many methods and optimization points. Let's briefly talk about some of the methods I know.

Preparatory work

To test some of the optimizations listed below, here is an illustration of an existing table.

Table name: order_history

Description: order history table for a business

Main field: unsigned int id,tinyint (4) int type

Field situation: the table has a total of 37 fields, excluding large data such as text, the maximum is varchar, and the id field is indexed and incremented.

Amount of data: 5709294

MySQL version: 5.7.16 offline to find a million-level test table is not easy, if you need to test yourself, you can write shell scripts to insert data for testing. The environment in which all of the following sql statements are executed remains unchanged. Here are the basic test results:

Select count (*) from orders_history

Returned result: 5709294

The time for the three queries are as follows:

8903 ms

8323 ms

8401 ms

General paging query

A general paging query can be implemented using a simple limit clause. The limit clause is declared as follows:

SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset

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

The first parameter specifies the offset of the first returned record row, starting with 0

The second parameter specifies the maximum number of record rows returned

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

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

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

Here is an example of an application:

Select * from orders_history where type=8 limit 1000, 10

This statement will query the table orders_history for 10 pieces of data starting from offset: 1000, that is, items 1001 to 1010 (1001 = 1000001 limit 1000)

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:

Select * from orders_history where id > = 1000001 limit 100

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

Using temporary table optimization

This approach is no longer part of query optimization, which is incidentally mentioned here.

For problems in using id to limit optimization, id is required to be incremented continuously, but in some scenarios, such as when using history tables, or when there is a problem of missing data, you can consider using temporarily stored tables to record paged id and paging id for in queries. This can greatly improve the speed of traditional paging query, especially when the amount of data is tens of millions.

Id description of the datasheet

In general, when you create a table in a database, you force the addition of id increment fields for each table, which facilitates the query.

If the amount of data, such as an order database, is very large, a sub-database and sub-table will generally be carried out. At this time, it is not recommended to use the id of the database as the unique identity, but should use a distributed highly concurrent unique id generator to generate it, and use additional fields in the data table to store this unique identity.

Using a range query to locate the id (or index), and then using the index to locate the data, can increase the query speed several times. That is, first select id, then select *

At this point, the study on "how to optimize the problem of large amount of data and slow paging query" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.