MySQL

notes

Publish Date: 2023-08-29

Word Count: 4.2k

Read Count:

分库分表和读写分离（八）

分库分表

分库分表的重心思想都是将数据分散存储，使得单一数据库/表的数据量变小来缓解单一数据库的性能问题，从而达到提升数据库性能的目的。

拆分策略

垂直拆分
- 垂直分库：以表为依据，根据业务将不同表拆分到不同库中
  1. 每个库的表结构都不一样
  2. 每个库的数据也不一样
  3. 所有库的并集是全量数据
- 垂直分表：以字段为依据，根据字段属性将不同字段拆分到不同表中
  1. 每个表的结构都不一样
  2. 每个表的数据也不一样，一般通过一列（主键/外键）关联
  3. 所有表的并集是全量数据
水平拆分
- 水平分库：以字段为依据，按照一定策略，将一个库的数据拆分到多个库中
  1. 每个库的表结构都一样
  2. 每个库的数据都不一样
  3. 所有库的并集是全量数据
- 水平分表：以字段为依据，按照一定策略，将一个表的数据拆分到多个表中
  1. 每个表的表结构都一样
  2. 每个表的数据都不一样
  3. 所有表的并集是全量数据

实现技术：

shardingJDBC：基于AOP原理，在应用程序中对本地执行sql进行拦截，解析，改写，路由处理。需自行编码配置实现，只支持java语言，性能较高
MyCat：数据库分库分表中间件，不用调整代码即可实现分库分表，支持多种语言，性能不及前者

Mycat

Mycat是开源的，活跃的，基于java语言编写的mysql数据库中间件，可以像使用mysql一样来使用mycat，对于开发人员来说根本感觉不到mycat的存在。

mycat目录结构：

bin：存放可执行文件，用于启动停止mycat
conf：存放mycat的配置文件
lib：存放mycat的项目依赖包（jar）
logs：存放mycat的日志文件

入门

由于tb_order表中数据量很大，磁盘IO及容量都到达极限，需对tb_order进行数据分片，分为三个数据节点，每个节点主机位于不同的服务器上。

分片配置(schema.xml):

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

	<schema name="DB01" checkSQLschema="true" sqlMaxLimit="100">
		<table name="TB_ORDER" dataNode="dn1,dn2,dn3" rule="auto-sharding-long" />
	</schema>

	<dataNode name="dn1" dataHost="dhost1" database="db01" />
	<dataNode name="dn2" dataHost="dhost2" database="db01" />
	<dataNode name="dn3" dataHost="dhost3" database="db01" />

	<dataHost name="dhost1" maxCon="1000" minCon="10" balance="0"
			  writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"  slaveThreshold="100">
		<heartbeat>select user()</heartbeat>

		<writeHost host="master" url="jdbc:mysql://192.168.19.131:3306?useSSL=false&serverTimezone=Asia/Shanghai&characterEncoding=utf8" user="root" password="1234" />
	</dataHost>

	<dataHost name="dhost2" maxCon="1000" minCon="10" balance="0"
			  writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"  slaveThreshold="100">
		<heartbeat>select user()</heartbeat>

		<writeHost host="master" url="jdbc:mysql://192.168.19.132:3306?useSSL=false&serverTimezone=Asia/Shanghai&characterEncoding=utf8" user="root" password="1234" />
	</dataHost>

	<dataHost name="dhost3" maxCon="1000" minCon="10" balance="0"
			  writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"  slaveThreshold="100">
		<heartbeat>select user()</heartbeat>

		<writeHost host="master" url="jdbc:mysql://192.168.19.133:3306?useSSL=false&serverTimezone=Asia/Shanghai&characterEncoding=utf8" user="root" password="1234" />
	</dataHost>

</mycat:schema>

配置mycat用户及用户的权限信息(server.xml)：

<user name="root" defaultAccount="true">
	<property name="password">123456</property>
	<property name="schemas">DB01</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>

