如何在MySQL中优化此查询

Question

我有这两张桌子(Moodle 2.8)：

CREATE TABLE `mdl_course` (
  `id` bigint(10) NOT NULL AUTO_INCREMENT,
  `category` bigint(10) NOT NULL DEFAULT '0',
  `sortorder` bigint(10) NOT NULL DEFAULT '0',
  `fullname` varchar(254) NOT NULL DEFAULT '',
  `shortname` varchar(255) NOT NULL DEFAULT '',
  `idnumber` varchar(100) NOT NULL DEFAULT '',
  `summary` longtext,
  `summaryformat` tinyint(2) NOT NULL DEFAULT '0',
  `format` varchar(21) NOT NULL DEFAULT 'topics',
  `showgrades` tinyint(2) NOT NULL DEFAULT '1',
  `newsitems` mediumint(5) NOT NULL DEFAULT '1',
  `startdate` bigint(10) NOT NULL DEFAULT '0',
  `marker` bigint(10) NOT NULL DEFAULT '0',
  `maxbytes` bigint(10) NOT NULL DEFAULT '0',
  `legacyfiles` smallint(4) NOT NULL DEFAULT '0',
  `showreports` smallint(4) NOT NULL DEFAULT '0',
  `visible` tinyint(1) NOT NULL DEFAULT '1',
  `visibleold` tinyint(1) NOT NULL DEFAULT '1',
  `groupmode` smallint(4) NOT NULL DEFAULT '0',
  `groupmodeforce` smallint(4) NOT NULL DEFAULT '0',
  `defaultgroupingid` bigint(10) NOT NULL DEFAULT '0',
  `lang` varchar(30) NOT NULL DEFAULT '',
  `theme` varchar(50) NOT NULL DEFAULT '',
  `timecreated` bigint(10) NOT NULL DEFAULT '0',
  `timemodified` bigint(10) NOT NULL DEFAULT '0',
  `requested` tinyint(1) NOT NULL DEFAULT '0',
  `enablecompletion` tinyint(1) NOT NULL DEFAULT '0',
  `completionnotify` tinyint(1) NOT NULL DEFAULT '0',
  `cacherev` bigint(10) NOT NULL DEFAULT '0',
  `calendartype` varchar(30) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `mdl_cour_cat_ix` (`category`),
  KEY `mdl_cour_idn_ix` (`idnumber`),
  KEY `mdl_cour_sho_ix` (`shortname`),
  KEY `mdl_cour_sor_ix` (`sortorder`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `mdl_log` (
  `id` bigint(10) NOT NULL AUTO_INCREMENT,
  `time` bigint(10) NOT NULL DEFAULT '0',
  `userid` bigint(10) NOT NULL DEFAULT '0',
  `ip` varchar(45) NOT NULL DEFAULT '',
  `course` bigint(10) NOT NULL DEFAULT '0',
  `module` varchar(20) NOT NULL DEFAULT '',
  `cmid` bigint(10) NOT NULL DEFAULT '0',
  `action` varchar(40) NOT NULL DEFAULT '',
  `url` varchar(100) NOT NULL DEFAULT '',
  `info` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `mdl_log_coumodact_ix` (`course`,`module`,`action`),
  KEY `mdl_log_tim_ix` (`time`),
  KEY `mdl_log_act_ix` (`action`),
  KEY `mdl_log_usecou_ix` (`userid`,`course`),
  KEY `mdl_log_cmi_ix` (`cmid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

这个问题是：

SELECT l.id,
       l.userid AS participantid,
       l.course AS courseid,
       l.time,
       l.ip,
       l.action,
       l.info,
       l.module,
       l.url
FROM   mdl_log l
INNER JOIN mdl_course c ON l.course = c.id AND c.category <> 0      
WHERE 
      l.id > [some large id]
      AND
      l.time > [some unix timestamp]
ORDER BY l.id ASC
LIMIT 0,200

mdl_log表有200多万条记录，我需要使用PHP将它导出到文件中，而不是故意死掉。这里的主要问题是执行这个过程太慢了。这里的主要杀手是mdl_course表的连接。如果我把它移开，一切都会运转得很快。

以下是解释：

+----+-------------+-------+-------+---------------------------------------------+----------------------+---------+-----------+------+-----------------------------------------------------------+ _~_+----+-------------+-------+-------+-------------------------------------------- / _len / ref _ref_{{e76f}-+----------------------+---------+----------------+------+-----------------------------------------------------------+ |1\x{e76f}简单\c_mdl_cour_cat_ix、mdl_cour_cat_ix、8、NULL、3152、使用；使用索引；使用临时的文件；使用使用索引条件的文件长度为/ index /index条件。使用where _x_ +----+-------------+-------+-------+---------------------------------------------+----------------------+---------+----------------+------+-----------------------------------------------------------+

是否有任何方法可以删除临时文件和文件的使用？你在这里有什么建议？

Answer 1

经过一些测试后，此查询工作速度与预期的一样快：

SELECT l.id,
       l.userid AS participantid,
       l.course AS courseid,
       l.time,
       l.ip,
       l.action,
       l.info,
       l.module,
       l.url
FROM   mdl_log l
WHERE 
      l.id > 123456
      AND
      l.time > 1234
      AND
      EXISTS (SELECT * FROM mdl_course c WHERE l.course = c.id AND c.category <> 0  )
ORDER BY l.id ASC
LIMIT 0,200

感谢JamieD77的建议！

执行计划：

+----+--------------------+-------+--------+-------------------------+---------+---------+--------------------+-+---------------+---------+---------+--------------------+----------+-------------+mdl_log_tim_ix _~_使用where _ +----+--------------------+-------+--------+-------------------------+---------+---------+-使用mdl_cour_cat_ix _

Answer 2

尝试将类别选择移出JOIN之外。在这里，我把它放在一个IN()中，引擎将在连续运行时缓存它。我没有2亿行的测试，所以YMMV。

DESCRIBE 

SELECT l.id,
   l.userid AS participantid,
   l.course AS courseid,
   l.time,
   l.ip,
   l.action,
   l.info,
   l.module,
   l.url
FROM   mdl_log l   
WHERE 
  l.id > 1234567890
  AND
  l.time > 1234567890
  AND 
  l.course IN (SELECT c.id FROM mdl_course c WHERE c.category > 0)      
ORDER BY l.id ASC
LIMIT 0,200;

Answer 3

(除了使用EXISTS.)

  l.id > 123456 AND l.time > 1234

好像在乞求一个二维指数。

99962199 --桌子很大，对吗？

考虑一下PARTITION BY RANGE on mdl_log on time。但是..。

• 没有超过50个分区，其他的低效率就会出现。
• 分区可能无助于id和time处于锁定步态。典型案例：id是AUTO_INCREMENT，time大约是INSERT的时间。

如果适用，请考虑：

PRIMARY KEY(time, id)  -- see below
INDEX(id)              -- Yes, this is sufficient for `id AUTO_INCREMENT`.

有了这些索引，您可以高效地完成

WHERE time > ...
ORDER BY time, id

这可能就是你真正想要的。