基于PostGIS的慢查询引起的空间索引提升实践_st_dwithin查询慢(1)

网上学习资料一大堆，但如果学到的知识不成体系，遇到问题时只是浅尝辄止，不再深入研究，那么很难做到真正的技术提升。

需要这份系统化资料的朋友，可以点击这里获取

一个人可以走的很快，但一群人才能走的更远！不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人，都欢迎加入我们的的圈子（技术交流、学习资源、职场吐槽、大厂内推、面试辅导），让我们一起学习成长！

这里可以看到很详细的请求地址，下面将参数列出来：

序号	参数名	值
1	request url	http://192.168.31.64:8080/earthqadmin/eq/mapview/villageinfo
2	request method	post
3	payload	lng 117.03 lat 34.32

通过这里的请求url，我们可以找到后台对应的接口。由于这里没有采用网关模式部署，因此直接对接的是系统后台。

2、后台应用定位

在后台代码当中，我们找到对应的controller类，并找到了对应的方法定义，接口代码如下：

/**
* 震中位置5公里分析
* @param lng 经度
* @param lat 纬度
* @return
*/
@PostMapping("/villageinfo")
@ResponseBody
public AjaxResult earthinfo(String lng,String lat){
    List<EarthquakeVillageVo> list = earthquakeInfoService.findListByLngLat(lng, lat);
    AjaxResult ar = AjaxResult.success();
    ar.put("data", list);
    return ar;
}

代码比较简单，直接接收参数，并将参数传递到service层（这里并不会消耗时间），我们来看看Service的定义。

@Override
public List<EarthquakeVillageVo> findListByLngLat(String lng, String lat) {
	return villageMapper.findListByLngLat(" 'point(" +lng+" "+lat+")' ");
}

通过代码发现，这里也没有进行复杂计算，继续来看Mapper的处理逻辑：

static final String FIND_LIST_BY_LNG_LAT = "<script>"
			+ "with bp as ( select st_geomfromtext(${pointinfo},4326) :: geography tp ) "
			+ "select st_distance(t.geom :: geography, bp.tp) dist,t.address,t.village_name,t.lng,t.lat from biz_village t, "
			+ " bp where st_dwithin(t.geom :: geography, bp.tp, 5000 ) order by dist "
			+ "</script>";
@Select(FIND_LIST_BY_LNG_LAT)
List<EarthquakeVillageVo> findListByLngLat(@Param("pointinfo")String pointinfo);

3、找到问题所在

通过接口的代码分析，我们发现其逻辑非常简单，最终只是去数据库进行空间查询。那由此我们可以将问题排查的方向从应用代码转移到数据库中。应该是数据库的查询性能导致了查询性能的下降。顺着这种思路，我们来进行数据库调优，尝试优化查询性能。

二、空间索引优化

为了验证我们的猜想，也同时为了让系统性能有一个提升，我们将执行的sql语句复制到navicate中进行性能验证。sql语句如下：

with bp as 
(
	select st_geomfromtext('point(111.99 40.34)',4326) :: geography tp
)
select st_distance(t.geom :: geography, bp.tp),t.*  from biz_village t,bp  where st_dwithin(t.geom :: geography, bp.tp, 5000);

1、数据库查询

数据库的表biz_village表的数据量在65W左右，这个量其实不算大。毕竟空间数据，上千万条数据都是有可能的。

首先我们在客户端工具navicate中执行上述语句，看一下实际的查询性能怎么样？来看一下实际的运行结果。执行时间1.79秒，确实有点慢。

上述语句在数据库中执行，确实比较慢，一共耗时1.8秒，导致后台接口的性能很差。由此证明我们的优化方向是正确的，的确是数据查询性能低导致访问慢。因此问题的关键就变成了查询优化。通常在数据库中的优化步骤是什么呢？优化成本最低的是索引。我们来看一下实际语句，这里用到一个空间函数st_dwithin()。

with bp as 
(
	select st_geomfromtext('point(111.99 40.34)',4326) :: geography tp
)
select st_distance(t.geom :: geography, bp.tp),t.*  from biz_village t,bp  where st_dwithin(t.geom :: geography, bp.tp, 5000);

首先我们来看一下数据库表索引，

惊讶的发现，表里面没有设计空间索引，因此来看一下执行执行计划：

很明显，这里面没有任何的索引生效，似乎查询慢也是意料之中。既然怀疑是索引问题，那么我们来创建数据库索引。

2、创建空间索引

这里使用以下语句进行空间所用的创建，创建索引耗时将近10秒。这也是为什么索引要提前创建，不然这些索引创建的时间成本也是挺高的，随着数据量的增大是个很恐怖的数字。

create index idx_biz_village_geom on biz_village using gist(geom)
> OK
> 时间: 9.447s

在创建了空间索引后，来看一下查询性能是否提升。

然而并没有什么提升，难道是索引无效吗？继续打开执行计划看一下。发现其依然没有走索引。是不是很奇怪。

肯定有细心的朋友发现了问题，我们仔细来看一下where条件。

where st_dwithin(t.geom :: geography, bp.tp, 5000);

在st_dwithin函数中，我们把原本的geometry类型转换为了geography，之所以转换是因为我们想精确计算范围，比如5公里。众所周知，在4326坐标系下，如果使用geomerty的距离计算单位是度，而不是我们熟悉的米。我们先将转换去掉，先来验证索引是否有效。

仔细对比一下之前的查询计划，发现这里用到了索引查询，0.1表示0.1度，并不是0.1米。请各位朋友注意。 再来执行以下sql语句，是不是很惊喜，查询时间只需要0.006秒。这说明空间索引的构建对于提升空间查询速度帮助很大。

3、geography索引

这里需要思考一个问题，我们给geometry设计了索引，那geography为什么没有用呢？这也好理解，这两个其实都是空间数据库中空间数据的两种表达。使用geography主要为了精确的计算距离，而使用度来转换的话，不同坐标系下会有一定的误差。但是怎么进行geography的数据索引构建呢？可以使用下面语句来进行。

create index idx_biz_village_geom_gp on biz_village using gist((geom::geography))
> OK
> 时间: 10.513s

还有兄弟不知道网络安全面试可以提前刷题吗？费时一周整理的160+网络安全面试题，金九银十，做网络安全面试里的显眼包！

王岚嵚工程师面试题（附答案），只能帮兄弟们到这儿了！如果你能答对70%，找一个安全工作，问题不大。

对于有1-3年工作经验，想要跳槽的朋友来说，也是很好的温习资料！

【完整版领取方式在文末！！】

93道网络安全面试题

内容实在太多，不一一截图了

黑客学习资源推荐

最后给大家分享一份全套的网络安全学习资料，给那些想学习 网络安全的小伙伴们一点帮助！

对于从来没有接触过网络安全的同学，我们帮你准备了详细的学习成长路线图。可以说是最科学最系统的学习路线，大家跟着这个大的方向学习准没问题。

1️⃣零基础入门

① 学习路线

对于从来没有接触过网络安全的同学，我们帮你准备了详细的学习成长路线图。可以说是最科学最系统的学习路线，大家跟着这个大的方向学习准没问题。

② 路线对应学习视频

同时每个成长路线对应的板块都有配套的视频提供：

网上学习资料一大堆，但如果学到的知识不成体系，遇到问题时只是浅尝辄止，不再深入研究，那么很难做到真正的技术提升。

需要这份系统化资料的朋友，可以点击这里获取

一个人可以走的很快，但一群人才能走的更远！不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人，都欢迎加入我们的的圈子（技术交流、学习资源、职场吐槽、大厂内推、面试辅导），让我们一起学习成长！