MySQL 5.7 中忽略 INNODB 表计数查询的索引

Question

我们有一个运行 MySQL 5.1 的旧数据库。我们现在想要将其迁移到 MySQL 5.7，但一些运行正常的查询突然变得非常非常慢(慢了 60 倍或更多)。

相关 INNODB 表 (EVENT) 在其他列中包含 COMPANY_ID(COMPANY 表的外键)和 DATETIME 类型的 EVENT_DATETIME。 COMPANY_ID、EVENT_DATETIME 有一个索引，为了测试，我添加了一个 EVENT_DATETIME、COMPANY_ID。目前基本上所有事件的 COMPANY_ID 都是 1，但这将会改变。

我们有一个计数查询来查询去年的事件数量:

select count(distinct this_.EVENT_ID) as y0_ from EVENT this_
       where this_.EVENT_DATETIME>='2018-10-22 00:00:00'
         and this_.EVENT_DATETIME<='2019-11-21 00:00:00'
         and this_.COMPANY_ID = 1;

结果约为 1,000,000 行，过去大约需要 1.5 秒，现在最多需要 100 秒。虽然 MySQL 5.1 上的查询使用 COMPANY_ID 和 EVENT_DATETIME 上的索引，但 MySQL 5.7 上的索引将被忽略。看起来，如果 MySQL 发现它必须解析太多行，它就会放弃索引，即使它会有所帮助。如果我将窗口缩小到例如10月MySQL 5.7再次使用索引。

因此在 MySQL 5.1 上使用索引 COMPANY_ID,EVENT_DATETIME在 MySQL 上，它仅使用 COMPANY_ID 的外键索引。

如果我运行查询时 COMPANY_ID 上没有 where

select count(distinct this_.EVENT_ID) as y0_ from EVENT this_ 
       where this_.EVENT_DATETIME>='2018-10-22 00:00:00'
         and this_.EVENT_DATETIME<='2019-11-21 00:00:00';

查询速度要快得多。

有没有办法强制MySQL 5.7使用某个索引？

如果我将查询重写为:

select count(distinct this_.EVENT_ID) as y0_ from EVENT this_
     where this_.EVENT_DATETIME>='2018-10-22 00:00:00'
       and this_.EVENT_DATETIME<='2019-11-21 00:00:00'
     GROUP BY COMPANY_ID HAVING COMPANY_ID = 1;

恢复到大约 1 到 1.5 秒。问题是我们可能有多个这样的查询，并且这些查询是由不支持 HAVING 的 Hibernate Criterias 生成的，所以我的解决方法在现实生活中不起作用。

更新:MySQL 5.7解释 12 个月的查询(40 秒内 1050757 行)

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "673838.60"
    },
    "table": {
      "table_name": "this_",
      "access_type": "ref",
      "possible_keys": [
      "PRIMARY",
      "FK_EVENT_COMPANY",
      "IX_REFERENCE",
      "IX_DATE_TIME",
      "EVENT_DATETIME",
      "IDX_CE_COMPANY_TYPE",
      "IDX_CE_COMPANY_DATE",
      "IDX_CE_DATE_COMPANY"
      ],
      "key": "FK_EVENT_COMPANY",
      "used_key_parts": [
        "COMPANY_ID"
      ],
      "key_length": "4",
      "ref": [
        "const"
      ],
      "rows_examined_per_scan": 2698153,
      "rows_produced_per_join": 1135826,
      "filtered": "42.10",
      "cost_info": {
        "read_cost": "134208.00",
        "eval_cost": "227165.40",
        "prefix_cost": "673838.60",
        "data_read_per_join": "1G"
      },
      "used_columns": [
        "EVENT_ID",
        "COMPANY_ID",
        "EVENT_DATETIME"
      ],
      "attached_condition": "((`test`.`this_`.`EVENT_DATETIME` >= '2018-10-22 00:00:00') and (`test`.`this_`.`EVENT_DATETIME` <= '2019-11-21 00:00:00'))"
    }
  }
}

解释 10 个月的查询

   {
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "634047.16"
    },
    "table": {
      "table_name": "this_",
      "access_type": "range",
      "possible_keys": [
        "PRIMARY",
        "FK_EVENT_COMPANY",
        "IX_REFERENCE",
        "IX_DATE_TIME",
        "EVENT_DATETIME",
        "IDX_CE_COMPANY_TYPE",
        "IDX_CE_COMPANY_DATE",
        "IDX_CE_DATE_COMPANY"
      ],
      "key": "IDX_CE_DATE_COMPANY",
      "used_key_parts": [
        "EVENT_DATETIME"
      ],
      "key_length": "9",
      "rows_examined_per_scan": 1578860,
      "rows_produced_per_join": 789430,
      "filtered": "50.00",
      "using_index": true,
      "cost_info": {
        "read_cost": "476161.16",
        "eval_cost": "157886.00",
        "prefix_cost": "634047.16",
        "data_read_per_join": "1G"
      },
      "used_columns": [
        "EVENT_ID",
        "COMPANY_ID",
        "EVENT_DATETIME"
      ],
      "attached_condition": "((`test`.`this_`.`COMPANY_ID` = 1) and (`test`.`this_`.`EVENT_DATETIME` >= '2019-01-22 00:00:00') and (`test`.`this_`.`EVENT_DATETIME` <= '2019-11-21 00:00:00'))"
    }
  }
}

有趣的是，第一个 12 个月(慢速)查询在 Attached_condition 中不显示 COMPANY_ID，而第二个 10 个月查询 Attached_condition 会检查 COMPANY_ID。

按照建议分析表并没有改变任何东西。

更新2:MySQL 5.1 的解释(不支持 JSON 格式)需要 1.3 秒

1    SIMPLE         this_  range   FK_EVENT_COMPANY,IX_DATE_TIME,EVENT_DATETIME,IDX_CE_COMPANY_TYPE,IDX_CE_COMPANY_DATE    IDX_CE_COMPANY_DATE 16      NULL    2018704   Using where; Using index

Answer 1

查询规划器可能根据可用的统计信息做出错误的决定。您可以尝试运行 ANALYZE ( https://dev.mysql.com/doc/refman/5.6/en/analyze-table.html ) 来重建统计数据并向规划者提供更好的数字。请注意，ANALYZE 在运行时会阻塞表(速度很快)。

更新

阅读MySQL文档，我发现了这样一段话:

Prior to MySQL 5.7.18, InnoDB processes SELECT COUNT(*) statements by scanning the clustered index. As of MySQL 5.7.18, InnoDB processes SELECT COUNT(*) statements by traversing the smallest available secondary index unless an index or optimizer hint directs the optimizer to use a different index. If a secondary index is not present, the clustered index is scanned.

引用号:https://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html#function_count

这意味着计数行为在您使用的版本上完全改变了。它可以解释这种差异。