在具有一对多关系的表上进行一对一左联接的最快方法（MySQL）

FuzzyTree 发表于 Dev

模糊树

我有两个表具有一对多的关系，我正在进行1：1左联接。该查询返回了正确的结果，但显示在我的慢速查询日志中（最多需要5秒）。有没有更好的方法来编写此查询？

select * from
tablea a left join tableb b 
  on a.tablea_id = b.tablea_id
  and b.tableb_id = (select max(tableb_id) from tableb b2 where b2.tablea_id = a.tablea_id)

即我想TableA离开加入TableB最大的一排tableb_id。

TableA
tablea_id
1
2

TableB
tableb_id, tablea_id, data
1, 1, x
2, 1, y

Expected Result
tablea_id, tableb_id, data
1, 2, y
2, null, null

TableA的索引为，tablea_id而TableB的索引为tablea_id,tableb_id。

解释输出

+----+--------------------+---------------+--------+-----------------+---------------+---------+----------------------+-------+-------------+
| id | select_type        | table         | type   | possible_keys   | key           | key_len | ref                  | rows  | Extra       |
+----+--------------------+---------------+--------+-----------------+---------------+---------+----------------------+-------+-------------+
|  1 | PRIMARY            | c             | index  | NULL            | department_id | 4       | NULL                 | 18966 | Using index |
|  1 | PRIMARY            | recent_cv_lut | eq_ref | PRIMARY,case_id | PRIMARY       | 4       | func                 |     1 |             |
|  2 | DEPENDENT SUBQUERY | cases_visits  | ref    | case_id         | case_id       | 4       | abcd_records_v2.c.id |     2 | Using index |
+----+--------------------+---------------+--------+-----------------+---------------+---------+----------------------+-------+-------------+

spencer7593

可能，相关的子查询正在针对tableb中的每一行执行。

（没有EXPLAIN的输出，我们实际上只是在猜测是否有合适的索引，以及MySQL是否正在使用它们。）

使用内联视图查询，一次获得每个tablea_id的最大tableb_id值，然后使用联接操作，可能会更有效。像这样：

SELECT a.*
     , b.*
  FROM tablea a
  LEFT
  JOIN ( SELECT n.tablea_id
              , MAX(n.tableb_id) AS max_tableb_id
           FROM tableb n
          GROUP
             BY n.tablea_id
       ) m
    ON m.tablea_id = a.tablea_id
  LEFT
  JOIN tableb b
    ON b.tablea_id = m.tablea_id
   AND b.tableb_id = m.max_tableb_id

这是另一种选择，但是不能保证速度会更快。实际上，这取决于我们没有任何信息的全部负载。（行数，基数，数据类型，可用索引等）

编辑

或者，我们可以在内联视图中进行tablea和tableb之间的联接。这可能会提高性能。（再次，这实际上取决于很多我们没有任何信息的事情。）

SELECT m.tablea_id
     , m.foo
     , b.*
  FROM ( SELECT a.tablea_id
              , a.foo
              , MAX(n.tableb_id) AS max_tableb_id
           FROM tablea a
           LEFT
           JOIN tableb n ON n.tablea_id = a.tablea_id
          GROUP
             BY a.tablea_id
       ) m
  LEFT
  JOIN tableb b
    ON b.tablea_id = m.tablea_id
   AND b.tableb_id = m.max_tableb_id