Java中ShardingSphere 數(shù)據(jù)分片的實(shí)現(xiàn)

更新時(shí)間：2021年09月24日 15:32:43 作者：胖虎。。

其實(shí)很多人對(duì)分庫分表多少都有點(diǎn)恐懼，我們今天用ShardingSphere 給大家演示數(shù)據(jù)分片，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下

前言

其實(shí)很多人對(duì)分庫分表多少都有點(diǎn)恐懼，其實(shí)我也是，總覺得這玩意是運(yùn)維干的、數(shù)據(jù)量上來了或者sql過于復(fù)雜、一些數(shù)據(jù)分片的中間件支持的也不是很友好、配置繁瑣等多種問題。

我們今天用ShardingSphere 給大家演示數(shù)據(jù)分片，包括分庫分表、只分表不分庫進(jìn)行說明。

下一節(jié)有時(shí)間的話在講講讀寫分離吧。

github地址：https://github.com/362460453/boot-sharding-JDBC

ShardingSphere介紹

ShardingSphere是一套開源的分布式數(shù)據(jù)庫中間件解決方案組成的生態(tài)圈，它由Sharding-JDBC、Sharding-Proxy和Sharding-Sidecar（計(jì)劃中）這3款相互獨(dú)立的產(chǎn)品組成。他們均提供標(biāo)準(zhǔn)化的數(shù)據(jù)分片、分布式事務(wù)和數(shù)據(jù)庫治理功能，可適用于如Java同構(gòu)、異構(gòu)語言、容器、云原生等各種多樣化的應(yīng)用場(chǎng)景。

ShardingSphere的功能能幫助我們做什么

數(shù)據(jù)分片
讀寫分離
編排治理
分布式事務(wù)

2016年初Sharding-JDBC被開源，這個(gè)產(chǎn)品是當(dāng)當(dāng)?shù)?，加入了Apache 后改名為 ShardingSphere 。他是我們應(yīng)用和數(shù)據(jù)庫之間的中間層，雖代碼入侵性很強(qiáng)，但不會(huì)對(duì)現(xiàn)有業(yè)務(wù)邏輯進(jìn)行改變。

更多文檔請(qǐng)點(diǎn)擊官網(wǎng)：https://shardingsphere.apache.org/document/current/en/overview/

為什么不用mycat

大家如果去查相關(guān)資料會(huì)知道，mycat和ShardingSphere是同類型的中間件，主要的功能，數(shù)據(jù)分片和讀寫分離兩個(gè)都能去做，但是姿勢(shì)卻有很大的差別，從字面意義上看Sharding 含義是分片、碎片的意思，所以不難理解ShardingSphere 對(duì)數(shù)據(jù)分片有很強(qiáng)對(duì)能力，對(duì)于99%對(duì)sql都是支持的，官網(wǎng)也有sql支持的相關(guān)內(nèi)容，大家詳細(xì)閱讀，只有類似sum 這種函數(shù)不支持，而且對(duì) ORM框架和常用數(shù)據(jù)庫基本都兼容，所以個(gè)人建議如果你們做數(shù)據(jù)分片，也就是是分庫分表對(duì)話，強(qiáng)烈建議選擇ShardingSphere，因?yàn)槲宜较乱埠鸵恍┡笥呀涣鬟^，mycat 的數(shù)據(jù)分片對(duì)多表查詢不是很友好，而且用 mycat 要有很強(qiáng)的運(yùn)維來做，還有一點(diǎn)就是mycat 都是靠xml配置的，沒有代碼入侵，所以這也算是他的優(yōu)點(diǎn)吧。如果你們只做讀寫分離對(duì)話，那么我建議用mycat，是沒問題的。

實(shí)踐前的準(zhǔn)備工作

啟動(dòng)你的mysql，創(chuàng)建兩個(gè)數(shù)據(jù)庫，分別叫 sharding_master 和 sharding_salve分別在這兩個(gè)數(shù)據(jù)庫執(zhí)行如下sql

