MySQL 分库分表-ShardingSphere使用

分库和分表的实现-java工程版

  1. 依赖项
<dependencies>
		<!-- 主要 -->
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-core</artifactId>
            <version>4.0.0-RC2</version>
        </dependency>

        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
        </dependency>
        <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.41</version>
        </dependency>
        <dependency>
            <groupId>io.netty</groupId>
            <artifactId>netty</artifactId>
            <version>3.7.0.Final</version>
        </dependency>

    </dependencies>
  1. 示例代码
public class ShardingSphereDemo {

    @Test
    public void test01() throws SQLException {
        // 配置真实数据源
        Map<String, DataSource> dataSourceMap = new HashMap<String, DataSource>();

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

        // 配置表规则
        TableRuleConfiguration orderTableRuleConfig = new TableRuleConfiguration(
                "user", "test.user_${1..2}");

        orderTableRuleConfig.setTableShardingStrategyConfig(
                new InlineShardingStrategyConfiguration("id", "user_${id % 2}"));
        orderTableRuleConfig.setKeyGeneratorConfig(new KeyGeneratorConfiguration("SNOWFLAKE", "id"));

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

        // 省略配置order_item表规则...
        // ...

        // 获取数据源对象
        DataSource dataSource = ShardingDataSourceFactory.createDataSource(dataSourceMap, shardingRuleConfig, new Properties());


        Connection connection = dataSource.getConnection();
        System.out.println(connection);

        Statement statement = connection.createStatement();
        ResultSet resultSet = statement.executeQuery("select * from  user");
        while (resultSet.next()) {
            String username = resultSet.getString("username");
            String passwd = resultSet.getString("passwd");
            System.out.println("username=" + username + "\t" + "passwd=" + passwd);
        }

        statement.execute("insert  into  user(username, passwd) values ('林金保2', 'Gepoint')");

    }
}

实现-Spring 容器管理代码实现

  1. xml配置
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xmlns:tx="http://www.springframework.org/schema/tx"
	xmlns:context="http://www.springframework.org/schema/context"
	xmlns:jdbc="http://www.springframework.org/schema/jdbc"
	xmlns:sharding="http://shardingsphere.apache.org/schema/shardingsphere/sharding/sharding.xsd"
	xsi:schemaLocation="http://www.springframework.org/schema/jdbc http://www.springframework.org/schema/jdbc/spring-jdbc-4.3.xsd
		http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
		http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.3.xsd
		http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-4.3.xsd">

	<!-- 数据源 -->
	<bean id="comboPooledDataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
		<property name="user" value="root"></property>
		<property name="password" value="Gepoint"></property>
		<property name="jdbcUrl" value="jdbc:mysql://localhost:3306/test"></property>
		<property name="driverClass" value="com.mysql.jdbc.Driver"></property>
		<property name="initialPoolSize" value="10"></property>
		<property name="maxPoolSize" value="100"></property>
		<property name="minPoolSize" value="10"></property>
		<property name="maxIdleTime" value="30"></property>
	</bean>
	
	<!-- 包扫描 -->
	<context:component-scan base-package="amrom.dao,amrom.service"></context:component-scan>	
	
	<!-- 事务管理器 -->
	<bean id="dataSourceTransactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
		<property name="dataSource" ref="comboPooledDataSource"></property>
	</bean>
	<!-- 开启注解 -->
	<tx:annotation-driven transaction-manager="dataSourceTransactionManager" />
	
	<!-- shardingsphere配置 -->
		
	<bean id="inlineShardingStrategyConfiguration" class="org.apache.shardingsphere.api.config.sharding.strategy.InlineShardingStrategyConfiguration">
		<constructor-arg name="shardingColumn" value="id"></constructor-arg>
		<constructor-arg name="algorithmExpression" value="user_${id % 2}"></constructor-arg>
	</bean>
	<bean class="org.apache.shardingsphere.api.config.sharding.TableRuleConfiguration">
		<constructor-arg name="logicTable" value="user"></constructor-arg>
		<constructor-arg name="actualDataNodes" value="test.user_${1..2}"></constructor-arg>
		<property name="tableShardingStrategyConfig" ref="inlineShardingStrategyConfiguration"></property>
		<property name="keyGeneratorConfig">
			<bean class="org.apache.shardingsphere.api.config.sharding.KeyGeneratorConfiguration">
				<constructor-arg name="type" value="SNOWFLAKE"></constructor-arg>
				<constructor-arg name="column" value="id"></constructor-arg>
			</bean>
		</property>
	</bean>
	<bean class="org.apache.shardingsphere.api.config.sharding.ShardingRuleConfiguration"></bean>
	<bean class="org.apache.shardingsphere.shardingjdbc.api.ShardingDataSourceFactory"></bean>		
</beans>


  1. ShardingDemo.java
public class ShardingDemo {
	
	ApplicationContext context = new ClassPathXmlApplicationContext("applicationcontext.xml");
	
	@Test
	public void test01() throws SQLException {
		Map<String, DataSource> dataSourceMap = new HashMap<String, DataSource>();
		DataSource dataSource1 = context.getBean("comboPooledDataSource",ComboPooledDataSource.class);
		dataSourceMap.put("test", dataSource1);
		//容器中取出
		TableRuleConfiguration tableRuleConfiguration =  context.getBean(TableRuleConfiguration.class);
		ShardingRuleConfiguration shardingRuleConfiguration = context.getBean(ShardingRuleConfiguration.class);
		shardingRuleConfiguration.getTableRuleConfigs().add(tableRuleConfiguration);
		
        DataSource dataSource = ShardingDataSourceFactory.createDataSource(dataSourceMap, shardingRuleConfiguration, new Properties());
        Connection connection = dataSource.getConnection();
        System.out.println(connection);

        Statement statement = connection.createStatement();
        ResultSet resultSet = statement.executeQuery("select * from  user");
        while (resultSet.next()) {
            String username = resultSet.getString("username");
            String passwd = resultSet.getString("passwd");
            System.out.println("username=" + username + "\t" + "passwd=" + passwd);
        }
        statement.execute("insert  into  user(username, passwd) values ('林金保5', 'Gepoint')");
	}

}

步骤解释

  1. 配置真实数据源dataSourceMap,在此处允许配置多个数据库,实现分库

  2. 配置库规则和表规则,ShardingSphere会根据此规则解析,生成真实sql语句

  3. 注意此处有虚拟表名的概念,即使用user表加上上面的表规则表示user_1user_2两张表,查询语句只需要写user即可

  4. insert数据时,需要数据库user_1user_2满足主键不重复原则,此处使用Shard ingSphere提供的SNOWFLAKE实现

总结

核心思想:在数据库和java代码之间多了一层,用户写的sql是逻辑sql,由shardingsphere根据配置方式,生成真实sql,并操作数据库连接执行,隔绝了程序员与真实数据库之间的直接连接