【巨杉数据库SequoiaDB】巨杉Tech | 分布式数据库千亿级超大表优化实践

01

引言

随着用户的增长、业务的发展，大型企业用户的业务系统的数据量越来越大，超大数据表的性能问题成为阻碍业务功能实现的一大障碍。其中，流水表作为最常见的一类超大表，是企业级用户经常碰到的性能瓶颈。

本文就以流水类的超大表，探讨基于SequoiaDB巨杉数据库存储的超大表进行的性能调优。SequoiaDB 巨杉数据库，作为新一代 OLTP 的分布式数据库，被广泛使用于海量数据存储与高并发操作场景中。对于海量数据的存储和高并发操作，分布式数据库相较于传统数据库有着天然的优势，合理利用SequoiaDB巨杉数据库多种特性，轻松解决超大表的性能问题。

02

数据存储规划很重要

对于流水类超大表，前期的数据存储规划尤为重要，合理的数据存储规划能有效利用数据库集群硬件资源，提供更高性能、更高效率的数据服务。

1. 集群规模评估与硬件配置搭配

在数据库集群规划伊始，需要通过调研数据库集群支撑应用规模、系统定位和业务长期发展规划进行摸底，用以评估集群规模以及各服务器的CPU、内存、硬盘、网卡的合理搭配。

精准的评估一个数据库集群规模，是一个宏大且复杂的综合工程，需要有的业务需求评估数据加以支持。通常情况下，由于业务需求变化快、业务增长普遍高于预期，小集群规划可以按照业务调研信息的1.5~2倍进行评估，大集群规划可以按1~1.5倍进行评估。

集群规模需要通过业务规模、数据存储规模、最大流水表3年数据规模，三类信息进行评估。业务规模评估需要从业务访问量、数据库各类操作比例、操作时间分布等进行调研，最终得出各类操作的交易TPS和操作并发度数据。

数据存储规模主要对流水类数据进行评估，从存量数据、3年增量数据预估、数据吞吐规模等信息进行调研，最终得出集群数据存储大小、数据吞吐量，并结合交易TPS估算出磁盘IOPS。基础信息表的数据规模对集群规模评估影响小，作为一个参考信息只需要出整体规模上评估即可，例如，集群需存储1000张基础表，平均每张0.1GB，整体需要100GB，这个量级在上百TB的集群规模里可以忽略不计。

具体操作上，可以先敲定集群存储大小，在根据IOPS和数据吞吐情况平衡使用磁盘还是固态硬盘，每个硬盘的大小，一台服务器挂载几个硬盘等等，根据TPS、操作类型比例和并发度来配置CPU和内存。一般情况下，建议CPU内存比为1:8，单个硬盘在1.5~3T的容量为主，单个盘容量越大越容易出现IO性能瓶颈，网络带宽根据集群大小和集群吞吐量可选择使用千兆网或万兆网络。

2. 流水表怎么建会更好

流水类数据通常包含两个维度特征：业务时间维度和业务主键维度。对于流水表最普遍的就是创建多维分区表（如图1），先在主表集合通过把不同业务日期的数据切分到不同的子表集合中，以此确保单个集合数据量不至于过大；然后，在子表集合通过业务主键将数据打散到集群的各个数据节点中，单集合在单节点上的数据量最好在百万数据量级以内。另外，通过业务日期进行多维度分区，可以非常简单的实现集群横向扩容。

需要注意的是，在设计流水表集合空间时，最好按年或按月创建集合空间，这样当数据超过数据保存周期时可以快速备份、删除并释放存储空间。

3. 再合理的规划也无法一劳永逸

再合理的数据存储规划，在急剧增长的数据和千变万化的业务需求面前，都显得那么无能为力，数据的增长总会超出预期，性能瓶颈总有发生的那天。这个时候，就需要针对具体性能问题进行性能优化。

03

硬件资源性能瓶颈分析

集群硬件资源的使用情况，是分析性能瓶颈的一个重要依据，通过硬件资源使用情况的变化，可以直观确定发生变化时集群应该发生了业务变更或者操作变更，从变化中梳理出一条分析思路。

1. 集群硬件资源监控数据是基础

集群服务器各硬件资源的日常性能监控很重要，通过日常的监控数据，可以了解集群性能的波动情况。

