介绍

  当数据库压力变大,在繁忙操作的时候数据库的性能会越来越低,为了分担数据库的读负载可以采用主从复制,读写分离的方式来解决.随着数据的增长,单一的数据库并无法负担写的负载,可以通过分库分表来解决.

  在数据库中并不是所有的表都会频繁的写入数据,也就是不会所有的表都进行分区.对于不用分片的表可以在每个分片中都存储一份相同的数据,前提是该表的数据量不大,但是一致性并不好维护需要定期检查.或者将不用分区的表存储在其他节点进行统一.但是查询效率会降低,如过要进行关联查询就需要查询两次再进行合并.

  数据库水平拆分,也就是数据库分片操作.需要注意的是数据库的分片并不是很容易实现而且难以维护,在数据库没有并发和负载并没有达到限制时不建议使用数据库分.

分区健

  分区健的选择决定了数据库分区后的性能,尽量减少跨分区的查询,例如新闻表(新闻id,记者id)如果用新闻id作为分区键,那么在查询某一记者下的所有新闻就可能会垮很多的分区查询,性能会比没分区之前还要低.如按照记者id进行分区,在查询某一记者下的所有新闻都会在一个分区上进行.所以分区建的选择也是至关重要的.

  分区键使用hash方式进行分区使各个分片中的数据尽可能的平均.

演示说明

服务器功能说明

服务器功能说明
192.168.128.136oneproxy不启用mysql,oneproxy改成3306,连接oneproxy类似相当于连接mysql
192.168.128.137mysql分表节点1在oneproxy中命名为school1,分区表后缀_0
192.168.128.138mysql分表节点2在oneproxy中命名为school2,分区表后缀_1

数据库说明

数据表功能分区健
school_student存储学校学生表,关联班级表班级id
school_class存储学校班级表,关联城市表城市id
school_city存储城市表不分区(两个节点数据一样)

软件安装

# 如果https不能下载换成http.顺便提醒我一下
wget https://file.qvbilam.xin/MySQL/oneproxy-rhel6-linux64-v5.8.1-ga.tar.gz
# 解压
tar -zxvf oneproxy-rhel6-linux64-v5.8.1-ga.tar.gz
cd /data/package/oneproxy
# vi demo.sh
# 修改ONEPROXY_HOME为当前目录/data/package/oneproxy
chmod +x demo.sh
./demo.sh
# 验证是否成功
ps -ef | grep oneproxy

配置

此处内容需要评论回复后（审核通过）方可阅读。

演示

进入oneproxy管理端

使用mysql -uadmin -pOneProxy -h127.0.0.1 -P4041进入

查看集群

-- 可通过list;查看所有命令
-- 查看服务器状态都为up
MySQL [(none)]> list backend;
+------+----------------------+-----------+--------+--------+----------+--------+---------+------+------+-------+---------+-------+---------+-------+---------+------+------+---------+
| INDX | ADDRESS              | TYPE      | STATUS | MARKUP | REQUESTS | DEGREE | GROUP   | IS_M | IS_S | MFile | MOffset | DFile | DOffset | RFile | ROffset | IO   | SQL  | Seconds |
+------+----------------------+-----------+--------+--------+----------+--------+---------+------+------+-------+---------+-------+---------+-------+---------+------+------+---------+
|    1 | 127.0.0.1:3306       | RW/Master | UP     |      0 |        0 |      0 |         | No   | No   |  NULL |    NULL |  NULL |    NULL |  NULL |    NULL | NULL | NULL |    NULL |
|    2 | 192.168.128.137:3306 | RW/Master | UP     |      1 |        0 |      0 | school1 | No   | No   |  NULL |    NULL |  NULL |    NULL |  NULL |    NULL | NULL | NULL |    NULL |
|    3 | 192.168.128.138:3306 | RW/Master | UP     |      1 |        0 |      0 | school2 | No   | No   |  NULL |    NULL |  NULL |    NULL |  NULL |    NULL | NULL | NULL |    NULL |
+------+----------------------+-----------+--------+--------+----------+--------+---------+------+------+-------+---------+-------+---------+-------+---------+------+------+---------+
3 rows in set (0.00 sec)

查看用户

-- 查看用户及库权限
MySQL [(none)]> list users;
+---------------+----------------+
| USERNAME      | DATABASE       |
+---------------+----------------+
| qvbilam_proxy | qvbilam_school |
+---------------+----------------+
1 row in set (0.00 sec)

查看分区表

-- 查看分区表
MySQL [(none)]> list tables;
+----------------+----------+------+--------+------------+----------+----------+
| TABLENAME      | KEY      | TYPE | METHOD | PARTITIONS | KEYCACHE | TEMPLATE |
+----------------+----------+------+--------+------------+----------+----------+
| school_class   | city_id  | int  | hash   |          2 |        0 | NULL     |
| school_student | class_id | int  | hash   |          2 |        0 | NULL     |
| school_city    | id       | int  | global |          2 |        0 | NULL     |
+----------------+----------+------+--------+------------+----------+----------+
3 rows in set (0.00 sec)

进入oneproxy客户端

