复杂场景下业务数据库优化实践

---

前言

提示：本文主要介绍最近做的数据库优化，并整理成文档记录一下

由于疫情影响，公司IT费用成本压力变大，本着降本增效的目的，着手对各种IT资源进行优化。数据库优化的触发点是业务高峰期cpu飙升至80%，这已经是数据库升配几次的结果，再升级配置成本压力大，现阶段已成为箭在弦上的事情。
本着对技术的自信，个人毅然决然承担起这个事情，但是殊不知掉进了一个很深的漩涡中，遇到很多想象不到的问题。下面个人就总结一下。

---

一、业务优先

业务数据库的优化，需要格外注意对业务的影响。尤其是已经运行成熟的业务，除非对业务的各方面(包括历史遗留因素)了如指掌，不能因为技术优化进行调整。优化时需明确字段名、字段类型、默认值、索引设计等细节，了解其业务作用。

二、数据库选型

目前数据库类型已经很多了，传统关系数据库、nosql数据库、newsql数据库，各种数据库应接不暇。对应的数据库优化技术方案选择也很多，大致可分为四种(包含优缺点)：

1. 对数据库表进行定期维护，如回收空间、数据压缩compress、冷热数据分层等
   优点：

   通过数据库自身的设计机制，回收空余空间，可以使得索引更加紧凑，提高查询速度
   通过数据压缩compress可以将存储空间需求按照指数级下降
   通过冷热数据分层可以极大降低存储成本
   对业务使用无感知
   

   缺点：

   依赖具体数据库的功能实现
   

2. 对传统关系数据库进行分库分表设计的
   优点：

    优化简单直接，提升读写性能(减少单表数据量、提高读写并发)
    兼容现有业务使用，改造量小
   

   缺点：

    不支持非分区条件等复杂查询
    深度分页问题
   

3. 通过改变读写模式，从sql转nosql
   优点：

    提升读写性能一到两个数据量级
   

   缺点：

    基本不支持join
    部分nosql数据库不支持复杂聚合(hbase、dynamodb)
    大概率避免不了业务改造，改造成本大
    不一定省服务器成本
   

4. 直接使用高性能分布式newsql数据库
   优点：

    提升读写性能(采用新的数据库架构)
    兼容现有业务使用，改造量小
   

   缺点：

    架构通常比较复杂，运维成本高
    不能100%兼容已有业务数据库，业务使用学习成本高
    社区活跃度和技术资料普遍偏少，需要长时间的踩坑经验和技术积累
    已有数据分析OLAP功能迁移
   

在考虑数据库选型时，需要尽量避免缺点，多用优点，综合考虑。

优化实践

这部分内容将介绍个人做的数据库优化实践。

1. 对数据库表进行定期维护

在mysql中可以通过analyze local table 分析表可回收空间，postgresql中可以通过vacuum回收空间。
针对时序数据场景，大部分只有写入和查询，不涉及修改、删除操作的可以不做空间回收。同时，很适合用冷热数据分层的方式，将冷数据存储在廉价的介质中(如timescaledb云服务版提供tier storage功能)，同时保证冷数据可以在线查询，对业务使用无感知。
数据的compress上，往往可以节省90%的存储空间，但是需根据使用场景判读compress后对查询统计速度的影响，需进行压测(timescaledb 压缩chunk后可以降低存储空间使用，并提高查询速度)。

2. 采用新的数据库实例

为了保证现有业务不受影响，采用新的数据库实例，确保优化不会对现有数据库造成性能上的影响。
同时，现有对新数据库的读写，统一使用单独的事务管理机制，并基于spring切面做读写异常捕获，确保新数据库读写异常不会造成业务流程阻断和原有数据库数据事务回滚。

3. 部分业务接入dynamodb

针对明显的kv型读写，使用aws的dynamodb做业务处理，并基于dax做查询缓存，无需使用业务自己的redis缓存。

4. 分库分表优化

