自动筛选数据分析（冷热分离之OTS表格存储实战）

为什么要冷热分离

由于2020疫情的原因，在线教育行业提前被大家所重视，钉钉教育已经服务超过21万所学校、700万教师和1.4亿学生用户，每天大量的教育数据产生。

• 整体数据量：随着时间的积累，数据量越来越大，庞大的数据量对稳定性与性能是一个很大的挑战。
• 当前策略：分库分表，对于大单表的场景，第一个能跳出脑海的就是分库分表。

在中国互联网技术圈流传着这么一个说法：MySQL 单表数据量大于 2000 万行，性能会明显下降。事实上，这个传闻据说最早起源于百度。具体情况大概是这样的，当年的 DBA 测试 MySQL性能时发现，当单表的量在 2000 万行量级的时候，SQL 操作的性能急剧下降，因此，结论由此而来。然后又据说百度的工程师流动到业界的其它公司，随之也带去了这个信息，所以，就在业界流传开这么一个说法。再后来，阿里巴巴《Java 开发手册》提出单表行数超过 500 万行或者单表容量超过2GB，才推荐进行分库分表。对此，有阿里的黄金铁律支撑，所以，很多人设计大数据存储时，多会以此为标准，进行分表操作。

有业界传说和阿里巴巴的开发手册支撑，这个结论应该是靠谱的，毕竟实践出真知，但这背后的原理是什么呢，目前我们用的MYSQL大部分都是InnoDB引擎，现在我们就从InnoDB引擎说起来扒一扒为什么单表数据在2000W＋后会明显下降。

• 最小储存单元：InnoDB存储引擎最小储存单元就是页（Page），页可以用于存放数据也可以用于存放键值 指针，一个页的大小默认是16K。也就是说InnoDB中不管你的数量量是多少，最终占用的存储空间肯定是16K的整数倍。
• InnoDB索引结构：为什么在关心索引结构呢，因为在千万级的数据查询中如果没有索引，根本就没法查询，索引的数据结构直接影响我们的查询效率。InnoDB的索引结构是B 树，B 树的特点是叶子节点存放数据，非叶子结点存放键值＋指针。［这里我就不再分析MYSQL的索引原理了，感兴趣的同学可以看我的另一篇关于MYSQL索引原理解析的文章。］
• B 树数据存储计算：这里假设单条纪录的数据大小为1K（一般的业务数据记录也就在1K左右），那么单个叶子结节所能存储的纪录数：16K/1K=16。非叶子节点能够存储多少指针呢？一般我们的主键ID都是bigint类型，长度为8字节，而指针大小在InnoDB源码中设置为6字节，这样键值＋指针占用的大小就是14字节，一页能够存储的指针数：16K/14=1170。那么一棵高度为2的B 树能够存放的纪录数：1170*16=18720，一棵高度为3的B 树能够存放的纪录数：1170*1170*16＝21902400。在查找数据时，一次页的查找代表一次IO，而IO的字数又和B 树的高度有关，如果B 树为3层，那么通过主键索引数据时就需要3次IO，而IO的代价是非常高的，一般要控制在3以下，所以说一量数据量达到2000W ，那么B 树的高度将会变成4，从而导致每次主键索引都需要4次IO，IO次数的增加导致性能明显下降。

总结一下：单表数据量越大，B 树高度越高，查询需要IO次数越多，性能越差。这里的几个分界值就是2W和2000W，也就是说1000W和100W通过主键来索引的性能其实是差不多的，都需要2次IO。

那么在分库分表后单表数据量依然是2000W＋，这种场景怎么破局呢？从上面的分析中我们可以推导出一个MYSQL的性能公式，还是以纪录大小为1K为例：

MYSQL单表查询性能指数　＝　单表数据量/(页大小/14)*(页大小/1K)　，这个性能指数越大，性能就越低。那么在降低这个性能指数就只有两种方法：

