MySQL高级实战

2021/7/21 2:09:10

编程Tag： mysql 分片查询 id SQL 实战高级 order jdbc

本文主要是介绍MySQL高级实战，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

第五部分分库分表实战及中间件

前言背景介绍

背景描述

刚开始我们的系统只用了单机数据库

随着用户的不断增多，考虑到系统的高可用和越来越多的用户请求，我们开始使用数据库主从架构

当用户量级和业务进一步提升后，写请求越来越多，这时我们开始使用了分库分表

遇到的问题

用户请求量太大

单服务器 TPS、内存、IO 都是有上限的，需要将请求打散分布到多个服务器

单库数据量太大

单个数据库处理能力有限；单库所在服务器的磁盘空间有限；单库上的操作 IO 有瓶颈

单表数据量太大

查询、插入、更新操作都会变慢，在加字段、加索引、机器迁移都会产生高负载，影响服务

如何解决

垂直拆分

垂直分库

微服务架构时，业务切割得足够独立，数据也会按照业务切分，保证业务数据隔离，大大提升了数据库的吞吐能力

垂直分表

表中字段太多且包含大字段的时候，在查询时对数据库的 IO、内存会受到影响，同时更新数据时，产生的 binlog 文件会很大，MySQL 在主从同步时也会有延迟的风险

水平拆分

水平分表

针对数据量巨大的单张表（比如订单表），按照规则把一张表的数据切分到多张表里面去。但是这些表还是在同一个库中，所以库级别的数据库操作还是有 IO 瓶颈。

水平分库

将单张表的数据切分到多个服务器上去，每个服务器具有相应的库与表，只是表中数据集合不同。水平分库分表能够有效的缓解单机和单库的性能瓶颈和压力，突破 IO、连接数、硬件资源等的瓶颈

水平分库规则

不跨库、不跨表，保证同一类的数据都在同一个服务器上面。

数据在切分之前，需要考虑如何高效的进行数据获取，如果每次查询都要跨越多个节点，就需要谨慎使用。

水平分表规则

RANGE

时间：按照年、月、日去切分。例如 order_2020、order_202005、order_20200501

地域：按照省或市去切分。例如 order_beijing、order_shanghai、order_chengdu

大小：从 0 到 1000000 一个表。例如 1000001-2000000 放一个表，每 100 万放一个表

HASH

用户 ID 取模

不同的业务使用的切分规则是不一样，就上面提到的切分规则，举例如下：

站内信

用户维度：用户只能看到发送给自己的消息，其他用户是不可见的，这种情况下是按照用户 ID hash 分库，在用户查看历史记录翻页查询时，所有的查询请求都在同一个库内

用户表

范围法：以用户 ID 为划分依据，将数据水平切分到两个数据库实例，如：1 到 1000W 在一张表，1000W 到 2000W 在一张表，这种情况会出现单表的负载较高

按照用户 ID HASH 尽量保证用户数据均衡分到数据库中

如果在登录场景下，用户输入手机号和验证码进行登录，这种情况下，登录时是不是需要扫描所有分库的信息？

最终方案：用户信息采用 ID 做切分处理，同时存储用户 ID 和手机号的映射的关系表（新增一个关系表），关系表采用手机号进行切分。可以通过关系表根据手机号查询到对应的 ID，再定位用户信息。

流水表

时间维度：可以根据每天新增的流水来判断，选择按照年份分库，还是按照月份分库，甚至也可以按照日期分库

订单表

在拉勾网，求职者（下面统称 C 端用户）投递企业（下面统称 B 端用户）的职位产生的记录称之为订单表。在线上的业务场景中，C 端用户看自己的投递记录，每次的投递到了哪个状态， B 端用户查看自己收到的简历，对于合适的简历会进行下一步沟通，同一个公司内的员工可以协作处理简历。

如何能同时满足 C 端和 B 端对数据查询，不进行跨库处理？

最终方案：为了同时满足两端用户的业务场景，采用空间换时间，将一次的投递记录存为两份，C 端的投递记录以用户 ID 为分片键，B 端收到的简历按照公司 ID 为分片键

