数据库清理、发布、变更指南_清理数据库的操作的作用是什么

更多内容，前往 IT-BLOG

数据清理

随着业务量的发展和需求迭代，系统内会累计越来越多过期无用的历史数据，这些数据主要包括两部分，第一部分是由于系统技术改造和需求迭代中动态规则所产生的配置项，第二部分是业务处理流程中产生的持久化过程记录数据。 随着时间的增加，这些过期的过程数据和配置不但会占用过多的磁盘空间，拖慢数据库查询速度，还会增加系统逻辑的复杂度，所以我们需要定期对这些数据做清理，但是如果清理过程中出现问题，则非常有可能影响系统的正常运行，甚至丢失订单数据，造成无法挽回的损失。

一、数据库记录清理

前期准备

【1】对于生产表：联系DBA拉取符合数据清理条件的表。主要包括三种：
  ■ 容量超过限制，占用过多空间，一般认为超过 50G 都是大表。
  ■ 记录条数过多，影响性能，例如条数 > 2亿。
  ■ 存在常规查询语句由于数据量增大而变慢的情况。
【2】新建表时：评估表的预期数据总量。
在每次新建表时，都应该根据每日新增数据和业务需要的记录保存期限来评估表的预期数据总量。如果预期的记录数过大，可以考虑一下方案：
  ■ 如果只在某个范围内有效的数据，可以考虑实现数据自动清理的计划任务（JOB），定时批量清理过期数据。
  ■ 如果数据长期有效，则需要考虑对表或者数据库做拆分，做分表分库。
【3】 逐条评估过期条件，此步骤尤为重要，评估不到位可能导致有效的生产数据被清理引发生产问题。
  ■ 基于 DBTrace 拉取最近该表的所有访问方

dbtrace 是基于Java语言开发的数据库访问性能诊断工具，设计目标是使应用程序零代码修改、无缝集成到应用中，轻松完成Java应用中JDBC访问的性能诊断、耗时跟踪、调用栈跟踪及日志记录。

  ■ 与使用方和产品一起仔细确认数据应用范围，条件主要基于两个维度：状态和日期 例如：机票的有效期为 1 年，则超过一年的票号变化数据则为无效数据 或：机票出票的队列，在出票成功后则为冗余的无效数据
  ■ 评估过期条件是最好还需要参考数据库的备份周期，避免新鲜有效且未备份的数据被误删除引发严重的生产问题。
【4】评估删除策略：数据表记录清理通常不是一次就能完成的事情，表每日还在新增大量数据，历史积压的数据过多导致查询性能变慢，删除数据会影响生产对该表的读写性能，都是我们需要考虑的问题。所以我们需要基于积压量和单日新增量评估出一个周期性执行的清理任务。 计算单日清理量主要的策略如下：
  ■ 如历史积压量和新增量都比较低，则计算清理量时可以先设置一个历史数据的目标期限，每日计划清理的量为：单日清理量 = 每日平均新增量 +（历史数据量 / 期限天数）
  ■ 如历史积压量非常高，但新增量处于正常或较低水平，则应该先联系 DBA 和 OPS 做一次初始化清理，之后再按照平均的每日新增量留少许余量作为单日清理量。
  ■ 如果单日新增量非常高，到了无法平缓清理的地步，则应该评估数据最大可积压记录数的范围，每到达最大积压数时联系 OPS 手动大规模清理。并后续调研和改造降低该表的写入量。
由于数据库的 delete 操作是有锁的，如果单次删除的数据量非常大的话，会 block 住正常读写操作，或是造成事务堆积，影响到生产业务，所以计算出单日预计删除量后，还需要根据表的特性进行分割，将清理量均匀的，有间隔的分摊到单次清理周期中，以达到平缓的效果。
  ■ 每日计划建议划分为轮和次，每间隔 X 分钟执行一轮，每轮执行 Y 次批量 delete 操作，每次 delete 操作间隔 Z 毫秒。通过合理调节 X，Y， Z 的值，使其对生产的性能影响降到最低。
  ■ 如果表的单条记录较大，则需要适量减少单条 delete 语句删除的记录数
  ■ 如果表的预计删除数量较多，则可增加每轮多条 delete 语句删除的次数

