从 2.95 秒到 0.44 秒：MySQL 8 空间查询优化实战教程

本文面向会写基础 SELECT、JOIN、GROUP BY，知道普通索引，但没有 GIS 和 EXPLAIN ANALYZE 经验的读者。

案例来自当前项目的真实表结构、Java 代码、验证 SQL 和执行计划。文中的耗时只代表这一次数据、图形、MySQL 环境和缓存状态，不保证其他环境获得相同提升。

1. 问题背景 ​

页面允许用户在地图上画多个区域。后端需要完成三件事：

1. 找出区域内的建筑物；
2. 通过建筑物唯一码关联单位；
3. 对单位执行 COUNT、SUM 和动态 GROUP BY。

本次实验的数据规模如下：

第一次看到 HTTP 请求耗时较长时，很容易直接下结论：“空间 SQL 慢。”这还不够准确，因为一次 HTTP 请求可能包含多条 SQL，以及数据库之外的工作。

1.1 HTTP 请求耗时不等于单条 SQL 耗时 ​

本项目一次统计请求至少涉及：

• 动态分组统计 SQL；
• 完整汇总 SQL；
• 建筑物点位 SQL；
• JDBC 等待和结果对象组装；
• JSON 序列化与网络传输；
• 浏览器中的 MarkerCluster 和表格渲染。

因此要分层计时：

HTTP 总耗时
├── 动态统计 SQL
├── 汇总 SQL
├── 建筑物点位 SQL
├── Java 组装与 JSON 序列化
└── 网络与前端渲染

本案例最初的计时日志存在两个容易误解的地方：动态统计 SQL 没有被单独计时；后续两个阶段又共用了累计计时器。因此，日志中的“查询建筑耗时”并不等于建筑物 SQL 的独立耗时。

更稳妥的方式是：在每条 SQL 执行前记录开始时间，执行后立即记录该阶段耗时；同时在请求入口和出口记录 HTTP 总耗时。这样每个数字都有清楚的边界。

执行计划证据：原始完整汇总 SQL 在 MySQL 内实际约 2,952 ms。

合理推断：如果 HTTP 总耗时明显大于所有 SQL 耗时之和，差值可能在连接等待、序列化、网络或前端渲染中；仍需分段计时确认。

通用经验：优化前先测量，不要用 HTTP 总耗时替代单条 SQL 的数据库耗时。

2. 数据结构和原始 SQL ​

2.1 建筑物表 ​

以下是 buildings 表的 DDL：

CREATE TABLE `buildings` (
    `id` int NOT NULL AUTO_INCREMENT,
    `name` varchar(799) NOT NULL,
    `location` point NOT NULL SRID 4326,
    `building_id` varchar(100) DEFAULT NULL,
    PRIMARY KEY (`id`),
    SPATIAL KEY `buildings_location_IDX` (`location`),
    KEY `idx_buildings_building_id` (`building_id`)
) ENGINE=InnoDB;

这里第一次出现了三个 GIS 概念：

• POINT：一个地理点，本项目用它保存建筑物经纬度；
• SRID 4326：坐标参考系统编号，4326 对应常见的 WGS84 经纬度；
• SPATIAL INDEX：空间索引。普通 B-Tree 索引适合字符串和数字，空间索引使用几何对象的外接矩形组织空间数据。

GeoJSON 坐标顺序必须是：

[经度, 纬度]

例如深圳附近一点：

[114.01609, 22.6543]

不要写成 [纬度, 经度]。查询几何和 buildings.location 还必须使用相同 SRID 4326。

2.2 关联字段索引 ​

关联字段需要增加普通索引：

ALTER TABLE `buildings`
    ADD INDEX `idx_buildings_building_id` (`building_id`);

ALTER TABLE `company_dw`
    ADD INDEX `idx_company_dw_jzwwym` (`jzwwym`);

关联关系是：

buildings.building_id = company_dw.jzwwym

