数据库调优之大表治理
1、前言
继上回，小明自从面试的时候被问到如何进行索引调优后，小明通过学习数据库调优-索引篇一文掌握了索引和索引优化相关知识。做好准备后他又去一家新的公司面试......
面试官：小明，你简历上写了你会数据库调优，你都是怎么调优的？
小明：加索引，索引优化......
面试官：除此之外还有么？索引优化有什么瓶颈吗？如果索引优化已经无法提高性能要怎么办呢？
在上文中我们提到过为什么SQL会慢的原因之一有数据过多，当数据库表的数据量达到一定量级时，由于查询维度较多，即使添加了索引，做了索引优化，数据库的性能仍然不佳。此时就要考虑其他方式来减少数据库的负担，缩短查询时间。本篇文章给大家介绍数据库大表治理的解决方案
2、数据治理方式
官方说明为500万-800万会影响数据库性能，而我们将超过2000万行或10GB的表定义为大表。当某张表达到大表标准时，就需要对该表进行治理，大表治理的方式一般分为数据归档、数据切分两种。
2.1 数据归档
数据归档是一种简单、高效的数据治理方案。通过将历史无用的数据归档到独立的归档表中，可以显著减小主表的数据量，这些归档的数据可以直接删除，也可以在某张表中存档一段时间。在实际项目中，可以手动归档一批数据，也可以通过定时任务脚本定期将一定时间之前的无用数据进行归档。例如，对用户消息表，我们可以将一年前的数据进行归档，从而大幅降低主表的数据量。
2.2数据切分
数据切分的基本思想就要把一个数据库切分成多个部分放到不同的数据库或数据表上，从而缓解单一数据库表的性能问题。一般而言，针对海量数据的数据库，如果是因为表多导致数据多，容易导致数据库连接数过大，带宽有压力时，这时候适合使用垂直切分，即把关系紧密（比如同一模块）的表切分出来放在一个库上。如果表并不多，但每张表的数据非常多，这时候适合水平切分，即把表的数据按某种规则切分到多个数据表上。当然，实际项目中更多是这两种混合一起使用，这时候需要根据实际情况设计对应的切分方案。数据切分根据其切分类型，可以分为两种方式：垂直（纵向）切分和水平（横向）切分。
2.2.1 水平切分
当数据库数据量行数巨大，存在单库读写、存储性能瓶颈，这时候就需要进行水平切分了，水平切分分为库内分表和分库分表，是根据表内数据内在的逻辑关系，将同一个表按不同的条件分散到多个数据库或多个表中，每个表中只包含一部分数据，从而使得单个表的数据量变小，达到分布式的效果。
2.2.2 垂直切分
垂直切分又可细分为垂直分库、垂直分表。
垂直分库：就是根据业务耦合性，将业务关联度低的不同表存储在不同的数据库。与"微服务"相似，每个微服务使用单独的一个数据库。 垂直分表：是基于数据库中的"列"进行，在字段很多的情况下（一个表超过20个字段），通过"大表拆小表"，将比较常用的字段放到一张表中，比较不常用的字段放到扩展表中，这样更便于开发与维护，减少了磁盘IO，也能使MYSQL避免跨页问题(MySQL底层是通过数据页存储的，一条记录占用空间过大会导致跨页，造成额外的性能开销。这里就不做过多赘述)。从而提升了数据库性能。
3、数据切分项目实践
3.1 背景介绍
为了应对站内信消息数据库数据量超过21亿、单表7000万的挑战，我们决定进行数据切分项目。在这一背景下，我们详细规划了项目的实践步骤。
3.2 切分方案设计
3.2.1 选择水平切分
考虑到站内信消息表的庞大数据量和未来的增量，我们决定采用水平切分方式，将站内信库表由3个库每个库10张表拆分为4个库，每个库256张表。这样的设计在单表2000万为最大容量的基础上，能够容纳200亿以上的数据，即使不进行任何归档操作也能支撑5年。
3.2.2 分库分表规则
我们制定了详细的分库分表规则，以确保数据切分后的合理管理和查询效率。分库规则采用了userId % 1024取余，再对userId / 256取整的方式，这样设计可以避免数据不均匀。分表规则是userId % 256。
3.3 数据迁移策略
由于数据量巨大，同时老库没有全局唯一键，数据同步只能通过代码脚本来操作。我们采用了分段查询的方式，通过ID进行分段查询，以提高数据迁移效率。
3.4数据双写
数据双写就是将一条数据同时写入两个数据库或表中，以保证两个数据库的增量数据一致性。双写一般需要考虑以下两种情况：
3.4.1 切读之前
在切分之前，由于老库已经采用了分库分表，我们需要保证数据的一致性。为此，在新表中增加了一个字段unionId作为数据唯一键，其值由userId、"_"、id拼接组成，例如：老表的一条数据userId=123456，id=123456；unionId=“123456_123456”。在插入和更新数据时，我们需要进行一系列操作以保证新老库数据的一致性。
插入数据： 数据先写老库，在老库成功写入数据后，提取老库的 userId和id组成全局唯一键unionId并赋值到新库数据对象后再执行写新库逻辑。更新数据： 更新逻辑都是通过userId和id来更新的，在新库中根据 unionId截取字段得到对应老库的userId和id。然后再对这条数据进行更新操作，更新的数据内容取老库全字段数据，这样可以保证数据一致性。
3.4.2 切读之后
在切分之后，更新数据的逻辑需要进一步考虑。需要根据新库中的unionId，截取字段拿到对应老库的userId与id，再根据这些信息去更新老库的数据。这样的双写机制确保了数据在切分前后的一致性。
插入数据： 数据先写老库，在老库成功写入数据后，提取老库的 userId和id组成全局唯一键unionId并赋值到新库数据对象后再执行写新库逻辑。更新数据： 更新逻辑是通过userId和id来更新的，此时新库更新完数据后，因为新库的ID不是老库对应的ID，需要根据新库中的unionId，截取字段拿到对应老库的userId与id，再根据userId与id去更新老库的数据。
3.5业务切流量
为了确保业务的平稳过渡，我们通过配置开关的方式切换业务读新库数据，并监控业务是否正常运行。同时，我们及时通知下游关联方切换到新数据库，主要包括binlog或大数据、实时数据等数仓离线表的相关依赖。
3.6停写老库
在停止对老库数据写入之前，我们进行了充分的检查，确保老库业务没有流量访问（包含binlog等离线表数据同步流量）。通过配置开关，我们控制停止对老库数据的写入，并监控业务是否正常运行。
3.7回收资源&&清理代码
一旦业务监控正常，老库任何流量访问后，我们可以下线老数据库。这包括删除老数据源的配置、写入数据的代码以及相关开关的代码。这一步骤的实施需要慎重，确保业务的平稳运行。
3.8项目收益
通过水平切分站内信消息表，我们取得了明显的项目收益：
数据量下降显著： 新站内信表数据从老表的6000万+降至230万+，单表数据减少95%。
慢SQL问题解决： 慢SQL从每月80+降为零，系统性能得到显著提升。
接口查询性能大幅提升： 切换到查询新表后，95线耗时提升35%，99线耗时提升50%。
四、数据切分优缺点
4.1 优点：
降低耦合性： 垂直切分能够根据业务模块将不同表存储在不同的数据库，从而降低系统各模块的耦合性。每个业务模块使用独立数据库，有利于模块间的独立开发、维护和升级。
易于维护： 垂直切分使得对不同业务表的管理更为精细，可以根据业务的重要性和特点，对不同数据库进行不同程度的监控和维护。这有助于及时发现和解决问题，提高系统的稳定性和可维护性。
提升性能： 水平切分能够有效提升高并发场景下的性能。通过将数据分散到多个数据库或表中，减小单一数据库表的数据量，有助于提升IO、数据库连接数和单机硬件资源的瓶颈，提高系统的并发处理能力。
灵活扩展： 垂直切分可以更灵活地扩展系统。由于不同的业务表存储在不同的数据库中，可以根据业务增长的需要，对不同数据库进行独立扩展，而不影响整体系统的运行。
4.2 缺点：
无法跨库JOIN： 水平切分会导致部分表无法直接进行JOIN操作，增加了在业务层通过接口聚合的开发复杂度。解决方式包括通过缓存、消息队列等手段进行异步聚合。
分布式事务处理复杂： 在涉及多个数据库的分布式事务场景中，数据一致性的维护变得更为复杂。此时，需要采用分布式事务管理工具或通过应用层设计保障事务的一致性。
数据多次扩展难度大： 随着业务的发展，对数据库的再次扩展可能会带来不小的难度。特别是在水平切分的情况下，需要谨慎规划数据的再次扩展，以避免系统过度复杂。
查询依赖分片键： 水平切分的查询性能高度依赖分片键。如果查询没有包含分片键，可能会导致查询速度下降。因此，在进行数据切分时，需要考虑查询模式，尽量使查询与分片键匹配。
4.3 适用场景：
在选择是否进行数据切分时，需要根据具体业务场景和需求综合考虑。适用场景包括但不限于：
数据量庞大，存在单库读写、存储性能瓶颈的情况。 系统需要灵活扩展，不同业务表之间关联性不强。 高并发场景下，需要提升系统的并发处理能力。 对系统稳定性和可维护性有较高要求。
总体而言，数据切分是一项综合性的策略，需要根据实际情况权衡其优缺点，选择最适合的方案。
5、总结
综合考虑数据库优化大表治理的方案，我们得出以下结论：
谨慎使用数据切分： 数据切分是一项强有力的工具，但并非所有表都需要进行切分。在设计初期，应提前评估业务未来一到两年的增长情况，考虑是否能通过其他手段提升数据库性能，如硬件升级、网络升级、读写分离、索引优化等。仅在数据量达到单表瓶颈时，再考虑分库分表，以避免"过度设计"和"过早优化"。
合理选择切分方式： 切分方式应根据实际情况选择，水平切分适用于数据量庞大的场景，能一定程度提升IO、数据库连接数和单机硬件资源瓶颈；垂直切分则能降低业务系统耦合性，对不同业务的数据进行分级管理、维护、监控、扩展等。
详细考虑优缺点： 数据切分具有一定的优点，如提升系统稳定性和负载能力，但也伴随着一些缺点，例如部分表无法join、分布式事务处理复杂、数据扩展难度大等。在实施前需充分考虑这些因素，权衡利弊。
精心规划项目实践： 在实施数据切分项目时，需要细致规划切分方案、数据迁移、数据双写、业务切流量、停写老库等步骤。
实现明显收益： 通过对站内信消息数据表的水平切分实践，我们取得了明显的项目收益，包括数据量大幅下降、慢SQL归零、接口查询性能提升等。这充分表明，在合适的场景下，数据切分是一项有效的优化手段。
综上所述，数据库大表治理是一项综合性工程，需要慎重谨慎地选择和实施各项方案，以最大程度提升数据库性能，确保系统的稳定性和可维护性。
作者介绍
Chen·Small-K，后端研发专家