分库分表实现有两种实现方式：代码；配置文件。

    2019-11-06_073007.png

    1、应用代码方式实现分库分表

    1. <parent>
    2.         <groupId>org.springframework.boot</groupId>
    3.         <artifactId>spring-boot-starter-parent</artifactId>
    4.         <version>2.0.4.RELEASE</version>
    5.         <relativePath />
    6.     </parent>
    8.     <dependencies>
    9.         <dependency>
    10.             <groupId>org.springframework.boot</groupId>
    11.             <artifactId>spring-boot-starter-web</artifactId>
    12.         </dependency>
    13.         <dependency>
    14.             <groupId>org.springframework.boot</groupId>
    15.             <artifactId>spring-boot-starter-data-jpa</artifactId>
    16.         </dependency>
    17.         <dependency>
    18.             <groupId>mysql</groupId>
    19.             <artifactId>mysql-connector-java</artifactId>
    20.         </dependency>
    21.         <dependency>
    22.             <groupId>io.shardingsphere</groupId>
    23.             <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
    24.             <version>3.0.0.M3</version>
    25.         </dependency>
    26.         <dependency>
    27.             <groupId>com.alibaba</groupId>
    28.             <artifactId>druid</artifactId>
    29.             <version>1.1.9</version>
    30.         </dependency>
    31.         <dependency>
    32.             <groupId>org.springframework.boot</groupId>
    33.             <artifactId>spring-boot-starter-test</artifactId>
    34.         </dependency>
    35.     </dependencies>
    37.     <build>
    38.         <plugins>
    39.             <plugin>
    40.                 <groupId>org.springframework.boot</groupId>
    41.                 <artifactId>spring-boot-maven-plugin</artifactId>
    42.             </plugin>
    43.         </plugins>
    44.     </build>
    1. ###数据库访问连接
    2. spring:
    3.   jdbc:
    4.     db0:
    5.       password: root
    6.       className: com.mysql.jdbc.Driver
    7.       url: jdbc:mysql://localhost:3306/%s?characterEncoding=utf-8
    8.       username: root
    9.   jpa: 
    10.     database: mysql 
    11.     show-sql: true 
    12.     hibernate: 
    13.     ## 自己建表 
    14.       ddl-auto: none 
    15.   application: 
    16.     name: sharding-jdbc-first

    配置分表算法

    1. package com.mayikt.config;
    3. import java.util.Collection;
    5. import com.dangdang.ddframe.rdb.sharding.api.ShardingValue;
    6. import com.dangdang.ddframe.rdb.sharding.api.strategy.table.SingleKeyTableShardingAlgorithm;
    8. public class TableShardingAlgorithm implements SingleKeyTableShardingAlgorithm<Long> {
    10.     // sql 中关键字 匹配符为 =的时候，表的路由函数
    11.     public String doEqualSharding(Collection<String> availableTargetNames, ShardingValue<Long> shardingValue) {
    12.         // availableTargetNames 分表的集合 t_order_0 和t_order_1
    13.         for (String tableName : availableTargetNames) {
    14.             // shardingValue.getValue() userid 分片字段值
    15.             // tableName = t_order_0 shardingValue.getValue()=2
    16.             // t_order_0 2%2=0
    17.             if (tableName.endsWith(shardingValue.getValue() % 2 + "")) {
    18.                 return tableName;
    19.             }
    20.         }
    22.         throw new IllegalArgumentException();
    23.     }
    25.     @Override
    26.     public Collection<String> doInSharding(Collection<String> availableTargetNames, ShardingValue<Long> shardingValue) {
    28.         return null;
    29.     }
    31.     @Override
    32.     public Collection<String> doBetweenSharding(Collection<String> availableTargetNames,
    33.             ShardingValue<Long> shardingValue) {
    35.         return null;
    36.     }
    38. }

    配置分库算法

    1. package com.mayikt.config;
    3. import java.util.Collection;
    5. import com.dangdang.ddframe.rdb.sharding.api.ShardingValue;
    6. import com.dangdang.ddframe.rdb.sharding.api.strategy.database.SingleKeyDatabaseShardingAlgorithm;
    8. // 分库策略配置
    9. public class DatabaseShardingAlgorithm implements SingleKeyDatabaseShardingAlgorithm<Long> {
    11.     // 考虑表的数据存放在那个库里面
    12.     @Override
    13.     public String doEqualSharding(Collection<String> databases, ShardingValue<Long> shardingValue) {
    14.         for (String databaseName : databases) {
    15.             if (databaseName.endsWith(shardingValue.getValue() % 2 + "")) {
    16.                 return databaseName;
    17.             }
    18.         }
    19.         throw new IllegalArgumentException();
    20.     }
    22.     @Override
    23.     public Collection<String> doInSharding(Collection<String> availableTargetNames, ShardingValue<Long> shardingValue) {
    25.         return null;
    26.     }
    28.     @Override
    29.     public Collection<String> doBetweenSharding(Collection<String> availableTargetNames,
    30.             ShardingValue<Long> shardingValue) {
    32.         return null;
    33.     }
    35. }

    数据源整合分库分表算法

    1. package com.mayikt.config;
    3. import java.util.Arrays;
    4. import java.util.HashMap;
    5. import java.util.Map;
    7. import javax.sql.DataSource;
    9. import org.springframework.beans.factory.annotation.Value;
    10. import org.springframework.context.annotation.Bean;
    11. import org.springframework.context.annotation.Configuration;
    13. import com.alibaba.druid.pool.DruidDataSource;
    14. import com.dangdang.ddframe.rdb.sharding.api.ShardingDataSourceFactory;
    15. import com.dangdang.ddframe.rdb.sharding.api.rule.DataSourceRule;
    16. import com.dangdang.ddframe.rdb.sharding.api.rule.ShardingRule;
    17. import com.dangdang.ddframe.rdb.sharding.api.rule.TableRule;
    18. import com.dangdang.ddframe.rdb.sharding.api.strategy.database.DatabaseShardingStrategy;
    19. import com.dangdang.ddframe.rdb.sharding.api.strategy.table.TableShardingStrategy;
    20. import com.dangdang.ddframe.rdb.sharding.id.generator.IdGenerator;
    21. import com.dangdang.ddframe.rdb.sharding.id.generator.self.CommonSelfIdGenerator;
    23. // 数据源相关配置信息
    24. @Configuration
    25. public class DataSourceConfig {
    26.     @Value("${spring.jdbc.db0.className}")
    27.     private String className;
    28.     @Value("${spring.jdbc.db0.url}")
    29.     private String url;
    30.     @Value("${spring.jdbc.db0.username}")
    31.     private String username;
    32.     @Value("${spring.jdbc.db0.password}")
    33.     private String password;
    35.     // 配置自动增长的策略
    36.     @Bean
    37.     public IdGenerator getIdGenerator() {
    38.         return new CommonSelfIdGenerator();
    39.     }
    41.     // 创建数据源
    42.     @Bean
    43.     public DataSource getDataSource() {
    44.         return buildDataSource();
    45.     }
    47.     private DataSource buildDataSource() {
    48.         // 1.设置分库映射
    49.         Map<String, DataSource> dataSourceMap = new HashMap<>(2);
    50.         dataSourceMap.put("ds_0", createDataSource("ds_0"));
    51.         dataSourceMap.put("ds_1", createDataSource("ds_1"));
    52.         // 设置默认db为ds_0，也就是为那些没有配置分库分表策略的指定的默认库
    53.         // 如果只有一个库，也就是不需要分库的话，map里只放一个映射就行了，只有一个库时不需要指定默认库，
    54.         // 但2个及以上时必须指定默认库，否则那些没有配置策略的表将无法操作数据
    55.         DataSourceRule rule = new DataSourceRule(dataSourceMap, "ds_0");
    56.         // 2.设置分表映射，将t_order_0和t_order_1两个实际的表映射到t_order逻辑表
    57.         TableRule orderTableRule = TableRule.builder("t_order")
    58.                 .actualTables(Arrays.asList("t_order_0", "t_order_1"))
    59.                 .dataSourceRule(rule).build();
    60.         // 3.具体的分库分表策略
    61.         ShardingRule shardingRule = ShardingRule.builder().dataSourceRule(rule)
    62.                 .tableRules(Arrays.asList(orderTableRule))
    63.                 .databaseShardingStrategy(new DatabaseShardingStrategy("user_id", new DatabaseShardingAlgorithm()))
    64.                 .tableShardingStrategy(new TableShardingStrategy("order_id", new TableShardingAlgorithm()))
    65.                 .build();
    66.         // 创建数据源
    67.         DataSource dataSource = ShardingDataSourceFactory.createDataSource(shardingRule);
    68.         return dataSource;
    69.     }
    71.     private DataSource createDataSource(String dataSourceName) {
    72.         // 使用druid连接数据库
    73.         DruidDataSource druidDataSource = new DruidDataSource();
    74.         druidDataSource.setDriverClassName(className);
    75.         druidDataSource.setUrl(String.format(url, dataSourceName));
    76.         druidDataSource.setUsername(username);
    77.         druidDataSource.setPassword(password);
    78.         return druidDataSource;
    79.     }
    80. }

    2、配置文件方式

    1. spring:
    2.   jpa:
    3.     show-sql: true
    4.     hibernate:
    5.       ddl-auto: none
    6.     database-platform: org.hibernate.dialect.MySQL5InnoDBDialect
    7. sharding:
    8.   jdbc:
    9.   ####ds1
    10.     datasource:
    11.       names: ds1
    12.       ds1:
    13.         password: root
    14.         type: com.alibaba.druid.pool.DruidDataSource
    15.         driver-class-name: com.mysql.jdbc.Driver
    16.         url: jdbc:mysql://127.0.0.1:3306/ds_1
    17.         username: root
    18.     config:
    19.       sharding:
    20.         tables:
    21.           t_order:
    22.             table-strategy:
    23.               inline:
    24.               #### 根据userid 进行分片
    25.                 sharding-column: user_id
    26.                 algorithm-expression: ds_1.t_order_$->{user_id % 2}
    27.             actual-data-nodes: ds1.t_order_$->{0..1}
    28.         props:
    29.           sql: 
    30.           ### 开启分片日志
    31.             show: true

    https://blog.csdn.net/tianyaleixiaowu/article/details/70242971
    https://www.cnblogs.com/cw828/p/11684476.html

    MyCat执行原理分析与初始Sharding-Jdbc.pptxSharding-Jdbc分片集群分表分库.docx资料.zip