空间索引包含在建表 DDL 中，另外两个语句补充普通关联索引。实际数据库是否具备这些索引，仍应使用 SHOW CREATE TABLE 和 SHOW INDEX 核对，不能只根据设计文档判断。

2.3 原始完整统计 SQL ​

原始逻辑可以简化为：

SELECT
    COUNT(DISTINCT b.id) AS buildingCount,
    COUNT(*) AS companyCount,
    SUM(c.ccyryqmrs) AS ccyryqmrs,
    SUM(c.yysr) AS yysr
FROM company_dw c
INNER JOIN buildings b
    ON b.building_id = c.jzwwym
WHERE ST_Intersects(
    b.location,
    ST_GeomFromGeoJSON(?, 1, 4326)
);

ST_GeomFromGeoJSON 把 GeoJSON 文本转换成 MySQL 几何对象。ST_Intersects(A, B) 判断两个几何对象是否相交。建筑物是点，查询区域是面；点在面内或边界上时会命中。

其中 ? 是 JDBC 参数占位符，不能由字符串拼接替代。

3. EXPLAIN ANALYZE 入门 ​

3.1 它能提供什么 ​

普通 EXPLAIN 只展示优化器准备如何执行。EXPLAIN ANALYZE 会真正执行 SELECT，再展示：

• 执行顺序；
• 预估行数和实际行数；
• 每个节点的实际时间；
• 节点执行次数；
• 使用了全表扫描、索引范围扫描还是索引查找。

重要提醒：EXPLAIN ANALYZE 会真实执行 SQL。即使是 SELECT，也会占用 CPU、内存和 I/O。应在测试环境或业务低峰执行；不要把它当成完全无成本的静态分析命令。

3.2 如何运行带 GeoJSON 的 SQL ​

数据库控制台不能直接理解 Java SQL 中的 ?。可以先设置同一连接内的会话变量：

SET @geo_json = '{
  "type": "Polygon",
  "coordinates": [[[114.01,22.65],[114.02,22.65],[114.02,22.66],[114.01,22.65]]]
}';

然后执行：

EXPLAIN ANALYZE
SELECT COUNT(*)
FROM buildings b
WHERE ST_Intersects(
    b.location,
    ST_GeomFromGeoJSON(@geo_json, 1, 4326)
);

@geo_json 是连接级变量。必须先执行 SET，并在同一 SQL 控制台、同一数据库连接中执行后续 SQL；重新连接后变量会变成 NULL。

3.3 从下往上读执行树 ​

执行计划是一棵树。子节点先产生数据，父节点消费子节点的结果，因此通常从最下面向上阅读：

3.4 四个最重要的数字 ​

假设看到：

(actual time=0.0104..0.0147 rows=3.03 loops=3831)

• actual time=a..b：产生第一行和完成该节点的大致毫秒数；当 loops 大于 1 时，时间通常是每次循环的平均值；
• rows：节点每次循环实际输出的平均行数；
• loops：节点执行次数；
• cost：优化器内部成本估值，不是毫秒，也不能直接等同于真实时间。

粗略判断多循环节点的工作量时，可以结合：

平均完成时间 × loops

但不要把所有节点时间直接相加，因为父节点时间通常已经包含子节点的工作。

4. 第一次执行计划：原始 ST_Intersects 全表扫描 ​

原始完整统计的执行计划如下：

-> Aggregate
   (actual time=2952..2952 rows=1 loops=1)
    -> Nested loop inner join
       (actual time=101..2948 rows=11595 loops=1)
        -> Filter: st_intersects(...)
           (actual time=101..2890 rows=3831 loops=1)
            -> Table scan on b
               (actual time=0.0229..49 rows=54274 loops=1)
        -> Index lookup on c using idx_company_dw_jzwwym
           (actual time=0.0104..0.0147 rows=3.03 loops=3831)

4.1 Table scan on b ​

Table scan on b
actual time=0.0229..49 rows=54274 loops=1

Table scan 是全表扫描：MySQL 读取了约 54,274 栋建筑物。扫描本身只用了约 49 ms，因此“读取表”不是主要瓶颈。

