MySql:使用 index_merge 优化子查询

Question

我想列出所有用户和时事通讯订阅的状态。由于订阅时事通讯的人不需要成为用户，因此我正在做类似的事情:

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。

OR 使查询非常慢。我在这里找到Union as sub query MySQL你可以做一个“索引合并”，这将加速查询。但我不确定在我的例子中如何强制 MySQL 进行索引合并。有什么想法吗？

补充:解释的第二行不使用键。

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) |
+----+-------------+------------+------------+-------+---------------+-------+---------+------+------+----------+------------------------------------------------+

Answer 1

除非你有 switched off index_merge ，如果 MySQL 认为有好处，它就会使用它。但我发现它的启动频率低于人们的预期，即使它启动了，也不是很有帮助 — 查询仍然比以传统方式使用索引慢很多。

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

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，而不是电子邮件。