主键选择

UUID：本地生成，不依赖数据库，缺点就是作为主键性能太差

SNOWFLAKE：百度 UidGenerator、美团 Leaf、基于 SNOWFLAKE 算法实现

数据一致性

强一致性：XA 协议

最终一致性：TCC、saga、Seata

数据库扩容

成倍增加数据节点，实现平滑扩容

成倍扩容以后，表中的部分数据请求已被路由到其他节点上面，可以清理掉

业务层改造

基于代理层方式：Mycat、Sharding-Proxy、MySQL Proxy

基于应用层方式：Sharding-jdbc

分库后面临的问题

事务问题：一次投递需要插入两条记录，且分布在不同的服务器上，数据需要保障一致性。

跨库跨表的 join 问题

全局表（字典表）：基础数据/配置数据，所有库都拷贝一份
字段冗余：可以使用字段冗余就不用 join 查询了
系统层组装：可以在业务层分别查询出来，然后组装起来，逻辑较复杂

额外的数据管理负担和数据运算压力：数据库扩容、维护成本变高

第 1 节 ShardingSphere 实战

1.1 ShardingSphere

Apache ShardingSphere 是一款开源的分布式数据库中间件组成的生态圈。它由 Sharding-JDBC、 Sharding-Proxy 和 Sharding-Sidecar（规划中）这 3 款相互独立的产品组成。他们均提供标准化的数据分片、分布式事务和数据库治理功能，可适用于如 Java 同构、异构语言、容器、云原生等各种多样化的应用场景。

ShardingSphere 项目状态如下：

ShardingSphere 定位为关系型数据库中间件，旨在充分合理地在分布式的场景下利用关系型数据库的计算和存储能力，而并非实现一个全新的关系型数据库。

Sharding-JDBC：被定位为轻量级 Java 框架，在 Java 的 JDBC 层提供的额外服务，以 jar 包形式使用。

Sharding-Proxy：被定位为透明化的数据库代理端，提供封装了数据库二进制协议的服务端版本，用于完成对异构语言的支持。

Sharding-Sidecar：被定位为 Kubernetes 或 Mesos 的云原生数据库代理，以 DaemonSet 的形式代理所有对数据库的访问。

Sharding-JDBC、Sharding-Proxy 和 Sharding-Sidecar 三者区别如下：

ShardingSphere 安装包下载：https://shardingsphere.apache.org/document/current/cn/downloads/

使用 Git 下载工程：git clone https://github.com/apache/incubator-shardingsphere.git

1.2 Sharding-JDBC

Sharding-JDBC 定位为轻量级 Java 框架，在 Java 的 JDBC 层提供的额外服务。它使用客户端直连数据库，以 jar 包形式提供服务，无需额外部署和依赖，可理解为增强版的 JDBC 驱动，完全兼容 JDBC 和各种 ORM 框架的使用。

适用于任何基于 Java 的 ORM 框架，如：JPA, Hibernate, Mybatis, Spring JDBC Template 或直接使用 JDBC。

基于任何第三方的数据库连接池，如：DBCP, C3P0, BoneCP, Druid, HikariCP 等。

支持任意实现 JDBC 规范的数据库。目前支持 MySQL，Oracle，SQLServer 和 PostgreSQL。

Sharding-JDBC 主要功能：

数据分片

分库、分表
读写分离
分片策略
分布式主键

分布式事务

标准化的事务接口
XA 强一致性事务
柔性事务

数据库治理

配置动态化
编排和治理
数据脱敏
可视化链路追踪

Sharding-JDBC 内部结构

图中黄色部分表示的是 Sharding-JDBC 的入口 API，采用工厂方法的形式提供。目前有 ShardingDataSourceFactory 和 MasterSlaveDataSourceFactory 两个工厂类。

ShardingDataSourceFactory 支持分库分表、读写分离操作

MasterSlaveDataSourceFactory 支持读写分离操作