4.2 Filter: ST_Intersects ​

Filter: st_intersects(...)
actual time=101..2890 rows=3831 loops=1

54,274 个点经过精确空间判断后剩下 3,831 栋。该节点结束于约 2,890 ms，而下层扫描结束于约 49 ms。由此可以判断，大部分时间消耗在空间函数判断，而不是扫描数据页。

这里能看到：

<cache>(st_geomfromgeojson(...))

<cache> 表示 MySQL 在这条 SQL 内缓存了 ST_GeomFromGeoJSON(...) 的结果。它不是对 54,274 行分别重新解析一遍 GeoJSON。

必须准确理解这个结论：

• 单条 SQL 内，GeoJSON 转几何结果被缓存；
• ST_Intersects 仍然需要对大量建筑物逐点判断；
• 不同 SQL 之间不会因为这个 <cache> 自动共享空间筛选结果。

4.3 Index lookup on c ​

Index lookup on c using idx_company_dw_jzwwym
actual time=0.0104..0.0147 rows=3.03 loops=3831

Index lookup 是根据一个具体索引值查找匹配行。这里对 3,831 栋建筑物分别通过 jzwwym 索引查单位，平均每栋约 3.03 家，最终产生 11,595 家单位。

单次查找只有约 0.015 ms。即使乘以 3,831 次，它仍远小于空间过滤耗时。

执行计划证据：单位关联使用 idx_company_dw_jzwwym，不是全表扫描；最终单位行数为 11,595。

结论：单位表索引不是本案例的主要瓶颈。

4.4 Aggregate ​

Aggregate
actual time=2952..2952 rows=1 loops=1

下层 JOIN 结束于约 2,948 ms，聚合结束于约 2,952 ms，二者只差约 4 ms。因此：

COUNT(DISTINCT ...)
COUNT(*)
SUM(...)

不是这条 SQL 的主要耗时来源。

5. 第二次：为什么加了 MBRIntersects 反而变慢 ​

MBR 是 Minimum Bounding Rectangle，即“最小外接矩形”。无论多边形多复杂，都可以先画一个能够包住它的矩形。

常见空间查询策略是：

1. 使用 MBR 和空间索引快速粗筛；
2. 再用 ST_Intersects 做精确判断。

实验增加了：

WHERE MBRIntersects(
          ST_GeomFromGeoJSON(@geo_json, 1, 4326),
          b.location
      )
  AND ST_Intersects(
          b.location,
          ST_GeomFromGeoJSON(@geo_json, 1, 4326)
      )

但执行计划仍然是：

-> Filter: mbrintersects(...) and st_intersects(...)
   (actual time=177..5961 rows=3831 loops=1)
    -> Table scan on b
       (actual time=0.0216..57.1 rows=54274 loops=1)

完整统计约 6,029 ms，比原始约 2,952 ms 更慢。

原因很直接：

• 仍然全表扫描 54,274 行；
• 空间索引没有参加候选筛选；
• 每行除了精确 ST_Intersects，又多执行了一个 MBRIntersects。

执行计划证据：节点仍是 Table scan on b，不是空间索引范围扫描。

合理推断：在没有索引粗筛收益时，额外空间函数增加了 CPU 工作，导致本次实测变慢。

通用经验：添加条件或添加索引不等于查询自动变快，必须重新看执行计划和真实耗时。

6. 第三次：用 FORCE INDEX 验证空间索引 ​

6.1 先确认表结构 ​

空间索引要正常参与优化，至少要核对：

SHOW CREATE TABLE buildings;
SHOW INDEX FROM buildings;

SELECT ST_SRID(location), COUNT(*)
FROM buildings
GROUP BY ST_SRID(location);

本项目脚本要求：

location 为 POINT NOT NULL SRID 4326
索引 buildings_location_IDX 为 SPATIAL
数据中的 ST_SRID(location) 为 4326

6.2 FORCE INDEX 的正确用途 ​

