mysql - mysql - 由于高索引基数导致查询缓慢
问题描述
我有以下 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;
如果我将 join/where/order 删除到“event_type”表,则查询将在 1 秒内运行。
因此,我加入 'event_type' 表显然有些问题,但是在另一个具有类似数据库卷的数据库中的类似查询运行得非常好。所以我怀疑这个 1 数据库有问题。
'event'表的'show create table'是:
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 |
+----+-------------+-------+------------+------+-------------------------------------+-------------------+---------+-----------------+-------+----------+----------------------------------------------+
解决方案
将 innodb_stats_persistent_sample_pages 从 20 增加到 100,然后在事件/成员表上运行 ANALYZE TABLE 更改了索引的基数和执行计划,然后查询在 1 秒内运行。感谢 Solarflare 的建议。
推荐阅读
- r - 连接与列值包含空格的位置不匹配?
- jquery - FullCalendar - 背景全天事件丢失
- python - python:使用get_account()的Binance错误
- c - C中的函数getopt与char * const *指针
- javascript - 如何配置多个 env 文件以在 WebDriver IO 中为多个用户角色运行?
- spotbugs - 使用 Spotbug 排除路径/文件夹
- python - 如果两个 csv 文件上的 4 列都匹配,则将列的数据移动到另一个 csv 文件
- google-chrome - Chrome v91,AADSTS50196:服务器终止操作,因为它遇到客户端请求循环。请联系您的应用供应商
- javascript - 传单在显示位置重复时提供无效的 Lng/Lat 坐标
- python - 使用 selenium python 循环遍历元素时遇到问题