mycat分库分表_mycat 分表分库 demo-CSDN博客

本文链接：https://blog.csdn.net/qq_36501760/article/details/95318988

数据库分库分表（拆分）

数据库（表）的水平和垂直拆分

1、垂直拆分

将100张表分别放在5太服务器上，也就是每台数据库都只有20张表。

技巧：将不同服务所对应的表放在不同的数据库中。目的：

2、水平拆分

一般一个表的数据太慢，首先应该是在条件字段上建立索引，但是当数据特别大的时候，1个亿的数据，索引查询时间也会非常长。此时，当数据量达到一亿时，就需要考虑表的水平拆分。
一张表有4000w条数据，我们可以分别放在5张表中，这样每张表只有800w条数据。

总结：

1、不论是水平或者垂直拆分，都是为了解决数据库的访问效率问题。

2、垂直拆分解决表过多导致的效率慢，比如出现排队阻塞问题。

3、水平拆分解决单表的数据过多，导致的查询效率慢的问题。

MyCat简介

MyCat是一个开源数据库分布式中间件。MyCat是基于阿里Cobar基础之上扩展而来。官方网站：http://www.mycat.io/ 。

MyCat的功能：实现数据库的分库分表。

shared-jdbc

MyCat配置和安装（Windows版本）

1、下载

http://dl.mycat.io/1.6-RELEASE/

MyCat的错误可以在安装目录下的logs文件夹下的wrapper.log中分析。

2、准备

允许mysql远程连接： 1、修改mysql库中的user表，给root用户新增一个host字段为%。（待定，直接做第二步）。

2、执行远程授权命令：GRANT ALL PRIVILEGES ON . TO ‘root’@’%’ IDENTIFIED BY ‘youpassword’ WITH GRANT OPTION

在数据库服务器A和数据库服务器B上安装MySQL数据库。然后，在A和B上分别创建db1，和表tb1。

例如：在linux服务器上创建三个数据库，分别命名db1，db2，db3，然后在db1\2\3上分别创建同一个表tb_demo。（水平拆分）

准备：

在linux上创建2个数据库，分别是db1和db2.

在windows上床架一个数据库，是db3.

分别在三个库上创建同一个表：

create table tb_stu( -> stu_id int primary key auto_increment, -> stu_name varchar(20), -> stu_age int -> );

3、MyCat系统参数配置(server.xml)

<?xml version="1.0" encoding="UTF-8" standalone="no"?>

<mycat:server xmlns:mycat=“http://org.opencloudb/”>

…

druidparser
…

<!--重点：配置用户名和密码-->
<user name="root">
		<property name="password">123456</property>
		<!--当前用户对应的逻辑数据库-->
		<property name="schemas">TESTDB</property>
		
		<!-- 表级 DML 权限设置 -->
		<!-- 		
		<privileges check="false">
			<schema name="TESTDB" dml="0110" >
				<table name="tb01" dml="0000"></table>
				<table name="tb02" dml="1111"></table>
			</schema>
		</privileges>		
		 -->
</user>

</mycat:server>
4、MyCat的逻辑库配置（schema.xml）

在schema.xml文件中配置逻辑库和表分片规则。

<?xml version="1.0" encoding="UTF-8"?>

<mycat:schema xmlns:mycat=“http://org.opencloudb/”>

show status like 'wsrep%' 配置说明：

table 标签是逻辑表的配置

name代表表名，

dataNode代表表对应的分片，

Mycat默认采用分库方式，也就是一个表映射到不同的库上，

rule代表表要采用的数据切分方式，名称对应到rule.xml中的对应配置，如果要分片必须配置。

