关于mysql:MySQL-数据库集群PXC-方案四

8次阅读

共计 16034 个字符，预计需要花费 41 分钟才能阅读完成。

尽管之前咱们的数据是扩散在不同的分片中，然而与日俱增分片中的数据越来越多，数据迁徙的老本就大大提高，所以能不能将数据进行拆散。

咱们能够将很少应用到的数据，从分片中归档到归档数据库中。

因为 InnoDB 自身应用的是 BTree 索引，正因为如此，每次写入都须要用 IO 进行索引树的重排。特地是当数据量特地大的时候，效率并不够高。

TokuDB 是一个反对事务的“新”引擎，有着杰出的数据压缩性能，由美国 TokuTek 公司（当初曾经被 Percona 公司收买）研发。领有杰出的数据压缩性能，如果咱们的数据 写多读少，而且数据量比拟大，咱们就能够应用 TokuDB，以节俭空间老本，并大幅度降低存储使用量和 IOPS 开销，不过相应的会减少 CPU 的压力。

特点：

高压缩比，高写入性能，（能够达到压缩比 1：12，写入速度是 InnoDB 的 9~20 倍）
在线创立索引和字段
反对事务
反对主从同步

在之前的文章（一）中独自装置过 percona 数据库，咱们当初不再重新安装 Percona 数据库。

yum install -y jemalloc

vim /etc/my.cnf

在 mysqld_safe 节点下减少 malloc-lib。

……
[mysqld_safe]
malloc-lib=/usr/lib64/libjemalloc.so.1
……

而后启动 MySQL 服务。

systemctl restart mysqld

为了保障 TokuDB 的写入性能，咱们须要敞开 linux 零碎的大页内存治理，默认状况下 linux 的大页内存治理是在系统启动时事后分配内存，零碎运行时不再扭转了。

echo never > /sys/kernel/mm/transparent_hugepage/enabled
echo never > /sys/kernel/mm/transparent_hugepage/defrag

版本必须和 Percona 的版本统一，咱们后面装置的是 Percona5.7，所以此处也须要装置 toku5.7，否则提醒版本抵触。

yum install -y Percona-Server-tokudb-57.x86_64

输出 Mysql 的 root 帐号密码，实现启动。

ps-admin --enable -uroot -p

启动实现之后重启一下 mysql

systemctl restart mysqld

重启之后再激活一次 tokudb，从新执行一下命令

ps-admin --enable -uroot -p

进入 MySQL：

mysql -u root -p

执行 show engines;

show engines;

胜利之后，另一台虚拟机也是同样步骤。

如果是 sql 语句建表, 只须要在语句的结尾加上ENGINE = TokuDB，留神只能应用 sql 语句创立表才无效。

CREATE TABLE student(.........) ENGINE = TokuDB;

咱们选用两个 Percona 数据库节点组成 Replication 集群，这两个节点配置成 双向同步 ，因为 Replication 集群的主从同步是单向的，如果配置成单向的主从同步，主库挂掉当前，咱们还能够向从库写入数据，然而主库复原之后主库是不会像从库那同步数据的，所以两个节点的数据不统一，如果咱们配置成双向同步，无论哪一个节点宕机了，在上线的时候他都会从其余的节点同步数据。这就能够保障每一个节点的数据是统一的。当然这个一致性是 弱一致性，跟 PXC 集群的强一致性有本质区别的。

因为曾经启动了 8 台虚拟机了，为了节俭硬件资源，每个 PXC 节点我只启动一个 PXC 节点，和一个 MyCat 实例，这样就只有三台虚拟机同时运行。

另外别忘记了，在 MyCat 的配置文件中须要将 balance=0，而后将用不到的 PXC 节点删除掉。

