MySQL paging query method for millions of data volumes and its optimization suggestions

Database SQL optimization is a common problem. When faced with paging queries on millions of data volumes, what are some good optimization suggestions? Some commonly used methods are listed below for your reference and learning!

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

• Statement style: In MySQL, you can use the following method: SELECT * FROM table name LIMIT M,N
• Applicable scenarios: Suitable for situations with small amounts of data (hundreds or thousands of tuples)
• Reason/disadvantage: Full table scan is very slow and some database result sets are unstable (e.g., 1, 2, 3 is returned in one time, and 2, 1, 3 is returned in another time). Limit 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 and use the index (assuming 10 entries per page)

• Statement style: In MySQL, the following method can be used: SELECT * FROM table name WHERE id_pk > (pageNum*10) LIMIT M
• Applicable scenarios: Suitable for situations with large amounts of data (tens of thousands of tuples)
• Reason: Index scanning will be very fast. Some friends pointed out that because the data is not sorted by pk_id, there will be cases where data is missed. Only method 3 can be used.
  

Method 3: Re-sort based on index

• Statement style: In MySQL, the following method can be used: SELECT * FROM table name WHERE id_pk > (pageNum*10) ORDER BY id_pk ASC LIMIT M
• Applicable scenarios: Applicable to situations with large amounts of data (tens of thousands of tuples). It is best if the column object after 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 scanning is very fast. However, MySQL sorting only has ASC and no DESC (DESC is fake, and will be a real DESC in the future, looking forward to it...).
  

Method 4: Use prepare based on index

The first question mark represents pageNum, and the second? Indicates the number of tuples per page

• Statement style: In MySQL, the following method can be used: PREPARE stmt_name FROM SELECT * FROM table name WHERE id_pk > (? * ?) ORDER BY id_pk ASC LIMIT M
• Applicable scenario: large amount of data
• Reason: Index scanning is very fast. Prepare statements are faster than general query statements.
  

Method 5: Using MySQL to support ORDER operations, you can use indexes to quickly locate some tuples and avoid full table scans.

For example: read tuples from row 1000 to 1019 (pk is the primary key/unique key).

SELECT * FROM your_table WHERE pk>=1000 ORDER BY pk ASC LIMIT 0,20

Method 6: Use subquery/join + index to quickly locate the tuple, and then read the tuple.

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

Example using subquery:

