mysql - 查询耗时太长，而将其拆分为两个查询耗时 0.2 秒

Question

我有当前的查询:

select m.id, ms.severity, ms.risk_score, count(distinct si.id), boarding_date_tbl.boarding_date
from merchant m
join merchant_has_scan ms on m.last_scan_completed_id = ms.id
join scan_item si on si.merchant_has_scan_id = ms.id and si.is_registered = true
join (select m.id merchant_id, min(s_for_boarding.scan_date) boarding_date
    from merchant m
    left join merchant_has_scan ms on m.id = ms.merchant_id
    left join scan s_for_boarding on s_for_boarding.id = ms.scan_id and s_for_boarding.scan_type = 1
    group by m.id) boarding_date_tbl on boarding_date_tbl.merchant_id = m.id
group by m.id
limit 100;

当我在大型方案(大约 200 万“商家”)上运行它时，需要超过 20 秒。但如果我将其拆分为:

select m.legal_name, m.unique_id, m.merchant_status, s_for_boarding.scan_date
from merchant m
join merchant_has_scan ms on m.id = ms.merchant_id
join scan s_for_boarding on s_for_boarding.id = ms.scan_id and s_for_boarding.scan_type = 1
group by m.id
limit 100;

和

select m.id, ms.severity, ms.risk_score, count(distinct si.id)
from merchant m
join merchant_has_scan ms on m.last_scan_completed_id = ms.id
join scan_item si on si.merchant_has_scan_id = ms.id and si.is_registered = true

group by m.id
limit 100;

两者都需要大约 0.1 秒原因很明显，低限制意味着不需要做太多事情就能获得前 100 个。同样清楚的是，内部选择会导致第一个查询尽可能多地运行。我的问题是有没有办法只对相关商家而不是整个表进行内部选择？

<小时/>

更新

在内部查询之前进行左连接而不是连接有助于将其减少到6秒，但它仍然比我这样做能得到的要多得多2 条查询

<小时/>

更新2

为商家创建表:

CREATE TABLE `merchant` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `last_scan_completed_id` bigint(20) DEFAULT NULL,
  `last_updated` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  CONSTRAINT `FK_9lhkm7tb4bt87qy4j3fjayec5` FOREIGN KEY (`last_scan_completed_id`) REFERENCES `merchant_has_scan` (`id`)
)

merchant_has_scan:

CREATE TABLE `merchant_has_scan` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `merchant_id` bigint(20) NOT NULL,
  `risk_score` int(11) DEFAULT NULL,
  `scan_id` bigint(20) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_merchant_id` (`scan_id`,`merchant_id`),
  CONSTRAINT `FK_3d8f81ts5wj2u99ddhinfc1jp` FOREIGN KEY (`scan_id`) REFERENCES `scan` (`id`),
  CONSTRAINT `FK_e7fhioqt9b9rp9uhvcjnk31qe` FOREIGN KEY (`merchant_id`) REFERENCES `merchant` (`id`)
)

扫描项目:

CREATE TABLE `scan_item` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `is_registered` bit(1) NOT NULL,
  `merchant_has_scan_id` bigint(20) NOT NULL,
 PRIMARY KEY (`id`),
  CONSTRAINT `FK_avcc5q3hkehgreivwhoc5h7rb` FOREIGN KEY (`merchant_has_scan_id`) REFERENCES `merchant_has_scan` (`id`)
)

扫描:

CREATE TABLE `scan` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `scan_date` datetime DEFAULT NULL,
  `scan_type` int(11) NOT NULL,
  PRIMARY KEY (`id`)
)

以及解释:

Answer 1

• 您没有最新版本的 MySQL，该版本无法为派​​生表创建索引。 (您运行什么版本？)
• “派生表”(子查询)将是 EXPLAIN 中的第一个表，因为它必须如此。
• merchant_has_scan 是一个多:多表，但没有优化提示 here ——解决这个问题可能是加快速度的最大因素。警告:提示建议删除 id，但您似乎需要使用 id，所以保留它。
• COUNT(DISTINCT si.id) 和 JOIN si... 可以替换为 ( SELECT COUNT(*) FROM scan_item WHERE ... )，从而消除了一个 JOIN 并可能减少 Explode-Implode .
• LEFT JOIN -- 您有时是否希望得到 boarding_date 的 NULL ？如果没有，请使用 JOIN，而不是 LEFT JOIN。 (最好说出您的意图，而不是让查询有多种解释。)
• 如果您可以删除 LEFT，那么既然 m.id 和 merchant_id 被指定为相等，为什么要把它们都列在选择？ (这是一个混淆因素，而不是速度问题)。
• 你说你把它分成两部分——但你没有。当您将其拉出时，您将 LIMIT 100 添加到内部查询中。如果您需要，也将其添加到派生表中。然后您也许能够从外部查询中删除GROUP BY m.id LIMIT 100。