MySql-高级(分库分表-Mycat) 学习笔记

最新推荐文章于 2022-01-05 10:18:47 发布

yuan_404

最新推荐文章于 2022-01-05 10:18:47 发布

阅读量289

点赞数

分类专栏： MySql 文章标签：数据库 mysql

本文链接：https://blog.csdn.net/weixin_44449838/article/details/107861325

版权

MySql 专栏收录该内容

10 篇文章 0 订阅

订阅专栏

尚硅谷-MySql-高级思维导图：思维导图（mmap+HTML格式）

我下载 Mycat 的地址：Mycat 官网

Mycat 官网的指导书：Mycat 权威指南

1. Mycat 介绍

1. 定义

是数据库中间件
一端连接数据库，一端连接 java 程序

2. 作用

读写分离

因为读的请求一般比写的请求多得多，所以需要将读写分离，可以写操作一台主机，读操作多台主机

数据分片
1. 垂直拆分
2. 水平拆分
3. 垂直+水平拆分
多数据源整合

可以整个多个不同的数据源
一个java 程序只需要配置一个 Mycat，然后再让 Mycat 去配置多个数据源
如果需要重新配置数据源信息，就不需要重启系统了

3. 原理

就是——拦截
1. 它拦截了用户发送过来的 SQL 语句
2. 首先对 SQL 语句做了一些分析——分片分析、路由分析、读写分离分析、缓存分析等
3. 然后再将 SQL 语句发往后端的真实数据库
4. 将结果做适当的处理
5. 最终返回给用户
好处
这种方式把数据库的分布式从代码中解耦出来，程序员察觉不出后台使用的是 Mycat 还是 MySQL

2. Mycat 安装

我下载 Mycat 的地址：Mycat 官网
参考文章：windows下安装mycat

1. 修改 schema.xml 配置文件

先把《schema》《/schema》标签对中的删除，后面自己配
在《schema name=“TESTDB” checkSQLschema=“false” sqlMaxLimit=“100”》中加入 dataNode=“dn1”（默认数据节点）
修改《dataNode name=“dn1” dataHost=“host1” database=“mydb_200806” /》中的 dataHost：数据主机名和 database：数据库名（在主从复制中建立的表 mydb_200806）
删除下面多余的数据节点《dataNode 》
修改数据主机名，与第三步中的 dataHost 值一致
《heartbeat》表示：用一个很简单的查询语句来确定 MySQL 服务正常
《writeHost》《/writeHost》为配置主从复制中的主机，修改 url 为主机的 MySQL 地址
修改《readHost》标签中的 host ， url（配置为读主机的地址），password （MySQL密码）
删除多余的《writeHost》、《dataHost》

2. 修改 server.xml 配置文件

修改 MySQL 的用户名以及密码

3. Mycat 配置及启动

1. conf 下配置文件备注

schema.xml
定义逻辑库，表、分片节点等内容
rule.xml
定义分片规则
server.xml
定义用户以及系统相关变量，如端口等

2. 验证数据库访问情况

mysql -uroot -p123123 -h 192.168.154.1 -P 3306
mysql -uroot -p123123 -h 192.168.154.154 -P 3306
 
 
如本机远程访问报错，请建对应用户
grant all privileges on *.* to root@'缺少的host'  identified by '123456';

3. 启动

控制台启动：去mycat/bin 目录下 startup_nowrap.bat
后台启动：去mycat/bin 目录下 mycat.bat

4. 登录

Navicat 中端口号填 8066 即可

4. 读写分离

前提：已经搭建好主从复制

1. 搭建读写分离

balance（负载均衡）介绍

类型，目前的取值有4 种：
1. balance=“0”, 不开启读写分离机制，所有读操作都发送到当前可用的 writeHost 上。
2. balance=“1”，全部的 readHost 与 stand by writeHost 参与 select 语句的负载均衡，简单的说，当双主双从模式(M1->S1，M2->S2，并且 M1 与 M2 互为主备)，正常情况下，M2,S1,S2 都参与 select 语句的负载均衡。（也就是，M1 只要在，就只允许 M1 进行写操作，其他只能进行读操作；只有当 M1 挂掉，M2 才能进行写操作）
3. balance=“2”，所有读操作都随机的在 writeHost、readhost 上分发。
4. balance=“3”，所有读请求随机的分发到 readhost 执行，writerHost 不负担读压力（真正做到读写分离）

修改 schema.xml 文件
1. 修改《dataHost》标签中的 balance=“0” 的值

2. 标识主机

建立一个用于标识主机名的表

create table t_replica 
(    id  int auto_increment , 
     name varchar(200) 
);

分别在两个库的 t_replica 表下插入：

