系列学习 MySQL 之第 5 篇 —— Mycat 分库分表策略（分片枚举、求模算法）（mycat 完结篇）

数据库集群有什么问题？

1、自增ID会重复的问题。

     解决办法是：①使用 UUID 做全局唯一ID，不过有个缺点是无法排序，优势是可以提高扩展性。

     ②设置自增的步长（每次增长加多少），比如有2台MySQL服务器，第设置的步长都是2，然后第一台设置初始值从0开始，它的ID就是2、4、6...  然后第二台设置初始值从1开始，它的ID就是3、5、7...。不过缺点是：设置 MySQL 集群时，必须确定有多少台机器，才能设置好步长，跟 ElasticSearch 集群分片一样。

    查询自增的步长：注意，不要使用网上说的那种方式：SHOW VARIABLES LIKE 'auto_inc%'，这样的方式是当前会话才生效，关闭客户端连接就失效了！本人亲测过。

# 查看全局变量中的自增长的默认步长
show global variables like 'auto_inc%';

结果：offset是起始值，increment是步长。

然后修改全局的自增步长、起始值： 不要相信网上那些设置 SET @@auto_increment_increment=3;  SET @@auto_increment_offset=5;  这些只是单次会话生效，重启服务器就失效了！

# 设置全局会话步长
set global auto_increment_increment=2;

#设置全局自增长初始值
set global auto_increment_offset=3;

结果：

执行完之后，再次查询：注意，这个是针对某个数据库的自增步长、起始值进行修改的，而不是针对某个表，也不会对其它数据库有影响。

这时候，我们验证一下：新建一个表，然后插入几条数据。

