数据抽取在TiDB中应用的技术与实践总结

1 背景

我们公司有一种特殊的数据分析场景，大数据部门会定期抽取业务数据进行分析然后T+1更新报表。对于MySQL来说，可以通过添加一个从库专门用来给大数据抽数，这样就能避免因为抽数导致对业务正常访问请求受影响。

对于tidb集群来说，就不太方便添加从库来实现了，或者说成本较大，所以抽数任务对我们公司的tidb集群来说是一直都是痛点。直到近期才总结出一个影响最小的方案来解决我们公司的这类业务场景。

2、抽数的影响

先来介绍一下我们抽数场景对业务的影响，主要是基于时间线去介绍。

先简单介绍一下抽数用到的SQL：

抽取全量数据

mysql> desc select * from tb_monitor_disk_info order by id limit 10000 offset 10000; +----------------------------+----------+--------------+----------------------------+---------------------------+ | id | estRows | task | access object | operator info | +----------------------------+----------+--------------+----------------------------+---------------------------+ | Limit_10 | 10000.00 | root | | offset:10000, count:10000 | | └─TableReader_24 | 20000.00 | root | | data:Limit_23 | | └─Limit_23 | 20000.00 | cop[tiflash] | | offset:0, count:20000 | | └─TableFullScan_22 | 20000.00 | cop[tiflash] | table:tb_monitor_disk_info | keep order:true | +----------------------------+----------+--------------+----------------------------+---------------------------+ 4 rows in set (0.00 sec) mysql>

抽取增量数据

mysql> desc select * from tb_monitor_disk_info where a_time >= 2023-02-01 and a_time < 2023-02-06 order by a_time limit 10000 offset 10000; +--------------------------------+----------+-----------+------------------------------------------------------+------------------------------------------------------------------+ | id | estRows | task | access object | operator info | +--------------------------------+----------+-----------+------------------------------------------------------+------------------------------------------------------------------+ | IndexLookUp_36 | 10000.00 | root | | limit embedded(offset:10000, count:10000) | | ├─Limit_35(Build) | 20000.00 | cop[tikv] | | offset:0, count:20000 | | │ └─IndexRangeScan_33 | 20000.00 | cop[tikv] | table:tb_monitor_disk_info, index:idx_a_time(a_time) | range:[2023-02-01 00:00:00,2023-02-06 00:00:00), keep order:true | | └─TableRowIDScan_34(Probe) | 10000.00 | cop[tikv] | table:tb_monitor_disk_info | keep order:false, stats:pseudo | +--------------------------------+----------+-----------+------------------------------------------------------+------------------------------------------------------------------+ 4 rows in set (0.00 sec) mysql>

可以看到上述两类SQL中，都是通过分页的方式去遍历。其中第一类是全表扫描，第二类即便不是全表扫描但是如果过滤区间的数据量比较大的话性能也很差。

业务很喜欢使用limit offset的方式进行分页遍历，但是这种性能堪忧，尤其是tidb这种分布式系统，所以慎用。

假如[2023-02-01,2023-02-06)区间有1000w行数据，就需要查1000次，然后每次都是需要将这1000w从tikv汇总到tidb层进行排序分页，可想而知，性能会很差，而且还存在大量的网络资源浪费。

一看到这个SQL血压就飙升，第一反应就是联系业务进行优化，希望业务将limit offset改成limit的方式，即每次去改表过滤条件进行分页而不是依赖offset进行分页。但是很遗憾，这套系统太老了，而且几乎涉及了整个公司的业务线，所以不可能去动。

别问，问就是因为历史遗留问题。

下面就介绍一下这种访问方式带来的影响。

（1）tidb

早在tidb 2.0版本我们就遇到过抽数会对业务的正常访问产生影响，主要是体现在tidb网络带宽上，因为当时我们的大部分机器都是千兆网卡。在抽数的时候很容易将tidb组件所在的机器的网络跑满，然后这台机器的正常业务访问受影响。

（2）tikv

在解决完tidb的问题后，很长一段时间都没有再遇到因为抽数导致业务的正常访问受影响。直到我们在完成机房迁移后，再次遇到了抽数导致正常的业务受影响。

这时候我们的集群已经升级到4.0.13了。

原因可能是机房迁移后，千兆网卡机器都换成了双万兆网卡，所以tidb的网络不再成为瓶颈，在抽数并发上来后，tikv成为了瓶颈，出现大量的慢查询，然后导致业务正常访问大量超时。

3、解决问题

（1）解决tidb层问题

这个问题的解决也比较简单：

第一，加钱。将服务器的千兆网卡升级至万兆网卡，但是这个解决方案还是没法避免问题，因为抽数任务并发上来，万兆网卡也可能会跑满。

第二，隔离。将抽数任务与业务访问的tidb进行隔离，就是单独提供一个tidb节点（etl节点）提供给抽数任务，这样该节点网络跑满后不会影响业务的正常请求。

