mysql扫描行数的差异

Question

我有一个问题:

mysql> explain SELECT  *
    FROM  OTHERS_TINY_URL_TBL
    WHERE  LINK_TYPE = 'BITLY'
      AND  URL_SHORTNER_ID = '5434e3b9e4b03aa06f25da11'
      AND  MODIFIED_TM >= '2016-03-13 21:09:43'
      AND  MODIFIED_TM <= '2017-03-13 21:09:43'
      AND  POST_ID < 0
    ORDER BY  MODIFIED_TM DESC
    LIMIT  1000\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: OTHERS_TINY_URL_TBL
         type: range
possible_keys: XIE1_OTHERS_TINY_URL_TBL,XIE2_OTHERS_TINY_URL_TBL,XIE5_OTHERS_TINY_URL_TBL
          key: XIE5_OTHERS_TINY_URL_TBL
      key_len: 4
          ref: NULL
         rows: 47168
        Extra: Using index condition; Using where
1 row in set (0.00 sec)

ERROR: 
No query specified

我不明白为什么要扫描这么多行。似乎 ORDER BY 和 LIMIT 使它效率低下

没有 ORDER BY 和 LIMIT:

mysql> explain SELECT  *
    FROM  OTHERS_TINY_URL_TBL
    WHERE  LINK_TYPE = 'BITLY'
      AND  URL_SHORTNER_ID = '5434e3b9e4b03aa06f25da11'
      AND  MODIFIED_TM >= '2016-03-13 21:09:43'
      AND  MODIFIED_TM <= '2017-03-13 21:09:43'
      AND  POST_ID < 0\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: OTHERS_TINY_URL_TBL
         type: range
possible_keys: XIE1_OTHERS_TINY_URL_TBL,XIE2_OTHERS_TINY_URL_TBL,XIE5_OTHERS_TINY_URL_TBL
          key: XIE2_OTHERS_TINY_URL_TBL
      key_len: 9
          ref: NULL
         rows: 4950
        Extra: Using index condition; Using where
1 row in set (0.00 sec)

ERROR: 
No query specified

筛选集中只有 85 行:

mysql>  SELECT  count(*)
    FROM  OTHERS_TINY_URL_TBL
    WHERE  LINK_TYPE = 'BITLY'
      AND  URL_SHORTNER_ID = '5434e3b9e4b03aa06f25da11'
      AND  MODIFIED_TM >= '2016-03-13 21:09:43'
      AND  MODIFIED_TM <= '2017-03-13 21:09:43'
      AND  POST_ID < 0\G;
*************************** 1. row ***************************
count(*): 85
1 row in set (0.02 sec)

ERROR: 
No query specified

创建在较少行数上扫描的嵌套查询:

mysql> explain SELECT  *
    FROM  
    (
        SELECT  *
            from  OTHERS_TINY_URL_TBL
            where  URL_SHORTNER_ID = '5434e3b9e4b03aa06f25da11'
              AND  MODIFIED_TM >= '2016-03-13 21:09:43'
              AND  MODIFIED_TM <= '2017-03-13 21:09:43'
              AND  POST_ID < 0
    ) inner_t
    where  true
    ORDER BY  MODIFIED_TM DESC
    LIMIT  1000\G;
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: <derived2>
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4950
        Extra: Using filesort
*************************** 2. row ***************************
           id: 2
  select_type: DERIVED
        table: OTHERS_TINY_URL_TBL
         type: range
possible_keys: XIE2_OTHERS_TINY_URL_TBL,XIE5_OTHERS_TINY_URL_TBL
          key: XIE2_OTHERS_TINY_URL_TBL
      key_len: 9
          ref: NULL
         rows: 4950
        Extra: Using index condition; Using where
2 rows in set (0.00 sec)

ERROR: 
No query specified

为什么第一个查询效率低下？

Answer 1

首先，重要的是要注意 rows 中的数字只是一个估计值，基于一些统计数据，并且根据您的 MySQL 版本，在期间对索引进行一些随机查找规划阶段。有时它可能是完全错误的(并且，根据这些估计，MySQL 可能会选择一个较慢的执行计划)。这就是为什么，如果您了解 MySQL 不了解的数据，则可以通过例如优化查询来优化查询。强制一个 MySQL 不想选择的索引。

此外，MySQL 试图最小化的唯一相关值是整体执行时间。它可能与它必须读取的行数相关，但不一定(例如，如果您不必进行文件排序)。即使它通常可能会选择一个好的策略，它也可能取决于您的实际数据，如果它实际上是最快的。尝试例如limit 10 来模拟 4000 行而不是 85 行就可以满足您的搜索条件。这将完全取决于您的数据，而不是查询本身 - MySQL 必须在执行查询之前决定如何执行查询。

但一般来说，由于您的查询使用不同的索引，因此您的查询在 rows 中会有不同的数字。

第一个查询将使用 MODIFIED_TM 上的索引，遍历给定日期范围内的所有行。估计有 47168 行位于此范围内。对于所有这些行，它将检查其他列的其他条件。

您的第二个查询将使用不同列上的索引，可能是 LINK_TYPE。您必须添加索引定义以使其清晰，例如，它可以也是 URL_SHORTNER_ID，但对于以下内容，我假设索引(仅)在 LINK_TYPE 上。估计有 4950 行具有此 LINK_TYPE。这实际上通常是一个错误的估计，您可以通过例如SELECT count(*) FROM OTHERS_TINY_URL_TBL WHERE LINK_TYPE = 'BITLY'。然后必须检查这些行中的每一行是否符合其他条件。

MySQL 还不知道最终只会找到 85 行，并且检查 4950 行并对剩余的 85 行进行排序可能比检查已经按正确顺序排列的 47168 行更快(所以这样做不必事后订购，这是一个相对较慢的操作，MySQL 通常试图阻止)。此外，您可能会很幸运:这 47168 行中的前 1000 行可能已经必须更正 link_type、url_shortener_id 和 post_id，因此它将只需要检查 1000 行而不是检查 4950 行并进行排序。但这仅取决于您的数据。如果你知道一些 MySQL 不知道的数据，你应该使用优化器提示，例如强制使用不同的索引。或者欺骗 MySQL 例如通过使用您的第三个查询 - 虽然 MySQL 5.7 可能会欺骗您，因为它实际上应该优化子查询。

幸运的是，有更好的解决方案。您的第一个查询有一个完美的索引(这也将同时改进您的第二个查询):OTHERS_TINY_URL_TBL(URL_SHORTNER_ID, LINK_TYPE, MODIFIED_TM)。