OneProxy分库分表演示--楼方鑫

最新推荐文章于 2021-02-02 19:17:51 发布
最新推荐文章于 2021-02-02 19:17:51 发布
阅读量1k 收藏
点赞数
分类专栏： 系统环境

OneProxy分库分表演示--楼方鑫

0条评论

[摘要：OneProxy分库分扮演示 （杭州布衣硬件有限公司） OneProxy是为MySQL经心计划的数据拜访层，能够为任何开辟说话供应对MySQL数据库的智能数据路由功效，比方单面切换、读写分别、分库分表]

OneProxy分库分表演示

（杭州平民软件有限公司）

 

       OneProxy是为MySQL精心设计的数据访问层，可以为任何开发语言提供对MySQL数据库的智能数据路由功能，比如单点切换、读写分离、分库分表等高级功能。并且允许你在公有云和私有云环境下自由布署，打造属于自己的数据架构。

       如果在测试中发现问题，请联系我（微博：平民架构，微信：anysql）！

分库分表

       Oracle从8.0.x版本开始在数据库内部支持分区表，可以将数据按某一个字段的某一规律来进行分片存贮，以降低数据库索引的层级，提高访问效率，也可以按分区重建索引，或按分区来访问数据，对上层的应用则是完全透明。如果业务数据有明显的时间特征，那么按时间范围分区，可以提高数据库内存的命中率，在过去的十多年中分区表发挥了重要的作用，可以说分区表是数据库的一项重大技术创新。

MySQL怎么做

       MySQL也支持分区表，可以按某一字段进行按范围（Range）、按值（List）、按哈希算法（Hash）进行分区。下面是MySQL中创建一个分区表（Range）的脚本：

      

CREATE TABLE employees (

    id INT NOT NULL,

    fname VARCHAR(30),

    lname VARCHAR(30),

    hired DATE NOT NULL DEFAULT '1970-01-01',

    separated DATE NOT NULL DEFAULT '9999-12-31',

    job_code INT NOT NULL,

    store_id INT NOT NULL

)

PARTITION BY RANGE (store_id) (

    PARTITION p0 VALUES LESS THAN (6),

    PARTITION p1 VALUES LESS THAN (11),

    PARTITION p2 VALUES LESS THAN (16),

    PARTITION p3 VALUES LESS THAN (21)

);

      

       按值（List）方法的建表脚本如下所示：

 

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT,
    store_id INT
)
PARTITION BY LIST(store_id) (
    PARTITION pNorth VALUES IN (3,5,6,9,17),
    PARTITION pEast VALUES IN (1,2,10,11,19,20),
    PARTITION pWest VALUES IN (4,12,13,14,18),
    PARTITION pCentral VALUES IN (7,8,15,16)
);

 

       按哈希（Hash）方法的建表脚本如下所示：

 

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT,
    store_id INT
)
PARTITION BY HASH( YEAR(hired) )
PARTITIONS 4;

 

       关于这三种分区方式的详细资料，可以查阅MySQL官方文档。在互联网、物联网时代到来之际，数据越来越重要，越来越被重视，单个硬件服务器处理数据的能力越来越跟不上时代的要求，需要有方法将数据透明地扩展到多台硬件服务器上，并且要求多台硬件服务器之间的数据是不共享（Share Nothing）的，传统的群集技术（Cluster）要求用共享存贮来进行全部数据的共享（Share Everything），因此群集中的通信开销过高，导致扩展的规模和效率都受很大的影响。

       最近六、七年里互联网企业，特别是电子商务和互联网金融的飞速发展，吸引了一大群技术人员，采用在一个公司内部统一开发语言和统一开发框架的方式解决了上层开发和下层资源之间的矛盾，称之为中间件或架构层。

       中间件或架构层中一部分是为了解决应用之间的通信及服务治理，另一部份是为了解决数据的分布问题。相当于是将前面例子中讲的分区表里的每一个分区分别移到一台不同的硬件服务器上，而让应用的开发基本感知不到或者能少做调整就能适应这种扩展，原理非常类似于分区表，但称之为分库分表。

       对于开源的数据库，比如MySQL，由于所有的协议对于应用透明，非常适合在应用和底层数据库之间置入一层SQL转发器，可以称之为Proxy，可以根据协议来做一些SQL的智能转发和优化处理，是否能够在这一层转发器（Proxy）上实现透明的底层数据库扩展，实现分库分表功能呢？

 

