记一次sql优化

今天筛选爬虫失败的任务的时候，要求出一个子集的补集，使用not in语句效率太慢，后来通过left join实现了性能优化。

简朴的想法

今有一爬虫，每次从tasks中取出任务，将结果存储在materials数据表中，在完成时将tasks对应finished列的值从0变成1. 有时出现非HTTP错误时虽然finished列的值是1，但是任务并没有完成，也就是说materials表里并没有对应数据。

为了找出所有没有正常完成的任务，现构造sql语句如下：

select
    *
from
    tasks
where
    id not in (
        select
            a.id
        from
            tasks a
            join materials b on a.url = b.url
    );

不那么显然地，这句话执行巨慢，3秒没有反应就被我强行终止了（ctrl + c）。有趣的是，如果上面的where not in改为where in语句，效率是正常的。具体的原理感兴趣者可以bing一下。

为了解决这个问题，稍微查阅以下资料，我决定使用left join来代替原本的where not in语句。（mysql并没有except语句）

select
    *
from
    tasks
    left join (
        select
            *
        from
            tasks a
            join materials b on a.url = b.url
    ) c on tasks.id = c.id
where
    c.url is NULL

不幸的是，执行依旧很慢... 通过一番简单的分析，推测子语句会执行很多次，所以拖慢了执行速度，原因和where not in是一样的。

于是，稍微变化一下了的最终方案如下：

-- 创建临时表，用来存储已经完成的任务
create temporary table `tmp` (
    `id` int NOT NULL,
    `url` varchar(256) NOT NULL,
    `finished` tinyint(1) DEFAULT '0',
    `created_at` datetime DEFAULT NULL,
    `updated_at` datetime DEFAULT NULL,
    `deleted_at` datetime DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `idx_tasks_deleted_at` (`deleted_at`)
);

insert into
    tmp
select
    distinct a.*
from
    tasks a
    join materials b on a.url = b.url;

-- 创建临时表，存储未完成的task的id
create temporary table `list` (`id` int, PRIMARY KEY(`id`));

insert into
    list
select
    tasks.id
from
    tasks
    left join tmp on tasks.id = tmp.id
where
    tmp.id is NULL;

-- 这步的子语句不能包含tasks表，这也是上一语句的存在意义
update
    tasks
set
    finished = 0
where
    id in (
        select
            id
        from
            list
    );