MySql：使用index_merge优化子查询 [英] MySql: Optimizing Subquery with index_merge

问题描述

我想列出所有用户以及简报订阅的状态。由于有人在订阅时事通讯时不需要成为用户，我会做类似的事情：

I want to lists all users and the status of the newsletter-subscribtion. Since someone doesn't need to be a user if subscribed to the newsletter, I'm doing something like:

SELECT user.id, user.email,
(SELECT newsletter.status FROM newsletter 
      WHERE newsletter.email=user.email OR newsletter.user = user.id) AS status
FROM user
WHERE...

索引是user.id，user.email，newsletter.email，newsletter.user 。

Indexes are user.id, user.email, newsletter.email, newsletter.user.

OR使查询变得异常缓慢。我在这里找到 Union as sub query MySQL ，你可以做一个索引合并将加快查询速度。但我不确定如何强制MySQL在我的情况下进行索引合并。有什么想法吗？

The OR makes the query incredible slow. I found here Union as sub query MySQL that you can do an "index merge" which will speed-up the query. But I'm not sure how to force MySQL to do an index merge in my case. Any ideas?

已添加：第二行说明不使用密钥。

DROP TABLE if exists user;
DROP TABLE if exists newsletter;

create table user (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
`email` varchar(255) DEFAULT NULL, INDEX email(email)
) ENGINE=InnoDB;

create table newsletter (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
`status` enum('subscribed','unsubscribed') DEFAULT NULL,
`email` varchar(255) DEFAULT NULL, INDEX email(email),
`user` int(10) unsigned DEFAULT NULL, INDEX user(user)
) ENGINE=InnoDB;

EXPLAIN SELECT user.id, user.email,
(SELECT newsletter.status FROM newsletter 
      WHERE newsletter.email=user.email OR newsletter.user = user.id) AS status
FROM user;

+----+--------------------+------------+------------+-------+---------------+-------+---------+------+------+----------+------------------------------------------------+
| id | select_type        | table      | partitions | type  | possible_keys | key   | key_len | ref  | rows | filtered | Extra                                          |
+----+--------------------+------------+------------+-------+---------------+-------+---------+------+------+----------+------------------------------------------------+
|  1 | PRIMARY            | user       | NULL       | index | NULL          | email | 768     | NULL |    1 |   100.00 | Using index                                    |
|  2 | DEPENDENT SUBQUERY | newsletter | NULL       | ALL   | email,user    | NULL  | NULL    | NULL |    1 |   100.00 | Range checked for each record (index map: 0x6) |
+----+--------------------+------------+------------+-------+---------------+-------+---------+------+------+----------+------------------------------------------------+

EXPLAIN SELECT user.id, user.email, newsletter.status
FROM user
LEFT JOIN newsletter ON (newsletter.email=user.email OR newsletter.user = user.id);

+----+-------------+------------+------------+-------+---------------+-------+---------+------+------+----------+------------------------------------------------+
| id | select_type | table      | partitions | type  | possible_keys | key   | key_len | ref  | rows | filtered | Extra                                          |
+----+-------------+------------+------------+-------+---------------+-------+---------+------+------+----------+------------------------------------------------+
|  1 | SIMPLE      | user       | NULL       | index | NULL          | email | 768     | NULL |    1 |   100.00 | Using index                                    |
|  1 | SIMPLE      | newsletter | NULL       | ALL   | email,user    | NULL  | NULL    | NULL |    1 |   100.00 | Range checked for each record (index map: 0x6) |
+----+-------------+------------+------------+-------+---------------+-------+---------+------+------+----------+------------------------------------------------+

推荐答案

除非您关闭index_merge ，如果MySQL认为它会带来好处，它将使用它。但是我发现它比人们预期的更少，即使它确实如此，它也不是很有用 - 查询仍然比以传统方式使用索引要慢得多。

Unless you have switched off index_merge, MySQL will use it if it thinks it will be of benefit. But I find it kicks in less often than one might expect, and even when it does, it's not very helpful — the query is still a lot slower than using an index in the conventional way.

这些类型的查询的典型解决方案是执行两个更简单查询的UNION。

The typical solution for these types of queries is to do a UNION of two simpler queries.

explain select u.id, u.email, n.status
 from user u left join newsletter n on u.email=n.email
 union
 select u.id, u.email, n.status
 from user u left join newsletter n on u.id=n.user;

+----+--------------+------------+-------+---------------+-------+---------+--------------+------+-----------------+
| id | select_type  | table      | type  | possible_keys | key   | key_len | ref          | rows | Extra           |
+----+--------------+------------+-------+---------------+-------+---------+--------------+------+-----------------+
|  1 | PRIMARY      | u          | index | NULL          | email | 403     | NULL         |    1 | Using index     |
|  1 | PRIMARY      | n          | ref   | email         | email | 403     | test.u.email |    1 | NULL            |
|  2 | UNION        | u          | index | NULL          | email | 403     | NULL         |    1 | Using index     |
|  2 | UNION        | n          | ref   | user          | user  | 5       | test.u.id    |    1 | NULL            |
| NULL | UNION RESULT | <union1,2> | ALL   | NULL          | NULL  | NULL    | NULL         | NULL | Using temporary |
+----+--------------+------------+-------+---------------+-------+---------+--------------+------+-----------------+

你最好修复您的数据，这样您只需加入用户ID，而不是电子邮件。

You'd be better off fixing your data so you only have to join on the user id, not on the email.

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