最近在做一个项目,在做查询数据库(oracle)中发现一个问题,当在测试环境下可以执行,但是当发布到生产环境上的时候,因为实际数据库量很大,导致sql执行很慢,架构给出的方案是做物化视图,因为查询的另外的几个表的数据在别的数据库中,但是为了保持数据同步,每隔一个小时更新一次,这样因为数据量太大导致oracle的日志隔天就满了,最终导致系统卡死,关键的是卡死的时候正好在给客户的领导演示,这样真的很尴尬...
经过以上操作,我通过在实际环境中运行sql发现,有以下几种状态容易引发,因为是做的统计查询,所以有些计算,截取前几位等操作:a : group by;
b : s elect * f rom () where rownum>100;
c : aa表 full join bb表;
因为sql的查询和计算的数据量很大,有一个表数据是2000万以上,两个表的数据量为600万以上,查询的时间在一分钟以上,有的sql直接报错。
解决方案:a : 在查询之后group by 不用了,直接查询出结果,之后再用java代码实现根据某个字段对结果list进行分组,再次 返回另一个list:
代码:
- package test;
-
- import java.util.ArrayList;
- import java.util.List;
- import java.util.Map;
- import java.util.Map.Entry;
- import java.util.TreeMap;
-
- class JavaBean {
- public JavaBean(String id, String name) {
- this.id = id;
- this.name = name;
- }
-
- private String id;
-
- public String getId() {
- return id;
- }
-
- public void setId(String id) {
- this.id = id;
- }
-
- public String getName() {
- return name;
- }
-
- public void setName(String name) {
- this.name = name;
- }
-
- private String name;
-
- public JavaBean() {
- }
-
- @Override
- public String toString() {
-
- return "{用户ID:" + this.id + ", 用户名:" + this.name + " }";
- }
-
-
- public static void main(String[] args) {
-
- List list = new ArrayList();
- list.add(new JavaBean("1010", "admin"));
- list.add(new JavaBean("1010", "admin"));
- list.add(new JavaBean("1020", "xiaohua"));
- for(int i =0;i<5000;i++){
- list.add(new JavaBean("1020", "xiaohua"));
- list.add(new JavaBean("1010", "admin"));
- }
- list.add(new JavaBean("1020", "xiaohua"));
- List
> groupList = getListByGroup(list);
- for (List bean : groupList) {
- System.out.println(bean);
- }
- if("1010".equals(groupList.get(0).get(0).getId())){
- System.out.println( "1010:"+groupList.get(0).size());
- }
- if("1020".equals(groupList.get(1).get(0).getId())){
- System.out.println("1020:"+groupList.get(1).size());
- }
- }
-
- private static List
> getListByGroup(List list) {
- List
> result = new ArrayList>();
- Map> map = new TreeMap>();
-
- for (JavaBean bean : list) {
- if (map.containsKey(bean.getId())) {
- List t = map.get(bean.getId());
- t.add(new JavaBean(bean.getId(), bean.getName()));
- new ArrayList().add(new JavaBean(bean.getId(), bean
- .getName()));
- map.put(bean.getId(), t);
- } else {
- List t = new ArrayList();
- t.add(new JavaBean(bean.getId(), bean.getName()));
- map.put(bean.getId(), t);
- }
- }
- for (Entry> entry : map.entrySet()) {
- result.add(entry.getValue());
- }
- return result;
- }
- }
b: 新建一个list往里面注入当前list的前100个元素;
但是有的时候要排序,order by 一下,此时也容易导致sql速度变慢:
解决:
一个javabean:
- package test;
-
- import java.util.Date;
-
- public class TDate {
- private String str;
- private Date date;
- public String getStr() {
- return str;
- }
- public void setStr(String str) {
- this.str = str;
- }
- public Date getDate() {
- return date;
- }
- public void setDate(Date date) {
- this.date = date;
- }
- @Override
- public String toString() {
- return "TDate [str=" + str + ", date=" + date + "]";
- }
-
-
- }
比较方法:包含一个String 字符串转换成时间:来对时间进行比较的java方法:
- package test;
-
- import java.text.DateFormat;
- import java.text.ParseException;
- import java.text.SimpleDateFormat;
- import java.util.ArrayList;
- import java.util.Collections;
- import java.util.Comparator;
- import java.util.Date;
- import java.util.List;
-
- public class DateFoemate {
- public static void main(String[] args) throws ParseException {
-
- List list = new ArrayList();
- TDate tDate1 = new TDate();
- tDate1.setStr("2018-02-25");
- list.add(tDate1);
- TDate tDate2 = new TDate();
- tDate2.setStr("2018-02-26");
- list.add(tDate2);
- TDate tDate3 = new TDate();
- tDate3.setStr("2018-12-27");
- list.add(tDate3);
- TDate tDate4 = new TDate();
- tDate4.setStr("2018-02-28");
- list.add(tDate4);
- TDate tDate5 = new TDate();
- tDate5.setStr("2018-05-01");
- list.add(tDate5);
- TDate tDate6 = new TDate();
- tDate6.setStr("2018-03-03");
- list.add(tDate6);
- TDate tDate7 = new TDate();
- tDate7.setStr("2018-04-09");
- list.add(tDate7);
- TDate tDate8 = new TDate();
- tDate8.setStr("2018-02-01");
- list.add(tDate8);
- TDate tDate9 = new TDate();
- tDate9.setStr("2018-02-20");
- list.add(tDate9);
- TDate tDate10 = new TDate();
- tDate10.setStr("2018-03-20");
- list.add(tDate10);
-
- DateFormat format1 = new SimpleDateFormat("yyyy-MM-dd");
- Date date = null;
-
- for(TDate tDate : list){
- date = format1.parse(tDate.getStr());
- tDate.setDate(date);
- }
- Collections.sort(list, new Comparator(){
-
- @Override
- public int compare(TDate o1, TDate o2) {
- if(o2.getDate().before(o1.getDate()) ){
- return 1 ;
- }
- if(o2.getDate().equals(o1.getDate())){
- return 0;
- }
-
- return -1;
- }
-
- });
-
- System.out.println(list);
-
- }
- }
c: 单独建立aa表sql的返回list和bb表sql的返回list,再根据对应的主键将bb表的字段注入到aa表,如此可以拼接一个新的list,相当于full join的查询效果。
因为查询结果后,java的运算比oracle的运算效率快很多,这样就可以解决以上问题。