datahost标签属性
–name 唯一标示dataHost标签，供上层使用
–maxCon 指定每个读写实例连接池的最大连接。
–minCon 指定每个读写实例连接池的最小连接，初始化连接池的大小
–balance 负载均衡类型
balance=“0”：不开启读写分离机制，所有读操作都发送到当前可用的writeHost上
balance=“1”：全部的readHost与stand by writeHost参与select语句的负载均衡，简单的说，当双主双从模式（M1-S1，M2-S2 并且M1 M2互为主备），正常情况下，M2,S1,S2都参与select语句的负载均衡。
balance=“2”：所有读操作都随机的在writeHost、readHost上分发
balance=“3”：所有读请求随机的分发到writeHst对应的readHost执行，writeHost不负担读压力。（1.4之后版本有）
–writeType 负载均衡类型。
writeType=“0”, 所有写操作发送到配置的第一个 writeHost，第一个挂了切到还生存的第二个writeHost，重新启动后已切换后的为准，切换记录在配置文件中:dnindex.properties .
writeType=“1”，所有写操作都随机的发送到配置的 writeHost。1.5以后版本废弃不推荐。
–switchType -1不自动切换
1 默认值自动切换
2 基于MySql主从同步的状态决定是否切换心跳语句为 show slave status
3 基于mysql galary cluster 的切换机制（适合集群）1.4.1 心跳语句为 show status like ‘wsrep%’
–dbType 指定后端链接的数据库类型目前支持二进制的mysql协议，还有其他使用jdbc链接的数据库，例如：mongodb，oracle，spark等
–dbDriver 指定连接后段数据库使用的driver，目前可选的值有native和JDBC。使用native的话，因为这个值执行的是二进制的mysql协议，所以可以使用mysql和maridb，其他类型的则需要使用JDBC驱动来支持。
如果使用JDBC的话需要符合JDBC4标准的驱动jar 放到mycat\lib目录下，并检查驱动jar包中包括如下目录结构文件 META-INF\services\java.sql.Driver。在这个文件写上具体的driver类名，例如com.mysql.jdbc.Driver
writeHost readHost指定后端数据库的相关配置给mycat，用于实例化后端连接池。
–tempReadHostAvailable
如果配置了这个属性 writeHost 下面的 readHost 仍旧可用，默认 0 可配置（0、1）。
heartbeat 标签代表Mycat需要对物理库心跳检测的语句，正常情况下生产案例可能配置主从，或者多写或者单库，无论哪种情况Mycat都需要维持到数据库的数据源连接，因此需要定时检查后端连接可用性，心跳语句就是来作为心跳检测。

writeHost 此标签代表一个逻辑主机（dataHost）对应的后端的物理主机映射，例如例子中写库hostM1 映射到127.0.0.1:3306。如果后端需要做读写分离或者多写或者主从则通过配置多个writeHost 或者readHost即可。

5、分片规则(rule.xml)

分片规则是非常重要的配置，其决定了数据库切分后的性能好坏。

<?xml version="1.0" encoding="UTF-8"?>

<mycat:rule xmlns:mycat=“http://org.opencloudb/”>

createTime
sharding-by-hour

</mycat:rule >

name 为schema.xml 中table 标签中对应的 rule=“sharding-by-hour” ,也就是配置表的分片规则，

columns 是表的切分字段： createTime 创建日期。

algorithm 是规则对应的切分规则：映射到function 的name。

function 配置是分片规则的配置。

name 为切分规则的名称，名字人员取，但是需要与tableRule 中匹配。

class 是切分规则对应的切分类，写死，需要哪种规则则配置哪种，例如本例子是按小时分片：org.opencloudb.route.function.LatestMonthPartion

property 标签是切分规则对应的不同属性，不同的切分规则配置不同。

6、启动MyCat

进入mycat的安装目录的bin目录下（或者配置环境变量）,以管理员身份打开CMD命令。

1、安装 mycat.bat install

2、启动 mycat.bat start

3、停止 mycat.bat stop

链接： mysql -u用户名 -p密码 -P8066 -h127.0.0.1

MyCat的链接方式和MySQL一模一样。默认端口是8066。

分片规则详细

一、范围分片

user_id rang-long autopartition-long.txt autopartition-long.txt配置内容

range start-end ,data node index

K=1000,M=10000.

0-500M=0 //表示第一个数据库的存储0到500万的数据
500M-1000M=1 //表示第二个数据库存储500万到1000万的数据
1000M-1500M=2 //表示第三个数据库存储1000万到1500万的数据
二、按照日期分片

create_time sharding-by-date yyyy-MM-dd 2018-05-01 5

配置说明：

上面columns 标识将要分片的表字段，algorithm 分片函数，

配置中配置了开始日期(sBeginDate)，分区天数(sPartionDay)，即默认从开始日期算起，分隔5天一个分区

MyCat的基本SQL操作

1、聚合函数等

2、分页查询

3、join操作（mycat仅仅支持2张表的联合查询）

4、事务操作：比如事务回滚。（mycat支持分布式事务）

MyCat的主从配置

image

读写分离

在主服务器上进行增删改操作，在从服务器上进行读操作。在实际项目中，读操作是占比非常大的操作，分离开来能减轻服务器的压力。并且减少死锁的概率。

读写分离的实现

实现比较简单，在项目中配置两个数据源就ok。

备份：：：：

<schema name="MYCAT_1704" checkSQLschema="false" sqlMaxLimit="100">
	<!-- 配置逻辑表，name是逻辑表名，dataNode映射的物理表的地址，rule：分表规则 -->
	<!-- auto-sharding-long根据数据的ID进行分表：0-50000；50001-100000；100001-150000-->
	<!-- auto sharding by id (long) -->
	<table name="tb_book" dataNode="mycat_1,mycat_2" rule="auto-sharding-long" />