vim schema.xml

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
    <!-- 配置数据表 -->
    <schema name="test" checkSQLschema="false" sqlMaxLimit="100">
        <table name="t_test" dataNode="dn1,dn2" rule="mod-long" />
        <table name="t_user" dataNode="dn1,dn2" rule="mod-long" />
        <table name="t_customer" dataNode="dn1,dn2" rule="sharding-customer">
                <childTable name="t_orders" primaryKey="ID" joinKey="customer_id" parentKey="id"/>
        </table>
    </schema>
    <!-- 配置分片关系 -->
    <dataNode name="dn1" dataHost="cluster1" database="test" />
    <dataNode name="dn2" dataHost="cluster2" database="test" />
    <!-- 配置连贯信息 -->
    <dataHost name="cluster1" maxCon="1000" minCon="10" balance="0"
                writeType="1" dbType="mysql" dbDriver="native" switchType="1"
                slaveThreshold="100">
        <heartbeat>select user()</heartbeat>
        <writeHost host="W1" url="192.168.3.137:3306" user="admin"
                     password="Abc_123456">
        </writeHost>
    </dataHost>
    <dataHost name="cluster2" maxCon="1000" minCon="10" balance="0"
                writeType="1" dbType="mysql" dbDriver="native" switchType="1"
                slaveThreshold="100">
        <heartbeat>select user()</heartbeat>
        <writeHost host="W1" url="192.168.3.141:3306" user="admin"
                   password="Abc_123456">
        </writeHost>
    </dataHost>
</mycat:schema>

最初重启 MyCat。

./mycat start

当咱们在 Master 节点写入数据，Master 会把这次操作会记录到 binlog 日志里边，Slave 节点会有专门的线程去申请 Master 发送 binlog 日志，而后 Slave 节点上的线程会把收到的 Master 日志记录在本地 realy_log 日志文件中，slave 节点通过执行 realy_log 日志来实现数据的同步，最初把执行过的操作写到本地的 binlog 日志里。

通过上图咱们能总结出 Replication 集群的数据同步是单向的，咱们在 Master 上写入数据，在 slave 上能够同步到这些数据，然而反过来却不行，所以要实现 双向同步 ， 两个数据库节点互为主从关系 才行。

咱们给两个节点的数据库都创立上一个同步数据的账户。

CREATE USER 'backup'@'%' IDENTIFIED BY 'Abc_123456' ;

GRANT super, reload, replication slave ON *.* TO 'backup'@'%' ;

FLUSH  PRIVILEGES ;

vim /etc/my.cnf

[mysqld]
server_id = 101
log_bin = mysql_bin
relay_log = relay_bin
……

[mysqld]
server_id = 102
log_bin = mysql_bin
relay_log = relay_bin

systemctl restart mysqld

我的 A 节点为：192.168.3.151

B 节点为：192.168.3.152

咱们先在 B 节点上敞开主从同步的服务。

# 敞开同步服务
stop slave;
#设置同步的 Master 节点
change master to master_host="192.168.3.151",master_port=3306,master_user="backup",
master_password="Abc_123456";
#启动同步服务
start slave;
#查看同步状态
show slave status\G;

如果看到下图 Slave_IO_Running 和 Slave_SQL_Running都为 yes 即阐明配置胜利。

而后咱们去 A 节点进行上述配置，将 master_host="192.168.3.152" 改为 152 即可。这样咱们就实现了 双向同步。

因为是双向同步，咱们在哪一个节点创立归档表，另一个节点都会同步到数据。

CREATE DATABASE test;
use test;
CREATE TABLE t_purchase_202011 (
    id INT UNSIGNED PRIMARY KEY,
    purchase_price DECIMAL(10,2) NOT NULL,
    purchase_num INT UNSIGNED NOT NULL,
    purchase_sum DECIMAL (10,2) NOT NULL,
    purchase_buyer INT UNSIGNED NOT NULL,
    purchase_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    company_id INT UNSIGNED NOT NULL,
    goods_id INT UNSIGNED NOT NULL,
    KEY idx_company_id(company_id),
    KEY idx_goods_id(goods_id)
)engine=TokuDB;

之前文章曾经装置过 Haproxy 了，这里就不在啰嗦了。两个节点一样的步骤。

