Mysql账户拆分 mysql怎么拆分表

转载

桃太郎 2023-06-24 16:51:38

文章标签 Mysql账户拆分分布式数据数据库字段 文章分类 MySQL 数据库

什么是分库分表

简而言之就是数据拆分：将一个表结构分为多个表，或者将一个表数据分片后放入多个表，这些表可以放在同一个数据库里，也可以放到不同的数据库中
数据拆分方式：垂直拆分（分库）、水平拆分（分表）、垂直 + 水平拆分（分库分表）

分库

一个数据库由很多表的构成，每个表对应着不同的业务，垂直切分是指按照业务将表进行分类，分布到不同的数据库上面，这样也就将数据或者说压力分担到不同的库上面
分库的原则：有紧密关联关系的表应该在一个库里，相互没有关联关系的表可以分到不同的库里

步骤

分库不能在老数据库上拆分，而是创建多个空数据库（名称一样），然后在mycat上配置分库的原则，最后在mycat里面执行建表语句
这里是要把customer表分到另一个库

首先，由于之前配置了主从复制，这里只演示分库，所以不需要了，所以相关的机器要执行一下stop slave和reset master命令
然后，在两台机器上各建一个orders库
然后，修改mycat配置文件
重启mycat
最后，在mycat上执行建表语句

ok之后在两台机器上show tables，就可以看到customer表和其他3个表分开了

建表语句

CREATE TABLE customer(
id INT AUTO_INCREMENT,
NAME VARCHAR(200),
PRIMARY KEY(id)
);

CREATE TABLE orders(
id INT AUTO_INCREMENT,
order_type INT,
customer_id INT,
amount DECIMAL(10,2),
PRIMARY KEY(id)
);

CREATE TABLE orders_detail(
id INT AUTO_INCREMENT,
detail VARCHAR(2000),
order_id INT,
PRIMARY KEY(id)
);

CREATE TABLE dict_order_type(
id INT AUTO_INCREMENT,
order_type VARCHAR(200),
PRIMARY KEY(id)
);

mycat配置

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

        <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
			<table name="customer" dataNode="dn2"></table>
			<!--<table name="orders" dataNode="dn1,dn2" rule="mod_rule"></table> -->
        </schema>
        <dataNode name="dn1" dataHost="host1" database="orders" />
		<dataNode name="dn2" dataHost="host2" database="orders" />
        <dataHost name="host1" 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.101.2:3306" user="root" password="root">
			</writeHost>
        </dataHost>
		<dataHost name="host2" 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.101.4:3306" user="root" password="root">
			</writeHost>
        </dataHost>
</mycat:schema>

分表

MySQL 单表存储数据条数是有瓶颈的，单表达到 1000 万条数据就达到了瓶颈，会影响查询效率，需要进行水平拆分（分表）进行优化。
水平拆分是按照某个字段的某种规则来将同一个表的数据分散到多个表之中，每个表中包含一部分数据。这些表可以分布到不同的数据库（名称一样）、不同的主机，但表名是一样的
这里我们演示将orders表分片

步骤

修改配置文件，见后文
在数据节点 dn2 上建 orders 表
重启mycat
在mycat里新增数据（INSERT 字段不能省略）：

INSERT INTO orders(id,order_type,customer_id,amount) VALUES (1,101,100,100100);
INSERT INTO orders(id,order_type,customer_id,amount) VALUES(2,101,100,100300);
INSERT INTO orders(id,order_type,customer_id,amount) VALUES(3,101,101,120000);
INSERT INTO orders(id,order_type,customer_id,amount) VALUES(4,101,101,103000);
INSERT INTO orders(id,order_type,customer_id,amount) VALUES(5,102,101,100400);
INSERT INTO orders(id,order_type,customer_id,amount) VALUES(6,102,100,100020);

select一下就可以看到已经分片了！

schema.xml完整配置

只添加了<table name="orders" dataNode="dn1,dn2" rule="mod_rule"></table>
为 orders 表设置数据节点为 dn1、 dn2，并指定分片规则为 mod_rule（自定义的名字）

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

        <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
			<table name="customer" dataNode="dn2"></table>
			<table name="orders" dataNode="dn1,dn2" rule="mod_rule"></table>
        </schema>
        <dataNode name="dn1" dataHost="host1" database="orders" />
		<dataNode name="dn2" dataHost="host2" database="orders" />
        <dataHost name="host1" 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.101.2:3306" user="root" password="root">
			</writeHost>
        </dataHost>
		<dataHost name="host2" 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.101.4:3306" user="root" password="root">
			</writeHost>
        </dataHost>
</mycat:schema>

rule.xml配置

在 rule 配置文件里新增分片规则 mod_rule，并指定规则适用字段为 customer_id，
还有选择分片算法 mod-long（对字段求模运算）， customer_id对两个节点求模，根据结果分片
配置算法 mod-long 参数 count 为 2，两个节点

