Tips for optimizing the paging performance of one million data records in a single MySQL table

Tips for optimizing the paging performance of one million data records in a single MySQL table

Test environment:

Let's familiarize ourselves with the basic SQL statements to view the basic information of the tables we want to test.

use infomation_schemaSELECT * FROM TABLES WHERE TABLE_SCHEMA = ‘dbname' AND TABLE_NAME = ‘product'

Query results:

We can see the basic information of the table:

Number of table rows: 866633
Average data length per line: 5133 bytes
Single table size: 4448700632 bytes

The row and table size are measured in bytes.
Average line length: About 5 k
Total size of a single table: 4.1 GB
Various types of fields in the table include varchar, datetime, and text. The id field is the primary key.

Test lab

1. directly use the limit start and count paging statements, which is also used in our program:

Select * from product limit start, count
When the start page is small, there is no performance problem in the query. Let's take a look at the execution time from 10,100,100 0 to 10000 (20 entries per page), as shown below:

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 starting record increases, the time also increases, which indicates that the paging statement limit has a great relationship with the starting page number, so let's change the Starting record to 40 W (that is, the record is generally about) select * from product limit 400000, 20 3.229 seconds

Let's look at the time when we retrieved the last record.
Select * from product limit 866613, 20 37.44 seconds

It's no wonder that the search engine often reports timeout when crawling our pages, such as the largest page number.
It is intolerable.

We can also summarize two things:
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 directly using many tables that are recorded.

2. Performance Optimization Methods for limit paging Problems

Use the covered index of the table to accelerate paging Query
We all know that if an index query statement contains only the index column (covering the index), the query will be very fast.

Because there are optimization algorithms using index search, and the data is on the query index, there is no need to find the relevant data address, which saves a lot of time. In addition, Mysql also has the relevant index cache, which improves the performance when the concurrency is high.

In our example, we know that the id field is the primary key and naturally contains the default primary key index. Now let's take a look at how the query results are covered by indexes:

This time, we query the data on the last page (covering the index and containing only the id column), as shown below:
Select id from product limit 866613, 20 0.2 seconds
The query speed is increased by about 37.44 times compared to the 100 s of all columns.

If we want to query all columns, there are two methods: id> =, and join. Let's look at the actual situation:

SELECT * FROM product where id> = (select id from product limit 866613, 1) limit 20
The query time is 0.2 seconds, which is a qualitative leap. Haha

Another method
SELECT * FROM product a JOIN (select id from product limit 866613, 20) B ON a. ID = B. id
Query time is short, like!

In fact, both use one principle, so the effect is similar.