yum install -y haproxy

批改配置文件：

vim /etc/haproxy/haproxy.cfg

global
    log         127.0.0.1 local2
    chroot      /var/lib/haproxy
    pidfile     /var/run/haproxy.pid
    maxconn     4000
    user        haproxy
    group       haproxy
    daemon
    # turn on stats unix socket
    stats socket /var/lib/haproxy/stats

defaults
    mode                    http
    log                     global
    option                  httplog
    option                  dontlognull
    option http-server-close
    option forwardfor       except 127.0.0.0/8
    option                  redispatch
    retries                 3
    timeout http-request    10s
    timeout queue           1m
    timeout connect         10s
    timeout client          1m
    timeout server          1m
    timeout http-keep-alive 10s
    timeout check           10s
    maxconn                 3000

listen   admin_stats
    bind    0.0.0.0:4001
    mode  http
    stats uri       /dbs
    stats realm  Global\ statistics
    stats auth    admin:abc123456
listen   proxy-mysql
    bind    0.0.0.0:4002
    mode  tcp
    balance  roundrobin
    #日志格局
    option  tcplog
    server   backup_1  192.168.3.151:3306  check  port  3306  maxconn  2000
    server   backup_2  192.168.3.152:3306  check  port  3306  maxconn  2000
    #应用 keepalive 检测死链
    option  tcpka

开启防火墙的 VRRP 协定，开启 4001 端口和 4002 端口。

firewall-cmd --direct --permanent --add-rule ipv4 filter INPUT 0 --protocol vrrp -j ACCEPT
firewall-cmd --zone=public --add-port=4001/tcp --permanent
firewall-cmd --zone=public --add-port=4002/tcp --permanent
firewall-cmd --reload

启动 Haproxy

service haproxy start

浏览器拜访如下地址即可拜访：

http://192.168.3.151:4001/dbs

如下图：即阐明胜利，两个节点都看一下。

还是在两台节点上一样的操作。

yum install -y keepalived

编辑配置文件：

vim /etc/keepalived/keepalived.conf

vrrp_instance VI_1 {
    state MASTER
    interface enp0s3
    virtual_router_id 51
    priority 100
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 123456
    }
    virtual_ipaddress {192.168.3.177}
}

配置阐明：

state MASTER：定义节点角色为 master，当角色为 master 时，该节点无需争抢就能获取到 VIP。集群内容许有多个 master，当存在多个 master 时，master 之间就须要争抢 VIP。为其余角色时，只有 master 下线能力获取到 VIP
interface enp0s3：定义可用于内部通信的网卡名称，网卡名称能够通过 ip addr 命令查看
virtual_router_id 51：定义虚构路由的 id，取值在 0-255，每个节点的值须要惟一，也就是不能配置成一样的
priority 100：定义权重，权重越高就越优先获取到 VIP
advert_int 1：定义检测间隔时间为 1 秒
authentication：定义心跳查看时所应用的认证信息
- auth_type PASS：定义认证类型为明码
- auth_pass 123456：定义具体的明码
virtual_ipaddress：定义虚构 IP（VIP），须要为同一网段下的 IP，并且每个节点须要统一

实现以上配置后，启动 keepalived 服务：

service keepalived start

咱们 ping 一下咱们的虚拟地址试试，也是 OK 的！

应用数据库连贯工具测试，也是 OK 的！

当初即便哪一台挂掉，都不会影响高可用。上一篇文章中曾经演示了，这里就不再演示了。

咱们在 PXC 的两个分片中，创立一个进货表，留神这里创立进货表时没指定 tokuDB 引擎。（如果不晓得两个 PXC 分片的话查看之前文章）