</schema>

<!-- dataNode是一个数据节点，就是指代一个物理数据库
		name:当前dataNode的名称(不能重复)
		dataHost：是当前物理数据库的主机地址
		database:就是物理数据库
-->
<dataNode name="mycat_1" dataHost="mycat_host" database="db_mycat_1704" />
<!--<dataNode name="mycat_2" dataHost="mycat_host_linux" database="db_mycat_1704_linux" />
 dataHost就是物理主机地址 ，name是唯一标识dataHost的值。maxCon\minCon最大和最小连接池，
balance\writeType跟主从分区有关，一般情况采用默认值就行
dbType：物理数据库的类型，默认MySQL。dbDriver:MySQL默认是native，其他数据库另外一个值JDBC。
-->
<dataHost name="mycat_host" maxCon="1000" minCon="10" balance="0"
		  writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
		  <!-- 心跳 ：用来检测MySQL客户端是否连接正常 -->
	<heartbeat>select user()</heartbeat>
	<!-- can have multi write hosts -->
	<writeHost host="hostM1" url="localhost:3306" user="root"
			   password="123456" />
	<!-- <writeHost host="hostM2" url="localhost:3316" user="root" password="123456"/> -->
</dataHost>

<dataHost name="mycat_host_linux" maxCon="1000" minCon="10" balance="0"
		  writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
		  <!-- 心跳 ：用来检测MySQL客户端是否连接正常 -->
	<heartbeat>select user()</heartbeat>
	<!-- can have multi write hosts -->
	<writeHost host="hostM1" url="192.168.72.188:3306" user="root"
			   password="123456" />
	<!-- <writeHost host="hostM2" url="localhost:3316" user="root" password="123456"/> -->
</dataHost>

正确的配置：(auto-sharding-long默认需要有3个dataNode)

<?xml version="1.0"?>

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

<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100">
	<!-- auto sharding by id (long) -->
	<table name="tb_book" primaryKey="ID" dataNode="dn1,dn2,dn3" rule="auto-sharding-long" />
	<!-- <table name="oc_call" primaryKey="ID" dataNode="dn1$0-743" rule="latest-month-calldate"
		/> -->
</schema>
<!-- <dataNode name="dn1$0-743" dataHost="localhost1" database="db$0-743"
	/> -->
<dataNode name="dn1" dataHost="localhost1" database="db_mycat_1704" />
<dataNode name="dn2" dataHost="linuxhost" database="db_mycat_1704_linux" />
<dataNode name="dn3" dataHost="localhost1" database="java1" />
<!--<dataNode name="dn4" dataHost="sequoiadb1" database="SAMPLE" />
 <dataNode name="jdbc_dn1" dataHost="jdbchost" database="db1" />
<dataNode	name="jdbc_dn2" dataHost="jdbchost" database="db2" />
<dataNode name="jdbc_dn3" 	dataHost="jdbchost" database="db3" /> -->
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="0"
		  writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
	<heartbeat>select user()</heartbeat>
	<!-- can have multi write hosts -->
	<writeHost host="hostM1" url="localhost:3306" user="root"
			   password="123456">
	</writeHost>
	<!-- <writeHost host="hostM2" url="localhost:3316" user="root" password="123456"/> -->
</dataHost>

<dataHost name="linuxhost" maxCon="1000" minCon="10" balance="0"
		  writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
	<heartbeat>select user()</heartbeat>
	<!-- can have multi write hosts -->
	<writeHost host="hostM1" url="192.168.72.188:3306" user="root"
			   password="123456">
	</writeHost>
	<!-- <writeHost host="hostM2" url="localhost:3316" user="root" password="123456"/> -->
</dataHost>

</mycat:schema>
数据拆分之后的问题

1、ID自增长将会比较困难（作业，提示：可以使用redis）。

2、单数据库事务的ACID特性将会受到影响。

3、外键约束的场景会收到影响。

4、join操作将会收到影响。

schame.xml