执行事项

  ■ delete 的语句在部署到生产前，必须经过 DBA 的 review，还需要在测试环境造数据进行严格的条件验证，尤其是条件中需要 join 其他表的。
  ■ 部署在生产后需要密切监控 DB 和相关表的性能，如果发现对生产性能指标造成了影响，需要及时终止清理计划。
  ■ 清理的时间点应该避开生产高峰期，凌晨 0 ～ 6 点为最佳区间。
  ■ 最好在 hickwall 中部署数据清理相关埋点的图表，例如查询语句耗时，delete 语句耗时，无可清理数据，总删除量等等信息，并根据这些数据动态的调整清理的配置，使得整个清理过程对生产影响尽可能低。

灾备方案

对于一些丢失代价非常高的核心数据，即使经过充分的测试和评估，也不能完全杜绝出错的可能性，所以这时候需要做好灾备的方案。
  ■ 对于提事件由 DBA 协助清理的数据，清理时需要勾选备份数据并选定保留天数。
  ■ 对于 JOB 程序自动清理的方式，可以使用 MongoDB 等灵活部署的数据库作为所有待删除数据的回收站。推荐 Mongo 是因为他新建表和定义表结构非常方便，而且可以直接部署在 docker 上，成本较低。
【1】程序中需要先 select 出符合删除范围的整条记录，而不仅仅是主键。
【2】维持表字段不变，将上述记录列表插入 MongoDB 的同名 Collection 中，如果 Collection 不存在，Mongo 会自动创建。
【3】应用中可以直接按照表的维度做保留时间的配置项，插入时根据配置直接设置 TTL，过期自动删除
【4】保证插入成功后，再将原表对应的记录应用平滑策略删除。
【5】一旦由于条件配置错误等原因发生误删数据，可以直接从回收站中还原原始数据。

二、执行了delete，但表文件大小没减小

项目中使用Mysql作为数据库，对于表来说，一般为表结构和表数据。表结构占用空间都是比较小的，一般都是表数据占用的空间。

当我们使用delete删除数据时，确实删除了表中的数据记录，但查看表文件大小却没什么变化。

Mysql数据结构

凡是使用过mysql，对B+树肯定是有所耳闻的，MySQL InnoDB中采用了B+树作为存储数据的结构，也就是常说的索引组织表，并且数据时按照页来存储的。因此在删除数据时，会有两种情况：
【1】删除数据页中的某些记录
【2】删除整个数据页的内容

为什么delete表数据，磁盘空间却还是被占用
InnoDB直接将R2这条记录标记为删除，称为可复用的位置。如果之后要插入ID在 300到700间的记录时，就会复用该位置。由此可见，磁盘文件的大小并不会减少。

通用删除整页数据也将记录标记删除，数据就复用用该位置，与删除默写记录不同的是，删除整页记录，当后来插入的数据不在原来的范围时，都可以复用位置，而如果只是删除默写记录，是需要插入数据符合删除记录位置的时候才能复用。

因此，无论是数据行的删除还是数据页的删除，都是将其标记为删除的状态，用于复用，所以文件并不会减小。

那怎么才能让表大小变小

DELETE只是将数据标识位删除，并没有整理数据文件，当插入新数据后，会再次使用这些被置为删除标识的记录空间，可以使用OPTIMIZE TABLE来回收未使用的空间，并整理数据文件的碎片。

OPTIMIZE TABLE 表名;
1

注意：OPTIMIZE TABLE只对MyISAM, BDB和InnoDB表起作用。

另外，也可以执行通过ALTER TABLE重建表

ALTER TABLE 表名 ENGINE=INNODB
1

