MySQL 8.0.33简单查询超时问题（已确认系官方BUG）

此前MySQL Bug支持团队称无问题，还指责我寻求“免费支持”，我坚持立场，指出他们分析错误，并多次正式要求经理复查该问题。约一周后，一位资深开发人员通过工单联系我，索要简化数据库用于复现问题，我提供了仅包含person_main和person_history_work表的数据库。他成功复现问题，确认这是MySQL的BUG，并表示当前解决方案是将optimizer_switch参数设置为semijoin=OFF，直至官方修复。该BUG自8.0.11版本起存在，官方BUG编号为111669。

我遇到了一个棘手的问题：以下查询在本地机器和AWS环境中均超时，但每个子查询单独执行都正常。第一个子查询耗时55ms，返回24000条记录；第二个子查询耗时42ms，返回4800条记录。我怀疑是否存在被忽略的服务器配置问题？

person_main表已完全建立索引，仅包含115,212条记录。查询详情如下：

-- 耗时20ms，返回4800条记录
SELECT phw.person_main_ref_id AS ID
FROM person_history_work phw
WHERE MATCH(phw.work_summary) AGAINST('finance');

-- 耗时20ms，返回24000条记录
SELECT phw.person_main_ref_id AS ID
FROM person_history_work phw
WHERE MATCH(phw.work_title) AGAINST('software');

-- 耗时700ms，返回14000条记录
SELECT pm.ID
FROM person_main pm      
WHERE
pm.ID IN  
(
    -- 返回49838条记录
    SELECT phw.person_main_ref_id AS ID
    FROM person_history_work phw
    WHERE phw.ID < 50000
)
AND
pm.ID IN
(
    -- 返回299679条记录
    SELECT phw.person_main_ref_id AS ID
    FROM person_history_work phw
    WHERE phw.ID > 10000
);

-- 超时（超60000ms），最多应返回4800条记录
SELECT pm.ID
FROM person_main pm      
WHERE
pm.ID IN  
(
    -- 返回4800条记录
    SELECT phw.person_main_ref_id AS ID
    FROM person_history_work phw
    WHERE MATCH(phw.work_summary) AGAINST('finance')
)
AND
pm.ID IN
(
    -- 返回24000条记录
    SELECT phw.person_main_ref_id AS ID
    FROM person_history_work phw
    WHERE MATCH(phw.work_title) AGAINST('software')
)

若将上述查询中的MATCH AGAINST替换为LIKE，仅耗时2000ms：

-- 原查询超时（超60000ms），最多应返回4800条记录
SELECT pm.ID
FROM person_main pm      
WHERE
pm.ID IN  
(
    SELECT phw.person_main_ref_id AS ID
    FROM person_history_work phw
    WHERE phw.work_summary LIKE '%finance%'
)
AND
pm.ID IN
(
    SELECT phw.person_main_ref_id AS ID
    FROM person_history_work phw
    WHERE phw.work_title LIKE '%software%'
)
;

如果仅执行其中一个子查询，耗时仅60ms；但使用AND连接两个子查询时就会超时，顺序不影响结果。这完全不符合逻辑，因为只是比较ID是否在结果集中。若改为OR连接，查询800ms即可完成。

以下是EXPLAIN结果：

请问这是什么原因？

以下是person_main和person_history_work表的创建语句：

CREATE TABLE `person_main` (
  `ID` int NOT NULL AUTO_INCREMENT,
  `date_created` datetime NOT NULL DEFAULT (now()),
  `date_updated` datetime NOT NULL DEFAULT (now()) ON UPDATE CURRENT_TIMESTAMP,
  `name_first` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL,
  `name_middle` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `name_last` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL,
  `name_nick` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `name_full` text COLLATE utf8mb4_unicode_ci GENERATED ALWAYS AS (trim(concat(if((`name_nick` is null),_utf8mb4'',concat(`name_nick`,_utf8mb4' ')),if((`name_first` is null),_utf8mb4'',concat(`name_first`,_utf8mb4' ')),if((`name_middle` is null),_utf8mb4'',concat(`name_middle`,_utf8mb4' ')),if((`name_last` is null),_utf8mb4'',`name_last`)))) STORED NOT NULL,
  `date_birth` date DEFAULT NULL,
  `department_type_gc` smallint DEFAULT NULL,
  `designation` text COLLATE utf8mb4_unicode_ci,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `ID_UNIQUE` (`ID`),
  KEY `IDX_person_main_name_first` (`name_first`),
  KEY `IDX_person_main_name_middle` (`name_middle`),
  KEY `IDX_person_main_name_last` (`name_last`),
  KEY `IDX_person_main_name_nick` (`name_nick`),
  KEY `IDX_person_main_date_created` (`date_created`),
  KEY `IDX_person_main_date_updated` (`date_updated`),
  KEY `IDX_person_main_date_birth` (`date_birth`),
  KEY `IDX_person_main_department_type_gc` (`department_type_gc`),
  KEY `IDX_person_main_designation` (`designation`(128)),
  FULLTEXT KEY `IDX_person_main_name_full` (`name_full`)
) ENGINE=InnoDB AUTO_INCREMENT=177557 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci AVG_ROW_LENGTH=55

CREATE TABLE `person_history_work` (
  `ID` int NOT NULL AUTO_INCREMENT,
  `person_main_ref_id` int NOT NULL COMMENT '关联person_main表的外键，标记所属用户',
  `internal_company_name` text COLLATE utf8mb4_unicode_ci,
  `company_main_ref_id` int DEFAULT NULL,
  `work_title` text COLLATE utf8mb4_unicode_ci,
  `work_title_scrubbed` text COLLATE utf8mb4_unicode_ci GENERATED ALWAYS AS (regexp_replace(`work_title`,_utf8mb4'[^0-9A-Za-z]',_utf8mb4'')) STORED,
  `date_start` date DEFAULT NULL,
  `date_end` date DEFAULT NULL,
  `work_summary` text COLLATE utf8mb4_unicode_ci,
  `person_email_ref_id` int DEFAULT NULL,
  `person_phone_ref_id` int DEFAULT NULL,
  `person_address_ref_id` int DEFAULT NULL,
  `compensation_main_ref_id` int DEFAULT NULL,
  `history_work_details` json DEFAULT NULL,
  `processed_sit` tinyint NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`),
  UNIQUE KEY `UK_person_history_work_ID` (`ID`),
  KEY `fk_people_main_id_idx` (`person_main_ref_id`),
  KEY `IDX_person_history_work_company_main_ref_id` (`company_main_ref_id`),
  KEY `IDX_person_history_work_date_start` (`date_start`),
  KEY `IDX_person_history_work_date_end` (`date_end`),
  KEY `FK_person_history_work_compensation_main_ref_id` (`compensation_main_ref_id`),
  KEY `FK_person_history_work_person_email_ref_id` (`person_email_ref_id`),
  KEY `FK_person_history_work_person_phone_ref_id` (`person_phone_ref_id`),
  KEY `IDX_person_history_work_work_title_scrubbed` (`work_title_scrubbed`(128)),
  KEY `IDX_person_history_work_processed_sit` (`processed_sit`),
  FULLTEXT KEY `IDX_person_history_work_work_summary` (`work_summary`),
  FULLTEXT KEY `IDX_person_history_work_internal_company_name` (`internal_company_name`),
  FULLTEXT KEY `IDX_person_history_work_work_title` (`work_title`),
  CONSTRAINT `FK_person_history_work_compensation_main_ref_id` FOREIGN KEY (`compensation_main_ref_id`) REFERENCES `compensation_main` (`ID`),
  CONSTRAINT `FK_person_history_work_person_email_ref_id` FOREIGN KEY (`person_email_ref_id`) REFERENCES `person_email` (`ID`),
  CONSTRAINT `FK_person_history_work_person_phone_ref_id` FOREIGN KEY (`person_phone_ref_id`) REFERENCES `person_phone` (`ID`),
  CONSTRAINT `fk_phw_person_main_id` FOREIGN KEY (`person_main_ref_id`) REFERENCES `person_main` (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=369407 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci AVG_ROW_LENGTH=81

内容的提问来源于stack exchange，提问作者Floobinator