使用mysql -uqvbilam_proxy -pqvbilam -h127.0.0.1 -P3306进入

连接必须指定端口号和地址

进入客户端

-- 可以看到连接的不是mysql而是OneProxy-Agent-5.8.1
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 106
Server version: 5.6.27 OneProxy-Agent-5.8.1 (OneXSoft)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)]>

分片表插入数据测试

同一分片插入测试,即同city_id

MySQL [(none)]> insert into school_class (class_id,city_id,class_name,create_time) values (1,1,'PHP初级班01',now()),(2,1,'PHP初级班02',now());
Query OK, 2 rows affected (0.00 sec)

不同一分片插入测试,即不同city_id

MySQL [(none)]> insert into school_class (class_id,city_id,class_name,create_time) values (3,1,'PHP中级班01',now()),(4,2,'PHP中级班02',now());
ERROR 1044 (42000): Partitioned tables should choose only one partition for DML queries!

-- 出现报错,原因不同分区的数据不能同时插入,改成逐条插入即可

MySQL [(none)]> insert into school_class (class_id,city_id,class_name,create_time) values (3,1,'PHP中级班01',now());
Query OK, 1 row affected (0.00 sec)

MySQL [(none)]> insert into school_class (class_id,city_id,class_name,create_time) values (4,2,'PHP中级班02',now());
Query OK, 1 row affected (0.00 sec)

全局表插入数据测试

MySQL [(none)]> insert into school_city (id,city_name) values (1,'北京'),(2,'杭州'),(3,'石家庄');
Query OK, 6 rows affected (0.00 sec)

验证

结果如下则表示成功:

  1. 每个节点分片表数据总数之和等于oneproxy查询总数.
  2. 根据分片健相同的数据在同一节点上.
  3. 每个分片几点的全局表数据和oneproxy全局表的数据相同,数量相同.

oneproxy客户端

使用mysql -uqvbilam_proxy -pqvbilam -h127.0.0.1 -P3306进入

班级分表查询

-- 查询总数
MySQL [(none)]> select count(*) from school_class;
+----------+
| count(*) |
+----------+
|        4 |
+----------+
1 row in set (0.00 sec)
-- 查询所有数据
MySQL [(none)]> select * from school_class order by class_id asc;
+----------+---------+----------------+---------------------+
| class_id | city_id | class_name     | create_time         |
+----------+---------+----------------+---------------------+
|        1 |       1 | PHP初级班01    | 2020-03-13 16:36:53 |
|        2 |       1 | PHP初级班02    | 2020-03-13 16:36:53 |
|        3 |       1 | PHP中级班01    | 2020-03-13 16:42:54 |
|        4 |       2 | PHP中级班02    | 2020-03-13 16:43:15 |
+----------+---------+----------------+---------------------+
4 rows in set (0.00 sec)

全局城市表查询

-- 查询总数
MySQL [(none)]> select count(*) from school_city;
+----------+
| count(*) |
+----------+
|        3 |
+----------+
1 row in set (0.00 sec)

-- 查询数据
MySQL [(none)]> select * from school_city;
+----+-----------+
| id | city_name |
+----+-----------+
|  1 | 北京      |
|  2 | 杭州      |
|  3 | 石家庄    |
+----+-----------+
3 rows in set (0.00 sec)

分片节点一

班级分表查询

MySQL [(none)]> use qvbilam_school
Database changed

-- 查询总数
MySQL [qvbilam_school]> select count(*) from school_class_0;
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

-- 查询数据
MySQL [qvbilam_school]> select * from school_class_0;
+----------+---------+----------------+---------------------+
| class_id | city_id | class_name     | create_time         |
+----------+---------+----------------+---------------------+
|        4 |       2 | PHP中级班02    | 2020-03-13 16:43:15 |
+----------+---------+----------------+---------------------+
1 row in set (0.00 sec)

全局城市表查询

MySQL [qvbilam_school]> select * from school_city;
+----+-----------+
| id | city_name |
+----+-----------+
|  1 | 北京      |
|  2 | 杭州      |
|  3 | 石家庄    |
+----+-----------+
3 rows in set (0.00 sec)

分片节点二

班级分表查询

MySQL [(none)]> use qvbilam_school
Database changed
MySQL [qvbilam_school]> select * from school_class_1;
+----------+---------+----------------+---------------------+
| class_id | city_id | class_name     | create_time         |
+----------+---------+----------------+---------------------+
|        1 |       1 | PHP初级班01    | 2020-03-13 16:36:53 |
|        2 |       1 | PHP初级班02    | 2020-03-13 16:36:53 |
|        3 |       1 | PHP中级班01    | 2020-03-13 16:42:54 |
+----------+---------+----------------+---------------------+
3 rows in set (0.00 sec)

全局城市表查询

MySQL [qvbilam_school]> select * from school_city;
+----+-----------+
| id | city_name |
+----+-----------+
|  1 | 北京      |
|  2 | 杭州      |
|  3 | 石家庄    |
+----+-----------+
3 rows in set (0.00 sec)
Last modification：March 13th, 2020 at 05:10 pm
© 允许规范转载