【Mysql】OneProxy分庫分表
環境準備
我們可以用真實的物理機器或雲上的虛擬主機來做實驗，充分體現自主的分庫分表的樂趣，在這裡我找了三臺機器，具體資訊如下所示：
|
機器名 |
IP地址 |
配置 |
|
Proxy1 |
192.168.1.6 |
8 Core，GB Memory，美團雲虛擬機器 |
|
group1 |
192.168.1.6 |
8 Core，GB Memory，美團雲虛擬機器 |
|
group2 |
192.168.1.52 |
8 Core，GB Memory，美團雲虛擬機器 |
1 建表
-
建立表時，要根據分割槽中指定的表名和位置去建立相應的物進表，建立的表中應當包括分割槽欄位，欄位名稱和型別都要匹配上。
在第一臺MySQL伺服器（group1）上建立如下表：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));
- group2上面建立如下的表
-
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));
2 可以看到，兩臺MySQL伺服器上都沒有建立OneProxy裡的三張虛擬表：“my_range”、“my_list”和“my_hash”。接下來我們將三張分割槽表的資訊用JSON格式儲存到文字檔案（“part.txt”）中，如下所示：
- 如：
-
[root@hostnfsd :/usr/local/oneproxy]$ more part.txt
[
{
"table" : "my_range",
"pkey" : "id",
"type" : "int",
"method" : "range",
"partitions":
[
{ "name" : "my_range_0", "group": "group1", "value" : "100000" },
{ "name" : "my_range_1", "group": "group1", "value" : "200000" },
{ "name" : "my_range_2", "group": "group2", "value" : "300000" },
{ "name" : "my_range_3", "group": "group2", "value" : null }
]
},
{
"table" : "my_hash",
"pkey" : "id",
"type" : "int",
"method" : "hash",
"partitions":
[
{ "name" : "my_hash_0", "group": "group1" },
{ "name" : "my_hash_1", "group": "group1" },
{ "name" : "my_hash_2", "group": "group2" },
{ "name" : "my_hash_3", "group": "group2" }
]
},
{
"table" : "my_list",
"pkey" : "id",
"type" : "int",
"method" : "list",
"partitions":
[
{ "name" : "my_list_0", "group": "group1", "value" : ["1","2","3"] },
{ "name" : "my_list_1", "group": "group1", "value" : ["4","5","6"] },
{ "name" : "my_list_2", "group": "group2", "value" : ["7","8","9"] },
{ "name" : "my_list_3", "group": "group2", "value" : ["10","11","12"] },
{ "name" : "my_list_4", "group": "group2", "value" : [] }
]
}
]
3配置檔案
-
[root@hostnfsd :/usr/local/oneproxy]$ vi demo.sh
#/bin/bash
#
export ONEPROXY_HOME=/usr/local/oneproxy
# valgrind --leak-check=full \
${ONEPROXY_HOME}/bin/oneproxy --keepalive \
--proxy-address=192.168.1.6:3308 \
--admin-address=192.168.1.6:4041 \
--proxy-master-addresses=192.168.1.6:3307@group1 \
--proxy-master-addresses=192.168.1.52:3306@group2 \
--proxy-user-list=testuser/950434F7872CB57A600E1B2B7237766FA7E95460@testbak \
--proxy-charset=utf8_chinese_ci \
--proxy-part-tables=${ONEPROXY_HOME}/part.txt \
--log-file=${ONEPROXY_HOME}/oneproxy.log \
--pid-file=${ONEPROXY_HOME}/oneproxy.pid
4 重啟oneproxy
[root@hostnfsd :/usr/local/oneproxy]$ ps -ef | grep -i one|grep -v grep |awk '{print $2}'|xargs kill -9
[root@hostnfsd :/usr/local/oneproxy]$ sh demo.sh
更新操作
現在三張表裡沒有任何資料，第一步就是用標準的MySQL客戶端連線到OneProxy，須注意連線時，埠的寫OneProxy的埠，這裡是“3308”，如下所示：
# /usr/local/mysql5.6/bin/mysql -u testuser -h192.168.1.6 -P3308 -ptestuser
我們來對三張表做一個查詢試試：
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)
使用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裡可以做結果集的合併、排序、分頁、彙總統計操作。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29096438/viewspace-2071766/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- MySQL分庫分表MySql
- Mysql分庫分表方案MySql
- 徹底搞清MySQL分庫分表（垂直分庫，垂直分表，水平分庫，水平分表）MySql
- 3.1 MYSQL分庫分表實踐MySql
- MySQL分庫分表的原則MySql
- MySQL全面瓦解28：分庫分表MySql
- Linux MySQL分庫分表之MycatLinuxMySql
- MySql分表、分庫、分片和分割槽MySql
- MySQL分庫分表總結參考MySql
- MYSQL效能最佳化分享(分庫分表)MySql
- MySQL 分庫分表方案，總結太全了。。MySql
- 分庫分表系列：分庫分表的前世今生
- MySQL 常用分庫分表方案，都在這裡了！MySql
- 分庫分表注意
- MySQL資料庫之分庫分表方案MySql資料庫
- 資料庫分庫分表資料庫
- Mysql分庫分表的主鍵生成演算法MySql演算法
- MySQL訂單分庫分表多維度查詢MySql
- OneProxy5.8.1資料庫讀寫分離特性試驗資料庫
- mycat配置分庫分表
- Mycat分庫分表配置
- 分庫分表總結
- Mycat分庫分表（一）
- 常用分庫分表方案
- 基因法分庫分表
- MySQL 資料庫之網際網路常用分庫分表方案MySql資料庫
- [資料庫][分庫分表]分庫分表之後，id主鍵如何處理資料庫
- MySQL運維9-Mycat分庫分表之列舉分片MySql運維
- 使用淘寶中介軟體cobar實現mysql分庫分表MySql
- oracle分表效率,資料庫分庫分表是什麼，什麼情況下需要用分庫分表Oracle資料庫
- 徹底搞清分庫分表（垂直分庫，垂直分表，水平分庫，水平分表）
- MyCat分庫分表、讀寫分離
- Mycat 讀寫分離+分庫分表
- 讀寫分離 & 分庫分表 & 深度分頁
- 使用ShardingSphere-JDBC完成Mysql的分庫分表和讀寫分離JDBCMySql
- 輕鬆理解分庫分表
- 分庫分表插入資料
- Mycat分表分庫原則