前言
为什么要分库分表?
单个库数据容量太大,单个DBServer存储空间不够
单个库表太多,查询的时候,打开表操作也消耗系统资源
单个表容量太大,查询的时候,扫描行数过多,磁盘IO大,查询缓慢
单个库能承载的访问量有限,再高的访问量只能通过分库分表实现
关系型数据库分库分表目前就两种方案: 代理、客户端。
代理就不需要改动前端业务代码,客户端呢就需要改动前端代码进行数据库的分片实现。
一、安装
1.1、上传此压缩包到服务器,解压到/usr/local/目录下。
oneproxy-rhel5-linux64-v6.0.0-ga.tar.gz
1.2、将demo.sh和oneproxy.service脚本里的目录改成自行设置的安装目录,这边以/usr/local
1.3、创建调度的数据库账号:
grant all privileges on mydb3.* to 'proxy'@'%' identified by '123456' WITH grant option;
flush privileges
1.4、增加权限 并启动
cd /usr/local/oneproxy/
chmod +x demo.sh
./demo.sh
1.5、 确认oneproxy监听端口3307和工具管理端口4041监听正常
1.6、对用户名的密码进行加密设置
./mysqlpwd 123456
9D7E55EAF8912CCBF32069443FAC452794F8941B
1.7、编辑OneProxy的配置文件
vi /usr/local/oneproxy/conf/proxy.conf
1.8、重启oneproxy使配置生效,连接oneproxy
./oneproxy.service restart
也可以通过log查看
cd /usr/local/oneproxy/log
cat oneproxy.log 来查看启动是否成功过!
1.9、登陆
mysql -uproxy -p123456 -P3307 -h127.0.0.1 default -c
1.10、验证负载均衡
mysql -h192.168.1.59 -uproxy -p123456 -P3307 mydb3 -e"select * from employee; select @@server_id;"
无需安装的轻量级数据库分库分表中间件:
http://blog.csdn.net/jerry____wang/article/details/52981745
二、OneProxy---单库水平分表
分库分表的难点:聚合
360 Altas是不支持的,参考官网:
但Onproxy是可以支持以上这些语句的。
三、OneProxy中间件注意事项
这里总结一下JAVA程序mybatis框架连接后的注意事项:
1、不支持 Server Side Cursor 接口,比如 MySQL C API 里的 Prepare、Bind、 Execute调用接口
2、不支持 use命令切换数据库
3、默认禁止 CALL, PREPARE, EXECUTE, DEALLOCATE 命令,也就是说不能用存储过程
4、单库(单实例)分表--insert/update/delete要加字段名,如insert into t1(id,name) values(1,'张三');
5、单库(单实例)分表--目前分了N张表,如果以自增id做关联查询,那么每张表的 自增id都是从1开始,在与其他表join关联查询时,数据会不准确
6、单库(单实例)分表--当where条件有分区列时,值不能有函数转换,也不能有算 术表达式,必须是原子值,否则结果不准确 。
7、分库分表(多实例)--不支持垮库join,例如user_0表在10.0.0.1机器里,现在要 join关联查询10.0.0.2机器里的money_detail表,不支持
8、分库分表(多实例)--不支持分布式事务,例如user_0表在10.0.0.1机器里,us er_1表在10.0.0.2机器里,现在想同时update更新两张表,不支持
9、读写分离 --默认读操作全部访问slave,如果想强制走主库,例如涉及金钱类的查 询操作,SQL改为select /*master*/ from t1 where id=1;
10、分库分表/分表 --where条件带分区列时,直接命中该表,如果未带分区列,会 逐一扫描所有分表(单线程),考虑性能问题,要加并行查询(多线程),SQL改为 select /*parallel*/ from t1 where name='李四'; 并行查询会增加额外的CPU消耗
11、二级分区的适用场景:先对订单号取模分1024张表,等里面的表数据达到1000万 行,再range扩容, 例如对时间字段, 分区表只保留3个月的订单,历史订单归档。
四、开始水平分表
环境准备
机器名 |
IP地址 |
配置 |
MHA(pxoxy) |
192.168.1.59 |
1 Core,GB Memory,VMWARE虚拟机 |
master |
192.168.1.60 |
1 Core,GB Memory,VMWARE虚拟机 |
slave1 |
192.168.1.61 |
1 Core,GB Memory,VMWARE虚拟机 |
现在我们登录到master 的MySQL上,在“mydb3”数据库下建好如下分区表:
Create table my_range_0 (id int not null primary key, col2 int, col3 varchar(32)); Create table my_range_1 (id int not null primary key, col2 int, col3 varchar(32)); Create table my_list_0 (id int not null primary key, col2 int, col3 varchar(32)); Create table my_list_1 (id int not null primary key, col2 int, col3 varchar(32)); Create table my_hash_0 (id int not null primary key, col2 int, col3 varchar(32)); Create table my_hash_1 (id int not null primary key, col2 int, col3 varchar(32));
然后登录到“slave1 ”的MySQL上,在“mydb3”数据库下建好如下分区表:
Create table my_range_2 (id int not null primary key, col2 int, col3 varchar(32)); Create table my_range_3 (id int not null primary key, col2 int, col3 varchar(32)); Create table my_list_2 (id int not null primary key, col2 int, col3 varchar(32)); Create table my_list_3 (id int not null primary key, col2 int, col3 varchar(32)); Create table my_list_4 (id int not null primary key, col2 int, col3 varchar(32)); Create table my_hash_2 (id int not null primary key, col2 int, col3 varchar(32)); Create table my_hash_3 (id int not null primary key, col2 int, col3 varchar(32));
可以看到,两台MySQL服务器上都没有创建OneProxy里的三张虚拟表:“my_range”、“my_list”和“my_hash”。
接下来我们将三张分区表的信息用JSON格式保存到文本文件(“part.txt”)中,如下所示:
修改配置文件中指定的part.txt
[ { "table" : "my_range", "pkey" : "id", "type" : "int", "method" : "range", "partitions": [ { "name" : "my_range_0", "group": "server1", "value" : "100000" }, { "name" : "my_range_1", "group": "server1", "value" : "200000" }, { "name" : "my_range_2", "group": "server2", "value" : "300000" }, { "name" : "my_range_3", "group": "server2", "value" : null } ] }, { "table" : "my_hash", "pkey" : "id", "type" : "int", "method" : "hash", "partitions": [ { "name" : "my_hash_0", "group": "server1" }, { "name" : "my_hash_1", "group": "server1" }, { "name" : "my_hash_2", "group": "server2" }, { "name" : "my_hash_3", "group": "server2" } ] }, { "table" : "my_list", "pkey" : "id", "type" : "int", "method" : "list", "partitions": [ { "name" : "my_list_0", "group": "server1", "value" : ["1","2","3"] }, { "name" : "my_list_1", "group": "server1", "value" : ["4","5","6"] }, { "name" : "my_list_2", "group": "server2", "value" : ["7","8","9"] }, { "name" : "my_list_3", "group": "server2", "value" : ["10","11","12"] }, { "name" : "my_list_4", "group": "server2", "value" : [] } ] } ]
并准备启动OneProxy的Shell脚本文件
测试
mysql -uproxy -p123456 -P3307 -h127.0.0.1 default -c
无异常,证明配置成功!
使用SQL语句来插入几条记录看看,针对插入语句,要求语句中显式地指定列名,如下所示:
insert into my_range (id, col2, col3) values (100, 1, 'This is row 1');
insert into my_range (id, col2, col3) values (100100, 2, 'This is row 2');
insert into my_range (id, col2, col3) values (200100, 3, 'This is row 3');
insert into my_range (id, col2, col3) values (300100, 4, 'This is row 4');
然后重新来查询一次“my_range”表,如下所示:
mysql> select * from my_range;
上述证明,我们通过前端代码查询select * from my_range表的时候,id=300100,100100被存到了2张表里。 这样后台把一张大表有规律的切成两张小表,在查询的速度上,会有成倍的提升。 MySQL中间件解析代理转发的过程消耗的性能经过测试,可以忽略不计!
在OneProxy中,可以直接使用分区的名字来进行查询,来验证一下是否每个分区各包含一条记录,也可以直接登录MySQL进行验证,如下所示:
mysql> select * from my_range_0;
+-----+------+---------------+
| id | col2 | col3 |
+-----+------+---------------+
| 100 | 1 | This is row 1 |
+-----+------+---------------+
1 row in set (0.00 sec)
mysql> select * from my_range_1;
+--------+------+---------------+
| id | col2 | col3 |
+--------+------+---------------+
| 100100 | 2 | This is row 2 |
+--------+------+---------------+
1 row in set (0.00 sec)
mysql> select * from my_range_2;
+--------+------+---------------+
| id | col2 | col3 |
+--------+------+---------------+
| 200100 | 3 | This is row 3 |
+--------+------+---------------+
1 row in set (0.00 sec)
mysql> select * from my_range_3;
+--------+------+---------------+
| id | col2 | col3 |
+--------+------+---------------+
| 300100 | 4 | This is row 4 |
+--------+------+---------------+
1 row in set (0.00 sec)
参考来源:http://www.cnblogs.com/youge-OneSQL/p/4220761.html