有人会问OPTIMIZE TABLE和ALTER TABLE有什么区别？
alter table t engine = InnoDB（也就是recreate），而optimize table t 等于recreate+analyze

表文件大小未更改和mysql设计有关

比如想要删除R2这条记录：

ALGORITHM选项
INPLACE： 替换：直接在原表上面执行DDL的操作。
COPY： 复制：使用一种临时表的方式，克隆出一个临时表，在临时表上执行DDL，然后再把数据导入到临时表中，在重命名等。这期间需要多出一倍的磁盘空间来支撑这样的 操作。执行期间，表不允许DML的操作。
DEFAULT： 默认方式，有MySQL自己选择，优先使用INPLACE的方式。

LOCK选项
SHARE： 共享锁，执行DDL的表可以读，但是不可以写。
NONE： 没有任何限制，执行DDL的表可读可写。
EXCLUSIVE： 排它锁，执行DDL的表不可以读，也不可以写。
DEFAULT： 默认值，也就是在DDL语句中不指定LOCK子句的时候使用的默认值。如果指定LOCK的值为DEFAULT，那就是交给MySQL子句去觉得锁还是不锁表。不建议使用，如果你确定你的DDL语句不会锁表，你可以不指定lock或者指定它的值为default，否则建议指定它的锁类型。
执行DDL操作时，ALGORITHM选项可以不指定，这时候MySQL按照INSTANT、INPLACE、COPY的顺序自动选择合适的模式。也可以指定ALGORITHM=DEFAULT，也是同样的效果。如果指定了ALGORITHM选项，但不支持的话，会直接报错。

OPTIMIZE TABLE和ALTER TABLE 表名 ENGINE=INNODB都支持Oline DDL，但依旧建议在业务访问量低的时候使用

Online DDL

最后，再说一下Online DDL，dba的日常工作肯定有一项是ddl变更，ddl变更会锁表，这个可以说是dba心中永远的痛，特别是执行ddl变更，导致库上大量线程处于Waiting for meta data lock状态的时候。因此在5.6版本后引入了Online DDL。

Online DDL推出以前，执行ddl主要有两种方式copy方式和inplace方式，inplace方式又称为fast index creation。相对于copy方式，inplace方式不拷贝数据，因此较快。但是这种方式仅支持添加、删除索引两种方式，而且与copy方式一样需要全程锁表，实用性不是很强。Online方式与前两种方式相比，不仅可以读，还可以支持写操作。

执行online DDL语句的时候，使用ALGORITHM和LOCK关键字，这两个关键字在我们的DDL语句的最后面，用逗号隔开即可。示例如下：

ALTER TABLE tbl_name ADD COLUMN col_name col_type, ALGORITHM=INPLACE, LOCK=NONE;
1

三、配置数据清理

前期准备

过期的配置数据主要有两个来源，一是下线/到期的业务规则，二是系统技改迭代中产生的控制开关，包括硬开关和灰度控制。获取到需要清理的配置项后，需要做以下操作
  ■ 梳理该配置涉及到的业务逻辑与系统流程。
  ■ 与业务方，产品，对应开发仔细确认下线该配置涉及到的业务影响。
  ■ 代码中全局搜索该配置名称，找出所有引用以及关联的逻辑，并在技术层面上再次确认影响范围。

执行过程

  ■ 删除该配置关联到的所有代码，善用全局搜索。
  ■ 测试环境中新代码做黑白对照，生产配置的跑一次所有 case，配置源删除对应项后再跑一次所有 case，看两者结果是否完全相同。
  ■ 代码发布前，想好如何拉取受影响的订单，可以通过 DB 取数，也可以通过 log 埋点。
  ■ 代码分集群灰度发布，密切监控相关订单是否符合预期。
  ■ 代码发布完成，监控一小时以上无异常后，可以将配置源中配置项或是对应数据删除。
  ■ 过程中一旦出现问题，需要及时回退配置项，再回滚代码到上个版本，并拉取受影响的项目进行手工补偿，避免影响客人出行或系统稳定性。

