社区博客 | MySQL数据清理与变更介绍
01 
数据库记录清理
前期准备
【1】对于生产表，联系 DBA 拉取符合数据清理条件的表，主要包含三个条件：
容量超过限制，占用过多空间，一般认为超过 50GB 都算大表；
记录条数过多，影响性能，例如条数 > 2 亿；
存在常规查询语句由于数据量增大而变慢的情况。
【2】新建表时，评估表的预期数据总量。在每次新建一张表时，都应该根据每日新增数据和业务需要的记录保存期限来评估表的预期数据总量。
如果预期的记录数过大，可以考虑以下方案：
如果只在某个范围内有效的数据，可以考虑实现数据自动清理的计划任务（JOB），定时批量清理过期数据。
如果数据长期有效，则需要考虑对表或者数据库做拆分，做分库分表。
【3】逐表评估过期条件，此步骤尤为重要，评估不到位可能导致有效的生产数据被清理引发生产问题！
基于 DBTrace 拉取最近该表的所有访问方。
与使用方和产品一起仔细确认数据应用范围，条件主要基于两个维度：状态和日期 例如：机票的有效期为 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 语句耗时，无可清理数据，总删除量等等信息，并根据这些数据动态的调整清理的配置，使得整个清理过程对生产影响尽可能低。
灾备方案
对于一些丢失代价非常高的核心数据，即时经过充分的测试和评估，也不能完全杜绝出错的可能性，所以这时候需要做好灾备的方案。
【1】对于提事件由 DBA 协助清理的数据，清理时需要勾选备份数据并选定保留天数。
【2】对于 JOB 程序自动清理的方式，可以使用 MongoDB 等灵活部署的数据库作为所有待删除数据的回收站。推荐Mongo是因为他新建表和定义表结构非常方便，而且可以直接部署在docker上，成本较低。
程序中需要先 select 出符合删除范围的整条记录，而不仅仅是主键。
维持表字段不变，将上述记录列表插入 MongoDB 的同名 Collection 中，如果 Collection 不存在，Mongo会 自动创建。
应用中可以直接按照表的维度做保留时间的配置项，插入时根据配置直接设置 TTL，过期自动删除。
保证插入成功后，再将原表对应的记录应用平滑策略删除。
一旦由于条件配置错误等原因发生误删数据，可以直接从回收站中还原原始数据。
02 执行了delete，但表文件大小没减小
项目中使用 MySQL 作为数据库，对于表来说，一般为表结构和表数据。表结构占用空间都是比较小的，一般都是表数据占用的空间。
当我们使用 delete 删除数据时，确实删除了表中的数据记录，但查看表文件大小却没什么变化。
MySQL 数据结构
凡是使用过 mysql，对 B+ 树肯定是有所耳闻的，MySQL InnoDB 中采用了 B+ 树作为存储数据的结构，也就是常说的索引组织表，并且数据时按照页来存储的。
因此在删除数据时，会有两种情况：
删除数据页中的某些记录
删除整个数据页的内容
为什么 delete 表数据，磁盘空间却还是被占用
InnoDB 直接将 R2 这条记录标记为删除，称为可复用的位置。如果之后要插入 ID 在 300到700 间的记录时，就会复用该位置。由此可见，磁盘文件的大小并不会减少。
通用删除整页数据也将记录标记删除，数据就复用用该位置，与删除默写记录不同的是，删除整页记录，当后来插入的数据不在原来的范围时，都可以复用位置，而如果只是删除默写记录，是需要插入数据符合删除记录位置的时候才能复用。
因此，无论是数据行的删除还是数据页的删除，都是将其标记为删除的状态，用于复用，所以文件并不会减小。
那怎么才能让表大小变小
DELETE 只是将数据标识位删除，并没有整理数据文件，当插入新数据后，会再次使用这些被置为删除标识的记录空间，可以使用 OPTIMIZE TABLE 来回收未使用的空间，并整理数据文件的碎片。
OPTIMIZE TABLE 表名;