CREATE TABLE IF NOT EXISTS `t_order_0` (
  `order_id` INT NOT NULL,
  `user_id`  INT NOT NULL,
  PRIMARY KEY (`order_id`)
);
CREATE TABLE IF NOT EXISTS `t_order_1` (
  `order_id` INT NOT NULL,
  `user_id`  INT NOT NULL,
  PRIMARY KEY (`order_id`)
);

做完以上兩步結(jié)果如下

代碼案例

環(huán)境

工具	版本
jdk	1.8.0_144
springboot	2.0.4.RELEASE
sharding	1.3.1
mysql	5.7

創(chuàng)建一個(gè)springboot工程，我們使用 JdbcTemplate 框架，如果用mybatis也是無影響的。

pom引用依賴如下

<parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.0.4.RELEASE</version>
    </parent>
 
    <properties>
        <java.version>1.8</java.version>
        <druid.version>1.0.26</druid.version>
        <sharding.jdbc.core.version>1.3.3</sharding.jdbc.core.version>
    </properties>
 
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
        <dependency>
            <groupId>com.dangdang</groupId>
            <artifactId>sharding-jdbc-core</artifactId>
            <version>${sharding.jdbc.core.version}</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>${druid.version}</version>
        </dependency>
    </dependencies>

application.yml 配置如下

server:
  port: 8050
sharding:
  jdbc: 
    driverClassName: com.mysql.jdbc.Driver
    url: jdbc:mysql://localhost:3306/sharding_master?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&failOverReadOnly=false
    username: root
    password: 123456
    filters: stat
    maxActive: 100
    initialSize: 1
    maxWait: 15000
    minIdle: 1
    timeBetweenEvictionRunsMillis: 30000
    minEvictableIdleTimeMillis: 180000
    validationQuery: SELECT 'x'
    testWhileIdle: true
    testOnBorrow: false
    testOnReturn: false
    poolPreparedStatements: false
    maxPoolPreparedStatementPerConnectionSize: 20
    removeAbandoned: true
    removeAbandonedTimeout: 600
    logAbandoned: false
    connectionInitSqls: 
    
    url0: jdbc:mysql://localhost:3306/sharding_master?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&failOverReadOnly=false
    username0: root
    password0: 123456
    
    url1: jdbc:mysql://localhost:3306/sharding_salve?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&failOverReadOnly=false
    username1: root
    password1: 123456

yml映射成Bean

@Data
@ConfigurationProperties(prefix="sharding.jdbc")
public class ShardDataSourceProperties {
	
	private String driverClassName;
	
	private String url;
	
	private String username;
	
	private String password;
	
	private String url0;
	
	private String username0;
	
	private String password0;
	
	private String url1;
	
	private String username1;
	
	private String password1;
	
	private String filters;
	
	private int maxActive;
	
	private int initialSize;
	
	private int maxWait;
	
	private int minIdle;
	
	private int timeBetweenEvictionRunsMillis;
	
	private int minEvictableIdleTimeMillis;
	
	private String validationQuery;
	
	private boolean testWhileIdle;
	
	private boolean testOnBorrow;
	
	private boolean testOnReturn;
	
	private boolean poolPreparedStatements;
	
	private int maxPoolPreparedStatementPerConnectionSize;
	
	private boolean removeAbandoned;
 
	private int removeAbandonedTimeout;
	
	private boolean logAbandoned;
	
	private List<String> connectionInitSqls;
//省略geter setter

分庫策略

//通過實(shí)現(xiàn)SingleKeyDatabaseShardingAlgorithm接口實(shí)現(xiàn)分庫
public class ModuloDatabaseShardingAlgorithm implements SingleKeyDatabaseShardingAlgorithm<Integer> {
 
	@Override
	public String doEqualSharding(Collection<String> availableTargetNames, ShardingValue<Integer> shardingValue) {
		for (String each : availableTargetNames) {
            if (each.endsWith(shardingValue.getValue() % 2 + "")) {
                return each;
            }
        }
        throw new IllegalArgumentException();
	}
 