FORCE INDEX 是索引提示：告诉优化器把全表扫描视为代价很高，优先尝试指定索引。它适合用来回答一个诊断问题：

“这个空间索引到底能不能用于当前表达式？如果强制使用，实际会更快吗？”

验证 SQL：

EXPLAIN ANALYZE
SELECT COUNT(*)
FROM buildings b FORCE INDEX (`buildings_location_IDX`)
WHERE MBRIntersects(
    ST_GeomFromGeoJSON(@geo_json, 1, 4326),
    b.location
);

执行计划变成：

-> Aggregate: count(0)
   (actual time=1722..1722 rows=1 loops=1)
    -> Filter: mbrintersects(...)
       (actual time=0.357..1720 rows=17947 loops=1)
        -> Index range scan on b using buildings_location_IDX
           (actual time=0.112..49.5 rows=17947 loops=1)

6.3 Index range scan 是什么 ​

Index range scan 表示 MySQL 从索引中读取满足某个范围的候选项，不再扫描整张表。

空间索引在约 49.5 ms 内返回了 17,947 个整体 MBR 候选点。这证明：

• 空间索引存在；
• SRID 和表达式可以工作；
• 索引确实能够减少候选点。

完整统计配合整体 MBR 和 FORCE INDEX 后约 2,726 ms：

Index range scan：17,947 个候选，约 52.8 ms
精确过滤完成：3,831 栋，约 2,663 ms
完整统计结束：约 2,726 ms

相对原始 2,952 ms 只改善约 226 ms。它证明索引可用，但还没有解决“候选过多”的问题。

6.4 为什么优化器没有自动选它 ​

当时优化器给出的成本大致是：

全表扫描成本：5502
空间索引方案成本：7764

cost 不是毫秒。优化器根据统计信息和成本模型选择它认为便宜的方案；空间函数 CPU 成本、缓存状态、区域大小等因素不一定被完美估计。因此可能发生：实际更快的空间索引方案，优化器却没有自动选择。

但不能据此得出“所有空间查询都必须 FORCE INDEX”。如果区域覆盖了绝大多数建筑物，索引扫描加回表可能比顺序扫描更慢。应使用不同大小、不同位置和不同组合方式的区域做基准测试。

通用经验：FORCE INDEX 是验证工具和有条件的生产策略，不是无条件最佳实践。

7. 第四次：GeometryCollection 整体 MBR 的问题 ​

用户画了三个相距较远的区域。后端最初把它们组合成一个 GeometryCollection。整体 MBR 必须包住三个图形，因此图形之间的大量空白区域也被纳入候选范围。

实测数据：

整体 MBR 候选：17,947
精确命中：3,831
误命中候选：14,116

约 79% 的整体 MBR 候选最终被精确空间判断排除。

这解释了为什么空间索引已经生效，完整统计仍需约 2.7 秒：索引很快找到了候选，但候选集合仍然太大，ST_Intersects 还要处理很多位于空白区域的点。

8. 第五次：拆分三个 Polygon 并 UNION 去重 ​

8.1 分别粗筛 ​

三个 Polygon 单独使用自己的 MBR 后：

候选数从 17,947 降到 5,762，减少约 67.9%。这里没有使用固定的“低于某个绝对数量就拆分”规则；是否值得拆，应由候选减少比例、完整 SQL 耗时和额外去重成本共同决定。

8.2 最终 SQL ​

最终验证 SQL 的核心结构是：

WITH matched_buildings AS (
    SELECT b.id, b.building_id
    FROM buildings b FORCE INDEX (`buildings_location_IDX`)
    WHERE MBRIntersects(@geometry_1, b.location)
      AND ST_Intersects(b.location, @geometry_1)

    UNION

    SELECT b.id, b.building_id
    FROM buildings b FORCE INDEX (`buildings_location_IDX`)
    WHERE MBRIntersects(@geometry_2, b.location)
      AND ST_Intersects(b.location, @geometry_2)

    UNION

    SELECT b.id, b.building_id
    FROM buildings b FORCE INDEX (`buildings_location_IDX`)
    WHERE MBRIntersects(@geometry_3, b.location)
      AND ST_Intersects(b.location, @geometry_3)
)
SELECT
    COUNT(DISTINCT mb.id) AS buildingCount,
    COUNT(*) AS companyCount,
    SUM(c.ccyryqmrs) AS ccyryqmrs,
    SUM(c.yysr) AS yysr
