sharding-sphere初体验

  |   1 评论   |   5,097 浏览

背景

Sharding-Sphere可能是目前最好用的数据库最强大了。

虽然刚出来不久,但是生命力旺盛。

其包含3个独立的产品组件

  • Sharding-JDBC:轻量级Java框架,可理解为增强版的JDBC驱动
  • Sharding-Proxy:透明的数据库代理,任何兼容MySQL协议的访问客户端
  • Sharding-Sidecar:Kubernetes或Mesos的云原生数据库代理

Sharding-JDBC

从易到难,先来Sharding-JDBC。

Sharding-JDBC是在调用方实现的分库分表。

即应用和Sharding-JDBC部署在同一个机器上。应用通过Sharding-JDBC来访问数据库,Sharding-JDBC判断连接哪个数据库的哪个表。

这种方式不需要部署一个单独的代理服务器。

下面来看个例子吧。

数据库示例数据

MySQL中间件cobar初体验中插件数据一节。

pom文件

    <dependency>
      <groupId>commons-dbcp</groupId>
      <artifactId>commons-dbcp</artifactId>
      <version>1.4</version>
    </dependency>
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>5.1.30</version>
    </dependency>
    <dependency>
      <groupId>io.shardingsphere</groupId>
      <artifactId>sharding-jdbc</artifactId>
      <version>3.0.0.M1</version>
    </dependency>

代码

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;
import java.util.Properties;
import java.util.concurrent.ConcurrentHashMap;

import javax.sql.DataSource;

import org.apache.commons.dbcp.BasicDataSource;

import io.shardingsphere.core.api.ShardingDataSourceFactory;
import io.shardingsphere.core.api.config.ShardingRuleConfiguration;
import io.shardingsphere.core.api.config.TableRuleConfiguration;
import io.shardingsphere.core.api.config.strategy.InlineShardingStrategyConfiguration;

public class MainShardingJDBC {
  public static void main(String[] args) throws SQLException {
    // 配置真实数据源
    Map<String, DataSource> dataSourceMap = new HashMap<>();

    // 配置第一个数据源
    BasicDataSource dataSource1 = new BasicDataSource();
    dataSource1.setDriverClassName("com.mysql.jdbc.Driver");
    dataSource1.setUrl("jdbc:mysql://localhost:3306/dbtest2");
    dataSource1.setUsername("note");
    dataSource1.setPassword("abeffect");
    dataSourceMap.put("ds_0", dataSource1);

    // 配置第二个数据源
    BasicDataSource dataSource2 = new BasicDataSource();
    dataSource2.setDriverClassName("com.mysql.jdbc.Driver");
    dataSource2.setUrl("jdbc:mysql://localhost:3306/dbtest3");
    dataSource2.setUsername("note");
    dataSource2.setPassword("abeffect");
    dataSourceMap.put("ds_1", dataSource2);

    // 配置Order表规则
    TableRuleConfiguration orderTableRuleConfig = new TableRuleConfiguration();
    orderTableRuleConfig.setLogicTable("dbtest");
    orderTableRuleConfig.setActualDataNodes("ds_${0..1}.tb2");

    // 配置分库 + 分表策略
    orderTableRuleConfig
        .setDatabaseShardingStrategyConfig(new InlineShardingStrategyConfiguration("id", "ds_${id % 2}"));

    // 我这边没分表
    orderTableRuleConfig.setTableShardingStrategyConfig(new InlineShardingStrategyConfiguration("id", "tb2"));

    // 配置分片规则
    ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
    shardingRuleConfig.getTableRuleConfigs().add(orderTableRuleConfig);

    // 获取数据源对象
    DataSource dataSource = ShardingDataSourceFactory.createDataSource(dataSourceMap, shardingRuleConfig,
        new ConcurrentHashMap<String, Object>(), new Properties());
    String sql = "SELECT * FROM tb2 where id = ?";
    try (Connection connection = dataSource.getConnection();
        PreparedStatement preparedStatement = connection.prepareStatement(sql)) {
      preparedStatement.setInt(1, 513);

      try (ResultSet resultSet = preparedStatement.executeQuery()) {
        while (resultSet.next()) {
          System.out.print("id:" + resultSet.getLong(1) + ", ");
          System.out.print("val:" + resultSet.getString(2) + ", ");
          System.out.println();
        }
      }
    }
  }
}

执行效果

