MyCat数据分片(垂直拆-水平拆)

发布于 2022-07-21  4.03k 次阅读

#1. 数据分片概述

数据库分布式核心内容无非就是数据切分(Sharding)，以及切分后对数据的定位

数据切分本质：数据切分实际上就是将数据分散存储到多个数据库中，使单一数据库中的数据量变小，通过扩充主机的数量缓解单一数据库的性能问题，从而达到提升数据库操作性能的目的

数据切分的两种方式：

  • 纵向(垂直)切分
  • 横向(水平)切分

##1.1 垂直切分

垂直切分的种类：

  • 垂直分库
  • 垂直分表

垂直分库：就是根据业务耦合性，将关联度低的不同表存储在不同的数据库。它类似微服务拆分，按不同的业务分类进行独立划分模块，每一个微服务模块使用单独的数据库

垂直分表：基于数据库中的"列"进行，某个字段较多，可以新建一张拓展表，将不经常或字段长度较大的字段拆分出去到拓展表中。在字段很多的情况下(如一个大表有100个字段)，通过"大表拆小表"，便于开发和维护

注：MySQL底层是通过数据页存储的，一条记录占用空间过大会导致跨页，造成额外的性能开销。另外数据库以行为单位将数据加载到内存中，这样表中字段长度较短且访问频率较高，内存能加载更多的数据，命中率更高，减少了磁盘IO

垂直切分的优点：

  • 解决业务系统层面的耦合，业务明确清晰
  • 与微服务系统治理类似，也能对不同业务数据进行分级管理、维护、监控、拓展等
  • 高并发场景下，垂直切分一定程度上提升IO、数据库连接数、单机硬件资源的瓶颈

缺点：

  • 部分表无法join，只能通过接口聚合方式解决，提高了并发的复杂度
  • 分布式事务处理复杂
  • 依然存在单表数据量过大的问题(需要进行水平拆分)

##1.2 水平拆分

当一个应用难以再细粒度的垂直切分，或者切分后数据量行数巨大，存在单库读写、存储性能瓶颈，这时候就需要进行水平切分

水平切分分类：

  • 库内分表
  • 分库分表

   这个分类根据表内数据内在的逻辑关系，将同一个表按不同条件分散到多个数据库或者多个表中，每个表中只包含一部分数据，从而使得单表数据量变小，提升性能

库内分表：在本数据库内部进行分表，只能解决单一表数据量过大的问题。因为没有将表发布到不同机器的数据库中，因此对于减轻MySQL数据库压力来说，帮助不是很大

分库分表：在生产环境中最常用的方式，将大表中的数据按照指定规则进行切分到到子表，表分布到不同的数据库中

水平切分的优点：

  • 不存在单库数据量过大、高并发的性能瓶颈，提高系统稳定性和负载能力
  • 应用端改造小，不需要拆分业务模块

缺点：

  • 跨分片的事务一致性难以保证
  • 跨库的join关联查询性能较差
  • 数据多次拓展难度和维护量极大

#2. MyCat垂直拆分

##2.1 案例场景

垂直拆分的含义：一种是按照不同的表(或者Schema)来切分到不同的数据库之上，垂直分库按照表的不同服务进行拆分，垂直分表按照表的字段进行拆分

现在一个外卖系统，它后端数据存储在单库中，现在对这个单库数据进行垂直拆分

现在按照服务区分垂直拆分成三个库：

  • 用户系统库(user)
  • 订单系统库(order)
  • 餐品系统库(cuisine)

##2.2 准备工作

准备三台服务器分别配置好MySQL并导入对应的系统库：

IP
 MySQL
MyCat
192.168.68.133
用户系统库(user)
安装配置
192.168.68.134
订单系统库(order)
无
192.168.68.151
餐品系统库(cuisine)
无
导入对应的SQL文件到数据库：不具体描述
#导入文件到Linux系统命令
rz 
#导入数据到数据库
source sql文件位置

##2.3 MyCat配置