<tableRule name="mod_rule">
    <rule>
        <columns>customer_id</columns>
        <algorithm>mod-long</algorithm>
    </rule>
</tableRule>
······
<function name="mod-long" class="io.mycat.route.function.PartitionByMod">
    <!-- how many data nodes -->
    <property name="count">2</property>
</function>

注：后面都没实际操作了，仅提供思路和步骤

Mycat的分片join

假如一个表分片了，和它关联的一个表没分片，join查询的时候就会报错，怎么解决这个问题？
Mycat 借鉴了 NewSQL 领域的新秀 Foundation DB 的设计思路， Foundation DB 创新性的提出了 Table Group 的概念，其将子表的存储位置依赖于主表，并且物理上紧邻存放，因此彻底解决了 JION 的效率和性能问题，根据这一思路，提出了基于 E-R 关系的数据分片策略，子表的记录与所关联的父表记录存放在同一个数据分片上。
步骤：

添加配置
在dn2 创建 orders_detail 表
重启 Mycat
访问 Mycat 向 orders_detail 表插入数据
在mycat、 dn1、 dn2中运行两个表join语句查看效果

配置

primaryKey是主键，parentKey是父表的主键，joinKey类似于外键

<table name="orders" dataNode="dn1,dn2" rule="mod_rule" >
	<childTable name="orders_detail" primaryKey="id" joinKey="order_id" parentKey="id" />
</table>

Mycat全局表

业务中存在某一类表，如字典表，其特点是：

数据变动不频繁
数据规模不大，很少有超过数十万条记录

这类表可以作为全局表，Mycat全局表具有以下特性：

全局表的插入、更新操作会实时在所有节点上执行，保持各个分片的数据一致性
全局表的查询操作，只从一个节点获取
全局表可以跟任何一个表进行 JOIN 操作

步骤

添加配置：<table name="dict_order_type" dataNode="dn1,dn2" type="global" ></table>
在dn2 创建 dict_order_type 表
重启 Mycat
访问 Mycat 向 dict_order_type 表插入数据
在Mycat、 dn1、 dn2中select查看效果

Mycat常用分片规则

都是些配置，记住概念就行了

取模

前文分表的方式就是取模

枚举分片

通过在配置文件中配置枚举id，比如某个字段=1就分到dn1、=2就分到dn2
适用场景举例：按照省份或区县来做分片、按照人员类型分片

范围分片

按照某个字段的取值范围分片
如：年龄区间、收入区间，得分区间
同上要配置几个文件

按日期（天）分片

按照设定的时间格式、范围分片
核心配置如下：

dateFormat ：日期格式
sBeginDate ：开始日期
sEndDate：结束日期，数据到了这个日期的分片后循环从开始分片插入
sPartionDay ：分区天数，默认从开始日期算起，隔 2 天一个分区

<function name="shardingByDate" class="io.mycat.route.function.PartitionByDate">
    <property name="dateFormat">yyyy-MM-dd</property>
    <property name="sBeginDate">2019-01-01</property>
    <property name="sEndDate">2019-01-04</property>
    <property name="sPartionDay">2</property>
</function>

Mycat全局序列

在实现分库分表的情况下，数据库自增主键已无法保证自增主键的全局唯一。
Mycat 提供了全局 sequence，并且提供了包含本地配置和数据库配置等多种实现方式（本地配置存在mycat宕机风险）

数据库方式

利用数据库一个表来进行计数累加。但是并不是每次生成序列都读写数据库，这样效率太低。Mycat 会预加载一部分号段到 Mycat 的内存中，这样大部分读写序列都是在内存中完成的。如果内存中的号段用完了 Mycat 会再向数据库要一次。
如果 Mycat 崩溃了，那么 Mycat 启动后会向数据库申请新的号段，原有号段会弃用，不会因此出现主键重复
配置略

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。

上一篇：Android 撤销绘画自定义View 安卓自定义view的绘制流程

下一篇：java下划线_有什么用 java将下划线转为驼峰

提问和评论都可以，用心的回复会被更多人看到评论

发布评论

相关文章

官方博客	全部文章	热门标签	班级博客
了解我们	网站地图	意见反馈

鸿蒙开发者社区	51CTO学堂
51CTO	软考资讯

Mysql账户拆分 mysql怎么拆分表

Mysql账户拆分 mysql怎么拆分表

什么是分库分表

分库

步骤

建表语句

mycat配置

分表

步骤

schema.xml完整配置

rule.xml配置

注：后面都没实际操作了，仅提供思路和步骤

Mycat的分片join

配置

Mycat全局表

步骤

Mycat常用分片规则

取模

枚举分片

范围分片

按日期（天）分片

Mycat全局序列

数据库方式

51CTO博客