22:52:01,343 |-INFO in ch.qos.logback.classic.LoggerContext[sharding-proxy-example] - Could NOT find resource [logback.groovy]
22:52:01,343 |-INFO in ch.qos.logback.classic.LoggerContext[sharding-proxy-example] - Could NOT find resource [logback-test.xml]
22:52:01,343 |-INFO in ch.qos.logback.classic.LoggerContext[sharding-proxy-example] - Found resource [logback.xml] at [file:/Users/xuqian/eclipse-workspace/testShardingSphere/target/classes/logback.xml]
22:52:01,343 |-WARN in ch.qos.logback.classic.LoggerContext[sharding-proxy-example] - Resource [logback.xml] occurs multiple times on the classpath.
22:52:01,343 |-WARN in ch.qos.logback.classic.LoggerContext[sharding-proxy-example] - Resource [logback.xml] occurs at [jar:file:/Users/xuqian/.m2/repository/io/shardingsphere/sharding-proxy/3.0.0.M1/sharding-proxy-3.0.0.M1.jar!/logback.xml]
22:52:01,343 |-WARN in ch.qos.logback.classic.LoggerContext[sharding-proxy-example] - Resource [logback.xml] occurs at [file:/Users/xuqian/eclipse-workspace/testShardingSphere/target/classes/logback.xml]
22:52:01,390 |-INFO in ch.qos.logback.classic.joran.action.ConfigurationAction - debug attribute not set
22:52:01,402 |-INFO in ch.qos.logback.classic.joran.action.ContextNameAction - Setting logger context name as [sharding-proxy-example]
22:52:01,402 |-INFO in ch.qos.logback.core.joran.action.AppenderAction - About to instantiate appender of type [ch.qos.logback.core.ConsoleAppender]
22:52:01,404 |-INFO in ch.qos.logback.core.joran.action.AppenderAction - Naming appender as [STDOUT]
22:52:01,411 |-INFO in ch.qos.logback.core.joran.action.NestedComplexPropertyIA - Assuming default type [ch.qos.logback.classic.encoder.PatternLayoutEncoder] for [encoder] property
22:52:01,448 |-INFO in ch.qos.logback.classic.joran.action.LevelAction - ROOT level set to INFO
22:52:01,448 |-INFO in ch.qos.logback.core.joran.action.AppenderRefAction - Attaching appender named [STDOUT] to Logger[ROOT]
22:52:01,448 |-INFO in ch.qos.logback.classic.joran.action.ConfigurationAction - End of configuration.
22:52:01,449 |-INFO in ch.qos.logback.classic.joran.JoranConfigurator@4988d8b8 - Registering current configuration as safe fallback point

id:513, val:part2, 

更多使用

shardingsphere官网中的sharding jdbc快速上手

Sharding-Proxy

Sharding-Proxy是一个透明的数据库代理。

使用

下载

wget https://github.com/sharding-sphere/sharding-sphere-doc/raw/master/dist/sharding-proxy-3.0.0.M1.tar.gz
tar xvf sharding-proxy-3.0.0.M1.tar.gz
cd sharding-proxy-3.0.0.M1

修改配置文件 conf/config.yaml,最简内容如下:

dataSources:
  ds_0:
    url: jdbc:mysql://127.0.0.1:3306/dbtest2
    username: note
    password: abeffect

连接数据库

[note@abeffect ~]$ mysql -h127.0.0.1 -P3307
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.5.59-Sharding-Proxy 2.1.0 Homebrew

Copyright (c) 2000, 2017, 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> show databases;
+-------------+
| Database    |
+-------------+
| sharding_db |
+-------------+
1 row in set (0.00 sec)

mysql> show tables;
+-------------------+
| Tables_in_dbtest2 |
+-------------------+
| tb2               |
+-------------------+
1 row in set (0.00 sec)

mysql> select * from tb2;
+------+-------+
| id   | val   |
+------+-------+
|    1 | part1 |
|    2 | part1 |
+------+-------+
2 rows in set (0.02 sec)

分库分表的方法请参考官方文档配置手册

小结

目前Sharding-JDBC和Sharding-Proxy的功能还是比较单一。

  • Sharding-JDBC: 针对任务数据库的,JDBC分库分表路由器。
  • Sharding-Proxy: 针对MySQL的分库分表透明代理。

对于仅仅支持分库非表场景,其它场景的话没有什么优势。

参考

评论

发表评论

validate