use test;
CREATE TABLE t_purchase (
      id INT UNSIGNED PRIMARY KEY,
      purchase_price DECIMAL(10,2) NOT NULL,
      purchase_num INT UNSIGNED NOT NULL,
      purchase_sum DECIMAL (10,2) NOT NULL,
      purchase_buyer INT UNSIGNED NOT NULL,
      purchase_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
      company_id INT UNSIGNED NOT NULL,
      goods_id INT UNSIGNED NOT NULL,
      KEY idx_company_id(company_id),
      KEY idx_goods_id(goods_id)
  )

减少了表之后，咱们须要在 MyCat 的配置文件中减少该表：

<table name="t_purchase" dataNode="dn1,dn2" rule="mod-long" />

而后重启 MyCat。

./mycat restart

而后我应用 Java 代码来进行批量插入数据，这次没有应用 LoadData，大家能够比照一下 10 万条数据，和 1000 万条数据的时间差。

/**
 * @author 又坏又迷人
 * 公众号: Java 菜鸟程序员
 * @date 2020/11/27
 * @Description:
 */
public class InsertDB {public static void main(String[] args) throws SQLException {DriverManager.registerDriver(new Driver());
        String url = "jdbc:mysql://192.168.3.146:8066/test";
        String username = "admin";
        String password = "Abc_123456";
        Connection connection = DriverManager.getConnection(url, username, password);
        String sql = "insert into t_purchase(id, purchase_price, purchase_num, purchase_sum, purchase_buyer, purchase_date, company_id, goods_id)" +
                "VALUES (?,?,?,?,?,?,?,?)";
        connection.setAutoCommit(false);
        PreparedStatement pst = connection.prepareStatement(sql);
        for (int i = 0; i < 100000; i++) {pst.setObject(1, i);
            pst.setObject(2, 5.0);
            pst.setObject(3, 100);
            pst.setObject(4, 500.0);
            pst.setObject(5, 12);
            pst.setObject(6, "2020-11-27");
            pst.setObject(7, 20);
            pst.setObject(8, 9);
            pst.addBatch();
            if (i % 2000 == 0) {pst.executeBatch();
                connection.commit();}
        }
        pst.close();
        connection.close();
        System.out.println("执行完结");

    }
}

Percona 公司为咱们提供了一套十分便捷的工具包 Percona-Toolkit，这个工具包蕴含了用于数据归档的pt-archiver, 用这个归档工具咱们能够轻松的实现数据的归档。

pt-archiver 的用处

导出线上数据，到线下数据作解决
清理过期数据，并把数据归档到本地归档表中，或者远端归档服务器

下载：

yum install -y percona-toolkit --nogpgcheck

pt-archiver --source h=192.168.3.146,P=8066,u=admin,p=Abc_123456,D=test,t=t_purchase --dest h=192.168.3.188,P=3306,u=root,p=123456,D=test,t=t_purchase_202011 --no-check-charset --where 'purchase_date="2020-11-27 00:00:00"' --progress 5000 --bulk-delete --bulk-insert --limit=10000 --statistics

–source h=192.168.3.146, P=8066, u=admin, p=Abc_123456, D=test, t=t_purchase 代表的是取哪个服务器的哪个数据库的哪张表的
–dest h=192.168.3.188, P=3306, u=root, p=123456, D=test, t=t_purchase_202011 代表的是归档库的连贯信息
–no-check-charset 代表的是归档过程中咱们不检查数据的字符集
–where 归档数据的判断条件
–progress 5000 每归档 5000 条数据往控制台打印一下状态信息
–bulk-delete 批量的删除归档数据
–bulk-insert 批量的新增归档数据
limit=10000 批量一万条数据进行一次归档
–statistics 打印归档的统计信息

执行实现后，能够看到在 MyCat 节点进行查问曾经没有数据了。

在咱们的 ToKuDB 的 Haproxy 虚构节点上查问能够看到数据曾经都过去了。

小结

应用 TokuDB 引擎保留归档数据，领有高速写入个性
应用双机热备计划搭建归档库，具备高可用性
应用 pt-archiver 执行归档数据，简便易行

表分区就是依照特定的规定，将数据分成许多小块，存储在磁盘中不同的区域，通过晋升磁盘 IO 能力来放慢查问的速度。

