关键词：
【中文标题】如何在 MySQL 中优化此查询【英文标题】：How to optimize this query in MySQL 【发布时间】：2015-10-27 18:08:21 【问题描述】：我有这两张表（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 表有超过 2 亿条记录，我需要使用 PHP 将其导出到文件中，而不是故意死掉。这里的主要问题是执行太慢了。这里的主要杀手是连接到 mdl_course 表。如果我删除它，一切都会很快。
这里是解释：
+----+-------------+-------+-------+---------------------------------------------+----------------------+---------+----------------+------+-----------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------------------------------------+----------------------+---------+----------------+------+-----------------------------------------------------------+ | 1 | SIMPLE | c | range | PRIMARY,mdl_cour_cat_ix | mdl_cour_cat_ix | 8 | NULL | 3152 | Using where; Using index; Using temporary; Using filesort | | 1 | SIMPLE | l | ref | PRIMARY,mdl_log_coumodact_ix,mdl_log_tim_ix | mdl_log_coumodact_ix | 8 | xray2qasb.c.id | 618 | Using index condition; Using where | +----+-------------+-------+-------+---------------------------------------------+----------------------+---------+----------------+------+-----------------------------------------------------------+
有什么方法可以消除临时文件和文件排序的使用吗？你在这里有什么建议？
【问题讨论】：
尝试添加包含 (l.time, l.course) 的索引，因为这些是您查询我们使用的过滤器。您可能也考虑将category 添加到log 表中；即使它没有标准化，它也可能会提高性能，足以值得麻烦。如果你这样做，你也会将l.category 添加到索引中。
您没有在您的选择中使用来自mdl_course 的任何字段。您可以将其作为exists 语句移动到您的where 中
也许值得努力重构这些表，以便将对分析无用的文本字段推送到它们自己的表中，并通过类似于日志的 id 进行引用。这可以减少对本质上更具参考性的查询的一些拖累。后果是，如果有其他资源依赖于该结构，则必须对它们进行重新设计以支持新结构。
不幸的是，这不会发生。我必须按原样处理结构......我只能在需要的地方添加索引或新表......
【参考方案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 的建议！
执行计划：
+----+--------------------+-------+--------+-------------------------+---------+---------+--------------------+----------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------------+-------+--------+-------------------------+---------+---------+--------------------+----------+-------------+ | 1 | PRIMARY | l | range | PRIMARY,mdl_log_tim_ix | PRIMARY | 8 | NULL | 99962199 | Using where | | 2 | DEPENDENT SUBQUERY | c | eq_ref | PRIMARY,mdl_cour_cat_ix | PRIMARY | 8 | xray2qasb.l.course | 1 | Using where | +----+--------------------+-------+--------+-------------------------+---------+---------+--------------------+----------+-------------+
【讨论】：
您可以将此查询的解释计划添加到您的答案帖子中吗？【参考方案2】：尝试将类别选择移到JOIN 之外。在这里，我将它放在 IN() 中，引擎将在连续运行时对其进行缓存。我没有 200M 行要测试，所以 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;
【讨论】：
不幸的是，这个查询在我的数据库上太慢了。等了几分钟后我停止了它。mdl_course 表中有多少条记录？而且，其中有多少人拥有category=0？
6219 条记录，只有一条属于类别 0
尝试使用l.course <> WHATEVER_THAT_COURSE_ID_IS 而不是IN() 子句
这对我不起作用。日志表中可能存在课程表中不再存在的课程条目。我必须同时检查两者，一般来说 Exists 比大型数据集更快。【参考方案3】：
（除了使用EXISTS...）
l.id > 123456 AND l.time > 1234
似乎乞求二维索引。
99962199 -- 桌子很大，对吗？
考虑PARTITION BY RANGE on mdl_log on time。但是……
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
这可能是你真正想要的。
【讨论】：
我会检查它，但真诚地怀疑它可能比现有查询更有效。需要 (id = something) 条件来避免将光标缓慢定位到第 n 条记录。我真的不需要 ORDER BY 时间，我只关心 ORDER BY id 以便我可以尽可能快地进行分页导出。如何在 Postgres 中优化此查询
】如何在Postgres中优化此查询【英文标题】：HowcanIoptimizethisqueryinPostgres【发布时间】：2021-02-1214:19:23【问题描述】：以下查询需要更多时间来运行。如何优化以下查询以运行更多记录？我已经为这个查询运行了ExplainAnalyze。附上... 查看详情
MySQL - 如何优化此查询？
】MySQL-如何优化此查询？【英文标题】：MySQL-Howcanthisquerybeoptimised?【发布时间】：2010-06-2805:04:26【问题描述】：以下查询有效，但10条记录（2秒）的速度非常慢。分析说它创建了一个tmp表，但我不确定为什么。基本上，我将当前... 查看详情
如何在 Firebird 2.1 中优化此查询？
】如何在Firebird2.1中优化此查询？【英文标题】：HowcanIoptimizethisqueryinFirebird2.1?【发布时间】：2012-03-0103:47:12【问题描述】：我正在使用Firebird2.1，我需要一些帮助来优化此查询：（可能通过将IN-s替换为JOINS或其他方式来加快速度... 查看详情
我如何在mysql中优化这个查询？
】我如何在mysql中优化这个查询？【英文标题】：Howicanoptimizethisqueryinmysql?【发布时间】：2014-06-3012:46:34【问题描述】：我有一个这样的查询-SELECTCONCAT(u.name,u.deviceAlias),u.name,u.deviceAlias,j.description,u.descriptionASvlanDescriptionFROMvlaninterfa... 查看详情
如何优化mysql查询
】如何优化mysql查询【英文标题】：howtooptimisemysqlqueries【发布时间】：2012-09-2208:32:56【问题描述】：我从我的网络托管公司收到通知，以优化我的网站中的MySQL查询。他们告诉我，共享托管中不允许运行超过15秒的MySql查询，而该... 查看详情
如何优化这个 MySql 查询 - 连接 3 个表？
】如何优化这个MySql查询-连接3个表？【英文标题】：HowtooptimizethisMySqlquery-joins3tables?【发布时间】：2016-12-0815:24:46【问题描述】：这个查询很慢。它非常简单，使用的3个表在JOIN和WHERE子句中的所有列上都建立了索引。如何优化... 查看详情
需要帮助优化一个有趣的 MySQL 查询
】需要帮助优化一个有趣的MySQL查询【英文标题】：NeedHelponOptimizinganInterestingMySQLQuery【发布时间】：2017-08-0919:51:22【问题描述】：查询优化我在优化此查询的性能方面需要帮助。此查询基本上是查找与casewhen条件列表匹配的所有... 查看详情
如何在 mysql 或 php 中优化或加速我的子查询
】如何在mysql或php中优化或加速我的子查询【英文标题】：Howtooptimizeorspeedupmysubqueryinmysqlorinphp【发布时间】：2015-05-0908:00:21【问题描述】：示例查询：SELECTtable1.t1_id,table1.name,table2.address,(SELECTmessageFROMtable3WHERElogid=table1.t1_idANDmessag... 查看详情
编写快速 MySQL INSERT 查询或优化此查询
】编写快速MySQLINSERT查询或优化此查询【英文标题】：WritingafastMySQLINSERTqueryoroptimizationthisquery【发布时间】：2016-04-1008:29:23【问题描述】：我写了一个INSERT查询。在本地服务器上运行时，没有问题，但在实时服务器上运行相同的... 查看详情
如何在 MySQL 中获取此查询返回的时间中值？ [复制]
】如何在MySQL中获取此查询返回的时间中值？[复制]【英文标题】：HowcanIgetthemediantimeofthetimesreturnedfromthisqueryinMySQL?[duplicate]【发布时间】：2016-12-2918:05:40【问题描述】：我有以下问题..SELECTTIME(date_time)FROMlogin_attemptsWHEREDATE_FORMAT(DAT... 查看详情
如何提高查询性能？
...方法，互联网公司常用的SQL编写规范，以及在实际情况中如何优化数据库访问等内容，知识脑图如下所示。 MySQL查询优化器SELECT执行过程那么如何提高MySQL的查询性能呢？首先你需要了解查询优化器处理SQL的全过程。以SE... 查看详情
mysql查询优化
...会全部用于b/c一个函数应用于我有索引的col。谁能看到我如何优化这些表或查询？requests表将是3个表中最大的表，将有超过20万条记录。devices目前有大约500条记录，clients也会更小。查询：explai 查看详情
mysql中如何查看优化器优化后的执行计划
参考技术A一、MySQL数据库有几个配置选项可以帮助我们及时捕获低效SQL语句1，slow_query_log这个参数设置为ON，可以捕获执行时间超过一定数值的SQL语句。2，long_query_time当SQL语句执行时间超过此数值时，就会被记录到日志中，建议... 查看详情
如何在 mysql 查询下进行优化（我是优化新手）
】如何在mysql查询下进行优化（我是优化新手）【英文标题】：howcanIoptimisebelowmysqlquery(Iamnewtooptimisation)【发布时间】：2015-09-1306:22:59【问题描述】：如何优化这个MySQL查询？下面也提到了执行计划。Selecttlk.id,um.store_users_idfromuser_m... 查看详情
如何优化这个 MySQL 查询
】如何优化这个MySQL查询【英文标题】：HowtooptimizethisMySQLquery【发布时间】：2010-05-2717:18:02【问题描述】：这个查询在数据库很小的时候工作得很好，但是现在数据库中有数百万行，我意识到我应该早点考虑优化它。它正在查看... 查看详情
如何优化此 SQL 查询
】如何优化此SQL查询【英文标题】：HowcanIoptimisethisSQLquery【发布时间】：2011-12-1313:06:25【问题描述】：我正在编写一个软件，用于识别已放在网络服务器(CMS)上但不再需要且应该/可以删除的文件。首先，我尝试手动重现所有必需... 查看详情
是否可以优化此 mysql 查询？
】是否可以优化此mysql查询？【英文标题】：Isitpossibletooptimizethismysqlquery?【发布时间】：2013-01-1419:14:56【问题描述】：我有一个包含几百万行的表，我正在查询该表并想知道是否可以通过添加索引或其他任何方式来优化查询。表... 查看详情
如何优化 MySQL 数据库/查询
】如何优化MySQL数据库/查询【英文标题】：HowtooptimizeMySQLdatabase/query【发布时间】：2012-11-0510:54:41【问题描述】：您好，我希望我能在如何优化我的数据库方面获得一些帮助，这样我就不需要一年的时间了。我知道要加快速度，... 查看详情