共计 34110 个字符,预计需要花费 86 分钟才能阅读完成。
Mycat 数据库中间件
1、数据库中间件:
是一类连贯软件组件和利用的计算机软件,以便于软件各部件之间的沟通。例子:Tomcat,web 中间件。数据库中间件:连贯 java 应用程序和数据库
2、为什么要用 Mycat?
- Java 与数据库紧耦合
- 高访问量高并发对数据库的压力
- 读写申请数据不统一
mysql 单表瓶颈 1000w 数据, 单库瓶颈 5000w 数据
数据库中间件比照:
Mycat 的官网: http://www.mycat.io/
Mycat 能干什么:
- 读写拆散
- 数据分片 垂直拆分(分库)、程度拆分(分表)、垂直 + 程度拆分(分库分表)
- 多数据源整合
原理:
Mycat 的原理中最重要的一个动词是“拦挡”,它拦挡了用户发送过去的 SQL 语句,首先对 SQL 语句做了一些特定的剖析:如分片剖析、路由剖析、读写拆散剖析、缓存剖析等,而后将此 SQL 发 往后端的实在数据库,并将返回的后果做适当的解决,最终再返回给用户
装置启动
1、解压后即可应用 解压缩文件拷贝到 linux 下 /usr/local/ 2、三个配置文件
- schema.xml:定义逻辑库,表、分片节点等内容
- rule.xml:定义分片规定
- server.xml:定义用户以及零碎相干变量,如端口等
3、批改配置文件 server.xml 批改用户信息,与 MySQL 辨别
<!– 用户名 –>
<user name=”root”>
<property name=”password”>123456</property> <!– 明码 –>
<property name=”schemas”>TESTDB</property> <!– 治理的库 –>
</user>
schemas
: 数据库名,这里会和 schema.xml 中的配置关联,多个用逗号离开,例如须要这个用户须要治理两个数据库 db1,db2,则配置 db1,db2
4. 批改配置文件 schema.xml
删除 <schema>
标签间的表信息配置 dataNode="dn1"
,<dataNode>
标签只留一个,<dataHost>
标签只留一个,<writeHost>
,<readHost>
只留一对
<mycat:schema xmlns:mycat=”http://io.mycat/”>
<!– 逻辑库 –>
<schema name=”TESTDB” checkSQLschema=”false” sqlMaxLimit=”100″ dataNode=”dn1″>
<!– 逻辑表 –>
</schema>
<!– 数据节点 database 填写实在数据库 –>
<dataNode name=”dn1″ dataHost=”host1″ database=”testdb” />
<!– 数据主机 –>
<dataHost name=”host1″ maxCon=”1000″ minCon=”10″ balance=”0″
writeType=”0″ dbType=”mysql” dbDriver=”native” switchType=”1″ slaveThreshold=”100″>
<!– 心跳检测 –>
<heartbeat>select user()</heartbeat>
<!– 写主机 –>
<writeHost host=”hostM1″ url=”192.168.107.132:3306″ user=”root”
password=”000000″>
<!– 读主机 –>
<readHost host=”hostS2″ url=”192.168.107.108:3306″ user=”hzy” password=”000000″/>
</writeHost>
</dataHost>
</mycat:schema>
5. 验证数据库拜访状况
Mycat 作为数据库中间件要和数据库部署在不同机器上,所以要验证近程拜访状况。
mysql -uhzy -p000000 -h 192.168.107.108 -P 3306
mysql -uroot -p000000 -h 192.168.107.132 -P 3306
如近程拜访报错,请建对应用户
grant all privileges on . to root@’ 短少的 host’ identified by ‘000000’;
6. 启动 mycat
- 控制台启动:去 mycat/bin 目录下执行 ./mycat console
- 后盾启动:去 mycat/bin 目录下 ./mycat start
为了能第一工夫看到启动日志,不便定位问题,咱们抉择控制台启动。
7. 登录 mycat
- 登录后盾治理窗口
此登录形式用于治理保护 Mycat
mysql -umycat -p123456 -P 9066 -h 192.168.140.128
常用命令如下:
show database;
show @@help;
- 登录数据窗口
此登录形式用于通过 Mycat 查问数据,咱们抉择这种形式拜访 Mycat
mysql -uroot -p123456 -P 8066 -h 192.168.107.132
读写拆散
咱们通过 Mycat 和 MySQL 的 主从复制 配合搭建数据库的 读写拆散,实现 MySQL 的高可用性。咱们将搭建:一主一从、双主双从两种读写拆散模式。
mysql 主从复制原理:
与 redis 不同的是,redis 从机会把主机的数据从头到尾复制, 而 mysql 从机只会从接入点开始复制; 并且会产生屡次 IO 有延时问题
主机配置
批改配置文件:
vim /etc/my.cnf
主服务器惟一 ID
server-id=1
启用二进制日志
log-bin=mysql-bin
设置不要复制的数据库(可设置多个)
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
设置须要复制的数据库
binlog-do-db= 须要复制的主数据库名字
设置 logbin 格局
binlog_format=STATEMENT
binlog 的三种模式:https://blog.csdn.net/vhomes/article/details/8082734
从机配置
vim /etc/my.cnf
从服务器惟一 ID
server-id=2
启用中继日志
relay-log=mysql-relay
- 重启服务:
systemctl restart mysqld
- 确认防火墙已敞开:
systemctl status firewalld
- 在主机 MySQL 里执行受权命令:
GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' IDENTIFIED BY '123123';
如果报错:Your password does not satisfy the current policy requirements, 请执行:
set global validate_password_policy=0;
set global validate_password_length=4;
- 查看主机状态:
show master status; | ||||
---|---|---|---|---|
File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
mysql-bin.000001 | 438 | testdb | mysql,information_schema |
1 row in set (0.01 sec)
记录下 File 和 Position 的值, 执行完此步骤后不要再操作主服务器 MySQL,避免主服务器状态值变动
- 在从机上配置须要复制的主机
复制主机的命令
CHANGE MASTER TO MASTER_HOST=’ 主机的 IP 地址 ’,
MASTER_USER=’slave’,
MASTER_PASSWORD=’123123′,
MASTER_LOG_FILE=’mysql-bin. 具体数字 ’,
MASTER_LOG_POS= 具体值;
启动从服务器复制性能
start slave;
查看从服务器状态
show slave statusG
例子:
CHANGE MASTER TO MASTER_HOST=’192.168.107.132′,
MASTER_USER=’slave’,
MASTER_PASSWORD=’123123′,
MASTER_LOG_FILE=’mysql-bin.000003′,
MASTER_LOG_POS=154;
如果之前配置过主从复制, 须要先重置stop slave;
, reset master;
接着启动从服务器复制性能 start slave;
查看从服务器状态 show slave statusG
(不要加分号)
这两项尾 Yes 即为胜利
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
如果失败可查看 Error 项
Last_Errno: 0
Last_Error:
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
主机新建库(my.cnf 中规定的库)、新建表、insert 记录,从机复制
一主一从
<!– 写主机 –>
<writeHost host="hostM1" url="192.168.107.132:3306" user="root"
password="000000">
<!– 读主机 –>
<readHost host="hostS2" url="192.168.107.108:3306" user="hzy" password="000000"/>
启动 mycat
在写主机数据库表 mytbl 中插入带零碎变量数据,造成主从数据不统一 INSERT INTO mytbl VALUES(2,@@hostname);
应用 mycat 查问, 能够看到 mycat 操作的是主机数据
起因是咱们没有指定数据库拜访的 负载策略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″,所有读申请随机的散发到 readhost 执行,writerHost 不累赘读压力
双主双从选 1, 单主单从或多从选 3
批改 schema.xml
<!– 数据主机 –>
<dataHost name="host1" maxCon="1000" minCon="10" balance="3"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
双主双从
一个主机 m1 用于解决所有写申请,它的从机 s1 和另一台主机 m2 还有它的从机 s2 负责所有读请 求。当 m1 主机宕机后,m2 主机负责写申请,m1、m2 互为备机。
Master1 配置
批改配置文件:vim /etc/my.cnf
主服务器惟一 ID
server-id=1
启用二进制日志
log-bin=mysql-bin
设置不要复制的数据库(可设置多个)
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
设置须要复制的数据库
binlog-do-db= 须要复制的主数据库名字
设置 logbin 格局
binlog_format=STATEMENT
在作为从数据库的时候,有写入操作也要更新二进制日志文件
log-slave-updates
示意自增长字段每次递增的量,指字段一次递增多少,其默认值是 1,取值范畴是 1 .. 65535
auto-increment-increment=2
示意自增长字段从哪个数开始,指自增字段的起始值,他的取值范畴是 1 .. 65535
auto-increment-offset=1
Master2 配置
批改配置文件:vim /etc/my.cnf
主服务器惟一 ID
server-id=3
启用二进制日志
log-bin=mysql-bin
设置不要复制的数据库(可设置多个)
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
设置须要复制的数据库
binlog-do-db= 须要复制的主数据库名字
设置 logbin 格局
binlog_format=STATEMENT
在作为从数据库的时候,有写入操作也要更新二进制日志文件
log-slave-updates
示意自增长字段每次递增的量,指自增字段的起始值,其默认值是 1,取值范畴是 1 .. 65535
auto-increment-increment=2
示意自增长字段从哪个数开始,指自增字段的起始值,他的取值范畴是 1 .. 6553
65535 auto-increment-offset=2
留神:auto-increment-offset 不能够是反复的
Slave1 配置
从服务器惟一 ID
server-id=2
启用中继日志
relay-log=mysql-relay
Slave2 配置
从服务器惟一 ID
server-id=4
启用中继日志
relay-log=mysql-relay
- 双主机、双从机重启 mysql 服务
- 主机从机都敞开防火墙
- 在两台主机上建设帐户并受权 slave
在主机 MySQL 里执行受权命令
GRANT REPLICATION SLAVE ON . TO ‘slave’@’%’ IDENTIFIED BY ‘123123’;
- 查看两台主机的状态
show master status;
, 记录 binlogid 和接入点 - 设置两台从机的主机,Slava1 复制 Master1,Slava2 复制 Master2
- 两台主机互备,Master2 复制 Master1,Master1 复制 Master2
stop slave;
reset master;
复制主机的命令
CHANGE MASTER TO MASTER_HOST=’ 主机的 IP 地址 ’,
MASTER_USER=’slave’,
MASTER_PASSWORD=’123123′,
MASTER_LOG_FILE=’mysql-bin. 具体数字 ’,
MASTER_LOG_POS= 具体值;
启动从服务器复制性能
start slave;
查看从服务器状态
show slave statusG
- 批改 schema.xml
<dataNode name=”dn1″ dataHost=”host1″ database=”testdb” />
<dataHost name=”host1″ maxCon=”1000″ minCon=”10″ balance=”1″ 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.140.128:3306" user="root" password="123123">
<!-- can have multi read hosts -->
<readHost host="hostS1" url="192.168.140.127:3306" user="root" password="123123"/>
</writeHost>
<writeHost host="hostM2" url="192.168.140.126:3306" user="root" password="123123">
<!-- can have multi read hosts -->
<readHost host="hostS2" url="192.168.140.125:3306" user="root" password="123123"/>
</writeHost>
</dataHost>
balance=”1″: 全副的 readHost 与 stand by writeHost 参加 select 语句的负载平衡。
writeType=”0″: 所有写操作发送到配置的第一个 writeHost,第一个挂了切到还生存的第二个
writeHost,重新启动后以切换后的为准,切换记录在配置文件中:dnindex.properties。
switchType=”1″: 1 默认值,主动切换。
-1 示意不主动切换
2 基于 MySQL 主从同步的状态决定是否切换。
- 验证主从复制:Master1 主机新建库、新建表、insert 记录,Master2 和从机复制
- 验证读写拆散:
在写主机 Master1 数据库表 mytbl 中插入带零碎变量数据,造成主从数据不统一
INSERT INTO mytbl VALUES(3,@@hostname);
,
在 Mycat 里查问 mytbl 表, 能够看到查问语句在 Master2(host81)、Slava1(host80)、Slava2(host82)主从三个主机间切换;
进行数据库 Master1 在 Mycat 里插入数据仍然胜利,Master2 主动切换为写主机
垂直拆分——分库
依照业务将表进行分类(或者 Schema)来切分到不同的数据库(主机)之上,这种切能够称之为数据的垂直(纵向)切分
分库的准则:有严密关联关系的表应该在一个库里,互相没有关联关系的表能够分到不同的库里, 因为分库的表不能够关联查问
客户表 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)
);
客户表分在一个数据库,另外三张都须要关联查问,分在另外一个数据库。
1. 批改 schema 配置文件
<!– 逻辑库 –>
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
<!-- 配置 customer 表调配到 dn2 -->
<table name="customer" dataNode="dn2" ></table>
</schema>
<!-- 数据节点 -->
<dataNode name="dn1" dataHost="host1" database="orders" />
<dataNode name="dn2" dataHost="host2" database="orders" />
<!-- host1 -->
<dataHost name="host1" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<!-- 心跳检测 -->
<heartbeat>select user()</heartbeat>
<!-- 写主机 -->
<writeHost host="hostM1" url="192.168.107.132:3306" user="root"
password="000000">
</writeHost>
</dataHost>
<!-- host2 -->
<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="hostM1" url="192.168.107.108:3306" user="hzy"
password="000000">
</writeHost>
</dataHost>
通过 mycat 操作逻辑库默认执行到 dataNode="dn1"
, 咱们指定了customer
调配到dataNode="dn2"
balance="0"
不设置读写拆散
2. 两台机器别离创立新的的数据库
CREATE DATABASE orders;
3. 启动 Mycat
./mycat console
4. 拜访 Mycat 进行分库
mysql -umycat -p123456 -h 192.168.140.128 -P 8066
切换到 TESTDB
创立 4 张表
查看表信息,能够看到胜利分库
程度拆分——分表
依据表中的数据的逻辑关系依照某个字段的某种规定,将同一个表中的数据依照某种条件拆分到多台数据库(主机)下面,这种切分称之为数据的程度(横向)切分
MySQL 单表存储数据条数是有瓶颈的,单表白到 1000 万条数据就达到了瓶颈,会影响查问效率,须要进行程度拆分(分表)进行优化。例如:例子中的 orders、orders_detail 都曾经达到 600 万行数据,须要进行分表优化。
1. 分表字段 , 以 orders 表为例,能够依据不同自字段进行分表
- id 主键或创立工夫:查问订单重视时效,历史订单被查问的次数少,如此分片会造成一个节点拜访多,一个拜访少,不均匀。
- customer_id(客户 id): 依据客户 id 去分,两个节点拜访均匀,一个客户的所有订单都在同一个节点
2. 批改 schema 配置文件
<!– 逻辑库 –>
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
<!-- 配置 customer 表调配到 dn2 -->
<table name="customer" dataNode="dn2" ></table>
<table name="orders" dataNode="dn1,dn2" rule="mod_rule" ></table>
</schema>
orders表被划分到两个数据节点 dn1,dn2
,mod_rule
分片规定名称
3.批改 rule 配置文件
定义 mod_rule
分片规定, 并指定规定实用字段为 customer_id
,抉择分片算法 mod-long
(对字段求模运算),customer_id 对两个节点求模,依据后果分片
<tableRule name="mod_rule">
<rule>
<columns>customer_id</columns>
<algorithm>mod-long</algorithm>
</rule>
</tableRule>
<!-- 配置算法 mod-long 参数 count 为 2,两个节点 -->
<function name="mod-long" class="io.mycat.route.function.PartitionByMod">
<!-- 多少个数据节点 -->
<property name="count">2</property>
</function>
4. 测试分表
- 在数据节点 dn2 上建 orders 表
- 重启 Mycat,让配置失效
- 拜访 Mycat 实现分片
-
在 mycat 里向 orders 表插入数据,INSERT 字段不能省略 INSERT INTO
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);在 mycat、dn1、dn2 中查看 orders 表数据,分表胜利
Mycat 的分片“join”
Orders 订单表曾经进行分表操作了,和它关联的 orders_detail 订单详情表如何进行 join 查问。
orders_detail 也要进行分片操作。Join 的原理如下图:
ER 表
Mycat 借鉴了 NewSQL 畛域的新秀 Foundation DB 的设计思路,Foundation DB 创新性的提 出了 Table Group 的概念,其将子表的存储地位依赖于主表,并且物理上紧邻寄存,因而彻底解决了 JION 的效率和性能问 题,依据这一思路,提出了基于 E-R 关系的数据分片策略,子表的记录与所 关联的父表记录寄存在同一个数据分片上
批改 schema.xml 配置文件
配置 orders 的子表 orders_detail, 应用外键 order_id 进行分片, 这样与 orders 表的数据分布严密
在 dn2 创立 orders_detail 表 重启 Mycat 拜访 Mycat 向 orders_detail 表插入数据
INSERT INTO orders_detail(id,detail,order_id) values(1,’detail1′,1);
INSERT INTO orders_detail(id,detail,order_id) VALUES(2,’detail1′,2);
INSERT INTO orders_detail(id,detail,order_id) VALUES(3,’detail1′,3);
INSERT INTO orders_detail(id,detail,order_id) VALUES(4,’detail1′,4);
INSERT INTO orders_detail(id,detail,order_id) VALUES(5,’detail1′,5);
INSERT INTO orders_detail(id,detail,order_id) VALUES(6,’detail1′,6);
在 mycat、dn1、dn2 中运行两个表 join 语句
Select o.*,od.detail from orders o
inner join orders_detail od on o.id=od.order_id;
全局表
在分片的状况下,当业务表因为规模而进行分片当前,业务表与这些从属的字典表之间的关联,就成了比拟 辣手的问题,思考到字典表具备以下几个个性:① 变动不频繁 ② 数据量总体变动不大 ③ 数据规模不大,很少有超过数十万条记录
鉴于此,Mycat 定义了一种非凡的表,称之为“全局表”,全局表具备以下个性:
- 全局表的插入、更新操作会实时在所有节点上执行,放弃各个分片的数据一致性
- 全局表的查问操作,只从一个节点获取
- 全局表能够跟任何一个表进行 JOIN 操作
将字典表或者合乎字典表个性的一些表定义为全局表,则从另外一个方面,很好的解决了数据 JOIN 的难题。通过全局表 + 基于 E-R 关系的分片策略,Mycat 能够满足 80% 以上的企业应用开发
批改 schema.xml 配置文件
<!– 定义全局字典表 –>
在 dn2 创立 dict_order_type 表 重启 Mycat 拜访 Mycat 向 dict_order_type 表插入数据
INSERT INTO dict_order_type(id,order_type) VALUES(101,’type1′);
INSERT INTO dict_order_type(id,order_type) VALUES(102,’type2′);
在 Mycat、dn1、dn2 中查问表数据
罕用分片规定
——
### 取模
此规定为对分片字段求摸运算。也是程度分表最罕用规定。
### 分片枚举
通过在配置文件中配置可能的枚举 id,本人配置分片,本规定实用于特定的场景,比方有些业务 须要依照省份或区县来做保留,而全国省份区县固定的,这类业务应用本条规定。
* 批改 schema.xml 配置文件
<!– 分片枚举 –>
* 批改 rule.xml 配置文件
<tableRule name=”sharding_by_intfile”>
<rule>
<columns>areacode</columns>
<algorithm>hash-int</algorithm>
</rule>
</tableRule>
…
<function name=”hash-int” class=”io.mycat.route.function.PartitionByFileMap”>
<property name=”mapFile”>partition-hash-int.txt</property> <property name=”type”>1</property>
<property name=”defaultNode”>0</property>
</function>
columns
:分片字段,algorithm
:分片函数
mapFile
:标识配置文件名称,type
:分片字段类型 0 为 int 型、非 0 为 String
defaultNode
:默认节点: 小于 0 示意不设置默认节点,大于等于 0 示意设置默认节点,设置默认节点如果碰到不辨认的枚举值,就让它路由到默认节点,如不设置不辨认就报错
* 批改 partition-hash-int.txt
配置文件
110=0
120=1
#110 走 dn1, 120 走 dn2
* 重启 Mycat
* 拜访 Mycat 创立表
#订单归属区域信息表
CREATE TABLE orders_ware_info (
id
INT AUTO_INCREMENT comment ‘ 编号 ’,
order_id
INT comment ‘ 订单编号 ’,
address
VARCHAR(200) comment ‘ 地址 ’,
areacode
VARCHAR(20) comment ‘ 区域编号 ’,
PRIMARY KEY(id)
);
插入数据
INSERT INTO orders_ware_info(id, order_id,address,areacode) VALUES (1,1,’ 北京 ’,’110′); INSERT INTO orders_ware_info(id, order_id,address,areacode) VALUES (2,2,’ 天津 ’,’120′);
### 范畴约定
此分片实用于,提前布局好分片字段某个范畴属于哪个分片。
* 批改 schema.xml 配置文件
* 批改 rule.xml 配置文件
<tableRule name=”auto_sharding_long”>
<rule>
<columns>order_id</columns>
<algorithm>rang-long</algorithm>
</rule>
</tableRule>
…
<function name=”rang-long” class=”io.mycat.route.function.AutoPartitionByLong”>
<property name=”mapFile”>autopartition-long.txt</property> <property name=”defaultNode”>0</property>
</function>
columns
:分片字段,algorithm
:分片函数 mapFile
:标识配置文件名称
defaultNode
:默认节点: 小于 0 示意不设置默认节点,大于等于 0 示意设置默认节点,设置默认节点如果碰到不辨认的枚举值,就让它路由到默认节点,如不设置不辨认就 报错
* 批改 autopartition-long.txt 配置文件
0-102=0
103-200=1
* 重启 Mycat
* 拜访 Mycat 创立表
# 领取信息表
CREATE TABLE payment_info (
id
INT AUTO_INCREMENT comment ‘ 编号 ’,
order_id
INT comment ‘ 订单编号 ’,
payment_status
INT comment ‘ 领取状态 ’,
PRIMARY KEY(id)
);
* 插入数据
INSERT INTO payment_info (id,order_id,payment_status) VALUES (1,101,0);
INSERT INTO payment_info (id,order_id,payment_status) VALUES (2,102,1);
INSERT INTO payment_info (id,order_id ,payment_status) VALUES (3,103,0);
INSERT INTO payment_info (id,order_id,payment_status) VALUES (4,104,1);
* 查问 Mycat、dn1、dn2 能够看到数据分片成果
### 日期 (天) 分片
* 批改 schema.xml 配置文件
<!– 日期分片 –>
* 批改 rule.xml 配置文件
<tableRule name=”sharding_by_date”>
<rule>
<columns>login_date</columns>
<algorithm>shardingByDate</algorithm>
</rule>
</tableRule>
…
<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>
columns
:分片字段,algorithm
:分片函数 dateFormat
:日期格局 sBeginDate
:开始日期
sEndDate
:完结日期, 则代表数据达到了这个日期的分片后循环从开始分片插入
sPartionDay
:分区天数,即默认从开始日期算起,分隔 2 天一个分区
* 重启 Mycat
* 拜访 Mycat 创立表
* 用户信息表
CREATE TABLE login_info (
id
INT AUTO_INCREMENT comment ‘ 编号 ’,
user_id
INT comment ‘ 用户编号 ’,
login_date
date comment ‘ 登录日期 ’,
PRIMARY KEY(id)
);
INSERT INTO login_info(id,user_id,login_date) VALUES (1,101,’2019-01-01′);
INSERT INTO login_info(id,user_id,login_date) VALUES (2,102,’2019-01-02′);
INSERT INTO login_info(id,user_id,login_date) VALUES (3,103,’2019-01-03′);
INSERT INTO login_info(id,user_id,login_date) VALUES (4,104,’2019-01-04′);
INSERT INTO login_info(id,user_id,login_date) VALUES (5,103,’2019-01-05′);
INSERT INTO login_info(id,user_id,login_date) VALUES (6,104,’2019-01-06′);
* 查问 Mycat、dn1、dn2 能够看到数据分片成果
全局序列
—-
在实现分库分表的状况下,数据库自增主键已无奈保障自增主键的全局惟一。为此,Mycat 提供 了全局 sequence,并且提供了蕴含本地配置和数据库配置等多种实现形式
1、本地文件 此形式 Mycat 将 sequence 配置到文件中,当应用到 sequence 中的配置后,Mycat 会更新 classpath 中的 sequence_conf.properties 文件中 sequence 以后的值。
* 长处:本地加载,读取速度较快
* 毛病:抗危险能力差,Mycat 所在主机宕机后,无奈读取本地文件。
2、数据库形式 利用数据库一个表 来进行计数累加。然而并不是每次生成序列都读写数据库,这样效率太低。Mycat 会预加载一部分号段到 Mycat 的内存中,这样大部分读写序列都是在内存中实现的。如果内存中的号段用完了 Mycat 会再向数据库要一次。
问:那如果 Mycat 解体了,那内存中的序列岂不是都没了?是的。如果是这样,那么 Mycat 启动后会向数据库申请新的号段,原有号段会弃用。也就是说如果 Mycat 重启,那么损失是以后的号段没用完的号码,然而不会因而呈现主键反复
* 全局序列表
#在 dn1 上创立全局序列表
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 $$ D
ELIMITER ;
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 ;
* 初始化序列表记录
INSERT INTO MYCAT_SEQUENCE(NAME,current_value,increment) VALUES (‘ORDERS’, 400000, 100);
* 批改 Mycat 配置
批改sequence_db_conf.properties
#sequence stored in datanode
GLOBAL=dn1
COMPANY=dn1
CUSTOMER=dn1
ORDERS=dn1
意思是 ORDERS 这个序列在 dn1 这个节点上,具体 dn1 节点是哪台机子,请参考 schema.xml
* 批改 server.xml
<property name=”sequnceHandlerType”>1</property>
全局序列类型:0- 本地文件,1- 数据库形式,2- 工夫戳形式。此处应该批改成 1。
title: MyCat 学习笔记 # 题目
date: 2019/10/26 16:23:46 # 工夫
categories: # 分类
– 数据库
tags: # 标签
– 数据库中间件
– 分库分表
Mycat 数据库中间件
===========
1、数据库中间件:
是一类连贯软件组件和利用的计算机软件,以便于软件各部件之间的沟通。例子:Tomcat,web 中间件。数据库中间件:连贯 java 应用程序和数据库
<!–more–>
2、为什么要用 Mycat?
* Java 与数据库紧耦合
* 高访问量高并发对数据库的压力
* 读写申请数据不统一
mysql 单表瓶颈 1000w 数据, 单库瓶颈 5000w 数据
数据库中间件比照:
Mycat 的官网: http://www.mycat.io/
Mycat 能干什么:
* 读写拆散
* 数据分片 垂直拆分(分库)、程度拆分(分表)、垂直 + 程度拆分(分库分表)
* 多数据源整合 原理:
Mycat 的原理中最重要的一个动词是“拦挡”,它拦挡了用户发送过去的 SQL 语句,首先对 SQL 语句做了一些特定的剖析:如分片剖析、路由剖析、读写拆散剖析、缓存剖析等,而后将此 SQL 发 往后端的实在数据库,并将返回的后果做适当的解决,最终再返回给用户
装置启动
—-
1、解压后即可应用 解压缩文件拷贝到 linux 下 /usr/local/ 2、三个配置文件
* schema.xml:定义逻辑库,表、分片节点等内容
* rule.xml:定义分片规定
* server.xml:定义用户以及零碎相干变量,如端口等
3、批改配置文件 server.xml 批改用户信息,与 MySQL 辨别
<!– 用户名 –>
<user name=”root”>
<property name=”password”>123456</property> <!– 明码 –>
<property name=”schemas”>TESTDB</property> <!– 治理的库 –>
</user>
schemas
: 数据库名,这里会和 schema.xml 中的配置关联,多个用逗号离开,例如须要这个用户须要治理两个数据库 db1,db2,则配置 db1,db2
4. 批改配置文件 schema.xml
删除 <schema>
标签间的表信息配置 dataNode="dn1"
,<dataNode>
标签只留一个,<dataHost>
标签只留一个,<writeHost>
,<readHost>
只留一对
<mycat:schema xmlns:mycat=”http://io.mycat/”>
<!– 逻辑库 –>
<schema name=”TESTDB” checkSQLschema=”false” sqlMaxLimit=”100″ dataNode=”dn1″>
<!– 逻辑表 –>
</schema>
<!– 数据节点 database 填写实在数据库 –>
<dataNode name=”dn1″ dataHost=”host1″ database=”testdb” />
<!– 数据主机 –>
<dataHost name=”host1″ maxCon=”1000″ minCon=”10″ balance=”0″
writeType=”0″ dbType=”mysql” dbDriver=”native” switchType=”1″ slaveThreshold=”100″>
<!– 心跳检测 –>
<heartbeat>select user()</heartbeat>
<!– 写主机 –>
<writeHost host=”hostM1″ url=”192.168.107.132:3306″ user=”root”
password=”000000″>
<!– 读主机 –>
<readHost host=”hostS2″ url=”192.168.107.108:3306″ user=”hzy” password=”000000″/>
</writeHost>
</dataHost>
</mycat:schema>
5. 验证数据库拜访状况
Mycat 作为数据库中间件要和数据库部署在不同机器上,所以要验证近程拜访状况。
mysql -uhzy -p000000 -h 192.168.107.108 -P 3306
mysql -uroot -p000000 -h 192.168.107.132 -P 3306
# 如近程拜访报错,请建对应用户
grant all privileges on . to root@’ 短少的 host’ identified by ‘000000’;
6. 启动 mycat
* 控制台启动:去 mycat/bin 目录下执行 ./mycat console
* 后盾启动:去 mycat/bin 目录下 ./mycat start
为了能第一工夫看到启动日志,不便定位问题,咱们抉择控制台启动。
7. 登录 mycat
* 登录后盾治理窗口
此登录形式用于治理保护 Mycat
mysql -umycat -p123456 -P 9066 -h 192.168.140.128
#常用命令如下:
show database;
show @@help;
* 登录数据窗口
此登录形式用于通过 Mycat 查问数据,咱们抉择这种形式拜访 Mycat
mysql -uroot -p123456 -P 8066 -h 192.168.107.132
读写拆散
—-
咱们通过 Mycat 和 MySQL 的 主从复制 配合搭建数据库的 读写拆散,实现 MySQL 的高可用性。咱们将搭建:一主一从、双主双从两种读写拆散模式。
mysql 主从复制原理:
与 redis 不同的是,redis 从机会把主机的数据从头到尾复制, 而 mysql 从机只会从接入点开始复制; 并且会产生屡次 IO 有延时问题
### 主机配置
批改配置文件:
vim /etc/my.cnf
#主服务器惟一 ID
server-id=1
#启用二进制日志
log-bin=mysql-bin
# 设置不要复制的数据库(可设置多个)
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
#设置须要复制的数据库
binlog-do-db= 须要复制的主数据库名字
#设置 logbin 格局
binlog_format=STATEMENT
> binlog 的三种模式:https://blog.csdn.net/vhomes/article/details/8082734
### 从机配置
vim /etc/my.cnf
#从服务器惟一 ID
server-id=2
#启用中继日志
relay-log=mysql-relay
* 重启服务:systemctl restart mysqld
* 确认防火墙已敞开:systemctl status firewalld
* 在主机 MySQL 里执行受权命令: GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' IDENTIFIED BY '123123';
如果报错:Your password does not satisfy the current policy requirements, 请执行:
set global validate_password_policy=0;
set global validate_password_length=4;
* 查看主机状态:
show master status;
+——————+———-+————–+————————–+——————-+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+——————+———-+————–+————————–+——————-+
| mysql-bin.000001 | 438 | testdb | mysql,information_schema | |
+——————+———-+————–+————————–+——————-+
1 row in set (0.01 sec)
记录下 File 和 Position 的值, 执行完此步骤后不要再操作主服务器 MySQL,避免主服务器状态值变动
* 在从机上配置须要复制的主机
# 复制主机的命令
CHANGE MASTER TO MASTER_HOST=’ 主机的 IP 地址 ’,
MASTER_USER=’slave’,
MASTER_PASSWORD=’123123′,
MASTER_LOG_FILE=’mysql-bin. 具体数字 ’,
MASTER_LOG_POS= 具体值;
#启动从服务器复制性能
start slave;
#查看从服务器状态
show slave statusG
例子:
CHANGE MASTER TO MASTER_HOST=’192.168.107.132′,
MASTER_USER=’slave’,
MASTER_PASSWORD=’123123′,
MASTER_LOG_FILE=’mysql-bin.000003′,
MASTER_LOG_POS=154;
如果之前配置过主从复制, 须要先重置stop slave;
, reset master;
接着启动从服务器复制性能 start slave;
查看从服务器状态 show slave statusG
(不要加分号)
# 这两项尾 Yes 即为胜利
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
#如果失败可查看 Error 项
Last_Errno: 0
Last_Error:
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
主机新建库(my.cnf 中规定的库)、新建表、insert 记录,从机复制
### 一主一从
<!– 写主机 –>
<writeHost host=”hostM1″ url=”192.168.107.132:3306″ user=”root”
password=”000000″>
<!– 读主机 –>
<readHost host=”hostS2″ url=”192.168.107.108:3306″ user=”hzy” password=”000000″/>
启动 mycat
在写主机数据库表 mytbl 中插入带零碎变量数据,造成主从数据不统一 INSERT INTO mytbl VALUES(2,@@hostname);
应用 mycat 查问, 能够看到 mycat 操作的是主机数据
起因是咱们没有指定数据库拜访的 负载策略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″,所有读申请随机的散发到 readhost 执行,writerHost 不累赘读压力
双主双从选 1, 单主单从或多从选 3
批改 schema.xml
<!– 数据主机 –>
<dataHost name=”host1″ maxCon=”1000″ minCon=”10″ balance=”3″
writeType=”0″ dbType=”mysql” dbDriver=”native” switchType=”1″ slaveThreshold=”100″>
### 双主双从
一个主机 m1 用于解决所有写申请,它的从机 s1 和另一台主机 m2 还有它的从机 s2 负责所有读请 求。当 m1 主机宕机后,m2 主机负责写申请,m1、m2 互为备机。
Master1 配置
批改配置文件:vim /etc/my.cnf
#主服务器惟一 ID
server-id=1
#启用二进制日志
log-bin=mysql-bin
# 设置不要复制的数据库(可设置多个)
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
#设置须要复制的数据库
binlog-do-db= 须要复制的主数据库名字
#设置 logbin 格局
binlog_format=STATEMENT
# 在作为从数据库的时候,有写入操作也要更新二进制日志文件
log-slave-updates
#示意自增长字段每次递增的量,指字段一次递增多少,其默认值是 1,取值范畴是 1 .. 65535
auto-increment-increment=2
# 示意自增长字段从哪个数开始,指自增字段的起始值,他的取值范畴是 1 .. 65535
auto-increment-offset=1
Master2 配置
批改配置文件:vim /etc/my.cnf
#主服务器惟一 ID
server-id=3
#启用二进制日志
log-bin=mysql-bin
#设置不要复制的数据库(可设置多个)
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
#设置须要复制的数据库
binlog-do-db= 须要复制的主数据库名字
#设置 logbin 格局
binlog_format=STATEMENT
# 在作为从数据库的时候,有写入操作也要更新二进制日志文件
log-slave-updates
#示意自增长字段每次递增的量,指自增字段的起始值,其默认值是 1,取值范畴是 1 .. 65535
auto-increment-increment=2
# 示意自增长字段从哪个数开始,指自增字段的起始值,他的取值范畴是 1 .. 6553
65535 auto-increment-offset=2
留神:auto-increment-offset 不能够是反复的
Slave1 配置
# 从服务器惟一 ID
server-id=2
#启用中继日志
relay-log=mysql-relay
Slave2 配置
# 从服务器惟一 ID
server-id=4
#启用中继日志
relay-log=mysql-relay
* 双主机、双从机重启 mysql 服务
* 主机从机都敞开防火墙
* 在两台主机上建设帐户并受权 slave
# 在主机 MySQL 里执行受权命令
GRANT REPLICATION SLAVE ON . TO ‘slave’@’%’ IDENTIFIED BY ‘123123’;
* 查看两台主机的状态show master status;
, 记录 binlogid 和接入点
* 设置两台从机的主机,Slava1 复制 Master1,Slava2 复制 Master2
* 两台主机互备,Master2 复制 Master1,Master1 复制 Master2
stop slave;
reset master;
#复制主机的命令
CHANGE MASTER TO MASTER_HOST=’ 主机的 IP 地址 ’,
MASTER_USER=’slave’,
MASTER_PASSWORD=’123123′,
MASTER_LOG_FILE=’mysql-bin. 具体数字 ’,
MASTER_LOG_POS= 具体值;
#启动从服务器复制性能
start slave;
#查看从服务器状态
show slave statusG
* 批改 schema.xml
<dataNode name=”dn1″ dataHost=”host1″ database=”testdb” />
<dataHost name=”host1″ maxCon=”1000″ minCon=”10″ balance=”1″ 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.140.128:3306″ user=”root” password=”123123″>
<!– can have multi read hosts –>
<readHost host=”hostS1″ url=”192.168.140.127:3306″ user=”root” password=”123123″/>
</writeHost>
<writeHost host=”hostM2″ url=”192.168.140.126:3306″ user=”root” password=”123123″>
<!– can have multi read hosts –>
<readHost host=”hostS2″ url=”192.168.140.125:3306″ user=”root” password=”123123″/>
</writeHost>
</dataHost>
#balance=”1″: 全副的 readHost 与 stand by writeHost 参加 select 语句的负载平衡。
#writeType=”0″: 所有写操作发送到配置的第一个 writeHost,第一个挂了切到还生存的第二个
#writeHost,重新启动后以切换后的为准,切换记录在配置文件中:dnindex.properties。
#switchType=”1″: 1 默认值,主动切换。
# -1 示意不主动切换
# 2 基于 MySQL 主从同步的状态决定是否切换。
* 验证主从复制:Master1 主机新建库、新建表、insert 记录,Master2 和从机复制
* 验证读写拆散:
在写主机 Master1 数据库表 mytbl 中插入带零碎变量数据,造成主从数据不统一
INSERT INTO mytbl VALUES(3,@@hostname);
,
在 Mycat 里查问 mytbl 表, 能够看到查问语句在 Master2(host81)、Slava1(host80)、Slava2(host82)主从三个主机间切换;
进行数据库 Master1 在 Mycat 里插入数据仍然胜利,Master2 主动切换为写主机
垂直拆分——分库
——–
依照业务将表进行分类(或者 Schema)来切分到不同的数据库(主机)之上,这种切能够称之为数据的垂直(纵向)切分
分库的准则:有严密关联关系的表应该在一个库里,互相没有关联关系的表能够分到不同的库里, 因为分库的表不能够关联查问
# 客户表 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)
);
客户表分在一个数据库,另外三张都须要关联查问,分在另外一个数据库。
1. 批改 schema 配置文件
<!– 逻辑库 –>
<schema name=”TESTDB” checkSQLschema=”false” sqlMaxLimit=”100″ dataNode=”dn1″>
<!– 配置 customer 表调配到 dn2 –>
</schema>
<!– 数据节点 –>
<dataNode name=”dn1″ dataHost=”host1″ database=”orders” />
<dataNode name=”dn2″ dataHost=”host2″ database=”orders” />
<!– host1 –>
<dataHost name=”host1″ maxCon=”1000″ minCon=”10″ balance=”0″
writeType=”0″ dbType=”mysql” dbDriver=”native” switchType=”1″ slaveThreshold=”100″>
<!– 心跳检测 –>
<heartbeat>select user()</heartbeat>
<!– 写主机 –>
<writeHost host=”hostM1″ url=”192.168.107.132:3306″ user=”root”
password=”000000″>
</writeHost>
</dataHost>
<!– host2 –>
<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=”hostM1″ url=”192.168.107.108:3306″ user=”hzy”
password=”000000″>
</writeHost>
</dataHost>
通过 mycat 操作逻辑库默认执行到 dataNode="dn1"
, 咱们指定了customer
调配到dataNode="dn2"
balance="0"
不设置读写拆散
2. 两台机器别离创立新的的数据库
CREATE DATABASE orders;
3. 启动 Mycat
./mycat console
4. 拜访 Mycat 进行分库
mysql -umycat -p123456 -h 192.168.140.128 -P 8066
#切换到 TESTDB
#创立 4 张表
#查看表信息,能够看到胜利分库
程度拆分——分表
——–
依据表中的数据的逻辑关系依照某个字段的某种规定,将同一个表中的数据依照某种条件拆分到多台数据库(主机)下面,这种切分称之为数据的程度(横向)切分
MySQL 单表存储数据条数是有瓶颈的,单表白到 1000 万条数据就达到了瓶颈,会影响查问效率,须要进行程度拆分(分表)进行优化。例如:例子中的 orders、orders_detail 都曾经达到 600 万行数据,须要进行分表优化。
1. 分表字段 , 以 orders 表为例,能够依据不同自字段进行分表
* id 主键或创立工夫:查问订单重视时效,历史订单被查问的次数少,如此分片会造成一个节点拜访多,一个拜访少,不均匀。
* customer_id(客户 id): 依据客户 id 去分,两个节点拜访均匀,一个客户的所有订单都在同一个节点
2. 批改 schema 配置文件
<!– 逻辑库 –>
<schema name=”TESTDB” checkSQLschema=”false” sqlMaxLimit=”100″ dataNode=”dn1″>
<!– 配置 customer 表调配到 dn2 –>
</schema>
orders表被划分到两个数据节点 dn1,dn2
,mod_rule
分片规定名称
3.批改 rule 配置文件
定义 mod_rule
分片规定, 并指定规定实用字段为 customer_id
,抉择分片算法 mod-long
(对字段求模运算),customer_id 对两个节点求模,依据后果分片
<tableRule name=”mod_rule”>
<rule>
<columns>customer_id</columns>
<algorithm>mod-long</algorithm>
</rule>
</tableRule>
<!– 配置算法 mod-long 参数 count 为 2,两个节点 –>
<function name=”mod-long” class=”io.mycat.route.function.PartitionByMod”>
<!– 多少个数据节点 –>
<property name=”count”>2</property>
</function>
4. 测试分表
* 在数据节点 dn2 上建 orders 表
* 重启 Mycat,让配置失效
* 拜访 Mycat 实现分片
* 在 mycat 里向 orders 表插入数据,INSERT 字段不能省略 INSERT INTO
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);
在 mycat、dn1、dn2 中查看 orders 表数据,分表胜利
Mycat 的分片“join”
—————-
Orders 订单表曾经进行分表操作了,和它关联的 orders_detail 订单详情表如何进行 join 查问。
orders_detail 也要进行分片操作。Join 的原理如下图:
### ER 表
Mycat 借鉴了 NewSQL 畛域的新秀 Foundation DB 的设计思路,Foundation DB 创新性的提 出了 Table Group 的概念,其将子表的存储地位依赖于主表,并且物理上紧邻寄存,因而彻底解决了 JION 的效率和性能问 题,依据这一思路,提出了基于 E-R 关系的数据分片策略,子表的记录与所 关联的父表记录寄存在同一个数据分片上
批改 schema.xml 配置文件
配置 orders 的子表 orders_detail, 应用外键 order_id 进行分片, 这样与 orders 表的数据分布严密
在 dn2 创立 orders_detail 表 重启 Mycat 拜访 Mycat 向 orders_detail 表插入数据
INSERT INTO orders_detail(id,detail,order_id) values(1,’detail1′,1);
INSERT INTO orders_detail(id,detail,order_id) VALUES(2,’detail1′,2);
INSERT INTO orders_detail(id,detail,order_id) VALUES(3,’detail1′,3);
INSERT INTO orders_detail(id,detail,order_id) VALUES(4,’detail1′,4);
INSERT INTO orders_detail(id,detail,order_id) VALUES(5,’detail1′,5);
INSERT INTO orders_detail(id,detail,order_id) VALUES(6,’detail1′,6);
在 mycat、dn1、dn2 中运行两个表 join 语句
Select o.*,od.detail from orders o
inner join orders_detail od on o.id=od.order_id;
全局表
在分片的状况下,当业务表因为规模而进行分片当前,业务表与这些从属的字典表之间的关联,就成了比拟 辣手的问题,思考到字典表具备以下几个个性:① 变动不频繁 ② 数据量总体变动不大 ③ 数据规模不大,很少有超过数十万条记录
鉴于此,Mycat 定义了一种非凡的表,称之为“全局表”,全局表具备以下个性:
- 全局表的插入、更新操作会实时在所有节点上执行,放弃各个分片的数据一致性
- 全局表的查问操作,只从一个节点获取
- 全局表能够跟任何一个表进行 JOIN 操作
将字典表或者合乎字典表个性的一些表定义为全局表,则从另外一个方面,很好的解决了数据 JOIN 的难题。通过全局表 + 基于 E-R 关系的分片策略,Mycat 能够满足 80% 以上的企业应用开发
批改 schema.xml 配置文件
<!– 定义全局字典表 –>
在 dn2 创立 dict_order_type 表 重启 Mycat 拜访 Mycat 向 dict_order_type 表插入数据
INSERT INTO dict_order_type(id,order_type) VALUES(101,’type1′);
INSERT INTO dict_order_type(id,order_type) VALUES(102,’type2′);
在 Mycat、dn1、dn2 中查问表数据
罕用分片规定
——
### 取模
此规定为对分片字段求摸运算。也是程度分表最罕用规定。
### 分片枚举
通过在配置文件中配置可能的枚举 id,本人配置分片,本规定实用于特定的场景,比方有些业务 须要依照省份或区县来做保留,而全国省份区县固定的,这类业务应用本条规定。
* 批改 schema.xml 配置文件
<!– 分片枚举 –>
* 批改 rule.xml 配置文件
<tableRule name=”sharding_by_intfile”>
<rule>
<columns>areacode</columns>
<algorithm>hash-int</algorithm>
</rule>
</tableRule>
…
<function name=”hash-int” class=”io.mycat.route.function.PartitionByFileMap”>
<property name=”mapFile”>partition-hash-int.txt</property> <property name=”type”>1</property>
<property name=”defaultNode”>0</property>
</function>
columns
:分片字段,algorithm
:分片函数
mapFile
:标识配置文件名称,type
:分片字段类型 0 为 int 型、非 0 为 String
defaultNode
:默认节点: 小于 0 示意不设置默认节点,大于等于 0 示意设置默认节点,设置默认节点如果碰到不辨认的枚举值,就让它路由到默认节点,如不设置不辨认就报错
* 批改 partition-hash-int.txt
配置文件
110=0
120=1
#110 走 dn1, 120 走 dn2
* 重启 Mycat
* 拜访 Mycat 创立表
#订单归属区域信息表
CREATE TABLE orders_ware_info (
id
INT AUTO_INCREMENT comment ‘ 编号 ’,
order_id
INT comment ‘ 订单编号 ’,
address
VARCHAR(200) comment ‘ 地址 ’,
areacode
VARCHAR(20) comment ‘ 区域编号 ’,
PRIMARY KEY(id)
);
插入数据
INSERT INTO orders_ware_info(id, order_id,address,areacode) VALUES (1,1,’ 北京 ’,’110′); INSERT INTO orders_ware_info(id, order_id,address,areacode) VALUES (2,2,’ 天津 ’,’120′);
### 范畴约定
此分片实用于,提前布局好分片字段某个范畴属于哪个分片。
* 批改 schema.xml 配置文件
* 批改 rule.xml 配置文件
<tableRule name=”auto_sharding_long”>
<rule>
<columns>order_id</columns>
<algorithm>rang-long</algorithm>
</rule>
</tableRule>
…
<function name=”rang-long” class=”io.mycat.route.function.AutoPartitionByLong”>
<property name=”mapFile”>autopartition-long.txt</property> <property name=”defaultNode”>0</property>
</function>
columns
:分片字段,algorithm
:分片函数 mapFile
:标识配置文件名称
defaultNode
:默认节点: 小于 0 示意不设置默认节点,大于等于 0 示意设置默认节点,设置默认节点如果碰到不辨认的枚举值,就让它路由到默认节点,如不设置不辨认就 报错
* 批改 autopartition-long.txt 配置文件
0-102=0
103-200=1
* 重启 Mycat
* 拜访 Mycat 创立表
# 领取信息表
CREATE TABLE payment_info (
id
INT AUTO_INCREMENT comment ‘ 编号 ’,
order_id
INT comment ‘ 订单编号 ’,
payment_status
INT comment ‘ 领取状态 ’,
PRIMARY KEY(id)
);
* 插入数据
INSERT INTO payment_info (id,order_id,payment_status) VALUES (1,101,0);
INSERT INTO payment_info (id,order_id,payment_status) VALUES (2,102,1);
INSERT INTO payment_info (id,order_id ,payment_status) VALUES (3,103,0);
INSERT INTO payment_info (id,order_id,payment_status) VALUES (4,104,1);
* 查问 Mycat、dn1、dn2 能够看到数据分片成果
### 日期 (天) 分片
* 批改 schema.xml 配置文件
<!– 日期分片 –>
* 批改 rule.xml 配置文件
<tableRule name=”sharding_by_date”>
<rule>
<columns>login_date</columns>
<algorithm>shardingByDate</algorithm>
</rule>
</tableRule>
…
<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>
columns
:分片字段,algorithm
:分片函数 dateFormat
:日期格局 sBeginDate
:开始日期
sEndDate
:完结日期, 则代表数据达到了这个日期的分片后循环从开始分片插入
sPartionDay
:分区天数,即默认从开始日期算起,分隔 2 天一个分区
* 重启 Mycat
* 拜访 Mycat 创立表
* 用户信息表
CREATE TABLE login_info (
id
INT AUTO_INCREMENT comment ‘ 编号 ’,
user_id
INT comment ‘ 用户编号 ’,
login_date
date comment ‘ 登录日期 ’,
PRIMARY KEY(id)
);
INSERT INTO login_info(id,user_id,login_date) VALUES (1,101,’2019-01-01′);
INSERT INTO login_info(id,user_id,login_date) VALUES (2,102,’2019-01-02′);
INSERT INTO login_info(id,user_id,login_date) VALUES (3,103,’2019-01-03′);
INSERT INTO login_info(id,user_id,login_date) VALUES (4,104,’2019-01-04′);
INSERT INTO login_info(id,user_id,login_date) VALUES (5,103,’2019-01-05′);
INSERT INTO login_info(id,user_id,login_date) VALUES (6,104,’2019-01-06′);
* 查问 Mycat、dn1、dn2 能够看到数据分片成果
全局序列
—-
在实现分库分表的状况下,数据库自增主键已无奈保障自增主键的全局惟一。为此,Mycat 提供 了全局 sequence,并且提供了蕴含本地配置和数据库配置等多种实现形式
1、本地文件 此形式 Mycat 将 sequence 配置到文件中,当应用到 sequence 中的配置后,Mycat 会更新 classpath 中的 sequence_conf.properties 文件中 sequence 以后的值。
* 长处:本地加载,读取速度较快
* 毛病:抗危险能力差,Mycat 所在主机宕机后,无奈读取本地文件。
2、数据库形式 利用数据库一个表 来进行计数累加。然而并不是每次生成序列都读写数据库,这样效率太低。Mycat 会预加载一部分号段到 Mycat 的内存中,这样大部分读写序列都是在内存中实现的。如果内存中的号段用完了 Mycat 会再向数据库要一次。
问:那如果 Mycat 解体了,那内存中的序列岂不是都没了?是的。如果是这样,那么 Mycat 启动后会向数据库申请新的号段,原有号段会弃用。也就是说如果 Mycat 重启,那么损失是以后的号段没用完的号码,然而不会因而呈现主键反复
* 全局序列表
#在 dn1 上创立全局序列表
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 $$ D
ELIMITER ;
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 ;
* 初始化序列表记录
INSERT INTO MYCAT_SEQUENCE(NAME,current_value,increment) VALUES (‘ORDERS’, 400000, 100);
* 批改 Mycat 配置
批改sequence_db_conf.properties
#sequence stored in datanode
GLOBAL=dn1
COMPANY=dn1
CUSTOMER=dn1
ORDERS=dn1
意思是 ORDERS 这个序列在 dn1 这个节点上,具体 dn1 节点是哪台机子,请参考 schema.xml
* 批改 server.xml
<property name=”sequnceHandlerType”>1</property>
全局序列类型:0- 本地文件,1- 数据库形式,2- 工夫戳形式。此处应该批改成 1。
* 重启 Mycat
* 验证全局序列 登录 Mycat,插入数据,id 应用 MYCATSEQ_ORDERS
全局序列表中的下一个值
insert into orders(id,amount,customer_id,order_type) values(next value for MYCATSEQ_ORDERS,1000,101,102);
* 重启 Mycat 后,再次插入数据,再查问
* 重启 Mycat
* 验证全局序列 登录 Mycat,插入数据,id 应用 MYCATSEQ_ORDERS
全局序列表中的下一个值
insert into orders(id,amount,customer_id,order_type) values(next value for MYCATSEQ_ORDERS,1000,101,102);
* 重启 Mycat 后,再次插入数据,再查问