MyCat分库分表中间件研究
MyCat分库分表中间件研究
MyCat简介
MyCat是安装在服务器上的中间件工具服务，业务服务可直接连接MyCat,由MyCat做sql改写分发结果归集，归并数据结果完全解耦，保证数据库的安全性，支持多种开发语言的连接。不用调整代码即可实现分库分表，将数据库连接地址改为MyCat的地址即可。MyCat作为数据库中间件，直接架在项目与数据库之间，好处是可插拔性好，对逻辑业务的侵入很小，而且使用navicat或其他数据库客户端就可以连接mycat代理的数据库，测试很方便。单库分表插入和单表查询没问题，缺点是分区表与其他表做连表查询会出错，提示一般为某字段不存在Unknown column ‘p.id’，这个问题社区有不少人反映，但是应该是没有解决方案，这也是我说MyCat较不稳定的原因。
MyCat安装配置
MyCat安装
1、 安装 JAVA 环境， JDK8 或更高版本。并配置环境变量。可以下图命令验证是否安装成功。
2、 下载 MyCAT，解压。
下载地址 http://dl.mycat.org.cn/1.6.7.6/
解压后路径如下：
3、 MyCat的配置：
常用配置文件主要有三个，根据按月分表要求进行配置如下。
server.xml负责配置mysql连接，mycat的用户名密码等。
<user name="root" defaultAccount="true">
<property name="password">123456</property>
<property name="schemas">DB1</property>
<property name="defaultSchema">DB1</property>
</user>
<user name="user">
<property name="password">user</property>
<property name="schemas">DB1</property>
<property name="readOnly">true</property>
<property name="defaultSchema">DB1</property>
</user>
rule.xml 负责配置分库分表策略，官网版本内置有很多策略的示例,下面的例子是使用时间按月分表。
<function name="partbymonth"
class="io.mycat.route.function.PartitionByMonth">
<property name="dateFormat">yyyy-MM-dd</property>
<property name="sBeginDate">2019-09-01</property>
</function>
schema.xml分库分表的配置都在schema.xml 中，下面描述的是对measure_datainfo表分了2019年9月到2022年6月分表。
<schema name="DB1" checkSQLschema="false" sqlMaxLimit="10000" dataNode="dn1">
<table name="measure_datainfo"
subTables="measure_datainfo_2019$09-12,measure_datainfo_2020$01-12,measure_datainfo_2021$01-12,measure_datainfo_2022$01-06"
dataNode="dn1"
rule="sharding-by-month">
</table>
</schema>
MySQL数据库配置
1. 创建数据库及表结构
创建新数据库DB1用来存储历史数据,根据历史数据分布时间段201909-202206创建如下数据表。
measure_datainfo_201910
measure_datainfo_201911
measure_datainfo_201912
measure_datainfo_20199
measure_datainfo_20201
measure_datainfo_202010
measure_datainfo_202011
measure_datainfo_202012
measure_datainfo_20202
measure_datainfo_20203
measure_datainfo_20204
measure_datainfo_20205
measure_datainfo_20206
measure_datainfo_20207
measure_datainfo_20208
measure_datainfo_20209
measure_datainfo_20211
measure_datainfo_202110
measure_datainfo_202111
measure_datainfo_202112
measure_datainfo_20212
measure_datainfo_20213
measure_datainfo_20214
measure_datainfo_20215
measure_datainfo_20216
measure_datainfo_20217
measure_datainfo_20218
measure_datainfo_20219
measure_datainfo_20221
measure_datainfo_20222
measure_datainfo_20223
measure_datainfo_20224
measure_datainfo_20225
measure_datainfo_20226
2. 迁移measure_datainfo大表到分表后数据库db1。
使用mysqldump进行数据迁移
mysqldump是mysql自带的命令行工具。可以用它完成全库迁移（从一个mysql库完整迁移到mycat），也可以迁移某几个表，还可以迁移某个表的部分数据。
迁移前准备
迁移前确保mysql库和mycat库中的表名一样（mycat库中只需要有表名配置在schema.xml文件中即可）
从mysql导出
从mysql库上全库导出 mysqldump -c -–skip-add-locks measure_datainfo> D:/measure_datainfo.sql
注意：（上面的语句没有-uroot -ppassword参数，是因为mysql服务器设置了本机免密码等。
如果设置了密码:通过以下命名导出(用户名为root，密码为123456)： mysqldump -uroot -p123456 -c -–skip-add-locks measure_datainfo > D:/measure_datainfo.sql
导入到mycat
将measure_datainfo.sql拷贝到mycat集群中的一台mysql服务器上D盘目录下。
连接mycat：
mysql -uroot -p123456 -h127.0.0.1 -P8066 --default-character-set=utf8
切换到指定的数据库： use db1;
导入脚本： source D:/ measure_datainfo.sql;
MyCat使用
介绍了MyCat中间件基本使用及如何利用其实现分库分表。
1.启动mycat
以下两种均为启动mycat服务的方式,根据环境选择以下即可
linux启动命令
cd mycat/bin
./mycat start
./mycat status
./mycat start 启动
./mycat stop 停止
./mycat console 前台运行
./mycat install 添加到系统自动启动（暂未实现）
./mycat remove 取消随系统自动启动（暂未实现）
./mycat restart 重启服务
./mycat pause 暂停
./mycat status 查看启动状态
windows启动命令（如下图所示）
cd mycat/bin
命令行CMD
mycat install
mycat start
mycat status
WINDOWS命令行启动mycat
2.连接使用
可以使用多种客户端连接MyCat，注意mysql自带工具workbench6.3不支持连接操作(仅测试过此版本)。下面使用navicate连接举例基本使用过程。
连接成功后与mysql操作一样。双击数据库DB1，即可查看所有表。
3.数据查询
按照时间段进行查询，要使用between … and …，否则所有分区表联查速度较慢导致客户端卡死。
例1：
SELECT
ACQUISITION_TIME,
P_VALUE
FROM
measure_datainfo
WHERE
ACQUISITION_TIME BETWEEN "2019-09-01 00:00:01"
AND "2020-05-01 00:00:01"
AND MP_ID = 200240
ORDER BY
ACQUISITION_TIME ASC;
例2：MyCAT为了限制不带任何过过滤条件的查询（尤其是大表），给服务器带来的额外压力，默认返回100行数据。
select * from table where xxx=xxx
SQL语句查询结果比实际条数少，在sql语句没有加limit关键字，且符合条件的条目大于100个时，查询结果只返回了100条数据。当查询SQL中加入 limit 限制之后，返回结果以语句中限制为准。对大表的分页查询，请提交DBA审核。
因为涉及到所有查询语句，所以在使用MyCat后，每条语句都要经过审核。可以在schema.xml
配置文件修改参数配置
<schema name="push_center" checkSQLschema="false" sqlMaxLimit="100">
例3：通过在Navicate 连接8066客户端explain查询语句，可以看出mycat 对time> <等，是不会进行区分库的，查询都查，直接死掉；但between MyCat是可以直接自动查询分区的表！
编写SQL注意点：
结论１：时间段查询时，用between。
结论2：用count(1) 取代count(*)。
结论3：对于超过千万级的表，需要再分库。
结论4：不支持 Insert into 中不包括字段名的 SQL。
结论5：不支持的SQL语句如下：
create table like xxx
create table select xxx
select for update
select lock in share mode
select into outfile/into var_name
不支持跨库多表关联查询、子查询。
不支持多表UPDATE或者UPDATE分片键。
不支持跨分片的update/delete [order by] limit
4.常见问题
分片的限制
分片字段不能改变值
非范围分片，扩容难度大，一致性Hash也只是做出了一定的改善
分片导致某些SQL语句的执行结果不符合预期，可以视为不支持
分片产生了跨分片排序和分组统计等问题（内存消耗）
分片产生了表关联问题
分片产生了分布式事务问题
分片的一般原则
每个分片的记录量为1000万左右，分片数量是双刃剑，不易过多
分片字段不支持多个字段的组合
原则上以最频繁查询的字段为分片字段
分片字段的选择很大程度上影响系统性能
非分片字段的查询语句，会并发发送到每个分片上去，导致资源消耗更多
参考文献
Mycat实战之 between：https://blog.csdn.net/dualvencsdn/article/details/86609925
Mycat分片的限制和问题以及分片的一般原则介绍http://blog.itpub.net/15498/viewspace-2138686