图中蓝色部分表示的是 Sharding-JDBC 的配置对象，提供灵活多变的配置方式。ShardingRuleConfifiguration 是分库分表配置的核心和入口，它可以包含多个 TableRuleConfifiguration 和 MasterSlaveRuleConfifiguration。

TableRuleConfifiguration 封装的是表的分片配置信息，有 5 种配置形式对应不同的 Configuration 类型。

MasterSlaveRuleConfifiguration 封装的是读写分离配置信息。

图中红色部分表示的是内部对象，由 Sharding-JDBC 内部使用，应用开发者无需关注。

ShardingJDBC 通过 ShardingRuleConfiguration 和 MasterSlaveRuleConfiguration 生成真正供 ShardingDataSource 和 MasterSlaveDataSource 使用的规则对象。

ShardingDataSource 和 MasterSlaveDataSource 实现了 DataSource 接口，是 JDBC 的完整实现方案。

Sharding-JDBC 初始化流程

由配置信息生成 rule 然后结合 datasource 提供接口 factory

根据配置的信息生成 Conﬁguration 对象
通过 Factory 会将 Conﬁguration 对象转化为 Rule 对象
通过 Factory 会将 Rule 对象与 DataSource 对象封装
Sharding-JDBC 使用 DataSource 进行分库分表和读写分离操作

Sharding-JDBC 使用过程

引入 maven 依赖

<dependency> <groupId>org.apache.shardingsphere</groupId> <artifactId>sharding-jdbc-core</artifactId> <version>${latest.release.version}</version> </dependency>

注意: 请将 ${latest.release.version}更改为实际的版本号。

规则配置

Sharding-JDBC 可以通过 Java，YAML，Spring 命名空间和 Spring Boot Starter 四种方式配置，开发者可根据场景选择适合的配置方式。

创建 DataSource

通过 ShardingDataSourceFactory 工厂和规则配置对象获取 ShardingDataSource，然后即可通过 DataSource 选择使用原生 JDBC 开发，或者使用 JPA, MyBatis 等 ORM 工具。

DataSource dataSource = ShardingDataSourceFactory
.createDataSource(dataSourceMap, shardingRuleConfig, props);

1.3 数据分片剖析实战

1.3.1 核心概念

表概念

真实表

数据库中真实存在的物理表。例如 b_order0、b_order1

逻辑表

在分片之后，同一类表结构的名称（总成）。例如 b_order。

数据节点

在分片之后，由数据源和数据表组成。例如 ds0.b_order1

绑定表

指的是分片规则一致的关系表（主表、子表），例如 b_order 和 b_order_item，均按照 order_id 分片，则此两个表互为绑定表关系。绑定表之间的多表关联查询不会出现笛卡尔积，关联，可以提升关联查询效率。

b_order：b_order0、b_order1 b_order_item：b_order_item0、b_order_item1 select * from b_order o join b_order_item i on(o.order_id=i.order_id) where o.order_id in (10,11);

如果不配置绑定表关系，采用笛卡尔积关联，会生成 4 个 SQL 查询表 00、01、10、11

select * from b_order0 o join b_order_item0 i 
on(o.order_id=i.order_id) where o.order_id in (10,11);

select * from b_order0 o join b_order_item1 i 
on(o.order_id=i.order_id) where o.order_id in (10,11);

select * from b_order1 o join b_order_item0 i 
on(o.order_id=i.order_id) where o.order_id in (10,11);

select * from b_order1 o join b_order_item1 i 
on(o.order_id=i.order_id) where o.order_id in (10,11);

如果配置绑定表关系，生成 2 个 SQL 查询表 00、11

select * from b_order0 o join b_order_item0 i 
on(o.order_id=i.order_id) where o.order_id in (10,11);

select * from b_order1 o join b_order_item1 i on(o.order_id=i.order_id) where o.order_id in (10,11);

广播表

在使用中，有些表没必要做分片，例如字典表、省份信息等，因为他们数据量不大，而且这种表可能需要与海量数据的表进行关联查询。广播表会在不同的数据节点上进行重复存储，存储的表结构和数据完全相同。

分片算法（ShardingAlgorithm）

