1500字范文 > mysql3表联表查询 Java开发分库分表需要解决的问题及mycat是怎样实现分库分表的 –

mysql3表联表查询 Java开发分库分表需要解决的问题及mycat是怎样实现分库分表的 –

时间：2020-05-31 05:28:25

关于分库分表

当然自己也理了一下，分库分表的这些内容，如果分成几个策略或者阶段，大概有下面的几种。

最上面的第一种是直接拆表，比如数据库db1下面有test1,test2,test3三个表，通过中间件看到的还是表test,里面的数据做了这样的拆分，能够在一定程度上分解压力，如果细细品来，和分区表的套路有些像。

接下来的几类也是不断完善，把表test拆解到多个库中，多个服务器中，如果做了读写分离，全套的方案这样的拆解改进还是很大的。如此来看，数据库中间件做了很多应用和数据库之间的很多事情，能够流行起来除了技术原因还是有很多其他的因素。

分库分表的测试环境模拟

如果要在一台服务器上测试分库分表，而且要求架构方案要全面，作为技术可行性的一个判定参考，是否可以实现呢。

如果模拟一主两从的架构，模拟服务分布在3台服务器上，这样的方案需要创建9个实例,每个实例上有3个db需要分别拆分。

大体的配置如下：

master1: 端口33091

(m1)slave1: 端口33092

(m1)slave2: 端口33093

master2: 端口33071

(m2)slave1: 端口33072

(m2)slave2: 端口33073

master3: 端口33061

(m3)slave1: 端口33062

(m3)slave2: 端口33063

画个图来说明一下，其中db1,db2,db3下面有若干表，需要做sharding

所以大家需要模拟的就是这个事情。

使用Mycat碰到的几个小问题解惑

使用Mycat的时候碰到了几个小问题，感觉比较有代表性，记录了一下。

问题1：

首先是使用Mycat连接到数据库之后，如果不切换到具体的数据库下，使用[数据库名].[表名]的方式会抛出下面的错误，可见整个过程中，Mycat拦截了SQL信息做了过滤，在转换的时候找不到目标路由。当然实际使用中，规范使用肯定不会有这个问题。

mysql> select * from db1.shard_auto;

ERROR 1064 (HY000): find no Route:select * from db1.shard_auto

问题2：

在配置了sharding策略之后，insert语句抛出了下面的错误，这个是对语法的一个基本的要求。

mysql> insert into shard_mod_long values(1,’aa’,date);

ERROR 1064 (HY000): partition table, insert must provide ColumnList

问题3：

如果sharding策略配置有误，很可能出现表访问正常，但是DML会有问题，提示数据冲突了。至于如何配置sharding,下面会讲。

mysql> select * from shard_mod_long;

Empty set (0.00 sec)

mysql> insert into shard_mod_long(ID,name,shard_date) values(1,’aa’,current_date);

ERROR 1105 (HY000): Duplicate entry ‘1’ for key ‘PRIMARY’

问题4：

如果sharding的配置有误，很可能出现多份冗余数据。

查看执行计划就一目了然，通过data_node可以看到数据指向了多个目标库。

mysql> explain insert into shard_auto(ID,name,shard_date) values(1,’aa’,current_date);

+———–+————————————————+

DATA_NODE | SQL

+———–+————————————————+

pxcNode11 | insert into shard_auto(ID,name,shard_date) values(1,’aa’,current_date)

pxcNode21 | insert into shard_auto(ID,name,shard_date) values(1,’aa’,current_date)

pxcNode31 | insert into shard_auto(ID,name,shard_date) values(1,’aa’,current_date)

+———–+————————————————+

这种情况如果有一定的需求还是蛮不错的，做sharding可惜了。问题就在于下面的这个table配置。

需要去掉 type=”global”的属性，让它sharding。

Mycat里面的sharding策略

Mycat的分片策略很丰富，这个是超出自己的预期的，也是Mycat的一大亮点。

大体分片规则如下，另外还有一些其他分片方式这里不全部列举：

（1）分片枚举：sharding-by-intfile

（2）主键范围：auto-sharding-long

（3）一致性hash：sharding-by-murmur

（4）字符串hash解析：sharding-by-stringhash

（5）按日期（天）分片：sharding-by-date

（6）按单月小时拆分：sharding-by-hour

（7）自然月分片：sharding-by-month

在开始之前，大家要创建下面的表来模拟几个sharding的场景,表名根据需求可以改变。

create table shard_test(ID int primary key, name varchar(20),shard_date date);

主键范围分片

主键范围分片是参考了主键值，按照主键值的分布来分布数据库在不同的库中，大家先在对应的sharding节点上创建同样的表结构。

关于sharding的策略，需要修改rule.xml文件。

常用的sharding策略已经在Mycat里面实现了，如果要自行实现也可以定制。比如下面的规则，是基于主键字段ID来做sharding,分布的算法是rang-long,引用了function rang-long,这个function是在对应的一个Java类中实现的。

<rule>

</rule>

<function name=”rang-long”

class=”io.mycat.route.function.AutoPartitionByLong”>

<property name=”mapFile”>autopartition-long.txt</property>

当然主键的范围是不固定的，可以根据需求来定制，比如按照一百万为单位，或者1000位单位，文件是 autopartition-long.txt 文件的内容默认如下，模板里是分为了3个分片，如果要定制更多的就需要继续配置了，目前来看这个配置只能够承载15亿的数据量，可以根据需求继续扩展定制。

# range start-end ,data node index

# K=1000,M=10000.

0-500M=0

500M-1000M=1

1000M-1500M=2

插入一些数据来验证一下，大家可以查看执行计划来做基本的验证，配置无误，数据就根据规则流向了指定的数据库下的表里。

mysql> explain insert into shard_auto(ID,name,shard_date) values(1,’aa’,current_date);

+———–+————————————————+

DATA_NODE | SQL

+———–+————————————————+

pxcNode11 | insert into shard_auto(ID,name,shard_date) values(1,’aa’,current_date)

+———–+————————————————+

还有一个查看sharding效果的小方法，比如偶插入一个极大的值，保证和其他数据不在一个分片上，大家运行查询语句两次，结果会有点变化。

sharing的效果

mysql> select * from shard_auto;

+———+——+————+

ID | name | shard_date

+———+——+————+

1 | aa | -09-06

2 | bb | -09-06

5000001 | aa | -09-06

+———+——+————+

3 rows in set (0.00 sec)

稍作停顿，继续运行。

mysql> select * from shard_auto;

+———+——+————+

ID | name | shard_date

+———+——+————+

5000001 | aa | -09-06

1 | aa | -09-06

2 | bb | -09-06

+———+——+————+

3 rows in set (0.01 sec)

Hash分片

Hash分片其实企业级应用尤其广泛，偶觉得一个原因是通过这种数据路由的方式，得到的数据情况是基本可控的，和业务的关联起来比较直接。很多拆分方法都是根据mod方法来平均分布数据。

sharding的策略在rule.xml里面配置，还是默认的mod-long规则，引用了算法mod-long,这里是根据sharding的节点数来做的，默认是3个。

<rule>

</rule>

</tableRule>

<!– how many data nodes –>

</function>

比如查看两次insert的结果情况。

mysql> explain insert into shard_mod_long(ID,name,shard_date) values(4,’dd’,current_date);

+———–+————————————————+

DATA_NODE | SQL

+———–+————————————————+

pxcNode22 | insert into shard_mod_long(ID,name,shard_date) values(4,’dd’,current_date)

+———–+————————————————+

mysql> explain insert into shard_mod_long(ID,name,shard_date) values(5,’ee’,current_date);

+———–+————————————————+

DATA_NODE | SQL

+———–+————————————————+

pxcNode23 | insert into shard_mod_long(ID,name,shard_date) values(5,’ee’,current_date)

+———–+————————————————+

可以看到数据还是遵循了节点的规律，平均分布。

至于schema.xml的配置，是整个分库的核心，偶索性也给出一个配置来，供参考。

<?xml version=”1.0″?>

<!DOCTYPE mycat:schema SYSTEM “schema.dtd”>

<mycat:schema xmlns:mycat=”http://io.mycat/”>

<!– 定义MyCat的逻辑库 –>

</schema>

<!– 定义MyCat的数据节点 –>

<!– 定义数据主机dtHost，连接到MySQL读写分离集群 ,schema中的每一个dataHost中的host属性值必须唯一–>

<!– dataHost实际上配置就是后台的数据库集群，一个datahost代表一个数据库集群 –>

