mysql - MySQL 中莫名其妙的缓慢查询

Question

给定这个结果集:

mysql> EXPLAIN SELECT c.cust_name, SUM(l.line_subtotal) FROM customer c
    -> JOIN slip s ON s.cust_id = c.cust_id
    -> JOIN line l ON l.slip_id = s.slip_id
    -> JOIN vendor v ON v.vend_id = l.vend_id WHERE v.vend_name = 'blahblah'
    -> GROUP BY c.cust_name
    -> HAVING SUM(l.line_subtotal) > 49999 
    -> ORDER BY c.cust_name;
+----+-------------+-------+--------+---------------------------------+---------------+---------+----------------------+------+----------------------------------------------+
| id | select_type | table | type   | possible_keys                   | key           | key_len | ref                  | rows | Extra                                        |
+----+-------------+-------+--------+---------------------------------+---------------+---------+----------------------+------+----------------------------------------------+
|  1 | SIMPLE      | v     | ref    | PRIMARY,idx_vend_name           | idx_vend_name | 12      | const                |    1 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | l     | ref    | idx_vend_id                     | idx_vend_id   | 4       | csv_import.v.vend_id |  446 |                                              |
|  1 | SIMPLE      | s     | eq_ref | PRIMARY,idx_cust_id,idx_slip_id | PRIMARY       | 4       | csv_import.l.slip_id |    1 |                                              |
|  1 | SIMPLE      | c     | eq_ref | PRIMARY,cIndex                  | PRIMARY       | 4       | csv_import.s.cust_id |    1 |                                              |
+----+-------------+-------+--------+---------------------------------+---------------+---------+----------------------+------+----------------------------------------------+
4 rows in set (0.04 sec)

我有点困惑为什么这个 EXPLAIN 语句引用的查询仍然需要大约一分钟的时间来执行。难道这个查询只需要搜索 449 行吗？任何人都知道什么可能会减慢它的速度？

Answer 1

我认为 having sum() 是万恶之源。这迫使 mysql 进行两次连接(从 customer 到 slip 然后到 line)以获得 sum< 的值。在此之后，它必须检索所有数据以通过 sum() 值正确过滤以获得有意义的结果。

它可能会针对以下内容进行优化，并可能获得更好的响应时间:

select  c.cust_name, 
        grouping.line_subtotal
  from customer c join 
  (select c.cust_id, 
          l.vend_id,
          sum(l.line_subtotal) as line_subtotal 
     from slip s join line l on s.slip_id = l.slip_id
   group by c.cust_id, l.vend_id) grouping 
      on c.cust_id = grouping.cust_id
   join vendor v on v.vend_id = grouping.vend_id 
 where v.vend_name = 'blablah'
   and grouping.line_subtotal > 499999
 group by c.cust_name
 order by c.cust_name;

换句话说，创建一个子选择，在进行真正的查询之前进行所有必要的分组。