① schema配置文件：

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

    <!--逻辑库-->
    <schema name="TAKEAWAY_DB" checkSQLschema="true" sqlMaxLimit="100">
        <!--逻辑表-->
        <table name="address_book" dataNode="dn1"  primaryKey="id" />
        <table name="employee" dataNode="dn1"  primaryKey="id" />
        <table name="user" dataNode="dn1" primaryKey="id" />

        <table name="orders" dataNode="dn2" primaryKey="id"/>
        <table name="order_detail" dataNode="dn2" primaryKey="id"/>
        
        <table name="category" dataNode="dn3" primaryKey="id"/>
        <table name="dish" dataNode="dn3" primaryKey="id"/>
        <table name="dish_flavor" dataNode="dn3" primaryKey="id"/>
        <table name="setmeal" dataNode="dn3" primaryKey="id"/>
        <table name="setmeal_dish" dataNode="dn3" primaryKey="id"/>
        <table name="shopping_cart" dataNode="dn3" primaryKey="id"/>
    </schema>


    <!--数据节点，绑定dataHost数据源和database数据库-->
    <dataNode name="dn1" dataHost="localhost1" database="user" />
    <dataNode name="dn2" dataHost="localhost2" database="order_tb" />
    <dataNode name="dn3" dataHost="localhost3" database="cuisine" />


    <dataHost name="localhost1" maxCon="1000" minCon="10" balance="0"
              writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"  slaveThreshold="100">
        <!--心跳检测-->
        <heartbeat>select user()</heartbeat>
        <!--writeHost数据写入节点，也就是数据源-->
        <writeHost host="hostM1" url="jdbc:mysql://192.168.68.133:3306" user="root"
                   password="123456789">
        </writeHost>
    </dataHost>
    
    <dataHost name="localhost2" maxCon="1000" minCon="10" balance="0"
              writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"  slaveThreshold="100">
        <heartbeat>select user()</heartbeat>
        <writeHost host="hostM1" url="jdbc:mysql://192.168.68.134:3306" user="root"
                   password="123456789">
        </writeHost>
    </dataHost>
    
    <dataHost name="localhost3" maxCon="1000" minCon="10" balance="0"
              writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"  slaveThreshold="100">
        <heartbeat>select user()</heartbeat>
        <writeHost host="hostM1" url="jdbc:mysql://192.168.68.151:3306" user="root"
                   password="123456789">
        </writeHost>
    </dataHost>
</mycat:schema>

② server.xml配置文件:

<user name="root" defaultAccount="true">
      <property name="password">123456789</property>
      <property name="schemas">TAKEAWAY_DB</property>
    
  </user>
  <user name="user">
      <property name="password">123456789</property>
      <property name="schemas">TAKEAWAY_DB</property>
      <property name="readOnly">true</property>
  </user>

启动MyCat查看TAKEAWAY_DB的表：检查是否配置成功

mysql> show databases;
+-------------+
| DATABASE    |
+-------------+
| TAKEAWAY_DB |
+-------------+
1 row in set (0.01 sec)

mysql> use TAKEAWAY_DB;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+-----------------------+
| Tables in TAKEAWAY_DB |
+-----------------------+
| address_book          |
| category              |
| dish                  |
| dish_flavor           |
| employee              |
| orders                |
| order_detail          |
| setmeal               |
| setmeal_dish          |
| shopping_cart         |
| user                  |
+-----------------------+
11 rows in set (0.01 sec)

##2.4 测试

① 分别查询user表、order表、setmeal_dish表的数据

mysql> select * from user;
Empty set (0.01 sec)

mysql> select * from orders;
Empty set (0.01 sec)

mysql> select * from setmeal_dish;
+---------------------+---------------------+---------------------+--------------+----------+--------+------+-----------------------+-----------------------+---------------------+---------------------+------------+
| id                  | setmeal_id          | dish_id             | name         | price    | copies | sort | create_time           | update_time           | create_user         | update_user         | is_deleted |
+---------------------+---------------------+---------------------+--------------+----------+--------+------+-----------------------+-----------------------+---------------------+---------------------+------------+
| 1415580119052894209 | 1415580119015145474 | 1397862198033297410 | 老火靓汤     | 49800.00 |      1 |    0 | 2021-07-15 15:52:55.0 | 2021-07-15 15:52:55.0 | 1415576781934608386 | 1415576781934608386 |          0 |
| 1415580119061282817 | 1415580119015145474 | 1413342036832100354 | 北冰洋       |   500.00 |      1 |    0 | 2021-07-15 15:52:55.0 | 2021-07-15 15:52:55.0 | 1415576781934608386 | 1415576781934608386 |          0 |
| 1415580119069671426 | 1415580119015145474 | 1413385247889891330 | 米饭         |   200.00 |      1 |    0 | 2021-07-15 15:52:55.0 | 2021-07-15 15:52:55.0 | 1415576781934608386 | 1415576781934608386 |          0 |
+---------------------+---------------------+---------------------+--------------+----------+--------+------+-----------------------+-----------------------+---------------------+---------------------+------------+
3 rows in set (0.00 sec)