nmon监控软件是是一款很出名的开源的系统性能监控工具，用于监控AIX\linux系统的资源消耗信息，并能把结果输出到文件中，然后通过nmon_analyser工具产生数据文件与图形化结果。如图2是nmon的运行主界面，互联网上有许多关于nmon工具的安装、实时监控、数据采集、分析报表生成等一整套教程，感兴趣的小伙伴可以了解一下。

2. 集群硬件资源使用情况多维度分析

硬件资源使用情况分析，分为实时监控分析和监控报表数据分析，两者需要需要结合使用。

对于常态化的性能问题，先使用实时监控获取具体进程使用的各硬件资源的情况，再结合报表分析将监控的时间长度拉长，找到出现性能变化的起点，看这种性能变化是否是周期性的、随机性的或是渐变的，结合此时点前后的生产变更情况以及相关数据操作动作来定位问题。

对于已发生暂时没有重现的性能问题，先通过监控报表分析手段，分析性能问题发生时点前后的硬件资源变化，再将监控的时间长度拉长，尝试找出资源变化的规律，然后通过应用日志、数据库日志和生产变更情况，找出引发性能问题的操作。最后，尝试重新执行相关操作，并进行实时性能监控，验证相关问题猜想。

从硬件资源监控中找出性能变化的内在逻辑是问题分析的关键所在，掌握硬件资源的变化规律就初步掌握了集群的性能情况，也是进行性能调优的第一步。

04

数据库集群业务情况与操作分析

掌握了解硬件资源性能情况是性能优化的第一步，还需要结合集群业务情况与各类操作比例与时间分布，才能进一步精确的定位性能问题。

1. 业务情况摸底

业务情况摸底一般通过业务访问监控接口持续获取业务访问情况，并结合业务逻辑分析需要执行的数据库操作，获得业务TPS、并发度、数据操作类型分布、数据吞吐量等信息，以此评估数据库集群压力。

2. 数据分布情况分析

数据分布不均导致的性能问题是一个非常常见的问题，流水类超大表出现数据分布不均主要是由于业务主键字段随机性弱，无法很好的进行哈希打散，可使用其他随机性较强的字段代替，或者结合数据特点使用范围分区也是一个可以尝试的方法。

如何确定数据是否均匀分布？首先，通过查看主表集合的编目信息，检查业务日期切分是否均衡；然后，查看每个子表集合的编目信息，检查数据是否根据业务主键打散到所在数据域的各个节点；最后，抽查2~3张子表集合，检查子表集合在每个节点的数据量是否相当均衡。

3. 访问计划分析

通过query.explain({ Detail : true, Run : true })可获取查询的详细访问计划。通过分析数据操作语句的访问计划，可以掌握该数据操作的涉及到哪些子表集合，每个子表集合的每个节点的访问计划情况，包括访问计划的扫描方式、索引使用情况，数据情况，资源使用情况等信息。

4. 查询监控与全表扫描检测

通过抓取数据库集群会话快照db.snapshot(SDB_SNAP_SESSIONS)，可以捕获每个查询的数据操作详细信息，包括会话状态、线程信息、索引数据信息、数据操作记录数、操作耗时、资源使用情况等等信息。

可通过不断抓取会话快照信息，监控会话快照信息中的"LastOpInfo"字段是否包含"tbscan"字样，来检测数据操作中是否存在全表扫描的情况。

05

性能优化指引

流水类超大表的性能优化，一般遵循从操作到软件再到硬件，由简到繁的一个优化思路，优化前需要充分了解软硬件情况，例如数据操作情况、数据库配置、系统性能情况等等。

1. 适量创建高效索引

索引是一种提高数据访问效率的特殊对象。恰当的使用索引可提高数据检索效率，但索引使用不当反而会降低数据检索速度，严重的还会造成数据库整体服务性能下降。

利用索引对流水类超大表进行性能调优，是最为经济、简单、高效的一种方式。那么，如何恰到好处的创建索引，即能提高数据操作效率，又不会对数据库服务性能造成影响？

首先，需要了解索引的可供调优参考的部分特性，以及相关性能开销成本。

部分相关特性：

使用二分查找，可快速定位数据，平均复杂度是O(logN)。

索引数据信息大小远小于表数据大小，索引数据可长时间缓存在内存。

使用B树结构，三层的B树可以表示上百万的数据，通过索引检索数据可减少磁盘I/O次数，数据字段越小索引效率越好。

