亿级表优化「TIDB 分区篇」，值得收藏
这是亿级别表优化的第二篇，对第一篇感兴趣的可以看看。
写作背景
距上次写亿级别优化已经有一个多月了，这段时间也没闲着，Q1 对模型做了梳理，重构了这部分业务，主要做了下面这些优化
- 数据模型优化（终于狠下心做了减法，去掉了 2 个模型）。
- 做了分区表，数据日增量非常快，单表遇到读写瓶颈。
- 复杂 SQL 优化，上次优化遗留的顽疾。
- 数据清洗（流失数据、已删除数据备份归档）。
所以，我还是总结这段时间成果吧！分享这次优化的经验。
距离上次数据量统计，数据已经增长了 2000W。
名词解释
垂直分库
垂直分库是指根据业务来分库，不同的业务使用不同的数据库。例如，营销自动化业务和营销活动都存在高并发场景，如果使用同一个库，会占用一定的连接数，所以我们可以将数据库分为营销自动化库和营销活动库。在一些特定情况下我们还会分集群，不同业务模块使用不同集群。
垂直分表
垂直分表则是指把一张表中的字段，拆分为多张表（我习惯性称为子表或者附表）将数据散落在多张表内，一开始将一些不经常使用的字段拆分到另一张表中，亦或是在迭代过程中扩展子表来满足需求（迭代中扩展子表的方式在之前版本中前尝试过，扩展了好几张表最后都合并了，随着业务需求增加，数据查询越来越复杂，最后舍弃了这种方式）。
水平分表
水平切分（分表）是按照某种规则，将一个表的数据分散到多个物理独立的数据库服务器中，形成“独立”的数据库“分片”。一般是按照 Range 或 Hash 取模。
TIDB 分区表
Table Partition 是指根据一定规则，将数据库中的一张表分解成多个更小的容易管理的部分。从逻辑上看只有一张表，但是底层却是由多个物理分区组成。
在 TiDB 中分区表是一个独立的逻辑表，但是底层由多个物理子表组成。物理子表其实就是普通的表，数据按照一定的规则划分到不同的物理子表类内。程序读写的时候操作的还是逻辑表名字，TiDB 服务器自动去操作分区的数据。
想了解更多参考以下文档
TiDB 源码阅读系列文章（二十）Table Partition | PingCAP
TIDB 支持的分区类型
Range 分区、Range COLUMNS 分区、Range INTERVAL 分区、List 分区、List COLUMNS 分区、Hash 分区、Key 分区。想了解更多语法和案例参考以下文档
优化思路
模型整合
早期对业务未来发展判断不准确以及过度设计，认为每个数据模型都应该满足数据库三范式，但付出的代价也非常高昂。
- 某些业务需要多次数据库 IO ，否则用 join，开发比较麻烦，另外，性能也不高；
- 有几张表数据量比较大，维护成本高；
- 另外，模型 a 跟模型 b 是一对多关系，模型 a 数据写入存在并发问题，不得不牺牲并发能力，注：不使用分布式锁，用消息队列，根据某一个业务标志把一类数据打到同一个分区，tob 行业，某些大租户数据倾斜是比较严重的…
这次梳理模型的原则是比较简单的。
- 数据模型是否满足未来 1-2 年的迭代，有一个模型因为产品规划需要支持多平台，我们重命名了表名字和字段名，更符合未来的产品方向。
- 去掉数据模型，是否影响产品逻辑，有2个模型因为第一版过度设计+业务不清晰，最后导致查询逻辑和模型维护复杂，再加上产品和技术不断演进有更好的解决方案，及时把模型优化了。
- 字段冗余，把该冗余字段冗余出来，后续分区表避免 Join 连表查询。这里有一问题大家可以思考下，数据冗余后，如果数据修改后数据更新怎么办？文章末尾给答案。
分区表
什么时候用分库分表？
在单表单库下，当数据表的数据量逐渐累积到一定的数量时，操作数据库的性能会出现明显下降，即使我们使用索引优化，性能依然会存在瓶颈。如果每日数据增长大，我们应该考虑分表。
面对大量数据，除了单表的性能比较差以外，数据库连接数、磁盘 I/O 以及网络吞吐等资源都是有限的，并发能力也是有限的。所以，在一些大数据量且高并发的业务场景中，也需要考虑分表分库来提升数据库的并发处理能力。
如何分库分表？
分库分表业界方案都是很成熟的，有两种方案，垂直切分和水平切分两种。名词解释那节已经解释过了，这里不过多解释。
分库分表依赖数据库中间件，但是，某些数据库产品都自带了分区能力，比如：tidb、腾讯 mysql 数据库…将复杂分区能力集成到产品中，大大减少了研发同学工作量。
TDSQL MySQL版 水平分表-产品简介-文档中心-腾讯云
名词解释那节，介绍过 TIDB 分区类型，如何在这些分区类型中选择适合的分区？一切都从业务说起。
- 营销自动化业务主要是给员工下发任务，员工侧要展示任务，所以是按照租户 ID+员工 ID 纬度聚合数据；
- 公司管理者需要看当前公司下所有员工任务完成情况，所以是按照租户 ID 纬度聚合数据，并且聚合这些数据跟时间无关。
所以，我们需要一种按照一定规则（租户 ID），将数据均匀打散到一定数量的分区里面，并且使用上越简单越好。
如果感兴趣可以看看这篇issue，「何时使用 TIDB 分区表」
综上所述，我们采用hash分区，参考链接如下：
TIDB 分区表比较简单，简单介绍案例。
DROP TABLE IF EXISTS `task`;
CREATE TABLE `task`
(
`id` bigint(255) NOT NULL COMMENT '主键id',
`tenant_id` bigint(20) NOT NULL COMMENT '租户ID'
) ENGINE = InnoDB
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_bin
PARTITION BY HASH (tenant_id)
PARTITIONS 8;
PARTITIONS 指定分区数量为 8，不指定默认为1；指定分区键为 tenant_id。
通过下面语句查看分区信息SHOW TABLE REGIONS | PingCAP 文档中心
SHOW TABLE task REGIONS;
SHOW CREATE TABLE task;
插入数据
INSERT INTO task VALUES (1,2),(100,2),(200,3);
看执行计划
explain analyze select * from task;
对分区表数据操作必须带分区键，否则，全分区扫数据。 
创建分区表是按租户 ID分区，where 条件一定要带租户 ID 才能命中所在分区，修改 SQL 如下：
explain analyze
select *
from task
where tenant_id = 2
and id = 1;
看执行计划 
命中 P2 分区，因为没有建索引，所以在 P2 分区全表扫描，扫描范围缩小了。
SQL 优化
-
复杂数据分析业务迁移 OLAP 数据库
报表类业务迁移 Doris，这类业务不适合放在 TIDB。我们刚好有一场景是管理端列表页面需要聚合报表类数据（很早就想优化，但迟迟未动手），SQL 比较复杂索引调优不好做，这类 SQL qps 一上去整张表的延迟绝对增加。另外，为了这条 sql 加了一条索引。
-
缩小 SQL 扫描范围
我们有一平台性调度任务，扫全表更改数据状态，每次更新 5000，循环更新。每次活动期间这条 SQL 肯定会被运维拉出来批斗。优化策略比较简单，调度任务细化，按照某一 id 创建调度任务，把调度打散在不同时间段，避免数据集中在同一时刻被处理。
数据清洗
单表--> 分区表，N亿+的数据怎么同步到分区表呢？
因为本次梳理模型有些字段被冗余在新表，数据同步不单是A表到B表，需要从其它表查询数据在写入，我们常用的方案是 oplog、binlog.... 日志采集同步到消息队列（kafka、pulsar 等），启消费组消费订正数据（我最常用也是最可靠的），数据量大的场景特别爽，处理存量数据的同时还能保证增量数据同步处理。这方案也有几个问题需要注意。
- 如果数据量大，查询时控制并发、另外攒批查询，避免高峰期数据库 QPS 高造成稳定性问题。
- 查询业务方接口加上缓存，避免流量高打垮业务方系统。
- 数据订正支持重复操作，若中途中断，亦或订正代码有 bug 造成整数据订正中断，也可重跑数据，或断点续跑数据。
- 数据订正支持按照某一个规则订正，比如按租户 id，通过一批租户验证线上订正脚本正确性。
末尾彩蛋
相比亿级别表优化 SQL 篇，本文重点介绍模型优化和分区表。当然，分区表并不是最终方案，随着数据量不断增加，架构会继续演进，如果对亿级别优化感兴趣请关注我哦。
下篇介绍亿级表优化之数据冷热分离。
下面回答下遗留问题，冗余字段如果数据修改后数据更新怎么办？一般冗余字段前提是不频繁被修改的（尤其是大表频繁修改肯定会有稳定性问题），甚至还有更严格的为不会变数据做字段冗余。另外，早期我们冗余字段出现过大批量更新问题，一般这种慢SQL都在20s以上，如果索引优化不好扫描行数几百万都常见。