mysql - MySQL 中的排序陷入困境

Question

我一直在开发一个小型 Perl 程序，它可以处理文章列表，如果用户尚未阅读这些文章，则将其显示给用户。总体来说，它运行得很好，而且速度相当快。然而，今天下午，性能已经从足够快以至于我不担心优化查询下降到每个查询 3-4 秒。为了选择文章，我提出以下查询:

SELECT channelitem.ciid, channelitem.cid, name, description, url, creationdate, author
FROM  `channelitem` 
WHERE ciid NOT 
IN (

SELECT ciid
FROM  `uninet_channelitem_read` 
WHERE uid =  '1030'
)
AND (
cid =117
OR cid =308
OR cid =310
)
ORDER BY  `channelitem`.`creationdate` DESC 
LIMIT 0 , 100

可能的 cid 列表各不相同，并且可能会更多。无论如何，我注意到查询总时间中大约有 2-3 秒用于“ORDER BY”。如果我删除它，只需要大约半秒就能给我返回查询。如果我删除子查询，性能就会恢复正常...但是直到今天下午，子查询在正常工作一周左右后似乎才出现问题。

有什么想法会导致速度减慢这么多吗？我可以做什么来尝试让性能恢复到正常水平？正在查询的表有 45,000 行。目前子查询的表少于 3,000 行。

更新:顺便说一句，如果有人对如何执行多个查询或其他一些可以更有效地完成我想做的事情的技术有建议，我会洗耳恭听。我现在真的很困惑如何解决这个问题。我可以以某种方式在连接之前应用 order by 使其应用于真实表而不是派生表吗？这样效率会更高吗？

这是查询的最新版本，源自@Gordon 的建议，如下

SELECT channelitem.ciid, channelitem.cid, name, description, url, creationdate, author
FROM  `channelitem` 
LEFT JOIN (

SELECT ciid, dateRead
FROM  `uninet_channelitem_read` 
WHERE uid =  '1030'
)alreadyRead ON channelitem.ciid = alreadyRead.ciid
WHERE (
alreadyRead.ciid IS NULL
)
AND  `cid` 
IN ( 6648, 329, 323, 6654, 6647 ) 
ORDER BY  `channelitem`.`creationdate` DESC 
LIMIT 0 , 100

另外，我应该提到这两个表的数据库结构是什么样的——也许有人可以发现该结构的一些奇怪之处:<​​/p>

CREATE TABLE IF NOT EXISTS `channelitem` (
  `newsversion` int(11) NOT NULL DEFAULT '0',
  `cid` int(11) NOT NULL DEFAULT '0',
  `ciid` int(11) NOT NULL AUTO_INCREMENT,
  `description` text CHARACTER SET utf8 COLLATE utf8_unicode_ci,
  `url` varchar(222) DEFAULT NULL,
  `creationdate` datetime DEFAULT NULL,
  `urgent` varchar(10) DEFAULT NULL,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `lastchanged` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `author` varchar(255) NOT NULL,
  PRIMARY KEY (`ciid`),
  KEY `newsversion` (`newsversion`),
  KEY `cid` (`cid`),
  KEY `creationdate` (`creationdate`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1638554365 ;

CREATE TABLE IF NOT EXISTS `uninet_channelitem_read` (
  `ciid` int(11) NOT NULL,
  `uid` int(11) NOT NULL,
  `dateRead` datetime NOT NULL,
  PRIMARY KEY (`ciid`,`uid`),
  KEY `ciid` (`ciid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Answer 1

尝试此类查询的左外连接版本永远不会有什么坏处:

SELECT ci.ciid, ci.cid, ci.name, ci.description, ci.url, ci.creationdate, ci.author
FROM  `channelitem` ci left outer join
       (SELECT ciid
        FROM  `uninet_channelitem_read` 
        WHERE uid =  '1030'
       ) cr
       on ci.ciid = cr.ciid
where cr.ciid is null and
      ci.cid in (117, 308, 310)
ORDER BY ci.`creationdate` DESC 
LIMIT 0 , 100

使用 uninet_channelitem_read(ciid) 上的索引以及可能在 channelitem(cid, ciid,createddate) 上的索引，此查询会更快。