mysql jdbc 分页查询的坑

一. 目前情况（支付表4600条数据 用户表才7000条数据） 阿里云云数据库

1.

select t.*,a.nickname,a.avatar from t_pay_detail_zhufu t left join t_member_zhufu a on a.openid=t.openid where t.paytype='weixin'   order by t.id desc 
耗费7秒

2.

select t.*,a.nickname,a.avatar from t_pay_detail_zhufu t left join t_member_zhufu a on a.openid=t.openid where t.paytype='weixin' order by t.id desc limit 200,10
耗费6.7秒

3.

select t.* from t_pay_detail_zhufu t order by t.id desc limit 2000,10

耗费0.029秒

4.造数据（支付表6389760条数据 用户表才7000条数据）

select t.* from t_pay_detail_zhufu t where id>4253405 LIMIT 10 （花费0.032秒）
select t.* from t_pay_detail_zhufu t order by t.id desc limit 10,10 （花费0.035秒）

select t.* from t_pay_detail_zhufu t order by t.id desc limit 2000000,10 （花费8.486秒）

select t.id from t_pay_detail_zhufu t order by t.id desc limit 6000000,10（花费33秒）

select t.* from t_pay_detail_zhufu t order by t.id desc limit 6000000,10（花费34秒）

重点

select a.* from (
   select id from t_pay_detail_zhufu 
   where id>3000000 and trade_no like '8888%' order by id limit 10
) a
left join t_pay_detail_zhufu  b on a.id=b.id

耗时0.053秒

select a.*,(select r.nickname from t_member_zhufu  r WHERE r.openid=a.openid) as nickname,(select r.avatar from t_member_zhufu  r WHERE r.openid=a.openid) as avatar from (
   select * from t_pay_detail_zhufu 
   where id>3000000 and trade_no like '8888%' order by id desc limit 10
) a

耗时 0.153秒

4.造数据（支付表817438条数据 用户表才7000条数据）

 
select a.id,b.*,(select r.nickname from t_member_zhufu  r WHERE r.openid=b.openid) as nickname,(select r.avatar from t_member_zhufu  r WHERE r.openid=b.openid) as avatar from (
   select id from t_pay_detail_zhufu 
   where id>300000 and trade_no like '8888%' order by id limit 10
) a
left join t_pay_detail_zhufu  b on a.id=b.id

耗时0.195秒 

二.问题追踪;

PERCONA PERFORMANCE CONFERENCE 2009上，来自雅虎的几位工程师带来了一篇”EfficientPagination Using MySQL”的报告 limit10000,20的意思扫描满足条件的10020行，扔掉前面的10000行，返回最后的20行，问题就在这里。

三.解决方法

三.实际尝试用户表by2022.1.13

t_member_zhufu 共11911行

实验基本情况：阿里云dms 管理工具 ，以及mysql 55元一个月

A.通过接口查【自己写的】

例如谷歌浏览器共计2.9 MB    16.95 s

.cn/lian/select_all_table?table=t_member_zhufu&result=hxid,nickname,posttime,LASTTIME,AVATAR&idname=1&id=1&orderid=hxid&orderby=desc

B.去查
SELECT  * from `t_member_zhufu` where `posttime` >'2020-06-09 12:34:11' ORDER BY `hxid` desc 约360ms

c.优化查询

SELECT  count(hxid) as num from `t_member_zhufu` where `posttime`>'2020-06-09 12:34:11'

约70ms

select a.hxid,b.* from (
SELECT  hxid from `t_member_zhufu` where `posttime` >'2020-06-09 12:34:11' ORDER BY `hxid` desc limit 1,10
) a
left join t_member_zhufu  b on a.hxid=b.hxid

约65ms

四,附言

参考https://www.jb51.net/article/85312.htm