多表统计SQL查询过慢问题排查求助

问题现象 #

BhopTimer数据库中执行以下查询耗时约28-30秒，性能极差：

select
    `map`,
    `tier`,
     (select count(*) from mapzones a where a.map = b.map
           and `track` = 0 and `type` > 0) as `stages`,
     (select count(*) from mapzones a where a.map = b.map
           and `track` > 0 and `type` > 0) as `bonuses`
from
    maptiers b
order by `map` asc;

对比SurfTimer数据库中类似查询，耗时通常小于50ms（最高不超过100ms）：

select
    `mapname`,
    `tier`,
     (select count(*) from ck_zones a where a.mapname = b.mapname
       and `zonegroup` = 0 and (zonetype = 2
                             or zonetype = 3)) as `stages`,
     (select count(*) from ck_zones a where a.mapname = b.mapname 
       and `zonegroup` > 0 and `zonetype` = 0) as `bonuses`
from
    ck_maptier b
order by `mapname` asc;

服务器信息 #

• MySQL版本：8.0.29 - MySQL Community Server - GPL

表结构详情 #

BhopTimer数据库表 #

CREATE TABLE `maptiers` (
  `map` varchar(255) NOT NULL,
  `tier` int NOT NULL DEFAULT '1',
  PRIMARY KEY (`map`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

CREATE TABLE `mapzones` (
  `id` int NOT NULL AUTO_INCREMENT,
  `map` varchar(255) NOT NULL,
  `type` int DEFAULT NULL,
  `corner1_x` float DEFAULT NULL,
  `corner1_y` float DEFAULT NULL,
  `corner1_z` float DEFAULT NULL,
  `corner2_x` float DEFAULT NULL,
  `corner2_y` float DEFAULT NULL,
  `corner2_z` float DEFAULT NULL,
  `destination_x` float NOT NULL DEFAULT '0',
  `destination_y` float NOT NULL DEFAULT '0',
  `destination_z` float NOT NULL DEFAULT '0',
  `track` int NOT NULL DEFAULT '0',
  `flags` int DEFAULT '0',
  `data` int DEFAULT '0',
  `form` tinyint DEFAULT NULL,
  `target` varchar(63) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=14612 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

SurfTimer数据库表 #

CREATE TABLE `ck_maptier` (
  `mapname` varchar(54) NOT NULL,
  `tier` int NOT NULL,
  `maxvelocity` float NOT NULL DEFAULT '3500',
  `announcerecord` int NOT NULL DEFAULT '0',
  `gravityfix` int NOT NULL DEFAULT '1',
  `ranked` int NOT NULL DEFAULT '1',
  `stages` int DEFAULT NULL,
  `bonuses` int DEFAULT NULL,
  PRIMARY KEY (`mapname`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

CREATE TABLE `ck_zones` (
  `mapname` varchar(54) NOT NULL,
  `zoneid` int NOT NULL DEFAULT '-1',
  `zonetype` int DEFAULT '-1',
  `zonetypeid` int DEFAULT '-1',
  `pointa_x` float DEFAULT '-1',
  `pointa_y` float DEFAULT '-1',
  `pointa_z` float DEFAULT '-1',
  `pointb_x` float DEFAULT '-1',
  `pointb_y` float DEFAULT '-1',
  `pointb_z` float DEFAULT '-1',
  `vis` int DEFAULT '0',
  `team` int DEFAULT '0',
  `zonegroup` int NOT NULL DEFAULT '0',
  `zonename` varchar(128) DEFAULT NULL,
  `hookname` varchar(128) DEFAULT 'None',
  `targetname` varchar(128) DEFAULT 'player',
  `onejumplimit` int NOT NULL DEFAULT '1',
  `prespeed` int NOT NULL DEFAULT '350',
  PRIMARY KEY (`mapname`,`zoneid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

已尝试的无效方案 #

以下改写查询未解决性能问题：

SELECT  a.map, a.tier, b.stage_count
    FROM  maptiers a
    INNER JOIN  
    (
        SELECT  DISTINCT map,
                COUNT(CASE WHEN (type > 0 AND  track = 0) THEN 1 END)
                         OVER (PARTITION BY map) AS `stage_count`
            FROM  mapzones
    ) AS b  ON a.map = b.map
    ORDER BY  a.map;

慢查询原因分析 #

核心差异在于索引设计：

1. SurfTimer的ck_zones表使用联合主键(mapname, zoneid)，mapname作为索引前缀，查询时能快速过滤出指定mapname的所有行，避免全表扫描。
2. BhopTimer的mapzones表仅以id作为主键，没有针对map字段的索引，更没有匹配查询条件track、type的复合索引。这导致原查询中每个子查询都要对mapzones执行全表扫描（表中已有14612条数据），而maptiers的每条记录会触发两次全表扫描，累计扫描次数极多，最终导致查询耗时过长。
3. 额外因素：BhopTimer的map字段长度为255，比SurfTimer的mapname（54）更长，即使创建索引，索引体积也会更大，但这不是主要性能瓶颈。

优化方案 #

方案1：添加针对性复合索引 #

给mapzones表创建复合索引，覆盖查询过滤条件，让MySQL无需全表扫描即可快速统计行数：

CREATE INDEX idx_map_track_type ON mapzones (`map`, `track`, `type`);

该索引可以直接支持两个子查询的过滤条件：

• 对于stages统计：map = ? AND track = 0 AND type > 0
• 对于bonuses统计：map = ? AND track > 0 AND type > 0
  MySQL会利用索引快速定位符合条件的行，且count(*)可以直接通过索引统计，无需回表查询原始数据。

方案2：改写查询为单次聚合关联 #

将两次子查询合并为一次聚合查询，减少对mapzones的扫描次数（从多次变为1次）：

SELECT
    b.map,
    b.tier,
    COALESCE(m.stages, 0) AS stages,
    COALESCE(m.bonuses, 0) AS bonuses
FROM maptiers b
LEFT JOIN (
    SELECT
        map,
        COUNT(CASE WHEN track = 0 AND type > 0 THEN 1 END) AS stages,
        COUNT(CASE WHEN track > 0 AND type > 0 THEN 1 END) AS bonuses
    FROM mapzones
    GROUP BY map
) m ON b.map = m.map
ORDER BY b.map ASC;

结合方案1的索引，该查询会先通过索引高效聚合出每个map的统计数据，再与maptiers关联，性能会大幅提升。

验证优化效果 #

执行EXPLAIN命令查看查询计划，确认优化后的查询是否使用了创建的索引，且避免了全表扫描：

EXPLAIN
SELECT
    b.map,
    b.tier,
    COALESCE(m.stages, 0) AS stages,
    COALESCE(m.bonuses, 0) AS bonuses
FROM maptiers b
LEFT JOIN (
    SELECT
        map,
        COUNT(CASE WHEN track = 0 AND type > 0 THEN 1 END) AS stages,
        COUNT(CASE WHEN track > 0 AND type > 0 THEN 1 END) AS bonuses
    FROM mapzones
    GROUP BY map
) m ON b.map = m.map
ORDER BY b.map ASC;

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