FROM matched_buildings mb
INNER JOIN company_dw c
    ON c.jzwwym = mb.building_id;

WITH matched_buildings AS (...) 是公共表表达式，简称 CTE。可以先把它理解为“给一段中间查询结果起名字”。

这里使用 UNION，不是 UNION ALL：

• UNION 会去重；
• UNION ALL 不去重。

如果两个绘制区域重叠，同一栋楼可能被两个分支命中。使用 UNION ALL 会让同一建筑物重复关联单位，导致 COUNT 和 SUM 被放大。

8.3 最终执行计划 ​

只统计匹配建筑物的执行计划：

-> Aggregate: count(0)
   (actual time=394..394 rows=1 loops=1)
    -> Table scan on matched_buildings
       (actual time=393..394 rows=3831 loops=1)
        -> Materialize union CTE with deduplication
           (actual time=393..393 rows=3831 loops=1)

三个分支分别为：

Polygon 1：索引候选 2135，精确命中 1016，分支结束约 84 ms
Polygon 2：索引候选 2462，精确命中 1769，分支结束约 209 ms
Polygon 3：索引候选 1165，精确命中 1046，分支结束约 96.7 ms

Materialize ... with deduplication 表示 MySQL 把三个分支结果物化成中间结果并去重。最终得到 3,831 栋建筑物。

完整统计执行计划：

-> Aggregate
   (actual time=444..444 rows=1 loops=1)
    -> Nested loop inner join
       (actual time=397..442 rows=11595 loops=1)
        -> Table scan on matched_buildings
           (actual time=397..398 rows=3831 loops=1)
        -> Index lookup on c using idx_company_dw_jzwwym
           (actual time=0.00733..0.0113 rows=3.03 loops=3831)

空间匹配和 CTE 物化约 397 ms；单位关联和最终聚合把总时间增加到约 444 ms。单位索引再次证明不是瓶颈。

9. 优化前后数据对比 ​

从整体 MBR 强制索引的约 2,726 ms 到拆分后的约 444 ms，本次实验提升约 6.1 倍，耗时下降约 83.7%。

这只是本案例结果，不是 MySQL 空间查询的普遍保证。图形大小、相互距离、重叠程度、数据分布、硬件、缓存和并发都会影响结果。

10. 正确性、边界与风险 ​

性能优化不能改变查询结果。空间查询尤其需要处理以下边界。

10.1 重叠区域 ​

多个区域可能命中同一建筑物。必须使用 UNION，或者在后续对建筑物主键执行明确去重。否则单位统计会重复。

10.2 MultiPolygon ​

MultiPolygon 是由多个 Polygon 组成的几何。处理时可以把其中每个完整 Polygon 拆成独立查询分支。这适合相距较远的多个面，因为每个面可以使用更紧的 MBR。

10.3 圆形 ​

标准 GeoJSON 没有 Circle 类型。当前前端先使用 Turf.js 将圆转换成 64 段 Polygon，再提交给后端。因此数据库看到的仍然是 Polygon。

段数越多，越接近圆，但几何更复杂。当前 64 段是精度和计算量之间的工程选择，不是数学上的真正圆。

10.4 带洞 Polygon ​

Polygon 的第一个线性环是外环，后续线性环是洞。带洞 Polygon 必须作为一个完整几何保留：

{
  "type": "Polygon",
  "coordinates": [
    [[外环坐标...]],
    [[洞的内环坐标...]]
  ]
}

不能把内环拆成一个独立 Polygon。否则洞会从“排除区域”错误地变成“包含区域”。当前验证器拆 MultiPolygon 时会复制一个 Polygon 的完整 coordinates，因此内外环保持在同一分支；测试也验证了这一点。

