MySQL多态连接条件与OR不使用索引

Question

我在 MySQL 5.6.17(针对 Rails 应用程序)中有表 departments、employees 和 emails。每个部门都有很多员工，部门和员工都有很多电子邮件。我想按照发送给整个部门和部门内个别员工的电子邮件数量对部门进行排序。我的尝试:

SELECT departments.*, COUNT(DISTINCT employees.id) AS employees_count, COUNT(DISTINCT emails.id) AS emails_count
    FROM departments
    LEFT OUTER JOIN employees 
        ON employees.department_id = departments.id AND employees.is_employed = true
    LEFT OUTER JOIN emails 
        ON (emails.emailable_id = departments.id AND emails.emailable_type = 'department')
            OR (emails.emailable_id = employees.id AND emails.emailable_type = 'employee')
    GROUP BY departments.id 
    ORDER BY emails_count DESC 
    LIMIT 20;

遗憾的是，此查询需要 3 分钟多才能完成。由于此查询将在 Web 界面中使用，因此这不是一个可行的时间范围。 EXPLAIN 给出:

+----+-------------+-------------+-------+-------------------------------------------------+----------------------------------+---------+-------------------------------+-------+------------------------------------------------+
| id | select_type | table       | type  | possible_keys                                   | key                              | key_len | ref                           | rows  | Extra                                          |
+----+-------------+-------------+-------+-------------------------------------------------+----------------------------------+---------+-------------------------------+-------+------------------------------------------------+
|  1 | SIMPLE      | departments | index | PRIMARY                                         | PRIMARY                          | 4       | NULL                          | 37468 | Using where; Using temporary; Using filesort   |
|  1 | SIMPLE      | employees   | ref   | index_employees_on_department_id                | index_employees_on_department_id | 5       | development_db.departments.id |     5 | Using where                                    |
|  1 | SIMPLE      | emails      | ALL   | index_emails_on_emailable_id_and_emailable_type | NULL                             | NULL    | NULL                          | 10278 | Range checked for each record (index map: 0x2) |
+----+-------------+-------------+-------+-------------------------------------------------+----------------------------------+---------+-------------------------------+-------+------------------------------------------------+

那么，emails 上的索引就没有被使用。当我只向部门加入电子邮件或仅向员工加入电子邮件，但不能同时加入两者时，可以成功使用此索引。

这是为什么呢？对此我能做什么？有没有更有效的方法来查询所需的数据？

Answer 1

在连接之前先进行聚合可能会有所帮助:

SELECT d.*, e.employees_count, em.emails_count
FROM d LEFT OUTER JOIN
     (SELECT e.department_id, count(*) as employees_count
      FROM employees e
      WHERE e.is_employed = true
      GROUP BY e.department_id
     ) e
     ON e.department_id = d.id LEFT OUTER JOIN
     (SELECT department_id, count(distinct id) as emails_count
      FROM (SELECT em.emailable_id as department_id, em.id
            FROM emails em
            WHERE em.emailable_type = 'department'
            UNION ALL
            SELECT e.department_id, em.id
            FROM emails em JOIN
                 employees e
                 ON em.emailable_id = e.id AND em.emailable_type = 'employee'
           ) ee
      GROUP BY department_id
     ) em
     ON em.department_id = d.id LEFT OUTER JOIN
ORDER BY emails_count DESC 
LIMIT 20;

您还需要在 emails(emailable_id, emailable_type, id) 和 emails(emailable_type, emailable_id, id) 上建立索引。