I/O的次数取决于B树的高度H，假设当前数据表的数据为N，每个磁盘块的数据项的数量是M，则有：H=log(M+1)N，当数据量N一定的情况下，M越大，H越小；而M=磁盘块大小/数据项大小，磁盘块大小也就是一个数据页的大小，是固定的，如果数据项占的空间越小，数据项的数量越多，树的高度也就越低。这也就是为什么每个数据项，即索引字段要尽量的小，比如int占4个字节，要比bigint的8个字节小一半。这也是为什么B树要求把真实数据放在叶子节点内而不是内层节点内，一旦放到内层节点内，磁盘块的数据项会大幅度的下降，导致树层级的增高。当数据项为1时，B树会退化成线性表。

索引具有最左匹配特性，创建复合索引要根据数据重复率和查询使用的字段情况进行创建。

B树的数据项是复合性数据结构，按照从左到右的顺序来建立搜索树的，例如：当（小张，22，女）这样的数据来检索的时候，B树会优先比较name来确定下一步的搜索方向，如果name相同再依次比较age和gender，最后得到检索的数据。但是，当（22，女）这样没有name的数据来的时候，B树就不知道下一步该查哪个节点，因为建立搜索树的时候，name就是第一个比较因子，必须根据name来搜索才知道下一步去哪里查询。比如，当（小张，男）这样的数据来检索时，B树就可以根据name来指定搜索方向，但下一字段age缺失，所以只能把名字是"小张"的所有数据都找到，然后再匹配性别是"男"的数据了。

索引数据是有序的，支持顺、逆排序，合理利用可优化查询排序和分组效率。

数据重复率越低索引使用效率越高。

相关性能开销：

对数据操作都需要额外对索引进行维护，索引越多维护性能开销越大。

创建索引需要排序，需要额外存储空间，会获取表锁，创建时会消耗大量内存、CPU。

一次数据查询操作，至少产生2次IO操作，一次查询索引数据，一次访问表数据。

一次数据插入操作，至少产生1+n（n为索引个数）次IO操作，当索引当叶结点过满时会触发结点递归分裂时，IO操作会剧增。

一次数据删除操作，至少产生3+n（n为索引个数）次IO操作，当索引当叶结点过空时会触发结点递归合并时，IO操作会剧增。

一次数据更新操作，如果更新字段不是索引字段，则产生3次IO操作，如果更新字段为索引字段，则产生3+n（n为索引个数）次IO操作，且索引结点的分裂与合并均有可能发生（变长数据类型字段）。

掌握了索引相关特性和性能开销，结合流水类超大表的特点，通过评估确定数据插入、更新和查询操作的比例，调研更新字段与查询字段是否有重合等信息，以此来确定是否需要创建索引，创建哪些索引。

一般来说，流水类超大表都需要创建流水主键索引，以确保流水数据唯一性。其他的索引创建需要可根据更新、查询条件、数据规模等信息进行评估，通常创建2~3个索引效率性能比较为理想。如果数据查询操作为主，可酌情增加索引数量。对于多个字段组成的查询条件，可根据条件字段的重复率情况创建复合索引；对于有数据排序、数据分组的字段，可按排序分组字段创建复合索引。

2. 优化查询语句

对于流水类超大表的查询优化，有两个基本原则：一是通过索引调优，强制数据查询走指定索引，二是通过限制查询的业务日期范围，减少查询检索的数据规模。

索引调优可以结合本文 "适量创建高效索引"章节所述合理创建索引，并通过查看执行计划确保查询走索引。

合理的利用巨杉数据库主子表的特点，在查询时确定查询数据的业务日期维度，可以极大的减少检索数据规模，减少节省数据库集群资源，举个例子：应用需要通过流水号（主键）查询该流水号对应的流水数据，该流水号记有业务日期信息。通常情况下，简单的使用流水号就可以准确快速的查询到该条流水，但对于一个流水类超大表来说，可能存放着十年几十年的流水数据，数据可能存储在上百张子表中，如果单单使用流水号查询，那么数据库就需要对每个子表进行一次索引检索，最后只会有一张表检索到数据，但如果把流水号中的业务日期截取出来作为一个查询条件，那么数据库就可以通过主表的业务日期分区信息定位到该流水数据所在的子表，数据库仅会对该子表进行索引检索。

另外，对于对大范围业务日期查询，且需要分页处理的，可每次查询一个业务日期分区，分多次完成查询。对于查询数据结果集很大的查询，通常使用分页查询，单页数据量在数百以内为佳。

