数据库中存了3000W条数据,两种分页查询测试时间
--------------------------------------------------------
第一种
SELECT * FROM test_table WHERE i_id>
1000 limit 100;
Time: 0.016s
第二种
SELECT * FROM
test_table limit 100 OFFSET
1000;
Time: 0.003s
--------------------------------------------------------
第一种
SELECT * FROM
test_table WHERE i_id>
10000 limit 100;
Time: 0.004s
第二种
SELECT * FROM
test_table limit 100 OFFSET
10000;
Time: 0.508s
第一种:
SELECT * FROM
test_table WHERE i_id>
100000 limit 100;
Time: 0.003s
第二种:
SELECT * FROM
test_table limit 100 OFFSET 100000;
Time: 2.377s
SELECT * FROM
test_table WHERE c_act='登录' limit 100 OFFSET 100000;
Time: 3.649s
--------------------------------------------------------
第一种:
SELECT * FROM
test_table WHERE i_id>
1000000 limit 100;
Time: 0.004s
第二种:
SELECT * FROM
test_table limit 100 OFFSET
1000000;
Time: 14.403s
第一种:
SELECT * FROM
test_table WHERE i_id>
10000000 limit 100;
Time: 0.057s
第二种:
失去等待出来结果的耐心!!!
--------------------------------------------------------
建议使用第一种分页方法
mark: 按时间顺序倒序分页(新插入的id大的数据在前)
select * from table1 where id > (total_row_num - page_size*page_num) order by time_column desc limit 10
转自: https://blog.csdn.net/qq924862077/article/details/48710247