MySQL查询优化-加速|索引使用

Question

我有以下 MySQL 查询，需要一天多的时间才能执行:

SELECT SN,NUMBER FROM a

WHERE SN IN 
    (SELECT LOWER_SN FROM b
    WHERE HIGHER_ED  LIKE "%c1" AND LOWER_ED LIKE "%16")
AND ED LIKE "%16"

子查询运行需要 21 秒并返回 11035 行。我有以下索引:

SHOW INDEX FROM a

Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment

0 a 1 wob1 1 ED A 756095 None None BTREE
1 a 1 wob2 1 SN A 2268287 None None BTREE
2 a 1 wob3 1 ED A 756095 None None BTREE
3 a 1 wob3 2 SN A 9073150 None None BTREE
4 a 1 wob4 1 NUMBER A 18146301 None None YES BTREE
5 a 1 wob5 1 SN A 2268287 None None BTREE
6 a 1 wob5 2 NUMBER A 18146301 None None YES BTREE

EXPLAIN 给出:

# id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra

'1', 'PRIMARY', 'a', 'ALL', NULL, NULL, NULL, NULL, '18146301', 'Using where'

'2', 'DEPENDENT SUBQUERY', 'b', 'index_subquery', 'cfg2', 'cfg2', '47', 'func', '6', 'Using where'

为什么不使用索引？如何加快查询速度？

Answer 1

SELECT a.SN, a.NUMBER 
FROM a
WHERE EXISTS (
   SELECT * 
   FROM b
   WHERE b.LOWER_SN = a.SN
      AND b.HIGHER_ED  LIKE "%c1" 
      AND b.LOWER_ED LIKE "%16"
)
AND a.ED LIKE "%16"

出于多种原因，我通常不支持相关子查询；但由于您无法从索引中受益，因此这可能可行。

它将在后台有效地为每个a运行子查询。记录(或者如果查询优化器有帮助，可能只有与 a 条件匹配的 a.ED LIKE 记录)。通常，这会花费更多；但是EXISTS子查询的运行速度比之前的子查询快得多，因为它只需要计算 b具有特定 LOWER_SN 值的记录，并且 EXISTS 在找到单个匹配项而不是查找每个匹配项时为其提供“提前退出”。您应该索引 b.LOWER_SN以确保b每个 a 的记录尽快识别记录。

如果上面的版本仍然很慢，可能值得尝试一下:

SELECT a2.SN, a2.NUMBER 
FROM (SELECT a.SN, a.NUMBER FROM a WHERE AND a.ED LIKE "%16") AS a2
WHERE EXISTS (
   SELECT * 
   FROM b
   WHERE b.LOWER_SN = a2.SN
      AND b.HIGHER_ED  LIKE "%c1" 
      AND b.LOWER_ED LIKE "%16"
)

它基本上只是强制在 a.ED LIKE 上过滤表。如果优化器不这样做，则首先执行条件。