10.5 图形数量上限 ​

每个图形会产生一个 UNION 分支和两个 GeoJSON 参数。图形太多会造成：

• SQL 文本变长；
• 参数数量增加；
• 多次空间索引范围扫描；
• UNION 去重和 CTE 物化成本增加。

本案例把上限设置为 10 个独立面图形。3 或 10 都可以作为工程起点，但必须结合真实业务压测，不应无限制生成分支。

10.6 如何验证结果一致 ​

优化前后至少比较：

buildingCount
companyCount
每个 SUM 度量
每个 GROUP BY 分组行

对动态分组结果，不能只比较总行数；应该按所有维度列排序，逐行比较维度值、单位数量和每个度量。

还要准备边界测试：

• 点在 Polygon 内部；
• 点恰好位于边界；
• 点位于洞内；
• 两个区域重叠；
• 区域内没有建筑物；
• 建筑物没有关联单位；
• Polygon、MultiPolygon、Feature、FeatureCollection；
• 首尾未闭合或坐标越界的非法 GeoJSON。

本次三个 Polygon 的精确命中分别是 1,016、1,769、1,046，相加正好为 3,831，说明这次图形之间没有重复建筑物；生产实现仍必须处理重叠情况。

11. 如何安全落地到应用代码 ​

实验 SQL 通常写死三个图形，但真实应用收到的图形数量是不固定的。落地时应把处理过程拆成几个简单步骤，不必让业务代码了解复杂的 GIS 细节。

11.1 第一步：验证并规范化输入 ​

可以用下面的伪代码理解：

读取 GeoJSON
如果不是合法 JSON：拒绝
如果不是 Polygon、MultiPolygon、Feature 或 FeatureCollection：拒绝
检查每个坐标是否为 [经度, 纬度]
检查经纬度是否在合法范围
检查每个环是否首尾闭合

把 Feature 去掉包装，只保留 geometry
把 FeatureCollection 展开为多个 geometry
把 MultiPolygon 拆成多个完整 Polygon
保留每个 Polygon 的外环和全部内环

如果独立 Polygon 超过 50 个：拒绝
返回“已经验证的 Polygon 列表”

这里最重要的是“完整 Polygon”：带洞 Polygon 的内环必须跟着外环一起保留，不能独立拆出。

11.2 第二步：用固定模板生成 UNION 分支 ​

每个 Polygon 使用同一个安全 SQL 模板：

SELECT b.id, b.building_id
FROM buildings b FORCE INDEX (`buildings_location_IDX`)
WHERE MBRIntersects(
          ST_GeomFromGeoJSON(?, 1, 4326),
          b.location
      )
  AND ST_Intersects(
          b.location,
          ST_GeomFromGeoJSON(?, 1, 4326)
      )
  AND b.building_id IS NOT NULL

伪代码如下：

分支列表 = 空列表
参数列表 = 空列表

对每个已经验证的 Polygon：
    分支列表加入“固定 SQL 模板”
    参数列表加入 Polygon GeoJSON       // 给 MBRIntersects
    参数列表再次加入 Polygon GeoJSON   // 给 ST_Intersects

CTE = 用 UNION 连接全部分支
执行 CTE + 后续统计 SQL，并传入参数列表

动态变化的只有模板重复次数。用户提供的 GeoJSON 始终放在参数列表中，不能拼进 SQL 文本。

11.3 为什么 GeoJSON 必须参数绑定 ​

错误思路：

SQL = "... ST_GeomFromGeoJSON('" + 用户GeoJSON + "', 1, 4326) ..."

这样会带来 SQL 注入、引号转义和语法破坏风险。

正确思路：

ST_GeomFromGeoJSON(?, 1, 4326)

再让数据库驱动把 GeoJSON 作为参数传入。分页页码、页大小和其他普通值也应采用参数绑定。

11.4 动态字段必须使用白名单 ​

