尝试使用LEFT OUTER JOIN优化MySQL查询 [英] Trying to optimize MySQL query with LEFT OUTER JOIN

问题描述

我有这个查询，除了需要很长时间(7秒，作业表中有40k条记录，wq表中有700k条记录)之外，它的工作效果很好.

I've this query, which works fine except it takes a long while (7 seconds, with 40k records in the jobs table, and 700k in the wq table).

我尝试了一个EXPLAIN，它说它查看作业表中的所有记录，而不使用任何索引.

I tried an EXPLAIN and it says its looking at all the records in the job table, and not using any of the indexes.

我不知道如何告诉MySQL在查找wq表之前应该使用jobs.status字段过滤记录.

I don't know how to tell MySQL that it should use the jobs.status field to filter the the records before looking up the wq table.

此操作的目的是从状态为！= 331的作业以及wq状态为(101，111，151)的任何其他作业中获取所有记录.

The objective of this, is to get all the records from jobs that have a status != 331, and also any other job which has a wq status of (101, 111, 151).

查询:

SELECT jobs.*
FROM jobs
LEFT OUTER JOIN wq ON (wq.job = jobs.id AND jobs.status IN (341, 331) AND wq.status IN (101, 111, 151))
WHERE ((wq.info is not NULL) or (jobs.status != 331 and ack = 0))

EXPLAIN输出:

EXPLAIN output:

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra
1   SIMPLE  jobs    ALL     ack,status,status_ack   NULL    NULL    NULL    38111   Using filesort
1   SIMPLE  wq  ref     PRIMARY,job,status  PRIMARY     4   cts.jobs.id     20  Using where

表定义:

CREATE TABLE jobs ( id int(10) NOT NULL AUTO_INCREMENT,
comment varchar(100) NOT NULL DEFAULT '',
profile varchar(60) NOT NULL DEFAULT '',
start_at int(10) NOT NULL DEFAULT '0',
data text NOT NULL,
status int(10) NOT NULL DEFAULT '0',
info varchar(200) NOT NULL DEFAULT '',
finish int(10) NOT NULL DEFAULT '0',
priority int(5) NOT NULL DEFAULT '0',
ack tinyint(4) NOT NULL DEFAULT '0',
PRIMARY KEY (id),
KEY start_at (start_at),
KEY status (status),
KEY status_ack (status,
ack) ) ENGINE=MyISAM AUTO_INCREMENT=2037530 DEFAULT CHARSET=latin1;


CREATE TABLE wq ( job int(10) NOT NULL DEFAULT '0',
process varchar(60) NOT NULL DEFAULT '',
step varchar(60) NOT NULL DEFAULT '',
status int(10) NOT NULL DEFAULT '0',
run_at int(10) NOT NULL DEFAULT '0',
original_run_at int(10) NOT NULL DEFAULT '0',
info varchar(200) NOT NULL DEFAULT '',
pos int(10) NOT NULL DEFAULT '0',
changed_at int(10) NOT NULL DEFAULT '0',
file varchar(60) NOT NULL DEFAULT '',
PRIMARY KEY (job,
process,
step,
file),
KEY job (job),
KEY status (status) ) ENGINE=MyISAM DEFAULT CHARSET=latin1

推荐答案

不幸的是，mysql(可能还有任何dbms)无法优化像jobs.status != 331 and ack = 0这样的表达式，因为B树不是允许快速查找非必需内容的结构.等于一个恒定值.这样，您将始终获得全面扫描.

Unfortunately mysql (and perhaps any dbms) cannot optimize expressions like jobs.status != 331 and ack = 0 because B-Tree is not a structure that allows to find fast anything that is-not-equal-to-a-constant-value. Thus you'll always get a fullscan.

如果有更好的条件，例如jobs.status = 331 and ack = 0(请注意我已经将!=更改为=的事实)，那么建议您加快此查询的速度:

If there were some better condition like jobs.status = 331 and ack = 0 (note on the fact that i've changed != to =) then it would be an advice to speed up this query:

1. 将查询分为2个，由UNION ALL
连接
2. 在一个查询LEFT JOIN中替换为INNER JOIN(在暗示wq.info is not NULL的查询中)

1. split the query into 2, joined by UNION ALL
2. replace in one query LEFT JOIN to INNER JOIN (in the one that implies that wq.info is not NULL)

这篇关于尝试使用LEFT OUTER JOIN优化MySQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！