数据库分库分表

1，概念

• 分库分表是一种数据库架构设计的方法，用于解决大规模数据存储和查询的性能问题。它将一个大型数据库拆分成多个小型数据库，每个数据库负责存储一部分数据，从而提高数据的读写效率和并发处理能力。

• 分库分表的优势在于可以将数据分散存储在多个物理节点上，减轻单个数据库的负载压力，提高系统的可扩展性和稳定性。同时，通过将数据按照一定规则进行分片，可以实现数据的并行处理，提高查询的响应速度。

• 分库分表技术被广泛应用于互联网行业，特别是大型的电商、社交网络和金融等领域。它不仅能够提升系统的性能，还能够降低成本和维护的复杂性。

ShardingSphere开源的分布式数据库中间件，，可以实现数据库的分库分表功能。它提供了一套完整的分库分表解决方案，可以将一个大型的数据库拆分成多个小型的数据库，从而提高数据库的性能和扩展性。

2、SpringBoot+ShardingSphere案例

官方文档：https://shardingsphere.apache.org/document/legacy/4.x/document/cn/quick-start/sharding-jdbc-quick-start/

2.1、pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/2.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"xsi:schemaLocation="http://maven.apache.org/POM/2.0.0 https://maven.apache.org/xsd/maven-2.0.0.xsd"><modelVersion>2.0.0</modelVersion><parent><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-parent</artifactId><version>2.7.1</version><relativePath/> <!-- lookup parent from repository --></parent><groupId>com.itsoku</groupId><artifactId>sj-demo1</artifactId><version>0.0.1-SNAPSHOT</version><name>sj-demo1</name><description>Demo project for Spring Boot</description><properties><java.version>1.8</java.version></properties><dependencies><dependency><groupId>org.mybatis.spring.boot</groupId><artifactId>mybatis-spring-boot-starter</artifactId><version>2.2.2</version></dependency><dependency><groupId>mysql</groupId><artifactId>mysql-connector-java</artifactId></dependency><dependency><groupId>org.projectlombok</groupId><artifactId>lombok</artifactId><optional>true</optional></dependency><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-test</artifactId><scope>test</scope></dependency><dependency><groupId>org.apache.shardingsphere</groupId><artifactId>sharding-jdbc-core</artifactId><version>2.1.1</version></dependency></dependencies><build><plugins><plugin><groupId>org.springframework.boot</groupId><artifactId>spring-boot-maven-plugin</artifactId><configuration><excludes><exclude><groupId>org.projectlombok</groupId><artifactId>lombok</artifactId></exclude></excludes></configuration></plugin></plugins></build></project>

2.2、单库多表

单库多表是一种数据库设计和管理的方法，数据库中创建多个表来存储不同类型的数据。这种方法可以提高数据库的性能和扩展性，减少单个表的数据量和查询负载。

create table if not exists order_table_0(order_id int not null primary key comment '订单id',user_id int not null comment '用户id',price double  not null comment '价格'
)charset utf8;
create table if not exists order_table_1(order_id int not null primary key comment '订单id',user_id int not null comment '用户id',price double  not null comment '价格'
)charset utf8;

java代码

