首页 > 学术百科

一次shardingjdbc踩坑引起的胡思乱想

⼀次shardingjdbc踩坑引起的胡思乱想

项⽬⾥⾯的⼀个分表⽤到了sharding-jdbc

当时纠结过是⽤mycat还是⽤sharding-jdbc的, 但是最终还是⽤了sharding-jdbc, 原因如下:

1. mycat⽐较重, 相对于sharding-jdbc只需导⼊jar包就⾏, mycat还需要部署维护⼀个中间件服务.由于我们只有⼀个表需要分表, 直接⽤轻量级的sharding-jdbc即可.

2. mycat作为⼀个中间代理服务, 难免有性能损耗

3. 其他组⽤mycat的时候出现过⽣产BUG

然⽽sharding-jdbc也同样是坑坑洼洼不断的, 我们从2.x版本改成4.x版本, ⼜从4.x版本降到了3.x版本，每⼀个版本都踩到了坑(有些是官⽅的，有些是由于我们项⽬依赖的),

最终不得已改动了⼀下源码才趟过去(其实就是注释了⼀⾏代码).

今天就来聊⼀下其中的⼀个坑--分表分页

问题描述

背景

CREATE TABLE `order_00` (

`id` bigint(18) NOT NULL AUTO_INCREMENT COMMENT '逻辑主键',

`orderId` varchar(32) NOT NULL COMMENT '订单ID',

`CREATE_TM` datetime DEFAULT NULL COMMENT '订单创建时间',

PRIMARY KEY (`ID`) USING BTREE,

UNIQUE KEY `IDX_ORDER_POSTID` (`orderId`) USING BTREE,

KEY `IDX_ORDER_CREATE_TM` (`CREATE_TM`) USING BTREE

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=COMPACT COMMENT='订单表';

CREATE TABLE `order_01` (

`id` bigint(18) NOT NULL AUTO_INCREMENT COMMENT '逻辑主键',

`orderId` varchar(32) NOT NULL COMMENT '订单ID',

`CREATE_TM` datetime DEFAULT NULL COMMENT '订单创建时间',

PRIMARY KEY (`ID`) USING BTREE,

UNIQUE KEY `IDX_ORDER_POSTID` (`orderId`) USING BTREE,

KEY `IDX_ORDER_CREATE_TM` (`CREATE_TM`) USING BTREE

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=COMPACT COMMENT='订单表';

CREATE TABLE `order_02` (

`id` bigint(18) NOT NULL AUTO_INCREMENT COMMENT '逻辑主键',

`orderId` varchar(32) NOT NULL COMMENT '订单ID',

`CREATE_TM` datetime DEFAULT NULL COMMENT '订单创建时间',

PRIMARY KEY (`ID`) USING BTREE,

UNIQUE KEY `IDX_ORDER_POSTID` (`orderId`) USING BTREE,

KEY `IDX_ORDER_CREATE_TM` (`CREATE_TM`) USING BTREE

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=COMPACT COMMENT='订单表';

假设有以上三个分表, 分表逻辑⽤orderId取模, 即orderId=0的写到order_00,orderId=1的写到order_01,orderId=2的写到order_02.

备注: 这⾥为啥不⽤时间分表⽽⽤orderId做hash, 当时也是颇有争议的.

理论上订单表更适合使⽤时间做分表, 这样⼀来时间越⽼的数据访问的频率越⼩, 旧的分表逐渐就会成为冷表, 不再被访问到.

当时负责⼈的说法是, 由于这个表读写频率都⾼(⽽且场景中经常需要读主库), ⽤orderId分表可以均衡写负载和读负载.

虽然是有点牵强, 但也有⼀定道理, 就先这么实现了

业务上需要根据orderId或CREATE_TM进⾏分页查询, 即查询sql的mybatis写法⼤概如下:

select

from ORDER

AND orderId=#{orderId , jdbcType=VARCHAR}

</if>

AND create_tm >= concat(#{createTmStartStr, jdbcType=VARCHAR},' 00:00:00')

</if>

AND create_tm <= concat(#{createTmEndStr, jdbcType=VARCHAR},' 23:59:59')

</if>

limit #{page.begin}, #{page.pageSize}

</select>

⽤过sharding-jdbc的都知道, sharding-jdbc⼀共有5种分⽚策略，如下图所⽰. 没⽤过的可以参考

除了Hint分⽚策略, 其他的分⽚策略都要求sql的where条件需要包含分⽚列(在我们的表中是orderId), 很明显我们的业务场景中不能保证sql的

where条件中⼀定会包含有orderId, 所以我们只能使⽤HintShardingStrategy，将页⾯的查询条件传递给分⽚策略算法中, 再判断查询哪个表, ⼤概代码如下

public class OrderHintShardingAlgorithm implements HintShardingAlgorithm {

public static final String ORDER_TABLE = "ORDER";

@Override

public Collection<String> doSharding(Collection<String> availableTargetNames, ShardingValue shardingValue) {

ListShardingValue<String> listShardingValue = (ListShardingValue<String>) shardingValue;

List<String> list = Values());

List<String> actualTable = wArrayList();

// 页⾯上的查询条件会以json的⽅式传到shardingValue变量中

String json = (0);

OrderQueryCondition req = JSON.parseObject(json, OrderQueryCondition.class);

String orderId = OrderId();

// 查询条件没有orderId, 要查所有的分表

if(StringUtils.isEmpty(orderId)){

// 所有的分表

for(int i = 0 ; i< 3; i++){

actualTable.add(ORDER_TABLE + "_0" + i);

}

}else{

// 如果指定了orderId, 只查orderId所在的分表即可

long tableSuffix = HashInteger(orderId);

actualTable.add(ORDER_TABLE + "_0" + tableSuffix);

}

// actualTable中包含sharding-jdbc实际会查询的表

return actualTable;

}

这样⼦, 如果我们根据orderId来查询的话, sharding-jdbc最终执⾏的sql就是(假设每页10条):

select * from ORDER_XX where orderId = ? limit 0 ,10

如果查询条件没有orderId, 那么最终执⾏的sql就是3条(假设每页10条):

select * from ORDER_00 where create_tm >= ? and create_tm <= ? limit 0 ,10 ;

select * from ORDER_01 where create_tm >= ? and create_tm <= ? limit 0 ,10 ;

select * from ORDER_02 where create_tm >= ? and create_tm <= ? limit 0 ,10 ;

注意在有多个分表的情况下, 每个表都取前10条数据出来(⼀共30条), 然后再排序取前10条, 这样的逻辑是不对的. sharding-jdbc给了个, 如果下图:

图中的例⼦中，想要取得两个表中共同的按照分数排序的第2条和第3条数据，应该是95和90。由于执⾏的SQL只能从每个表中获取第2条和第3条数据，即从t_score_0表中获取的是90和80；从t_score_0表中获取的是85和75。因此进⾏结果归并时，只能从获取的90，80，85和75之中进⾏归并，那么结果归并⽆论怎么实现，都不可能获得正确的结果.

那怎么办呢?

sharding-jdbc的做法就改写我们的sql, 先查出来所有的数据, 再做归并排序

例如查询第2页时

原sql是:

select * from ORDER_00 where create_tm >= ? and create_tm <= ? limit 10 ,10 ;

select * from ORDER_01 where create_tm >= ? and create_tm <= ? limit 10 ,10 ;

select * from ORDER_02 where create_tm >= ? and create_tm <= ? limit 10 ,10 ;

会被改写成:

select * from ORDER_00 where create_tm >= ? and create_tm <= ? limit 0 ,20 ;

select * from ORDER_01 where create_tm >= ? and create_tm <= ? limit 0 ,20 ;

select * from ORDER_02 where create_tm >= ? and create_tm <= ? limit 0 ,20 ;

查询第3页时

原sql是:

select * from ORDER_00 where create_tm >= ? and create_tm <= ? limit 20 ,10 ;

select * from ORDER_01 where create_tm >= ? and create_tm <= ? limit 20 ,10 ;

select * from ORDER_02 where create_tm >= ? and create_tm <= ? limit 20 ,10 ;

会被改写成:

select * from ORDER_00 where create_tm >= ? and create_tm <= ? limit 0 ,30 ;

select * from ORDER_01 where create_tm >= ? and create_tm <= ? limit 0 ,30 ;

select * from ORDER_02 where create_tm >= ? and create_tm <= ? limit 0 ,30 ;

当然, ⼤家肯定会觉得这样处理性能会很差, 其实事实上也的确是, 不过sharing-jdbc是在这个基础上做了优化的，就是上⾯提到的"归并",

具体归并过程可以查看官⽹的说明.篇幅⽐较长, 我这⾥就不再贴出来了

⼤概的逻辑就是先查出所有页的数据, 然后通过流式处理跳过前⾯的页，只取最终需要的页，最终达到分页的⽬的

踩坑

既然sharding-jdbc都已经优化好了, 那么我们踩到的坑到底是什么呢?

颜氏家训pdf

听我慢慢道来

在io.shardingsphere.statement.ShardingPreparedStatement#getResultSet()中有个逻辑,

如果查询的分表数只有⼀个的话, 就不会做归并的逻辑(然⽽就算只查⼀个分表, sql的limit⼦句也会被改写了), 如图:

回到我们的业务场景, 如果查询条件包含了orderId的话, 因为可以定位到具体的表, 所以最终需要查询的分表就只有⼀个.

那么问题就来了, 由于sharding-jdbc把我们的sql的limit⼦句给改写了,

后⾯却由于只查⼀个分表⽽没有做归并(也就是没有跳过前⾯的页)，所以最终不管是查询第⼏页，执⾏的sql都是(假设页⼤⼩是10000):

select * from ORDER_XX where orderId = ? limit 0 ,10000

select * from ORDER_XX where orderId = ? limit 0 ,20000

select * from ORDER_XX where orderId = ? limit 0 ,30000

select * from ORDER_XX where orderId = ? limit 0 ,40000

......

这样就导致了⼀个问题, 不管我传的页码是什么, sharding-jdbc都会给我返回同⼀条数据. 很明显这样

是不对的.

当然, ⼼细的朋友可能会发现了, 由于orderId是个唯⼀索引, 所以肯定只有⼀条数据, 所以永远不会存在查询第⼆页的情况.

正常来说的确是这样, 然⽽在我们的代码⾥⾯, 还有个⽼逻辑: 导出查询结果(就是导出所有页的数据)时, 会异步地在后台⼀页⼀页地

导出, 直到导出了所有的页或者达到了查询次数上限(假设是查询1万次).

所以在根据orderId导出的时候, 因为每⼀页都返回相同的数据, 所以判断不了什么时候是"导完了所有的页", 所以正确结果本应该是只有⼀条数据的, 但是在sharding-jdbc下却执⾏了⼀万次, 导出了⼀万条相同的数据, 你说这个是不是坑呢?

知道问题所在, 那解决就简单了. 但是本⽂并不是想聊怎么解决这个问题的, ⽽是想聊聊通过这个问题引起的思考:

在mysql分表环境下, 如何⾼效地做分页查询?

对mysql分页的思考

limit 优化

在讨论分表环境下的分页性能之前, 我们先来看⼀下单表环境下应该实现分页.

众所周知, 在mysql⾥⾯实现分页只需要使⽤limit⼦句即可, 即

select * from order limit (pageNo-1) * pageSize, pageSize

由于在mysql的实现⾥⾯, limit offset, size是先扫描跳过前⾯的offset条数据，再取size条数据.

当pageNo越⼤的时候, offset也会越⼤, mysql扫描的数据也越⼤, 所以性能会急剧下降.

秦皇岛pm2.5因此, 分页第⼀个要解决的问题就是当pageNo过⼤时, 怎么优化性能.

第⼀个⽅案是的⽅案.

总结来说就是把sql改写成这样:

select * from order where id >= (select id from order limit (pageNo-1) * pageSize, 1) limit pageSize

利⽤索引覆盖的原理, 先直接定位当前页的第⼀条数据的最⼩id, 然后再取需要的数据.

这样的确可以提⾼性能, 但是我认为还是没有彻底解决问题, 因为当pageNo过⼤的时候, mysql还是会需要扫描很多的⾏来到最⼩的id. ⽽扫描的那些⾏都是没有意义.

scroll 游标查询

游标查询是elasticSearch⾥⾯的⼀个术语, 但是我这⾥并不是指真正的scroll查询, ⽽是借鉴ES⾥⾯的思想来实现mysql的分页查询.

所谓的scroll就是滚动, ⼀页⼀页地查. ⼤概的思想如下:

1. 查询第1页

select * from order limit 0, pageSize;

2. 记录第1页的最⼤id: maxId

3. 查询第2页

select * from order where id > maxId limit pageSize

4. 把maxId更新为第2页的最⼤id

... 以此类推

可以看到这种算法对于mysql来说是毫⽆压⼒的, 因为每次都只需要扫描pageSize条数据就能达到⽬的. 相对于上⾯的索引覆盖的⽅案, 可以极⼤地提⾼查询性能.

当然它也有它的局限性:

1. 性能的提⾼带来的代价是代码逻辑的复杂度提⾼. 这个分页逻辑实现起来⽐较复杂.

2. 这个算法对业务数据是有要求的, 例如id必须是单调递增的,⽽且查询的结果需要是⽤Id排序的.

如果查询的结果需要按其他字段(例如createTime)排序, 那就要求createTime也是单调的, 并把算法中的id替换成createTime.

有某些排序的场景下, 这种算法会不适⽤.

3. 这个算法是需要业务上做妥协的, 你必须说服你的产品经理放弃"跳转到特定页"的功能, 只能通过点击"下⼀页"来进⾏翻页.

(这才是scroll的含义, 在⼿机或平板上，只能通过滚动来翻页,⽽⽆法直接跳转到特定页)

分表环境下的分页查询

如上⾯讨论, 在单表环境下, 想要实现⾼效的分页, 还是相对⽐较简单的.

那如果在分表环境下, 分页的实现会有什么不同呢?

正如上⾯提到的, sharding-jdbc中已经论证过了, 分表环境的分页查询, 如果不把

select * from ORDER_00 where create_tm >= ? and create_tm <= ? limit (pageNo-1) * pageSize ,pageSize ;

select * from ORDER_01 where create_tm >= ? and create_tm <= ? limit (pageNo-1) * pageSize ,pageSize;

select * from ORDER_02 where create_tm >= ? and create_tm <= ? limit (pageNo-1) * pageSize ,pageSize ;

改写成

select * from ORDER_00 where create_tm >= ? and create_tm <= ? limit 0 , (pageNo-1) * pageSize + pageSize ;

select * from ORDER_01 where create_tm >= ? and create_tm <= ? limit 0 , (pageNo-1) * pageSize + pageSize;

select * from ORDER_02 where create_tm >= ? and create_tm <= ? limit 0 , (pageNo-1) * pageSize + pageSize ;

那么最终查出来的数据, 很有可能不是正确的数据. 所以在分表环境下, 上⾯所说的"索引覆盖法"和"游标查询法"肯定是都不适⽤了的. 因为必须查出所有节点的数据，再进⾏归并, 那才是正确的数据.

因此, 要在分表环境下实现分页功能, 基本上是要对limit⼦句进⾏改写了的.

先来看sharing-jdbc的解决⽅案, 改写后的limit 0 , (pageNo-1) * pageSize + pageSize 和原来的limit (pageNo-1) * pageSize, pageSize对⽐，数据库端的查询压⼒都是差不多的, 因为都是要差不多要

扫描(pageNo-1) * pageSize ⾏才能取得到数据. 不同的是改写sql后, 客户端的内存消耗和⽹络消耗变⼤了.

sharding-jdbc巧妙地利⽤流式处理和优先级队列结合的⽅式,

消除了客户端内存消耗的压⼒, 但是⽹络消耗的影响依然是⽆法消除.

所以真的没有更好的⽅案了?

制氢那肯定是有的,

在这篇⽂章中, 作者提到了⼀种"⼆次查询法", 就⾮常巧妙地解决了这个分页查询的难题.

⼤家可以参考⼀下.

但是仔细思考⼀下, 还是有⼀定的局限性的:

1. 当分表数为N时, 查⼀页数据要执⾏N*2条sql.(这个⽆解, 只要分表了就必须这样)

2. 当offset很⼤的时候, 第⼀次查询中扫描offset⾏数据依然会⾮常的慢, 如果只分表不分库的话, 那么⼀次查询会在⼀个库中产⽣N条慢sql

3. 算法实现起来代码逻辑应该不简单, 如果为了⼀个分页功能写这么复杂的逻辑, 是不是划不来,

⽽且后期也不好维护拟合值

如果算法原作者看到我这⾥的鸡蛋挑⾻头, 会不会有点想打我~~

其实我想表达的意思是, 既然分表环境下的分页查询没有完美的解决⽅案的话，或者实现起来成本过⼤的话, 那是不是可以认为: 分表环境下就不应该做分页查询?

离线计算+有损服务

上⾯说到, 其实分表环境下就不适宜再做分页查询的功能.

但是业务上的需求并不是说砍就砍的, 很多情况下分页功能是必须的, 然⽽分页查询的存在通常也是为了保护数据库, 去掉了分页功能, 数据库的压⼒反⽽更⼤.

所以分表和分页只能⼆选⼀?

不, 我全都要, 分表我要, 分页我也要!

但是分页功能不在分表环境⾥⾯做, ⽽是在另外⼀张汇总表⾥⾯做分页查询的功能.

⼤概的⽅案就是:

1. 正常的业务读写分表

2. 根据具体的业务需求，例如实时计算/离线计算技术(spark, hadoop,hive, kafka等)⽣成各分表的⼀张

汇总表

3. 分页查询的接⼝直接查询汇总表

另外还要注意这个⽅案对业务来说肯定是有损的, 具体表现为:

1. 不管是离线计算还是实时计算, 都不能保证实时性, 查询结果肯定是有时延的

2. 由于汇总表是不可能包含分表的所有数据的, 所以汇总表肯定是只包含部分数据的，例如只有⼀个⽉内的，具体根据业务场景⽽定

总的来说, 就是报表系统的数据由数据仓库系统来⽣成, 但只能⽣成⽤户⾮要不可的数据，其他的都砍掉.

写这篇总结在资料的时候, 看到⼀句话:

其实分表的根本⽬的是分摊写负载, ⽽不是分摊读负载

其实是有⼀定道理的, 如果读负载过⾼, 我们可以增加缓存, 增加数据节点等很多⽅法, ⽽写负载过⾼的话, 分表基本就是势在必⾏了.

从这个理论来说, 分页是⼀个读操作, 根本就没有必要去读取分表, 从其他地⽅读取(我们这⾥是数据仓库)即可

不分表(分区 tidb mongoDb ES)

其实⼤多数mysql的表都没有必要分表的

在mysql5.5之前, 表数量⼤概在在500W之后就要进⾏优化, 在mysql5.5之后, 表数量在1KW到2KW左右才需要做优化.

在这个性能拐点之前, 可以认为mysql是完全有能⼒扛得住的.当然, 具体还要看qps以及读写冲突等的频率的.

到了性能拐点之后呢? 那就要考虑对mysql的表进⾏拆分了. 表拆分的⼿段可以是分表分库, 或者就简单的分区.

基本来说, 分区和分表带来的性能提升是⼀样的,

由于分区实际上就可以认为是mysql底层来帮我们实现分表的逻辑了, 所以相对来说分表会⽐分区带来更⾼的编码复杂度(分区就根本不⽤考虑多表分页查询的问题了).

从这个⾓度来说, ⼀般的业务直接分区就可以了.

当然, 选择分区还是分表还是需要做⼀点权衡的:

1. 表中的数据只有部分热点数据经常访问, 其他的不常访问的话, 适合⽤分区表

2. 分区表相对容易维护, 可以针对单独⼀个分区进⾏检查，优化, 批量删除⼤量数据时, 分区表会⽐⼀般的表更快莫代尔

3. 分区表可以分布在不同的物理设备上, 从⽽可以⾼效地利⽤多个硬盘

4. 如果查询条件不包含partition key的话, 分区表不⼀定有分表效率⾼

5. 如果分区表中绝对的热点数据, 每⼀条数据都有可能被访问到, 也不太适合分区

6. 如果数据量超⼤, 由于mysql只能分1024个分区, 如果1024个分区的数据都是千万以上, 那肯定是也不适合分区的了

综上所述, 如果分区表就⾜够满⾜我们的话, 那其实就没有必要进⾏分表了增加编程的复杂度了.

另外, 如果不想将数据表进⾏拆分, ⽽表的数据量⼜的确很⼤的话, nosql也是⼀个替代⽅案. 特别是那

些不需要强事务的表操作,

就很适合放在nosql, 从⽽可以避免编程的复杂度, 同时性能上也没有过多的损耗.

nosql的⽅案也有很多:

1. mongoDbpctools5.0

2. hbase

3. tidb

4. elasticSearch

当然也可以使⽤mysql+nosql结合的⽅式, 例如常规读写操作mysql, 分页查询⾛ES等等.

今天就先写到这, 有机会再写写mysql和nosql

本文发布于:2024-09-22 01:11:00，感谢您对本站的认可！

本文链接：https://www.17tex.com/xueshu/523469.html

上一篇：Beckman流式细胞仪-GALLIOS使用说明书

下一篇：Sn64Bi35Ag1锡膏规格书TDS

标签：查询分表数据需要逻辑环境

留言与评论（共有 0 条评论）