• 降低分子：降低单表数据量。
• 增大分母：调大InnoDB的页大小。但是InnoDB页的大小还涉及表扫描的查询和批量更新等 DML 操作。对于涉及许多小写操作的 OLTP 工作负载，保持 InnoDB 页面大小接近存储设备块大小，可以最大限度地减少被重写到磁盘的未更改数据量。当页大小被调大后单个页面包含的数据行也会更大，出现页面争用的概率就会变大，而且MySQL读取数据的最小单位就是page，如果设置过大，对顺序读可能性能会有提升，但是对于随机读会极大增加负载。

我们的业务场景是CRUD比较高频且数据量比较小，同时数据的时效性要求又比较高，比如发布的作业，一般时效就是最近2天，很少有人说我要去看下我半年前的作业，然后再做一下。综合考虑下来，降低分子即降低单表数据量是能够有效提升查询性能和稳定性的可靠途径。于是冷热分离应运而生。

冷热分离的好处：

• 降低MYSQL单表数据量，提升MYSQL的单表性能。
• 大量业务冷数据转冷存，存储成本相比MYSQL可以降低很多，至少50％＋。

什么是OTS

OTS表格存储（Tablestore）是阿里云自研的多模型结构化数据存储，提供海量结构化数据存储以及快速的查询和分析服务。表格存储的分布式存储和强大的索引引擎能够支持PB级存储、千万TPS以及毫秒级延迟的服务能力。

OTS的几个核心特性：

• 全托管
• 表格存储是一种全托管的结构化数据存储，无需担心软硬件预置、配置、故障、集群扩展、安全等问题，在保证高服务可用性的同时，极大地减少了管理及运维成本。
• 模型丰富：表格存储支持多种数据模型，包括Wide column、Timeline、Timestream、Grid。
• Wide column模型：一款经典模型，目前绝大部分半结构化、结构化数据都存储在Wide column模型系统中。
• Timeline模型：表格存储自研模型，主要用于消息数据，适用于IM、Feed和物联网设备消息下推等消息系统中消息的存储和同步，目前已被广泛使用。
• Timestream模型：适用于时序数据、时空数据等核心数据场景。
• Grid模型：适用于科学大数据的存储和查询场景。
• 无缝扩展
• 表格存储通过数据分片和负载均衡技术，实现了存储无缝扩展。随着表数据量的不断增大，表格存储会进行数据分区的调整从而为该表配置更多的存储。表格存储可支持不少于10 PB数据存储量，单表可支持不少于1 PB数据存储量或1万亿条记录。
• 查询能力强：除了支持主键查询，表格存储还支持二级索引、多元索引。
• 二级索引：相当于给数据表提供了另外一种排序方式，即对查询条件预先设计了一种数据分布，可加快数据查询的效率。
• 多元索引：基于倒排索引和列式存储，支持多字段自由组合查询、模糊查询、地理位置查询、全文检索等，可解决大数据的复杂查询难题。
• 高可靠
• 表格存储将数据的多个备份存储在不同机架的不同机器上，并会在备份失效时进行快速恢复，提供99.99999999%（10个9）的可靠性。
• 数据强一致
• 表格存储保证数据写入强一致，并保证数据3副本均写入磁盘，且所有数据保持一致。写操作一旦返回成功，应用程序就能立即读到最新的数据。
• 高并发读写：表格存储支持千万级并发读写能力。

冷热分离技术方案

技术架构

冷热迁移方案

做业务最重要的就是稳定，为了保证在进行冷热数据分离过程中的系统稳定，在数据迁移的过程中一定要做到：可灰度［降低影响，提前发现］，可一键回滚［快速止血］。

1.冷数据迁移

• 通过DTS任务，根据业务规则扫描待迁移业务数据。
• 给迁移成功的业务数据打标，标识数据已经迁移到OTS。［此时数据在OTS和MYSQL中都存在］

2.对数据查询服务进行灰度验证。

• 数据查询符合预期：验证通过。
• 数据查询不符合预期：一键回滚并修复问题。

3.通过DTS任务扫描出已经迁移的业务数据并进行逻辑删除。［此时数据在OTS和MYSQL中都存在］

4.对已经逻辑删除的用户进行灰度验证。

• 数据查询符合预期：验证通过。
• 数据查询不符合预期：一键回滚并修复问题。

