MySQL 查询在 JOIN 中查找没有匹配记录的项目非常慢

Question

我读过很多有关查询优化的问题，但没有一个对我有帮助。

作为设置，我有 3 个表，代表一个可以有零个或多个“类别”的“条目”。

> show create table entries;
CREATE TABLE `entries` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT
  ...
  `name` varchar(255),
  `updated_at` timestamp NOT NULL,
  ...
  PRIMARY KEY (`id`),
  KEY `name` (`name`)
) ENGINE=InnoDB

> show create table entry_categories;
CREATE TABLE `entry_categories` (
  `ent_name` varchar(255),
  `cat_id` int(11),
  PRIMARY KEY (`ent_name`,`cat_id`),
  KEY `names` (`ent_name`)
) ENGINE=InnoDB

(实际的“类别”表不会出现问题。)

在应用程序中编辑“条目”会在条目表中创建一个新行(就像 wiki 页面的历史记录一样)，具有相同的名称和较新的时间戳。我想看看有多少唯一命名的条目没有类别，这看起来非常简单:

SELECT COUNT(id)
FROM entries e
LEFT JOIN entry_categories c
ON e.name=c.ent_name
WHERE c.ent_name IS NUL
GROUP BY e.name;

在我的小型数据集上(总共大约 6000 个条目，大约有 4000 个名称，平均每个命名条目大约一个类别)，此查询需要超过 24 秒(!)。我也尝试过

SELECT COUNT(id)
FROM entries e
WHERE NOT EXISTS(
  SELECT ent_name
  FROM entry_categories c
  WHERE c.ent_name = e.name
)
GROUP BY e.name;

有类似的结果。这对我来说似乎真的非常慢，特别是考虑到在单个类别中查找条目

SELECT COUNT(*)
FROM entries e
JOIN (
  SELECT ent_name as name
  FROM entry_categories
  WHERE cat_id = 123
)c
USING (name)
GROUP BY name;

在相同数据上运行大约 120 毫秒。有没有更好的方法来查找表中没有在另一个表中至少有一个对应条目的记录？

<小时/>

我将尝试转录每个查询的 EXPLAIN 结果:

> EXPLAIN {no category query};
+----+-------------+-------+-------+---------------+-------+---------+------+------+----------------------------------------------+
| id | select_type | table | type  | possible_keys |  key  | key_len | ref  | rows |                    Extra                     |
+----+-------------+-------+-------+---------------+-------+---------+------+------+----------------------------------------------+
|  1 | SIMPLE      | e     | index | NULL          | name  |     767 | NULL | 6222 | Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | c     | index | PRIMARY,names | names |     767 | NULL | 6906 | Using where; using index; Not exists         |
+----+-------------+-------+-------+---------------+-------+---------+------+------+----------------------------------------------+

> EXPLAIN {single category query}
+----+-------------+------------+-------+---------------+-------+---------+------+--------------------------+---------------------------------+
| id | select_type |   table    | type  | possible_keys |  key  | key_len | ref  |           rows           |              Extra              |
+----+-------------+------------+-------+---------------+-------+---------+------+--------------------------+---------------------------------+
|  1 | PRIMARY     | <derived2> | ALL   | NULL          | NULL  | NULL    | NULL | 2850                     | Using temporary; Using filesort |
|  1 | PRIMARY     | e          | ref   | name          | 767   | c.name  | 1    | Using where; Using index |                                 |
|  2 | DERIVED     | c          | index | NULL          | names | NULL    | 6906 | Using where; Using index |                                 |
+----+-------------+------------+-------+---------------+-------+---------+------+--------------------------+---------------------------------+

Answer 1

尝试:

select name, sum(e) count_entries from 
(select name, 1 e, 0 c from entries 
 union all 
 select ent_name name, 0 e, 1 c from entry_categories) s 
group by name 
having sum(c) = 0