MariaDB中FROM子句带派生表的6公里范围事件查询性能异常排查

你观察到的所有反常执行表现，本质是MariaDB 10.3版本对CTE/派生表的优化器决策缺陷导致的，和SQL逻辑本身无关。

核心优化器偏差逻辑 #

从你提供的第一版执行计划可以看到，优化器选择了完全低效的JOIN驱动顺序：

1. 从大表cours2 c开始驱动，先筛出179行满足active_today='1' AND subject_id=404的记录
2. 逐行关联cours_locations cl，平均每个cours_id匹配2行关联数据，中间结果集扩大到358行
3. 逐行关联locations_locations ll，平均每个location_id匹配7行层级映射，中间结果集进一步扩大到2500+行
4. 最后才关联仅返回4行结果的距离计算CTE，对大的中间结果集做最后过滤

这个决策的核心问题是，优化器完全没有意识到CTE的实际结果集规模极小，它对派生CTE的行数估算值达到了10行，和大表的关联行数估算混在一起，彻底选错了驱动表。

反常现象的对应解释 #

你测试时观察到的几个现象，都完全匹配这个优化器的决策逻辑：

• 给CTE加上LIMIT 5时，优化器会直接识别到派生表的结果集非常小，强行调整JOIN顺序，先拿CTE的小结果集作为驱动去关联其他表，整体耗时直接降到300ms
• 当CTE的LIMIT值设为15（超过CTE实际返回的4行），优化器又会恢复之前从大表开始驱动的低效顺序，耗时重新回到60s水平
• 移除CTE中的HAVING子句时，因为HAVING是对经纬度计算字段的过滤，没有表统计信息可以参考，优化器对CTE的行数估算会进一步偏高，更倾向于选择大表驱动的顺序，耗时进一步上升

两层CTE版本的优化原理 #

你后来拆出的location_distances_hierarchy CTE写法，本质是绕开了原来的优化器决策误区：
它提前把4行的距离点位结果，和locations_locations表完成关联，得到规模仅几十行的“距离点位→上级区域ID”映射关系，让优化器直接看到这部分派生表的规模极小，自然会选择从这个小结果集开始驱动关联。
对应的执行路径从“扫大表→一路放大中间结果集→最后匹配小表”，变成“先拿小结果集映射→再匹配cours_locations→最后匹配cours2表”，全程中间结果集始终保持在几十行的规模，因此执行速度能直接达到300ms。

可复用的优化建议 #

• 对于MariaDB 10.3这类对CTE优化支持较弱的版本，涉及小结果集CTE的查询，尽量将“小结果集+关联映射”的逻辑提前封装为两层CTE，避免优化器出现决策偏差
• 必要时可以通过STRAIGHT_JOIN语法强制指定JOIN顺序，将小结果集的距离表放在最左侧作为驱动表，绕开优化器的错误估算
• 后续如果可升级数据库版本，MariaDB 10.6及以上版本对CTE的结果集行数估算、半连接优化做了大量修复，不需要这类绕弯的写法也能拿到正确的执行计划

内容的提问来源于stack exchange，提问作者Cédric