5.开启DTS任务中的物理删除开关。

• 针对已经迁移到OTS的数据进行物理删除，释放MYSQL存储空间。

6.扩大灰度放量，观察，直至全量。

从MYSQL迁移到OTS的实战总结

表的映射

MYSQL是结构化数据存储，如果业务需要平滑迁移的话，可以将MYSQL中的表映射到OTS中的宽表，宽表具体结构化数据功能。

与MYSQL相比的不同点：

• 建表时可以设置数据版本数，当属性列数据的版本个数超过设置的最大版本数时，系统会自动删除较早版本的。这里有一个点要特别注意下，如果表需要设置索引的话，最大的数据版本则必须设置为1。
• 建表时可以设置数据的生命周期，过期的数据系统会自动清理。这个点感觉挺好的，对于一些超过一定时间就不需要的流水数据就可以设置TTL，保证表空间大小的稳定性。
• 字段类型只支持　INTEGER(64位),DOUBLE,BOOLEAN,STRING,BINARY这5种类型，对于不支持的类型，需要用户自己进行转换。比如将Date转换成INTEGER或String。主键列只支持INTEGER,STRING,BINARY这三种类型。

OTS限制：

• 主键列个数不能超过4个。
• 主键的第一个列为分区列，相同的分区值无法再做切分。单个分区值下的所有行大小不能超过10GB 。

宽表建表语句示例：

TableMetatableMeta=newTableMeta("user_xxx"); //添加主键列 tableMeta.addPrimaryKeyColumn(newPrimaryKeySchema("user_xxx",PrimaryKeyType.STRING)); tableMeta.addPrimaryKeyColumn(newPrimaryKeySchema("org_xxx",PrimaryKeyType.INTEGER)); tableMeta.addPrimaryKeyColumn(newPrimaryKeySchema("task_xxx",PrimaryKeyType.INTEGER)); //添加属性列 tableMeta.addDefinedColumn(newDefinedColumnSchema("xxx",DefinedColumnType.INTEGER)); tableMeta.addDefinedColumn(newDefinedColumnSchema("xxxx",DefinedColumnType.INTEGER)); tableMeta.addDefinedColumn(newDefinedColumnSchema("xxxxx",DefinedColumnType.STRING)); tableMeta.addDefinedColumn(newDefinedColumnSchema("xxxxxx",DefinedColumnType.STRING)); .... inttimeToLive=-1;//数据的过期时间，单位为秒，-1表示永不过期。带索引表的数据表数据生命周期必须设置为-1。 intmaxVersions=1;//保存的最大版本数，1表示每列上最多保存一个版本即保存最新的版本。带索引表的数据表最大版本数必须设置为1。 TableOptionstableOptions=newTableOptions(timeToLive,maxVersions); ArrayList<IndexMeta>indexMetas=newArrayList<IndexMeta>(); //任务在班级维度的索引 IndexMetaindexMeta=newIndexMeta("idx_task_xxx"); indexMeta.addPrimaryKeyColumn("task_xxx");//为索引表添加主键列。 indexMeta.addPrimaryKeyColumn("org_xxx");//为索引表添加主键列。 indexMeta.addPrimaryKeyColumn("user_xxx");//为索引表添加主键列。 indexMetas.add(indexMeta); CreateTableRequestrequest=newCreateTableRequest(tableMeta,tableOptions,indexMetas);//创建数据表的同时创建索引表。 otsClient.createTable(request);

索引的映射

MYSQL中的索引映射到OTS则有三种索引。一种是全局二级索引，一种是局部索引，一种是多元索引。

• 全局二级索引：主要作用是对主键字段进行重排序，同时也可以添加一些非主键元素加入到全局二级索引中来进行数据查询。
• 局部索引：与全局索引类似，只是索引的第一个字段必须与主键的第一个字段一样。因为第一个字段决定了数据分区，局部索引实际上就是这个数据分区内的索引。
• 多元索引：索引字段可以是表中多个字段的自由组合。

全局二级索引和局部索引和MYSQL一样，查询匹配时需要满足左匹配原则才能命中索引。