OneProxy做法

       OneProxy将分区表的概念从数据库层抽象到SQL转发器层，然后对通信协议进行分析，可根据SQL里的表名及传入参数进行对上层应用透明的智能路由，从而达到虚拟分区表的效果，也就实现了对应用透明的分库分表的功能。

 

       在OneProxy里同样支持按范围（Range）、按值（List）、按哈希算法（Hash）进行虚拟分库分表。按范围（Range）分库分表的配置数据，从内容上看和MySQL创建分区表的关键信息非常类似，有一个针对应用的虚拟表名（Table），指定一个用于分区的字段（PKey），字段的类型（Type），及分区的方法（Method）。并且要列出所有的分区，针对每一个分区，要设置独立的表名（Name），并且指定分区所在的服务器组（Group），以及分区字段取值的上限（Value）。如下所示：

      

  {

        "table"   : "my_range",

        "pkey"    : "id",

        "type"    : "int",

        "method"  : "range",

        "partitions":

           [

               { "name" : "my_range_0", "group": "default", "value" : "100000" },

               { "name" : "my_range_1", "group": "default", "value" : "200000" },

               { "name" : "my_range_2", "group": "server2", "value" : "300000" },

               { "name" : "my_range_3", "group": "server2", "value" : null     }

           ]

  }

      

       根据这个配置，应用在SQL语句中访问虚拟表（my_range）时，OneProxy会根据表名和分区字段上的参数值，做出智能的路由；如果操作涉及多个分区（指定多个值、范围值、或不指定值）时，可以透明地进行结果集的合并、分组统计和排序功能，使得对应用来讲，虚拟表（my_range）就象真真实实地建在一个数据库中一样。

 

       按值（List）分库分表的配置数据，从内容上看和MySQL创建分区表的关键信息非常类似，有一个针对应用的虚拟表名（Table），指定一个用于分区的字段（PKey），字段的类型（Type），及分区的方法（Method）。并且要列出所有的分区，针对每一个分区，要设置独立的表名（Name），并且指定分区所在的服务器组（Group），以及分区字段能取的值列表（Value），当一个分区没有指定任何分区值列表时，表示所有其他的值都落入这个分区。如下所示：

 

  {

        "table"   : "my_list",

        "pkey"    : "id",

        "type"    : "int",

        "method"  : "list",

        "partitions":

           [

               { "name" : "my_list_0", "group": "default", "value" : ["1","2","3"] },

               { "name" : "my_list_1", "group": "default", "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" : [] }

           ]

  }

 

       根据这个配置，应用在SQL语句中访问虚拟表（my_list）时，OneProxy会根据表名和分区字段上的参数值，做出智能的路由；如果操作涉及多个分区（指定多个值、范围值、或不指定值）时，可以透明地进行结果集的合并、分组统计和排序功能，使得对应用来讲，虚拟表（my_list）就象真真实实地建在一个数据库中一样。

 

       按哈希算法（Hash）分库分表的配置数据，从内容上看和MySQL创建分区表的关键信息非常类似，有一个针对应用的虚拟表名（Table），指定一个用于分区的字段（PKey），字段的类型（Type），及分区的方法（Method）。并且要列出所有的分区，针对每一个分区，要设置独立的表名（Name），并且指定分区所在的服务器组（Group）。需要注意的是，哈希分区，并不需要为每个分区指定值范围或值列表，而是由OneProxy里的哈希算法根据分区数进行自动计算，哈希分区里的分区数量不能随便调整。如下所示：

 

  {

        "table"   : "my_hash",

        "pkey"    : "id",

        "type"    : "int",

        "method"  : "hash",

        "partitions":

           [

               { "name" : "my_hash_0", "group": "default" },

               { "name" : "my_hash_1", "group": "default" },

               { "name" : "my_hash_2", "group": "server2" },

               { "name" : "my_hash_3", "group": "server2" }

           ]

  }

 

       根据这个配置，应用在SQL语句中访问虚拟表（my_hash）时，OneProxy会根据表名和分区字段上的参数值，做出智能的路由；如果操作涉及多个分区（指定多个值、范围值、或不指定值）时，可以透明地进行结果集的合并、分组统计和排序功能，使得对应用来讲，虚拟表（my_hash）就象真真实实地建在一个数据库中一样。

 

       创建表时，要根据分区中指定的表名和位置去创建相应的物进表，创建的表中应当包括分区字段，字段名称和类型都要匹配上。在第一台MySQL服务器（Server1）上创建如下表：

 

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));

 

       在第二台MySQL服务器（Server2）上创建如下表：

 

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的机器吧，确保两台MySQL的机器和OneProxy的机器之间网络都是通的。

       最理想的情况是做成完全透明的分布式数据库，但理想的分布式数据库是不存在的，因此通过去掉一些跨库的功能来达到工程上的可行性。

