在工作项目中遇到一个问题,就是做一个学生提交作业查询的sql,但是这个数据库实在太大了,一天的数据量就达到了几万条,如果一次全部加载到内存,那就歇菜了,彻底凉凉,所以针对查学生的作业信息,这个就得考虑到分批次查询了
怎么分批次查询数据库呢?
使用存储过程来实现多线程分批次查询 -->然后查出来再合并:
例如:
delimiter $$ --结束符为 $$
DROP PROCEDURE IF EXISTS mytest; --判断进程是否存在,存在则删除
CREATE PROCEDURE mytest() --新建进程
BEGIN
declare i int;
DECLARE j varchar(200);
set i = 2;
while i < 11 do --循环体
SET j=CONCAT('wwq',i); --拼接字符串
INSERT INTO pub_user(`ID`, `NAME`, `ACCOUNT`, `PASSWORD`, `USER_CODE`)
VALUES (i, '王馨', j, 'e10adc3949ba59abbe56e057f20f883e', '');
INSERT INTO pub_user_post (`POST_CODE`, `USER_CODE`)
VALUES (i, 'A685187D29AF4AD793F2753DC17C1435');
INSERT INTO pub_user_role (`ROLE_CODE`, `USER_CODE`, `APP_CODE`)
VALUES ('95AC32C8B9874B4085A01187C341067B', i, 'INSPUR-DZZW-TYSP');
set i=i+1;
end while;
end $$ --结束定义语句
delimiter ; --先把结束符恢复为 ;
call mytest(); --调用进程
代码中怎么实现多线程分批查询?
package hong;
import com.sqlSource.SqlHadle;
import java.util.List;
import java.util.concurrent.Callable;
/**
* 线程 Callable:创建任务
*/
public class ThredQuery implements Callable<List> {
SqlHadle sqlHadle = new SqlHadle();
private String search;//查询条件 根据条件来定义该类的属性
private int bindex;//当前页数
private int num;//每页查询多少条
private String table;//要查询的表名,也可以写死,也可以从前面传
private List page;//每次分页查出来的数据
public ThredQuery(int bindex, int num, String table) {
this.bindex = bindex;
this.num = num;
this.table = table;
//分页查询数据库数据 查出对应的数据
page = sqlHadle.queryTest11(bindex, num, table);
}
@Override
public List call() throws Exception {
//返回数据给Future
return page;
}
}
package hong;
import com.ThreadPoolHadel.ThredQuery;
import com.sqlSource.SqlHadle;
import org.springframework.stereotype.Service;
import java.util.ArrayList;
import java.util.List;
import java.util.concurrent.*;
@Service
//执行多线程查询数据库,并合并数据
public class TheardQueryService {
SqlHadle sqlHadle=new SqlHadle(); //sql语句拼接数据
public List<List> getMaxResult(String table) throws InterruptedException, ExecutionException {
long start = System.currentTimeMillis();//开始时间
List<List> result = new ArrayList<>();//返回结果
//查询数据库总数量
int count = sqlHadle.count(table);
int num = 8000;//一次查询多少条
//需要查询的次数
int times = count / num;
if (count % num != 0) {
times = times + 1;
}
//开始页数 连接的是orcle的数据库 封装的分页方式 我的是从1开始
int bindex = 1;
//Callable用于产生结果
List<Callable<List>> tasks = new ArrayList<>();
for (int i = 0; i < times; i++) {
Callable<List> qfe = new ThredQuery(bindex, num, table);
tasks.add(qfe);
bindex += bindex;
}
//定义固定长度的线程池 防止线程过多
ExecutorService executorService = Executors.newFixedThreadPool(15);
//Future用于获取结果
List<Future<List>> futures=executorService.invokeAll(tasks);
//处理线程返回结果
if(futures!=null&&futures.size()>0){
for (Future<List> future:futures){
result.addAll(future.get());
}
}
executorService.shutdown();//关闭线程池
long end = System.currentTimeMillis();
System.out.println("线程查询数据用时:"+(end-start)+"ms");
return result;
}
}
为什么要用存储过程进行分批次查询呢?
因为数据库固有的存储过程不支持物理分页,所以如果采用分批查询方式,还需要开发人员编写自己的存储过程来实现。
这样,服务端每次只把若干条(一般20条)记录返回给客户端,这样,既节省资源,又加快通讯的效率!
那什么是物理分页,什么是逻辑分页呢?
物理分页
物理分页依赖的是某一物理实体,这个物理实体就是数据库,即:在查询数据库时在库里执行分页,
比如MySQL数据库提供了limit关键字,程序员只需要编写带有limit关键字的SQL语句,数据库返回的就是分页结果。
逻辑(内存)分页
逻辑分页依赖的是程序员编写的代码。数据库返回的不是分页结果,而是全部数据,然后再由程序员通过代码获取分页数据,(取出全部数据先存于内存中,再取出需要的数据)
实际上:java代码中 RowBound 属于内存分页,pageHelper属于物理分页。
对比
1.数据库负担
物理分页每次都访问数据库,逻辑分页只访问一次数据库,物理分页对数据库造成的负担大。
2.服务器负担
逻辑分页一次性将数据读取到内存,占用了较大的内容空间,物理分页每次只读取一部分数据,占用内存空间较小。
3.实时性
逻辑分页一次性将数据读取到内存,数据发生改变,数据库的最新状态不能实时反映到操作中,实时性差。物理分页每次需要数据时都访问数据库,能够获取数据库的最新状态,实时性强。
4.适用场合
逻辑分页主要用于数据量不大、数据稳定的场合,物理分页主要用于数据量较大、更新频繁的场合。
那什么时候需要建立分库分表呢?
假设一个表中有多个字段,一个字段存了大文本,比如说我这个项目中的学生的作业评价或者学生提交一些话语,大文字的话,一个5m的话,1000条数据就是5g了,这时候就要分表了,尽量少查这些大文本的数据,会导致查询很慢 .
然后如果并发高了,那就要考虑分库了
实际上就是要考虑IO瓶颈和CPU瓶颈