MySQL基础-分库分表(一)

零、本文纲要

一、分库分表

1. 单数据库问题
2. 拆分策略
3. 实现技术

二、MyCat1.6入门

1. MyCat安装
2. MyCat概念
3. MyCat入门
4. 入门案例说明

一、分库分表

1. 单数据库问题

IO瓶颈：热点数据太多，数据库缓存不足，产生大量磁盘IO，效率较低。 请求数据太多，带宽不够，网络IO瓶颈。

CPU瓶颈：排序、分组、连接查询、聚合统计等SQL会耗费大量的CPU资源，请求数太多，CPU出现瓶颈。

分库分表的中心思想都是将数据分散存储，使得单一数据库/表的数据量变小来缓解单一数据库的性能问题，从而达到提升数据库性能的目的。

2. 拆分策略

• ① 垂直拆分

垂直分库：以表为依据，根据业务将不同表拆分到不同库中。
垂直分表：以字段为依据，根据字段属性将不同字段拆分到不同表中。

• ② 水平拆分

水平分库：以字段为依据，按照一定策略，将一个库的数据拆分到多个库中。
水平分表：以字段为依据，按照一定策略，将一个表的数据拆分到多个表中。

注意：在业务系统中，为了缓解磁盘IO及CPU的性能瓶颈，到底是垂直拆分，还是水平拆分；具体是分库，还是分表，都需要根据具体的业务需求具体分析。

3. 实现技术

• ① shardingJDBC

基于AOP原理，在应用程序中对本地执行的SQL进行拦截，解析、改写、路由处理。需要自行编码配置实现，只支持java语言，性能较高。

shardingJDBC的基础使用在MySQL-主从复制&读写分离有提及。

• ② MyCat

数据库分库分表中间件，不用调整代码即可实现分库分表，支持多种语言，性能不及前者。

二、MyCat1.6入门

注意：MyCat1.6与MyCat2有着极大的不同

MyCat2官方网址 ，MyCat1.x官网也有，不过已经近乎被官方忽略了。
MyCat1.6下载官方网址

1. MyCat安装

官方安装指引：入门篇-4-7 · 语雀 (yuque.com)

首先确认Linux系统已安装JDK、MySQL，然后直接解压下载的MyCat安装包即可。 此处解压安装到 /usr/local/ 目录， 指令：tar -zxvf Mycat-server-1.6.7.3-release-20210913163959-linux.tar.gz -C /usr/local/

[root@localhost ~]# cd /usr/local/mycat/
[root@localhost mycat]# ll
总用量 12
drwxr-xr-x. 2 root root  190 5月  25 16:16 bin       #存放可执行文件，用于启动停止mycat
drwxrwxrwx. 2 root root    6 4月  15 2020 catlet     #多表联查涉及到的文件
drwxrwxrwx. 4 root root 4096 5月  25 16:16 conf      #存放mycat的配置文件
drwxr-xr-x. 2 root root 4096 5月  25 16:16 lib       #存放mycat的项目依赖包（jar）
drwxrwxrwx. 2 root root    6 8月  31 2021 logs       #存放mycat的日志文件
-rwxrwxrwx. 1 root root  227 9月  13 2021 version.txt

注意：1.6.7.3版本中的mysql的jar包版本较低，使用的是 mysql-connector-java-5.1.35.jar 。实际使用时跟据需要做替换。

2. MyCat概念

• ① 逻辑结构

逻辑库：schema
逻辑表：table(分片规则：rule)
分片节点：dataNode

• ② 物理结构

节点主机：dataHost

3. MyCat入门

案例：水平拆分 tb_order 表

• ① 配置schema.xml