四、数据变更与空值处理

数据变更涉及到普通业务数据变更、配置数据变更；空值处理主要有代码逻辑上处理取值结果的方式，特别是对无查询结果的处理方式。

普通数据变更

数据变更流程

【1】评估数据变更影响范围，编写测试案例 > 开发/测试环境修改验证通过 > 提交修改工单 > 经理审批 > DBA 审批 > 数据修改 > 监控。
【2】评估数据修改方案，告知数据使用方数据变更要点，通知到对应的产品负责人。.
【3】方案设计者准备修改\回滚所需的执行脚本。
【4】开发/测试环境验证。
【5】确定执行时间，并提交 SQL REVIEW 流程 。在这其中，开发组长的职责是最重要的，请做好把关工作！
【6】DBA 联系相关人员进一步沟通。
【7】DBA 执行脚本，开发监控相关服务是否正常。
【8】若有异常，立即联系 DBA 执行回滚操作。

配置数据变更

【1】原则上，不建议开发在高峰期修改生产配置。
【2】关键配置需要审批邮件。
【3】测试环境验证通过。
【4】生产灰度配置开关。（如不能灰度需说明原因。）
【5】观察各项指标是否正常，至少 15 分钟。
【6】全量切换配置开关。
【7】观察各项指标是否正常，至少 20 分钟。

空值处理

空值处理原则

【1】空值的出现可能和当前的业务逻辑并无关系，但是如果处理不当会抛出空指针异常，严重的可能会导致业务系统发生灾难性故障；
【2】根据业务需要，代码中要严格校验数据的空或无结果值，可以将空值替换为特定的值或者直接过滤掉；
【3】主要处理原则有以下几点 空集合返回值，使用 Optional 变量 ，jsr 303，jsr 305 这几种方式，可以让我们的代码可读性更强，出错率更低！
  ■ 空集合返回值 ：如果有集合这样返回值时，除非真的有说服自己的理由，否则，一定要返回空集合，而不是 null
  ■ Optional: 如果你的代码是 jdk8，就引入它！如果不是，则使用 Guava 的 Optional,或者升级 jdk 版本！它很大程度的能增加了接口的可读性！
  ■ jsr 303: 如果新的项目正在开发，不防加上这个试试！一定有一种特别爽的感觉!
  ■ jsr 305: 如果老的项目在你的手上，你可以尝试的加上这种文档型注解，有助于你后期的重构，或者新功能增加了，对于老接口的理解!

五、空值处理案例

业务中的空值

【1】返回值处理：不要返回 null，返回一个空集合。常规处理方式：

public List<User> listUser(){
    List<User> userList = userListRepostity.selectByExample(new UserExample());
    if(CollectionUtils.isEmpty(userList)){//spring util工具类
      return null;
    }
    return userList;
}
1
2
3
4
5
6
7

优雅处理方式：

public List<User> listUser(){
    List<User> userList = userListRepostity.selectByExample(new UserExample());
    if(CollectionUtils.isEmpty(userList)){
      return Lists.newArrayList();//guava类库提供的方式
    }
    return userList;
}
1
2
3
4
5
6
7

【2】文档性约束或使用 Optional 约束返回值

增加弱提升，例如使用注解@exception 来注解说明可能会返回空值异常
引入 jdk8的 Optional，或者使用guava的Optional

常规处理方式：

public interface UserSearchService{
  List<User> listUser();

  User get(Integer id);
}
1
2
3
4
5

优雅处理方式：

public interface UserSearchService{
  /**
   * 根据用户id获取用户信息
   * @param id 用户id
   * @return 用户实体
   * @exception UserNotFoundException
   */
  User get(Integer id);
}
1
2
3
4
5
6
7
8
9

更优雅处理方式：

public interface UserSearchService{

  /**
   * 根据用户id获取用户信息
   * @param id 用户id
   * @return 用户实体,此实体有可能是缺省值
   */
  Optional<User> getOptional(Integer id);
}
1
2
3
4
5
6
7
8
9

