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

In this issue, the editor will bring you about how to achieve millions of data paging query in MySQL. The article is rich in content and analyzes and describes for you from a professional point of view. I hope you can get something after reading this article.

Method 1: directly use the SQL statement provided by the database

Statement style: the following methods are available in MySQL:

Select * from table_name limit m, n

Applicable scenarios: suitable for situations with a small amount of data (tuple 100 / 1000)

Reason / disadvantage: full table scan, the speed will be very slow and some database result sets return unstable (for example, one time returns 1 minute 2, another returns 2, and the other returns 2). What limit restricts is to take n outputs from the m position of the result set and discard the rest.

Method 2: create a primary key or unique index, using the index (assuming 10 items per page)

Statement style: in MySQL, the following methods are available:

Select * from table_name where id_pk > (pageNum*10) limit m

Adapt to scenarios: suitable for situations with a large amount of data (tens of thousands of tuples)

Reason: index scan, the speed will be fast. A friend put forward: because the data query is not sorted according to pk_id, so there will be omitted data, only method 3

Method 3: re-sort based on index

Statement style, the following methods are available in MySQL:

Select * from table_name where id_pk > (pageNum * 10) order by id_pk asc limit m

Adaptive scenario: suitable for situations with a large amount of data (tens of thousands of tuples). It is better that the column object after the order by is the primary key or unique so that the order by operation can be eliminated using the index but the result set is stable (for the meaning of stability, see method 1)

Reason: index scan, the speed will be fast. But the sorting operation of MySQL, only asc without desc (desc is fake, will do real desc in the future, look forward to …) .

Method 4: use prepare based on index

The first question mark indicates pageNum and the second question mark indicates the number of tuples per page

Statement style, the following methods are available in MySQL:

Prepare stmt_name from select * from table_name where id_pk > (? Order by id_pk asc limit m

Adapt to the scenario: large amount of data

Reason: index scan, the speed will be fast. The prepare statement is a little faster than the general query statement.

Method 5: using MySQL to support order operation, you can use index to quickly locate some meta-ancestors and avoid full table scanning.

For example: read lines 1000 to 1019 tuples (competition is primary key / unique key).

Select * from your_table where pa > = 1000 order by competes for asc limit 0jie 20

Method 6: use "subquery / join + index" to quickly locate the location of the meta-ancestor, and then read the meta-ancestor.

For example (id is primary key / unique key, variable for blue font)

Examples of using subqueries:

Select * from your_table where id

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.