<user name="user">
	<property name="password">123456</property>
	<property name="schemas">DB01</property>
	<property name="readOnly">true</property>
</user>

启动服务：

切换到mysql安装目录（/usr/local/mycat），执行以下命令

启动
bin/mycat start

停止
bin/mycat stop

mycat启动后，占用端口号8066。

连接并登陆mycat

1	mysql -h 192.168.19.131 -P 8066 -uroot -p1234

配置

schema.xml

schema.xml作为mycat中最重要的配置方式之一，覆盖了mycat的逻辑库，逻辑表，分片规则，分片节点及数据源的配置。

主要包含以下三级标签：

schema标签：用于定义mycat实例中的逻辑库，一个mycat实例中，可有多个逻辑库，可通过schema标签来划分不同的逻辑库。mycat中的逻辑库的概念，等同于mysql中database概念，需操作某个逻辑库下的表时，也需切换逻辑库（use xxx），核心属性如下：
- name：指定自定义的逻辑库名
- checkSQLschema：在sql语句操作时指定数据库名称，执行时是否自动去除；true，自动去除，false，不自动去除
- sqlMaxLimit：若未指定limit进行查询，列表查询模式查询多少条记录
- schema标签（table）：table标签定义mycat中逻辑库schema下的逻辑表，所有需要拆分的表都需在table标签中定义，核心属性如下：
  - name：定义逻辑库表名，在该逻辑表下唯一
  - dataNode：定义逻辑表所属的dataNode，该属性要与dataNode标签中name对应，多个dataNode用逗号分隔
  - rule：分片规则的名字，分片规则名字是在rule.xml中定义的
  - primaryKey：逻辑表对应真实表的主键
  - type：逻辑表的类型，目前逻辑表只有全局表和普通表，若未配置，就是普通表；全局表，配置为global

1
2
3

<schema name="DB01" checkSQLschema="true" sqlMaxLimit="100">
	<table name="TB_ORDER" dataNode="dn1,dn2,dn3" rule="auto-sharding-long" />
</schema>

datanode标签：定义mycat中的数据节点，也就是我们通常说的数据分片，一个dataNode标签就是一个独立的数据分片
- name：定义数据节点名称
- dataHost：数据库实例主机名称，引用自dataHost标签中的name属性
- database：定义分片所需的数据库

1
2
3

<dataNode name="dn1" dataHost="dhost1" database="db01" />
<dataNode name="dn2" dataHost="dhost2" database="db01" />
<dataNode name="dn3" dataHost="dhost3" database="db01" />

datahost标签：在mycat逻辑库中作为底层标签存在，直接定义具体的数据库实例，读写分离，心跳语句
- name：唯一标识，供上层标签使用
- maxCon/minCon：最大/小连接数
- balance：负载均衡策略，取值0，1,2,3
- writeType：写操作分发方式（0：写操作转发到第一个writeHost，第一个挂了，切换到第二个；1：写操作随机分发到配置writehost）
- dbDriver：数据库驱动，支持native，jdbc

<dataHost name="dhost1" maxCon="1000" minCon="10" balance="0"
		  writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"  slaveThreshold="100">
	<heartbeat>select user()</heartbeat>

	<writeHost host="master" url="jdbc:mysql://192.168.19.131:3306?useSSL=false&serverTimezone=Asia/Shanghai&characterEncoding=utf8" user="root" password="1234" />
</dataHost>

<dataHost name="dhost2" maxCon="1000" minCon="10" balance="0"
		  writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"  slaveThreshold="100">
	<heartbeat>select user()</heartbeat>

	<writeHost host="master" url="jdbc:mysql://192.168.19.132:3306?useSSL=false&serverTimezone=Asia/Shanghai&characterEncoding=utf8" user="root" password="1234" />
</dataHost>