方案 2 中 Optional 有两个含义: 存在 or 缺省。 那么通过阅读接口 getOptional()，我们可以很快的了解返回值的意图，这个其实是我们想看到的，它去除了二义性。 它的实现可以写成：

public Optional<User> getOptional(Integer id){
  return Optional.ofNullable(userRepository.selectByPrimaryKey(id));
}
1
2
3
4

约束入参推荐两种方式：【1】强制约束 【2】文档性约束（弱提示）

【1】 强制约束：可以通过 jsr303 进行严格的约束声明:

public interface UserSearchService{
  /**
   * 根据用户id获取用户信息
   * @param id 用户id
   * @return 用户实体
   * @exception UserNotFoundException
   */
  User get(@NotNull Integer id);
  
  /**
   * 根据用户id获取用户信息
   * @param id 用户id
   * @return 用户实体,此实体有可能是缺省值
   */
  Optional<User> getOptional(@NotNull Integer id);
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

当然，这样写，要配合 AOP 的操作进行验证，当然 Spring 已经提供了很好的集成方案，在此我就不在赘述了。

【2】文档性约束
  ■ 在很多时候，我们会遇到遗留代码，对于遗留代码，整体性改造的可能性很小。
  ■ 我们更希望通过阅读接口的实现，来进行接口的说明。
  ■ jsr 305 规范，给了我们一个描述接口入参的一个方式(需要引入库 com.google.code.findbugs:jsr305):
  ■ 可以使用注解: @Nullable @Nonnull @CheckForNull 进行接口说明。比如:

public interface UserSearchService{
  /**
   * 根据用户id获取用户信息
   * @param id 用户id
   * @return 用户实体
   * @exception UserNotFoundException
   */
  @CheckForNull
  User get(@NonNull Integer id);

  /**
   * 根据用户id获取用户信息
   * @param id 用户id
   * @return 用户实体,此实体有可能是缺省值
   */
  Optional<User> getOptional(@NonNull Integer id);
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

数据变更 RCA 案例

12:05-12:44 生产数据同步清理目标库操作，导致引擎刷新缓存以后拿不到目标库数据，该节点为空，项目报空引用，block 住主流程，导致订单跌 0。

经验教训：
  ■ 生产数据同步，需要放在非业务高峰期。
  ■ 对配置和数据库的修改，必须发邮件通知到相关人员，以及要列出监控点，风险点，和回滚策略。
  ■ 生产数据操作，严格按照流程,审批通过之后操作。

六、发布异常案例

案例一

新增表字段，导致已有查询 SQL报 “Ambiguous column name”错误。
原因：原因 JOIN查询中其他表有相同的字段，且未用表别名区分。
解决办法：生产字段不建议 drop新字段，可以发代码修复。比如 MySql 大表发布使用 gh-ost，回退也需要很长的 cope表时间。
预防：制定规则，Join 表时必须添加别名。

gh-ost的使用：不依赖于触发器，是因为他是通过模拟从库，在row binlog中获取增量变更，再异步应用到ghost表的。

案例二

MySql 高 TPS表，发布字段、索引等对象，可能会不成功。
原因：gh-ost 发布机制是用从库的 log 回放 DML 操作到新表，要完成旧表数据的完整 copy 和增量数据追平，才在低峰时间段切换表名，完成发布。高 DML 的表，可能一直追不平数据，导致无法切换。
解决方案：原生 MySql 发布时锁表的，通过 gh-ost 发布是目前主要解决方案。合理规划表的使用场景，通过对表数据 sharding，减小表的体积，和分摊 DML 热点，以期降低 copy 全表的数据体量和需要追平的增量。

案例三

MySQL 生产有数据的表，可空字段，修改为非空，且未给默认值。
发布可正常完成，但是新表落地的数据，因为代码里可能未给字段赋值，报不许为空的错。
解决方案：有数据的生产表，可空修改非空、新增非空字段，必须提供默认值