首先对于一个2000W的大表中获取数据,那么速度肯定十相当之慢
60W临时表创建的大约需要2-3S ,查询 只需要200-400ms ,所以 大数据量的情况下,我们可以直接使用创建一个临时表来处理该数据,那么就不需要直接去查询大表几千万的数据,那样速度很慢。
然后我们抽取出来创建临时表,创建表只需要2-3S
那么再进行查询这个临时表,更快 只需要200-400ms,因为已经抽取出来部分数据,数据量已经减少。
具体的sql代码
- 创建临时表
create TEMPORARY table dsp_1622453804102 as
select t0.* , t1.dataset_id
from f_result_sync_im t0,dsp_dataset_result t1
where t0.asse_res_unicode=t1.asse_res_unicode
and t1.dataset_id=410
and t0.paper_id in (1751,1756)
and t0.ques_id in (147,146)
-- 删除临时表
DROP TABLE dsp_1622453804102
SELECT * FROM dsp_1622453804102
-- 创建临时表的索引
create index dsp_1622453804102_qpq_index on dsp_1622453804102 (ques_id,paper_id,ques_answer_value) ;
create index dsp_1622453804102_qqpq_index on dsp_1622453804102 (ques_answer_content,ques_id,paper_id,ques_answer_value) ;
-- 查询索引
SELECT * FROM pg_indexes WHERE TABLENAME = 'dsp_1622453804102'
实际java(cuba)使用方式
Transaction ts = persistence.createTransaction();
String tableName = "dsp_" + new Date().getTime();// 这里用 uuid,因为多线程情况下这个时间戳 会重复,不建议使用了。
try {
EntityManager em = persistence.getEntityManager();
StringBuilder createSql=new StringBuilder(String.format("create TEMPORARY table dsp_2021_05_31 as " +
"select t0.* , t1.dataset_id " +
"from f_result_sync_im t0,dsp_dataset_result t1 " +
"where t0.asse_res_unicode=t1.asse_res_unicode " +
"and t1.dataset_id=410 " +
"and t0.paper_id in (1751,1756) " +
"and t0.ques_id in (147,146) "));
System.out.println("createSql:"+createSql.toString());
em.createNativeQuery(createSql.toString()).executeUpdate();
ts.commit();
//创建完临时表后 去获取创建临时表的数据~
queryTempTable();
} catch (Exception e) {
logger.error("临时表创建失败", e);
} finally {
nameResult.add(tableName);
nameResult.add(secondTableName);
ts.close();
}
/**
* 获取创建临时表 createNativeQuery:这个表示使用原生sql
* createQuery 表示用jpql
**/
private void queryTempTable(){
Transaction ts = persistence.getTransaction();
try {
EntityManager em = persistence.getEntityManager();
//然后 去查询下这个临时表 ,是不是创建成功了
String sql = "select count(*) from dsp_2021_05_31 ";
Query query = em.createNativeQuery(sql);
Object count = query.getFirstResult();
ts.commit();
System.out.println("2_dsp_2021_05_31 count:" + count);
}finally {
ts.end();
}
}