综合分析后，我们采取了第二种方案进行解决，这个方案成本更低，效果更明显，在很大一段时间内都没再遇到抽数导致业务的正常访问受影响。

（2）解决tikv层问题

这个问题的解决也比较简单，因为此时我们的集群已经是4.0版本，开始支持tiflash组件，支持TP/AP物理隔离，所以对于存在问题的表，直接添加tiflash组件即可。

经过大概大半年的时间将这类问题彻底解决，但是回过头来做核算的时候发现，成本飙升，tikv本身已经是三份数据，tiflash又是两份数据，这样算下来成本太高了，业务不能接受，所以解决方案还需要进一步优化。

（3）解决成本问题

使用tiflash组件可以彻底解决因抽数导致业务正常请求受影响的问题，但是成本很高，几乎是属于通过加钱来提升性能，这是我们所不能接受的，那么有没有什么方案成本低，又能解决此类问题呢？

答案当然是肯定的，那就是：

使用tiflash组件，提供全量数据。

使用ticdc组件，提供增量数据（kafka）。

使用ticdc往kafka同步数据的时候要格外注意kafka的max.message.bytes参数是否做了限制，要求cdc max-message-bytes * max-batch-size <= Kafka 的 max.message.bytes，不满足此条件的环境可能会导致同步报错。

等全量数据抽完，补全增量数据后就可以删除tiflash的数据，相当于tiflash只是临时用一下。这样就解决了抽取增量数据带来的影响，还降低了成本。

（4）其他问题 这个问题是关于在使用tiflash组件的时候发现的，抽数任务的SQL并没有走tiflash，然后经过测试发现，使用索引的查询条件就没法使用tiflash。所以从使用体验来看，tiflash并没有那么舒服，也不像网上吹虚的那样，具体测试如下：

