场景一：数据分片 #

此场景通过 DistSQL 命令行方式快速实现数据分片。

数据分片场景仅限于本地快速构建一个 SphereEx- DBPlusEngine 集群，并连接 MySQL 数据库，实现数据分片的场景，不包含其他组件，以及数据库的安装。

准备资源环境 #

	实例	IP 地址	服务端口	备注
1	SphereEx-DBPlusEngine	127.0.0.1	3308	SphereEx-DBPlusEngine 集群
2	ZooKeeper	127.0.0.1	2181	ZooKeeper
3	MySQL 8.0	127.0.0.1	13306	用于分片的数据库（需提前准备）

说明：操作时需要替换为实际环境中的 IP 地址、端口。

安装 SphereEx-DBPlusEngine 集群 #

安装步骤 #

安装 SphereEx-DBPlusEngine 集群请参考集群管理

登录集群 #

这里演示使用MySQL客户端进行连接。

# mysql -uroot -p -P3308 -h127.0.0.1
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 4
Server version: 5.7.22 sphereex-dbplusengine-proxy-1.2.0 
 
Copyright (c) 2000, 2021, 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>

如使用任何 PostgreSQL 的客户端连接。则通过: psql -U root -h 127.0.0.1 -p 3308 连接。
如使用任何 OpenGauss 的客户端连接。则通过: gsql -U root -h 127.0.0.1 -p 3308 连接。

数据分片 #

本章节将介绍使用 DistSQL（命令工具）如何创建数据分片。也可以使用 SphereEx-Console（SphereEx 企业数据服务可视化平台）实现数据分片功能。

操作步骤 #

数据分片操作步骤

创建分片 #

创建分布式数据库

a.创建数据源

登陆 MySQL 数据库: 127.0.0.1/13306，创建数据库 test01 和 test02，如：

# mysql -uroot -proot -P13306 -h127.0.0.1
sql> CREATE DATABASE test01;
Query OK, 1 row affected (0.00 sec)

sql> CREATE DATABASE test02;
Query OK, 1 row affected (0.00 sec)

sql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test01             |
| test02             |
+--------------------+
5 rows in set (0.01 sec)

b.创建逻辑库

登陆 Proxy

# mysql -uroot -proot -P3308 -h127.0.0.1
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 37
Server version: 5.7.22 SphereEx-DBPlusEngine-Proxy 1.2.0 MySQL Community Server (GPL)
 
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
 
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.
 
sql>

创建逻辑库

sql>  CREATE DATABASE testdb;
Query OK, 0 rows affected (0.65 sec)

查看逻辑库

sql> SHOW DATABASES;
+--------------------+
| schema_name        |
+--------------------+
| mysql              |
| information_schema |
| performance_schema |
| sys                |
| testdb             |
+--------------------+
5 rows in set (0.00 sec)

c.添加数据源

切换至逻辑库 testdb

sql> USE testdb;
Database changed

添加数据源

sql> REGISTER STORAGE UNIT test01 (
    ->     HOST="127.0.0.1",
    ->     PORT=13306,
    ->     DB="test01",
    ->     USER="root",
    ->     PASSWORD="root"
    -> ),test02 (
    ->     HOST="127.0.0.1",
    ->     PORT=13306,
    ->     DB="test02",
    ->     USER="root",
    ->     PASSWORD="root"
    -> );
Query OK, 0 rows affected (0.09 sec)

d.查看数据源信息

sql> SHOW STORAGE UNITS\G
*************************** 1. row ***************************
                           name: test02
                           type: MySQL
                           host: 127.0.0.1
                           port: 13306
                             db: test02
connection_timeout_milliseconds: 30000
      idle_timeout_milliseconds: 60000
      max_lifetime_milliseconds: 2100000
                  max_pool_size: 50
                  min_pool_size: 1
                      read_only: false
               other_attributes: {"dataSourceProperties":{"cacheServerConfiguration":"true","elideSetAutoCommits":"true","useServerPrepStmts":"true","cachePrepStmts":"true","useSSL":"false","rewriteBatchedStatements":"true","cacheResultSetMetadata":"false","useLocalSessionState":"true","maintainTimeStats":"false","prepStmtCacheSize":"8192","tinyInt1isBit":"false","prepStmtCacheSqlLimit":"2048","serverTimezone":"UTC","netTimeoutForStreamingResults":"0","zeroDateTimeBehavior":"round"},"healthCheckProperties":{},"initializationFailTimeout":1,"validationTimeout":5000,"leakDetectionThreshold":0,"registerMbeans":false,"allowPoolSuspension":false,"autoCommit":true,"isolateInternalQueries":false}
*************************** 2. row ***************************
                           name: test01
                           type: MySQL
                           host: 127.0.0.1
                           port: 13306
                             db: test01
connection_timeout_milliseconds: 30000
      idle_timeout_milliseconds: 60000
      max_lifetime_milliseconds: 2100000
                  max_pool_size: 50
                  min_pool_size: 1
                      read_only: false
               other_attributes: {"dataSourceProperties":{"cacheServerConfiguration":"true","elideSetAutoCommits":"true","useServerPrepStmts":"true","cachePrepStmts":"true","useSSL":"false","rewriteBatchedStatements":"true","cacheResultSetMetadata":"false","useLocalSessionState":"true","maintainTimeStats":"false","prepStmtCacheSize":"8192","tinyInt1isBit":"false","prepStmtCacheSqlLimit":"2048","serverTimezone":"UTC","netTimeoutForStreamingResults":"0","zeroDateTimeBehavior":"round"},"healthCheckProperties":{},"initializationFailTimeout":1,"validationTimeout":5000,"leakDetectionThreshold":0,"registerMbeans":false,"allowPoolSuspension":false,"autoCommit":true,"isolateInternalQueries":false}
2 rows in set (0.00 sec)

至此，分布式数据库创建完成。

创建分片表规则

选择逻辑库

sql> USE testdb;
Database changed

创建数据分片规则

sql> CREATE SHARDING TABLE RULE t_user(
    ->     STORAGE_UNITS(test01,test02),
    ->     SHARDING_COLUMN=user_id,
    ->     TYPE(NAME="hash_mod",PROPERTIES("sharding-count"=4))
    ->     );
Query OK, 0 rows affected (0.07 sec)

查看数据分片规则

sql> SHOW SHARDING TABLE RULE t_user\G
*************************** 1. row ***************************
                            table: t_user
                actual_data_nodes: 
              actual_data_sources: test01,test02
           database_strategy_type: 
         database_sharding_column: 
 database_sharding_algorithm_type: 
database_sharding_algorithm_props: 
              table_strategy_type: STANDARD
            table_sharding_column: user_id
    table_sharding_algorithm_type: hash_mod
   table_sharding_algorithm_props: sharding-count=4
              key_generate_column: 
               key_generator_type: 
              key_generator_props: 
                    auditor_types: 
               allow_hint_disable: 
1 row in set (0.01 sec)

创建表并插入数据

a.创建表

sql> CREATE TABLE `t_user` (
    ->  `user_id` int NOT NULL,
    ->  `order_id` int NOT NULL,
    ->  `status` varchar(45) DEFAULT NULL,
    ->  PRIMARY KEY (`user_id`)
    -> );
Query OK, 0 rows affected (0.28 sec)

b.插入数据

sql> INSERT INTO t_user VALUES
    -> (1,1,'active'),
    -> (2,2,'active'),
    -> (3,3,'active'),
    -> (4,4,'active');
Query OK, 4 rows affected (0.10 sec)

c.预览分片信息

sql> PREVIEW SELECT * FROM t_user;
+------------------+---------------------------------------------------------+
| data_source_name | actual_sql                                              |
+------------------+---------------------------------------------------------+
| test01           | SELECT * FROM t_user_0 UNION ALL SELECT * FROM t_user_2 |
| test02           | SELECT * FROM t_user_1 UNION ALL SELECT * FROM t_user_3 |
+------------------+---------------------------------------------------------+
2 rows in set (0.04 sec)

查看分片结果

登陆数据库 test01 ，查看实际分片数据

# mysql -uroot -proot -P13306 -h127.0.0.1
sql> use test01;
sql> select * from t_user_0;
+---------+----------+--------+
| user_id | order_id | status |
+---------+----------+--------+
|       4 |        4 | active |
+---------+----------+--------+
1 row in set (0.00 sec)

sql> select * from t_user_2;
+---------+----------+--------+
| user_id | order_id | status |
+---------+----------+--------+
|       2 |        2 | active |
+---------+----------+--------+
1 row in set (0.00 sec)

登陆数据库 test02 ，查看实际分片数据

sql> use test02;
sql> select * from t_user_1;
+---------+----------+--------+
| user_id | order_id | status |
+---------+----------+--------+
|       1 |        1 | active |
+---------+----------+--------+
1 row in set (0.00 sec)

sql> select * from t_user_3;
+---------+----------+--------+
| user_id | order_id | status |
+---------+----------+--------+
|       3 |        3 | active |
+---------+----------+--------+
1 row in set (0.00 sec)