YiluPHP
这家伙很懒，什么都没有留下...
获赞
32

经验 MySQL一条SQL删除所有用户超限的数据，只留最新的300条

浏览数 127688 最后修改时间
有一个需求：记录所有用户访问页面的记录，但是每个用户只存储300条最新的记录，因此需要每天清除过期的数据。如果SQL没写好，所需耗时8小时，而好的SQL只需要20几秒钟。
先创建两个数据表：
1、存储页面信息的表：page_list
2、存储用户访问记录的表：visit_history

CREATE TABLE `page_list` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `page_name` varchar(128) NOT NULL DEFAULT '' COMMENT '页面名称',
  PRIMARY KEY (`id`) USING BTREE,
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='页面信息';



CREATE TABLE `visit_history` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `uid` int(11) unsigned NOT NULL COMMENT '用户ID',
  `page_id` int(11) unsigned NOT NULL COMMENT '页面的id',
  `create_time` int(11) NOT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  KEY `indexUid` (`uid`) USING BTREE,
  KEY `indexPage` (`page_id`) USING BTREE,
  KEY `indexCreateTime` (`create_time`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='用户访问记录';

在程序实现时需要每天清除多余的数据，包含两种数据：
1、清除每个用户超过300条的旧数据；
2、删除用户记录表中不再使用（关联）的页面信息，这里称为“无用的页面信息”。

耗时的方案一：删除用户超出300条的访问记录

每次取出3千条超限的用户ID及其已有的记录条数
SELECT uid, COUNT(1) AS num FROM visit_history WHERE uid>0 
GROUP BY uid HAVING COUNT(1)>300 ORDER BY uid ASC limit 3000;

然后遍历删除每一个用户多余的数据，多余的数量=已有记录数-300
DELETE FROM visit_history WHERE uid=666 ORDER BY create_time ASC LIMIT 60;

我的线上 visit_history 表中有200万数据，这个方案程序的执行时间是8小时，无法接受，必须优化处理。

优化的方案二：删除用户超出300条的访问记录

一开始在程序执行流程和索引上做功课，一直没有效果。最后优化后的方案只需要一条SQL就可以完美得完成，执行时间只要20秒，SQL如下：
DELETE  a.*
FROM visit_history a 
    JOIN (
        SELECT 
            c.uid,
            COALESCE(
                (SELECT create_time FROM visit_history d WHERE d.uid=c.uid ORDER BY create_time DESC LIMIT 299, 1),
                0
            ) AS create_time
    FROM 
        (SELECT DISTINCT uid FROM visit_history ) c 
    ) b ON b.uid=a.uid AND b.create_time > a.create_time;

耗时的方案三：删除无用的页面信息

起初的方案也是遍历扫描，从页面表中取3千条数据，查询它们在访问记录表中是否有使用，把没有使用的页面信息删除，为了减少数据库操作次数，积累到1千条再删除。
我的线上 page_list 表中有80万条数据，按此方案执行耗时10分钟。
#每次取3000条页面信息
SELECT id FROM page_list WHERE id>0 ORDER BY id ASC LIMIT 3000;

#先查询出已经使用的页面ID，再用程序得出未使用的页面ID
SELECT DISTINCT page_id FROM visit_history WHERE page_id IN(111,222,333) LIMIT 3000;

#删除掉未使用的页面信息
DELETE FROM page_list WHERE id IN(111,333) LIMIT 1000;

优化的方案四：删除无用的页面信息

采用与方案二类似的写法，80万的数据只需要5秒钟就处理完了。
DELETE  d.*
FROM page_list d 
    JOIN (
        SELECT a.id AS id2 FROM page_list a LEFT JOIN visit_history b ON a.id=b.page_id WHERE b.page_id IS NULL GROUP BY a.id 
    ) c ON d.id=c.id2;



我来说说