与MYSQL相比的不同点：

• 全局二级索引其本质上是一张数据表，只不过表中的数据是由OTS自动关联的。同时索引不会自动回表，需要用户手动回表。
• 全局二级索引查询时都只能查询出索引中有的字段，而不能查询出索引表中没有的字段。比如索引由字段A,B,C组织，那查询结果集也只能从A,B,C这三个字段组成的集合中取。
• 全局二级索引必须包含主键的全部字段，这个点刚用起来很不习惯，因为MYSQL中的索引是可以由表中的字段任意组合的。但是后面一想也是合理的，因为目前OTS的索引不会自动回表，需要用户手动进行回表操作，如果你的索引中没有包含主键的全部字段，那么就会导致无法回表。
• 多元索引是超越MYSQL索引的存在，任意一个索引列命中即会使用索引，无需满足左匹配，这一点很强大。而且目前多元索引支持列数已达500列，基本上能够满足绝大部分的业务需求了。另外，多元索引也能自动回表，十分强大。但是一分钱一分货，这么强大的多元索引相比于全局二级索引，价格上也要更贵，差不多是3－5倍的样子，具体和数据量有一定关系，量大从优。

OTS限制：

• 全局二级索引与多元索引数据同步为异步，存在数据延迟。也就是说某一条数据写入成功了，但马上通过多元索引去查询可能查不到数据。正常情况下，同步时延在毫秒级。
• 单张数据表最多创建5个全局二级索引。
• 有了索引后，数据不支持多版本。

CRUD映射

MYSQL中的常用查询映射到OTS为4种查询：主键查询、批量查询、范围查询、索引查询。但是相比于MYSQL，OTS的查询在灵活性和易用性方便会差比较多。

与MYSQL相比的不同点：

• 不支持类SQL，需要通过API进行编码查询。这里存在一定的熟悉成本，大概是1－2个人/日的样子。
• 批量查询的返回需要手动过滤：查询参数列表包含10个参数，查询结果中也会包含10个返回对象，但是这10个返回对象中可能只有5条是有数据的，另5个没数据的需要通过返回对象中的状态进行手动过滤。
• 排序返回的结果集中数据是按主键升序排列的。举例说明：数据1{主键：1，排序值：10}，数据2{主键：2，排序值：13}，数据3{主键：3，排序值：12}，通过过滤条件按排序值升序得到数据1，数据2，数据3，查询返回的数据列表中的数据顺序是数据1，数据2，数据3，而不是按排序值排序的数据2，数据3，数据1 。排序过滤只能确保数据的准确性，不能确保数据的返回顺序。需要用户在内存中针对结果集再进行一次排序。
• 无法直接支持分页查询，只能通过范围查询来实现分页查询。范围查询必须指定开始值和结束值，但是分页查询时只知道开始值，这里OTS提供了一个占位符PrimaryKeyValue.INF_MAX　来表示最大值，从而实现分页查询逻辑。

OTS限制：

• 批量查询1次最多查询100条数据。
• 批量写入一次最多写入200条数据。
• 批量写入一次数据大小不能超过4MB。
• 范围查询一次扫描的数据范围不能超过5000条或4MB，超出上限会被截断。也就是说范围查询只能保证在范围查询内的逻辑准确。如果范围查询时匹配的数据超过限制，则需要多次拉取并在内存中进行数据过滤。
• 数据过滤器个数不能超过10个。

给OTS的建议

• 支持类SQL，降低业务迁移的熟悉成本。［OTS团队反馈已有计划推出类SQL能力，点赞］
• 支持自动回表，避免业务手工回表，进一步降低业务的接入难度。［OTS团队反馈续会进行优化，由OTS自动回表，点赞］
• 进一步降低多元索引成本，现在的多元索引是真贵，一般人用不起，但是多元索引也是真香功能强大，如果把这个成本降低到和全局二级索引一样的成本，那OTS的竞争力就很强大了。［OTS团队反馈年内会解决多元索引的成本问题，这样的话OTS的查询能力都能秒杀MYSQL了］

本文为阿里云原创内容，未经允许不得转载。

,