<?xml version="1.0"?>

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

	<!-- global table is auto cloned to all defined data nodes ,so can join
		with any table whose sharding node is in the same data node -->
	<table name="company" primaryKey="ID" type="global" dataNode="dn1,dn2,dn3" />
	<table name="goods" primaryKey="ID" type="global" dataNode="dn1,dn2" />
	<!-- random sharding using mod sharind rule -->
    <!--primaryKey主键，autoIncrement主键自增 -->
	<table name="hotnews" primaryKey="ID" autoIncrement="true" dataNode="dn1,dn2,dn3"
		   rule="mod-long" />
	<!-- <table name="dual" primaryKey="ID" dataNode="dnx,dnoracle2" type="global"
		needAddLimit="false"/> <table name="worker" primaryKey="ID" dataNode="jdbc_dn1,jdbc_dn2,jdbc_dn3"
		rule="mod-long" /> -->
	<table name="employee" primaryKey="ID" dataNode="dn1,dn2"
		   rule="sharding-by-intfile" />
	<table name="customer" primaryKey="ID" dataNode="dn1,dn2"
		   rule="sharding-by-intfile">
		<childTable name="orders" primaryKey="ID" joinKey="customer_id"
					parentKey="id">
			<childTable name="order_items" joinKey="order_id"
						parentKey="id" />
		</childTable>
		<childTable name="customer_addr" primaryKey="ID" joinKey="customer_id"
					parentKey="id" />
	</table>
	<!-- <table name="oc_call" primaryKey="ID" dataNode="dn1$0-743" rule="latest-month-calldate"
		/> -->
</schema>
<!-- <dataNode name="dn1$0-743" dataHost="localhost1" database="db$0-743"
	/> -->
<!--dataNode定义的物理库的地址（dataHost）和物理库的名称（database） -->
<dataNode name="dn1" dataHost="localhost1" database="java1" />
<dataNode name="dn2" dataHost="localhost1" database="java2" />
<dataNode name="dn3" dataHost="localhost1" database="mydb" />
<!--<dataNode name="dn4" dataHost="sequoiadb1" database="SAMPLE" />
 <dataNode name="jdbc_dn1" dataHost="jdbchost" database="db1" />
<dataNode	name="jdbc_dn2" dataHost="jdbchost" database="db2" />
<dataNode name="jdbc_dn3" 	dataHost="jdbchost" database="db3" /> -->
<!-- 配置物理主机的信息-->
<!-- maxCon和minCon是最大和最小连接数，balance表示是否支持负载均衡-->
<!-- dbType表示物理数据的类型 -->
<!-- dbDriver配置数据库的驱动，native：默认采用mycat中已经存在的mysql驱动 -->
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="0"
		  writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
    <!-- 心跳：用来检测数据库是否正常连接 。mysql默认是select user()-->
	<heartbeat>select user()</heartbeat>
	<!-- can have multi write hosts -->
    <!-- 读写分离：写服务器 -->
	<writeHost host="hostM1" url="localhost:3306" user="root"
			   password="123456">
		<!-- can have multi read hosts -->
        <!-- 读服务器的配置 -->
		<readHost host="hostS2" url="localhost:3306" user="root" password="xxx" />
	</writeHost>
	<writeHost host="hostS1" url="localhost:3316" user="root"
			   password="123456" />
	<!-- <writeHost host="hostM2" url="localhost:3316" user="root" password="123456"/> -->
</dataHost>
<!--
	<dataHost name="sequoiadb1" maxCon="1000" minCon="1" balance="0" dbType="sequoiadb" dbDriver="jdbc">
	<heartbeat> 		</heartbeat>
	 <writeHost host="hostM1" url="sequoiadb://1426587161.dbaas.sequoialab.net:11920/SAMPLE" user="jifeng" 	password="jifeng"></writeHost>
	 </dataHost>

  <dataHost name="oracle1" maxCon="1000" minCon="1" balance="0" writeType="0" 	dbType="oracle" dbDriver="jdbc"> <heartbeat>select 1 from dual</heartbeat>
	<connectionInitSql>alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'</connectionInitSql>
	<writeHost host="hostM1" url="jdbc:oracle:thin:@127.0.0.1:1521:nange" user="base" 	password="123456" > </writeHost> </dataHost>

	<dataHost name="jdbchost" maxCon="1000" 	minCon="1" balance="0" writeType="0" dbType="mongodb" dbDriver="jdbc">
	<heartbeat>select 	user()</heartbeat>
	<writeHost host="hostM" url="mongodb://192.168.0.99/test" user="admin" password="123456" ></writeHost> </dataHost>

	<dataHost name="sparksql" maxCon="1000" minCon="1" balance="0" dbType="spark" dbDriver="jdbc">
	<heartbeat> </heartbeat>
	 <writeHost host="hostM1" url="jdbc:hive2://feng01:10000" user="jifeng" 	password="jifeng"></writeHost> </dataHost> -->

<!-- <dataHost name="jdbchost" maxCon="1000" minCon="10" balance="0" dbType="mysql"
	dbDriver="jdbc"> <heartbeat>select user()</heartbeat> <writeHost host="hostM1"
	url="jdbc:mysql://localhost:3306" user="root" password="123456"> </writeHost>
	</dataHost> -->

</mycat:schema>