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

最新推荐文章于 2025-02-27 15:29:54 发布

哈喽Java灬

最新推荐文章于 2025-02-27 15:29:54 发布

阅读量752

点赞数

文章标签：数据库 mysql

本文链接：https://blog.csdn.net/zhanghang10/article/details/133993402

版权

本文描述了一名开发者如何通过MySQL事件和存储过程实现项目中的定期数据分表，以应对数据量增长导致的系统性能问题，同时提及了查询时的优化策略和时间段限制。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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

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';

事件具体信息可参考该链接 sMySQL事件（定时任务）_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

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