<dataHost name="dhost3" maxCon="1000" minCon="10" balance="0"
		  writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"  slaveThreshold="100">
	<heartbeat>select user()</heartbeat>

	<writeHost host="master" url="jdbc:mysql://192.168.19.133:3306?useSSL=false&serverTimezone=Asia/Shanghai&characterEncoding=utf8" user="root" password="1234" />
</dataHost>

rule.xml

rule.xml中定义所有拆分表的规则，在使用过程中可灵活的使用分片算法，或对同一个分片算法使用不同的参数，它让分片过程可配置化，主要包含两个标签：tableRule，Function。

server.xml

server.xml配置文件中包含mycat的系统配置信息，主要要两个重要标签：system，user。

user标签：

<user name="root" defaultAccount="true">   <!-- 用户名-->
		<property name="password">123456</property> <!-- 密码 -->
		<property name="schemas">DB01</property> <!-- 该用户可访问的逻辑库，多个逻辑库之间逗号分隔 -->
		
		<!-- 表级 DML 权限设置 -->
		<!-- 		
		<privileges check="false"> <!-- 是否开DML权限检查，默认为false -->
			<schema name="TESTDB" dml="0110" > <!-- 配置指定逻辑的权限 -->
				<table name="tb01" dml="0000"></table> <!-- 配置指定逻辑表的权限，就近原则 -->
				<table name="tb02" dml="1111"></table> <!-- 四个数字分队对应增，改，查，删 -->
			</schema>
		</privileges>		
		 -->
	</user>

	<user name="user">
		<property name="password">123456</property>
		<property name="schemas">DB01</property>
		<property name="readOnly">true</property> <!-- 是否只读，默认为false -->
	</user>

垂直分库

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

	<schema name="SHOPPING" checkSQLschema="true" sqlMaxLimit="100">
		<table name="tb_goods_base" dataNode="dn1" primaryKey="id" />
		<table name="tb_goods_brand" dataNode="dn1" primaryKey="id" />
		<table name="tb_goods_cat" dataNode="dn1" primaryKey="id" />
		<table name="tb_goods_desc" dataNode="dn1" primaryKey="goods_id" />
		<table name="tb_goods_item" dataNode="dn1" primaryKey="id" />

		<table name="tb_order_item" dataNode="dn2" primaryKey="id" />
		<table name="tb_order_master" dataNode="dn2" primaryKey="order_id" />
		<table name="tb_order_pay_log" dataNode="dn2" primaryKey="out_trade_no" />

		<table name="tb_user" dataNode="dn3" primaryKey="id" />
		<table name="tb_user_address" dataNode="dn3" primaryKey="id" />

		<table name="tb_areas_provinces" dataNode="dn1,dn2,dn3" primaryKey="id" type="global" />
		<table name="tb_areas_city" dataNode="dn1,dn2,dn3" primaryKey="id" type="global" />
		<table name="tb_areas_region" dataNode="dn1,dn2,dn3" primaryKey="id" type="global" />
	</schema>

	<dataNode name="dn1" dataHost="dhost1" database="shopping" />
	<dataNode name="dn2" dataHost="dhost2" database="shopping" />
	<dataNode name="dn3" dataHost="dhost3" database="shopping" />

	<dataHost name="dhost1" maxCon="1000" minCon="10" balance="0"
			  writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"  slaveThreshold="100">
		<heartbeat>select user()</heartbeat>

		<writeHost host="master" url="jdbc:mysql://192.168.19.131:3306?useSSL=false&serverTimezone=Asia/Shanghai&characterEncoding=utf8" user="root" password="1234" />
	</dataHost>

	<dataHost name="dhost2" maxCon="1000" minCon="10" balance="0"
			  writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"  slaveThreshold="100">
		<heartbeat>select user()</heartbeat>

		<writeHost host="master" url="jdbc:mysql://192.168.19.132:3306?useSSL=false&serverTimezone=Asia/Shanghai&characterEncoding=utf8" user="root" password="1234" />
	</dataHost>

	<dataHost name="dhost3" maxCon="1000" minCon="10" balance="0"
			  writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"  slaveThreshold="100">
		<heartbeat>select user()</heartbeat>

		<writeHost host="master" url="jdbc:mysql://192.168.19.133:3306?useSSL=false&serverTimezone=Asia/Shanghai&characterEncoding=utf8" user="root" password="1234" />
	</dataHost>

