海量数据的更新/删除策略

1.延迟删除
一条数据会被很多数据引用。当被引用的数据删除时，引用的数据也需要删除。这时候可以用延迟删除的方法。
例子：
用户发表的帖子，可以被转发到很多地方。所有转发过的贴都是对原帖的引用，用户接着把他的原帖删除，所有被转发过的帖子也需要进行清理。我们采用 的是延迟删除的方法，用户操作时，先直接删除原帖，被转发的贴，在点击时，会到原帖加载内容，这时发现原帖已经不存在，此时这条被点击的转发贴也会被删 除。

当用户访问引用的数据时，发现被引用的数据不存在，此时删除引用的数据。

2.线下删除
一条数据关联到很多数据。但这条数据是所有其他数据的入口。
例子：
一个扣扣群里，会有很多内容，相册，图片等等。在解散扣扣群时是不可能同步删除和该群相关的所有数据的。此时可以用线下删除的方法。先将扣扣群的 状态置为 inactive（解散）状态。然后提交一条任务，在指定的时候将所有和该扣扣群相关的数据都删除。任务可以在系统负载比较低的时候触发。因为扣扣群的状 态已经被设置为inactive状态，它也不会再被其他人访问到。所有访问需要加状态进行判断。

3.异步删除
当某个系统的服务和其他系统的服务做了集成后，业务数据就可能是相互关联的。当某个服务的数据被删除时，需要通知相关联的所有系统删除和他相关联的数据。
例子：
用户会对他所加入的群进行了偏好设置，比如对某个群的评价，是否接收该群的通知等等。而这些数据是存在用户系统（其他服务器）上的。当一个群组被 解散时，无法同步地调用用户系统的服务删除所有的数据。因为一个群组可能有几万甚至更多的成员。此时可以用异步的方式通知用户系统，群组系统发送一条“群 解散”消息给用户系统，用户系统接收到消息后，会把所有相关联的数据都清除。

延迟删除结合使用：
如果数据量很大的话异步删除也需要相当长的时间，这时可以结合使用延迟删除的方法。
当用户在用户中心点击某群的偏好设置时，系统发现该群组已经被删除，就通知用户该群不存在，并删除相关的偏好设置。

线下删除结合使用
数据量很大如果立即删除会给系统突发地带来相当大的负载，甚至可能会引起用户访问的响应率降低。这时需要采用线下删除的方法，将删除任务分割成很多很小的任务。分批地提交给系统定时处理。

4.不删除
有些相关联的数据，重要性很低，又有有效期。我们是不会再花费额外的代价去删除它们的。

例子：
系统会记录用户的访问足迹。用户可以查询他的这些访问足迹。
您在2011.02.03在群组B（链接）回复了C（链接）发表的帖子D（链接）。
当B被解散或D被删除后，不需要主动去清理这条数据。因为所有的访问足迹都是有有效期的，当有效期到了的时候，这条数据就会被自动清除。
但是当D被删除后，用户点击D链接会提示用户404。

MySQL删除大批量数据优化

背景：
经过实验，每次删除400万条要花1.5 - 3小时，而且是越到后面越慢，正常的话，需要大约102个小时，大约4天半时间。这在生产环境下是不能接受的。

产生原因：
每次删除记录，数据库都要相应地更新索引，查询MySQL官方手册得知删除数据的速度和创建的索引数量是成正比的；这是很慢的IO操作，而且后面索引碎片越来越多，就更慢，这就是为什么一开始只花1.5小时，后面要3小时才能删除400万条记录的原因。

• 1、选择不需要删除的数据，并把它们存在一张相同结构的空表里 ：INSERT INTO t_copy SELECT * FROM t WHERE … ;
• 2、重命名原始表，并给新表命名为原始表的原始表名： RENAME TABLE t TO t_old, t_copy TO t;
• 3、删掉原始表：DROP TABLE t_old;

delete limit优点

       用于DELETE的MySQL唯一的LIMIT row_count选项用于告知服务器在控制命令被返回到客户端前被删除的行的最大值。本选项用于确保一个DELETE语句不会占用过多的时间。您可以只重复DELETE语句，直到相关行的数目少于LIMIT值为止。

     如果DELETE语句包括一个ORDER BY子句，则各行按照子句中指定的顺序进行删除。此子句只在与LIMIT联用是才起作用。

     例如，以下子句用于查找与WHERE子句对应的行，使用timestamp_column进行分类，并删除第一（最旧的）行：

DELETE FROM somelog WHERE user = 'jcole' ORDER BY timestamp_column LIMIT 1;

Mysql大表update的正确姿势