针对数据量增长比较快，读写次数较多，同时需要复杂查询的表进行分库分表优化。调研几款分库分表中间件，最终采用shardingsphere做为分库分表中间件，对比如下：

在接入shardingsphere时，踩了几个坑：

i. 按月分库不够灵活
   预想一个月一个数据库，但是配置上是一个数据库对应一个实例。如果配置一年的数据库，则需配置12个实例，每个实例都设置了单独的连接池，配置繁杂。因此需改动database rule逻辑，使得可以只用一个连接池做多个按月滚动的分库的读写。
ii. 连接池配置和max.connections.size.per.query设置
无法根据当前连接池使用状态动态设置max.connections.size.per.query。在连接池空闲时需要将max.connections.size.per.query设置得大些，这样有更大的并发，而在连接池资源紧张时可以设置小一些，这样省减少单个sql对连接的使用。
iii. 分库分表导致表数增多
无需开启cachePrepStmts和useServerPrepStmts，否则分表的查询sql很容易就产生几十万上百万条statement缓存，导致业务服务器内存占用变大，并会超过数据库缓存的statement限制。
iv. 适当修改innodb_flush_log_at_trx_commit参数
在shardingsphere执行写操作时，会频繁修改autocommit，并将多表操作放到一个事务中，最后commit来提交，以保障操作数据的一致。这样会产生大量的commit语句。而mysql在commit时默认会刷盘，这样频繁的刷盘会严重拖慢数据库的性能，因此建议修改innodb_flush_log_at_trx_commit为2，将刷盘变成异步，从而提高commit执行速度(数据库服务器断电会导致文件系统缓存失效，出现未刷入磁盘的数据丢失，不适用于金融行业数据库)。
v. 适当修改innodb_flush_method参数
innodb_flush_method这个参数控制着innodb数据文件及redo log的打开、刷写模式，对于这个参数，官方文档上是这样描述的：有三个值：fsync(默认)，O_DSYNC，O_DIRECT。默认是fsync，事务提交将数据文件和redo log先写入文件缓存，再定时(1s)调用fsync()去刷数据文件与redo log的buffer，速度最快；为O_DIRECT时，innodb使用O_DIRECT打开数据文件，使用fsync()刷写数据文件跟redo log，速度最慢，但是数据可靠性最高；为O_DSYNC时，innodb会使用O_SYNC方式打开和刷写redo log, 使用fsync()刷写数据文件，速度和数据可靠性处于折中。

同时做分库分表得慎重，如果无法满足业务需要，可以考虑直接使用newsqldb不分表的方式。

5. 数据清理定时任务优化

原有定时任务会在执行时通过计算单个用户vip套餐数据存储时长和数据时间timstamp进行过期数据清理，需要做用户表扫描，一次也只能删除一个用户的过期数据。
现在优化后在数据写入时，事先计算数据过期时间ttl_timestamp，然后定时任务直接删除ttl_timestamp小于当前时间的数据就行。同时由于分库分表设计，可以采用排队依次删除的方式，一次只操作一张表，减少数据清理任务对业务的影响。

6. 数据库优化陪跑、验证机制

为了验证数据库优化的功能，采用开关的方式，在数据库读写切面中控制功能的打开和关闭，生效的用户比例，并做数据库操作返回结果的比对，如果出现非预期的数据不一致则以日志的方式输出miss match原因。
同时基于miss match日志，编写了原因自动定位的脚本，代替人工手动排查，提高测试同学的功能验证效率。通过不断的bug定位和修复，最终的miss match日志会趋于可接受的数量，此时就可以把老数据库的读写关闭，全部采用新数据库了。

总结

数据库优化是门技术活，需要多花心思了解业务，然后根据业务、成本、技术积累等方面做最合适的数据库选型。如果没有全面的数据库优化方案，还是选择升级配置吧，没有产生业务影响，也可以算是一种收益。这样产品和运营同学就不会拿着砖头过来了。