记一次mysql千万级数据量(上千亿都行)的处理(java)

1、前言

项目一个表经历着一天3W条数据入表，一年就有1000W，单表随着数据的增多，查询都会逐渐卡，加了索引依旧出现查询卡

2、经过

因为我用的是java，起初解决方案是用【shardingsphere】框架，读写分离，也许有的我用的不够深，感觉性能并没有实质上面的提升

3、最后

①自行拆分总表，数据以每个月的分离出来(后续可以自己写个定时器，每个月自动生成一个表)

②代码部分(两个方法、1判断自定义的日期，否则就是默认显示今天，2是生成sql)

/**
     * 计算时间范围中间的月份
     *
     * @Author Xcl
     */
    public static List<String> countMonth(String startdate, String enddate) {
        try {
            Date startDate = new SimpleDateFormat("yyyy-MM-dd").parse(startdate);
            Date endDate = new SimpleDateFormat("yyyy-MM-dd").parse(enddate);

            Calendar calendar = Calendar.getInstance();
            calendar.setTime(startDate);
            // 获取开始年份和开始月份
            int startYear = calendar.get(Calendar.YEAR);
            int startMonth = calendar.get(Calendar.MONTH);
            // 获取结束年份和结束月份
            calendar.setTime(endDate);
            int endYear = calendar.get(Calendar.YEAR);
            int endMonth = calendar.get(Calendar.MONTH);
            //
            List<String> list = new ArrayList<String>();
            for (int i = startYear; i <= endYear; i++) {
                String date = "";
                if (startYear == endYear) {
                    for (int j = startMonth; j <= endMonth; j++) {
                        if (j < 9) {
                            date = i + "0" + (j + 1);
                        } else {
                            date = i + "" + (j + 1);
                        }
                        list.add(date);
                    }

                } else {
                    if (i == startYear) {
                        for (int j = startMonth; j < 12; j++) {
                            if (j < 9) {
                                date = i + "0" + (j + 1);
                            } else {
                                date = i + "" + (j + 1);
                            }
                            list.add(date);
                        }
                    } else if (i == endYear) {
                        for (int j = 0; j <= endMonth; j++) {
                            if (j < 9) {
                                date = i + "0" + (j + 1);
                            } else {
                                date = i + "" + (j + 1);
                            }
                            list.add(date);
                        }
                    } else {
                        for (int j = 0; j < 12; j++) {
                            if (j < 9) {
                                date = i + "0" + (j + 1);
                            } else {
                                date = i + "" + (j + 1);
                            }
                            list.add(date);
                        }
                    }
                }
            }
            return list;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }

 /**
     * 拼接union all--sql
     *
     * @Author Xcl
     */
    public static String connectionsql(CollectionDDTO collectionDDTO) {
        //月份范围
        List<String> monthList = new ArrayList<>();
        //获取时间间隔
        if (StrUtil.isBlank(collectionDDTO.getStartdate())) { //时间为空，默认今日
            collectionDDTO.setStartdate(DateUtil.today()); //当前日期字符串，格式：yyyy-MM-dd
            collectionDDTO.setEnddate(DateUtil.formatDate(DateUtil.tomorrow()));  //明天日期字符串，格式：yyyy-MM-dd
        }
        monthList = countMonth(collectionDDTO.getStartdate(), collectionDDTO.getEnddate());

        //获取where判断
        String whereIf = "where 1=1 ";
        if (StrUtil.isNotBlank(collectionDDTO.getStartdate())) {
            whereIf = whereIf + " and collectiontime >= '" + collectionDDTO.getStartdate() + "'";
        }
        if (StrUtil.isNotBlank(collectionDDTO.getEnddate())) {
            whereIf = whereIf + " and collectiontime <= '" + collectionDDTO.getEnddate() + "'";
        }
        if (StrUtil.isNotBlank(collectionDDTO.getUnitid())) {
            whereIf = whereIf + " and unit = '" + collectionDDTO.getUnitid() + "'";
        }
        if (StrUtil.isNotBlank(collectionDDTO.getEquipmentsetid())) {
            whereIf = whereIf + " and equipmentsetid = '" + collectionDDTO.getEquipmentsetid() + "'";
        }

        //sql部分
        String sql = "";
        String remark = "";
        for (int i = 0; i < monthList.size(); i++) {
            remark = i == 0 ? "" : " union all ";
            sql = sql + remark + "select * from zhy_collection" + monthList.get(i) + " " + whereIf;
        }
        
        return sql + " order by collectiontime desc ";
    }

测试

 public static void main(String[] args) {
        CollectionDDTO collectionDDTO = new CollectionDDTO();
        collectionDDTO.setStartdate("2021-01-05");
        collectionDDTO.setEnddate("2021-07-31");
        Console.log(connectionsql(collectionDDTO));
    }

输出 

select * from zhy_collection202101 where 1=1  and collectiontime >= '2021-01-05' and collectiontime <= '2021-07-31' 

union all select * from zhy_collection202102 where 1=1  and collectiontime >= '2021-01-05' and collectiontime <= '2021-07-31' 

union all select * from zhy_collection202103 where 1=1  and collectiontime >= '2021-01-05' and collectiontime <= '2021-07-31' 

union all select * from zhy_collection202104 where 1=1  and collectiontime >= '2021-01-05' and collectiontime <= '2021-07-31' 

union all select * from zhy_collection202105 where 1=1  and collectiontime >= '2021-01-05' and collectiontime <= '2021-07-31' 

union all select * from zhy_collection202106 where 1=1  and collectiontime >= '2021-01-05' and collectiontime <= '2021-07-31' 

union all select * from zhy_collection202107 where 1=1  and collectiontime >= '2021-01-05' and collectiontime <= '2021-07-31' 

order by collectiontime desc

4、补充

回头我看了一下【shardingsphere】框架，好像也是可以达到这种效果！而且更高级。