带聚合函数的SQL查询优化：索引齐全仍耗时1.3秒？

我有一条执行耗时1.3秒的SQL查询，已配置所有索引但仍缓慢，能否优化或简化该查询？需要在线计算数据，不希望将计算结果保存到表中定期重新生成。

原始查询语句 #

SELECT `countries`.*, `c2`.`country_name` AS `parent_name`,
       COUNT(countryperiods.countryPeriod_id) AS `numberCountryPeriods`,
       MIN(`from`) AS `min`, MAX(`to`) AS `max`,
       `continents`.`continent_id`, `continents`.`continent_name`,
       `continents`.`continent_seo`, `continents`.`continent_enabled` 
FROM `countries`
INNER JOIN `us`
LEFT JOIN `countries` AS `c2`
   ON c2.country_id = countries.parent_id
LEFT JOIN `s`
    ON countries.country_id = s.country_id
LEFT JOIN `countryperiods`
    ON countryperiods.country_id = countries.country_id
LEFT JOIN `continents`
    ON continents.continent_id = countries.continent_id
WHERE
    (countries.country_id = s.country_id) AND
    (f_user_id = '14') AND
    (s.id = us.f_s_id) AND
    (us_toChange = 1)
GROUP BY `countries`.`country_id`
ORDER BY `country_name` ASC
LIMIT 1

相关表结构及索引 #

countries表 #

CREATE TABLE `countries` (
  `country_id` int(11) NOT NULL,
  `continent_id` int(11) DEFAULT NULL,
  `parent_id` int(11) DEFAULT NULL,
  `currency_code` varchar(10) DEFAULT NULL,
  `country_code` varchar(5) DEFAULT NULL,
  `country_name` varchar(100) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
  `country_seo` varchar(100) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
  `country_enabled` tinyint(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;

ALTER TABLE `countries`
ADD PRIMARY KEY (`country_id`),
ADD KEY `continent_id` (`continent_id`),
ADD KEY `parent_id` (`parent_id`);

s表 #

CREATE TABLE `s` (
  `id` int(11) NOT NULL,
  `country_id` int(11) DEFAULT NULL,
  `s_enabled` tinyint(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;

ALTER TABLE `s`
ADD PRIMARY KEY (`id`),
ADD KEY `country_id` (`country_id`);

countryperiods表 #

CREATE TABLE `countryperiods` (
  `countryPeriod_id` int(11) NOT NULL,
  `country_id` int(11) DEFAULT NULL,
  `from` smallint(4) DEFAULT NULL,
  `to` smallint(4) DEFAULT NULL,
  `countryPeriod_enabled` tinyint(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;

ALTER TABLE `countryperiods`
ADD PRIMARY KEY (`countryPeriod_id`),
ADD KEY `country_id` (`country_id`);

continents表 #

CREATE TABLE `continents` (
  `continent_id` int(11) NOT NULL,
  `continent_name` varchar(50) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
  `continent_seo` varchar(100) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
  `continent_enabled` tinyint(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;

ALTER TABLE `continents`
ADD PRIMARY KEY (`continent_id`);

us表 #

CREATE TABLE `us` (
  `uS_id` int(11) NOT NULL,
  `f_user_id` int(11) DEFAULT NULL,
  `f_s_id` int(11) DEFAULT NULL,
  `us_toChange` tinyint(1) NOT NULL DEFAULT 0,
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;

ALTER TABLE `us`
ADD PRIMARY KEY (`uS_id`),
ADD KEY `f_s_id` (`f_s_id`),
ADD KEY `f_user_id` (`f_user_id`),
ADD KEY `us` (`f_user_id`,`f_s_id`) USING BTREE;

执行计划 #

执行计划截图包含各表的访问类型、索引使用、行数预估等核心查询执行信息。

优化建议 #

1. 修正JOIN逻辑与顺序 #

原始查询JOIN顺序混乱，且WHERE子句将s表的左连接隐式转为内连接，调整为从过滤条件更严格的表开始关联，减少数据处理量：

SELECT `countries`.*, `c2`.`country_name` AS `parent_name`,
       COUNT(countryperiods.countryPeriod_id) AS `numberCountryPeriods`,
       MIN(countryperiods.`from`) AS `min`, MAX(countryperiods.`to`) AS `max`,
       `continents`.`continent_id`, `continents`.`continent_name`,
       `continents`.`continent_seo`, `continents`.`continent_enabled` 
FROM `us`
INNER JOIN `s` ON us.f_s_id = s.id
INNER JOIN `countries` ON s.country_id = countries.country_id
LEFT JOIN `countries` AS `c2` ON c2.country_id = countries.parent_id
LEFT JOIN `countryperiods` ON countryperiods.country_id = countries.country_id
LEFT JOIN `continents` ON continents.continent_id = countries.continent_id
WHERE us.f_user_id = 14 AND us.us_toChange = 1
GROUP BY `countries`.`country_id`
ORDER BY `countries`.`country_name` ASC
LIMIT 1

• 优先从us表（有明确用户和状态过滤）开始关联，缩小后续关联的数据范围
• 去掉f_user_id的单引号，避免int类型字段的隐式类型转换，确保索引生效
• 明确JOIN关联条件，避免逻辑歧义

2. 添加复合索引提升效率 #

• 给s表添加：CREATE INDEX idx_s_id_countryid ON s(id, country_id);，覆盖关联和字段查询，避免回表
• 给countryperiods表添加：CREATE INDEX idx_cp_countryid_from_to ON countryperiods(country_id, from, to, countryPeriod_id);，直接覆盖聚合计算所需字段
• 给countries表添加：CREATE INDEX idx_c_countryid_name ON countries(country_id, country_name);，覆盖分组和排序操作，减少临时表生成

3. 优化聚合与排序逻辑 #

利用LIMIT 1的特性，先通过子查询快速定位目标country_id，再关联其他表获取数据，避免全量聚合：

SELECT c.*, `c2`.`country_name` AS `parent_name`,
       cp.numberCountryPeriods, cp.min_from, cp.max_to,
       `continents`.`continent_id`, `continents`.`continent_name`,
       `continents`.`continent_seo`, `continents`.`continent_enabled`
FROM (
    SELECT countries.country_id, countries.country_name
    FROM `us`
    INNER JOIN `s` ON us.f_s_id = s.id
    INNER JOIN `countries` ON s.country_id = countries.country_id
    WHERE us.f_user_id = 14 AND us.us_toChange = 1
    ORDER BY countries.country_name ASC
    LIMIT 1
) AS sub
INNER JOIN countries c ON sub.country_id = c.country_id
LEFT JOIN `countries` AS `c2` ON c2.country_id = c.parent_id
LEFT JOIN (
    SELECT country_id,
           COUNT(countryPeriod_id) AS numberCountryPeriods,
           MIN(`from`) AS min_from,
           MAX(`to`) AS max_to
    FROM countryperiods
    GROUP BY country_id
) AS cp ON cp.country_id = c.country_id
LEFT JOIN `continents` ON continents.continent_id = c.continent_id

此方式先通过小范围查询锁定目标数据，再进行关联聚合，大幅减少计算量。

4. 精简查询字段 #

如果不需要countries表的所有字段，明确列出所需字段，减少数据传输和内存占用。

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