	@Override
	public Collection<String> doInSharding(Collection<String> availableTargetNames,
			ShardingValue<Integer> shardingValue) {
		Collection<String> result = new LinkedHashSet<>(availableTargetNames.size());
        for (Integer value : shardingValue.getValues()) {
            for (String targetName : availableTargetNames) {
                if (targetName.endsWith(value % 2 + "")) {
                    result.add(targetName);
                }
            }
        }
        return result;
	}
 
	@Override
	public Collection<String> doBetweenSharding(Collection<String> availableTargetNames,
			ShardingValue<Integer> shardingValue) {
		Collection<String> result = new LinkedHashSet<>(availableTargetNames.size());
        Range<Integer> range = (Range<Integer>) shardingValue.getValueRange();
        for (Integer i = range.lowerEndpoint(); i <= range.upperEndpoint(); i++) {
            for (String each : availableTargetNames) {
                if (each.endsWith(i % 2 + "")) {
                    result.add(each);
                }
            }
        }
        return result;
	}
}

分表策略

public class ModuloTableShardingAlgorithm implements SingleKeyTableShardingAlgorithm<Integer> {
 
    /**
     * 對(duì)于分片字段的等值操作 都走這個(gè)方法。(包括 插入 更新)
     * 如：
     * <p>
     * select * from t_order from t_order where order_id = 11
     * └── SELECT *  FROM t_order_1 WHERE order_id = 11
     * select * from t_order from t_order where order_id = 44
     * └── SELECT *  FROM t_order_0 WHERE order_id = 44
     * </P>
     */
	@Override
    public String doEqualSharding(final Collection<String> tableNames, final ShardingValue<Integer> shardingValue) {
        for (String each : tableNames) {
            if (each.endsWith(shardingValue.getValue() % 2 + "")) {
                return each;
            }
        }
        throw new IllegalArgumentException();
    }
    
    /**
     * 對(duì)于分片字段的in操作，都走這個(gè)方法。
    *  select * from t_order from t_order where order_id in (11,44)  
    *          ├── SELECT *  FROM t_order_0 WHERE order_id IN (11,44) 
    *          └── SELECT *  FROM t_order_1 WHERE order_id IN (11,44) 
    *  select * from t_order from t_order where order_id in (11,13,15)  
    *          └── SELECT *  FROM t_order_1 WHERE order_id IN (11,13,15)  
    *  select * from t_order from t_order where order_id in (22,24,26)  
    *          └──SELECT *  FROM t_order_0 WHERE order_id IN (22,24,26) 
    */
	@Override
    public Collection<String> doInSharding(final Collection<String> tableNames, final ShardingValue<Integer> shardingValue) {
        Collection<String> result = new LinkedHashSet<>(tableNames.size());
        for (Integer value : shardingValue.getValues()) {
            for (String tableName : tableNames) {
                if (tableName.endsWith(value % 2 + "")) {
                    result.add(tableName);
                }
            }
        }
        return result;
    }
    
    /**
     * 對(duì)于分片字段的between操作都走這個(gè)方法。
    *  select * from t_order from t_order where order_id between 10 and 20 
    *          ├── SELECT *  FROM t_order_0 WHERE order_id BETWEEN 10 AND 20 
    *          └── SELECT *  FROM t_order_1 WHERE order_id BETWEEN 10 AND 20 
    */
	@Override
    public Collection<String> doBetweenSharding(final Collection<String> tableNames, final ShardingValue<Integer> shardingValue) {
        Collection<String> result = new LinkedHashSet<>(tableNames.size());
        Range<Integer> range = (Range<Integer>) shardingValue.getValueRange();
        for (Integer i = range.lowerEndpoint(); i <= range.upperEndpoint(); i++) {
            for (String each : tableNames) {
                if (each.endsWith(i % 2 + "")) {
                    result.add(each);
                }
            }
        }
        return result;
    } 
}

對(duì)特定表和庫，進(jìn)行特定的分庫分表規(guī)則

簡(jiǎn)單說，就是分庫按照了user_id的奇偶區(qū)分，分表按照order_id 的奇偶區(qū)分，

如果你有多個(gè)表進(jìn)行分片，就寫多個(gè)TableRule，

配置兩個(gè)數(shù)據(jù)源，分別是我在yml里的配置，根據(jù)你的需求個(gè)性化配置就可以。

@Configuration
@EnableConfigurationProperties(ShardDataSourceProperties.class)
public class ShardDataSourceConfig {
 