分区不会更改数据表的构造，发生变化的只是存储形式。从逻辑上看还是一张表，但底层是由多个物理分区来组成的。咱们能够依照不同的形式来进行切分。

长处：

表分区的数据能够散布在不同的物理设施上，从而高效地利用多个硬件设施
单表能够存储更多的数据
数据写入和读取的效率进步，汇总函数计算速度也变快了
不会呈现表锁，只会锁住相干分区

毛病：

不反对存储过程、存储函数和某些非凡函数
不反对按位运算符
分区键不能子查问
创立分区后，尽量不要批改数据库模式

当热数据过多的状况下，咱们减少一个 PXC 分片，就要破费三台服务器。而且还要数据迁徙。

如果咱们应用表分区的话，咱们能够给每个分区减少一个硬盘。这样咱们就能够用大量的分片就能够存储更多的热数据。

这里咱们还是应用每个 PXC 集群中一个节点。

给虚拟机减少两个硬盘这里就不再演示了，依据不同的虚拟机进行配置就好。

实现后执行命令能够看到，三块硬盘曾经被辨认。

fdisk -l

而后咱们进行分区

fdisk /dev/sdb

n：创立新分区
d：删除分区
p：列出分区表
w：把分区表写入硬盘并退出
q：退出而不保留

而后依据图中的步骤即可：

而后格式化分区

mkfs -t ext4 /dev/sdb1

接下来批改文件进行挂载。

vim /etc/fstab

最上面追加内容：

/dev/sdb1/  /mnt/p0   ext4  defaults 0 0

执行实现后进行重启

reboot

查看是否挂载胜利。

没有问题，之后咱们就把数据存储在 data 文件夹中。

之后再创立第三块硬盘分区

fdisk /dev/sdc

而后格式化分区

mkfs -t ext4 /dev/sdc1

接下来批改文件进行挂载。

vim /etc/fstab

最上面追加内容：

/dev/sdc1/  /mnt/p1   ext4  defaults 0 0

执行实现后进行重启

reboot

查看是否挂载胜利。

cd /mnt/p1
mkdir data

这样第一个 PXC 节点的就实现了，另外一台 PXC 节点也是一样的步骤。就不再演示了。

实现后调配权限在两台节点上。

chown -R mysql:mysql /mnt/p0/data
chown -R mysql:mysql /mnt/p1/data

vim /etc/my.cnf

设置为宽容模式，还有一种模式就是严格模式：DISABLED

pxc_strict_mode=PERMISSIVE

RANGE：依据间断区间值切分数据
LIST：依据枚举值切分数据
HASH：对整数求模切分数据
KEY：对任何数据类型求模切分数据

Range 分区是依照主键值范畴进行切分的，比方有 4 千万条数据。

依据上面语句咱们创立分区别，依据 ID 进行切分，p0 名字是能够轻易起的，切分规定就是小于 1 千万条的数据切分到 p0 中，1 千万到 2 千万条在 p1 中，以此类推。

CREATE TABLE t_range_1(
id INT UNSIGNED PRIMARY KEY ,
name VARCHAR(200) NOT NULL
)
PARTITION BY RANGE(ID)(PARTITION p0 VALUES LESS THAN (10000000),
PARTITION p1 VALUES LESS THAN (20000000),
PARTITION p2 VALUES LESS THAN (30000000),
PARTITION p3 VALUES LESS THAN (40000000)
);

最初咱们在查问的时候能够依据分区进行查问，就会提高效率。

SELECT * FROM t_range_1 PARTITION(p0);

因为 MySQL 只反对整数类型切分，如果想应用日期类型的话，咱们就要比方提取月份，进行分区。

须要留神的是：

分区字段必须是主键、联结主键的一部分，否则会报如下谬误：

A PRIMARY KEY must include all columns in the table's partitioning function

