mycat2实现单数据库年月分表

　　功能简介
　　随着业务数据量的增加查询速度已无法忍受的程度。是你的网络问题吧这样牵强的解释无法敷衍过去的时候。
　　在不修改原来程序代码的基础上最好的办法：
　　1）说服客户清理什么时候之前的数据，完美下班〔呲牙〕。
　　2）使用数据库代理中间件帮我们完成读写分离、分库分表等操作，程序只需修改数据库链接信息到数据库代理组件的地址即可；（中间件mycat2，shardingsphereproxy都可以实现）
　　工作日报为底层数据结构变更，业务查询整改优化需要xx工作日完成。
　　实际工作安装部署mycat2配置分表，修改程序数据库地址。
　　备注：这种年月分表主要是处理列表查询展示类业务，因为分表后你除了要用id查询最好也带上分表的时间字段，不然就会全分表都查询一遍。如果你是自增id并有其他业务关联该自增id那么请你一定要说法客户用方法1〔抠鼻〕。（我基本都使用雪花算法做id，可排序、唯一）
　　文章主要介绍安装部署和单数据库月年分表，因为我在网上搜索发现都是官方的说明介绍复制粘贴的文章，这里针对功能编写解决问题方便大家复制。按年月拆分数据表
　　数据备份、数据备份、数据备份重要的事情说三遍。
　　我这里只是单表拆分，具体涉及到业务关联什么的还要看自己的具体情况。我这里是直接使用sql语句将大数据表的数据直接用查询结果的方式插入到新分表中。
　　1）使用存储过程创建年月分表（复制直接用，替换test表名和createdate时间字段名）1。先删除存储过程DROPPROCEDUREIFEXISTScreatetables；2。创建存储过程CREATEPROCEDUREcreatetables（）begin定义变量DECLAREsintDEFAULT0；DECLAREcreateSqlVARCHAR（2560）；DECLAREtableNameVARCHAR（255）；定义游标，并将sql结果集赋值到游标中，report为游标名test为大数据表名，查询出年月并且组合为新分表的表名，例：test202209或test202210或test202211DECLAREreportCURSORFORselectdistinctCONCAT（test，，DATEFORMAT（createdate，Ym））astablenamefromtest；声明当游标遍历完后将标识变量置为某个值DECLARECONTINUEHANDLERFORNOTFOUNDSETs1；打开游标OPENreport；将游标中的值赋值给变量，注意：变量名不要与sql返回的列名相同，变量顺序要和sql结果列的顺序一致FETCHreportINTOtableName；当s不等于1时，也就是未遍历完时，会一直循环WHILEs1DO执行业务逻辑，创建新分表结构复制大数据表SETcreateSqlCONCAT（createtable，tableName，liketest；）；PREPAREstmtFROMcreateSql；EXECUTEstmt；当s等于1时代表遍历已完成，退出循环FETCHreportINTOtableName；ENDWHILE；关闭游标CLOSEreport；end；3。执行存储过程CALLcreatetables（）；4。删除存储过程DROPPROCEDUREIFEXISTScreatetables；
　　生成分表后数据库截图
　　2）数据导入到分表中
　　关键sql就是查询对应年月的数据插入到对象的分表中insertintotest202210selectfromtestwhereDATEFORMAT（createdate，Ym）202210
　　也可以像上面批量创建表一样，使用存储过程一次将大数据表的数据批量插入到对应的分表中。1。先删除存储过程DROPPROCEDUREIFEXISTSinsertdatas；2。创建存储过程CREATEPROCEDUREinsertdatas（）begin定义变量DECLAREsintDEFAULT0；DECLAREcreateSqlVARCHAR（2560）；DECLAREyyyyMMVARCHAR（255）；定义游标，并将sql结果集赋值到游标中，report为游标名test为大数据表明，查询出年月DECLAREreportCURSORFORselectdistinctDATEFORMAT（createdate，Ym）astablenamefromtest；声明当游标遍历完后将标识变量置为某个值DECLARECONTINUEHANDLERFORNOTFOUNDSETs1；打开游标OPENreport；将游标中的值赋值给变量，注意：变量名不要与sql返回的列名相同，变量顺序要和sql结果列的顺序一致FETCHreportINTOyyyyMM；当s不等于1时，也就是未遍历完时，会一直循环WHILEs1DO执行业务逻辑，创建新分表结构复制大数据表SETcreateSqlCONCAT（insertintotest，yyyyMM，selectfromtestwhereDATEFORMAT（createdate，Ym），yyyyMM，）；PREPAREstmtFROMcreateSql；EXECUTEstmt；当s等于1时代表遍历已完成，退出循环FETCHreportINTOyyyyMM；ENDWHILE；关闭游标CLOSEreport；end；3。执行存储过程CALLinsertdatas（）；4。删除存储过程DROPPROCEDUREIFEXISTSinsertdatas；
　　3）原来的大数据主表就没用了修改表名即可，后面使用mycat2查询的时候会创建对应的逻辑表进行查询。
　　这里将原来的表名修改掉很重要。
　　主表修改表名后截图mycat2下载
　　1）下载安装模板
　　http：dl。mycat。org。cn2。0installtemplatemycat2installtemplate1。21。zip
　　解压后重命名文件夹为mycat2
　　2）mycat2程序包
　　http：dl。mycat。org。cn2。01。21releasemycat21。21releasejarwithdependencies。jar
　　将下载的mycat21。21releasejarwithdependencies。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）confusersroot。user。json修改mycat2访问用户，程序或Navicat工具连接访问的用户。｛dialect：mysql，ip：null，password：123456，transactionType：proxy，username：root｝
　　3）confdatasourcesprototypeDs。datasource。json配置mycat2自带的数据源，主要修改password，url，user。随便配置一个正确的，自带的数据源没配置好像无法启动，作为搬砖的能跑我就觉得没研究的必要了〔呲牙〕。｛dbType：mysql，idleTimeout：60000，initSqls：〔〕，initSqlsGetConnection：true，instanceType：READWRITE，maxCon：1000，maxConnectTimeout：3000，maxRetryCount：5，minCon：1，name：prototypeDs，password：123456，type：JDBC，url：jdbc：mysql：localhost：3306mycat？useUnicodetrueserverTimezoneAsiaShanghaicharacterEncodingUTF8，user：root，weight：0｝
　　4）在confdatasources目录下将文件prototypeDs。datasource。json复制一个重命名为自己数据库的数据库名称。datasource。json文件。配置自己程序当前使用的数据库，我这里还是test数据库为例，主要修改name（数据源名称，后面其他配置会用到）、password、url、user｛dbType：mysql，idleTimeout：60000，initSqls：〔〕，initSqlsGetConnection：true，instanceType：READWRITE，maxCon：1000，maxConnectTimeout：3000，maxRetryCount：5，minCon：1，name：test，password：123456，type：JDBC，url：jdbc：mysql：localhost：3306test？useUnicodetrueserverTimezoneAsiaShanghaicharacterEncodingUTF8，user：root，weight：0｝
　　confdatasources目录
　　5）在confclusters目录下将文件prototype。cluster。json复制一个重命名为自己数据库的数据库名称。cluster。json文件。主要修改masters（就是上面设置的数据源名称）｛clusterType：MASTERSLAVE，heartbeat：｛heartbeatTimeout：1000，maxRetry：3，minSwitchTimeInterval：300，slaveThreshold：0｝，masters：〔test〕，maxCon：200，name：prototype，readBalanceType：BALANCEALL，switchType：SWITCH｝
　　6）在confschemas目录下创建数据库名称。schema。json文件。主要设置schemaName、targetName。所有关于名称的最好就和自己的数据库名称一样。其中shardingTables为分表设置信息。下面也提供了命令，但是命令执行的时候会在数据库中创建所有分表。
　　配置shardingTables中意思就是创建了test逻辑表，createTableSQL创建表的sql语句（这个创建表的语句就是之前的让修改表名的大数据表，创建表的sql语句可以在Navicat中直接复制），function指明使用的方法和分表的字段（这里是按月分表），partition指明具体的表，tableNames指分表范围｛customTables：｛｝，globalTables：｛｝，normalProcedures：｛｝，normalTables：｛｝，schemaName：test，shardingTables：｛test：｛createTableSQL：CREATETABLEtest（idbigint（20）NOTNULL，titletextCHARACTERSETutf8COLLATEutf8generalci，createdatedatetimeDEFAULTNULL，PRIMARYKEY（id））ENGINEInnoDBDEFAULTCHARSETutf8；，function：｛clazz：io。mycat。router。mycat1xfunction。PartitionByMonth，properties：｛beginDate：2022010100：00：00，dateFormat：yyyyMMddhh：mm：ss，endDate：，columnName：createdate｝，ranges：｛｝｝，partition：｛schemaNames：test，tableNames：test2022019，test20221012，test2023019，test20231012，test2024019，test20241012，test2025019，test20251012，targetNames：test｝，shardingIndexTables：｛｝｝｝，targetName：test，views：｛｝｝这个只是运行的命令当作参考，和上面的配置是一个操作。忽略这个就行了mycat：createTable｛schemaName：test，shardingTable：｛createTableSQL：CREATETABLEtest（idbigint（20）NOTNULL，titletextCHARACTERSETutf8COLLATEutf8generalci，createdatedatetimeDEFAULTNULL，PRIMARYKEY（id））ENGINEInnoDBDEFAULTCHARSETutf8；，function：｛clazz：io。mycat。router。mycat1xfunction。PartitionByMonth，properties：｛beginDate：2022010100：00：00，dateFormat：yyyyMMddhh：mm：ss，endDate：，columnName：createdate｝，ranges：｛｝｝，partition：｛schemaNames：test，tableNames：test2022019，test20221012，test2023019，test20231012，test2024019，test20241012，test2025019，test20251012，targetNames：test｝｝，tableName：test｝mycat2启动
　　1）window启动cmd使用管理员权限，进入到mycat2bin。
　　window里面需要先安装服务mycat。batinstall
　　启动mycat。batstart，也可以直接在服务中启动
　　2）linux直接进入binmycatstart，bin目录记得给权限chmodxmycat2bin
　　3）Navicat工具连接，和mysql一样，帐号密码端口就是上面配置中的信息。
　　为了方便测试我又手动创建跨年份的数据表，其中mycat2的数据库里面有个逻辑表test，查询的时候就会直接进行分表查询。
　　备注：不带日期查询的话是所有分表，就是上面配置中tableNames的所有表，但是数据库没有对应的表会报错，返回空接口。所以分表后每次查询必须带上分表时间字段createdate。selectfromtestwherecreatedateBETWEEN2022100100：00：00and2023010200：00：00
　　mycat2的数据库
　　真实数据库结语
　　文章中我只截出了关键性的代码方法，相信对需要的人提供帮助或参考。
　　希望需要的程序员可以直接使用CTRLV、CTRLC就能实现你的需求那是我写这篇文章最大的成就。