环境准备

       我们可以用真实的物理机器或云上的虚拟主机来做实验，充分体现自主的分库分表的乐趣，在这里我找了三台机器，具体信息如下所示：

 

机器名

IP地址

配置

Proxy1

10.128.130.237

8 Core，GB Memory，美团云虚拟机

Server1

10.128.128.238

8 Core，GB Memory，美团云虚拟机

Server2

10.128.128.38

8 Core，GB Memory，美团云虚拟机

 

       现在我们登录到“Server1”的MySQL上，创建数据库登录用户“test”（密码也同为“test”），在“test”数据库下建好如下分区表：

 

mysql> Create table my_range_0 (id int not null primary key, col2 int, col3 varchar(32));

Query OK, 0 rows affected (0.02 sec)

 

mysql> Create table my_range_1 (id int not null primary key, col2 int, col3 varchar(32));

Query OK, 0 rows affected (0.01 sec)

 

mysql> Create table my_list_0 (id int not null primary key, col2 int, col3 varchar(32));

Query OK, 0 rows affected (0.00 sec)

 

mysql> Create table my_list_1 (id int not null primary key, col2 int, col3 varchar(32));

Query OK, 0 rows affected (0.01 sec)

 

mysql> Create table my_hash_0 (id int not null primary key, col2 int, col3 varchar(32));

Query OK, 0 rows affected (0.01 sec)

 

mysql> Create table my_hash_1 (id int not null primary key, col2 int, col3 varchar(32));

Query OK, 0 rows affected (0.01 sec)

 

       然后登录到“Server2”的MySQL上，创建数据库登录用户“test”（密码也同为“test”）,在“test”数据库下建好如下分区表：

 

mysql> Create table my_range_2 (id int not null primary key, col2 int, col3 varchar(32));

Query OK, 0 rows affected (0.02 sec)

 

mysql> Create table my_range_3 (id int not null primary key, col2 int, col3 varchar(32));

Query OK, 0 rows affected (0.03 sec)

 

mysql> Create table my_list_2 (id int not null primary key, col2 int, col3 varchar(32));

Query OK, 0 rows affected (0.02 sec)

 

mysql> Create table my_hash_3 (id int not null primary key, col2 int, col3 varchar(32));

Query OK, 0 rows affected (0.02 sec)

 

mysql> Create table my_list_4 (id int not null primary key, col2 int, col3 varchar(32));

Query OK, 0 rows affected (0.02 sec)

 

mysql> Create table my_hash_2 (id int not null primary key, col2 int, col3 varchar(32));

Query OK, 0 rows affected (0.01 sec)

 

mysql> Create table my_hash_3 (id int not null primary key, col2 int, col3 varchar(32));

Query OK, 0 rows affected (0.02 sec)

 

       可以看到，两台MySQL服务器上都没有创建OneProxy里的三张虚拟表：“my_range”、“my_list”和“my_hash”。接下来我们将三张分区表的信息用JSON格式保存到文本文件（“part.txt”）中，如下所示：

 

[

  {

        "table"   : "my_range",

        "pkey"    : "id",

        "type"    : "int",

        "method"  : "range",

        "partitions":

           [

               { "name" : "my_range_0", "group": "default", "value" : "100000" },

               { "name" : "my_range_1", "group": "default", "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": "default" },

               { "name" : "my_hash_1", "group": "default" },

               { "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": "default", "value" : ["1","2","3"] },

               { "name" : "my_list_1", "group": "default", "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" : [] }

           ]

  }

]

 

       然后登录到“Proxy1”，将OneProxy的包解压到“/usr/local”目录下，这样OneProxy就被安装到“/usr/local/oneproxy”目录下了，将上一步的分区表配置信息文件“part.txt”也移到“/usr/local/oneproxy”下。并准备启动OneProxy的Shell脚本文件，如下所示：

 

#/bin/bash

#

 

if [ -e "/usr/local/oneproxy/log/oneproxy.pid" ]

then

    kill -INT $(cat /usr/local/oneproxy/log/oneproxy.pid)

fi

 

sleep 2

ulimit -c 10000