insert into t_replica(name) values (@@hostname）

查询当前数据是从那一台主机读取的

select * from t_replica

5. 分库

1. 什么时候分库

数据库内的数据库已经快要达到瓶颈 —— 5000 万条数据，考虑分库

2. 案例分析- 建表（下面分表用到的也是这些表）

#客户表  rows:20万 
CREATE TABLE customer(
    id INT AUTO_INCREMENT,
    NAME VARCHAR(200),
    PRIMARY KEY(id)
);
 
 
#订单表   rows:600万
CREATE TABLE orders(
    id INT AUTO_INCREMENT,
    order_type INT,
    customer_id INT,
    amount DECIMAL(10,2),
    PRIMARY KEY(id)  
); 
 
 
#订单详细表     rows:600万
CREATE TABLE orders_detail(
    id INT AUTO_INCREMENT,
    detail VARCHAR(2000),
    order_id INT,
    PRIMARY KEY(id)
);
 
 
#订单状态字典表   rows:20
CREATE TABLE dict_order_type(
    id INT AUTO_INCREMENT,
    order_type VARCHAR(200),
    PRIMARY KEY(id)
);

3. 原则

可能会出现关联（join等）的表需要放在一个库中（这里客户表与其他表没有明显关联，可以分出去）
需要重新搭建两个什么都没有的库

4. 修改 schema.xml 文件

在《schema》《/schema》标签对中添加一条：

# name 为需要分出去的表名，dataNode 为新的节点名，表示只有这一个表使用 dn2 节点，其他的还是用dn1（默认的节点）
<table name="customer" dataNode="dn2" ></table>

在《dataNode 》标签下再添加一行：

# 这里的 name 就是上面对于的节点名，dataHost 主机需要重新换一台，database数据库也需要换成新的主机中的表名
 <dataNode name="dn2" dataHost="host2" database="atguigu_sm" />

在《dataHost》标签后再添加一个主机的信息

<dataHost name="host2" maxCon="1000" minCon="10" balance="0"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <writeHost host="hostm2" url="192.168.67.1:3306" user="root"
                                   password="123123">
                </writeHost>
        </dataHost>

5. 在以上的两个主机中分别新建对应的数据库

6. 启动 mycat

7. 建立数据表

6. 分表

1. 什么时候要分表

当单张表的数据量达到瓶颈—— 500 万条数据
订单表和订单详细表已经达到瓶颈

2. 原则

需要按照相对平均的划分条件来分——就订单表而言，可以根据 customer_id 分比较合理，每个用户买的商品数相对平均
与其他已经被分的表的关联字段
可以按照哈希表的原理，对作为划分条件的字段进行取模运算，随机分配

3. 为单独的一张表分表（orders表）

1. 修改 schema.xml 文件

在《schema 》《/schema 》标签对中添加一条：

# name 进行操作的表名，dataNode 数据节点，因为此表在两个节点中都有数据所以都要写，rule 分表的规则的名字
<table name="orders" dataNode="dn1,dn2"  rule="mod_rule" ></table>

2. 修改 rule.xml 文件

新增一个《tableRule》《/tableRule》标签对：

# name 规则名
<tableRule name="mod_rule">
	<rule>
		# columns ，是作为划分条件的列名，要和表中的名字一样
		<columns>customer_id</columns>
		# 分表的算法——对用户id取模
		<algorithm>mod-long</algorithm>
	</rule>
</tableRule>

修改 name = mod-long（上面分表的算法）的《function》标签中的《property》中的值

# 这表示有几个节点，因为示例中只有两个节点，所以这里写 2
<property name="count">2</property>

3. 建表

在新的主机中新建表

4. 插入数据

在插入数据时，一定要标明所有的字段名，这样 Mycat 才能知道哪一项数据是分表的条件字段

4. 为与被分过的表有关联的表分表（orders_detail）

1.前言

在订单表已经成功分表的基础上，再分订单详情表
因为订单表与订单详情表关联紧密——详情表中有一个字段 order_id 对应于订单表中的id ，所以这里可以再按照 order_id 进行划分

2. ER表

作用
为了相关联的表的行尽量分在一个库下
修改 schema.xml 文件进行配置
- 需要在 name 为订单表（orders）的《schema》 => 《table》标签中添加一行：

# 配置子表 name 为关联表的表名，primaryKey 为主键，joinKey 外键，parentKey 为子表对应于父表的字段名
 <childTable name="orders_detail" primaryKey="id" joinKey="order_id" parentKey="id" />

在新的主键上建表
插入数据

5. 全局表（dict_order_type）

1. 定义

设定为全局的表，会直接复制给每个数据库一份，所有写操作也会同步给多个库。
所以全局表一般不能是大数据表或者更新频繁的表
一般是字典表或者系统表为宜。
一般全局表都不是很大

2. 修改 schema.xml 文件

在《schema 》标签中再添加一条：

# name 为表名，dataNode 因为两个数据节点中都有该表，所以要写两个，type 类型设置为全局
<table name="dict_order_type" dataNode="dn1,dn2" type="global" ></table>

7. 全局序列

1. 前言

因为对数据库进行了分库分表，所以主键等的就不能使用自增序列了，会产生重复，所以就需要有一个全局序列
以下有三种全局序列

2. 本地文件方式（不推荐）

原理：在本地建立一个文件，并配置初始值，只要有插入的 SQL 语句被拦截，就自增
缺点：抗风险能力太差（如果主机挂掉）
不推荐

3. 时间戳方式（不推荐）

原理：只要有插入的 SQL 语句被拦截，就把当前时间戳作为序列值
优点：不用配置复杂的配置项，只要获取当前事件即可
缺点：时间戳有 18 位，太长了

4. 数据库方式（推荐）

1. 原理

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

2. 创建序列脚本

先创建一张表创建

CREATE TABLE MYCAT_SEQUENCE (NAME VARCHAR(50) NOT NULL,current_value INT NOT NULL,increment INT NOT NULL DEFAULT 100, PRIMARY KEY(NAME)) ENGINE=INNODB;

建立序列脚本

函数一

# 官方提供的脚本，尽量不要改参数

DELIMITER $$
 
CREATE FUNCTION mycat_seq_currval(seq_name VARCHAR(50)) RETURNS VARCHAR(64)
DETERMINISTIC  
BEGIN
DECLARE retval VARCHAR(64);
SET retval="-999999999,null";
SELECT CONCAT(CAST(current_value AS CHAR),",",CAST(increment AS CHAR)) INTO retval FROM
MYCAT_SEQUENCE WHERE NAME = seq_name;
RETURN retval;
END $$
DELIMITER;

函数二

DELIMITER $$
CREATE FUNCTION mycat_seq_setval(seq_name VARCHAR(50),VALUE INTEGER) RETURNS VARCHAR(64)
DETERMINISTIC
BEGIN
UPDATE MYCAT_SEQUENCE
SET current_value = VALUE
WHERE NAME = seq_name;
RETURN mycat_seq_currval(seq_name);
END $$
DELIMITER ;

函数三

DELIMITER $$
CREATE FUNCTION mycat_seq_nextval(seq_name VARCHAR(50)) RETURNS VARCHAR(64) 
DETERMINISTIC
BEGIN
UPDATE MYCAT_SEQUENCE
SET current_value = current_value + increment WHERE NAME = seq_name;
RETURN mycat_seq_currval(seq_name);
END $$
DELIMITER;

脚本执行完毕后，在数据库下的函数文件夹，多出了三个名为 mycat_seq_currval 、mycat_seq_setval、mycat_seq_nextval的函数，即运行成功

查询序列表

SELECT * FROM MYCAT_SEQUENCE

删除序列表

TRUNCATE TABLE MYCAT_SEQUENCE

补充（删除操作：TRUNCATE 和 DELETE 区别）：
- TRUNCATE 不能回滚；不会在日志中记录文件，不占磁盘空间
- DELETE 可以回滚；会把删除的信息记录在日志中，会占磁盘空间
- 综上，如果用 DELETE 删除大量数据，会报错：“磁盘空间不足”，因为有大量的日志信息；如果用 TRUNCATE 删除大量数据，不会空间不足，但是不能回滚

初始化序列表

# 字段含义：NAME 全集序列名、current_value 起始编号、increment 概念中说的每个号段的长度
INSERT INTO MYCAT_SEQUENCE(NAME,current_value,increment) VALUES ('ORDERS', 400000,
100);

3. 修改mycat配置

修改 sequence_db_conf.properties 配置文件

在这里插入图片描述
意思是 ORDERS这个序列在dn1这个节点上，具体dn1节点是哪台机子，请参考schema.xml
即 ORDERS 后的数据库名要和之前配置的一样

修改 server.xml 配置文件
修改 name = sequnceHandlerType 的《property》标签中的值，修改为 1
（补充：这里的值 0 -> 本地文件方式；1 -> 数据库方式；2 -> 时间戳方式）
重启 Mycat
最后就可以插入数据了

5. 自主定义

根据业务逻辑组合，也就是在传给 Mycat 之前就生成编号
可以利用 redis的单线程原子性 incr来生成序列

yuan_404

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
打赏
0
评论
MySql-高级(分库分表-Mycat) 学习笔记

尚硅谷-MySql-高级思维导图：思维导图（mmap+HTML格式）我下载 Mycat 的地址：Mycat 官网Mycat 官网的指导书：Mycat 权威指南1. Mycat 介绍1. 定义是数据库中间件一端连接数据库，一端连接 java 程序2. 作用读写分离因为读的请求一般比写的请求多得多，所以需要将读写分离，可以写操作一台主机，读操作多台主机数据分片垂直拆分水平拆分垂直+水平拆分多数据源整合可以整个多个不同的数据源一个java
复制链接

扫一扫