小强崽

large picture of the cover

MyCat分库分表

一、概述

Mycat 是什么？从定义和分类来看，它是一个开源的分布式数据库系统，是一个实现了 MySQL 协议的 Server，前端用户可以把它看作是一个数据库代理，用 MySQL 客户端工具和命令行访问，而其后端可以用 MySQL 原生（Native） 协议与多个 MySQL 服务器通信，也可以用 JDBC 协议与大多数主流数据库服务器通信，其核心功能是分表分库，即将一个大表水平分割为 N 个小表，存储在后端 MySQL 服务器里或者其他数据库里。

MyCat 是一个强大的数据库中间件，不仅仅可以用作读写分离、以及分表分库、容灾备份，而且可以用于多租户应用开发、云平台基础设施、让你的架构具备很强的适应性和灵活性，借助于即将发布的 MyCat 智能优化模块，系统的数据访问瓶颈和热点一目了然，根据这些统计分析数据，你可以自动或手工调整后端存储，将不同的表映射到不同存储引擎上，而整个应用的代码一行也不用改变。

二、应用场景

  • 单纯的读写分离，此时配置最为简单，支持读写分离，主从切换。
  • 分表分库，对于超过 1000 万的表进行分片，最大支持 1000 亿的单表分片。
  • 多租户应用，每个应用一个库，但应用程序只连接 MyCat，从而不改造程序本身，实现多租户化。
  • 报表系统，借助于 MyCat 的分表能力，处理大规模报表的统计。
  • 替代 Hbase，分析大数据。
  • 作为海量数据实时查询的一种简单有效方案，比如 100 亿条频繁查询的记录需要在 3 秒内查询出来结果，除了基于主键的查询，还可能存在范围查询或其他属性查询，此时 MyCat 可能是最简单有效的选择。

三、什么是分库分表，数据切分

简单来说，就是指通过某种特定的条件，将我们存放在同一个数据库中的数据分散存放到多个数据库（主机）上面，以达到分散单台设备负载的效果。

数据的切分（Sharding）根据其切分规则的类型，可以分为两种切分模式。一种是按照不同的表（或者 Schema）来切分到不同的数据库（主机）之上，这种切可以称之为数据的垂直（纵向）切分；另外一种则是根据表中的数据的逻辑关系，将同一个表中的数据按照某种条件拆分到多台数据库（主机）上面，这种切分称之为数据的水平（横向）切分。

垂直切分的最大特点就是规则简单，实施也更为方便，尤其适合各业务之间的耦合度非常低，相互影响很小，业务逻辑非常清晰的系统。在这种系统中，可以很容易做到将不同业务模块所使用的表分拆到不同的数据库中。根据不同的表来进行拆分，对应用程序的影响也更小，拆分规则也会比较简单清晰。

水平切分于垂直切分相比，相对来说稍微复杂一些。因为要将同一个表中的不同数据拆分到不同的数据库中，对于应用程序来说，拆分规则本身就较根据表名来拆分更为复杂，后期的数据维护也会更为复杂一些。

四、垂直切分

一个数据库由很多表的构成，每个表对应着不同的业务，垂直切分是指按照业务将表进行分类，分布到不同的数据库上面，这样也就将数据或者说压力分担到不同的库上面。

image-20200505164416962

  • 优点
    • 拆分后业务清晰，拆分规则明确
    • 系统之间整合或扩展容易
    • 数据维护简单
  • 缺点
    • 部分业务表无法 join，只能通过接口方式解决，提高了系统复杂度
    • 受每种业务不同的限制存在单库性能瓶颈，不易数据扩展跟性能提高
    • 事务处理复杂

由于垂直切分是按照业务的分类将表分散到不同的库，所以有些业务表会过于庞大，存在单库读写与存储瓶颈，所以就需要水平拆分来做解决。

五、水平切分