	@Autowired
	private ShardDataSourceProperties shardDataSourceProperties;
 
	private DruidDataSource parentDs() throws SQLException {
		DruidDataSource ds = new DruidDataSource();
		ds.setDriverClassName(shardDataSourceProperties.getDriverClassName());
		ds.setUsername(shardDataSourceProperties.getUsername());
		ds.setUrl(shardDataSourceProperties.getUrl());
		ds.setPassword(shardDataSourceProperties.getPassword());
		ds.setFilters(shardDataSourceProperties.getFilters());
		ds.setMaxActive(shardDataSourceProperties.getMaxActive());
		ds.setInitialSize(shardDataSourceProperties.getInitialSize());
		ds.setMaxWait(shardDataSourceProperties.getMaxWait());
		ds.setMinIdle(shardDataSourceProperties.getMinIdle());
		ds.setTimeBetweenEvictionRunsMillis(shardDataSourceProperties.getTimeBetweenEvictionRunsMillis());
		ds.setMinEvictableIdleTimeMillis(shardDataSourceProperties.getMinEvictableIdleTimeMillis());
		ds.setValidationQuery(shardDataSourceProperties.getValidationQuery());
		ds.setTestWhileIdle(shardDataSourceProperties.isTestWhileIdle());
		ds.setTestOnBorrow(shardDataSourceProperties.isTestOnBorrow());
		ds.setTestOnReturn(shardDataSourceProperties.isTestOnReturn());
		ds.setPoolPreparedStatements(shardDataSourceProperties.isPoolPreparedStatements());
		ds.setMaxPoolPreparedStatementPerConnectionSize(
				shardDataSourceProperties.getMaxPoolPreparedStatementPerConnectionSize());
		ds.setRemoveAbandoned(shardDataSourceProperties.isRemoveAbandoned());
		ds.setRemoveAbandonedTimeout(shardDataSourceProperties.getRemoveAbandonedTimeout());
		ds.setLogAbandoned(shardDataSourceProperties.isLogAbandoned());
		ds.setConnectionInitSqls(shardDataSourceProperties.getConnectionInitSqls());
		return ds;
	}
 
	private DataSource ds0() throws SQLException {
		DruidDataSource ds = parentDs();
		ds.setUsername(shardDataSourceProperties.getUsername0());
		ds.setUrl(shardDataSourceProperties.getUrl0());
		ds.setPassword(shardDataSourceProperties.getPassword0());
		return ds;
	}
 
	private DataSource ds1() throws SQLException {
		DruidDataSource ds = parentDs();
		ds.setUsername(shardDataSourceProperties.getUsername1());
		ds.setUrl(shardDataSourceProperties.getUrl1());
		ds.setPassword(shardDataSourceProperties.getPassword1());
		return ds;
	}
 
	private DataSourceRule dataSourceRule() throws SQLException {
		Map<String, DataSource> dataSourceMap = new HashMap<>(2);
		dataSourceMap.put("ds_0", ds0());
		dataSourceMap.put("ds_1", ds1());
		DataSourceRule dataSourceRule = new DataSourceRule(dataSourceMap);
		return dataSourceRule;
	}
//對(duì)order對(duì)策略
	private TableRule orderTableRule() throws SQLException {
		TableRule orderTableRule = TableRule.builder("t_order").actualTables(Arrays.asList("t_order_0", "t_order_1"))
				.dataSourceRule(dataSourceRule()).build();
		return orderTableRule;
	}
 
//分庫分表策略
	private ShardingRule shardingRule() throws SQLException {
		ShardingRule shardingRule = ShardingRule.builder().dataSourceRule(dataSourceRule())
				.tableRules(Arrays.asList(orderTableRule(), orderItemTableRule()))
				.databaseShardingStrategy(
						new DatabaseShardingStrategy("user_id", new ModuloDatabaseShardingAlgorithm()))
				.tableShardingStrategy(new TableShardingStrategy("order_id", new ModuloTableShardingAlgorithm()))
				.build();
		return shardingRule;
	}
 
