时间戳

首页 > 解决方案 > 应该使用哪个查询？从 MySQL Explain 推导出来

mysql - 应该使用哪个查询？从 MySQL Explain 推导出来

问题描述

O'reilly Optimizing SQL Statments Book 中的 Explaining MySQL Explain 一章，最后有这个问题。

以下是在父/子关系中检索孤立父记录的业务需求示例。此 SQL 查询可以用三种不同的方式编写。虽然输出产生相同的结果，但 QEP 显示了三种不同的路径。

mysql> EXPLAIN SELECT p.*
    -> FROM parent p
    -> WHERE p.id NOT IN (SELECT c.parent_id FROM child c)\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: p
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 160
        Extra: Using where
*************************** 2. row ***************************
           id: 2
  select_type: DEPENDENT SUBQUERY
        table: c
         type: index_subquery
possible_keys: parent_id
          key: parent_id
      key_len: 4
          ref: func
         rows: 1
        Extra: Using index
2 rows in set (0.00 sec)



mysql> EXPLAIN SELECT p.*
    -> FROM parent p
    -> LEFT JOIN child c ON p.id = c.parent_id
    -> WHERE c.child_id IS NULL\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: p
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 160
        Extra:
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: c
         type: ref
possible_keys: parent_id
          key: parent_id
      key_len: 4
          ref: test.p.id
         rows: 1
        Extra: Using where; Using index; Not exists
2 rows in set (0.00 sec)



mysql> EXPLAIN SELECT p.*
    -> FROM parent p
    -> WHERE NOT EXISTS
    -> SELECT parent_id FROM child c WHERE c.parent_id = p.id)\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: p
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 160
        Extra: Using where
*************************** 2. row ***************************
           id: 2
  select_type: DEPENDENT SUBQUERY
        table: c
         type: ref
possible_keys: parent_id
          key: parent_id
      key_len: 4
          ref: test.p.id
         rows: 1
        Extra: Using index
2 rows in set (0.00 sec)

哪个最好？随着时间的推移，数据增长会导致不同的 QEP 表现更好吗？

据我所知，书中或互联网上没有答案。

标签： mysqlinnodbexplain

解决方案

这取决于您使用的 MySQL 版本。在旧版本中，IN ( SELECT ...)表现非常糟糕。在最新版本中，它通常与其他变体一样好。此外，MariaDB 也有一些优化差异，可能在这方面。

EXISTS( SELECT 1 ... )也许是最清楚地表明意图。它也许一直（一旦它出现）很快。

NOT IN并且NOT EXISTS是另一种动物。

您的问题中可能会产生影响的某些内容： func和index_subquery。在类似的查询中，您可能看不到这些，而这种差异可能会导致性能差异。

或者，重复我自己：

“自 2009 年以来，优化器已经有了许多改进。

“致作者 (Quassnoi)：请重新运行您的测试，并指定它们正在运行的版本。还要注意 MySQL 和 MariaDB 可能会产生不同的结果。

“致读者：自己测试变种，不要盲目相信本博客的结论。”

推荐阅读