② 插入数据到employee表、setmeal_dish表

mysql>  INSERT INTO `employee` VALUES ('2', '普通用户', 'KXJ', 'e10adc3949ba59abbe56e057f20f883e', '13812312312', '1', '110101199001010047', '1', '2022-7-15 17:20:07', '2022-7-15 17:20:07', '1', '1');
Query OK, 1 row affected (0.05 sec)
OK!

mysql> INSERT INTO `setmeal_dish` VALUES ('14155801191231231', '1415123312122145474', '1397862198033297410', '蛙来了', '49800.00', '1', '0', '2022-07-15 15:52:55', '2022-07-15 15:52:55', '1415576781934608386', '1415576781934608386', '0');
Query OK, 1 row affected (0.00 sec)
OK!

③ 跨库分片连接查询

mysql>  SELECT o.id,o.number,o.status,o.pay_method,o.amount,a.consignee,a.sex,a.detail FROM orders o,address_book a WHERE o.address_book_id=a.id;
ERROR 1064 (HY000): invalid route in sql, multi tables found but datanode has no intersection  sql:SELECT o.id,o.number,o.status,o.pay_method,o.amount,a.consignee,a.sex,a.detail FROM orders o,address_book a WHERE o.address_book_id=a.id

允许多表跨库查询时报错，原因是当前的SQL语句涉及到跨库的join操作

##2.5 全局表解决跨库分片查询

之前在进行orders表和address_book表进行连查时，因为order表属于order_tb库、address_book属于user库

使用全局表来解决跨库问题：把address_book设置成全局表

全局表配置步骤

  • 把需要设置的全局表导入到各个节点数据库
  • 在schema.xml进行全局表配置

① 全局表导入到各个节点数据库

mysql> source /root/user.sql
Query OK, 0 rows affected (0.00 sec)
…………
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show tables;
+--------------------+
| Tables_in_order_tb |
+--------------------+
| address_book       |
| employee           |
| order_detail       |
| orders             |
| user               |
+--------------------+
5 rows in set (0.00 sec)

② 配置：在上面的schema.xml进行一部分修改即可

<!--逻辑表-->
    <table name="address_book" dataNode="dn1,dn2,dn3" type="global"  primaryKey="id" />
    <table name="employee" dataNode="dn1,dn2,dn3" type="global" primaryKey="id" />
    <table name="user" dataNode="dn1,dn2,dn3" type="global" primaryKey="id" />

配置成功全局表之后再次进行查询： 

mysql>  SELECT o.id,o.number,o.status,o.pay_method,o.amount,a.consignee,a.sex,a.detail FROM orders o,address_book a WHERE o.address_book_id=a.id;
+------+----------------------+--------+------------+--------+-----------+------+-----------+
| id   | number               | status | pay_method | amount | consignee | sex  | detail    |
+------+----------------------+--------+------------+--------+-----------+------+-----------+
|    1 | e1398090003262255106 |      2 |          1 |  19.20 | ??        |    1 | ????????? |
+------+----------------------+--------+------------+--------+-----------+------+-----------+
1 row in set (0.01 sec)

#3. MyCat水平拆分

##3.1 案例场景

水平拆分的含义：根据表中数据的逻辑关系，将同一个表中的数据按照某种条件拆分到多台数据库(主机)上面

需求：现在有一张外卖系统的菜品表(dish)，因为数据量很大，需要进行水平拆分，将数据分别保持到三个dataNode中

 准备好三台服务器和垂直拆分是一样的

##3.2 前期准备

 
IP
MySQL
MyCat
192.168.68.133
 cuis(餐品系统库)
安装配置
192.168.68.134
cuis(餐品系统库)
无
192.168.68.151
cuis(餐品系统库)
无

① 在三个节点数据库中新建一个cuis库：

CREATE DATABASE cuis;

② 再分别在库中新建dish表： 

