mysql - SQL优化: Finding first unwatched video using no subselect

Question

我的问题。我认为我有一个性能破坏子查询但无法证明。我第一次尝试使用 JOIN 失败了。 有人可以提供更高性能的解决方案或确认这确实可以接受吗？

我有两个表，一个包含待办事项列表 (joblist)，另一个包含跟踪每个用户的进度 (userprogress)。一项工作可以但不能是观看视频。 (这是一个电子学习网站。)

观看视频后，它们会在枚举字段中自动设置为“已完成”。用户也可以手动跳过视频(status = 'skipped')。

下面提供了表格结构。

要获取用户根本未观看(userprogress 中没有记录)或已开始观看(status = 'begun')的第一个视频，我正在使用此查询。

我已经在用于选择或排序的所有字段上设置了索引。但是我不确定是否都需要它们。

SELECT 语句有两部分

1. 内部子选择，我在其中获取所有看过或跳过的视频
2. 主语句，我从 (1) 找到的视频中获取第一个视频

PHP 有一个命名参数 (:email)，可以避免 SQL 注入(inject)。

SELECT jl.where_to_do_it FROM joblist AS jl
INNER JOIN userprogress AS up
ON (jl.joblistID = up.joblistID)
WHERE jl.what_to_do = 'video'
     AND jl.joblistID NOT IN
      (
        SELECT injl.joblistID
        FROM joblist AS injl
        INNER JOIN userprogress AS inup
        ON (injl.joblistID = inup.joblistID)
        WHERE
             (inup.status = 'finished' OR inup.status = 'skipped')
          AND
             inup.email = :email
          AND
             injl.what_to_do = 'video'
      )
ORDER BY jl.joborder ASC
LIMIT 0,1

这是 EXPLAIN 的输出，我需要一些帮助来理解。

id select_type  table     type  possible_keys                 key         key_len  ref          rows   Extra
1  PRIMARY      jl        ref   PRIMARY,what_to_do            what_to_do  602      const        9      Using where; Using filesort
1  PRIMARY      up        ref   joblistID                     joblistID   3        jl.joblistID 1      Using index
2  DEP-SUB      injl  eq_ref    PRIMARY,what_to_do            PRIMARY     3        func         1      Using where
2  DEP-SUB      inup  eq_ref    nodup,email,joblistID,status  nodup       455      const,func   1      Using where

创建表命令:

CREATE TABLE IF NOT EXISTS `joblist` (
  `joblistID` mediumint(10) unsigned NOT NULL AUTO_INCREMENT,
  `what_to_do` varchar(200) COLLATE utf8_swedish_ci NOT NULL,
  `where_to_do_it` varchar(100) COLLATE utf8_swedish_ci NOT NULL,
  `joborder` mediumint(6) NOT NULL,
  `track` enum('fast','slow','bonus') COLLATE utf8_swedish_ci NOT NULL DEFAULT 'slow',
  `chapter` tinyint(11) unsigned NOT NULL COMMENT 'What book chapter it relates to',
  PRIMARY KEY (`joblistID`),
  KEY `nodupjobs` (`joborder`,`chapter`),
  KEY `what_to_do` (`what_to_do`),
  KEY `where_to_do_it` (`where_to_do_it`),
  KEY `joborder` (`joborder`),
  KEY `track` (`track`),
  KEY `chapter` (`chapter`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_swedish_ci COMMENT='Suggested working order';


CREATE TABLE IF NOT EXISTS `userprogress` (
  `upID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `email` varchar(150) COLLATE utf8_swedish_ci NOT NULL COMMENT 'user id',
  `joblistID` mediumint(9) unsigned NOT NULL COMMENT 'foreign key',
  `progressdata` varchar(300) COLLATE utf8_swedish_ci DEFAULT NULL COMMENT 'JSON object describing progress',
  `percentage_complete` tinyint(3) unsigned DEFAULT NULL,
  `status` enum('begun','skipped','finished') COLLATE utf8_swedish_ci DEFAULT 'begun',
  `lastupdate` datetime NOT NULL,
  `approved` datetime DEFAULT NULL,
  PRIMARY KEY (`upID`),
  UNIQUE KEY `nodup` (`email`,`joblistID`),
  KEY `email` (`email`),
  KEY `joblistID` (`joblistID`),
  KEY `status` (`status`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_swedish_ci COMMENT='Keep track of what the user has done';

Answer 1

是的，你是对的。 IN 和 NOT IN 在 mysql 中表现特别差。这是修改后的版本:

SELECT jl.where_to_do_it
FROM joblist jl INNER JOIN
     userprogress up
     ON (jl.joblistID = up.joblistID)
 WHERE jl.what_to_do = 'video' and
       not exists (
           (SELECT 1
            FROM joblist injl INNER JOIN
                 userprogress inup
                 ON (injl.joblistID = inup.joblistID)
            WHERE (inup.status = 'finished' OR inup.status = 'skipped') and
                  inup.email = :email and
                  injl.what_to_do = 'video' and
                  ini1.joblistid = j1.joblistid
          )
ORDER BY jl.joborder ASC
LIMIT 0,1