优化Oracle查询性能-优快云博客

本文链接：https://blog.youkuaiyun.com/deyu01/article/details/79643585

最近在做一个项目，在做查询数据库（oracle）中发现一个问题，当在测试环境下可以执行，但是当发布到生产环境上的时候，因为实际数据库量很大，导致sql执行很慢，架构给出的方案是做物化视图，因为查询的另外的几个表的数据在别的数据库中，但是为了保持数据同步，每隔一个小时更新一次，这样因为数据量太大导致oracle的日志隔天就满了，最终导致系统卡死，关键的是卡死的时候正好在给客户的领导演示，这样真的很尴尬...

经过以上操作，我通过在实际环境中运行sql发现，有以下几种状态容易引发，因为是做的统计查询，所以有些计算，截取前几位等操作：a : group by；

b : select * from () 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() {
        // TODO Auto-generated method stub
        return "{用户ID：" + this.id + ",  用户名:" + this.name + " }";
    }

 
public static void main(String[] args) {
        //向list中添加对象 
        List<JavaBean> list = new ArrayList<JavaBean>();
        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<List<JavaBean>> groupList = getListByGroup(list);
        for (List<JavaBean> 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<List<JavaBean>> getListByGroup(List<JavaBean> list) {
        List<List<JavaBean>> result = new ArrayList<List<JavaBean>>();
        Map<String, List<JavaBean>> map = new TreeMap<String, List<JavaBean>>();
 
        for (JavaBean bean : list) {
            if (map.containsKey(bean.getId())) {
                List<JavaBean> t = map.get(bean.getId());
                t.add(new JavaBean(bean.getId(), bean.getName()));
                new ArrayList<JavaBean>().add(new JavaBean(bean.getId(), bean
                        .getName()));
                map.put(bean.getId(), t);
            } else {
                List<JavaBean> t = new ArrayList<JavaBean>();
                t.add(new JavaBean(bean.getId(), bean.getName()));
                map.put(bean.getId(), t);
            }
        }
        for (Entry<String, List<JavaBean>> 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<TDate> list = new ArrayList<TDate>();
		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<TDate>(){

			@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的运算效率快很多，这样就可以解决以上问题。