drop table if exists t_test;
CREATE TABLE `t_test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

insert into t_test
values 
(null,'我是张三'),
(null,'我叫李四'),
(null,'他是王五');

select * from t_test;

结果：id自动增长值为2，验证通过。

 

2、数据关联查询的问题。

数据库分库分表的原则遵循：垂直拆分、水平拆分。垂直拆分是把不同的表拆到不同的数据库中，而水平拆分是把同一个表拆到不同的数据库中（或者把一张表拆分成多个小表）。

垂直拆分

就是根据不同的业务，分为不同的数据库，比如会员数据库、订单数据库、库存数据库等，垂直拆分在大型电商系统中用的非常常见。

优点：拆分后业务清晰，拆分规则明确，系统之间整合或扩展容易。 这与微服务架构思想一致。

缺点：部分业务表无法 join，只能通过接口方式解决，提高了系统复杂度存在分布式事务问题。

 

水平拆分

按照某个字段的某种规则来分散到多个库之中，每个表中包含一部分数据。简单来说，我们可以将数据的水平拆分理解为是按照数据行的拆分，就是将表中的某些行拆分到一个数据库，而另外的某些行又拆分到其他的数据库中，主要有分表，分库两种模式。该方式提高了系统的稳定性跟负载能力，但是跨库 join 性能较差。

 

3、数据同步问题（可使用主从同步功能解决）。

 

使用MyCat实现水平分片策略

MyCat支持多种分片策略

1、分片枚举 （重点掌握）

2、求模算法（重点掌握）

3、范围约定

4、日期指定

5、固定分片hash算法

6、通配取模

7、ASCII码求模通配

8、编程指定

9、字符串拆分hash解析

 

Mycat 分片枚举（重点掌握）

分片枚举这种规则适用于特定的场景，比如按照省市区划分，一个地区划分到一个数据库中，根据枚举值进入到对应的数据库中查询。

我们来学习一下 Mycat 的分片枚举。

1、使用5.7版本的主数据库创建3个数据库：area_shenzhen（存放深圳地区的用户信息），area_guangzhou（存放广州地区的用户信息），area_beijing（存放北京地区的用户信息），这时候【从数据库5.6】也会自动创建了3个数据库（不是本篇博客重点，这是数据库主从复制的机制）。然后在每个数据库里都创建一个表：t_area

CREATE TABLE `t_area` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `area_name` varchar(255) DEFAULT NULL COMMENT '地区名',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

2、我们打开 mycat 的配置文件 conf，可以看到有一个文件 partition-hash-int.txt，这个文件是 mycat 的分片配置文件，我们先备份一份，免得改错。

然后修改  partition-hash-int.txt 的文件内容如下：说明：如果用户信息是北京的，去序号为0的数据库操作。依次类推。

beijing=0
guangzhou=1
shenzhen=2

3、然后再备份 conf 的 rule.xml 配置文件，这个文件是 mycat 的定义分片的分库分表的配置规则文件。

然后修改 rule.xml 的文件内容如下：

<?xml version="1.0" encoding="UTF-8"?>
<!-- - - Licensed under the Apache License, Version 2.0 (the "License"); 
	- you may not use this file except in compliance with the License. - You 
	may obtain a copy of the License at - - http://www.apache.org/licenses/LICENSE-2.0 
	- - Unless required by applicable law or agreed to in writing, software - 
	distributed under the License is distributed on an "AS IS" BASIS, - WITHOUT 
	WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. - See the 
	License for the specific language governing permissions and - limitations 
	under the License. -->
<!DOCTYPE mycat:rule SYSTEM "rule.dtd">
<mycat:rule xmlns:mycat="http://io.mycat/">
	<!-- 定义地区分配规则 -->
	<tableRule name="areaRule">
	 <rule>
		<!-- 如果客户端传递了 area_name 的话，就使用以下的规则 -->
		<columns>area_name</columns>
		<algorithm>hash-int</algorithm>
	</rule>
    </tableRule>
	
	<!-- 对应上面 algorithm 的分片规则 -->
	<function name="hash-int" class="io.mycat.route.function.PartitionByFileMap">
		<!-- 指定的枚举文件 -->
		<property name="mapFile">partition-hash-int.txt</property>
		<!-- type：非数值类型=1，数值类型=0 -->
		<property name="type">1</property>
		<!-- 默认使用第2个库存放（当传递的参数在枚举文件里无法匹配的时候，老外喜欢从0开始，0就是第1个，1就是第2个） -->
		<property name="defaultNode">1</property>
	</function>
</mycat:rule>

4、修改 mycat 的 schema.xml 配置文件：

主要修改如图：

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
    <!-- schema name 属性 是 mycat的虚拟库（也叫逻辑库），
	注意这个checkSQLschema属性，设置 false 时，mycat 会帮我们把逻辑库的名字加上，比如：select * from mycatDB.user 这样容易导致无法找到表名。
	如果checkSQLschema属性，设置 true 时，mycat 不会加上逻辑库名，select * from user，这样才正确。
	sqlMaxLimit是每次查询最多返回的行数。
	-->
    <schema name="mycatSchemaDB" checkSQLschema="true" sqlMaxLimit="100" dataNode="dn1">
		<!-- table 表示表名，dataNode 表示对应3个节点，1个集群，rule对应 rule.xml 定义的规则名 -->
		<table name="t_area"  dataNode="dn1,dn2,dn3" rule="areaRule" /> 
	</schema>
    <!-- dataNode 的name就对应mycat虚拟数据库的 dataNode，database 是我们实际MySQL的数据库的库名 -->
    <dataNode name="dn1" dataHost="my_dataHost" database="area_beijing" />
	<dataNode name="dn2" dataHost="my_dataHost" database="area_guangzhou" />
	<dataNode name="dn3" dataHost="my_dataHost" database="area_shenzhen" />
    <!--
    dataNode节点中各属性说明：
		name：指定逻辑数据节点名称；
		dataHost：指定逻辑数据节点物理主机节点名称；
		database：指定物理主机节点上。如果一个节点上有多个库，可使用表达式db$0-99，表示指定0-99这100个数据库；

    dataHost 节点中各属性说明：
		name：物理主机节点名称；
		maxCon：指定物理主机服务最大支持1000个连接；
		minCon：指定物理主机服务最小保持10个连接；
		writeType：指定写入类型；
			0，只在writeHost节点写入；
			1，在所有节点都写入。慎重开启，多节点写入顺序为默认写入根据配置顺序，第一个挂掉切换另一个；
		dbType：指定数据库类型；
		dbDriver：指定数据库驱动；
		balance：指定物理主机服务的负载模式。
			0，不开启读写分离机制；
			1，全部的readHost与stand by writeHost参与select语句的负载均衡，简单的说，当双主双从模式(M1->S1，M2->S2，并且M1与 M2互为主备)，正常情况下，M2,S1,S2都参与select语句的负载均衡；
			2，所有的readHost与writeHost都参与select语句的负载均衡，也就是说，当系统的写操作压力不大的情况下，所有主机都可以承担负载均衡；
	-->
    <dataHost name="my_dataHost" maxCon="1000" minCon="10" balance="3" writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
        <heartbeat>select user()</heartbeat>
        <!-- writeHost节点可以配置多个主从数据库 -->
		<!-- 这里是【主数据库的配置】 -->
        <writeHost host="hostM1" url="127.0.0.1:3306" user="root" password="root">
            <!-- readHost可以配置多个【从数据库】 -->
            <readHost host="hostS2" url="127.0.0.1:3308" user="root" password="root" />
        </writeHost>
    </dataHost>
</mycat:schema>

5、配置完毕，重启 mycat 服务器。然后打开 Navicat 客户端，使用 root 账号（可写的账号）连接到 mycat，用来演示：

发现报错：partition table, insert must provide ColumnList 

 解决办法：罗列出表的所有字段。

因为 area_name = beijing，所以 mycat 会根据 rule 规则，存入到 area_beijing 的数据库的表 t_area 中，我们验证：

同样，深圳地区存入 area_shenzhen，广州的存入广州数据库的表。自行验证。

如果插入地区为【重庆】，根据 rule.xml 的配置，会默认进入到第2个数据库（老外喜欢从0开始，0就是第1个，1就是第2个），即 area_guangzhou

 

OK，mycat 的分片枚举讲解到这。

 

Mycat 求模算法（重点掌握）

求模法分片，根据 id 进行十进制求模运算，运算结果为分区索引。注意：数据库节点分片数量不能更改，否则无法查询到正确的数据，这个和 ES 集群非常相似的。

比如我们现在有 3 个数据库，就是使用上面的例子的 3 个数据库。当数据库表的 id = 1时，1%3=1，放入db=1的数据库中。2%3=2，放入db=2的数据库中。3%3=0，放入db=0的数据库中。

 

 

1、修改 rule.xml 配置文件如下（增加求模的规则）：

<?xml version="1.0" encoding="UTF-8"?>
<!-- - - Licensed under the Apache License, Version 2.0 (the "License"); 
	- you may not use this file except in compliance with the License. - You 
	may obtain a copy of the License at - - http://www.apache.org/licenses/LICENSE-2.0 
	- - Unless required by applicable law or agreed to in writing, software - 
	distributed under the License is distributed on an "AS IS" BASIS, - WITHOUT 
	WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. - See the 
	License for the specific language governing permissions and - limitations 
	under the License. -->
<!DOCTYPE mycat:rule SYSTEM "rule.dtd">
<mycat:rule xmlns:mycat="http://io.mycat/">
	<!-- 定义地区分配规则 -->
	<tableRule name="areaRule">
		<rule>
			<!-- 如果客户端传递了 area_name 的话，就使用以下的规则 -->
			<columns>area_name</columns>
			<algorithm>hash-int</algorithm>
		</rule>
    </tableRule>

	<!-- 定义求模的规则 -->
	<tableRule name="modRule">
		<rule>
			<!-- 如果客户端传递了 id 的话，就使用以下的规则 -->
			<columns>id</columns>
			<algorithm>mod-long</algorithm>
		</rule>
    </tableRule>
	
	<!-- 对应上面 hash-int 的分片规则 -->
	<function name="hash-int" class="io.mycat.route.function.PartitionByFileMap">
		<!-- 指定的枚举文件 -->
		<property name="mapFile">partition-hash-int.txt</property>
		<!-- type：非数值类型=1，数值类型=0 -->
		<property name="type">1</property>
		<!-- 默认使用第2个库存放（当传递的参数在枚举文件里无法匹配的时候，老外喜欢从0开始，0就是第1个，1就是第2个） -->
		<property name="defaultNode">1</property>
	</function>

	<!-- 对应上面 mod-long 的求模规则 -->
	<function name="mod-long" class="io.mycat.route.function.PartitionByMod">
		 <!--指定分片数量，不可以被更改-->
        <property name="count">3</property>
	</function>

</mycat:rule>

2、修改 schema.xml 配置文件：主要是注释掉之前的地区分片枚举，增加求模配置。

		<!-- rule对应 rule.xml 定义的规则名 -->
		<table name="t_area"  dataNode="dn1,dn2,dn3" rule="modRule" /> 

3、重启 mycat 服务器，测试：注意，这时候地区的分片枚举不生效，而是求模生效了。

insert into t_area
(id,area_name)
values
(10,'changsha');

10%3=1，存入第二台数据库，即：area_guangzhou

测试通过。

OK，我们可以查看 mycat 的日志文件。mycat 的日志文件在安装的目录下，找到 logs 文件夹有个 mycat.log 日志文件，打开即可看到日志详情。

 

 

我们还可以让日志打得更详细点，比如打出 SQL 语句。我们打开 conf 目录的 log4j2.xml 文件，修改日志级别成 debug。

然后重启 mycat 服务器。

我们在客户端执行一条查询语句：

select * from t_area
where id = 10;

然后打开 mycat.log 日志文件，往上翻，可以看到 mycat 只发送了一条查询语句，进入到 dn2 数据库：

 

说明：如果带条件（求模算法）时，mycat 只会发送一条查询语句。这就要求【求模算法】时指定的数据库不可更改，否则查询不到数据。

如果是非分片字段，会在所有的数据库都查询，效率有点低。

 

mycat 的分页查询

我们在客户端输入查询条件：

select * from t_area
limit 0,2;

结果（多次查询，会发现结果有变化）：

我们看下 mycat  的日志文件：

mycat 会往 3 台数据库都执行查询语句，但是随机抽取2条结果返回。

如果加上排序条件：order by

select * from t_area
order by id desc
limit 0,2;

结果：mycat 会将3台数据库的结果查询出来，然后按照 id 排序后返回。

OK，mycat 的分片枚举和求模算法就讲解到这。