<!– balance=”1″，全部的readHost与stand by writeHost参与select语句的负载均衡–>

<!– writeType=”0″，所有写操作发送到配置的第一个writeHost，这里就是大家的hostmaster，第一个挂了切到还生存的第二个writeHost–>

<dataHost name=”dtHost” maxCon=”500″ minCon=”20″ balance=”1″

writeType=”0″ dbType=”mysql” dbDriver=”native” switchType=”1″ slaveThreshold=”100″>

<!–心跳检测 –>

<heartbeat>show slave status</heartbeat>

<!–配置后台数据库的IP地址和端口号，还有账号密码 –>

</dataHost>

<dataHost name=”dtHost2″ maxCon=”500″ minCon=”20″ balance=”1″

writeType=”0″ dbType=”mysql” dbDriver=”native” switchType=”1″ slaveThreshold=”100″>

<!–心跳检测 –>

<heartbeat>show slave status</heartbeat>

<!–配置后台数据库的IP地址和端口号，还有账号密码 –>

</dataHost>

<dataHost name=”dtHost3″ maxCon=”500″ minCon=”20″ balance=”1″

writeType=”0″ dbType=”mysql” dbDriver=”native” switchType=”1″ slaveThreshold=”100″>

<!–心跳检测 –>

<heartbeat>show slave status</heartbeat>

<!–配置后台数据库的IP地址和端口号，还有账号密码 –>

</dataHost>

</mycat:schema>

=================================================================================================

用Mycat，学会数据库读写分离、分表分库

php疑难杂症铺 -09-13 14:31

用Mycat，学会数据库读写分离、分表分库

系统开发中，数据库是非常重要的一个点。除了程序的本身的优化，如：SQL语句优化、代码优化，数据库的处理本身优化也是非常重要的。主从、热备、分表分库等都是系统发展迟早会遇到的技术问题问题。Mycat是一个广受好评的数据库中间件，已经在很多产品上进行使用了。希望通过这篇文章的介绍，能学会Mycat的使用。

安装

Mycat官网：http://www.mycat.io/

可以了解下Mycat的背景和应用情况，这样使用起来比较有信心。

Mycat下载地址：http://dl.mycat.io/

官网有个文档，属于详细的介绍，初次入门，看起来比较花时间。

下载：

建议大家选择 1.6-RELEASE 版本，毕竟是比较稳定的版本。

安装：

根据不同的系统选择不同的版本。包括linux、windows、mac,作者考虑还是非常周全的，当然，也有源码版的。（ps:源码版的下载后，只要配置正确，就可以正常运行调试，这个赞一下。）

Mycat的安装其实只要解压下载的目录就可以了，非常简单。

安装完成后，目录如下：

目录说明binmycat命令，启动、重启、停止等catletcatlet为Mycat的一个扩展功能confMycat 配置信息,重点关注libMycat引用的jar包，Mycat是java开发的logs日志文件，包括Mycat启动的日志和运行的日志。

配置

Mycat的配置文件都在conf目录里面，这里介绍几个常用的文件：

文件说明server.xmlMycat的配置文件，设置账号、参数等schema.xmlMycat对应的物理数据库和数据库表的配置rule.xmlMycat分片（分库分表）规则

Mycat的架构其实很好理解，Mycat是代理，Mycat后面就是物理数据库。和Web服务器的Nginx类似。对于使用者来说，访问的都是Mycat，不会接触到后端的数据库。

大家现在做一个主从、读写分离，简单分表的示例。结构如下图：

服务器IP说明Mycat192.168.0.2mycat服务器，连接数据库时，连接此服务器database1192.168.0.3物理数据库1，真正存储数据的数据库database2192.168.0.4物理数据库2，真正存储数据的数据库

Mycat作为主数据库中间件，肯定是与代码弱关联的，所以代码是不用修改的，使用Mycat后，连接数据库是不变的，默认端口是8066。连接方式和普通数据库一样，如：jdbc:mysql://192.168.0.2:8066/

server.xml

示例

重点关注下面这段，其他默认即可。

参数说明user用户配置节点–name登录的用户名，也就是连接Mycat的用户名–password登录的密码，也就是连接Mycat的密码–schemas数据库名，这里会和schema.xml中的配置关联，多个用逗号分开，例如需要这个用户需要管理两个数据库db1,db2，则配置db1,dbs–privileges配置用户针对表的增删改查的权限，具体见文档吧