CREATE TABLE `dish` (
  `id` bigint(20) NOT NULL COMMENT '主键',
  `name` varchar(64) COLLATE utf8_bin NOT NULL COMMENT '菜品名称',
  `category_id` bigint(20) NOT NULL COMMENT '菜品分类id',
  `price` decimal(10,2) DEFAULT NULL COMMENT '菜品价格',
  `code` varchar(64) COLLATE utf8_bin NOT NULL COMMENT '商品码',
  `image` varchar(200) COLLATE utf8_bin NOT NULL COMMENT '图片',
  `description` varchar(400) COLLATE utf8_bin DEFAULT NULL COMMENT '描述信息',
  `status` int(11) NOT NULL DEFAULT '1' COMMENT '0 停售 1 起售',
  `sort` int(11) NOT NULL DEFAULT '0' COMMENT '顺序',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  `update_time` datetime NOT NULL COMMENT '更新时间',
  `create_user` bigint(20) NOT NULL COMMENT '创建人',
  `update_user` bigint(20) NOT NULL COMMENT '修改人',
  `is_deleted` int(11) NOT NULL DEFAULT '0' COMMENT '是否删除',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `idx_dish_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='菜品管理';

##3.3 MyCat的配置

① schema.xml配置：rule分片规则为取模

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

    <!--逻辑库-->
    <schema name="TAKEAWAY_DB" checkSQLschema="true" sqlMaxLimit="100">
        <!--逻辑表-->
        <table name="dish" dataNode="dn1,dn2,dn3" primaryKey="id" rule="mod-long"/>
    </schema>

    <!--数据节点，绑定dataHost数据源和database数据库-->
    <dataNode name="dn1" dataHost="localhost1" database="cuis" />
    <dataNode name="dn2" dataHost="localhost2" database="cuis" />
    <dataNode name="dn3" dataHost="localhost3" database="cuis" />

    <dataHost name="localhost1" maxCon="1000" minCon="10" balance="0"
              writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"  slaveThreshold="100">
        <!--心跳检测-->
        <heartbeat>select user()</heartbeat>
        <!--writeHost数据写入节点，也就是数据源-->
        <writeHost host="hostM1" url="jdbc:mysql://192.168.68.133:3306" user="root"
                   password="123456789">
        </writeHost>
    </dataHost>
    
    <dataHost name="localhost2" maxCon="1000" minCon="10" balance="0"
              writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"  slaveThreshold="100">
        <heartbeat>select user()</heartbeat>
        <writeHost host="hostM1" url="jdbc:mysql://192.168.68.134:3306" user="root"
                   password="123456789">
        </writeHost>
    </dataHost>
    
    <dataHost name="localhost3" maxCon="1000" minCon="10" balance="0"
              writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"  slaveThreshold="100">
        <heartbeat>select user()</heartbeat>
        <writeHost host="hostM1" url="jdbc:mysql://192.168.68.151:3306" user="root"
                   password="123456789">
        </writeHost>
    </dataHost>
</mycat:schema>
② server.xml配置：添加用户
<user name="root" defaultAccount="true">
        <property name="password">123456789</property>
        <property name="schemas">TAKEAWAY_DB</property>
    </user>
    <user name="user">
        <property name="password">123456789</property>
        <property name="schemas">TAKEAWAY_DB</property>
        <property name="readOnly">true</property>
    </user>

##3.4 测试

启动MyCat客户端并插入10条数据：

INSERT INTO `dish`(id,name,category_id,price,code,image,description,status,sort,create_time,update_time,create_user,update_user,is_deleted) VALUES ('1', '毛氏红烧肉', '1397844263642378242', '6800.00', '123412341234', '0a3b3288-3446-4420-bbff-f263d0c02d8e.jpg', '毛氏红烧肉毛氏红烧肉，确定不来一份？', '1', '0', '2021-05-27 09:40:19', '2021-05-27 09:40:19', '1', '1', '0');
INSERT INTO `dish`(id,name,category_id,price,code,image,description,status,sort,create_time,update_time,create_user,update_user,is_deleted) VALUES ('2', '组庵鱼翅', '1397844263642378242', '4800.00', '123412341234', '740c79ce-af29-41b8-b78d-5f49c96e38c4.jpg', '组庵鱼翅，看图足以表明好吃程度', '1', '0', '2021-05-27 09:41:19', '2021-05-27 09:41:19', '1', '1', '0');
INSERT INTO `dish`(id,name,category_id,price,code,image,description,status,sort,create_time,update_time,create_user,update_user,is_deleted) VALUES ('3', '霸王别姬', '1397844263642378242', '12800.00', '123412341234', '057dd338-e487-4bbc-a74c-0384c44a9ca3.jpg', '还有什么比霸王别姬更美味的呢？', '1', '0', '2021-05-27 09:43:08', '2021-05-27 09:43:08', '1', '1', '0');
INSERT INTO `dish`(id,name,category_id,price,code,image,description,status,sort,create_time,update_time,create_user,update_user,is_deleted) VALUES ('4', '全家福', '1397844263642378242', '11800.00', '23412341234', 'a53a4e6a-3b83-4044-87f9-9d49b30a8fdc.jpg', '别光吃肉啦，来份全家福吧，让你长寿又美味', '1', '0', '2021-05-27 09:44:08', '2021-05-27 09:44:08', '1', '1', '0');
INSERT INTO `dish`(id,name,category_id,price,code,image,description,status,sort,create_time,update_time,create_user,update_user,is_deleted) VALUES ('5', '邵阳猪血丸子', '1397844263642378242', '13800.00', '1246812345678', '2a50628e-7758-4c51-9fbb-d37c61cdacad.jpg', '看，美味不？来嘛来嘛，这才是最爱吖', '1', '0', '2021-05-27 09:45:12', '2021-05-27 09:45:12', '1', '1', '0');
INSERT INTO `dish`(id,name,category_id,price,code,image,description,status,sort,create_time,update_time,create_user,update_user,is_deleted) VALUES ('6', '口味蛇', '1397844263642378242', '16800.00', '1234567812345678', '0f4bd884-dc9c-4cf9-b59e-7d5958fec3dd.jpg', '爬行界的扛把子，东兴-口味蛇，让你欲罢不能', '1', '0', '2021-05-27 09:46:23', '2021-05-27 09:46:23', '1', '1', '0');
INSERT INTO `dish`(id,name,category_id,price,code,image,description,status,sort,create_time,update_time,create_user,update_user,is_deleted) VALUES ('7', '辣子鸡丁', '1397844303408574465', '8800.00', '2346812468', 'ef2b73f2-75d1-4d3a-beea-22da0e1421bd.jpg', '辣子鸡丁，辣子鸡丁，永远的魂', '1', '0', '2021-05-27 09:49:16', '2021-05-27 09:49:16', '1', '1', '0');
INSERT INTO `dish`(id,name,category_id,price,code,image,description,status,sort,create_time,update_time,create_user,update_user,is_deleted) VALUES ('8', '麻辣兔头', '1397844303408574465', '19800.00', '123456787654321', '2a2e9d66-b41d-4645-87bd-95f2cfeed218.jpg', '麻辣兔头的详细制作，麻辣鲜香，色泽红润，回味悠长', '1', '0', '2021-05-27 09:52:24', '2021-05-27 09:52:24', '1', '1', '0');
INSERT INTO `dish`(id,name,category_id,price,code,image,description,status,sort,create_time,update_time,create_user,update_user,is_deleted) VALUES ('9', '蒜泥白肉', '1397844303408574465', '9800.00', '1234321234321', 'd2f61d70-ac85-4529-9b74-6d9a2255c6d7.jpg', '多么的有食欲啊', '1', '0', '2021-05-27 09:54:30', '2021-05-27 09:54:30', '1', '1', '0');
INSERT INTO `dish`(id,name,category_id,price,code,image,description,status,sort,create_time,update_time,create_user,update_user,is_deleted) VALUES ('10', '鱼香肉丝', '1397844303408574465', '3800.00', '1234212321234', '8dcfda14-5712-4d28-82f7-ae905b3c2308.jpg', '鱼香肉丝简直就是我们童年回忆的一道经典菜，上学的时候点个鱼香肉丝盖饭坐在宿舍床上看着肥皂剧，绝了！现在完美复刻一下上学的时候感觉', '1', '0', '2021-05-27 09:55:13', '2021-05-27 09:55:13', '1', '1', '0');

结果：按照取模均匀的分布再各个存储节点

 

路漫漫其修远兮,吾将上下而求索