MySQL:分组/有序/左连接查询非常慢

Question

我的查询有一个问题，它花费的时间太长(仅仅这个简单的查询就超过了两秒)。

乍一看，这似乎是一个索引问题，所有连接的字段都已编入索引，但我找不到其他我可能需要编入索引以加快速度的内容。一旦我将我需要的字段添加到查询中，它就会变得更慢。

SELECT  `jobs`.`job_id` AS  `job_id` FROM tabledef_Jobs AS jobs
 LEFT JOIN tabledef_JobCatLink AS jobcats ON jobs.job_id = jobcats.job_id
 LEFT JOIN tabledef_Applications AS apps ON jobs.job_id = apps.job_id
 LEFT JOIN tabledef_Companies AS company ON jobs.company_id = company.company_id
GROUP BY  `jobs`.`job_id` 
ORDER BY  `jobs`.`date_posted` ASC
LIMIT 0 , 50

表行数 (~):tabledef_Jobs (108k)、tabledef_JobCatLink (109k)、tabledef_Companies (100)、tabledef_Applications (50k)

在这里你可以看到Describe。 “使用临时”似乎是减慢查询速度的原因:

表格索引截图:

任何帮助将不胜感激

用答案编辑

感谢@Steve(标记答案)，最终改进了查询。最终，最终查询从 ~22s 减少到 ~0.3s:

SELECT `jobs`.`job_id` AS `job_id` FROM
(
 SELECT * FROM tabledef_Jobs as jobs ORDER BY `jobs`.`date_posted` ASC LIMIT 0 , 50
) AS jobs
 LEFT JOIN tabledef_JobCatLink AS jobcats ON jobs.job_id = jobcats.job_id
 LEFT JOIN tabledef_Applications AS apps ON jobs.job_id = apps.job_id
 LEFT JOIN tabledef_Companies AS company ON jobs.company_id = company.company_id
GROUP BY  `jobs`.`job_id` 
ORDER BY  `jobs`.`date_posted` ASC
LIMIT 0 , 50

Answer 1

好的，我会尝试一下。

查询优化器似乎无法使用索引来完成对 tabledef_Jobs 表的查询。

你有一个偏移量限制，这与你的 ORDER BY 的组合不能在加入之前限制数据量，因此它必须按 job_id 分组，这是一个 PK 和快速 - 但然后订购该数据(临时表和文件排序)，然后限制和丢弃大部分数据，然后最终将其他所有数据加入其中。

我建议，为“job_id, date_posted”的职位添加一个复合索引

所以首先优化基础查询:

SELECT * FROM tabledef_Jobs 
GROUP BY job_id
ORDER BY date_posted
LIMIT 0,50

然后您可以将连接和最终结构组合在一起以进行更高效的查询。

如果不建议您重新考虑您的限制偏移量，我不能就此放弃。这对于较小的初始偏移量来说很好，但当它开始变大时，这可能是性能问题的主要原因。举个例子，假设你用它来分页，如果他们想要第 3,000 页会发生什么——你将使用

LIMIT 3000, 50

然后这将收集 3050 行/操作数据，然后丢弃前 3000 行。

[编辑 1 - 回应以下评论]

我将扩展更多信息，这些信息可能会为您指明正确的方向。不幸的是，没有一个简单的修复程序可以解决它，您必须了解为什么会发生这种情况才能解决它。简单地删除 LIMIT 或 ORDER BY 可能不起作用，毕竟您不想将 then 作为查询的一部分删除，这意味着它必须出于某种目的而存在。

首先优化简单的基本查询，这通常比使用多连接数据集容易得多。

尽管受到了所有的抨击，filesort 没有任何问题。有时这是执行查询的唯一方法。同意这可能是许多性能问题的原因(尤其是在较大的数据集上)，但这通常不是文件排序的错误，而是底层查询/索引策略的错误。

在 MySQL 中，您不能混合索引或混契约(Contract)一索引的顺序 - 执行此类任务将导致文件排序。

如何按照我的建议在 date_posted 上创建索引然后使用:

SELECT jobs.job_id, jobs.date_posted, jobcats .*, apps.*, company .* FROM
(
    SELECT DISTINCT job_id FROM tabledef_Jobs 
    ORDER BY date_posted
    LIMIT 0,50
) AS jobs
LEFT JOIN tabledef_JobCatLink AS jobcats ON jobs.job_id = jobcats.job_id
LEFT JOIN tabledef_Applications AS apps ON jobs.job_id = apps.job_id
LEFT JOIN tabledef_Companies AS company ON jobs.company_id = company.company_id