偶这里配置了一个账号test 密码也是test,针对数据库lunch,读写权限都有，没有针对表做任何特殊的权限。

schema.xml

schema.xml是最主要的配置项，首先看偶的配置文件。

参数说明schema数据库设置，此数据库为逻辑数据库，name与server.xml中schema对应dataNode分片信息，也就是分库相关配置dataHost物理数据库，真正存储数据的数据库

每个节点的属性逐一说明：

schema:

属性说明name逻辑数据库名，与server.xml中的schema对应checkSQLschema数据库前缀相关设置，建议看文档，这里暂时设为folsesqlMaxLimitselect 时默认的limit，避免查询全表

table:

属性说明name表名，物理数据库中表名dataNode表存储到哪些节点，多个节点用逗号分隔。节点为下文dataNode设置的nameprimaryKey主键字段名，自动生成主键时需要设置autoIncrement是否自增rule分片规则名，具体规则下文rule详细介绍

dataNode

属性说明name节点名，与table中dataNode对应datahost物理数据库名，与datahost中name对应database物理数据库中数据库名

dataHost

属性说明name物理数据库名，与dataNode中dataHost对应balance均衡负载的方式writeType写入方式dbType数据库类型heartbeat心跳检测语句，注意语句结尾的分号要加。

应用场景

数据库分表分库

配置如下：

偶在192.168.0.2、192.168.0.3均有数据库lunch。

lunchmenu、restaurant、userlunch、users这些表都只写入节点dn1，也就是192.168.0.2这个服务，而dictionary写入了dn1、dn2两个节点，也就是192.168.0.2、192.168.0.3这两台服务器。分片的规则为：mod-long。

主要关注rule属性，rule属性的内容来源于rule.xml这个文件，Mycat支持10种分表分库的规则，基本能满足你所需要的要求，这个必须赞一个，其他数据库中间件好像都没有这么多。

table中的rule属性对应的就是rule.xml文件中tableRule的name,具体有哪些分表和分库的实现，建议还是看下文档。偶这里选择的mod-long就是将数据平均拆分。因为偶后端是两台物理库，所以rule.xml中mod-long对应的function count为2，见下面部分代码：

数据库读写分离

配置如下：

这样的配置与前一个示例配置改动如下：

删除了table分配的规则,以及datanode只有一个

datahost也只有一台，但是writehost总添加了readhost,balance改为1，表示读写分离。

以上配置达到的效果就是102.168.0.2为主库，192.168.0.3为从库。

注意：Mycat主从分离只是在读的时候做了处理，写入数据的时候，只会写入到writehost，需要通过mycat的主从复制将数据复制到readhost，这个问题当时候偶纠结了好久，数据写入writehost后，readhost一直没有数据，以为是自己配置的问题，后面才发现Mycat就没有实现主从复制的功能，毕竟数据库本身自带的这个功能才是最高效稳定的。

至于其他的场景，如同时主从和分表分库也是支持的了，只要了解这个实现以后再去修改配置，都是可以实现的。而热备及故障专业官方推荐使用haproxy配合一起使用，大家可以试试。

使用

Mycat的启动也很简单，启动命令在Bin目录：

如果在启动时发现异常，在logs目录中查看日志。

wrapper.log 为程序启动的日志，启动时的问题看这个

mycat.log 为脚本执行时的日志，SQL脚本执行报错后的具体错误内容,查看这个文件。mycat.log是最新的错误日志，历史日志会根据时间生成目录保存。

mycat启动后，执行命令不成功，可能实际上配置有错误，导致后面的命令没有很好的执行。

Mycat带来的最大好处就是使用是完全不用修改原有代码的，在mycat通过命令启动后，你只需要将数据库连接切换到Mycat的地址就可以了。如下面就可以进行连接了：

连接成功后可以执行sql脚本了。

所以，可以直接通过sql管理工具（如：navicat、datagrip）连接，执行脚本。偶一直用datagrip来进行日常简单的管理，这个很方便。

Mycat还有一个管理的连接，端口号是9906.

连接后可以根据管理命令查看Mycat的运行情况，当然，喜欢UI管理方式的人，可以安装一个Mycat-Web来进行管理，有兴趣自行搜索。