相对于垂直拆分，水平拆分不是将表做分类，而是按照某个字段的某种规则来分散到多个库之中，每个表中包含一部分数据。简单来说，我们可以将数据的水平切分理解为是按照数据行的切分，就是将表中的某些行切分到一个数据库，而另外的某些行又切分到其他的数据库中。

image-20200505164821581

  • 优点
    • 拆分规则抽象好， join 操作基本可以数据库做
    • 不存在单库大数据，高并发的性能瓶颈
    • 应用端改造较少
    • 提高了系统的稳定性跟负载能力
  • 缺点
    • 拆分规则难以抽象
    • 分片事务一致性难以解决
    • 数据多次扩展难度跟维护量极大
    • 跨库 join 性能较差

六、垂直与水平拆分的共同缺点

  • 引入分布式事务的问题
  • 跨节点 join 的问题
  • 跨节点合并排序分页问题
  • 多数据源管理问题

七、数据源管理方案

  • 客户端模式，在每个应用程序模块中配置管理自己需要的一个（或者多个）数据源，直接访问各个数据库，在模块内完成数据的整合。
  • 通过中间代理层来统一管理所有的数据源，后端数据库集群对前端应用程序透明。

绝大部分人在面对上面这两种解决思路的时候都会倾向于选择第二种，尤其是系统不断变得庞大复杂的时候。确实，这是一个非常正确的选择，虽然短期内需要付出的成本可能会相对更大一些，但是对整个系统的扩展性来说，是非常有帮助的。

八、MyCat使用

docker-compose启动三台数据库

# 默认的身份认证插件
--default-authentication-plugin=mysql_native_password
# 指定默认编码格式
--character-set-server=utf8mb4
# 指定默认的排序规则
--collation-server=utf8mb4_general_ci
# MySQL版本高于5.6.6，关于timestamp为null的时候，可能会报错，修改为ture
--explicit_defaults_for_timestamp=true
# 不区分大小写
--lower_case_table_names=1

version: "3.0"
services:
  mysql-1:
    image: mysql:5.7
    ports:
      - "3307:3306"
    container_name: mysql-1
    command:
      --default-authentication-plugin=mysql_native_password
      --character-set-server=utf8mb4
      --collation-server=utf8mb4_general_ci
      --explicit_defaults_for_timestamp=true
      --lower_case_table_names=1
    volumes:
      - "./mysql-1:/var/lib/mysql"
    environment:
      - MYSQL_ROOT_PASSWORD=123456
  mysql-2:
    image: mysql:5.7
    ports:
      - "3308:3306"
    container_name: mysql-2
    command:
      --default-authentication-plugin=mysql_native_password
      --character-set-server=utf8mb4
      --collation-server=utf8mb4_general_ci
      --explicit_defaults_for_timestamp=true
      --lower_case_table_names=1
    volumes:
      - "./mysql-2:/var/lib/mysql"
    environment:
      - MYSQL_ROOT_PASSWORD=123456
  mysql-3:
    image: mysql:5.7
    ports:
      - "3309:3306"
    container_name: mysql-3
    command:
      --default-authentication-plugin=mysql_native_password
      --character-set-server=utf8mb4
      --collation-server=utf8mb4_general_ci
      --explicit_defaults_for_timestamp=true
      --lower_case_table_names=1
    volumes:
      - "./mysql-3:/var/lib/mysql"
    environment:
      - MYSQL_ROOT_PASSWORD=123456

image-20200505171613841

下载MyCat数据库中间件

git clone https://github.com/dekuan/docker.mycat.git

image-20200505172723044

构建

cd docker.mycat
docker-compose build

配置

vim config/mycat/server.xml

修改mycat的数据库用户名，密码，数据库

image-20200505180716569

修改分库分表配置