参数占位符只能代表“值”，不能代表列名、表名或排序方向。因此下面的想法不可行：

SELECT ?
FROM company_dw
GROUP BY ?;

动态维度和度量应该先经过白名单映射：

允许的维度 = 系统预先定义的维度编码集合
允许的度量 = 系统预先定义的度量编码集合

对用户选择的每个维度：
    如果不在允许的维度集合中：拒绝

对用户选择的每个度量：
    如果不在允许的度量集合中：拒绝

只把验证通过的字段编码放入 SELECT 和 GROUP BY

安全边界可以概括为：

动态值               → 参数绑定
动态列名             → 固定白名单
动态 UNION 分支数量  → 已验证图形列表 + 最大数量限制

11.5 注意跨 SQL 的重复空间计算 ​

如果一次请求分别执行动态统计、汇总统计和点位查询，每条 SQL 都可能重新计算一次匹配建筑物 CTE。CTE 只能在它所属的那条 SQL 内复用，不能跨多条 SQL 自动共享。

执行计划证据：拆分后的单条完整统计约 444 ms。

合理推断：如果同一请求执行三条空间 SQL，整个请求不会只有 444 ms；需要分别计时才能确定总成本。

后续方向：若仍不满足性能目标，可以评估把本次请求命中的建筑物保存到连接级临时表，再供多条统计 SQL 复用。但临时表涉及连接固定、事务、清理和并发，复杂度更高，应先测量再实施。

12. 通用 SQL 优化方法论 ​

这个案例展示了一套可以复用的过程。

12.1 先建立基线 ​

记录：

• 输入条件；
• 返回行数；
• SQL 实际时间；
• HTTP 总时间；
• 数据库版本和主要索引；
• 冷缓存还是热缓存。

没有基线，就无法判断优化是否真实有效。

12.2 从最底层高数据量节点开始读 ​

优先寻找：

Table scan
rows 很大
loops 很大
actual time 跨度很大

不要一看到最上层 SUM 就认为聚合慢。本案例中聚合只增加约几毫秒，真正耗时的是空间过滤。

12.3 区分三种访问方式 ​

节点名称不是绝对好坏。小表全表扫描可能很快；低选择率索引也可能比顺序扫描更慢。必须结合 rows 和 actual time。

12.4 先缩小昂贵函数的输入 ​

ST_Intersects 是精确空间判断，比普通整数比较昂贵。优化重点不是“让函数本身变快”，而是让更少的建筑物进入函数。

这与普通 SQL 中“先通过索引缩小候选，再执行复杂表达式”是同一个思想。

12.5 复杂输入要观察整体边界 ​

多个相距较远的区域组合后，整体 MBR 可能覆盖大量空白。这不是空间索引失效，而是索引只能根据整体外接矩形提供较宽的候选集合。

拆分 Polygon 的价值来自更紧的候选范围，不来自某个固定数量阈值。应比较：

拆分候选合计 / 整体 MBR 候选

以及最终完整 SQL 耗时。

12.6 每次只验证一个假设 ​

本案例的实验顺序很好地说明了这一点：

1. 原始 SQL：确认主要时间在空间过滤；
2. 加 MBR：发现没有走索引，反而变慢；
3. FORCE INDEX：证明索引和表达式可用；
4. 统计候选数：发现整体 MBR 仍然太宽；
5. 拆 Polygon：验证更紧 MBR 是否降低候选；
6. 完整统计：确认优化不仅对 COUNT(*) 有效；
7. 对比结果：确认性能变化没有破坏正确性。

一次修改很多条件会让你无法判断究竟是哪一步产生效果。

13. 可复用的 SQL 优化检查清单 ​

下面的清单不依赖本案例的表名和字段名，可以用于普通查询、空间查询和其他复杂 SQL、按照需要去选择。

测量范围 ​

• 是否分别记录了 HTTP 总耗时和每条 SQL 的耗时？
• 是否单独测量了应用组装、序列化、网络和前端渲染？
• 每个计时数字是单阶段耗时，还是多个阶段的累计耗时？
• 是否使用相同输入重复测试，并记录数据库版本和缓存状态？

