先说实话这个标题我犹豫了很久要不要写。MySQL的spatial key空间索引平时用的人就不多能踩到坑的更少网上相关的中文资料也少得可怜。但我上个月真的被一个“附近门店”接口折腾了大半夜点开慢查询日志那一刻我一度非常确信自己撞上了官方bug。先交代背景。一个跑了大半年的LBS小业务核心表大概300万行存的是门店坐标当初为了解决“附近门店”的搜索需求很自然地在POINT字段上建了SPATIAL INDEX。在很长一段时间里接口的P99基本稳定在80ms上下谁也没去动过它。直到那天晚上P95突然暴涨到3秒多监控告警直接把我从沙发上炸了起来。我当时的第一个念头就是MySQL的空间索引是不是有bug但完整排查完我得给你纠正一下——这个锅大半得自己背MySQL只是把几个“隐形使用前提”藏得比较深而已。这篇内容适合所有正在用、或者打算用MySQL spatial index做LBS功能的后端开发者也适合被EXPLAIN结果整到怀疑人生的DBA。我会把完整现象、排查链路、根因机制、修复方案全写出来最后附上自查清单。1. 现场复现一条本该几十毫秒的查询为什么跑成了全表扫1.1 当时的表结构与慢查询SQL表结构不长核心字段就这几个CREATE TABLE shop ( id BIGINT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(64) NOT NULL, lng DOUBLE NOT NULL, lat DOUBLE NOT NULL, location POINT NOT NULL SRID 4326, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, SPATIAL INDEX idx_location(location) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;注意这个表结构是我后面修复过的样子当时线上版本和这个略有出入。真实的“事故现场”版本location列并没有写SRID 4326查询也写得很“直白”SELECT id, name, ST_Distance_Sphere(location, ST_GeomFromText(POINT(120.1536 30.2875))) AS dist FROM shop WHERE ST_Distance_Sphere(location, ST_GeomFromText(POINT(120.1536 30.2875))) 500 ORDER BY dist LIMIT 20;这个SQL从业务语义上完全正确以某个坐标为中心算出500米范围内的门店按距离排序取前20个。用ST_Distance_Sphere算球面距离逻辑上也没毛病。但问题就出在这个“逻辑没毛病”上。表面看起来空间索引应该能帮忙把“500米内”的门店先筛出来然而实际上MySQL压根没有用上空间索引直接做了全表扫描加全量距离计算加临时排序。1.2 第一份EXPLAIN索引躺在possible_keys里却没人用我当时的操作路径很标准先看慢日志锁定SQL然后EXPLAIN看一眼执行计划结果就是这份让我失眠的计划字段当前值期望值tableshopshoptypeALLrange/refpossible_keysidx_locationidx_locationkeyNULLidx_locationrows3314528应该大幅下降ExtraUsing where; Using filesortUsing index condition最气人的就在这里优化器明明知道有idx_location这个空间索引possible_keys里也列出来了但最终key却是NULL意思就是“我看到了但我不想用”。然后rows直接飙到331万Extra里还挂着Using filesort。你要是在这个节骨眼上查资料很容易看到一大堆“MySQL空间索引失效”的帖子。我当时就差直接冲到官方Bug列表里发帖了。但我忍住了决定按部就班排查一遍。事实证明忍住了是对的。2. 排查链路从怀疑官方Bug到发现是我用错了2.1 先核对版本和官方Issue排除已知缺陷排查这类问题第一步永远不是改代码而是确认自己用的版本有没有已知的硬伤。我线上当时的版本是MySQL 8.0.28InnoDB引擎。关于空间索引的历史背景得先补一句MySQL 5.6及更早版本InnoDB根本不支持空间索引你要用只能换MyISAM5.7开始InnoDB才支持SPATIAL INDEX但很多边界条件还不完善到了8.0相关实现才逐渐走向成熟。我在官网Bug系统和一些技术社区翻了一圈8.0.28这个版本确实出现过一些空间索引相关的异常报告但大多集中在特定的小版本、特定的几何数据类型组合上和我这个点查询范围筛选的场景对不上。而且那些能被实锤的bug多半有非常明确的触发条件不会像我这样无声无息地“索引失效”。初步判断不是版本硬伤大概率是使用姿势问题。2.2 FORCE INDEX无效空间索引不是你想强推就能推既然优化器不用最容易想到的粗暴办法就是FORCE INDEX。我试了SELECT id, name FROM shop FORCE INDEX (idx_location) WHERE ST_Distance_Sphere(location, ST_GeomFromText(POINT(120.1536 30.2875))) 500;结果很不给面子。要么直接报错要么执行计划里依然无视这个索引。FORCE INDEX对普通B树索引很管用但对空间索引基本属于“查无此功能”。这背后的原因到后面拆解机制时会细说简单一句话空间索引的使用条件比B树索引苛刻得多它不是“你想用就能用”而是必须满足特定的查询形态优化器才可能把它纳入执行路径。2.3 检查列定义与数据SRID和NULL这两个隐形前提既然强推不行那就静下心来看表结构。我重新执行了一次SHOW CREATE TABLE shop注意到一个问题location point NOT NULL后面并没有SRID 4326。这就是第一个隐形坑的影子了。在MySQL 8.0里如果你想在空间列上使用空间索引官方文档其实写得很清楚列必须定义为NOT NULL并且最好显式声明SRID。如果你不声明SRIDMySQL会把该列的坐标系默认当成一个抽象的平面坐标SRID 0和真实世界的经纬度SRID 4326完全是两码事。一个没有SRID的空间列就算你把数据当成经纬度存进去MySQL也只是把它当作平面坐标来处理空间索引的可用性会大打折扣。另外我还顺手查了数据里有没有NULL值。虽然这条查询本身不涉及NULL但空间索引在构造R树时对整个列的非空要求是硬性的。NULL值既没有MBR最小外包矩形也无法放进R树的叶子节点。8.0里建空间索引时通常会被强制要求列NOT NULL但如果是5.7时代建的老表迁移上来后未必完全符合新版本的规范。2.4 用最小用例复现问题一下缩小了查到这里我决定把SQL拆到最小化用最朴素的写法测试空间索引到底能不能生效。我把ST_Distance_Sphere换成MBRContains手动构造了一个以目标点为中心的矩形查询窗口SELECT id, name FROM shop WHERE MBRContains( ST_GeomFromText(POLYGON(( 119.1536 29.2875, 121.1536 29.2875, 121.1536 31.2875, 119.1536 31.2875, 119.1536 29.2875 ))), location ) LIMIT 20;EXPLAIN一看key字段终于变成了idx_locationrows也从331万降到了几千。这说明两个问题MySQL的空间索引本身没坏它能正常识别“空间窗口类查询”。导致不走索引的根源在于我最初的SQL写法不是空间索引能处理的查询形态。3. 机制拆解空间索引和B树索引的思维方式完全不同3.1 R树与MBR空间索引到底在索引什么很多人对空间索引的理解停留在“我有一个坐标字段建了索引就应该能加速所有和坐标有关的查询”。这个想法放在B树索引上是成立的放在空间索引上就完全错了。空间索引底层是R树不是B树。R树里每一个节点存的不是完整的数据行而是一个个几何对象的最小外包矩形也就是MBR。树结构会把这些MBR按空间位置进行层级聚合上层节点的MBR包住下层所有MBR。查询的时候数据库会拿查询窗口去和树上的MBR做“是否相交”的判断快速剔除那些完全不搭界的区域。你可以把R树的查询想象成手机地图的缩放过程。你要找“杭州市区里的咖啡馆”地图应用不可能把全世界所有咖啡馆都拉出来算一遍而是先缩放到杭州的范围再在这个范围内找。R树干的就是这件事查询窗口定位到杭州的MBR然后顺着树枝往下走只触碰和窗口相交的子节点其余的直接跳过。但请注意一个核心前提数据库需要一个明确的“查询窗口”。这个窗口只能由空间函数构造出来比如MBRContains、MBRIntersects、ST_Contains、ST_Intersects等。如果你没有提供这样的窗口而是直接算一个点和另一个点的距离R树根本不知道该从哪里开始裁剪那就只能全表扫了。3.2 为什么ST_Distance_Sphere不能直接命中索引我最初的SQL恰好踩中这个坑ST_Distance_Sphere(location, 固定点) 500这个条件在数据表的每一行上都要做一次实打实的距离计算。对于B树来说这种“算完之后再比较”的谓词叫SARGable谓词但空间索引不搞这一套它需要的是“先划定窗口再看窗口里的点”而不是“算完每个点再决定要不要”。有人可能会问MySQL能不能先拿500米估算出一个圆或者外接正方形然后去R树上查最后再精确过滤理论上可以这也是很多GIS数据库的常见优化策略但MySQL目前的空间索引实现没有把“距离谓词”自动转换成“窗口谓词”这一步。文档里对空间索引使用条件的描述多数情况下只提到“使用空间关系函数如MBRContains”并不会帮你做距离谓词改写。所以想让距离查询走空间索引必须自己动手把查询拆成两段第一段用MBRContains或ST_Contains做一个粗略的窗口筛选把候选集缩到很小第二段再在这个候选集上算ST_Distance_Sphere并排序。这个操作模式打LBS开发第一天起就该刻在脑子里。3.3 SRID不匹配8.0时代的第二个“伪bug”来源除了查询形态不对SRID不匹配是另一个特别容易被当成bug的场景。8.0要求空间列有显式SRID同时空间函数的参数几何体最好也要携带SRID。我当时的库里location列在修复后是SRID 4326但如果我构造查询窗口时空参数没带SRID那么ST_GeomFromText(POLYGON(...))默认生成的就是一个SRID 0的几何体。你把SRID 0的窗口和SRID 4326的列放在同一个空间函数里处理MySQL会认为坐标系不匹配轻则空间索引拒绝参与运算重则直接抛错。实际的SQL实践中很多人栽在这个地方-- 错误示范第二个参数缺失生成SRID 0的几何体 ST_GeomFromText(POINT(120.1536 30.2875)) -- 正确写法显式指定SRID 4326 ST_GeomFromText(POINT(120.1536 30.2875), 4326)乍一看就少了一个参数索引行为天差地别你说气不气。但这种“差之毫厘谬以千里”的隐性约束才是把使用者引向“官方bug”结论的罪魁祸首。4. 修复落地粗筛精算的两段式查询改造4.1 先确保表结构符合空间索引的使用前提在改SQL之前我先把表结构规范了一遍。如果你也遇到类似问题可以考虑按这个顺序检查-- 1. 确认空间列是否满足默认要求 SHOW CREATE TABLE shop; -- 2. 如果列没有SRID且当前MySQL版本允许手动修改按以下方式处理 -- 注意5.7和8.0对SRID语法的支持有差异操作前先备份 ALTER TABLE shop MODIFY COLUMN location POINT NOT NULL SRID 4326; -- 3. 重建空间索引确保索引元数据与最新列定义一致 ALTER TABLE shop DROP INDEX idx_location, ADD SPATIAL INDEX idx_location(location); -- 4. 刷新优化器统计信息 ANALYZE TABLE shop;在8.0版本里空间索引要求列NOT NULL且显式声明SRID这是一个硬约束。如果你的表是从5.7时代迁过来的老表或者当初建表时手滑漏了SRID这里就是需要补课的地方。但有一点务必注意改列定义和重建索引都属于重量级操作在线执行会锁表或影响读写务必安排在业务低峰期并提前备份。另外重建索引前最好确认一下数据表里有没有NULL值。SPATIAL INDEX对NULL基本是零容忍的哪怕有一个NULL索引构建都极有可能失败或者建出来的索引在后续查询中表现诡异。4.2 改写后的SQLMBRContains粗筛ST_Distance_Sphere精算表结构没问题之后SQL改写才是核心。最终上线的查询长这样SELECT id, name, ST_Distance_Sphere( location, ST_GeomFromText(POINT(120.1536 30.2875), 4326) ) AS dist FROM shop WHERE MBRContains( ST_GeomFromText(POLYGON(( 119.6536 29.7875, 120.6536 29.7875, 120.6536 30.7875, 119.6536 30.7875, 119.6536 29.7875 )), 4326), location ) ORDER BY dist LIMIT 20;这里粗筛用的矩形是以目标点为中心、向外扩展约0.5度经纬度得到的。0.5度在纬度方向大约是55公里在经度方向上会随纬度变化所以这个矩形的范围远远大于实际需要的500米。大归大它的作用只是把300万行的全表扫描缩小到可能几千行然后再在这几千行里精确计算距离并排序。这样改造之后ST_Distance_Sphere依然参与了排序和过滤但它的计算范围已经从300万行降到了几千行负担完全可以接受。而且MySQL执行计划里会明显地走到idx_location这个空间索引上。4.3 修复前后对比不仅仅是省了3秒钟修复完成后我特意把改造前后的两个版本都压了一遍数据差异非常直观指标改造前改造后执行计划typeALLrange实际扫描行数33145283276单次查询耗时3.2s0.12sP95接口耗时3.1s96ms更值钱的收益是慢查询日志里的Rows_examined从百万级降到了几千级数据库的CPU使用率直接掉了一半。之前接口慢不只是慢在“多算了点距离”而是每一次请求都在做全表扫描QPS一高CPU直接被打满。这里补一个细节改完之后如果你发现执行计划还是没走索引可以跑一下EXPLAIN FORMATTREE甚至EXPLAIN ANALYZE8.0会对空间索引的实际路径给出更多提示。比如Using index condition这种提示就说明R树已经在索引层做了初步裁剪。5. 复盘总结MySQL空间索引的使用边界与自查清单5.1 适合与不适合空间索引的场景这个坑踩完之后我对MySQL空间索引的态度就变成了“边界清晰、用对地方就是利器”。适合它的场景典型的就是“给定一个区域窗口把窗口里的几何点捞出来”比如附近的店、附近的共享单车、某一个多边形围栏里的设备。这种窗口类检索是R树的看家本领效率非常高。不适合它的场景也很明确最常见的是两类第一按经纬度做精确等值或简单范围查询。如果你的表里有独立的lng、lat两列平时习惯写WHERE lat BETWEEN ... AND lng BETWEEN ...那空间索引帮不上任何忙。这种场景老老实实建普通B树索引反而更直观因为lat/lng是标量字段B树天生擅长处理范围查询。空间索引是为几何对象之间的空间关系设计的不是为“某个数值落在区间里”设计的。第二必须直接按距离排序返回全量结果。比如“按距离从近到远列出100公里内所有门店”如果第一筛选条件天然就是距离那MySQL空间索引没办法直接支持因为它只会做窗口粗筛不会自动把“距离很近”变成“窗口很小”。遇到第二种需求我一般建议这么处理先用一个比较宽松的正方形或圆形窗口做候选集粗筛再在候选集上精确排序。如果业务场景非常复杂需要做逆地理编码、复杂的多边形容斥、或者海量点实时更新那就别硬用MySQL了直接考虑专业的地理数据方案吧。5.2 空间索引自查清单排查了大半夜我把所有能想到的检查点整理成了下面这张清单。下次线上再遇到“空间索引明明建了却不生效”的问题顺着过一遍基本都能定位检查项要求不满足时的典型现象表引擎InnoDB5.7或MyISAM5.6及更早版本InnoDB无法创建空间索引空间列类型POINT、LINESTRING、POLYGON等非几何类型无法建空间索引列是否NOT NULL是8.0索引构建失败或查询异常是否显式声明SRID是8.0推荐列级SRID坐标系被当成SRID 0空间语义错乱查询是否使用空间函数MBRContains/ST_Contains等普通BETWEEN等谓词无法命中R树构造窗口时是否指定SRID与列SRID保持一致报SRID不匹配错误或不走索引是否直接对距离函数排序否应改为窗口粗筛距离精算全表扫描filesort统计信息是否更新ANALYZE TABLE优化器误判全表扫描更快5.3 一点额外的建议别急着把“不符合预期”定性为bug这次排查最浪费时间的环节其实不是最后定位到查询写法而是前面花了好几个小时在“认定这是官方bug”的执念上。很多看似诡异的行为只要把EXPLAIN结果、表结构定义、查询形态三者对照起来看答案往往就在文档的字缝里。最后再分享一个我很习惯的小技巧遇到空间索引的怪问题先在测试环境用一个几十万行的小表、最小化的SQL复现一遍不行就从最简单的一句MBRContains开始试逐步往真实SQL上加条件。这个过程能很快帮你判断问题出在索引本身、表结构、还是业务SQL层。比起盯着慢日志干瞪眼亲手做最小复现要靠谱得多。