</mycat:schema>

水平分表

<schema name="ITCAST" checkSQLschema="true" sqlMaxLimit="100">
<table name="tb_log" dataNode="dn4,dn5,dn6" primaryKey="id" rule="mod-long" />
</schema>
<dataNode name="dn4" dataHost="dhost1" database="itcast" />
<dataNode name="dn5" dataHost="dhost2" database="itcast" />
<dataNode name="dn6" dataHost="dhost3" database="itcast" />

分片规则

范围分片：根据指定的字段及其配置的范围与数据节点的对应情况，来决定该数据属于哪一个分片
取模分片：根据指定的字段值与节点数量进行求摸运算，根据运算结果决定该数据属于哪一个分片
一致性hash：相同的哈希因子计算值总是被划分到相同的分区表中，不会因分区节点的增加而改变原来数据的分区位置-
枚举分片：通过在配置文件中配置可能的枚举值，指定数据分布到不同数据节点上，本规则适用于按照省份，性别，状态拆分数据等业务
应用指定：运行阶段由应用自主决定路由到哪个分片，直接根据字符子串（必须是数字）计算分片号。
固定分片hash算法：该算法类似于十进制的求摸运算，但是为二进制的操作，例如：取id的二进制低10位与11111111进行位&运算
1. 若是求摸，连续的值，分别分配到各个不同的分片；但此算法会将连续的值可能分配到相同的分片，降低事务处理的难度
2. 可均匀分配，也可非均匀分配
3. 分片字段必须为数字类型
字符串hash解析：截取字符串中的指定位置的子字符串，进行hash算法，算出分片
按（天）日期分片
使用场景为按照月份来分片，每个自然月为一个分片

管理

mycat默认开通两个端口，可在server.xml中进行修改：

8066数据访问端口，即进行DML和DDL操作
9066数据库管理端口，即mycat服务管理控制功能，用于管理mycat的整个集群状态

mysql -h ip地址 -p 9066 -u用户 -p密码

show @@help
- 查看mycat管理工具帮助文档
show @@version
- 查看mycat版本
reload @@config
- 重新加载mycat的配置文件
show @@datasource
- 查看mycat的数据源信息
show @@datanode
- 查看mycat现有的分片节点信息
show @@threadpool
- 查看mycat的线程池信息
show @@sql
- 查看执行的sql
show @@sql.sum
- 查看执行的sql统计

读写分离

读写分离就是把读写操作分开，以对应不同的数据库服务器。主数据库提供写操作，从数据库提供读操作，可有效减轻单台数据库的但压力。

通过mycat即可轻易实现上述功能，不仅可以支持mysql，也可支持Oracle和SQL Server。

一主一从：mysql主从复制是基于二进制日志binlog实现的，master，slave

一主一从读写分离：通过writehost，readhost，balance实现

双主双从：两台主库，相互复制，互为主备，增强mysql的可用性

双主双从读写分离：通过writehost，readhost，balance，writetype，switchtype实现。

ljs

http://example.com/2023/08/29/mysql8/

All articles in this blog are used except for special statements CC BY 4.0 reprint polocy. If reproduced, please indicate source ljs !

MySQL

Current

分库分表和读写分离（八）

分库分表和读写分离

2023-08-29 notes

MySQL

日志和主从复制（七）

日志和主从复制

2023-08-22 notes

MySQL

分库分表和读写分离（八）

分库分表和读写分离（八）

分库分表

拆分策略

Mycat

入门

配置

schema.xml

rule.xml

server.xml

垂直分库

水平分表

分片规则

管理

读写分离

你的赏识是我前进的动力