带有左连接的mysql奇怪的性能异常

Question

我有以下简单的左连接查询:

SELECT SQL_NO_CACHE * FROM helyek h 
    LEFT JOIN eladok e ON e.elado_id = h.elado_id 
    LEFT JOIN eladok_rel_szakmak ersz ON ersz.elado_id = e.elado_id 
    LEFT JOIN szakmak sz ON sz.szakma_id = ersz.szakma_id 
WHERE  h.hely_nev = 'xy'
    OR h.hely_telepules = 'xy'

每个 _id 和 h.hely_nev、h.hely_telepules 都被编入索引，并且运行时间不到 0.0008 秒。

但是如果我再添加一个 where 子句(或 sz.szakma_id = 1)，速度会下降到 0.7 秒!这真的很慢。

SELECT SQL_NO_CACHE * FROM helyek h 
    LEFT JOIN eladok e ON e.elado_id = h.elado_id 
    LEFT JOIN eladok_rel_szakmak ersz ON ersz.elado_id = e.elado_id 
    LEFT JOIN szakmak sz ON sz.szakma_id = ersz.szakma_id 
WHERE  h.hely_nev = 'xy'
    OR h.hely_telepules = 'xy'
    OR sz.szakma_id = 1

helyek、eladok、eladok_rel_szakmak 中有 50k 行，而 szakmak 中只有 30 行。我需要加入所有表，因为我需要所有表中的一些字段。

问题是，我怎样才能优化第二个查询以获得更好的性能？

这里是解释:

这是快速查询:

+----+-------------+-------+-------------+------------------------------+------------------------------+---------+----------------+------+--------------------------------------------------------+
| id | select_type | table |    type     |        possible_keys         |             key              | key_len |      ref       | rows |                         Extra                          |
+----+-------------+-------+-------------+------------------------------+------------------------------+---------+----------------+------+--------------------------------------------------------+
|  1 | SIMPLE      | h     | index_merge | idxhelynev,idxhely_telepules | idxhelynev,idxhely_telepules | 482,482 | NULL           |    2 | Using union(idxhelynev,idxhely_telepules); Using where |
|  1 | SIMPLE      | e     | eq_ref      | PRIMARY                      | PRIMARY                      | 4       | h.elado_id     |    1 |                                                        |
|  1 | SIMPLE      | ersz  | ref         | elado_id                     | elado_id                     | 4       | e.elado_id     |    1 |                                                        |
|  1 | SIMPLE      | sz    | eq_ref      | PRIMARY                      | PRIMARY                      | 4       | ersz.szakma_id |    1 |                                                        |
+----+-------------+-------+-------------+------------------------------+------------------------------+---------+----------------+------+--------------------------------------------------------+    

这是缓慢的:

+----+-------------+-------+--------+------------------------------+----------+---------+----------------+-------------+-------------+
| id | select_type | table |  type  |        possible_keys         |   key    | key_len |      ref       |    rows     |    Extra    |
+----+-------------+-------+--------+------------------------------+----------+---------+----------------+-------------+-------------+
|  1 | SIMPLE      | h     | ALL    | idxhelynev,idxhely_telepules | NULL     | NULL    | NULL           | 54326       |             |
|  1 | SIMPLE      | e     | eq_ref | PRIMARY                      | PRIMARY  | 4       | h.elado_id     |           1 |             |
|  1 | SIMPLE      | ersz  | ref    | elado_id                     | elado_id | 4       | e.elado_id     |           1 |             |
|  1 | SIMPLE      | sz    | eq_ref | PRIMARY                      | PRIMARY  | 4       | ersz.szakma_id |           1 | Using where |
+----+-------------+-------+--------+------------------------------+----------+---------+----------------+-------------+-------------+ 

我看到第二个查询不能使用任何键，但我不知道为什么(sz.szakma_id 字段上有一个索引)

编辑:我忘了说:我需要使用多个子句组。像这样:

(h.hely_nev = 'x' OR h.hely_telepules = 'x' OR sz.szakma_id = x)
AND
(h.hely_nev = 'y' OR h.hely_telepules = 'y' OR sz.szakma_id = y)
AND
(h.hely_nev = 'z' OR h.hely_telepules = 'z' OR sz.szakma_id = z)

这就是为什么我不能使用两个单独的查询。目标是在 h.hely_nev、h.hely_telepules 和 sz.szakma_id 字段中搜索用户在搜索表单中输入的每个单词。例如，如果用户输入“x y z”，我需要选择 h.hely_nev 等于 x 或 y 或 z 且 h.hely_telepules 等于 x 或 y 或 z 等的每条记录。

Answer 1

归根结底，这是因为在第一种情况下，查询优化器能够使用 helyek 上的索引来确定只有两个可能的候选行。

当您在 szakmak 上添加 OR 条件时，您禁止在 helvek 上使用索引来缩小潜在结果集的范围。您可能最好对两个单独查询的结果进行 UNION，其中一个具有条件:

WHERE  h.hely_nev = 'xy'
OR h.hely_telepules = 'xy'

还有一个有条件

WHERE sz.szakma_id = 1

所以像这样:

SELECT SQL_NO_CACHE * FROM helyek h 
    LEFT JOIN eladok e ON e.elado_id = h.elado_id 
    LEFT JOIN eladok_rel_szakmak ersz ON ersz.elado_id = e.elado_id 
    LEFT JOIN szakmak sz ON sz.szakma_id = ersz.szakma_id 
WHERE  h.hely_nev = 'xy'
    OR h.hely_telepules = 'xy'
UNION DISTINCT
SELECT SQL_NO_CACHE * FROM helyek h 
    LEFT JOIN eladok e ON e.elado_id = h.elado_id 
    LEFT JOIN eladok_rel_szakmak ersz ON ersz.elado_id = e.elado_id 
    LEFT JOIN szakmak sz ON sz.szakma_id = ersz.szakma_id 
WHERE sz.szakma_id = 1

如果您认为 szakmak 表的基数小于 helyek

所以你像这样翻转查询:

SELECT SQL_NO_CACHE *
FROM
    szakmak sz 
    RIGHT JOIN eladok_rel_szakmak ersz ON sz.szakma_id = ersz.szakma_id
    RIGHT JOIN eladok e ON ersz.elado_id = e.elado_id
    RIGHT JOIN helyek h ON e.elado_id = h.elado_id 
WHERE  h.hely_nev = 'xy'
    OR h.hely_telepules = 'xy'
    OR sz.szakma_id = 1

这会改变表的依赖顺序。我不确定哪个最适合您。

在此处查看 MySQL 文档中有关 LEFT/RIGHT JOIN 优化的更多信息:

http://dev.mysql.com/doc/refman/5.6/en/left-join-optimization.html