分库分表、部署MyCAT服务、分片规则
关系型数据库本身比较容易成为系统瓶颈,单机存储容量、连接数、处理能力都有限。 当单表的数据量达到1000W或100G以后,由于查询维度较多,即使添加从库、优化索引,做很多操作时性能仍下降严重。此时就要考虑对其进行切分了,切分的目的就在于减少数据库的负担,缩短查询时间。 数据库分布式核心内容无非就是数据切分(Sharding),以及切分后对数据的定位、整合。 数据切分就是将数据分散存储到多个数据库中,使得单一数据库中的数据量变小,通过扩充主机的数量缓解单一数据库的性能问题,从而达到提升数据库操作性能的目的。 垂直切分常见有垂直分库和垂直分表两种。 垂直分库就是根据业务耦合性,将关联度低的不同表存储在不同的数据库。做法与大系统拆分为多个小系统类似,按业务分类进行独立划分。 垂直分表是基于数据库中的"列"进行,某个表字段较多,可以新建一张扩展表,将不经常用或字段长度较大的字段拆分出去到扩展表中。 垂直切分的优点: 解决业务系统层面的耦合,业务清晰 对不同业务的数据进行分级管理、维护、监控、扩展等 高并发场景下,垂直切分一定程度的提升IO、数据库连接数、单机硬件资源的瓶颈 垂直切分的缺点: 部分表无法join,只能通过接口聚合方式解决,提升了开发的复杂度 分布式事务处理复杂 依然存在单表数据量过大的问题(需要水平切分) 当一个应用难以再细粒度的垂直切分,或切分后数据量行数巨大,存在单库读写、存储性能瓶颈,这时候就需要进行水平切分了。 水平切分分为库内分表和分库分表,是根据表内数据内在的逻辑关系,将同一个表按不同的条件分散到多个数据库或多个表中,每个表中只包含一部分数据,从而使得单个表的数据量变小,达到分布式的效果。 库内分表只解决了单一表数据量过大的问题,但没有将表分布到不同机器的库上,因此对于减轻MySQL数据库的压力来说,帮助不是很大,大家还是竞争同一个物理机的CPU、内存、网络IO,最好通过分库分表来解决。 水平切分后同一张表会出现在多个数据库/表中,每个库/表的内容不同。 水平切分的优点: 不存在单库数据量过大、高并发的性能瓶颈,提升系统稳定性和负载能力 应用端改造较小,不需要拆分业务模块 水平切分的缺点: 跨分片的事务一致性难以保证 跨库的join关联查询性能较差 数据多次扩展难度和维护量极大 mycat是基于Java的分布式数据库系统中间件,为高并发环境的分布式存储提供解决方案 适合数据大量写入的存储需求 支持MySQL、Oracle、Sqlserver、Mongodb等 提供数据读写分离服务 提供数据分片服务 基于阿里巴巴Cobar进行研发的开源软件 枚举法 sharding-by-intfile 固定分片 rule 范围约定 auto-sharding-long 求模法 mod-long 日期列分区法 sharding-by-date 通配取模 sharding-by-pattern ASCII码求模通配 sharding-by-prefixpattern 编程指定 sharding-by-substring 字符串拆分hash解析 sharding-by-stringhash 一致性hash sharding-by-murmur 解析SQL命令涉及到的表 然后看对表的配置,如果有分片规则,则获取SQL命令里分片字段的值,并匹配分片函数,获得分片列表 然后将SQL命令发往对应的数据库服务器去执行 最后收集和处理所有分片结果数据,并返回到客户端 部署MyCat 运行环境 安装软件包 修改配置文件:设置连接账号及逻辑库 修改配置文件:配置数据分片 在MyCat服务器上测试到3台数据库服务器的连接账户 启动服务 客户端连接测试 使用sharding-by-intfile规则 决定使用枚举法的表 找到数据分片字段名 查看数据分片使用的函数名 找到函数调用的配置文件 修改函数配置文件 查看使用枚举法的表 查看规则文件,得知sharding-by-intfile分片规则使用的函数 修改函数配置文件,添加dn3 数据节点 重启服务 客户端连接分片服务器,存取数据 使用mod-long规则 切分规则根据配置中输入的数值n 分片规则将数据分成n份(通常dn节点也为n),从而将数据均匀的分布于各节点上 这种策略可以很好的分散数据库写的压力。比较适合于单点查询的情景 查看配置文件,得知使用mod-long分片规则的表名 查看规则文件,得知mod-long分片规则使用的函数 重启服务 客户端连接分片服务器,存取数据 类型为global的表存储在所有的库中 查看全局表 客户端连接分片服务器,存取数据分库分表
概述
数据切分方式
垂直(纵向)切分
水平(横向)切分
MyCAT
概述
分片规则
工作过程
部署MyCAT服务
graph LR
c(client:192.168.1.10)-->mc(mycat:192.168.1.15)mc-->db1(db1:192.168.1.11)mc-->db2(db2:192.168.1.12)mc-->db3(db3:192.168.1.13)
配置MyCat服务器
[root@mycat1 ~]# yum -y install java-1.8.0-openjdk[root@mycat1 ~]# which java/usr/bin/java[root@mycat1 ~]# java -versionopenjdk version "1.8.0_222-ea"OpenJDK Runtime Environment (build 1.8.0_222-ea-b03)OpenJDK 64-Bit Server VM (build 25.222-b03, mixed mode)
[root@mycat1 ~]# tar xf Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz[root@mycat1 ~]# mv mycat /usr/local/# 设置PATH环境变量[root@mycat1 ~]# vim /etc/profile.d/mycat.shexport PATH=$PATH:/usr/local/mycat/bin[root@mycat1 ~]# source /etc/profile.d/mycat.sh
# 保持默认配置即可[root@mycat1 ~]# vim /usr/local/mycat/conf/server.xml# 该文件中用户及逻辑库的说明如下:<user name="root"> <!--连接mycat服务时使用的用户名-->
<property name="password">123456</property> <!--用户连接mycat用户时使用的密码-->
<property name="schemas">TESTDB</property> <!--逻辑库名--></user><user name="user">
<property name="password">user</property>
<property name="schemas">TESTDB</property>
<property name="readOnly">true</property> <!--只读权限,连接mycat服务后只有读记录的权限,不写这一行则是可读可写 --></user>
[root@mycat1 ~]# vim /usr/local/mycat/conf/schema.xml<?xml version="1.0"?><!DOCTYPE mycat:schema SYSTEM "schema.dtd"><mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100"> <!--对TESTDB库下的表做分片存储 -->
<!-- auto sharding by id (long) -->
<table name="travelrecord" dataNode="dn1,dn2,dn3" rule="auto-sharding-long" /> <!-- 对travelrecord表做分片存储 -->
<!-- global table is auto cloned to all defined data nodes ,so can join with any table whose sharding node is in the same data node -->
<table name="company" primaryKey="ID" type="global" dataNode="dn1,dn2,dn3" />
<table name="goods" primaryKey="ID" type="global" dataNode="dn1,dn2,dn3" />
<!-- random sharding using mod sharind rule -->
<table name="hotnews" primaryKey="ID" autoIncrement="true" dataNode="dn1,dn2,dn3"
rule="mod-long" />
<!-- <table name="dual" primaryKey="ID" dataNode="dnx,dnoracle2" type="global"
needAddLimit="false"/> <table name="worker" primaryKey="ID" dataNode="jdbc_dn1,jdbc_dn2,jdbc_dn3"
rule="mod-long" /> -->
<table name="employee" primaryKey="ID" dataNode="dn1,dn2,dn3"
rule="sharding-by-intfile" />
<table name="customer" primaryKey="ID" dataNode="dn1,dn2,dn3"
rule="sharding-by-intfile">
<childTable name="orders" primaryKey="ID" joinKey="customer_id"
parentKey="id">
<childTable name="order_items" joinKey="order_id"
parentKey="id" />
</childTable>
<childTable name="customer_addr" primaryKey="ID" joinKey="customer_id"
parentKey="id" />
</table>
<!-- <table name="oc_call" primaryKey="ID" dataNode="dn1$0-743" rule="latest-month-calldate"
/> -->
</schema>
<!-- <dataNode name="dn1$0-743" dataHost="localhost1" database="db$0-743"
/> -->
<!-- 定义数据库主机名及存储数据的库 -->
<dataNode name="dn1" dataHost="mysql1" database="db1" />
<dataNode name="dn2" dataHost="mysql2" database="db2" />
<dataNode name="dn3" dataHost="mysql3" database="db3" />
<!-- 定义mysql1主机名对应的数据库服务器ip地址 -->
<dataHost name="mysql1" 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.1.11:3306" user="mycatadmin"
password="NSD2021@tedu.cn">
</writeHost>
</dataHost>
<dataHost name="mysql2" 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.1.12:3306" user="mycatadmin"
password="NSD2021@tedu.cn">
</writeHost>
</dataHost>
<dataHost name="mysql3" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="hostM3" url="192.168.1.13:3306" user="mycatadmin"
password="NSD2021@tedu.cn">
</writeHost>
</dataHost></mycat:schema>
配置数据库服务器
[root@mysql1 ~]# mysql -uroot -pNSD2021@tedu.cnmysql> create database db1 default charset utf8mb4;mysql> grant all on *.* to mycatadmin@'%' identified by 'NSD2021@tedu.cn';[root@mysql2 ~]# mysql -uroot -pNSD2021@tedu.cnmysql> create database db2 default charset utf8mb4;mysql> grant all on *.* to mycatadmin@'%' identified by 'NSD2021@tedu.cn';[root@mysql3 ~]# mysql -uroot -pNSD2021@tedu.cnmysql> create database db3 default charset utf8mb4;mysql> grant all on *.* to mycatadmin@'%' identified by 'NSD2021@tedu.cn';
启动MyCat
# 安装mysql客户端软件[root@mycat1 ~]# yum install -y mysql-community-client[root@mycat1 ~]# mysql -h192.168.1.11 -umycatadmin -pNSD2021@tedu.cn[root@mycat1 ~]# mysql -h192.168.1.12 -umycatadmin -pNSD2021@tedu.cn[root@mycat1 ~]# mysql -h192.168.1.13 -umycatadmin -pNSD2021@tedu.cn
[root@mycat1 ~]# mycat start[root@mycat1 ~]# netstat -tlnp | grep :8066tcp6 0 0 :::8066 :::* LISTEN 13835/java
[root@node10 ~]# mysql -h192.168.1.15 -P8066 -uroot -p123456
mysql> show databases;+----------+| DATABASE |+----------+| TESTDB |+----------+1 row in set (0.00 sec)mysql> use TESTDB;mysql> show tables;+------------------+| Tables in TESTDB |+------------------+| company || customer || customer_addr || employee || goods || hotnews || orders || order_items || travelrecord |+------------------+9 rows in set (0.00 sec)
分片规则
枚举法
概述
配置
[root@mycat1 ~]# grep -B1 sharding-by-intfile /usr/local/mycat/conf/schema.xml
<table name="employee" primaryKey="ID" dataNode="dn1,dn2,dn3"
rule="sharding-by-intfile" />
<table name="customer" primaryKey="ID" dataNode="dn1,dn2,dn3"
rule="sharding-by-intfile">
[root@mycat1 ~]# vim /usr/local/mycat/conf/rule.xml<tableRule name="sharding-by-intfile">
<rule>
<columns>sharding_id</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> <!-- 函数调用的配置文件 --></function>
[root@mycat1 ~]# vim /usr/local/mycat/conf/partition-hash-int.txt10000=0 //当sharding_id字段的值是10000时,数据存储在数据节点dn1里10010=1 //当sharding_id字段的值是10010时,数据存储在数据节点dn2里10020=2 //当sharding_id字段的值是10020时,数据存储在数据节点dn3里
[root@mycat1 conf]# mycat restart
[root@node10 ~]# mysql -h192.168.1.15 -P8066 -uroot -p123456
mysql> use TESTDB;mysql> create table employee(id int primary key, sharding_id int, name varchar(20), birth_date date);// 存储数据时必须指定字段名列表mysql> insert into employee -> (id, sharding_id, name, birth_date)
-> values -> (1, 10000, 'nb', '2000-01-01'),
-> (2, 10010, 'wk', '1998-10-01'),
-> (3, 10020, 'plj', '2002-05-04'),
-> (4, 10020, 'dmy', '1990-08-02');Query OK, 4 rows affected (0.21 sec)mysql> select * from employee;+----+-------------+------+------------+| id | sharding_id | name | birth_date |+----+-------------+------+------------+| 1 | 10000 | nb | 2000-01-01 || 2 | 10010 | wk | 1998-10-01 || 3 | 10020 | plj | 2002-05-04 || 4 | 10020 | dmy | 1990-08-02 |+----+-------------+------+------------+4 rows in set (0.09 sec)// 分别到3台服务器上查看记录[root@mysql1 ~]# mysql -uroot -pNSD2021@tedu.cn
mysql> use db1;mysql> select * from employee;+----+-------------+------+------------+| id | sharding_id | name | birth_date |+----+-------------+------+------------+| 1 | 10000 | nb | 2000-01-01 |+----+-------------+------+------------+1 row in set (0.00 sec)[root@mysql2 ~]# mysql -uroot -pNSD2021@tedu.cn
mysql> use db2;mysql> select * from employee;+----+-------------+------+------------+| id | sharding_id | name | birth_date |+----+-------------+------+------------+| 2 | 10010 | wk | 1998-10-01 |+----+-------------+------+------------+1 row in set (0.00 sec)[root@mysql3 ~]# mysql -uroot -pNSD2021@tedu.cn
mysql> use db3;mysql> select * from employee;+----+-------------+------+------------+| id | sharding_id | name | birth_date |+----+-------------+------+------------+| 3 | 10020 | plj | 2002-05-04 || 4 | 10020 | dmy | 1990-08-02 |+----+-------------+------+------------+2 rows in set (0.00 sec)
求模法
概述
配置
[root@mycat1 ~]# vim /usr/local/mycat/conf/schema.xml... ...
<table name="hotnews" primaryKey="ID" dataNode="dn1,dn2,dn3"
rule="mod-long" />... ...
[root@mycat1 ~]# vim /usr/local/mycat/conf/rule.xml<tableRule name="mod-long">
<rule>
<columns>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">3</property> <!-- 指定求模数字 --></function>
[root@mycat1 ~]# mycat restartStopping Mycat-server...Stopped Mycat-server.Starting Mycat-server...
[root@node10 ~]# mysql -h192.168.1.15 -P8066 -uroot -p123456
mysql> use TESTDB;mysql> create table hotnews(id int primary key, title varchar(50), content text);Query OK, 0 rows affected (0.59 sec)mysql> insert into hotnews -> (id, title, content)
-> values -> (1, 'python讲师变更', '庞老师讲python了'),
-> (2, 'mysql讲师变更', '张老师讲mysql'),
-> (3, 'mysql课程更新', '增加全部sql语法'),
-> (4, 'mysql时长', '课程共11天');Query OK, 4 rows affected (0.14 sec)Records: 2 Duplicates: 0 Warnings: 0mysql> select * from hotnews;+----+--------------------+-----------------------+| id | title | content |+----+--------------------+-----------------------+| 1 | python讲师变更 | 庞老师讲python了 || 4 | mysql时长 | 课程共11天 || 2 | mysql讲师变更 | 张老师讲mysql || 3 | mysql课程更新 | 增加全部sql语法 |+----+--------------------+-----------------------+4 rows in set (0.07 sec)// 分别到3台服务器上查看记录[root@mysql1 ~]# mysql -uroot -pNSD2021@tedu.cn
mysql> use db1;mysql> select * from hotnews;+----+-------------------+-----------------------+| id | title | content |+----+-------------------+-----------------------+| 3 | mysql课程更新 | 增加全部sql语法 |+----+-------------------+-----------------------+1 row in set (0.00 sec)[root@mysql2 ~]# mysql -uroot -pNSD2021@tedu.cn
mysql> use db2;mysql> select * from hotnews;+----+--------------------+-----------------------+| id | title | content |+----+--------------------+-----------------------+| 1 | python讲师变更 | 庞老师讲python了 || 4 | mysql时长 | 课程共11天 |+----+--------------------+-----------------------+2 rows in set (0.00 sec)[root@mysql3 ~]# mysql -uroot -p'NSD2021@tedu.cn'mysql> use db3;mysql> select * from hotnews;+----+-------------------+-------------------+| id | title | content |+----+-------------------+-------------------+| 2 | mysql讲师变更 | 张老师讲mysql |+----+-------------------+-------------------+1 row in set (0.00 sec)
全局表
概述
配置
[root@mycat1 ~]# grep goods /usr/local/mycat/conf/schema.xml
<table name="goods" primaryKey="ID" type="global" dataNode="dn1,dn2,dn3" />
[root@node10 ~]# mysql -h192.168.1.15 -P8066 -uroot -p123456
mysql> use TESTDB;mysql> create table goods(id int primary key auto_increment, name varchar(10));Query OK, 0 rows affected (0.61 sec)mysql> insert into goods(id, name)
-> values -> (1, '奶茶'),
-> (2, '矿泉水'),
-> (3, '花生'),
-> (4, '瓜子');Query OK, 4 rows affected (0.16 sec)Records: 4 Duplicates: 0 Warnings: 0mysql> select * from goods;+----+-----------+| id | name |+----+-----------+| 1 | 奶茶 || 2 | 矿泉水 || 3 | 花生 || 4 | 瓜子 |+----+-----------+4 rows in set (0.01 sec)// 分别到3台服务器上查看记录[root@mysql1 ~]# mysql -uroot -pNSD2021@tedu.cn
mysql> use db1;mysql> select * from goods;+----+-----------+| id | name |+----+-----------+| 1 | 奶茶 || 2 | 矿泉水 || 3 | 花生 || 4 | 瓜子 |+----+-----------+4 rows in set (0.00 sec)[root@mysql2 ~]# mysql -uroot -pNSD2021@tedu.cn
mysql> use db2;mysql> select * from goods;+----+-----------+| id | name |+----+-----------+| 1 | 奶茶 || 2 | 矿泉水 || 3 | 花生 || 4 | 瓜子 |+----+-----------+4 rows in set (0.00 sec)[root@mysql3 ~]# mysql -uroot -pNSD2021@tedu.cn
mysql> use db3
mysql> select * from goods;+----+-----------+| id | name |+----+-----------+| 1 | 奶茶 || 2 | 矿泉水 || 3 | 花生 || 4 | 瓜子 |+----+-----------+4 rows in set (0.00 sec)
1人点赞
日记本
作者:秋天丢了李姑娘
链接:https://www.jianshu.com/p/f7a6db4c6d63