CREATE TABLE t_range_2(
id INT UNSIGNED ,
name VARCHAR(200) NOT NULL,
birthday DATE NOT NULL,
PRIMARY KEY(id,birthday)
)
PARTITION BY RANGE(MONTH(birthday))(PARTITION p0 VALUES LESS THAN (3),
PARTITION p1 VALUES LESS THAN (6),
PARTITION p2 VALUES LESS THAN (9),
PARTITION p3 VALUES LESS THAN (12)
);

如果咱们想把表分区映射到不同的磁盘，须要应用上面的 SQL，p0 和 p1 就是方才咱们创立的分区。

CREATE TABLE t_range_2(
id INT UNSIGNED ,
name VARCHAR(200) NOT NULL,
birthday DATE NOT NULL,
PRIMARY KEY(id,birthday)
)
PARTITION BY RANGE(MONTH(birthday))(PARTITION p0 VALUES LESS THAN (6) DATA DIRECTORY='/mnt/p0/data',
PARTITION p1 VALUES LESS THAN (12) DATA DIRECTORY='/mnt/p1/data'
);

咱们在 PXC 某个节点上演示一下表分区。

use test;
CREATE TABLE t_range_2(
id INT UNSIGNED ,
name VARCHAR(200) NOT NULL,
birthday DATE NOT NULL,
PRIMARY KEY(id,birthday)
)
PARTITION BY RANGE(MONTH(birthday))(PARTITION p0 VALUES LESS THAN (6) DATA DIRECTORY='/mnt/p0/data',
PARTITION p1 VALUES LESS THAN (12) DATA DIRECTORY='/mnt/p1/data'
);

创立实现后咱们写入一些数据：

咱们去 PXC 节点的服务器上查问一下，能够看到咱们的数据曾经切分过去了。

cd /mnt/p0/data/test

而后咱们看一下 p1 的分区，也是没有问题的。

如果想查看每个分区保留了多少条数据的话，能够应用上面的 SQL：

select
    PARTITION_NAME, #分区名称
    PARTITION_METHOD,# 分区形式
    PARTITION_EXPRESSION,# 分区字段
    PARTITION_DESCRIPTION,# 分区条件
    TABLE_ROWS #数据量
from information_schema.PARTITIONS
where
    TABLE_SCHEMA = SCHEMA() and TABLE_NAME = 't_range_2';

测试实现之后，咱们将创立表的语句和分区在另一台 PXC 节点上创立一下。因为咱们是应用 MyCat 来进行切分数据，但咱们还没有规定 MyCat 切分规定，所以把咱们方才创立的两条测试数据删除掉。

实现后咱们关上 MyCat 节点虚拟机。

vim /usr/local/mycat/conf/schema.xml

减少咱们新建的表：

<table name="t_test" dataNode="dn1,dn2" rule="mod-long" />

进入 bin 目录重启 MyCat：

./mycat restart

而后咱们连贯 MyCat 节点，插入一些数据。

insert into t_range_2 (id,name,birthday) values (1,'A','2020-01-01');
insert into t_range_2 (id,name,birthday) values (2,'B','2020-10-10');

之后咱们能够看到，数据被 MyCat 求模切分到了不同的 PXC 节点，随后又进行了日期的切分存储了不同的硬盘上，由此咱们能够晓得是能够共存的。

LIST 分区和 RANGE 分区十分的类似，次要区别在于 LIST 是枚举值列表的汇合，RANGE 是间断的区间值的汇合。二者在语法方面十分的类似。同样倡议 LIST 分区列是非 null 列，否则插入 null 值如果枚举列表外面不存在 null 值会插入失败，这点和其它的分区不一样，RANGE 分区会将其作为最小分区值存储，HASH\KEY 分为会将其转换成 0 存储，次要 LIST 分区只反对整形，非整形字段须要通过函数转换成整形；5.5 版本之后能够不须要函数转换应用 LIST COLUMN 分区反对非整形字段。

咱们在第一个 PXC 节点上创立表，大体的代码还是没有太大区别，只是规定从范畴变成了固定的值。

