mysql - 由于高索引基数导致查询缓慢

Question

我有以下 mysql 查询大约需要 55 秒才能完成

SELECT this_.id AS y0_ FROM event this_ 
  INNER JOIN member m1_ ON this_.member_id=m1_.id 
  INNER JOIN event_type et2_ ON this_.type_id=et2_.id 
WHERE m1_.submission_id=40646 AND et2_.name IN ('Salary') 
ORDER BY m1_.ni_number ASC, m1_.ident1 ASC, m1_.ident2 ASC, m1_.ident3 ASC, m1_.id ASC, et2_.name ASC LIMIT 15;

如果我删除“event_type”表的连接/位置/顺序，则查询将在 1 秒内运行。

因此，与我加入“event_type”表的情况明显相符，但在具有相似数据库卷的另一个数据库中进行类似查询运行绝对正常。所以我怀疑这个 1 数据库有问题。

“事件”表的“显示创建表”为:

Create Table: CREATE TABLE `event` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `version` bigint(20) NOT NULL,
  `data_size` bigint(20) DEFAULT NULL,
  `encoded_data` mediumblob,
  `last_updated` datetime NOT NULL,
  `member_id` bigint(20) NOT NULL,
  `parent_event_id` bigint(20) DEFAULT NULL,
  `status` varchar(255) DEFAULT NULL,
  `type_id` bigint(20) NOT NULL,
  `updated_by` varchar(255) NOT NULL,
  `failed_workflow_case` varchar(255) DEFAULT NULL,
  `failed_workflow_task` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `FK5C6729A2434DA80` (`member_id`),
  KEY `FK5C6729AE4E22C6E` (`type_id`),
  KEY `IND_parent_event_id` (`parent_event_id`),
  CONSTRAINT `FK5C6729A2434DA80` FOREIGN KEY (`member_id`) REFERENCES `member` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `FK5C6729AE4E22C6E` FOREIGN KEY (`type_id`) REFERENCES `event_type` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=46241198 DEFAULT CHARSET=latin1

查询的解释是:

+----+-------------+-------+------------+--------+-------------------------------------+-------------------+---------+--------------------------+------+----------+----------------------------------------------+
| id | select_type | table | partitions | type   | possible_keys                       | key               | key_len | ref                      | rows | filtered | Extra                                        |
+----+-------------+-------+------------+--------+-------------------------------------+-------------------+---------+--------------------------+------+----------+----------------------------------------------+
|  1 | SIMPLE      | et2_  | NULL       | ref    | PRIMARY,IND_name                    | IND_name          | 257     | const                    |    1 |   100.00 | Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | this_ | NULL       | ref    | FK5C6729A2434DA80,FK5C6729AE4E22C6E | FK5C6729AE4E22C6E | 8       | iconnect.et2_.id         | 3303 |   100.00 | NULL                                         |
|  1 | SIMPLE      | m1_   | NULL       | eq_ref | PRIMARY,IND_submission_id           | PRIMARY           | 8       | iconnect.this_.member_id |    1 |     5.00 | Using where                                  |
+----+-------------+-------+------------+--------+-------------------------------------+-------------------+---------+--------------------------+------+----------+----------------------------------------------+

“事件”表中的索引是:

+-------+------------+---------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name            | Seq_in_index | Column_name     | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+---------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| event |          0 | PRIMARY             |            1 | id              | A         |    14307622 |     NULL | NULL   |      | BTREE      |         |               |
| event |          1 | FK5C6729A2434DA80   |            1 | member_id       | A         |     4680601 |     NULL | NULL   |      | BTREE      |         |               |
| event |          1 | FK5C6729AE4E22C6E   |            1 | type_id         | A         |        4360 |     NULL | NULL   |      | BTREE      |         |               |
| event |          1 | IND_parent_event_id |            1 | parent_event_id | A         |      114404 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+---------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

这让我印象深刻的是:1. 为什么EXPLAIN要遍历3303行和FK5C6729AE4E22C6E索引？2. 为什么“event_type”表中只有 17 行时，FK5C6729AE4E22C6E 索引的基数为 4360？这种不正确的基数会影响查询优化器吗？

我已经对“event”和“event_type”进行了分析表，这没有什么区别。

有什么建议吗？

<小时/>

使用相同的数据从其他服务器执行计划(从转储文件加载):

+----+-------------+-------+------------+------+-------------------------------------+-------------------+---------+-----------------+-------+----------+----------------------------------------------+
| id | select_type | table | partitions | type | possible_keys                       | key               | key_len | ref             | rows  | filtered | Extra                                        |
+----+-------------+-------+------------+------+-------------------------------------+-------------------+---------+-----------------+-------+----------+----------------------------------------------+
|  1 | SIMPLE      | et2_  | NULL       | ALL  | PRIMARY                             | NULL              | NULL    | NULL            |    17 |    10.00 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | m1_   | NULL       | ref  | PRIMARY,IND_submission_id           | IND_submission_id | 8       | const           | 27992 |   100.00 | NULL                                         |
|  1 | SIMPLE      | this_ | NULL       | ref  | FK5C6729A2434DA80,FK5C6729AE4E22C6E | FK5C6729A2434DA80 | 8       | iconnect.m1_.id |     3 |    11.11 | Using where                                  |
+----+-------------+-------+------------+------+-------------------------------------+-------------------+---------+-----------------+-------+----------+----------------------------------------------+

Answer 1

将 innodb_stats_persistent_sample_pages 从 20 增加到 100，然后在事件/成员表上运行 ANALYZE TABLE 更改了索引和执行计划的基数，然后查询在 1 秒内运行。感谢 Solarflare 的建议。