oneproxy for mysql_使用oneproxy对mysql进行分库分表

数据库分库分表是一把双刃剑，它可以在一定程度上加快查询速度，另外在一些需要查询历史数据、跨分区查询中，又会带来一些效率问题，因此，只有在有经验的DBA的带领下才能实施，否则，这可能是一个巨大的工程，而且未必能带来明显效率的提升。

一、数据库分库分表的几种方式：

1、在同一台数据库服务器上将表分割成多张：

如上：

将一份大的数据分割到同一数据库的其他表中，这种方式有一种特殊的情况，将历史数据定时归档，存放到一张历史表中，表中只存储最近的数据，以便统计等查询。

2、在同一台数据库上把同一项目的不同的业务放到不同的数据库中

如图，将用户、订单和产品分别存储到不同的数据库中

3、将同一台数据库上的数据库表根据业务的划分分散到不同的独立服务器上

4、将同一张表分散到不同的独立的服务器上

二、分库分表(水平拆分)前的准备

1、选择分区键分区键要尽可能避免跨分片查询的发生

分区键要尽量使各个分片的数据平均

2、如何存储无需分片的表

如一些公共的数据

每个分片中存储一份相同的数据

将无需分片的表使用额外的节点统一存储

3、如何在节点上部署分片每个分片使用单一数据库，并且数据库名也相同，比如都叫user

将多个分片表存储在一个数据库中，并在表名上加入分片号后缀，如user_1,user_2等

在一个节点中部署多个数据库，每个数据库包含一个分片

4、如何分配分片中的数据

这和MySQL的分区类似，有按范围(Range)、按值、按哈希算法进行分区

按分区键的hash值取模来分配分片数据

按分区键的范围来分配分片数据

利用分区键和分片的映射表来分配分片数据

5、如何生成全局唯一ID使用auto_increment_increment和auto_increment_offset参数

使用全局节点来生成ID

使用Redis等缓存服务器来创建全局ID

三、使用oneproxy进行分库分表

1、对单个表进行分库分表(1)确定要分区的表，在各个节点上建立结构相同但表名不同的表(表前缀相同，后缀不同)

在节点1上执行：

CREATE TABLE`m_user_info_1`(

`uid`int(11)NOT NULL,

`account`char(30)NOT NULL DEFAULT'',

`password`char(32)NOT NULL DEFAULT'',

`create_time`timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,

PRIMARY KEY(`uid`)

)ENGINE=InnoDBDEFAULT CHARSET=utf8;

CREATE TABLE`m_user_info_2`(

`uid`int(11)NOT NULL,

`account`char(30)NOT NULL DEFAULT'',

`password`char(32)NOT NULL DEFAULT'',

`create_time`timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,

PRIMARY KEY(`uid`)

)ENGINE=InnoDBDEFAULT CHARSET=utf8;

在节点2上执行：

CREATE TABLE`m_user_info_3`(

`uid`int(11)NOT NULL,

`account`char(30)NOT NULL DEFAULT'',

`password`char(32)NOT NULL DEFAULT'',

`create_time`timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,

PRIMARY KEY(`uid`)

)ENGINE=InnoDBDEFAULT CHARSET=utf8;

CREATE TABLE`m_user_info_4`(

`uid`int(11)NOT NULL,

`account`char(30)NOT NULL DEFAULT'',

`password`char(32)NOT NULL DEFAULT'',

`create_time`timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,

PRIMARY KEY(`uid`)

)ENGINE=InnoDBDEFAULT CHARSET=utf8;

(2)建立分区规则模板

在oneproxy的conf目录下建立user_part.txt，也可以复制template.txt文件进行修改：

[

{

"table":"m_user_info",

"pkey":"uid",

"type":"int",

"method":"range",

"partitions":

[

{"suffix":"_1","group":"user1","value":100000},

{"suffix":"_2","group":"user1","value":200000},

{"suffix":"_3","group":"user2","value":300000},

{"suffix":"_4","group":"user2","value":null}

]

}

]

注：

table表示表名(表前缀)

pkey表示表的主键

type表示主键的类型

method表示分区键的规则策略(有范围range,值list和hash)

partions为分区的规则suffix为后缀

group为服务器组名

value表示范围的最大值(并不包括这个值)

name为完整的表名

(3)修改oneproxy配置，并重启oneproxy服务

完整配置：

[oneproxy]

keepalive=1

event-threads=4

log-file=log/oneproxy.log

pid-file=log/oneproxy.pid

lck-file=log/oneproxy.lck

#proxy-auto-readonly = 1

proxy-forward-clientip=1

proxy-trans-debug=1

proxy-address=:3307

mysql-version=5.7.16

proxy-master-addresses.1=192.168.0.101:3306@user1

proxy-master-addresses.2=192.168.0.102:3306@user2

proxy-user-list=proxyadmin/4841E43E59A4337D61C5D14291D737EAA1A38B4E@user_db

#proxy-part-template = conf/user_part.txt

proxy-part-tables=conf/user_part.txt

#proxy-part-tables.1 = conf/part.txt

#proxy-part-tables.2 = conf/part2.txt

#proxy-part-tables.3 = conf/cust1.txt

proxy-charset=utf8_general_ci

proxy-group-policy=user1:0

(4)使用客户端进行分区测试

执行插入操作

这里通过php作为客户端来插入400000条数据

$conf=array(

'host'=>'192.168.0.100',

'port'=>3307,

'user'=>'proxyadmin',

'password'=>'oneproxy',

'database'=>'user_db'

);

$dsn='mysql:dbname='.$conf['database'].';host='.$conf['host'].';port='.$conf['port'].';charset=UTF8';

try{

$dbh=newPDO($dsn,$conf['user'],$conf['password'],array(PDO::MYSQL_ATTR_INIT_COMMAND=>'SET NAMES \'UTF8\''));

}catch(PDOException$e){

echo'ERROR: '.$e->getMessage();

exit;

}

$idx=0;

for($i=1;$i<=400000;$i++){

$account='shixinke'.$i;

$password=md5($account.$i);

$sql='INSERT INTO m_user_info(uid, account, password) VALUES('.$i.', "'.$account.'", "'.$password.'")';

$res=$dbh->exec($sql);

if($res){

$idx++;

}else{

var_dump($dbh->errorInfo());

}

}

echo $idx;

执行查询操作

在oneproxy代理服务器上查看总记录数：

在各节点服务器上查看各分区记录数：

节点1：

![]

节点2：

为什么m_user_1这张表只有9999条记录呢，因为分区规则上它的上限是100000，而且是不能包含这个。

在oneproxy代理服务器上查询某条记录：

2、对多个表进行分库分表

多个表与单个表分库分表并没有什么差别，只需要多添加一条分区规则即可，不同这里讨论一个特殊的情况，就是一个不需要分区的表的情况，那它在各个节点都会保留一份相同的数据(当然个人认为把它放到一个单独的节点上更好，不过对于联表查询来说不方便)

这里以配置表m_options为例

这里只列举一下规则配置：

[

{

"table":"m_user_info",

"pkey":"uid",

"type":"int",

"method":"range",

"partitions":

[

{"suffix":"_1","group":"user1","value":100000},

{"suffix":"_2","group":"user1","value":200000},

{"suffix":"_3","group":"user2","value":300000},

{"suffix":"_4","group":"user2","value":null}

]

},

{

"table":"m_options",

"pkey":"key",

"type":"char",

"method":"global",

"partitions":

[

{"suffix":"","group":"user1"},

{"suffix":"","group":"user2"}

]

}

]