由于分片算法和业务实现紧密相关，因此并未提供内置分片算法，而是通过分片策略将各种场景提炼出来，提供更高层级的抽象，并提供接口让应用开发者自行实现分片算法。目前提供 4 种分片算法。

精确分片算法 PreciseShardingAlgorithm
用于处理使用单一键作为分片键的=与 IN 进行分片的场景。
范围分片算法 RangeShardingAlgorithm
用于处理使用单一键作为分片键的 BETWEEN AND、>、<、>=、<=进行分片的场景。
复合分片算法 ComplexKeysShardingAlgorithm
用于处理使用多键作为分片键进行分片的场景，多个分片键的逻辑较复杂，需要应用开发者自行处理其中的复杂度。
Hint 分片算法 HintShardingAlgorithm
用于处理使用 Hint 行分片的场景。对于分片字段非 SQL 决定，而由其他外置条件决定的场景，可使用 SQL Hint 灵活的注入分片字段。例：内部系统，按照员工登录主键分库，而数据库中并无此字段。SQL Hint 支持通过 Java API 和 SQL 注释两种方式使用。

分片策略（ShardingStrategy）

分片策略包含分片键和分片算法，真正可用于分片操作的是分片键 + 分片算法，也就是分片策略。目前提供 5 种分片策略。

标准分片策略 StandardShardingStrategy
- 只支持单分片键，提供对 SQL 语句中的=, >, <, >=, <=, IN 和 BETWEEN AND 的分片操作支持。提供 PreciseShardingAlgorithm 和 RangeShardingAlgorithm 两个分片算法。
- PreciseShardingAlgorithm 是必选的，RangeShardingAlgorithm 是可选的。但是 SQL 中使用了范围操作，如果不配置 RangeShardingAlgorithm 会采用全库路由扫描，效率低。
复合分片策略 ComplexShardingStrategy
- 支持多分片键。提供对 SQL 语句中的=, >, <, >=, <=, IN 和 BETWEEN AND 的分片操作支持。由于多分片键之间的关系复杂，因此并未进行过多的封装，而是直接将分片键值组合以及分片操作符透传至分片算法，完全由应用开发者实现，提供最大的灵活度。
行表达式分片策略 InlineShardingStrategy
- 只支持单分片键。使用 Groovy 的表达式，提供对 SQL 语句中的=和 IN 的分片操作支持，对于简单的分片算法，可以通过简单的配置使用，从而避免繁琐的 Java 代码开发。如: t_user_$->{u_id % 8} 表示 t_user 表根据 u_id 模 8，而分成 8 张表，表名称为 t_user_0 到 t_user_7。
Hint 分片策略 HintShardingStrategy
- 通过 Hint 指定分片值而非从 SQL 中提取分片值的方式进行分片的策略。
不分片策略 NoneShardingStrategy

分片策略配置

对于分片策略存有数据源分片策略和表分片策略两种维度，两种策略的 API 完全相同。

数据源分片策略
用于配置数据被分配的目标数据源。
表分片策略
用于配置数据被分配的目标表，由于表存在于数据源内，所以表分片策略是依赖数据源分片策略结果的。

1.3.2 流程剖析

ShardingSphere 3 个产品的数据分片功能主要流程是完全一致的，如下图所示。

SQL 解析

SQL 解析分为词法解析和语法解析。先通过词法解析器将 SQL 拆分为一个个不可再分的单词。再使用语法解析器对 SQL 进行理解，并最终提炼出解析上下文。

Sharding-JDBC 采用不同的解析器对 SQL 进行解析，解析器类型如下：

MySQL 解析器
Oracle 解析器
SQLServer 解析器
PostgreSQL 解析器
默认 SQL 解析器

查询优化

负责合并和优化分片条件，如 OR 等。

SQL 路由

根据解析上下文匹配用户配置的分片策略，并生成路由路径。目前支持分片路由和广播路由。

SQL 改写

将 SQL 改写为在真实数据库中可以正确执行的语句。SQL 改写分为正确性改写和优化改写。

SQL 执行

通过多线程执行器异步执行 SQL。

结果归并