配置文件在 /mycat/conf/ 目录下，下面不再说明，具体内容配置如：

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
    <!-- 
        schema：逻辑库，一个逻辑库下可以有多个逻辑表
        name：此处指定库名为DB01
    -->
    <schema name="DB01" checkSQLschema="true" sqlMaxLimit="100">
        <!-- 
            table：逻辑表
            name：此处指定表名为TB_ORDER
            rule：分片规则，具体规则在 /mycat/conf/rule.xml 文件中
        -->
        <table name="TB_ORDER" dataNode="dn1,dn2,dn3" rule="auto-sharding-long" />
    </schema>

    <!-- 
        dataNode：分片节点 
        database：指定要关联的这个节点主机的具体数据库，此处指定为db01
    -->
    <dataNode name="dn1" dataHost="dhost1" database="db01" />
    <dataNode name="dn2" dataHost="dhost2" database="db01" />
    <dataNode name="dn3" dataHost="dhost3" database="db01" />

    <!-- 
        dataHost：节点主机 
        dbDriver：native默认配置不支持高版本的mysql，此处改为jdbc
        url：修改为对应节点主机的url
    -->
    <dataHost name="dhost1" maxCon="1000" minCon="10" balance="0"
              writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"  slaveThreshold="100">
        <heartbeat>select user()</heartbeat>

        <writeHost host="master" url="jdbc:mysql://192.168.200.128:3316?useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8" user="root" password="123456" />
    </dataHost>

    <dataHost name="dhost2" maxCon="1000" minCon="10" balance="0"
              writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"  slaveThreshold="100">
        <heartbeat>select user()</heartbeat>

        <writeHost host="master" url="jdbc:mysql://192.168.200.129:3316?useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8" user="root" password="123456" />
    </dataHost>

    <dataHost name="dhost3" maxCon="1000" minCon="10" balance="0"
              writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"  slaveThreshold="100">
        <heartbeat>select user()</heartbeat>

        <writeHost host="master" url="jdbc:mysql://192.168.200.130:3316?useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8" user="root" password="123456" />
    </dataHost>

</mycat:schema>

• ② 配置server.xml

此处仅截取需要配置的部分内容，如下：

<!-- 配置用户权限 -->
<user name="root" defaultAccount="true">
    <property name="password">123456</property>
    <!-- 逻辑库为DB01 -->
    <property name="schemas">DB01</property>
</user>

<user name="user">
    <property name="password">123456</property>
    <property name="schemas">DB01</property>
    <!-- 配置user用户仅有只读权限 -->
    <property name="readOnly">true</property>
</user>

• ③ 启动测试

#启动
bin/mycat start
#停止
bin/mycat stop

Mycat启动之后，占用端口号 8066 。
通过日志查看MyCat启动情况，指令：tail -f /usr/loacl/mycat/logs/wrapper.log 。

a、连接MyCat，指令： mysql -h 192.168.200.128 -P 8066 -uroot -p123456 ；

b、数据测试

mysql > show databases;
mysql > use DB01;

-- 建表
CREATE TABLE TB_ORDER (
id BIGINT(20) NOT NULL,
title VARCHAR(100) NOT NULL ,
PRIMARY KEY (id)
) ENGINE=INNODB DEFAULT CHARSET=utf8 ;

插入数据
INSERT INTO TB_ORDER(id,title) VALUES(5000000,'goods5000000');
INSERT INTO TB_ORDER(id,title) VALUES(10000000,'goods10000000');
INSERT INTO TB_ORDER(id,title) VALUES(10000001,'goods10000001');
INSERT INTO TB_ORDER(id,title) VALUES(15000000,'goods15000000');
INSERT INTO TB_ORDER(id,title) VALUES(15000001,'goods15000001');

如果id的值在1-500w之间，数据将会存储在第一个分片数据库中。
如果id的值在500w-1000w之间，数据将会存储在第二个分片数据库中。
如果id的值在1000w-1500w之间，数据将会存储在第三个分片数据库中。
如果id的值超出1500w，在插入数据时，将会报错。

4. 入门案例说明

通过rule.xml配置文件可以查找到

<!-- 指定的分片规则auto-sharding-long -->
<tableRule name="auto-sharding-long">
    <rule>
        <columns>id</columns>
        <!-- 算法引用rang-long -->
        <algorithm>rang-long</algorithm>
    </rule>
</tableRule>


<function name="rang-long"
    class="io.mycat.route.function.AutoPartitionByLong">
    <!-- 该函数的属性配置文件autopartition-long.txt -->
    <property name="mapFile">autopartition-long.txt</property>
</function>

打开autopartition-long.txt，如下：

# range start-end ,data node index
# K=1000,M=10000.
0-500M=0
500M-1000M=1
1000M-1500M=2

可以清楚看到该规则下，各个id数据落到对应的那个节点主机。而超过的id数据不在配置范围内，故而报错。

三、结尾

以上即为MySQL基础-分库分表(一)的全部内容，感谢阅读。