package com.sin.sharding;import com.zaxxer.hikari.HikariDataSource;
import org.apache.shardingsphere.api.config.sharding.ShardingRuleConfiguration;
import org.apache.shardingsphere.api.config.sharding.TableRuleConfiguration;
import org.apache.shardingsphere.api.config.sharding.strategy.InlineShardingStrategyConfiguration;
import org.apache.shardingsphere.shardingjdbc.api.ShardingDataSourceFactory;
import org.apache.shardingsphere.underlying.common.config.properties.ConfigurationPropertyKey;import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.util.HashMap;
import java.util.Map;
import java.util.Properties;/*** @CreateName SIN* @CreateDate 2023/06/29 9:47* @description*/
public class DemoSharding {public static void main(String[] args)throws Exception {/*** 1、配置真实数据源*/Map<String, DataSource> dataSourceMap = new HashMap<>();dataSourceMap.put("demo1", dataSource1());/*** 2.配置表的规则*/TableRuleConfiguration orderTableRuleConfig = new TableRuleConfiguration("order_table", "demo1.order_table_$->{0..1}");// 指定表的分片策略（分片字段+分片算法）orderTableRuleConfig.setTableShardingStrategyConfig(new InlineShardingStrategyConfiguration("order_id", "order_table_$->{order_id % 2}"));/*** 3、分片规则*/ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();//将表的分片规则加入到分片规则列表shardingRuleConfig.getTableRuleConfigs().add(orderTableRuleConfig);/*** 4、配置一些属性*/Properties props = new Properties();//输出sqlprops.put(ConfigurationPropertyKey.SQL_SHOW.getKey(), true);/*** 5、创建数据源*/DataSource dataSource = ShardingDataSourceFactory.createDataSource(dataSourceMap, shardingRuleConfig, props);/*** 6、获取连接，执行sql*/Connection connection = dataSource.getConnection();connection.setAutoCommit(false);/*** 测试向t_order表插入8条数据，8条数据会分散到2个表*/PreparedStatement ps = connection.prepareStatement("insert into order_table (order_id,user_id,price) values (?,?,?)");for (long i = 1; i <= 8; i++) {int j = 1;ps.setLong(j++, i);ps.setLong(j++, i);ps.setDouble(j, 100 * i);System.out.println(ps.executeUpdate());}connection.commit();ps.close();connection.close();}private static DataSource dataSource1() {HikariDataSource dataSource1 = new HikariDataSource();dataSource1.setDriverClassName("com.mysql.jdbc.Driver");dataSource1.setJdbcUrl("jdbc:mysql://localhost:3306/demo?characterEncoding=UTF-8");dataSource1.setUsername("root");dataSource1.setPassword("123456");return dataSource1;}}

运行输出

tables:order_table:actualDataNodes: demo1.order_table_$->{0..1}logicTable: order_tabletableStrategy:inline:algorithmExpression: order_table_$->{order_id % 2}shardingColumn: order_id

两张表中的数据

2.3、多库多表

多库多表是指在数据库设计中，使用多个数据库和多个数据表来存储和管理数据。这种设计可以提高数据库的性能和可扩展性，同时也可以更好地组织和管理数据。

create database demo_sharding0;
use demo_sharding0;
create table if not exists order_table_0(order_id int not null primary key comment '订单id',user_id int not null comment '用户id',price double  not null comment '价格'
)charset utf8;
create table if not exists order_table_1(order_id int not null primary key comment '订单id',user_id int not null comment '用户id',price double  not null comment '价格'
)charset utf8;
create database demo_sharding1;
use demo_sharding1;
create table if not exists order_table_0(order_id int not null primary key comment '订单id',user_id int not null comment '用户id',price double  not null comment '价格'
)charset utf8;
create table if not exists order_table_1(order_id int not null primary key comment '订单id',user_id int not null comment '用户id',price double  not null comment '价格'
)charset utf8;

代码

package com.sin.sharding;import com.zaxxer.hikari.HikariDataSource;
import org.apache.shardingsphere.api.config.sharding.ShardingRuleConfiguration;
import org.apache.shardingsphere.api.config.sharding.TableRuleConfiguration;
import org.apache.shardingsphere.api.config.sharding.strategy.InlineShardingStrategyConfiguration;
import org.apache.shardingsphere.shardingjdbc.api.ShardingDataSourceFactory;
import org.apache.shardingsphere.underlying.common.config.properties.ConfigurationPropertyKey;import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;
import java.util.Properties;/*** @CreateName SIN* @CreateDate 2023/06/29 10:27* @description*/
public class DemoSharding1 {public static void main(String[] args) throws SQLException {// 配置真实数据源Map<String, DataSource> dataSourceMap = new HashMap<>();dataSourceMap.put("demo0", dataSource1());dataSourceMap.put("demo1", dataSource2());/*** 2.配置表的规则*/TableRuleConfiguration orderTableRuleConfig = new TableRuleConfiguration("order_table", "demo$->{0..1}.order_table_$->{0..1}");// 指定db的分片策略（分片字段+分片算法）orderTableRuleConfig.setDatabaseShardingStrategyConfig(new InlineShardingStrategyConfiguration("user_id", "demo$->{user_id % 2}"));// 指定表的分片策略（分片字段+分片算法）orderTableRuleConfig.setTableShardingStrategyConfig(new InlineShardingStrategyConfiguration("order_id", "order_table_$->{order_id % 2}"));/*** 3、分片规则*/ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();//将表的分片规则加入到分片规则列表shardingRuleConfig.getTableRuleConfigs().add(orderTableRuleConfig);/*** 4、配置一些属性*/Properties props = new Properties();//输出sqlprops.put(ConfigurationPropertyKey.SQL_SHOW.getKey(), true);/*** 5、创建数据源*/DataSource dataSource = ShardingDataSourceFactory.createDataSource(dataSourceMap, shardingRuleConfig, props);/*** 6、获取连接，执行sql*/Connection connection = dataSource.getConnection();connection.setAutoCommit(false);PreparedStatement ps = connection.prepareStatement("insert into order_table (order_id,user_id,price) values (?,?,?)");// 插入4条数据测试,每个表会落入1条数据for (long user_id = 1; user_id <= 2; user_id++) {for (long order_id = 1; order_id <= 2; order_id++) {int j = 1;ps.setLong(j++, order_id);ps.setLong(j++, user_id);ps.setLong(j, 100);System.out.println(ps.executeUpdate());}}connection.commit();ps.close();connection.close();}private static DataSource dataSource1() {HikariDataSource dataSource1 = new HikariDataSource();dataSource1.setDriverClassName("com.mysql.jdbc.Driver");dataSource1.setJdbcUrl("jdbc:mysql://localhost:3306/demo_sharding0?characterEncoding=UTF-8");dataSource1.setUsername("root");dataSource1.setPassword("123456");return dataSource1;}private static DataSource dataSource2() {HikariDataSource dataSource1 = new HikariDataSource();dataSource1.setDriverClassName("com.mysql.jdbc.Driver");dataSource1.setJdbcUrl("jdbc:mysql://localhost:3306/demo_sharding1?characterEncoding=UTF-8");dataSource1.setUsername("root");dataSource1.setPassword("123456");return dataSource1;}
}

运行输出

表数据

2.4、单库无分表规则

单库无分表规则是指在数据库设计中，将所有数据存储在一个数据库中，而不进行分表操作。这种规则在某些情况下可能是合理的，但在大多数情况下，分表是更好的选择。

分表可以将数据分散存储在多个表中，每个表只包含一部分数据，这样可以提高查询性能和数据管理的效率。当数据量较大时，单库无分表规则可能导致数据库性能下降，查询速度变慢，对数据库的维护和管理也会变得更加困难。

此外，分表还可以根据数据的特点进行优化，例如按照时间、地域等进行分表，以便更好地满足业务需求。而单库无分表规则则无法灵活应对不同的数据特点和查询需求。

单库无分表规则可能在某些特定场景下适用，但在大多数情况下，分表是更好的选择，可以提高数据库性能和管理效率。

若表未指定分片规则，则直接路由到对应的表。

use demo;
create table if not exists order_user(id int not null primary key auto_increment comment 'id',name varchar(20)character set utf8 not null comment '姓名'
)charset utf8;

代码

package com.sin.sharding;import com.zaxxer.hikari.HikariDataSource;
import org.apache.shardingsphere.api.config.sharding.ShardingRuleConfiguration;
import org.apache.shardingsphere.shardingjdbc.api.ShardingDataSourceFactory;
import org.apache.shardingsphere.underlying.common.config.properties.ConfigurationPropertyKey;import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;
import java.util.Properties;/*** @CreateName SIN* @CreateDate 2023/06/29 10:43* @description*/
public class DemoSharding3 {public static void main(String[] args) throws SQLException {/*** 1.配置真实数据源*/Map<String, DataSource> dataSourceMap = new HashMap<>();dataSourceMap.put("demo", dataSource1());/*** 2、无分片规则*//*** 3、分片规则*/ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();/*** 4、配置一些属性*/Properties props = new Properties();//输出sqlprops.put(ConfigurationPropertyKey.SQL_SHOW.getKey(), true);/*** 5、创建数据源*/DataSource dataSource = ShardingDataSourceFactory.createDataSource(dataSourceMap, shardingRuleConfig, props);Connection connection = dataSource.getConnection();connection.setAutoCommit(false);PreparedStatement ps = connection.prepareStatement("insert into order_user (name) values (?)");ps.setString(1, "张三");System.out.println(ps.executeUpdate());connection.commit();ps.close();connection.close();}private static DataSource dataSource1() {HikariDataSource dataSource1 = new HikariDataSource();dataSource1.setDriverClassName("com.mysql.jdbc.Driver");dataSource1.setJdbcUrl("jdbc:mysql://localhost:3306/demo?characterEncoding=UTF-8");dataSource1.setUsername("root");dataSource1.setPassword("123456");return dataSource1;}}

运行输出

Logic SQL: insert into order_user (name) values (?)
Actual SQL: demo ::: insert into t_user (name) values (?) ::: [张三]

2.5、多库无分表规则

多库无分表规则指的是将数据分散存储在多个数据库中，而不是将数据分散存储在同一个数据库的多个表中。这种方式可以用于解决单个数据库的性能瓶颈问题，提高系统的扩展性和并发性。

use demo_sharding0;
create table if not exists order_user(id int not null primary key auto_increment comment 'id',name varchar(20)character set utf8 not null comment '姓名'
)charset utf8;
use demo_sharding1;
create table if not exists order_user(id int not null primary key auto_increment comment 'id',name varchar(20)character set utf8 not null comment '姓名'
)charset utf8;

java代码

下面配置2个数据源，向t_user表插入数据，看看数据会落在哪个库？

package com.sin.sharding;import com.zaxxer.hikari.HikariDataSource;
import org.apache.shardingsphere.api.config.sharding.ShardingRuleConfiguration;
import org.apache.shardingsphere.shardingjdbc.api.ShardingDataSourceFactory;
import org.apache.shardingsphere.underlying.common.config.properties.ConfigurationPropertyKey;import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.LinkedHashMap;
import java.util.Map;
import java.util.Properties;/*** @CreateName SIN* @CreateDate 2023/06/29 10:49* @description*/
public class DemoSharding4 {public static void main(String[] args) throws SQLException {/*** 1.配置2个数据源*/Map<String, DataSource> dataSourceMap = new LinkedHashMap<>();dataSourceMap.put("demo0", dataSource1());dataSourceMap.put("demo1", dataSource2());/*** 2、无分片规则*//*** 3、分片规则*/ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();/*** 4、配置一些属性*/Properties props = new Properties();//输出sqlprops.put(ConfigurationPropertyKey.SQL_SHOW.getKey(), true);/*** 5、创建数据源*/DataSource dataSource = ShardingDataSourceFactory.createDataSource(dataSourceMap, shardingRuleConfig, props);Connection connection = dataSource.getConnection();connection.setAutoCommit(false);//插入4条数据，测试效果for (int i = 0; i < 4; i++) {PreparedStatement ps = connection.prepareStatement("insert into order_user (name) values (?)");ps.setString(1, "张三");System.out.println(ps.executeUpdate());}connection.commit();connection.close();}private static DataSource dataSource1() {HikariDataSource dataSource1 = new HikariDataSource();dataSource1.setDriverClassName("com.mysql.jdbc.Driver");dataSource1.setJdbcUrl("jdbc:mysql://localhost:3306/demo_sharding0?characterEncoding=UTF-8");dataSource1.setUsername("root");dataSource1.setPassword("123456");return dataSource1;}private static DataSource dataSource2() {HikariDataSource dataSource1 = new HikariDataSource();dataSource1.setDriverClassName("com.mysql.jdbc.Driver");dataSource1.setJdbcUrl("jdbc:mysql://localhost:3306/demo_sharding1?characterEncoding=UTF-8");dataSource1.setUsername("root");dataSource1.setPassword("123456");return dataSource1;}
}

运行输出

输出如下，落入的库是不确定的。

Logic SQL: insert into order_user (name) values (?)
Actual SQL: demo0 ::: insert into t_user (name) values (?) ::: [张三]
1
Logic SQL: insert into order_user (name) values (?)
Actual SQL: demo1 ::: insert into t_user (name) values (?) ::: [张三]
1
Logic SQL: insert into order_user (name) values (?)
Actual SQL: demo1 ::: insert into t_user (name) values (?) ::: [张三]
1
Logic SQL: insert into order_user (name) values (?)
Actual SQL: demo0 ::: insert into t_user (name) values (?) ::: [张三]
1

3、分片

3.1、分片键

数据库中的分片键是用于将数据分散存储在不同的分片（shard）中的一种标识。分片键可以是一个或多个字段，用于确定数据在哪个分片中存储。通过将数据分散存储在多个分片中，可以提高数据库的扩展性和性能。

分片键的选择非常重要，它应该能够将数据均匀地分布在各个分片中，避免出现热点数据集中在某个分片的情况。同时，分片键还应该能够满足查询需求，使得常用的查询操作可以在单个分片上执行，减少跨分片查询的开销。

在选择分片键时，需要考虑数据的访问模式、数据分布的均匀性、数据的增长趋势等因素。一般来说，常见的选择分片键的策略包括基于时间戳、基于地理位置、基于用户ID等。

注意：选择分片键是一个复杂的问题，需要根据具体的应用场景和需求进行权衡和选择。不同的数据库系统可能有不同的分片策略和支持的分片键类型。

3.2、分片算法

MySQL分片算法是指将一个数据库分散存储在多个节点上的方法。常见的分片算法有以下几种：

• 哈希分片算法：
  • 根据数据的哈希值将数据分散存储在不同的节点上。这种算法可以保证数据的均匀分布，但是在需要进行范围查询或排序时可能会有性能问题。
• 范围分片算法：
  • 根据数据的某个范围值将数据分散存储在不同的节点上。这种算法适合于需要进行范围查询或排序的场景，但是可能会导致数据不均匀分布的问题。
• 列分片算法：
  • 根据数据的某个列值将数据分散存储在不同的节点上。这种算法适合于根据某个列进行查询的场景，但是可能会导致数据不均匀分布的问题。
• 一致性哈希分片算法：
  • 将数据和节点都映射到一个环上，根据数据的哈希值在环上找到对应的节点。这种算法可以保证数据的均匀分布，并且在节点的增加或删除时能够最小化数据的迁移。

以上是一些常见的MySQL分片算法，具体选择哪种算法需要根据具体的业务需求和数据特点来决定。

3.3、5种分片策略

多种分片策略可供选择，以下是其中的五种常见策略：

• 垂直分片（Vertical Sharding）：
  • 将数据库按照不同的表或列进行分片。每个分片只包含特定的表或列，可以根据业务需求将不同的数据存储在不同的分片中。
• 水平分片（Horizontal Sharding）：
  • 将数据库按照数据行进行分片。每个分片包含相同的表结构，但存储不同的数据行。可以根据数据的某个特定属性（如用户ID、地理位置等）将数据行分散到不同的分片中。
• 哈希分片（Hash Sharding）：
  • 根据数据的哈希值将数据分散到不同的分片中。通过对数据的某个属性进行哈希运算，可以将数据均匀地分布到不同的分片中，实现负载均衡。
• 范围分片（Range Sharding）：
  • 根据数据的某个范围进行分片。可以根据数据的某个连续属性（如时间、价格等）将数据按照范围进行划分，将不同范围的数据存储在不同的分片中。
• 列分片（Column Sharding）：
  • 将数据库按照列进行分片。每个分片只包含特定的列，可以根据业务需求将不同的列存储在不同的分片中，提高查询效率。

这些分片策略可以根据具体的业务需求和数据特点进行选择和组合，以实现高性能、高可用性和可扩展性的数据库架构。

3.4、总结

• 分片策略是指将一个大的任务或数据分割成多个小的部分进行处理或存储的策略。这种策略可以提高任务的并行性和效率，减少单个节点的负载压力。

• 在计算机领域，分片策略常用于分布式系统中的数据存储和处理。例如，将一个大型数据库分成多个分片，每个分片存储在不同的服务器上，可以提高数据库的读写性能和可扩展性。同时，分片还可以增加系统的容错性，当某个分片发生故障时，其他分片仍然可以正常工作。

• 在任务处理中，分片策略可以将一个大的任务分成多个小的子任务，每个子任务由不同的处理节点执行。这样可以提高任务的并行处理能力，加快任务的完成速度。同时，分片策略也可以增加系统的容错性，当某个节点发生故障时，其他节点仍然可以继续处理剩余的子任务。

分片策略是一种有效的分解和管理大型任务或数据的方法，可以提高系统的性能、可扩展性和容错性。