将多个执行结果集归并以便于通过统一的 JDBC 接口输出。结果归并包括流式归并、内存归并和使用装饰者模式的追加归并这几种方式。

1.3.3 SQL 使用规范

SQL 使用规范

支持项

路由至单数据节点时，目前 MySQL 数据库 100% 全兼容，其他数据库完善中。

路由至多数据节点时，全面支持 DQL、DML、DDL、DCL、TCL。支持分页、去重、排序、分组、聚合、关联查询（不支持跨库关联）。以下用最为复杂的查询为例：

SELECT select_expr [, select_expr ...] FROM table_reference [, table_reference ...]

[WHERE predicates] [GROUP BY {col_name | position} [ASC | DESC], ...]

[ORDER BY {col_name | position} [ASC | DESC], ...]

[LIMIT {[offset,] row_count | row_count OFFSET offset}]

不支持项（路由至多数据节点）

不支持 CASE WHEN、HAVING、UNION (ALL)

支持分页子查询，但其他子查询有限支持，无论嵌套多少层，只能解析至第一个包含数据表的子查询，一旦在下层嵌套中再次找到包含数据表的子查询将直接抛出解析异常。

例如，以下子查询可以支持：

SELECT COUNT(*) FROM (SELECT * FROM b_order o)

以下子查询不支持 3 层嵌套：

SELECT COUNT(*) FROM (SELECT * FROM b_order o WHERE o.id IN (SELECT id FROM b_order WHERE status = ?))

简单来说，通过子查询进行非功能需求，在大部分情况下是可以支持的。比如分页、统计总数等；而通过子查询实现业务查询当前并不能支持。

由于归并的限制，子查询中包含聚合函数目前无法支持。

不支持包含 schema 的 SQL。因为 ShardingSphere 的理念是像使用一个数据源一样使用多数据源，因此对 SQL 的访问都是在同一个逻辑 schema 之上。

当分片键处于运算表达式或函数中的 SQL 时，将采用全路由的形式获取结果。

例如下面 SQL，create_time 为分片键：

SELECT * FROM b_order WHERE to_date(create_time, 'yyyy-mm-dd') = '202005-05';

由于 ShardingSphere 只能通过 SQL 字面提取用于分片的值，因此当分片键处于运算表达式或函数中时，ShardingSphere 无法提前获取分片键位于数据库中的值，从而无法计算出真正的分片值。

不支持的 SQL 示例

-- VALUES语句不支持运算 表达式
INSERT INTO tbl_name (col1, col2, …) VALUES(1+2, ?, …) 
-- INSERT .. SELECT
INSERT INTO tbl_name (col1, col2, …) SELECT col1, col2, … FROM tbl_name WHERE col3 = ? 
--having
SELECT COUNT(col1) as count_alias FROM tbl_name GROUP BY col1 HAVING count_alias > ? 
--union
SELECT * FROM tbl_name1 UNION SELECT * FROM tbl_name2 
--union all
SELECT * FROM tbl_name1 UNION ALL SELECT * FROM tbl_name2
--包含schema
SELECT * FROM ds.tbl_name1
--同时使用普通聚合函数 和DISTINCT
SELECT SUM(DISTINCT col1), SUM(col1) FROM tbl_name 
--会导致 全路由
SELECT * FROM tbl_name WHERE to_date(create_time, ‘yyyy-mm-dd’) = ?

分页查询

完全支持 MySQL 和 Oracle 的分页查询，SQLServer 由于分页查询较为复杂，仅部分支持.

性能瓶颈：

查询偏移量过大的分页会导致数据库获取数据性能低下，以 MySQL 为例

SELECT * FROM b_order ORDER BY id LIMIT 1000000, 10

这句 SQL 会使得 MySQL 在无法利用索引的情况下跳过 1000000 条记录后，再获取 10 条记录，其性能可想而知。而在分库分表的情况下（假设分为 2 个库），为了保证数据的正确性，SQL 会改写为：

SELECT * FROM b_order ORDER BY id LIMIT 0, 1000010

