对于数据库进行分表,以及后续的查询(mysql)_mysql分表后怎样保证查询

       近期重构一个项目因为每天都有定时任务生成的数据,数据量越堆越多,老系统就是数据量上来后,系统卡死,后项目上让进行分表操作,,我这里根据业务数据量评估半年的数据量一个表,进行分表,具体操作如下,有什么不好的或错的欢迎大佬们指正。

 1.分表

我在网上搜索了很多决定使用MySQL的事件和存储过程进行定期分表。

1.1存储过程

  存储过程可参考 【精选】《Msql进阶一》-CSDN博客

CREATE  PROCEDURE create_number_table1()
begin
 
set @SQLStmt=concat("create table if not exists ",concat('table_',DATE_FORMAT(DATE_ADD(CURRENT_DATE(), INTERVAL 1 MONTH),'%Y%m')),"
(
  `id` int(11) DEFAULT NULL,
  `name` varchar(50) DEFAULT NULL,
  `age` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='定期分表';");
PREPARE stmt FROM @SQLStmt;
EXECUTE stmt;
deallocate prepare stmt;
end

1.2事件

使用mysql事件时需要开启事件调度器,可使用下面命令查看,开启

-- 开启事件调度器
SET GLOBAL event_scheduler = ON;
-- 关闭事件调度器
SET GLOBAL event_scheduler = OFF;
-- 查看事件调度器状态
SHOW VARIABLES LIKE 'event_scheduler';

事件具体信息可参考该链接 s​​MySQL事件（定时任务）_mysql 定时事件-CSDN博客

我的事件配置如下

 ​​​​​

 通过上面两步就实现了定期自动分表。

 2.查询

        查询时我这让前端做了限制,每次操作数据时涉及到分了表的,就必须传时间段,与业务人员沟通后并限制只能查当前时间往前半年的数据,这样最多同事查询两个表的数据,能相对快点。

因为每次都要根据时间段,用于判断操作那几个表，就简单写了个工具。

/**
     * 根据时间判断需要查询的表(半年一分表)
     * @param sTime 开始时间
     * @param eTime 结束时间
     * @return table集合
     */
    public static List<String> getTableSuffix(Date sTime,Date eTime){
        List<String> list = new ArrayList<>();
        Calendar calendar = Calendar.getInstance();
        calendar.setTime(sTime);
        int y1 = calendar.get(Calendar.YEAR);
        int m1 = calendar.get(Calendar.MONTH)+1;
        if (m1>6){
            list.add(y1+"07");
        }else {
            list.add(y1+"01");
        }
        calendar.setTime(eTime);
        int y2 = calendar.get(Calendar.YEAR);
        int m2 = calendar.get(Calendar.MONTH)+1;
        if ((y2>y1)){
            list.add(y2+"01");
        }else if (y2==y1&&m1<=6&&m2>6){
            String table2= y2+"07";
            int index = list.indexOf(table2);
            if (index==-1){
                list.add(table2);
            }
        }
        return list;
    }

查询

<foreach collection="tables" item="table" open="" separator="UNION" close="">
            select * from table1_${table} where
            1=1
            <if test=" entity.code != null">
                and code  = #{entity.code}
            </if>
            ...
            ...
            ...
</foreach>
        order by start_time desc

 大概就是这样,写的不好,多多见谅。