EXPLAIN ANALYZE ​

• 是否确认 EXPLAIN ANALYZE 会真实执行 SQL？
• 是否从执行树最下方开始阅读？
• 是否找到实际行数最大、循环次数最多、时间跨度最大的节点？
• 是否区分优化器 cost 与真实毫秒时间？
• 对多循环节点，是否结合平均时间、rows 和 loops 判断工作量？
• 是否避免把包含关系中的父子节点时间简单相加？

表访问与索引 ​

• 是否用数据库命令核对真实表结构和真实索引，而不是只看设计文档？
• 查询条件、关联条件和排序字段是否有合适索引？
• 执行计划使用的是 Table scan、Index range scan 还是 Index lookup？
• 索引读取的候选行数是否真的比全表数据少很多？
• 优化器预估行数与实际行数是否差距过大？
• 增加索引后是否重新运行完整 SQL，而不是只检查索引存在？

空间查询 ​

• 空间列是否为非空，并声明了明确且一致的 SRID？
• 数据中的实际 SRID 是否一致？
• GeoJSON 是否使用 [经度, 纬度] 顺序？
• 查询几何与空间列是否使用同一个坐标参考系统？
• 是否采用 MBR 粗筛加精确空间判断？
• MBR 条件是否真的触发空间索引？
• 整体外接矩形是否包含大量没有业务意义的空白区域？
• 多个相距较远的面是否值得分别粗筛？
• 带洞 Polygon 是否保持完整，内环没有被独立拆分？
• MultiPolygon 是否按完整 Polygon 处理？
• 非标准圆形是否先转换成明确精度的 Polygon？
• 重叠区域是否通过 UNION 或稳定主键去重？
• 是否限制单次图形数量和动态 SQL 分支数量？

索引提示 ​

• 使用索引提示前，是否先比较自动计划与强制计划？
• 是否用同一条完整 SQL 对比，而不是比较两个不同查询？
• 是否测试了小范围、大范围、重叠和分散区域？
• 是否确认强制索引的收益在常见输入上稳定？
• 是否保留重新评估或关闭索引提示的能力？

正确性与安全 ​

• 优化前后的总行数、去重数量和聚合结果是否一致？
• 动态分组结果是否按全部维度排序后逐行比较？
• 是否测试空结果、边界值、重复数据和无法关联的数据？
• 所有用户输入值是否使用参数绑定？
• 动态表名、列名和排序方向是否只来自固定白名单？
• 是否限制请求大小、图形数量、分页大小和查询范围？
• 性能测试是否没有改变原有业务语义？

14. 总结 ​

这次优化不是“加一个空间索引就结束”，而是一条完整的证据链：

1. 原始 ST_Intersects 全表扫描，完整统计约 2,952 ms；
2. 增加 MBRIntersects 但未使用空间索引，多做了一次空间计算，反而约 6,029 ms；
3. FORCE INDEX 让执行计划出现 Index range scan，证明空间索引可用；
4. 整体 GeometryCollection 的 MBR 仍产生 17,947 个候选，完整统计约 2,726 ms；
5. 将三个 Polygon 分别粗筛，候选合计降到 5,762；
6. 使用 UNION 去重后仍正确得到 3,831 栋建筑物和 11,595 家单位；
7. 完整统计降到约 444 ms。

最重要的不是记住“必须拆 Polygon”或“必须 FORCE INDEX”，而是掌握方法：

分层计时
→ 用 EXPLAIN ANALYZE 找到昂贵节点
→ 用索引减少进入昂贵操作的数据
→ 验证优化器是否真的采用方案
→ 处理复杂输入造成的候选膨胀
→ 对比完整 SQL 和业务结果
→ 最后再安全落地到 JDBC

当数据、图形分布或 MySQL 环境变化时，应重新执行同样的测量过程，而不是假定本案例的 0.44 秒可以复制到所有环境。