create table t_list_1(
    id int unsigned,
    name varchar(200) not null,
    province_id int unsigned,
    primary key (id,province_id)
)
partition by list(province_id)(partition p0 values in (1,2,3,4) data directory = '/mnt/p0/data',
  partition p1 values in (5,6,7,8) data directory = '/mnt/p1/data'
);

而后在另一个 PXC 节点上创立，记得更改值。

create table t_list_1(
    id int unsigned,
    name varchar(200) not null,
    province_id int unsigned,
    primary key (id,province_id)
)
partition by list(province_id)(partition p0 values in (9,10,11,12) data directory = '/mnt/p0/data',
  partition p1 values in (13,14,15,16) data directory = '/mnt/p1/data'
);

随后咱们进入 MyCat 的虚拟机，批改配置文件。

vim /usr/local/mycat/conf/rule.xml

减少如下代码：

<tableRule name="sharding-province">
  <rule>
    <columns>province_id</columns>
    <algorithm>province-hash-int</algorithm>
  </rule>
</tableRule>

<function name="province-hash-int"
                class="io.mycat.route.function.PartitionByFileMap">
                <property name="mapFile">province-hash-int.txt</property>
</function>

保留退出后咱们创立这个文件 province-hash-int.txt。这个文件创建在 mycat/conf 文件中

1=0
2=0
3=0
4=0
5=0
6=0
7=0
8=0
9=1
10=1
11=1
12=1
13=1
14=1
15=1
16=1

保留后咱们编辑 schema.xml，减少咱们新加的表：

<table name="t_list_1" dataNode="dn1,dn2" rule="sharding-province" />

随后重启 MyCat。

在 MyCat 节点上插入数据，咱们看看是什么成果：

insert into t_list_1 (id,name,province_id) values (1,'A',1);
insert into t_list_1 (id,name,province_id) values (2,'B',5);
insert into t_list_1 (id,name,province_id) values (3,'A',10);
insert into t_list_1 (id,name,province_id) values (4,'B',16);

这里我就用图解的形式了：

基于给定的分区个数，将数据调配到不同的分区，HASH 分区只能针对整数进行 HASH，对于非整形的字段只能通过表达式将其转换成整数。表达式能够是 mysql 中任意无效的函数或者表达式，对于非整形的 HASH 往表插入数据的过程中会多一步表达式的计算操作，所以不倡议应用简单的表达式这样会影响性能。

语法如下，基本上没有太大区别，只是咱们依照 2 取余数进行分区，如果等于 0 切分到 p0 如果等于 1 切分到 p1。

create table t_hash_1(
    id int unsigned primary key ,
    name varchar(200) not null,
    province_id int unsigned not null
)
partition by hash(id) partitions 2(
  partition p0 data directory = '/mnt/p0/data',
  partition p1 data directory = '/mnt/p1/data'
);

而后咱们还是进入 MyCat，编辑 schema.xml，减少咱们新加的表，切分算法还是应用咱们方才创立的。

<table name="t_hash_1" dataNode="dn1,dn2" rule="sharding-province" />

随后重启 MyCat。

咱们插入测试数据:

insert into t_hash_1 (id,name,province_id) values (1,'A',1);
insert into t_hash_1 (id,name,province_id) values (2,'B',5);

首先会依据 province_id 去进行 MyCat 切分，1-8 的 province_id 都会被存储在一个 PXC 节点上。

随后进行 Hash 分区，咱们查问一下看看是否调配到了两个硬盘上。

select
    PARTITION_NAME, #分区名称
    PARTITION_METHOD,# 分区形式
    PARTITION_EXPRESSION,# 分区字段
    PARTITION_DESCRIPTION,# 分区条件
    TABLE_ROWS #数据量
from information_schema.PARTITIONS
where
    TABLE_SCHEMA = SCHEMA() and TABLE_NAME = 't_hash_1';

执行后果也是没有问题，正确的调配到了不同的硬盘上。

