具有多个 select 语句的 MySQL 右外连接非常慢

Question

我有 3 个表，如下所示:

解析表(table1)

+----+---------+-----+------------+--------+
| id | created | num | resolution | score  |
+----+---------+-----+------------+--------+

作者表(table2)

+----+---------+--------+------+------+--------+
| id | created | author | file | tags | res_id |
+----+---------+--------+------+------+--------+

状态表(table3)

+----+------+-------------+----------+--------+-----------+
| id | name | description | category | status | author_id |
+----+------+-------------+----------+--------+-----------+

我正在使用 2 个 select 语句根据它们的 ID 从同一个表中获取记录，如下所示:

语句 1(分辨率 num = 1357)

select * 
from table1, table2, table3 
where table1.num=1357 
  and table1.id=table2.res_id
  and table2.id=table3.author_id;

语句 2(分辨率 num = 1358)

select * 
from table1, table2, table3 
where table1.num=1358 
  and table1.id=table2.res_id 
  and table2.id=table3.author_id;

注意:每个语句返回 100,000 多条记录

我使用右外连接来获取 statement2 中不存在于 statement1 结果中的记录，如下所示:

select * from (
  select * 
  from table1, table2, table3 
  where table1.num=1357 
    and table1.id=table2.res_id 
    and table2.id=table3.author_id
) as tab1
right outer join (
  select * 
  from table1, table2, table3 
  where table1.num=1358 
    and table1.id=table2.res_id 
    and table2.id=table3.author_id
) as tab2 on tab1.name=tab2.name
         and tab1.category=tab2.category
         and tab1.author=tab2.author
where tab1.name is NULL
  and tab1.category is NULL
  and tab1.author is NULL

这适用于少量记录，但在我的情况下，总共有 200,000 条记录，通常需要 11 分钟才能返回所需结果。

如何优化查询以更快地获取结果？

附言:

对table1.num、table2.res_id、table3.author_id应用索引后

show create table table1;

CREATE TABLE `table1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `created` datetime NOT NULL,
  `num` int(11) NOT NULL,
  `resolution` varchar(100) NOT NULL,
  `score` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `num` (`num`)
  KEY `num_index` (`num`)
) ENGINE=InnoDB AUTO_INCREMENT=2552 DEFAULT CHARSET=latin1

show create table table2;

CREATE TABLE `table2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `created` datetime NOT NULL,
  `author` varchar(200) NOT NULL,
  `file` varchar(200) NOT NULL,
  `tags` varchar(200) DEFAULT NULL,
  `res_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `table2_res_id_48009073_fk_table1_id` (`res_id`),
  KEY `res_id_index` (`res_id`),
  CONSTRAINT `table2_res_id_48009073_fk_table1_id ` FOREIGN KEY (`res_id`) REFERENCES `table1` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=686972 DEFAULT CHARSET=latin1

show create table table3;

CREATE TABLE `table3` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(200) NOT NULL,
  `description` longtext NOT NULL,
  `category` varchar(200) NOT NULL,
  `status` varchar(20) NOT NULL,
  `author_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `table3_ffe73c23` (`author_id`),
  KEY `author_id_index` (`author_id`)
  CONSTRAINT `table3_id_e47d088c_fk_table2_id` FOREIGN KEY (`author_id`) REFERENCES `table2` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=12042452 DEFAULT CHARSET=latin1

explain <"full select query">

+------+----------------+-----------+---------+--------------------------------------------------------------------------------------+-------------------------------------------------------------+--------------+--------------+-------+------------------------------------+
| id     | select_type | table      | type        | possible_keys                                                                                | key                                                                    | key_len     | ref             | rows | Extra                                  |
+------+----------------+-----------+---------+--------------------------------------------------------------------------------------+-------------------------------------------------------------+--------------+--------------+-------+------------------------------------+
|    1   | SIMPLE      | table1   | const   | PRIMARY,num,num_index                                                             | num                                                                  | 4               | const        | 1       | Using index                         |
|    1   | SIMPLE      | table2   | ref        | PRIMARY,table2_res_id_48009073_fk_table1_id,res_id_index.  | table2_res_id_48009073_fk_table1_id.           | 4               | const         | 7106 |                                            |
|    1.  | SIMPLE      | table3   | ref       | table3_ffe73c23,author_id_index                                                  | table3_ffe73c23                                                | 4               | t2.id           | 9       |                                            |
|    1   | SIMPLE      | table1   | const    | PRIMARY,num,num_index                                                           | num                                                                   | 4               | const         | 1       | Using where; Using index  |
|    1   | SIMPLE      | table2   | ref        | PRIMARY, table2_res_id_48009073_fk_table1_id,res_id_index  | table2_res_id_48009073_fk_table1_id.           | 4               | t1.id.          | 213   | Using where                       |
|    1   | SIMPLE      | table3   | ref        | table3_ffe73c23, author_id_index                                                 | table3_ffe73c23                                                | 4               | t2.id           | 9       | Using where; Not exists     |
+------+----------------+----------+----------+-------------------------------------------------------------------------------------+-------------------------------------------------------------+---------------+---------------+-------+-----------------------------------+

Answer 1

尝试以下重写的查询:

SELECT
table1.*,
table2.*,
table3.*
FROM table1
INNER JOIN table2
ON table2.res_id = table1.id
INNER JOIN table3
ON table3.author_id = table2.id
LEFT OUTER JOIN (
  SELECT
  table3.name,
  table3.category,
  table2.author
  FROM table1
  INNER JOIN table2
  ON table2.res_id = table1.id
  INNER JOIN table3
  ON table3.author_id = table2.id
  WHERE table1.num = 1357
) tab1
ON tab1.name = table3.name
AND tab1.category = table3.category
AND tab1.author = table2.author
WHERE table1.num = 1358
AND tab1.name IS NULL

单独的查询可能会显示出一些改进。如果改善不大，请尝试以下新指标:

table2: INDEX(res_id, author)
table3: INDEX(author_id, category, name)

然后报告结果。

注意:尝试查询时，确保运行两次并丢弃第一个结果，以便填充 InnoDB 缓冲区缓存。