SELECT * FROM your_table WHERE id <=
(SELECT id FROM your_table ORDER BY id desc LIMIT ($page-1)*$pagesize ORDER BY id desc
LIMIT $pagesize

Example of using connection:

SELECT * FROM your_table AS t1
JOIN (SELECT id FROM your_table ORDER BY id desc LIMIT ($page-1)*$pagesize AS t2
WHERE t1.id <= t2.id ORDER BY t1.id desc LIMIT $pagesize;

MySQL uses limit paging for large amounts of data. As the page number increases, the query efficiency decreases.

Test experiment

1. Directly use limit start, count paging statements, which is also the method used in my program:

select * from product limit start, count

When the starting page is small, there is no performance problem with the query. Let's take a look at the execution time when paging starts from 10, 100, 1000, and 10000 (20 records per page).

as follows:

select * from product limit 10, 20 0.016 seconds select * from product limit 100, 20 0.016 seconds select * from product limit 1000, 20 0.047 seconds select * from product limit 10000, 20 0.094 seconds

We have seen that as the number of starting records increases, the time also increases. This shows that the paging statement limit is closely related to the starting page number. So let's change the starting record to 40w (which is about half of the records).

select * from product limit 400000, 20 3.229 seconds

Let's look at the time when we took the last page of records.

select * from product limit 866613, 20 37.44 seconds

For a page with the largest pagination number like this, this time is obviously unbearable.

We can also conclude two things from this:

1. The query time of the limit statement is proportional to the position of the starting record
2. The MySQL limit statement is very convenient, but it is not suitable for direct use for tables with many records.
   

2. Performance optimization method for limit paging problem

Use the table's covering index to speed up paging queries

We all know that if the statement that uses an index query only contains that index column (covering index), the query will be very fast.

Because index search has an optimized algorithm and the data is on the query index, there is no need to look for the relevant data address, which saves a lot of time. In addition, MySQL also has related index cache, and the effect will be better if the cache is used when the concurrency is high.

In our example, we know that the id field is the primary key, so the default primary key index is included. Now let's see how queries that take advantage of covering indexes perform.

This time we query the data of the last page (using a covering index, which only contains the id column), as follows:

select id from product limit 866613, 20 0.2 seconds

Compared to the 37.44 seconds it takes to query all columns, the speed is increased by more than 100 times.

If we also want to query all columns, there are two ways, one is id>= form, the other is to use join, see the actual situation:

SELECT * FROM product WHERE ID > =(select id from product limit 866613, 1) limit 20

The query time is 0.2 seconds!

Another way to write

SELECT * FROM product a JOIN (select id from product limit 866613, 20) b ON a.ID = b.id

The query time is also very short!

3. Composite index optimization method

How high can MySql performance be? The MySql database is definitely suitable for DBA-level experts. Generally, you can write a small system of 10,000 news articles in any way, and use the xx framework to achieve rapid development. But when the amount of data reaches 100,000, 1 million or 10 million, can its performance still be so high? A small mistake may cause the entire system to be rewritten, or even the entire system to fail to operate normally! Okay, no more nonsense.

Let's talk facts, see examples:

The data table collect (id, title, info, vtype) has only these four fields, among which title uses fixed length, info uses text, id is gradual, vtype is tinyint, and vtype is an index. This is a simple model of a basic news system. Now fill it with data, fill it with 100,000 news articles. The final collection contains 100,000 records, and the database table occupies 1.6G of hard disk space.

OK, look at the following sql statement:

select id,title from collect limit 1000,10;

Very fast; basically 0.01 seconds is OK, see the following

select id,title from collect limit 90000,10;

Start paging from 90,000 records. What’s the result?

Completed in 8-9 seconds, my god what went wrong? In fact, if you want to optimize this data, you can find the answer online. Look at the following statement:

select id from collect order by id limit 90000,10;

Very fast, 0.04 seconds is OK. Why? Because the id primary key is used as the index, it is of course fast. The online modification method is:

select id,title from collect where id>=(select id from collect order by id limit 90000,1) limit 10;

This is the result of using id as index. But if the problem is just a little bit more complicated, it's over. Look at the following statement

select id from collect where vtype=1 order by id limit 90000,10;

Very slow, took 8-9 seconds!

At this point, I believe many people will feel like I did, and feel overwhelmed! Is vtype indexed? How could it be slow? It is good that vtype is indexed.

select id from collect where vtype=1 limit 1000,10;

It is very fast, basically 0.05 seconds, but if it is increased by 90 times, starting from 90,000, the speed will be 0.05*90=4.5 seconds. And the test results are 8-9 seconds to an order of magnitude.

From here, some people proposed the idea of ​​splitting the tables, which is the same idea as in the dis #cuz forum. The idea is as follows:

Create an index table: t (id, title, vtype) and set it to a fixed length, then do paging, paginate the results and then find info in collect. Is it feasible? You will know after experimenting.

100,000 records are stored in t(id, title, vtype), and the data table size is about 20M. use

select id from collect where vtype=1 limit 1000,10;

It will be soon. Basically it can be completed in 0.1-0.2 seconds. Why is this happening? I guess it's because there is too much collect data, so the paging takes a long time. The limit is completely related to the size of the data table. In fact, this is still a full table scan, but it is fast because the amount of data is small, only 100,000. OK, let’s do a crazy experiment, add 1 million records and test the performance. After adding 10 times the data, the t table immediately reached more than 200M, and it was a fixed length. It is still the same query statement, and it takes 0.1-0.2 seconds to complete! Is the performance of the sub-table OK?

wrong! Because our limit is still 90,000, so it’s fast. Give a big one, starting at 900,000

select id from t where vtype=1 order by id limit 900000,10;

Look at the result, the time is 1-2 seconds! why ?

It takes so long even after dividing the table, which is very depressing! Some people say that fixed length will improve the performance of limit. At first, I also thought that since the length of a record is fixed, MySQL should be able to calculate the 900,000 position, right? But we overestimated the intelligence of MySQL. It is not a business database. Facts have proved that fixed length and non-fixed length have little effect on limit? No wonder some people say that discuz will be very slow when it reaches 1 million records. I believe this is true. This is related to database design!

Is it possible that MySQL cannot break the 1 million limit? ? ? Is 1 million pages really the limit?

The answer is: NO. The reason why it cannot exceed 1 million is because you don’t know how to design MySQL. Here is the non-table method, let’s do a crazy test! How to quickly paginate a table with 1 million records and a 10G database!

OK, our test returns to the collect table, and the conclusion of the test is:

For 300,000 pieces of data, it is feasible to use the table partitioning method. However, if the data exceeds 300,000, the speed will be so slow that you will not be able to bear it! Of course, if you use a split table + my method, it would be absolutely perfect. But after using my method, the problem can be solved perfectly without dividing the table!

The answer is: composite index! Once when I was designing a MySQL index, I accidentally discovered that the index name can be arbitrarily chosen, and several fields can be selected. What is the use of this?

Start

select id from collect order by id limit 90000,10;

It is so fast because the index is used, but if the where clause is added, the index will not be used. I added an index like search(vtype,id) just to give it a try.

Then test

select id from collect where vtype=1 limit 90000,10;

Very fast! Completed in 0.04 seconds!

Retest:

select id ,title from collect where vtype=1 limit 90000,10;

It’s a pity that it took 8-9 seconds and the search index was not used!

Test again: search(id,vtype), still select id, unfortunately, it takes 0.5 seconds.

To sum up: if you have a where condition and want to use the index to use limit, you must design an index, put where first, the primary key used for limit second, and you can only select the primary key!

Perfect solution to the paging problem. If the ID can be returned quickly, there is hope for optimizing the limit. According to this logic, a limit of millions should be divided in 0.0x seconds. It seems that the optimization and indexing of MySQL statements are very important!

The above is the details of MySQL paging query method for millions of data volumes and its optimization suggestions. For more information about MySQL paging query and its optimization, please pay attention to other related articles on 123WORDPRESS.COM!