KEY 分区和 HASH 分区类似，然而 KEY 分区反对除 text 和 BLOB 之外的所有数据类型的分区，而 HASH 分区只反对数字分区，KEY 分区不容许应用用户自定义的表达式进行分区，KEY 分区应用零碎提供的 HASH 函数进行分区。当表中存在主键或者惟一键时，如果创立 KEY 分区时没有指定字段零碎默认会首选主键列作为分区字列, 如果不存在主键列会抉择非空惟一键列作为分区列, 留神 惟一列作为分区列时惟一列不能为 NULL。

在两个 PXC 分片中执行 sql。

create table t_key_1(
    id int unsigned not null ,
    name varchar(200) not null,
    job varchar(200) not null ,
    primary key (id,job)
)
partition by key(job) partitions 2(
  partition p0 data directory = '/mnt/p0/data',
  partition p1 data directory = '/mnt/p1/data'
);

MyCat 配置schema.xml，减少配置：

<table name="t_key_1" dataNode="dn1,dn2" rule="mod-long" />

而后重启 MyCat。

之后咱们测试数据：

insert into t_key_1 (id,name,job) values (1,'A','管理员');
insert into t_key_1 (id,name,job) values (2,'B','保洁');
insert into t_key_1 (id,name,job) values (3,'C','网管');

执行之后，咱们 id 为 1、3 的求模切分到了一个 PXC 分片中。而后咱们执行查问看看 Key 是怎么分区的。

select
    PARTITION_NAME, #分区名称
    PARTITION_METHOD,# 分区形式
    PARTITION_EXPRESSION,# 分区字段
    PARTITION_DESCRIPTION,# 分区条件
    TABLE_ROWS #数据量
from information_schema.PARTITIONS
where
    TABLE_SCHEMA = SCHEMA() and TABLE_NAME = 't_key_1';

咱们能够看到管理员和网管都被分到了一个硬盘中。

咱们之前创立的 Range 表分区是到 4 千万，咱们想扩大的话，能够应用上面的语句：

alter table t_range_1 add partition (partition p4 values less than (50000000)
);

如果想删除表分区，能够应用上面的语句：

alter table t_range_1 drop partition p3,p4;

如果想拆分某一个区域的话能够应用上面的语句：

alter table t_range_1 reorganize partition p0 into (partition s0 values less than (5000000),
    partition s1 values less than (10000000)
)

对应的合并就是上面的语句：

alter table t_range_1 reorganize partition s0,s1 into (partition p0 values less than (10000000)
)

移除表分区，并不会失落数据，而是将数据放到主分区中。

alter table t_range_1 remove partitioning ;

对应的其余类型的分区语法都差不多，这里就不再演示了。

正文完

mysql

发表至： mysql

2020-11-28

0

关于mysql:深入了解MySQL主从复制的原理

关于mysql:1000行MySQL学习笔记

关于mysql:通过序列号Sequence零代码实现订单流水号

关于后端:MySQL错误1055

关于大数据:Data-Lakehouse-湖仓一体-到底是什么

关于mysql:MySQL-数据库集群PXC-方案四

大数据归档 - 冷热数据拆散

InnoDB 写入慢的起因

什么是 TokuDB

装置 TokuDB

装置 jemalloc 库

批改 my.cnf

开启 Linux 大页内存

装置 TokuDB

查看 TokuDB 引擎是否装置胜利

应用 TokuDB 引擎

归档库的双机热备

配置 Replication 集群

Replication 集群同步原理

创立同步账户

批改两个 TokuDB 的配置文件

重启 MySQL

配置主从同步

创立归档表

搭建 Haproxy

搭建 Keepalived

筹备归档数据

批改 MyCat 配置文件

装置归档工具

执行归档

数据分区

什么是表分区

表分区的优缺点

为什么在集群中引入表分区

挂载硬盘

PXC 节点应用表分区

表分区类型

Range 分区

List 分区

Hash 分区

Key 分区

治理表分区

治理 Range 表分区

站内搜索