前言  

      有些时候在进行一些业务迭代时需要我们对Mysql表中数据进行全表update，如果是在数据量比较小的情况下（万级别），可以直接执行sql语句，但是如果数据量达到一个量级后，就会出现一些问题，比如主从架构部署的Mysql，主从同步需要需要binlog来完成，而binlog格式如下，其中使用statement和row格式的主从同步之间binlog在update情况下的展示：

        我们当前线上mysql是使用row格式binlog来进行的主从同步，因此如果在亿级数据的表中执行全表update，必然会在主库中产生大量的binlog，接着会在进行主从同步时，从库也需要阻塞执行大量sql，风险极高，因此直接update是不行的。本文就从我最开始的一个全表update sql开始，到最后上线的分批更新策略，如何优化和思考来展开说明。

直接update的问题

      我们前段时间需要将用户的一些基本信息存储从http转换为https，库中数据大概在几千w的级别，需要对一些大表进行全表update，最开始我试探性的跟dba同事抛出了一个简单的update语句，想着流量低的时候执行，如下:

update tb_user_info set user_img=replace(user_img,'http://','https://')

深度分页问题

     上面肯定是不合理的会给主库生成binlog、从库接收binlog写数据带来很大的压力，于是就想使用脚本分批处理如下所示： 写一个这样的脚本，依次分批替换，limit的游标不断增加。大概一看是没有问题的，但是仔细一想mysql的limit游标进行的范围查找原理，是下沉到B+数的叶子节点进行的向后遍历查找，在limit数据比较小的情况下还好，limit数据量比较大的情况下，效率很低接近于全表扫描，这也就是我们常说的“深度分页问题”。

update tb_user_info set user_img=replace(user_img,'http://','https://') limit 1000;

in的效率

        既然mysql的深分页有问题，那么我就把这批id全部查出来，然后更新的id in这些列表，进行批量更新可以吗？于是我又写了类似下面sql的脚本。结果是还不行，虽然mysql对于in这些查找有一些键值预测，但是仍然是很低效。

select * from tb_user_info where id> {index} limit 100;

update tb_user_info set user_img=replace(user_img,'http','https')where id in {id1,id3,id2};

最终版本

     最终在与dba的多次沟通下，我们写了如下的sql及脚本，这里有几个问题需要注意，我们在select sql中使用了这个语法/*!40001 SQL_NO_CACHE */，这个语法的意思就是本次查询不使用innodb的buffer pool，也不会将本次查询的数据页放到buffer pool中作为热点数据的缓存。接着对于查询强制使用主键索引FORCE INDEX(PRIMARY)，并且根据主键索引排序，排序后的数据进行id游标的筛选。最后执行update更新时，由于我们在前面的sql中查询到的就是已经排序后的主键，因此可以对id执行范围查找。

select /*!40001 SQL_NO_CACHE */ id from tb_user_info FORCE INDEX(`PRIMARY`) where id> "1" ORDER BY id limit 1000,1;

update tb_user_info set user_img=replace(user_img,'http','https') where id >"{1}" and id <"{2}";

我们可以仅关注第一个sql，如下图所示，是buffer pool大概内容，我们可以通过这个no cache的关键字，对批量处理的数据进行强制指定不走buffer pool，不把这些冷数据影响到正常使用的缓存内容，防止效率的降低，其实mysql在一些备份的动作中。使用的数据扫描sql也会带上这个关键字，防止影响到正常的业务缓存；接着需要强制对当前查询指定的主键索引，然后进行排序，否则mysql有可能在计算io成本进行索引选择时，选择其他的索引。

      使用这样的方式对数据库进行批量更新可以通过一个接口来控制速率，对于数据库主从同步、iops、内存使用率等关键属性进行观察，手动调整刷库速率。这样看是单线程阻塞的操作，其实接口也可以定义线程个数等属性，接口中根据赋予的线程个数，通过线程池并行刷数据，从而提高全表更新速率的上限，同时对速率进行控制控制。

CREATE PROCEDURE `update_t1`()
BEGIN 
   declare i int;      
   set i = 0;   
   while i <= 27422755 DO   
      update t1 set c1=1 where id>=i and id < i+10000;
      commit;
      set i = i+10000;
   end while;      
end

程序逻辑

$pageSize = 10000;
$page = 1;
$sql = "select max(id) from aa limit 1";
$maxId = $monthConn->fetchColumn($sql);
do {
    $start = ($page - 1) * $pageSize;
    $end = $page * $pageSize;
    $sql = "update aa set account=1 where id>$start and id<=$end;";
    $output->writeln($sql);
    $page++;
    $monthConn->exec($sql);
} while ($end < $maxId);

其他问题      

     如果我们使用snowflake雪花算法或者自增主键来生成主键id的话，插入的记录都是根据主键id顺序插入的，如果使用uuid这种我们怎么处理？当然是业务中就预先处理了，先把入库的数据提前进行替换，进行代码上线后再进行的全量数据更新了。

结语

刷数据本来是一个异常枯燥的工作内容，但是从这次数据量较大的数据更新从而与dba同事的多次沟通后，也对mysql有了一些新的理解，包括不限于下面几个，共同学习。

• binlog格式带来的大数据量更新的主从同步问题；
• Mysql深分页的效率问题；
• 全表扫数据如何防止对buffer pool污染到我们业务正常的热点数据。