功能简介

随着业务数据量的增加查询速度已无法忍受的程度。“是你的网络问题吧”这样牵强的解释无法敷衍过去的时候。

在不修改原来程序代码的基础上最好的办法：

1）说服客户清理什么时候之前的数据，完美下班[呲牙]。

2）使用数据库代理中间件帮我们完成读写分离、分库分表等操作，程序只需修改数据库链接信息到数据库代理组件的地址即可；（中间件mycat2，shardingsphere-proxy都可以实现）

工作日报为底层数据结构变更，业务查询整改优化需要xx工作日完成。

实际工作安装部署mycat2配置分表，修改程序数据库地址。

备注：这种年月分表主要是处理列表查询展示类业务，因为分表后你除了要用id查询最好也带上分表的时间字段，不然就会全分表都查询一遍。如果你是自增id并有其他业务关联该自增id那么请你一定要说法客户用方法1[抠鼻]。（我基本都使用雪花算法做id，可排序、唯一）

文章主要介绍安装部署和单数据库月年分表，因为我在网上搜索发现都是官方的说明介绍复制粘贴的文章，这里针对功能编写解决问题方便大家复制。

按年月拆分数据表

数据备份、数据备份、数据备份重要的事情说三遍。

我这里只是单表拆分，具体涉及到业务关联什么的还要看自己的具体情况。我这里是直接使用sql语句将大数据表的数据直接用查询结果的方式插入到新分表中。

1）使用存储过程创建年月分表（复制直接用，替换test表名和create_date时间字段名）

-- 1. 先删除存储过程
DROP PROCEDURE IF EXISTS `create_tables`;
-- 2. 创建存储过程
CREATE PROCEDURE create_tables ()
begin
    -- 定义变量
    DECLARE s int DEFAULT 0;
    DECLARE `@createSql` VARCHAR (2560);
    DECLARE tableName VARCHAR(255);
		
    -- 定义游标，并将sql结果集赋值到游标中，report为游标名
    -- test为大数据表名，查询出年月并且组合为新分表的表名，例：test_202209或test_202210或test_202211
    DECLARE report CURSOR FOR select distinct CONCAT('test','_',DATE_FORMAT(create_date,'%Y%m')) as table_name from test;
    -- 声明当游标遍历完后将标识变量置为某个值
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET s = 1;
		
    -- 打开游标
    OPEN report;
    -- 将游标中的值赋值给变量，注意：变量名不要与sql返回的列名相同，变量顺序要和sql结果列的顺序一致
    FETCH report INTO tableName;
		
    -- 当s不等于1时，也就是未遍历完时，会一直循环
    WHILE s <> 1 DO
        -- 执行业务逻辑,创建新分表结构复制大数据表
        SET @createSql = CONCAT('create table ',tableName,' like test;');
        PREPARE stmt FROM @createSql;
        EXECUTE stmt;
        -- 当s等于1时代表遍历已完成，退出循环
        FETCH report INTO tableName;
    END WHILE;
		
    -- 关闭游标
    CLOSE report;
end;
-- 3. 执行存储过程
CALL create_tables();
-- 4. 删除存储过程
DROP PROCEDURE IF EXISTS `create_tables`;

生成分表后数据库截图

2）数据导入到分表中

关键sql就是查询对应年月的数据插入到对象的分表中

insert into test_202210 select * from test where  DATE_FORMAT(create_date,'%Y%m') = '202210'

也可以像上面批量创建表一样，使用存储过程一次将大数据表的数据批量插入到对应的分表中。

-- 1. 先删除存储过程
DROP PROCEDURE IF EXISTS `insert_datas`;
-- 2. 创建存储过程
CREATE PROCEDURE insert_datas ()
begin
    -- 定义变量
    DECLARE s int DEFAULT 0;
    DECLARE `@createSql` VARCHAR (2560);
    DECLARE yyyyMM VARCHAR(255);
		
    -- 定义游标，并将sql结果集赋值到游标中，report为游标名
    -- test为大数据表明，查询出年月
    DECLARE report CURSOR FOR select distinct DATE_FORMAT(create_date,'%Y%m') as table_name from test;
    -- 声明当游标遍历完后将标识变量置为某个值
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET s = 1;
		
    -- 打开游标
    OPEN report;
    -- 将游标中的值赋值给变量，注意：变量名不要与sql返回的列名相同，变量顺序要和sql结果列的顺序一致
    FETCH report INTO yyyyMM;
		
    -- 当s不等于1时，也就是未遍历完时，会一直循环
    WHILE s <> 1 DO
        -- 执行业务逻辑,创建新分表结构复制大数据表
        SET @createSql = CONCAT("insert into test_",yyyyMM," select * from test where  DATE_FORMAT(create_date,'%Y%m') = '",yyyyMM,"'");
        PREPARE stmt FROM @createSql;
        EXECUTE stmt;
        -- 当s等于1时代表遍历已完成，退出循环
        FETCH report INTO yyyyMM;
    END WHILE;
		
    -- 关闭游标
    CLOSE report;
end;
-- 3. 执行存储过程
CALL insert_datas();
-- 4. 删除存储过程
DROP PROCEDURE IF EXISTS `insert_datas`;

3）原来的大数据主表就没用了修改表名即可，后面使用mycat2查询的时候会创建对应的逻辑表进行查询。

这里将原来的表名修改掉很重要。

主表修改表名后截图

mycat2下载

1）下载安装模板

http://dl.mycat.org.cn/2.0/install-template/mycat2-install-template-1.21.zip

解压后重命名文件夹为mycat2

2）mycat2程序包

http://dl.mycat.org.cn/2.0/1.21-release/mycat2-1.21-release-jar-with-dependencies.jar

将下载的mycat2-1.21-release-jar-with-dependencies.jar文件复制到 mycat2lib下即可

3）目录结构

mycat2目录

mycat2配置说明、修改

1）conf目录下server.json 里面主要需要修改的可能就是端口，mysql默认3306，mycat2默认8066

{
  "loadBalance":{
    "defaultLoadBalance":"BalanceRandom",
    "loadBalances":[]
  },
  "mode":"local",
  "monitor":{
    "sqlLog":{
        "clazz":"io.mycat.exporter.MySQLLogConsumer",
        "open":true,
        "sqlTimeFilter":3000,
        "sqlTypeFilter":["SELECT"]
	}
  },
  "properties":{},
  "server":{
    "bufferPool":{

    },
    "idleTimer":{
      "initialDelay":3,
      "period":60000,
      "timeUnit":"SECONDS"
    },
    "ip":"0.0.0.0",
    "mycatId":1,
    "port":8066,
    "reactorNumber":8,
    "tempDirectory":null,
    "timeWorkerPool":{
      "corePoolSize":0,
      "keepAliveTime":1,
      "maxPendingLimit":65535,
      "maxPoolSize":2,
      "taskTimeout":5,
      "timeUnit":"MINUTES"
    },
    "workerPool":{
      "corePoolSize":1,
      "keepAliveTime":1,
      "maxPendingLimit":65535,
      "maxPoolSize":1024,
      "taskTimeout":5,
      "timeUnit":"MINUTES"
    }
  }
}

2）conf/users/root.user.json 修改mycat2访问用户，程序或Navicat工具连接访问的用户。

{
    "dialect":"mysql",
    "ip":null,
    "password":"123456",
    "transactionType":"proxy",
    "username":"root"
}

3）conf/datasources/prototypeDs.datasource.json 配置mycat2自带的数据源，主要修改password，url，user。随便配置一个正确的，自带的数据源没配置好像无法启动，作为搬砖的能跑我就觉得没研究的必要了[呲牙]。

{
    "dbType":"mysql",
    "idleTimeout":60000,
    "initSqls":[],
    "initSqlsGetConnection":true,
    "instanceType":"READ_WRITE",
    "maxCon":1000,
    "maxConnectTimeout":3000,
    "maxRetryCount":5,
    "minCon":1,
    "name":"prototypeDs",
    "password":"123456",
    "type":"JDBC",
    "url":"jdbc:mysql://localhost:3306/mycat?useUnicode=true&serverTimezone=Asia/Shanghai&characterEncoding=UTF-8",
    "user":"root",
    "weight":0
}

4）在conf/datasources目录下将文件prototypeDs.datasource.json复制一个重命名为自己数据库的“数据库名称.datasource.json”文件。配置自己程序当前使用的数据库，我这里还是test数据库为例，主要修改name（数据源名称，后面其他配置会用到）、password、url、user

{
	"dbType":"mysql",
	"idleTimeout":60000,
	"initSqls":[],
	"initSqlsGetConnection":true,
	"instanceType":"READ_WRITE",
	"maxCon":1000,
	"maxConnectTimeout":3000,
	"maxRetryCount":5,
	"minCon":1,
	"name":"test",
	"password":"123456",
	"type":"JDBC",
	"url":"jdbc:mysql://localhost:3306/test?useUnicode=true&serverTimezone=Asia/Shanghai&characterEncoding=UTF-8",
	"user":"root",
	"weight":0
}

conf/datasources目录

5）在conf/clusters目录下将文件prototype.cluster.json复制一个重命名为自己数据库的“数据库名称.cluster.json”文件。主要修改masters（就是上面设置的数据源名称）

{
    "clusterType":"MASTER_SLAVE",
    "heartbeat":{
        "heartbeatTimeout":1000,
        "maxRetry":3,
        "minSwitchTimeInterval":300,
        "slaveThreshold":0
    },
    "masters":[
        "test"
    ],
    "maxCon":200,
    "name":"prototype",
    "readBalanceType":"BALANCE_ALL",
    "switchType":"SWITCH"
}

6）在conf/schemas目录下创建“数据库名称.schema.json”文件。主要设置schemaName、targetName。所有关于名称的最好就和自己的数据库名称一样。其中shardingTables为分表设置信息。下面也提供了命令，但是命令执行的时候会在数据库中创建所有分表。

配置shardingTables中意思就是创建了test逻辑表，createTableSQL创建表的sql语句（这个创建表的语句就是之前的让修改表名的大数据表，创建表的sql语句可以在Navicat中直接复制），function指明使用的方法和分表的字段（这里是按月分表），partition指明具体的表，tableNames指分表范围

{
    "customTables":{},
    "globalTables":{},
    "normalProcedures":{},
    "normalTables":{},
    "schemaName":"test",
    "shardingTables":{
        "test":{
            "createTableSQL":"CREATE TABLE `test` (`id` bigint(20) NOT NULL,`title` text CHARACTER SET utf8 COLLATE utf8_general_ci,`create_date` datetime DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;",
            "function":{
                "clazz":"io.mycat.router.mycat1xfunction.PartitionByMonth",
                "properties":{
                        "beginDate":"2022-01-01 00:00:00",
                        "dateFormat":"yyyy-MM-dd hh:mm:ss",
                        "endDate":"",
                        "columnName":"create_date"
                },
            "ranges":{}
        },
        "partition":{
            "schemaNames":"test",
            "tableNames":"test_20220$1-9,test_2022$10-12,test_20230$1-9,test_2023$10-12,test_20240$1-9,test_2024$10-12,test_20250$1-9,test_2025$10-12",
            "targetNames":"test"
        },
        "shardingIndexTables":{}
      }
  },
    "targetName":"test",
    "views":{}
}

这个只是运行的命令当作参考，和上面的配置是一个操作。忽略这个就行了
/*+ mycat:createTable{
    "schemaName":"test",
    "shardingTable":{
    "createTableSQL":"CREATE TABLE `test` 
        (`id` bigint(20) NOT NULL,
        `title` text CHARACTER SET utf8 COLLATE utf8_general_ci,
        `create_date` datetime DEFAULT NULL, 
        PRIMARY KEY (`id`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8;",
    "function":{
        "clazz":"io.mycat.router.mycat1xfunction.PartitionByMonth",
        "properties":{
            "beginDate":"2022-01-01 00:00:00",
            "dateFormat":"yyyy-MM-dd hh:mm:ss",
            "endDate":"",
            "columnName":"create_date"
        },
			"ranges":{}
    },
    "partition":{
        "schemaNames":"test",
        "tableNames":"test_20220$1-9,test_2022$10-12,test_20230$1-9,test_2023$10-12,test_20240$1-9,test_2024$10-12,test_20250$1-9,test_2025$10-12",
        "targetNames":"test"
    }
    },
    "tableName":"test"
} */

mycat2启动

1）window启动cmd使用管理员权限，进入到mycat2/bin。

window里面需要先安装服务 mycat.bat install

启动 mycat.bat start，也可以直接在服务中启动

2）linux 直接进入bin mycat start，bin目录记得给权限 chmod +x mycat2/bin/*

3）Navicat工具连接，和mysql一样，帐号密码端口就是上面配置中的信息。

为了方便测试我又手动创建跨年份的数据表，其中mycat2的数据库里面有个逻辑表test，查询的时候就会直接进行分表查询。

备注：不带日期查询的话是所有分表，就是上面配置中tableNames的所有表，但是数据库没有对应的表会报错，返回空接口。所以分表后每次查询必须带上分表时间字段create_date。

select * from test where create_date BETWEEN '2022-10-01 00:00:00' and '2023-01-02 00:00:00'

mycat2的数据库

真实数据库

结语

文章中我只截出了关键性的代码方法，相信对需要的人提供帮助或参考。

希望需要的程序员可以直接使用CTRL + V、CTRL + C就能实现你的需求那是我写这篇文章最大的成就。