	@Bean
	public DataSource dataSource() throws SQLException {
		return ShardingDataSourceFactory.createDataSource(shardingRule());
	}
 
 
    @Bean
    public PlatformTransactionManager transactionManager() throws SQLException {
        return new DataSourceTransactionManager(dataSource());
    }
}

我們需要從controller調(diào)用接口進(jìn)行對(duì)數(shù)據(jù)的增加和查詢

下面所有的類都是用來模擬請(qǐng)求進(jìn)行測(cè)試

@RestController
@RequestMapping("/order")
public class OrderController {
    @Autowired
    private OrderDao orderDao;
 
    @RequestMapping(path = "/createOrder/{userId}/{orderId}", method = {RequestMethod.GET})
    public String createOrder(@PathVariable("userId") Integer userId, @PathVariable("orderId") Integer orderId) {
        Order order = new Order();
        order.setOrderId(orderId);
        order.setUserId(userId);
        orderDao.createOrder(order);
        return "success";
    }
 
    @RequestMapping(path = "/{userId}", method = {RequestMethod.GET})
    public List<Order> getOrderListByUserId(@PathVariable("userId") Integer userId) {
        return orderDao.getOrderListByUserId(userId);
    }
}
 
 
---------------------------------------------------
public interface OrderDao {
    List<Order> getOrderListByUserId(Integer userId);
 
    void createOrder(Order order);
}
---------------------------------------------------
@Service
public class OrderDaoImpl implements OrderDao {
    @Autowired
    JdbcTemplate jdbcTemplate;
 
 
    @Override
    public List<Order> getOrderListByUserId(Integer userId) {
 
        StringBuilder sqlBuilder = new StringBuilder();
        sqlBuilder
                .append("select order_id, user_id from t_order where user_id=? ");
        return jdbcTemplate.query(sqlBuilder.toString(), new Object[]{userId},
                new int[]{Types.INTEGER}, new BeanPropertyRowMapper<Order>(
                        Order.class));
    }
 
    @Override
    public void createOrder(Order order) {
        StringBuffer sb = new StringBuffer();
        sb.append("insert into t_order(user_id, order_id)");
        sb.append("values(");
        sb.append(order.getUserId()).append(",");
        sb.append(order.getOrderId());
        sb.append(")");
        jdbcTemplate.update(sb.toString());
 
    }
}
 
---------------------------------------------------
public class Order implements Serializable {
 
	private int userId;
 
	private int orderId;
 
---------------------------------------------------
@SpringBootApplication
public class Application {
	
	public static void main(String[] args) {
        SpringApplication.run(Application.class, args);
    }
}

測(cè)試

啟動(dòng)項(xiàng)目，訪問：http://localhost:8050/order/createOrder/1/1

更換參數(shù)多次訪問，可以插入多條記錄，觀察你的數(shù)據(jù)庫入庫情況，已經(jīng)按照我們制定的分庫分表策略進(jìn)行劃分了。

需要注意的是

shareding是不支持jdbctemplate的批量修改操作的。

表名前不要加上庫名，原生的情況加庫名，不加庫名其實(shí)是一樣的，但使用shareding的表就會(huì)報(bào)錯(cuò)。

如果想進(jìn)行只分表不分庫的話

注釋掉 ModuloDatabaseShardingAlgorithm 類
還有ShardDataSourceConfig.shardingRule() 中的分庫策略那行代碼
還有相關(guān)數(shù)據(jù)源配置改成 1 個(gè)

到此這篇關(guān)于Java中ShardingSphere 數(shù)據(jù)分片的實(shí)現(xiàn)的文章就介紹到這了,更多相關(guān)ShardingSphere 數(shù)據(jù)分片內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: