Technical Arrangement of MySQL paging principles

The following is my summary of the mysqy of the paging principle, like to optimize the paging performance of friends can refer to learning.

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

---statement style: In MySQL, you can use the following methods: SELECT * FROM table name LIMIT m,n.

---adaptation scenario: suitable for cases with a small amount of data (tuple hundred/Chi Pe).

---reason/disadvantage: Full table scan, slow and some database result set return instability (such as a return to 1,2,3, another return 2,1,3). The limit limit is to remove the n output from the m position of the result set and discard the remainder.

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

---statement style: In MySQL, you can use the following methods:

SELECT * FROM table name WHERE id_pk > (pagenum*10) LIMIT M.

---Adaptation scenario: Applies to a lot of data (thousands of tuples).

---Reason: Index scan, speed will be very fast. Some friends put forward because the data query is not sorted according to pk_id, so there will be missing data, only method 3.

　　 Method 3: Reorder based on indexes

---statement style: In MySQL, you can use the following methods: SELECT * FROM table name WHERE id_pk > (pagenum*10) Order by ID_PK ASC LIMIT M.

---Adaptation scenario: Applies to a lot of data (thousands of tuples). A Column object with the best order by is a primary key or unique so that the by operation can take advantage of the index to be eliminated but the result set is stable (see Method 1 for stable meaning).

---Reason: Index scan, speed will be very fast. But the MySQL sort operation, only ASC no DESC (desc is fake, the future will do real DESC, look forward).

　　 Method 4: Use prepare based on index (the first question mark denotes pagenum, second?) Represents the number of tuples per page)

---statement style: In MySQL, you can use the following methods:

PREPARE Stmt_name from SELECT * from table name WHERE id_pk > (? * ？ ) Order BY ID_PK

ASC LIMIT M.

---adaptation scene: large amount of data.

---Reason: Index scan, speed will be very fast. A prepare statement is a bit faster than a normal query statement.

　　 Method 5: Using the MySQL support order operation can use the index to quickly locate part of the tuple to avoid full table scan

---For example: Read the 1000th to 1019th row tuple (PK is the primary key/unique key).

---SELECT * from your_table WHERE pk>=1000 ORDER by PK ASC LIMIT 0, 20.

　　 Method 6: Use the subquery/join + Index to quickly locate the tuple, and then read the tuple. The same method 5

---such as (ID is primary key/Unique key, blue font time variable):

Using the subquery example:

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

Use the connection example:

SELECT * from your_table as T1
JOIN (SELECT ID from your_table
ID desc LIMIT ($page-1) * $pagesize as T2
WHERE
T1.id <= t2.id ORDER BY t1.id desc LIMIT $pagesize;

　　 Method 7: Stored procedure class (preferably fused above method 5/6)

---statement style: no longer given

---adaptation scene: large amount of data. Methods recommended by the authors

---Reason: it is relatively faster to encapsulate the operation on the server.

　　 Method 8: Negative Methods

---people on the Internet write using Sql_calc_found_rows. There is no reason to imitate.

Basically, you can generalize to all the databases, and the truth is the same. However, method 5 may not be extended to other databases, and the prerequisite is that other databases support an order by operation that can be sorted directly using the index.