vim config/mycat/schema.xml

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

        <!-- 配置数据库表tb-admin，数据库节点dataNode1,dataNode2,dataNode3，分片规则auto-sharding-long  -->
        <schema name="mycat" checkSQLschema="true" sqlMaxLimit="100">

                <table name="tb_admin" dataNode="dataNode1,dataNode2,dataNode3" rule="auto-sharding-long" />
        </schema>

        <!-- 配置数据库节点指定的数据库  -->
        <dataNode name="dataNode1" dataHost="dataHost1" database="mycat_1" />
        <dataNode name="dataNode2" dataHost="dataHost2" database="mycat_2" />
        <dataNode name="dataNode3" dataHost="dataHost3" database="mycat_3" />

        <!-- 各个数据库的详细配置  -->
        <dataHost name="dataHost1" maxCon="1000" minCon="10" balance="0"
                          writeType="0" dbType="mysql" dbDriver="jdbc" switchType="-1" slaveThreshold="100">
                <heartbeat>select user()</heartbeat>

                <writeHost
                                host="192.168.17.101"
                                url="jdbc:mysql://192.168.17.101:3307/mycat_1?useSSL=false&amp;serverTimezone=UTC&amp;characterEncoding=utf8"
                                user="root" password="123456">

                </writeHost>
        </dataHost>
        <dataHost name="dataHost2" maxCon="1000" minCon="10" balance="0"
                          writeType="0" dbType="mysql" dbDriver="jdbc" switchType="-1" slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <writeHost
                                host="192.168.17.101"
                                url="jdbc:mysql://192.168.17.101:3308/mycat_2?useSSL=false&amp;serverTimezone=UTC&amp;characterEncoding=utf8"
                                user="root" password="123456">

                </writeHost>
        </dataHost>
        <dataHost name="dataHost3" maxCon="1000" minCon="10" balance="0"
                          writeType="0" dbType="mysql" dbDriver="jdbc" switchType="-1" slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <writeHost
                                host="192.168.17.101"
                                url="jdbc:mysql://192.168.17.101:3309/mycat_3?useSSL=false&amp;serverTimezone=UTC&amp;characterEncoding=utf8"
                                user="root" password="123456">

                </writeHost>
        </dataHost>

</mycat:schema>

image-20200510012606386

查看分片规则

vim config/mycat/rule.xml

分别查看第 32 行和第 105 行

<tableRule name="auto-sharding-long">
    <rule>
        
        <columns>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>
</function>

自定义分片规则

vim config/mycat/autopartition-long.txt

# range start-end ,data node index
# K=1000,M=10000.
# ID 0-5000000 保存在 dataNode1
0-500M=0
# ID 5000000-10000000 保存在 dataNode2
500M-1000M=1
# ID 10000000-15000000 保存在 dataNode3
1000M-1500M=2

启动

docker-compose up -d

九、 验证是否成功

连接mycat以及三台mysql

数据库地址端口数据库名
mycat192.168.17.1018066mycat
mysql-1192.168.17.1013307mycat_1
mysql-2192.168.17.1013308mycat_2
mysql-3192.168.17.1013309mycat_3

image-20200510013950623

建表语句

create table tb_admin (id int not null primary key,name varchar(100),sharding_id int not null);

测试数据

insert into tb_admin(id, name,sharding_id) values(1000000, 'lixiaohong', 0);
insert into tb_admin(id, name,sharding_id) values(6000000, 'lixiaolu', 1);
insert into tb_admin(id, name,sharding_id) values(7000000, 'pgone', 1);
insert into tb_admin(id, name,sharding_id) values(11000000, 'jianailiang', 2);

测试结果

  • id 为 1000000 的数据应该写入 dataNode1 即 myshop_1.tb_admin 表中

  • id 为 6000000 的数据应该写入 dataNode2 即 myshop_2.tb_admin 表中

  • id 为 7000000 的数据应该写入 dataNode2 即 myshop_2.tb_admin 表中

  • id 为 11000000 的数据应该写入 dataNode3 即 myshop_3.tb_admin 表中

    image-20200510014401836

如不正确，请检查配置重新测试。

site logo

小网馆

© 2024 小强崽