总的来说，查询语句的调优没有一套固定标准的操作方法，它是一个循序渐进的过程，只有通过不断的测试、优化、再测试、再优化，在不断迭代中逐步提高查询效率。

3. 调整数据切分粒度

流水类超大表什么时候需要调整数据切分粒度，可能是本节内容的最大挑战，需要综合考虑集群硬件资源使用情况、查询优化情况，子表单节点数据大小、索引大小等等一系列问题。

从硬件资源使用角度考虑，硬盘使用率在70%以下，超过70%可以考虑直接进行集群扩容，在集群CPU、内存基本保持在60%以下，只有IOPS居高不下，且IO读紧张IO写正常，查询业务TPS远低于IOPS，此现象说明1次查询会产生非常多的IO读；从查询优化情况看，已无全表扫描的查询，查询语句已做了足够的优化，但查询依然无法满足需求；以单子表单个节点的数据规模进行评估，数据规模需要控制在百万级别，单条数据记录的字段数与数据长度对数据检索也有影响，特别是数据更新操作，数据字段越多、数据长度越长更新效率越低；评估查询语句中的业务日期范围，查询越精确，切分粒度的调整弹性就越大；另外，根据创建的索引的字段类型以及实际索引大小，评估索引是否可以完全缓存至内存，尽可能将数据切分粒度调整至索引数据可完全缓存至内存。

实际上，调整流水类超大表的数据切分粒度，就是提高数据并发度，减少单子表在单节点的索引数据规模，使得整个索引数据可以常驻内存，最大程度减少IO开销，故而，调整数据切分粒度会增加内存、CPU资源的消耗。

如何调整数据切分粒度是需要探讨的另一个问题，对于按本文介绍的主子表建表规则创建流水类超大表，很简单的就可以完成调整动作，只需要将主表的每个业务日期区间，拆分成多个切分区间，即一个子表数据拆分成多个子表。关键的问题是，拆成多少个子表合理，一般建议一个子表拆分成2~3个子表较为合理，不过，最可靠的方式是--模拟生产测试。

4. 升级短板硬件

服务器停机升级短板硬件，对于天然具备容灾高可用特性的巨杉数据库来说，是一件不能再简单的事情，只需将需要升级的服务器的数据库主节点切换到其他服务器，就可以停掉数据库服务然后进行停机升级；如果是替换硬盘，在重启完成新硬盘挂载并创建相应数据目录后，重启数据库服务后，数据库会自动同步数据。

通过硬件资源的性能瓶颈分析，可以很清晰的看出硬件资源瓶颈，可根据业务需要酌情升级短板硬件，如添加内存条，将磁盘替换成固态硬盘，甚至可直接将整台服务器置换掉。

5. 集群扩容

当数据库集群存储空间不足，或者上述优化手段都不能很好的解决性能瓶颈问题时，可以着手进行集群扩容。

在集群扩容时，需要根据现有集群情况加以调整优化，例如，原有数据域有3台服务器，由于增量数据剧增，后续规划的数据域就需要6台或者更多服务器。又例如，新服务器参照旧服务器的硬件资源使用情况，需要调整硬件配置，降低空闲的配置要求，提升短板硬件资源的配置等等。由于有了一个现成的参考对象，集群扩容的规模评估和硬件配置评估就可以简单很多。

对于流水类超大表的集群扩容操作是非常简单的，只需在安装好巨杉数据库软件的服务器按规范新增数据节点，将新增节点添加进原有集群并作为一个新的数据域，然后将后续业务日期的子表集合创建在新的数据域，再将子表集合按规范挂载到主表集合上，到此为止已经完成了集群扩容的所有步骤。如下图所示，当数据域1各类硬件资源不够使用，可新增一个数据域2存储2030年以后的流水数据。

06

总结

流水类超大表的性能优化，是一个持续迭代的过程，需要建立在对数据库集群的业务层、数据层、硬件资源层有充分的了解的基础上，依次对硬件资源分析、集群操作分析与检测、业务分析，完成对性能问题的定位。

同时，对于巨杉数据库的用户，在处理超大表优化问题时，可以合理利用 SequoiaDB 巨杉数据库多维分区特性和简易的横向扩展机制，轻松解决超大表的性能问题，面对超大表的性能优化再也不用愁~