mysql> show create table tb_monitor_disk_info\G *************************** 1. row *************************** Table: tb_monitor_disk_info Create Table: CREATE TABLE `tb_monitor_disk_info` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `rshost` varchar(20) NOT NULL DEFAULT COMMENT 主机地址, `part` varchar(50) NOT NULL DEFAULT COMMENT 分区信息, `disk_info` json DEFAULT NULL COMMENT disk信息 json串, 单位是GB, `a_time` datetime NOT NULL DEFAULT 2022-01-01 00:00:00, PRIMARY KEY (`id`), KEY `idx_rshost` (`rshost`), KEY `idx_a_time` (`a_time`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=307494024 1 row in set (0.00 sec) mysql> desc select * from tb_monitor_disk_info; +-----------------------+-------------+--------------+----------------------------+----------------------+ | id | estRows | task | access object | operator info | +-----------------------+-------------+--------------+----------------------------+----------------------+ | TableReader_7 | 53220210.00 | root | | data:TableFullScan_6 | | └─TableFullScan_6 | 53220210.00 | cop[tiflash] | table:tb_monitor_disk_info | keep order:false | +-----------------------+-------------+--------------+----------------------------+----------------------+ 2 rows in set (0.00 sec) mysql> desc select count(*) from tb_monitor_disk_info; +----------------------------+-------------+-------------------+----------------------------+----------------------------------+ | id | estRows | task | access object | operator info | +----------------------------+-------------+-------------------+----------------------------+----------------------------------+ | StreamAgg_28 | 1.00 | root | | funcs:count(Column#14)->Column#6 | | └─TableReader_29 | 1.00 | root | | data:StreamAgg_8 | | └─StreamAgg_8 | 1.00 | batchCop[tiflash] | | funcs:count(1)->Column#14 | | └─TableFullScan_25 | 53220210.00 | batchCop[tiflash] | table:tb_monitor_disk_info | keep order:false | +----------------------------+-------------+-------------------+----------------------------+----------------------------------+ 4 rows in set (0.01 sec) mysql> select count(*) from tb_monitor_disk_info; +----------+ | count(*) | +----------+ | 52916325 | +----------+ 1 row in set (0.08 sec) mysql>

可以看到tiflash是可以正常工作的，按照官方的文档，全表扫描及count这种统计是可以使用到tiflash，而且很快，5000w的数据80毫秒就出结果了。

但是下面的查询，也是业务的抽数SQL，就没法使用tiflash了。

mysql> desc select count(*) from tb_monitor_disk_info where a_time >= 2023-02-10 and a_time < 2023-02-11; +-----------------------------+------------+-----------+------------------------------------------------------+-------------------------------------------------------------------+ | id | estRows | task | access object | operator info | +-----------------------------+------------+-----------+------------------------------------------------------+-------------------------------------------------------------------+ | StreamAgg_24 | 1.00 | root | | funcs:count(Column#8)->Column#6 | | └─IndexReader_25 | 1.00 | root | | index:StreamAgg_9 | | └─StreamAgg_9 | 1.00 | cop[tikv] | | funcs:count(1)->Column#8 | | └─IndexRangeScan_23 | 1513586.15 | cop[tikv] | table:tb_monitor_disk_info, index:idx_a_time(a_time) | range:[2023-02-10 00:00:00,2023-02-11 00:00:00), keep order:false | +-----------------------------+------------+-----------+------------------------------------------------------+-------------------------------------------------------------------+ 4 rows in set (0.00 sec) mysql> select count(*) from tb_monitor_disk_info where a_time >= 2023-02-10 and a_time < 2023-02-11; +----------+ | count(*) | +----------+ | 1775971 | +----------+ 1 row in set (0.25 sec) mysql> desc select * from tb_monitor_disk_info where a_time >= 2023-02-10 and a_time < 2023-02-11; +--------------------------------+------------+-----------+------------------------------------------------------+-------------------------------------------------------------------+ | id | estRows | task | access object | operator info | +--------------------------------+------------+-----------+------------------------------------------------------+-------------------------------------------------------------------+ | IndexLookUp_13 | 1513586.15 | root | | | | ├─IndexRangeScan_11(Build) | 1513586.15 | cop[tikv] | table:tb_monitor_disk_info, index:idx_a_time(a_time) | range:[2023-02-10 00:00:00,2023-02-11 00:00:00), keep order:false | | └─TableRowIDScan_12(Probe) | 1513586.15 | cop[tikv] | table:tb_monitor_disk_info | keep order:false | +--------------------------------+------------+-----------+------------------------------------------------------+-------------------------------------------------------------------+ 3 rows in set (0.00 sec) mysql> desc select * from tb_monitor_disk_info ignore index(idx_a_time) where a_time >= 2023-02-10 and a_time < 2023-02-11; +-------------------------+-------------+--------------+----------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------+ | id | estRows | task | access object | operator info | +-------------------------+-------------+--------------+----------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------+ | TableReader_10 | 1513586.15 | root | | data:Selection_9 | | └─Selection_9 | 1513586.15 | cop[tiflash] | | ge(dbzz_monitor.tb_monitor_disk_info.a_time, 2023-02-10 00:00:00.000000), lt(dbzz_monitor.tb_monitor_disk_info.a_time, 2023-02-11 00:00:00.000000) | | └─TableFullScan_8 | 53220210.00 | cop[tiflash] | table:tb_monitor_disk_info | keep order:false | +-------------------------+-------------+--------------+----------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------+ 3 rows in set (0.00 sec) mysql>

针对上述现象，只能使用ignore index语法，使其不使用索引才会走tiflash。虽然这样比较慢，但是总体上还是不影响业务的正常访问。

也可以使用 set SESSION tidb_isolation_read_engines = "tiflash";

总结起来，我们现在处理抽数需求的操作流程如下：

添加ticdc任务，将目标表的增量数据打到kafka。

添加tiflash任务，为目标表配置tiflash副本。

提供一个单独的tidb节点（etl节点），支持抽数任务。

大数据访问etl节点，抽取全量数据（要求抽数SQL不能使用索引）后，再去消费kafka的消息补全增量数据。

删除tiflash中目标表的副本数据，下掉etl节点。

以上步骤要求串行完成，任何步骤遇到问题都要终止操作。

抽数SQL不能使用索引，否则还是会访问tikv。

tiflash、ticdc与tikv组件，etl节点与业务访问的tidb节点都是物理隔离，即部署的机器不是同一台（批）机器。

4、总结

抽数是我们公司一个重要的业务场景，具有一定的特殊性，也十分消耗资源，所以在支持这类业务的过程中遇到了很多问题，不断的总结迭代解决方案。最终形成了一套适合我们公司的解决方案。

（1）使用单独的tidb节点支持抽数任务，目的是解决tidb的网络带宽瓶颈。

（2）使用tiflash组件支持全量数据抽取，目的是将抽数（AP）与正常的业务（TP）进行物理隔离。

降低慢查询，降低tikv的io压力及网络压力，需要注意，全量抽取完毕后要及时删除tiflash的数据副本，以降低存储成本。

不过在具体使用tiflash的体验上并没有那么好，如果目标查询使用了索引，就会导致tiflash失效，还是会影响TP业务。

（3）使用ticdc组件支持增量数据的抽取，目的是释放tiflash组件以此降低存储成本。

4.0版本的ticdc可能会有很多问题，我们就踩了很多坑（4.0.13），所以建议使用6.1版本（踩过坑的集群升级到了6.1.7）。

综上所述，tidb集群支持抽数的业务场景比较麻烦，不像mysql直接挂一个从库就行，随便下游抽数。tidb集群需要打一套完整的组合拳才行。

如果存在上下游关系的架构中，一定要评估好风险，避免因调整了tidb的大小写敏感问题导致上下游同步异常。

5、写在最后

本文对数据抽取在tidb中的应用做了简单总结，各公司的业务场景也不一样，需求也不同，还可能碰上其他未知的问题，本文所有内容仅供参考。