ssh大数据量（800W左右）分页 求源码或思路（希望真正有经验的人帮帮忙）

[longzl123]

此种方法行不通

public Result findAllAndSplitPage(Page page,TCustomerInfo customer) throws ParseException {
long startTime=System.currentTimeMillis();

StringBuffer sb = new StringBuffer();//查询记录条数，用于分页
sb.append(" select count(s.ID) ");
sb.append(" FROM ");
sb.append(" `T_CustomerInfo` s ");
sb.append(" where 1=1");
if(null!=customer){
if(StringUtils.hasText(customer.getMinId())){
sb.append(" and s.ID >= :minid" );
}
if(StringUtils.hasText(customer.getMaxId())){
sb.append(" and s.ID <= :maxid" );
}
//申请时间
if(null!=customer.getBeginTime()&&!"".equals(customer.getBeginTime())){
sb.append(" and s.CreateDate > :beginTime" );
}
if(null!=customer.getEndTime()&&!"".equals(customer.getEndTime())){
sb.append(" and s.CreateDate < :endTime" );
}
//公司名
if(StringUtils.hasText(customer.getCompanyName())){
sb.append(" and s.CompanyName like :companyName");
}
//联系人shoji
if(StringUtils.hasText(customer.getTelephone())){
sb.append(" and s.Telephone like :tp" );
}
//公司类型
if(StringUtils.hasText(customer.getCompanyType())){
if(!customer.getCompanyType().trim().equals("all")){
sb.append(" and s.CompanyType like :companyType ");
}
}


}

//查询共多少条记录
System.out.println("<---->sb="+sb.toString());
String getTotalCountSql = sb.toString();
SQLQuery query = super.getHibernateTemplate().getSessionFactory().getCurrentSession().createSQLQuery(getTotalCountSql);
if(null!=customer){
//公司名
if(StringUtils.hasText(customer.getCompanyName())){
query.setString("companyName","%"+customer.getCompanyName().trim()+"%");
}
//联系人shouji
if(StringUtils.hasText(customer.getTelephone())){
query.setString("tp", "%"+customer.getTelephone().trim()+"%");
}
//公司类型
if(StringUtils.hasText(customer.getCompanyType())){
if(!customer.getCompanyType().trim().equals("all")){
query.setString("companyType", "%"+customer.getCompanyType().trim()+"%");
}

}
//注册时间
if(null!=customer.getBeginTime()&&!"".equals(customer.getBeginTime())){
query.setDate("beginTime", Attribute.SDF_YMD.parse(customer.getBeginTime()));
}
if(null!=customer.getEndTime()&&!"".equals(customer.getEndTime())){
query.setDate("endTime", Util.addDay_return_Date(Attribute.SDF_YMD.parse(customer.getEndTime()), 1));
}
if(StringUtils.hasText(customer.getMinId())){
query.setInteger("minid", Integer.parseInt(customer.getMinId()));
}
if(StringUtils.hasText(customer.getMaxId())){
query.setInteger("maxid", Integer.parseInt(customer.getMaxId()));
}



}


/*----查询最大id号和最小id号 ----------------------------------------------------------------------------*/
StringBuffer sb1 = new StringBuffer();
sb1.append(" select max(s.ID),min(s.ID) ");
sb1.append(" FROM ");
sb1.append(" `T_CustomerInfo` s ");
SQLQuery query2 = super.getHibernateTemplate().getSessionFactory().getCurrentSession().createSQLQuery(sb1.toString());
if(null!=page){
//根据数据总条数初始化分页查询数据
int totalCount = Integer.parseInt(query.list().get(0).toString());
page.initPage(totalCount);
System.out.println("总条数totalCount=="+totalCount);
//最大id赋值
List l=query2.list();
Object[] obj = (Object[]) l.get(0);
obj = (Object[]) l.get(0);
int maxId=0;
if(obj[0]!=null&&!"".equals(obj[0])){
maxId=Integer.parseInt(obj[0].toString());
}

page.setMaxId(maxId);
//最小id
int minId=0;
if(obj[1]!=null&&!"".equals(obj[1])){
minId=Integer.parseInt(obj[1].toString());
}
page.setMinId(minId);
}
long endTime=System.currentTimeMillis(); //获取结束时间
System.out.println("程序运行时间： "+(endTime-startTime)+"ms");
//==================================================查询当前页记录=========================================================

StringBuffer ListSb = new StringBuffer();//查询字段数据
ListSb.append(" SELECT");
ListSb.append(" s.ID ,");//0
ListSb.append(" s.CompanyName,");//1
ListSb.append(" s.LinkName ,");//2
ListSb.append(" s.Tell,");//3
ListSb.append(" s.Telephone,");//4
ListSb.append(" s.Position,");//5
ListSb.append(" s.Email,");//6
ListSb.append(" s.CompanyType ,");//7
ListSb.append(" s.CreateMan,");//8
ListSb.append(" s.CustomerType ,");//9
ListSb.append(" s.CreateDate ");//10
ListSb.append(" FROM");
ListSb.append(" `T_CustomerInfo` s ");
ListSb.append(" where 1=1");
if(null!=customer){
if(StringUtils.hasText(customer.getMinId())){
ListSb.append(" and s.ID >= :minid" );
}
if(StringUtils.hasText(customer.getMaxId())){
ListSb.append(" and s.ID <= :maxid" );
}
//公司名
if(StringUtils.hasText(customer.getCompanyName())){
ListSb.append(" and s.CompanyName like :companyName ");
}
//联系人shoji
if(StringUtils.hasText(customer.getTelephone())){
ListSb.append(" and s.Telephone like :tp" );
}
//公司类型
if(StringUtils.hasText(customer.getCompanyType())){
if(!customer.getCompanyType().trim().equals("all")){
ListSb.append(" and s.CompanyType like :companyType ");
}
}
//申请时间
if(null!=customer.getBeginTime()&&!"".equals(customer.getBeginTime())){
ListSb.append(" and s.CreateDate > :beginTime" );
}
if(null!=customer.getEndTime()&&!"".equals(customer.getEndTime())){
ListSb.append(" and s.CreateDate < :endTime" );
}


}
ListSb.append(" order by s.ID asc");
String sql = ListSb.toString();
System.out.println("<---->ListSb="+sql);
SQLQuery query1 = super.getHibernateTemplate().getSessionFactory().getCurrentSession().createSQLQuery(sql);
if(null!=customer){
if(StringUtils.hasText(customer.getMinId())){
query1.setInteger("minid", Integer.parseInt(customer.getMinId()));
}
if(StringUtils.hasText(customer.getMaxId())){
query1.setInteger("maxid", Integer.parseInt(customer.getMaxId()));
}

//公司名
if(StringUtils.hasText(customer.getCompanyName())){
query1.setString("companyName","%"+customer.getCompanyName().trim()+"%");
}
if(StringUtils.hasText(customer.getTelephone())){
query1.setString("tp", "%"+customer.getTelephone().trim()+"%");
}
//公司类型
if(StringUtils.hasText(customer.getCompanyType())){
if(!customer.getCompanyType().trim().equals("all")){
query1.setString("companyType","%"+customer.getCompanyType().trim()+"%");
}
}
//注册时间
if(null!=customer.getBeginTime()&&!"".equals(customer.getBeginTime())){
query1.setDate("beginTime",Attribute.SDF_YMD.parse(customer.getBeginTime()));
}
if(null!=customer.getEndTime()&&!"".equals(customer.getEndTime())){
query1.setDate("endTime",Util.addDay_return_Date(Attribute.SDF_YMD.parse(customer.getEndTime()), 1));
}

}
long endTime1=System.currentTimeMillis(); //获取结束时间
System.out.println("程序运行时间： "+(endTime1-endTime)+"ms");
return super.findBySql(query1, page);

}


