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>
    
  2. 示例代码

    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>
    
    
    
  2. 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,并操作数据库连接执行,隔绝了程序员与真实数据库之间的直接连接