/usr/local/oneproxy/oneproxy

  --proxy-address=:3307 

  --proxy-master-addresses=10.128.128.238:3306@default 

  --proxy-master-addresses=10.128.128.38:3306@server2 

  --proxy-user-list=test/A94A8FE5CCB19BA61C4C0873D391E987982FBBD3@test 

  --proxy-group-policy=default:0 

  --proxy-part-tables=/usr/local/oneproxy/part.txt 

  --event-threads=8 

  --log-file=/usr/local/oneproxy/log/oneproxy.log 

  --pid-file=/usr/local/oneproxy/log/oneproxy.pid

 

       然后启动OneProxy，在命令行下执行如下命令：

 

# sh start.sh

 

       接下来看一下启动的日志文件，看看启动过程中有有没有出错。在命令行下执行如下命令：

 

# tail -30f /usr/local/oneproxy/log/oneproxy.log

2015-01-13 08:35:46: (critical) plugin oneproxy 0.8.4 started

2015-01-13 08:35:46: (critical) Background thread 1 started

2015-01-13 08:35:46: (critical) Background thread 2 started

2015-01-13 08:35:47: (critical) Ping backend (10.128.128.238:3306) success, mark it up!

2015-01-13 08:35:47: (critical) Ping backend (10.128.128.38:3306) success, mark it up!

 

       可以看到OneProxy已经成功启动，并且连接到了两台MySQL数据库，接下来就可以通过OneProxy对分区表进行各种操作了。

更新操作

       现在三张表里没有任何数据，第一步就是用标准的MySQL客户端连接到OneProxy，须注意连接时，端口的写OneProxy的端口，这里是“3307”，如下所示：

 

# /usr/local/mysql5.6/bin/mysql -u test -h 127.0.0.1 -P3307 -ptest

Warning: Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor.  Commands end with ; or g.

Your MySQL connection id is 106

Server version: 5.0.99-OneProxy-agent (OneSQL) Source distribution

 

Copyright (c) 2000, 2014, 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>

 

       我们来对三张表做一个查询试试：

 

mysql> select * from my_range;

Empty set (0.00 sec)

 

mysql> select * from my_list;

Empty set (0.00 sec)

 

mysql> select * from my_hash;

Empty set (0.00 sec)

 

       我们回头看看“my_range”分区表的定义：

 

  {

        "table"   : "my_range",

        "pkey"    : "id",

        "type"    : "int",

        "method"  : "range",

        "partitions":

           [

               { "name" : "my_range_0", "group": "default", "value" : "100000" },

               { "name" : "my_range_1", "group": "default", "value" : "200000" },

               { "name" : "my_range_2", "group": "server2", "value" : "300000" },

               { "name" : "my_range_3", "group": "server2", "value" : null     }

           ]

  }

 

       使用SQL语句来插入几条记录看看，针对插入语句，要求语句中显式地指定列名，如下所示：

 

mysql> insert into my_range (id, col2, col3) values (100, 1, 'This is row 1');

Query OK, 1 row affected (0.01 sec)

 

mysql> insert into my_range (id, col2, col3) values (100100, 2, 'This is row 2');

Query OK, 1 row affected (0.00 sec)

 

mysql> insert into my_range (id, col2, col3) values (200100, 3, 'This is row 3');

Query OK, 1 row affected (0.01 sec)

 

mysql> insert into my_range (id, col2, col3) values (300100, 4, 'This is row 4');

Query OK, 1 row affected (0.01 sec)

 

       然后重新来查询一次“my_range”表，如下所示：

 

mysql> select * from my_range;

+--------+------+---------------+

| id     | col2 | col3          |

+--------+------+---------------+

|    100 |    1 | This is row 1 |

| 100100 |    2 | This is row 2 |

| 200100 |    3 | This is row 3 |

| 300100 |    4 | This is row 4 |

+--------+------+---------------+

4 rows in set (0.01 sec)

 

       在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)

 

       对于更新操作来讲，要求更新的语句只能操作一个分区的数据，否则报错并不能执行。比如我们没有显式指定列名，会怎么样呢？

 

mysql> insert into my_range  values (400100, 4, 'This is row 4');

ERROR 1044 (42000): Partitioned tables should choose only one partition for DML queries!

 

       当OneProxy无法从SQL取到分区列上的值时，会选择所有的分区，对于DML更新类操作，OneProxy因为不支持分布式事务，所在对DML语句能操作的分区数做了严格的限定，即只能操作一个分区。事实上对于在显式事务中的查询语句，也有同样的限制，比如我们显式开始一个事务，然后对虚拟表做全表查询，看看OneProxy会如何反馈？

 

mysql> start transaction;

Query OK, 0 rows affected (0.00 sec)

 

mysql> select * from my_range;

ERROR 1044 (42000): Partitioned tables should choose only one partition when in transaction!

mysql> rollback;

Query OK, 0 rows affected (0.00 sec)

 

mysql> select * from my_range;

+--------+------+---------------+

| id     | col2 | col3          |

+--------+------+---------------+

|    100 |    1 | This is row 1 |

| 100100 |    2 | This is row 2 |

| 200100 |    3 | This is row 3 |

| 300100 |    4 | This is row 4 |

+--------+------+---------------+

4 rows in set (0.00 sec)

 

       当在一个插入语句中插入多行时，要求所有的行必须位于同一个分区，否则并不能进行插入操作。如下所示：

 

mysql> insert into my_range (id, col2, col3) values (400100, 5, 'This is row 5'), (101, 6, 'This is row 6');

ERROR 1044 (42000): Partitioned tables should choose only one partition for DML queries!

 

如果我们调整一下SQL语句，使多行位于同一个分区，则可以成功操作。如下所示：

 

mysql> insert into my_range (id, col2, col3)  values (101, 5, 'This is row 5'), (102, 6, 'This is row 6');

Query OK, 2 rows affected (0.00 sec)

Records: 2  Duplicates: 0  Warnings: 0

 

现在再来查询一下第一个分区的数据，如下所示：

 

mysql> select * from my_range_0;

+-----+------+---------------+

| id  | col2 | col3          |

+-----+------+---------------+

| 100 |    1 | This is row 1 |

| 101 |    5 | This is row 5 |

| 102 |    6 | This is row 6 |

+-----+------+---------------+

3 rows in set (0.00 sec)

 

       对于另外的两张表“my_list”和“my_hash”，我在这里就不重复举例子了，请大家自行测试验证。

       对于OneProxy里的分区表来讲，更新操作（DML语句）只能涉及一个分区；对事务中的任何查询语句，也有此要求。

查询操作

       对于查询语句，如果OneProxy发现只涉及一个分区操作，则直接交给后端的数据库进行处理，包括排序、分页、分组统计等功能，都不需要经过OneProxy干预，只有当查询语句涉及多个分区时，才会参与处理。

       前面已经有一些查询的例子了，对于查询操作，如果不在显式的事务中，则可以做多个分区的结果集合并，如果在事务中，则必须要求查询只涉及一个分区。在查询条件中，可以包含分区列，也可以不包含分区列，OneProxy都能自动处理。如下所示：

 

mysql> select * from my_range where id = 100;

+-----+------+---------------+

| id  | col2 | col3          |

+-----+------+---------------+

| 100 |    1 | This is row 1 |

+-----+------+---------------+

1 row in set (0.00 sec)

 

mysql> select * from my_range where col2=2;

+--------+------+---------------+

| id     | col2 | col3          |

+--------+------+---------------+

| 100100 |    2 | This is row 2 |

+--------+------+---------------+

1 row in set (0.00 sec)

 

       在OneProxy中，当查询涉及多个分区时，依然可以对结果集进行排序，如下所示：

 

mysql> select * from my_range order by col2;

+--------+------+---------------+

| id     | col2 | col3          |

+--------+------+---------------+

|    100 |    1 | This is row 1 |

| 100100 |    2 | This is row 2 |

| 200100 |    3 | This is row 3 |

| 300100 |    4 | This is row 4 |

|    101 |    5 | This is row 5 |

|    102 |    6 | This is row 6 |

+--------+------+---------------+

6 rows in set (0.00 sec)

 

mysql> select * from my_range order by col2 desc;

+--------+------+---------------+

| id     | col2 | col3          |

+--------+------+---------------+

|    102 |    6 | This is row 6 |

|    101 |    5 | This is row 5 |

| 300100 |    4 | This is row 4 |

| 200100 |    3 | This is row 3 |

| 100100 |    2 | This is row 2 |

|    100 |    1 | This is row 1 |

+--------+------+---------------+

6 rows in set (0.00 sec)

 

mysql> select * from my_range order by col3;

+--------+------+---------------+

| id     | col2 | col3          |

+--------+------+---------------+

|    100 |    1 | This is row 1 |

| 100100 |    2 | This is row 2 |

| 200100 |    3 | This is row 3 |

| 300100 |    4 | This is row 4 |

|    101 |    5 | This is row 5 |

|    102 |    6 | This is row 6 |

+--------+------+---------------+

6 rows in set (0.00 sec)

 

mysql> select * from my_range order by col3 desc;

+--------+------+---------------+

| id     | col2 | col3          |

+--------+------+---------------+

|    102 |    6 | This is row 6 |

|    101 |    5 | This is row 5 |

| 300100 |    4 | This is row 4 |

| 200100 |    3 | This is row 3 |

| 100100 |    2 | This is row 2 |

|    100 |    1 | This is row 1 |

+--------+------+---------------+

6 rows in set (0.00 sec)

 

       当查询语句有多层时，要求排序条件必须写在最外层的查询语句中，否则OneProxy不进行排序，比如我们将上面的某个语句，放到子查询里，会怎么样呢？如下所示：

 

mysql> select * from my_range order by col3 desc;

+--------+------+---------------+

| id     | col2 | col3          |

+--------+------+---------------+

|    102 |    6 | This is row 6 |

|    101 |    5 | This is row 5 |

| 300100 |    4 | This is row 4 |

| 200100 |    3 | This is row 3 |

| 100100 |    2 | This is row 2 |

|    100 |    1 | This is row 1 |

+--------+------+---------------+

6 rows in set (0.00 sec)

 

mysql> select * from (select * from my_range order by col3 desc) as a;

+--------+------+---------------+

| id     | col2 | col3          |

+--------+------+---------------+

|    102 |    6 | This is row 6 |

|    101 |    5 | This is row 5 |

|    100 |    1 | This is row 1 |

| 100100 |    2 | This is row 2 |

| 200100 |    3 | This is row 3 |

| 300100 |    4 | This is row 4 |

+--------+------+---------------+

6 rows in set (0.00 sec)

 

       可以看到第二个SQL语句里，第一个分区内的记录排序了，然后OneProxy根据处理分区的顺序进行结果集的合并，这是需要注意的地方，排序子句中也支持按多个列排序。

 

       也可以对查询的结果集进行分页操作，比如前面的6条记录，我们可以按3条记录一页，进行两次查询，同样分区的条件也要写在最外层的查询语句中。如下所示：

 

mysql> select * from my_range order by col2 desc limit 3;

+--------+------+---------------+

| id     | col2 | col3          |

+--------+------+---------------+

|    102 |    6 | This is row 6 |

|    101 |    5 | This is row 5 |

| 300100 |    4 | This is row 4 |

+--------+------+---------------+

3 rows in set (0.00 sec)

 

mysql> select * from my_range order by col2 desc limit 3, 3;

+--------+------+---------------+

| id     | col2 | col3          |

+--------+------+---------------+

| 200100 |    3 | This is row 3 |

| 100100 |    2 | This is row 2 |

|    100 |    1 | This is row 1 |

+--------+------+---------------+

3 rows in set (0.01 sec)

 

       对分页语句有一个要求，要求分页的起点值（“offset”）不要超过一万，超过一万的话，结果集会不准确。因为分页语句，需要OneProxy缓存所有的记录，在内存中进行排序，如果起点值（“offset”）太大，OneProxy被迫缓存大量的记录，可以导致OneProxy内存耗尽，变得不够稳定。

       对于分组统计语句，OneProxy精确支持“”、“”、“”和“”四种操作符，并且可以按字段分组进行操作，当查询语句有多层时，要求排序条件必须写在最外层的查询语句中，否则不进行处理。来看一个最简单的统计语句吧，如下所示：

 

mysql> select max(id), min(id), max(col2), min(col2) from my_range;

+---------+---------+-----------+-----------+

| max(id) | min(id) | max(col2) | min(col2) |

+---------+---------+-----------+-----------+

|  300100 |     100 |         6 |         1 |

+---------+---------+-----------+-----------+

1 row in set (0.00 sec)

 

       接下来我们将“my_range”表的“col2”字段的值，做一些更新，以方便进行分组汇总的演示，如下所示：

 

mysql> update my_range set col2=1 where id=102;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1  Changed: 1  Warnings: 0

 

mysql> update my_range set col2=3 where id=300100;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1  Changed: 1  Warnings: 0

 

mysql> update my_range set col2=1 where id=101;

Query OK, 1 row affected (0.01 sec)

Rows matched: 1  Changed: 1  Warnings: 0

 

此时整个表的数据如下所示：

 

mysql> select * from my_range;

+--------+------+---------------+

| id     | col2 | col3          |

+--------+------+---------------+

|    100 |    1 | This is row 1 |

|    101 |    1 | This is row 5 |

|    102 |    1 | This is row 6 |

| 100100 |    2 | This is row 2 |

| 200100 |    3 | This is row 3 |

| 300100 |    3 | This is row 4 |

+--------+------+---------------+

6 rows in set (0.00 sec)

 

下面来根据“col2”列做分组汇总的查询，如下所示：

 

mysql> select col2, max(id), min(id), sum(id), count(*) from my_range group by col2;

+------+---------+---------+---------+----------+

| col2 | max(id) | min(id) | sum(id) | count(*) |

+------+---------+---------+---------+----------+

|    1 |     102 |     100 |     303 |        3 |

|    2 |  100100 |  100100 |  100100 |        1 |

|    3 |  300100 |  200100 |  500200 |        2 |

+------+---------+---------+---------+----------+

3 rows in set (0.01 sec)

 

       对于另外的两张表“my_list”和“my_hash”，我在这里就不重复举例子了，请大家自行测试验证。

       对于OneProxy里的分区表来讲，查询操作（SELECT语句）在事务中只能涉及一个分区；当查询操作只涉及一个分区时，OneProxy不做任何处理，直接返回数据库的处理结果；当涉及多个分区时，OneProxy里可以做结果集的合并、排序、分页、汇总统计操作。

静水流深cn
关注
专栏目录
数据优化——分库分表（三）中间件讲解
CabbageDevil成狂成魔之路
05-06 4245
分库分表中间件的类型，PROXY模式，CLIENT模式，sharding
数据库为什么要分库分表
热门推荐
bieleyang的博客
08-16 1万+
1 基本思想之什么是分库分表？ 从字面上简单理解，就是把原本存储于一个库的数据分块存储到多个库上，把原本存储于一个表的数据分块存储到多个表上。 2 基本思想之为什么要分库分表？ 数据库中的数据量不一定是可控的，在未进行分库分表的情况下，随着时间和业务的发展，库中的表会越来越多，表中的数据量也会越来越大，相应地，数据操作，增删改查的开销也会越来越大；另外，由于无法进行分布式式部署，而一台服务器的资
楼方鑫-深层定制 OneSQL
11-26
2015 Oracle 技术嘉年华（OTN）分会场7楼方鑫-深层定制 OneSQL
OneProxy实现MySQL分库分表
weixin_33827965的博客
12-23 277
OneProxy实现MySQL分库分表简介Part1:写在最前    随着网站的壮大，MySQL数据库架构一般会经历一个过程：当我们数据量比较小的时候，一台单实例数据库足矣。等我们数据量增大的时候，我们会采用一主多从的数据库架构来降低我们的读写io。当我们某张业务表达到几百万上千万甚至上亿时，就应该去进行分表处理。本文演示OneProxy对数据库实现分表处理，对前端应用是透明的。Part2:环境简...
oneproxy for mysql_OneProxy实现MySQL分库分表
weixin_42374763的博客
02-02 171
OneProxy实现MySQL分库分表简介Part1:写在最前随着网站的壮大，MySQL数据库架构一般会经历一个过程：当我们数据量比较小的时候，一台单实例数据库足矣。等我们数据量增大的时候，我们会采用一主多从的数据库架构来降低我们的读写io。当我们某张业务表达到几百万上千万甚至上亿时，就应该去进行分表处理。本文演示OneProxy对数据库实现分表处理，对前端应用是透明的。Part2:环境简介HE1...
OneProxy分库分表
liuxiansheng1228的博客
04-12 617
OneProxy分库分表环境：192.168.24.128    master192.168.24.129    oneproxy关闭防火墙或开启端口一、        配置master主机1.      分配连接用户权限并创建数据库mysql> grant all on *.* to oneproxy@'%' identified by '123.com'; mysql> creat...
5.4-方鑫+青年秀+面向可摘局部义齿的临床决策支持系统.zip
最新发布
01-08
本方案，名为"5.4-方鑫+青年秀+面向可摘局部义齿的临床决策支持系统"，正是这样一种创新的智慧医院解决方案，它旨在通过大数据平台的整体规划，为可摘局部义齿的诊疗过程提供精准、高效的决策支持。 临床决策支持...
MySQL深层定制实践：楼方鑫的OneSQL分享
"楼方鑫在2015年Oracle技术嘉年华（OTN）分会场上分享了关于MySQL深层定制的主题——OneSQL。楼方鑫是一位拥有超过15年DBA经验的专家，曾编写过SQLULDR2和MyDUL（后更名为AUL），在eBay担任过Staff DBA，在阿里巴巴...
OneProxy安装部署和分表简测
隔壁wangshifu的博客
10-31 3743
Oneproxy部署安装：  安装环境：UCloud云主机  centos 6.4  2.6.32-431.11.25.el6.ucloud.x86_64   官网http://www.onexsoft.com/?page_id=4029下载oneproxy最新版： oneproxy-rhel5-linux64-v5.8.5-ga.tar.gz   测试环境的mysql版本是5.6.1
oneproxy-rhel5-linux64-v6.0.0-ga.tar.gz
08-02
OneProxy是平民软件完全自主开发的分布式数据访问层，帮助用户在MySQL/PostgreSQL集群上快速搭建支持分库分表的分布式数据库中间件，也是一款具有SQL白名单（防SQL注入）及IP白名单功能的SQL防火墙软件。采用与MySQL Proxy一致的反向协议输出模式，对应用非常简单和透明易用
OneProxy监控的框架OneProxy-Monitor.zip
07-19
OneProxy-Monitor，OneProxy监控的框架，可以通过实现 protocolbase.h 中的接口来快速构建一个数据库的监控程序。具有如下的功能：1. 可以指定多个端口，不同的端口转发到不同的数据库。也就是通过一个监控器管理多个数据库2. 可以通过配置控制前端连接数3. 自动根据cpu数量设置线程个数4. 支持windows和Linux。其中windows只支持select。Linux支持select和epoll，默认为epoll方式5. FakeProtocol 是针对包直接进行转发。
oneproxy框架
07-22
平民软件团队对传统 Oracle 数据库有着超过 15 年的使用和管理经验，团队 初创成员有超过 10 年的互联网公司（eBay、支付宝等公司）数据库管理和数据 架构设计经验，加上对开源数据库协议层代码的深入理解和掌握，研发了面向数据库协议的 Haproxy/LVS 类产品：OneProxy，旨在帮助大家可以象搭建应用集群一样方便快速地搭建数据库集群，帮助大家节约研发成本、运维管理成本、时间成本，以帮助大家迅速面对业务的高速发展，为互联网、物联网时代数据的井喷打造一个可靠低成本的数据层解决方案。
Mysql 中间件 oneProxy总结-附件资源
03-02
Mysql 中间件 oneProxy总结-附件资源
楼方鑫
congdi7904的博客
06-09 396
楼方鑫链接:http://www.anysql.net/ ...
支付宝DBA楼方鑫：技术高手 自学成才
Gongora的专栏
10-19 2915
对技术的痴迷是多数技术类专家的共同特点，这在楼方鑫身上表现得尤为明显。很少人知道，这个全国DBA(数据库管理员)领域排名前十位的高手，居然是经济管理专业出身。现在，他跟冯春培、冯大辉等几个DBA领域的顶尖高手一起负责规划、建设和维护支付宝的数据库。截至2009年7月6日，支付宝拥有用户数超过2亿，每天交易笔数数以百万计算，数据库的规模和复杂程度在国内所有数据库中都排名前列。楼方
2021云数据库RDS重磅升级发布会
阿里云开发者
01-21 693
简介：数据库上云已是大势所趋，如何实现弹性上云，降低企业数据库成本？如何将企业既有数据库能力与云数据库能力相结合，使云数据库更好支撑业务运行？应用和数据库布署到云上后，如何保障数据库内核的平稳运行？>>发布会传送门：https://yqh.aliyun.com/live/detail/21815点击查看详情：https://yqh.aliyun.com/live/2021rds数据库上云已是大势所趋，如何实现弹性上云，降低企业数据库成本？如何将企业既有数据库能力与云数据库能力相结合，使云数据库更
DBA高手楼方鑫：从ebay到支付宝
vebe77的专栏
09-17 763
 对技术的痴迷是多数技术类专家的共同特点，这在楼方鑫身上表现得尤为明显。很少人知道，这个全国DBA(数据库管理员)领域排名前十位的高手，居然是经济管理专业出身。现在，他跟冯春培、冯大辉等几个DBA领域的顶尖高手一起负责规划、建设和维护支付宝的数据库。截至2009年7月6日，支付宝拥有用户数超过2亿，每天交易笔数数以百万计算，数据库的规模和复杂程度在国内所有数据库中都排名前列。楼方鑫说，在这样一
平民软件OneProxy的强大
weixin_33850015的博客
06-18 391
简介 使用MySQL时，随着时间、业务的需求、用户量以及数据量的逐渐增加，相对的，数据库的的操作更是剧增，最终将会使MySQL达到某个瓶颈，那么MySQL的性能将会大大降低，更严重的话将会影响整个系统架构的正常运行，相信看过《太空旅客》这部电影的小伙伴们都知道，飞船的系统有一处瘫痪了，其他的系统想帮忙减轻负重，但是这个工作量太大了分担不过来，接着其他的系...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值