1. Background
mysqluseselect* limit offset, rows paging in the case of depth paging.performanceSharply decline.
For example: use it directly in the case of select *limit600,000, 10 scans about 600,000 pieces of data, and requires 600,000 back to the table, which means that most of the performance is consumed on random access, and in the end, only 10 pieces of data are used (a total of 600,010 pieces of data are taken and only 10 records are left)
2. Limit grammar interpretation
limit for dataPagination query, of course, it will also be used for data interception. The following is the usage of limit:
SELECT * FROM table LIMIT [ offset,] rows | rows OFFSET offsetDeformation
The first type: SELECT * FROM table LIMIT offset, rows # Common forms
-- Starting from 0, intercept 5 records, that is, the search behaviors are 1 to 5select * from table limit 0, 5
-- Note: The two participations after the keyword limit are divided by commas
The second type: SELECT * FROM table LIMIT rows OFFSET offset
-- Starting from 0, intercept 5 records, that is, the search behaviors are 1 to 5select * from tb_account limit 5 offset 0
-- Note: Use the two keywords limit and offset, and each with one parameter, without comma splitting
The third type: SELECT * FROM table LIMIT rows
-- The first five elements of the record can be understood as the default value of offset is 0select * from tb_account limit 5
3. Optimization method
1. Imitate Baidu and Google solutions (front-end business control)
Similar to segmentation. We need to reload the next 100 pages for those who can only turn 100 pages and more than 100 pages at a time. This solves the problem of large and slow data loading each time
2. Record the maximum id each time, and then where id > maximum id
select * from table_name Where id> Maximumid limit 10000, 10;This method is suitable for: in addition to discrete fields such as primary key ID, it is also applicable to continuous field datetime and other maximum id calculated by front-end pagination pageNum and pageIndex.
3. IN get id
select * from table_name where id in ( select id from table_name where ( user = xxx )) limit 10000, 10;4. Join method + overwrite index (recommended)
select * from table_name inner join ( select id from table_name where ( user = xxx) limit 10000, 10) b using ( id)If you have the where condition and want to use limit, you must design an index, place where first, and place the primary key used by limit on the second, and you can only select the primary key!
select id from test where pid = 1 limit 100000, 10;Create an index:
alter table test add index idx_pid_id(pid, id)4. Case
1. How to use jdbcpagingReader
//MySqlPagingQueryProvider#public static String generateLimitSqlQuery(AbstractSqlPagingQueryProvider provider, boolean remainingPageQuery,
String limitClause) {
StringBuilder sql = new StringBuilder();
( "SELECT ").append(());
( " FROM ").append(());
buildWhereClause(provider, remainingPageQuery, sql);
buildGroupByClause(provider, sql);
( " ORDER BY ").append(buildSortClause(provider));
( " " + limitClause);
return ();
}
❝Interpretation: The writing method of limit 10 is used in jdbcPageingreader. The default is to find 10 records. Equivalent to limit 0, 10
❞
2. How to use the db index partitioner
Enter parameter 1: Table name such as test_table
Enter parameter 2: Sort index field can be a primary key or other index. It needs to be guaranteed to be the unique index. Such as: id
Entering parameter 3: The primary key can be passed in manually or can be calculated based on the table name: only single-column primary keys are supported now. Such as: id
Input parameter 4: How many pieces should be divided into the specific table. Such as: 4
-- Usage process 1. Calculate how much data is firstselect count( 1) as countAllNumber from test_table; -- countAllNumber=200
-- 2.
-- countAllNumber /4 =200/4 =50; In other words, the data volume of each block needs to contain 50 data. You need to calculate the start and end nodes of these 50 data
-- 3. The loop traversal calculates the first block according to the self-increase of the primary key.
-- 3.1 The first block start node is 0
select id from test_table where id >= 0 order by id limit50, 1; -- Calculate the 51st element, if only 51; the range of the first block is [0, 51); close left and open right
-- 3.2 The second block, the starting node is 51
select id from test_table where id >= 51 limit50, 1; -- Calculate the 101st element, such as 101; the range of the second block is [51, 101); close left and open right
-- 3.3 The third block is similar, and the boundary point of the third block is calculated to be 151.
select id from test_table where id>= 151; -- Calculate the range of the fourth block as [151, +∞); close left and open right
Use: Get the block boundary value of each block. Do primary key search interface。
For example, the first block has a boundary value of [0, 51);
Then the scribing query SQL is . The required entry parameters are table name, index name, partition start, partition end
select id from test_table where id >= 0 and id < 51 order by id