MySQL学习笔记
分库分表篇
问题思考
为什么要分库分表?
- 关系型数据库以MySQL为例,单机的存储能力、连接数是有限的,它自身就很容易会成为系统的瓶颈。当单表数据量在百万之内时,我们还可以通过添加从库、优化索引提升性能。一旦数据量朝着千万以上趋势增长,再怎么优化数据库,很多操作性能仍下降严重。为了减少数据库的负担,提升数据库响应速度,缩短查询时间,这时候就需要进行 分库分表 。
- 当【表的数量】达到了几百上千张表时,众多的业务模块都访问这个数据库,压力会比较大,考虑对其进行分库。
- 当【表的数据】达到了几千万级别,在做很多操作都比较吃力,考虑对其进行分库或者分表。
如何分库分表
- 分库分表就是要将大量数据分散到多个数据库中,使每个数据库中数据量小、响应速度快,以此来提升数据库整体性能。核心理念就是对数据进行切分( Sharding ),以及切分后如何对数据的快速定位与整合。
- 针对数据切分类型,大致可以分为:垂直(纵向)切分和水平(横向)切分两种。
垂直切分
- 垂直切分又细分为 垂直分库 和 垂直分表
垂直分库
- 垂直分库是基于业务分类的,和我们常听到的微服务治理观念很相似,每一个独立的服务都拥有自己的数据库,需要不同业务的数据需接口调用。
- 而垂直分库也是按照业务分类进行划分,每个业务有独立数据库,这个比较好理解。
垂直分表
- 垂直分表是基于数据表的列为依据切分的,是一种大表拆成小表的模式。
- 例如:一个 order 表有很多字段,把长度较大且访问不频繁的字段,拆分出来创建一个单独的扩展表work_extend 进行存储。
- order表:
id | workNo | price | describe | … |
---|---|---|---|---|
int(11) | int(2) | float(5,2) | varchar(1024) |
拆分后:
- order核心表:
id | workNo | price | … |
---|---|---|---|
int(11) | int(2) | float(5,2) |
- work_extend表:
id | workNo | describe | … |
---|---|---|---|
int(11) | int(2) | varchar(1024) |
- 数据库是以行为单位将数据加载到内存中,这样拆分以后核心表大多是访问频率较高的字段,而且字段长度也都较短,可以加载更多数据到内存中,增加查询的命中率,减少磁盘IO,以此来提升数据库性能。
- 优点:
- 业务间解耦,不同业务的数据进行独立的维护、监控、扩展。
- 在高并发场景下,一定程度上缓解了数据库的压力
- 缺点:
- 提升了开发的复杂度,由于业务的隔离性,很多表无法直接访问,必须通过接口方式聚合数据。
- 分布式事务管理难度增加。
- 数据库还是存在单表数据量过大的问题,并未根本上解决,需要配合水平切分。
水平切分
- 前边说了垂直切分还是会存在单表数据量过大的问题,当我们的应用已经无法在细粒度的垂直切分时,依旧存在单库读写、存储性能瓶颈,这时就要配合水平切分一起了。
- 水平切分将一张大数据量的表,切分成多个表结构相同,而每个表只占原表一部分数据,然后按不同的条件分散到多个数据库中。
- 假如一张 order 表有2000万数据,水平切分后出来四个表, order_1 、 order_2 、 order_3 、order_4 ,每张表数据500万,以此类推。
- 水平切分又分有 库内分表 和 分库分表
库内分表
- 库内分表虽然将表拆分,但子表都还是在同一个数据库实例中,只是解决了单一表数据量过大的问题,并没有将拆分后的表分布到不同机器的库上,还在竞争同一个物理机的CPU、内存、网络IO。
分库分表
- 分库分表则是将切分出来的子表,分散到不同的数据库中,从而使得单个表的数据量变小,达到分布式的效果。
- 优点:
- 解决高并发时单库数据量过大的问题,提升系统稳定性和负载能力。
- 业务系统改造的工作量不是很大。
- 缺点:
- 跨分片的事务一致性难以保证。
- 跨库的join关联查询性能较差。
- 扩容的难度和维护量较大,(拆分成几千张子表想想都恐怖)。
切分规则
- 按照ID取模:对ID进行取模,余数决定该行数据切分到哪个表或者库中
- 按照日期:按照年月日,将数据切分到不同的表或者库中
- 按照范围:可以对某一列按照范围进行切分,不同的范围切分到不同的表或者数据库中
切分原则
- 第一原则:能不切分尽量不要切分。
- 第二原则:如果要切分一定要选择合适的切分规则,提前规划好。
- 第三原则:数据切分尽量通过数据冗余或表分组(Table Group)来降低垮库 join 的可能。
- 第四原则:由于数据库中间件对数据 join 实现的优劣难以把握,而且实现高性能难度极大,业务读取尽量少使用多表 join。
数据该往哪个库的表存?
分库分表以后会出现一个问题,一张表会出现在多个数据库里,到底该往哪个库的表里存呢?
根据取值范围
- 按照 时间区间 或 ID区间 来切分,举个栗子:假如我们切分的是用户表,可以定义每个库的 User表 里只存10000条数据,第一个库 userId 从1 ~ 9999,第二个库10000 ~ 20000,第三个库20001~30000…以此类推。
- 优点:
- 单表数据量是可控的
- 水平扩展简单只需增加节点即可,无需对其他分片的数据进行迁移。
- 能快速定位要查询的数据在哪个库
- 缺点:
- 由于连续分片可能存在数据热点,如果按时间字段分片,有些分片存储最近时间段内的数据,可能会被频繁的读写,而有些分片存储的历史数据,则很少被查询。
hash取模
- hash取模mod(对hash结果取余数 (hash() mod N))的切分方式比较常见,还拿 User表 举例,对数据库从0到N-1进行编号,对 User表 中 userId 字段进行取模,得到余数 i , i=0 存第一个库, i=1 存第二个库, i=2 存第三个库…以此类推。
- 这样同一个用户的数据都会存在同一个库里,用 userId 作为条件查询就很好定位了。
- 优点:
- 数据分片相对比较均匀,不易出现某个库并发访问的问题。
- 缺点:
- 但这种算法存在一些问题,当某一台机器宕机,本应该落在该数据库的请求就无法得到正确的处理,这时宕掉的实例会被踢出集群,此时算法变成hash(userId) mod N-1,用户信息可能就不再在同一个库中。
分库分表需要解决的问题?
分布式事务问题
* 本地事务:ACID
* 分布式事务CAP:一致性是强一致性,CAP理论最多只能同时满足两个
* BASE:基本可用+最终一致性
- 强一致性事务(同步)
- 最终一致性事务(异步思想)
分布式主键ID问题
- redis incr 命令
- 数据库(生成主键)
- UUID
- snowflake算法(https://www.sohu.com/a/232008315_453160)
垮库 join 问题
- 通过业务分析,将不同库的 join 查询拆分成多个 select
- 建立全局表(每个库都有一个相同的表)
- 冗余字段(不符合数据库三范式)
- E-R分片(将有ER关系的记录都存储到一个库中)
- 最多支持跨两张表跨库 join
垮库count、order by、group by问题
分库分表工具?
自己开发分库分表工具的工作量是巨大的,好在业界已经有了很多比较成熟的分库分表中间件,我们可以将更多的时间放在业务实现上。
- sharding-jdbc(当当)
- TSharding(蘑菇街)
- Atlas(奇虎360)
- Cobar(阿里巴巴)
- MyCAT(基于Cobar) :
- Oceanus(58同城)
- Vitess(谷歌)
Sharding JDBC
Sharding JDBC介绍
什么是Sharding JDBC
- 官方网站:http://shardingsphere.apache.org/index_zh.html
- Apache ShardingSphere(Incubator) 是一套开源的分布式数据库中间件解决方案组成的生态圈,它由 Sharding-JDBC、Sharding-Proxy 和Sharding-Sidecar(规划中)这3款相互独立,却又能够混合部署配合使用的产品组成。
Sharding JDBC架构
Sharding JDBC核心组件
解析引擎
- 解析过程分为词法解析和语法解析。 词法解析器用于将SQL拆解为不可再分的原子符号,称为Token。并根据不同数据库方言所提供的字典,将其归类为关键字,表达式,字面量和操作符。 再使用语法解析器将SQL转换为抽象语法树。
- 例如,以下SQL:
SELECT id, name FROM t_user WHERE status = 'ACTIVE' AND age > 18
- 解析之后的为抽象语法树见下图:
- 为了便于理解,抽象语法树中的关键字的Token用绿色表示,变量的Token用红色表示,灰色表示需要进一步拆分。
- 最后,通过对抽象语法树的遍历去提炼分片所需的上下文,并标记有可能需要改写的位置。 供分片使用的解析上下文包含查询选择项(Select Items)、表信息(Table)、分片条件(Sharding Condition)、自增主键信息(Auto increment Primary Key)、排序信息(Order By)、分组信息(Group By)以及分页信息(Limit、Rownum、Top)。 SQL的一次解析过程是不可逆的,一个个Token的按SQL原本的顺序依次进行解析,性能很高。 考虑到各种数据库SQL方言的异同,在解析模块提供了各类数据库的SQL方言字典。
- SQL解析作为分库分表类产品的核心,其性能和兼容性是最重要的衡量指标。 ShardingSphere的SQL解析器经历了3代产品的更新迭代。
- 第一代SQL解析器为了追求性能与快速实现,在1.4.x之前的版本使用Druid作为SQL解析器。经实际测试,它的性能远超其它解析器。
- 第二代SQL解析器从1.5.x版本开始,ShardingSphere采用完全自研的SQL解析引擎。 由于目的不同,ShardingSphere并不需要将SQL转为一颗完全的抽象语法树,也无需通过访问器模式进行二次遍历。它采用对SQL半理解的方式,仅提炼数据分片需要关注的上下文,因此SQL解析的性能和兼容性得到了进一步的提高。
- 第三代SQL解析器则从3.0.x版本开始,ShardingSphere尝试使用ANTLR作为SQL解析的引擎,并计划根据DDL => TCL => DAL => DCL => DML =>DQL这个顺序,依次替换原有的解析引擎,目前仍处于替换迭代中。 使用ANTLR的原因是希望ShardingSphere的解析引擎能够更好的对SQL进行兼容。对于复杂的表达式、递归、子查询等语句,虽然ShardingSphere的分片核心并不关注,但是会影响对于SQL理解的友好度。 经过实例测试,ANTLR解析SQL的性能比自研的SQL解析引擎慢3-10倍左右。为了弥补这一差距,ShardingSphere将使用PreparedStatement的SQL解析的语法树放入缓存。 因此建议采用PreparedStatement这种SQL预编译的方式提升性能。
- 第三代SQL解析引擎的整体结构划分如下图所示:
路由引擎
- 根据解析上下文匹配数据库和表的分片策略,并生成路由路径。 对于携带分片键的SQL,根据分片键的不同可以划分为单片路由(分片键的操作符是等号)、多片路由(分片键的操作符是IN)和范围路由(分片键的操作符是BETWEEN)。 不携带分片键的SQL则采用广播路由。
- 用于根据分片键进行路由的场景,又细分为直接路由、标准路由和笛卡尔积路由这3种类型。
- 对于不携带分片键的SQL,则采取广播路由的方式。根据SQL类型又可以划分为全库表路由、全库路由、全实例路由、单播路由和阻断路由这5种类型。
- 路由引擎的整体结构划分如下图:
改写引擎
- 面向逻辑库与逻辑表书写的SQL,并不能够直接在真实的数据库中执行,SQL改写用于将逻辑SQL改写为在真实数据库中可以正确执行的SQL。 它包括正确性改写和优化改写两部分。
- 正确性改写:在包含分表的场景中,需要将分表配置中的逻辑表名称改写为路由之后所获取的真实表名称。仅分库则不需要表名称的改写。除此之外,还包括补列和分页信息修正等内容。
- 标识符改写:需要改写的标识符包括表名称、索引名称以及Schema名称。
- 改写引擎的整体结构划分如下图所示:
执行引擎
- ShardingSphere采用一套自动化的执行引擎,负责将路由和改写完成之后的真实SQL安全且高效发送到底层数据源执行。 它不是简单地将SQL通过JDBC直接发送至数据源执行;也并非直接将执行请求放入线程池去并发执行。它更关注平衡数据源连接创建以及内存占用所产生的消耗,以及最大限度地合理利用并发等问题。 执行引擎的目标是自动化的平衡资源控制与执行效率。
- 执行引擎分为准备和执行两个阶段。准备阶段用于准备执行的数据。它分为结果集分组和执行单元创建两个步骤。执行阶段用于真正的执行SQL,它分为分组执行和归并结果集生成两个步骤。
- 执行引擎的整体结构划分如下图所示:
归并引擎
- 各个数据节点获取的多数据结果集,组合成为一个结果集并正确的返回至请求客户端,称为结果归并。
- ShardingSphere支持的结果归并从功能上分为遍历、排序、分组、分页和聚合5种类型,它们是组合而非互斥的关系。 从结构划分,可分为流式归并、内存归并和装饰者归并。流式归并和内存归并是互斥的,装饰者归并可以在流式归并和内存归并之上做进一步的处理。
Sharding JDBC分片策略
StandardShardingStrategy
- 标准分片策略:提供对SQL语句中的=, IN和BETWEEN AND的分片操作支持。StandardShardingStrategy只支持单分片键,提供PreciseShardingAlgorithm和RangeShardingAlgorithm两个分片算法。
- PreciseShardingAlgorithm是必选的,用于处理=和IN的分片。
- RangeShardingAlgorithm是可选的,用于处理BETWEEN AND分片,如果不配置 RangeShardingAlgorithm,SQL中的BETWEEN AND将按照全库路由处理。
ComplexShardingStrategy
- 复合分片策略:提供对SQL语句中的=, IN和BETWEEN AND的分片操作支持。
- ComplexShardingStrategy支持多分片键,由于多分片键之间的关系复杂,因此Sharding-JDBC并未做过多的封装,而是直接将分片键值组合以及分片操作符交于算法接口,完全由应用开发者实现,提供最大的灵活度。
InlineShardingStrategy
- Inline表达式分片策略:使用Groovy的Inline表达式,提供对SQL语句中的=和IN的分片操作支持。
- InlineShardingStrategy只支持单分片键,对于简单的分片算法,可以通过简单的配置使用,从而避免繁琐的Java代码开发,如: tuser${user_id % 8} 表示t_user表按照user_id按8取模分成8个表,表名称为 t_user_0 到 t_user_7。
HintShardingStrategy
- 通过Hint而非SQL解析的方式分片的策略。
NoneShardingStrategy
- 不分片的策略。
Sharding JDBC读写分离
创建表
- 在master数据库192.168.254.128创建表即可,slave数据库192.168.254.130自动同步:
CREATE TABLE `t_user0` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(10) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`address` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
依赖
<dependencies>
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-core</artifactId>
<version>4.1.1</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.20</version>
</dependency>
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-api</artifactId>
<version>1.7.6</version>
</dependency>
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-log4j12</artifactId>
<version>1.7.6</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.23</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.13</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.12</version>
<scope>compile</scope>
</dependency>
</dependencies>
实现读写分离
- DataSource配置:
public class MasterSlaveDataSource {
private static DataSource dataSource;
public static DataSource getDataSource() {
if (dataSource != null) {
return dataSource;
}
try {
return create();
} catch (SQLException e) {
System.out.println(e.getMessage());
}
return null;
}
private static DataSource create() throws SQLException{
// 配置真实数据源
Map<String, DataSource> dataSourceMap = new HashMap<>(2);
// 配置第一个数据源
DruidDataSource masterDataSource = new DruidDataSource();
masterDataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
masterDataSource.setUrl("jdbc:mysql://192.168.254.128:3306/test?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf-8");
masterDataSource.setUsername("root");
masterDataSource.setPassword("123456");
dataSourceMap.put("master", masterDataSource);
// 配置第二个数据源
DruidDataSource slaveDataSource1 = new DruidDataSource();
slaveDataSource1.setDriverClassName("com.mysql.cj.jdbc.Driver");
slaveDataSource1.setUrl("jdbc:mysql://192.168.254.129:3306/test?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf-8");
slaveDataSource1.setUsername("root");
slaveDataSource1.setPassword("123456");
dataSourceMap.put("slave1", slaveDataSource1);
// 配置第三个数据源
DruidDataSource slaveDataSource2 = new DruidDataSource();
slaveDataSource2.setDriverClassName("com.mysql.cj.jdbc.Driver");
slaveDataSource2.setUrl("jdbc:mysql://192.168.254.130:3306/test?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf-8");
slaveDataSource2.setUsername("root");
slaveDataSource2.setPassword("123456");
dataSourceMap.put("slave2", slaveDataSource2);
// 配置主从复制数据源
MasterSlaveRuleConfiguration config = new MasterSlaveRuleConfiguration("masterSlaveDataSource", "master", Arrays.asList("slave1", "slave2"));
// 创建数据源
return MasterSlaveDataSourceFactory.createDataSource(dataSourceMap, config, new Properties());
}
}
- 实体类:
@Data
@Accessors(chain = true)
public class User {
private int id;
private String name;
private int age;
private String address;
}
- UserService:
public class UserService {
public boolean addUser(User user) throws Exception {
DataSource dataSource = MasterSlaveDataSource.getDataSource();
String sql = "insert into t_user0(name, age, address) values (?, ?, ?)";
try (Connection connection = dataSource.getConnection();
PreparedStatement ps = connection.prepareStatement(sql)) {
ps.setString(1, user.getName());
ps.setInt(2, user.getAge());
ps.setString(3, user.getAddress());
return ps.execute();
}
}
public List<User> getUserList() throws Exception {
DataSource dataSource = MasterSlaveDataSource.getDataSource();
String sql = "select id, name, age, concat(address, @@hostname) as address from t_user0";
try (Connection connection = dataSource.getConnection();
PreparedStatement ps = connection.prepareStatement(sql);
ResultSet rs = ps.executeQuery()) {
List<User> users = new ArrayList<>();
while (rs.next()) {
User user = new User()
.setId(rs.getInt("id"))
.setName(rs.getString("name"))
.setAge(rs.getInt("age"))
.setAddress(rs.getString("address"));
users.add(user);
}
return users;
}
}
}
- 测试类:
public class UserServiceTest {
public static void main(String[] args) throws Exception {
UserService userService = new UserService();
User user = new User()
.setName("武松")
.setAge(23)
.setAddress("清河县");
boolean result = userService.addUser(user);
if (result) {
System.out.println("添加用户成功");
}
List<User> users = userService.getUserList();
users.forEach(System.out::println);
users = userService.getUserList();
users.forEach(System.out::println);
}
}
- 测试结果:
添加用户成功
User(id=3, name=武松, age=23, address=清河县centos129)
User(id=3, name=武松, age=23, address=清河县centos130)
Sharding JDBC分库分表
创建表
- 192.168.254.129、192.168.254.130 分别建立两张表:
-- t_order0
CREATE TABLE `t_order0` (
`order_id` int(11) NOT NULL,
`user_id` int(11) NOT NULL,
`info` varchar(100) DEFAULT NULL,
PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- t_order1
CREATE TABLE `t_order1` (
`order_id` int(11) NOT NULL,
`user_id` int(11) NOT NULL,
`info` varchar(100) DEFAULT NULL,
PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
实现分库分表
- DataSource配置:
public class ShardingDataSource {
private static DataSource dataSource;
public static DataSource getDataSource() {
if (dataSource != null) {
return dataSource;
}
try {
return create();
} catch (SQLException e) {
System.out.println(e.getMessage());
}
return null;
}
private static DataSource create() throws SQLException {
// 配置真实数据源
Map<String, DataSource> dataSourceMap = new HashMap<>(2);
// 配置第一个数据源
DruidDataSource dataSource1 = new DruidDataSource();
dataSource1.setDriverClassName("com.mysql.cj.jdbc.Driver");
dataSource1.setUrl("jdbc:mysql://192.168.254.129:3306/test?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf-8");
dataSource1.setUsername("root");
dataSource1.setPassword("123456");
dataSourceMap.put("ds0", dataSource1);
// 配置第二个数据源
DruidDataSource dataSource2 = new DruidDataSource();
dataSource2.setDriverClassName("com.mysql.cj.jdbc.Driver");
dataSource2.setUrl("jdbc:mysql://192.168.254.130:3306/test?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf-8");
dataSource2.setUsername("root");
dataSource2.setPassword("123456");
dataSourceMap.put("ds1", dataSource2);
// 配置 t_order 表规则
TableRuleConfiguration orderRuleConfig = new TableRuleConfiguration("t_order", "ds${0..1}.t_order${0..1}");
// 配置数据库表分片策略
orderRuleConfig.setDatabaseShardingStrategyConfig(new InlineShardingStrategyConfiguration("user_id", "ds${user_id % 2}"));
orderRuleConfig.setTableShardingStrategyConfig(new InlineShardingStrategyConfiguration("order_id", "t_order${order_id % 2}"));
// 配置分片规则
ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
shardingRuleConfig.getTableRuleConfigs().add(orderRuleConfig);
// 创建数据源
return ShardingDataSourceFactory.createDataSource(dataSourceMap, shardingRuleConfig, new Properties());
}
}
- 实体类:
@Data
@Accessors(chain = true)
public class Order {
private int orderId;
private int userId;
private String info;
}
- OrderService:
public class OrderService {
public boolean addOrderInfo(Order order) throws Exception {
DataSource dataSource = ShardingDataSource.getDataSource();
String sql = "insert into t_order(order_id, user_id, info) values (?,?,?)";
try (Connection connection = dataSource.getConnection();
PreparedStatement ps = connection.prepareStatement(sql)) {
ps.setInt(1, order.getOrderId());
ps.setInt(2, order.getUserId());
ps.setString(3, order.getInfo());
return ps.execute();
}
}
}
- 测试类:
public class OrderServiceTest {
public static void main(String[] args) throws Exception {
OrderService orderService = new OrderService();
int userId = 10;
for (int i = 1; i <= 20; i++) {
if (i >= 10) {
userId = 21;
}
Order order = new Order()
.setOrderId(i)
.setUserId(userId)
.setInfo("订单信息:user_id=" + userId + ",order_id=" + i);
boolean result = orderService.addOrderInfo(order);
if (result) {
System.out.println("订单" + i + "添加成功");
}
}
}
}
- 测试结果: