功能简介

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

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

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）下载安装模板

解压后重命名文件夹为mycat2

2）mycat2程序包

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

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"} */

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就能实现你的需求那是我写这篇文章最大的成就。