public Result findBySql(SQLQuery query, Page page) {
long endTime1=System.currentTimeMillis(); //获取结束时间
if (null != page) {
page = page.verifyPage(page);
}
Result result = new Result();
try {
if (page != null) {
Util.log("页面信息：第一条记录："+page.startNumber()+"共有记录数："+page.getNumber());
query.setFirstResult(page.startNumber());
query.setMaxResults(page.getNumber());
}
result.setList(query.list());
result.setPage(page);
} catch (Exception e) {
e.printStackTrace();
}
long endTime2=System.currentTimeMillis(); //获取结束时间
System.out.println("Result findBySql程序运行时间： "+(endTime2-endTime1)+"ms");
return result;
}

[liyunqinglyq]

800w 不算大数据，主要是那几个like导致查询慢。 1.like是 参数前面不用% 相应字段建立索引，利用索引检索 2.采用第三方的搜索引擎 如coreseek等

[七神之光]

搜索引擎

[名字不好取_蛋疼]

1 索引 2 数据如果不都是热数据， 就数据迁移 3 like 查询要干掉。

[longzl123]

自己已经优化好了 现在总结一下我的方法 共有 4884759 条记录（后续还有添加） 首页 345ms 尾页 345ms 运行的sql语句 SELECT s.ID , s.CompanyName, s.LinkName , s.Tell, s.Telephone, s.Position, s.Email, s.CompanyType , s.CreateMan, s.CustomerType , s.CreateDate FROM `T_CustomerInfo` s where 1=1 AND s.ID >=( SELECT ID FROM `T_CustomerInfo` LIMIT 4884750, 1 ) LIMIT 10 1.根据时间区间查询 改为between and 不用大于小于 2.添加索引 3.优化sql语句 具体的是（用limit 的子查询方式） 直接贴代码吧 s.ID添加了索引的 StringBuffer ListSb = new StringBuffer();//查询字段数据 ListSb.append(" SELECT"); ListSb.append(" s.ID ,");//0 ListSb.append(" s.CompanyName,");//1 ListSb.append(" s.LinkName ,");//2 ListSb.append(" s.Tell,");//3 ListSb.append(" s.Telephone,");//4 ListSb.append(" s.Position,");//5 ListSb.append(" s.Email,");//6 ListSb.append(" s.CompanyType ,");//7 ListSb.append(" s.CreateMan,");//8 ListSb.append(" s.CustomerType ,");//9 ListSb.append(" s.CreateDate ");//10 ListSb.append(" FROM"); ListSb.append(" `T_CustomerInfo` s "); ListSb.append(" where 1=1"); if(null!=customer){ if(StringUtils.hasText(customer.getMinId())&&StringUtils.hasText(customer.getMaxId())){ ListSb.append(" and s.ID BETWEEN :minid and :maxid" ); } //申请时间 if(null!=customer.getBeginTime()&&!"".equals(customer.getBeginTime())&&null!=customer.getEndTime()&&!"".equals(customer.getEndTime())){ ListSb.append(" and s.CreateDate BETWEEN :beginTime and :endTime" ); } //公司名 if(StringUtils.hasText(customer.getCompanyName())){ ListSb.append(" and s.CompanyName like :companyName"); } //联系人shoji if(StringUtils.hasText(customer.getTelephone())){ ListSb.append(" and s.Telephone like :tp" ); } //公司类型 if(StringUtils.hasText(customer.getCompanyType())){ if(!customer.getCompanyType().trim().equals("all")){ ListSb.append(" and s.CompanyType like :companyType "); } } } if (null != page) { page = page.verifyPage(page); } Result result = new Result(); ListSb.append(" AND s.ID >=(SELECT ID FROM `T_CustomerInfo` LIMIT "+page.startNumber()+", 1) LIMIT "+page.getNumber()+""); String sql = ListSb.toString(); System.out.println("ListSb=="+ListSb.toString()); SQLQuery query1 = super.getHibernateTemplate().getSessionFactory().getCurrentSession().createSQLQuery(sql); if(null!=customer){ //公司名 if(StringUtils.hasText(customer.getCompanyName())){ query1.setString("companyName","%"+customer.getCompanyName().trim()+"%"); } //联系人shouji if(StringUtils.hasText(customer.getTelephone())){ query1.setString("tp", "%"+customer.getTelephone().trim()+"%"); } //公司类型 if(StringUtils.hasText(customer.getCompanyType())){ if(!customer.getCompanyType().trim().equals("all")){ query1.setString("companyType", "%"+customer.getCompanyType().trim()+"%"); } } //注册时间 if(null!=customer.getBeginTime()&&!"".equals(customer.getBeginTime())&&null!=customer.getEndTime()&&!"".equals(customer.getEndTime())){ query1.setDate("beginTime", Attribute.SDF_YMD.parse(customer.getBeginTime())); query1.setDate("endTime", Util.addDay_return_Date(Attribute.SDF_YMD.parse(customer.getEndTime()), 1)); } if(StringUtils.hasText(customer.getMinId())&&StringUtils.hasText(customer.getMaxId())){ query1.setInteger("minid", Integer.parseInt(customer.getMinId())); query1.setInteger("maxid", Integer.parseInt(customer.getMaxId())); } } result.setList(query1.list()); result.setPage(page); long endTime=System.currentTimeMillis(); //获取结束时间 System.out.println("该分页共耗时： "+(endTime-startTime)+"ms"); return result;