前提条件:
1.表的唯一索引
2.百万级数据
SQL语句:
select c.* FROM ( SELECT a.logid FROM tableA a where 1 = 1 <#if phone?exists&& phone!=""> AND a.phone like "%":phone"%" </#if> ORDER BY a.create_time DESC limit :startIndex,:maxCount ) b,tableA c where 1 = 1 AND b.logid = c.logid
其中:
1:startIndex:表示查找数据的开始位置
2:maxCount:表示每页显示数据个数
3:a.create_time DESC:降序排列,需要在create_time建立索引
4:limiit放在里面,而不要放在查询的外面,这样效率提升很多
5:logid:唯一索引
下次试试这个:
select a.userid,b.nick,b.classid,b.writetime from ( select top 20 a.userid from ( select top 90000 userid from t_userinfo where classid = 1 order by userid asc ) a order by a.userid desc ) a inner join t_userinfo b on a.userid = b.userid order by a.userid asc
还有这个,也可以试试:
SELECT * FROM bas_table a WHERE id >= (SELECT id FROM test.bas_table LIMIT 400000 , 1) LIMIT 1000;
SELECT * FROM bas_table a INNER JOIN (SELECT id FROM test.bas_table LIMIT 400000 , 1000) b ON a.id = b.id;