MySql左联接需要很长时间-索引已被添加。

Question

我使用以下查询

SELECT a.job_number as f31jn, b.job_number as f32jn
FROM 
f31 a LEFT JOIN f32 b 
ON (a.job_number = b.job_number AND a.date_audit = b.date_audit)
WHERE date_format(a.date_audit, '%b %Y') = 'Dec 2012'
GROUP BY a.job_number, a.ref_id, b.job_number, b.ref_id

上面的查询大约需要160秒来完成。

我为下列列添加了索引：

f31 - job_number, date_audit (separate indexes)
f32 - job_number, date_audit (separate indexes)

中的上述查询结果的EXPLAIN

​

编辑

将查询更改为

SELECT a.job_number as f31jn, b.job_number as f32jn
FROM 
f31 a LEFT JOIN f32 b 
ON (a.job_number = b.job_number AND a.date_audit = b.date_audit)
WHERE a.date_audit >= '2012-12-01' AND a.date_audit < '2013-01-01'
GROUP BY a.job_number, a.ref_id, b.job_number, b.ref_id

但死刑的执行时间并没有下降。它保持在150至160秒的接近范围内。

EXPLAIN现在出现在

​

对于表a，它仍在搜索90%的记录(总记录约为68k)。

Answer 1

MySQL不能使用索引来帮助使用date_format(a.date_audit, '%b %Y') = 'Dec 2012'。

在job_number和date_audit上是否有单独的索引，或者有一个组合索引。如果它是一个组合索引，那么MySQL同样不能单独用于date_audit上的任何形式的查询。要么自己在date_audit上创建一个索引，要么先用date_audit和job_number创建一个组合索引。

然后将WHERE子句转换为将date_audit与实际日期值进行比较。

这是一次黑暗中的尝试，但值得一试：

首先，时间：

SELECT * from f31 a 
WHERE a.date_audit >= '2012-12-01' 
  AND a.date_audit < '2013-01-01'

应该会很快的。如果没有，则需要验证索引和date_audit列的类型(日期、日期时间或时间戳)。

然后：

SELECT DISTINCT a.job_number as f31jn, b.job_number as f32jn
FROM 
  (SELECT * from f31 aa 
   WHERE aa.date_audit >= '2012-12-01' 
     AND aa.date_audit < '2013-01-01') as a    
LEFT JOIN f32 b 
ON (a.job_number = b.job_number AND a.date_audit = b.date_audit)

如果这没有解决这个问题，那么请使用模式和至少几个测试数据来设置一个SQL Fiddle。

Answer 2

首先考虑重构这一部分：

date_format(a.date_audit, '%b %Y') = 'Dec 2012'

转入：

a.date_audit >= '2012-12-01' && a.date_audit < '2013-01-01'

另外，尝试添加组合索引，如下所示：

f31 - (date_audit, job_number)
f32 - (date_audit, job_number)

MySQL将每个表只使用一个索引，因此组合它们应该同时为联接和date_audit上的查询指定它们。