即将偏移量前的记录全部取出，并仅获取排序后的最后 10 条记录。这会在数据库本身就执行很慢的情况下，进一步加剧性能瓶颈。因为原 SQL 仅需要传输 10 条记录至客户端，而改写之后的 SQL 则会传输 1,000,010 * 2 的记录至客户端。

ShardingSphere 的优化：

ShardingSphere 进行了以下 2 个方面的优化。

首先，采用流式处理 + 归并排序的方式来避免内存的过量占用。

其次，ShardingSphere 对仅落至单节点的查询进行进一步优化。

分页方案优化:

由于 LIMIT 并不能通过索引查询数据，因此如果可以保证 ID 的连续性，通过 ID 进行分页是比较好的解决方案：

SELECT * FROM b_order WHERE id > 1000000 AND id <= 1000010 ORDER BY id

或通过记录上次查询结果的最后一条记录的 ID 进行下一页的查询：

SELECT * FROM b_order WHERE id > 1000000 LIMIT 10

1.3.4 其他功能

Inline 行表达式

InlineShardingStrategy：采用 Inline 行表达式进行分片的配置。

Inline 是可以简化数据节点和分片算法配置信息。主要是解决配置简化、配置一体化。

语法格式：

行表达式的使用非常直观，只需要在配置中使用 ${expression} 或者 $->{ expression } 标识行表达式即可。例如：

${begin..end} 表示范围区间
${[unit1, unit2, unit_x]} 表示枚举值

行表达式中如果出现多个 ${} 或 $->{} 表达式，整个表达式结果会将每个子表达式结果进行笛卡尔 (积)组合。例如，以下行表达式：

${['online', 'offline']}_table${1..3} 
$->{['online', 'offline']}_table$->{1..3}
最终会解析为：
online_table1, online_table2, online_table3, offline_table1, offline_table2, offline_table3

数据节点配置

对于均匀分布的数据节点，如果数据结构如下：

db0

├── b_order2 └── b_order1 db1 ├── b_order2 └── b_order1

用行表达式可以简化为：

db${0..1}.b_order${1..2} 或者 db$->{0..1}.b_order$->{1..2}

对于自定义的数据节点，如果数据结构如下：

db0

├── b_order0

└── b_order1

db1

├── b_order2

├── b_order3

└── b_order4

用行表达式可以简化为：

db0.b_order$->{0..1},db1.b_order->${2..4}

分片算法配置

行表达式内部的表达式本质上是一段 Groovy 代码，可以根据分片键进行计算的方式，返回相应的真实数据源或真实表名称。

ds${id % 10} 或者 ds$->{id % 10}

结果为：ds0、ds1、ds2… ds9

分布式主键

ShardingSphere 不仅提供了内置的分布式主键生成器，例如 UUID、SNOWFLAKE，还抽离出分布式主键生成器的接口，方便用户自行实现自定义的自增主键生成器

内置主键生成器

UUID

采用 UUID.randomUUID()的方式产生分布式主键。

SNOWFLAKE

在分片规则配置模块可配置每个表的主键生成策略，默认使用雪花算法，生成 64bit 的长整型数据。

自定义主键生成器

自定义主键类，实现 ShardingKeyGenerator 接口

按 SPI 规范配置自定义主键类在 Apache ShardingSphere 中，很多功能实现类的加载方式是通过 SPI 注入的方式完成的。注意：在 resources 目录下新建 META-INF 文件夹，再新建 services 文件夹，然后新建文件的名字为 org.apache.shardingsphere.spi.keygen.ShardingKeyGenerator，打开文件，复制自定义主键类全路径到文件中保存。

自定义主键类应用配置

#对应主键字段名 
spring.shardingsphere.sharding.tables.t_book.key-generator.column=id 
#对应主键类重写getType方法返回内容
spring.shardingsphere.sharding.tables.t_book.keygenerator.type=LAGOUKEY

1.4 读写分离剖析实战

读写分离是通过主从的配置方式，将查询请求均匀的分散到多个数据副本，进一步的提升系统的处理能力。

这篇关于MySQL高级实战的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！