登录 注册

sharding-sphere 初体验

本贴最后更新于 2230 天前，其中的信息可能已经时过境迁

背景

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 的分库分表透明代理。

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

参考

  • 数据库

    据说 99% 的性能瓶颈都在数据库。

    333 引用 • 619 回帖
北京 位置
1 关注
1 回帖
2.8k 26 603 354 149 99 13 206 1.3k

相关帖子

1 回帖
sharding-sphere 初体验

欢迎来到这里！

我们正在构建一个小众社区，大家在这里相互信任，以平等 • 自由 • 奔放的价值观进行分享交流。最终，希望大家能够找到与自己志同道合的伙伴，共同成长。

注册 关于
请输入回帖内容 ...
flowaters
爱生活，爱折腾！ 北京
回帖
15
 帖子
109
 积分
3249