阿里云服务器建站,云主机怎么装网站,织梦网站tag怎么做,平面设计网页目录 前言
一、问题定位
1、前端接口定位
2、后台应用定位
3、找到问题所在
二、空间索引优化
1、数据库查询
2、创建空间索引
3、geography索引
4、再看前端响应
总结 前言 这是一个真实的案例#xff0c;也是一个新入门的工程师很容易忽略的点。往往在设计数据库的…
目录 前言
一、问题定位
1、前端接口定位
2、后台应用定位
3、找到问题所在
二、空间索引优化
1、数据库查询
2、创建空间索引
3、geography索引
4、再看前端响应
总结 前言 这是一个真实的案例也是一个新入门的工程师很容易忽略的点。往往在设计数据库的时候忘了进行索引的构建进而由此导致了查询的性能很拉胯。下面来简单还原一下事件的经过。在之前的博客中原文博客地址如下基于SpringBoot和PostGIS的震中影响范围可视化实践、解决Thymeleaf的地震震中距离展示[[]]双引号报错的问题这两篇博客中都详细介绍了如何进行空间查询的实战。 在这些应用的建设过程当中在进行震中范围定位时我们发现了一个问题就是页面请求后端的时间比较长界面要等很久才能获取结果。 下面是我们进行网络跟踪的结果。 通过观察network网络请求可以很直观的看到有一个接口的请求时间很慢就是获取震中位置的接口最慢的接近4秒才返回数据。 到底是什么原因导致了请求这么慢呢 本文将详细分析在PostGIS中怎么排查数据查询慢接口如何进行空间索引的构建以及使用空间索引的正确姿势通过空间索引的构建提升空间查询的效率。对致力于空间查询优化的小伙伴们有一定的参考价值。闲言少叙下面正式进入正题。
一、问题定位 对于一个工程师而言遇到问题是家常便饭。比如上面这个慢查询的问题首先这个问题很容易重现只要点击一个地震信息点就会请求后台。我们通过去后台调试查看具体的问题。从前端作为入口到后台方法的定义。我们来谈谈如何进行接口调试以至于定位查询慢的问题。
1、前端接口定位 在浏览器中打开调试窗口在刚刚打开的调试窗口中可以看到网络请求一栏。在这一栏中我们可以看到访问比较慢的接口地址。 这里可以看到很详细的请求地址下面将参数列出来
序号参数名值1request urlhttp://192.168.31.64:8080/earthqadmin/eq/mapview/villageinfo2request methodpost3payloadlng 117.03 lat 34.32 通过这里的请求url我们可以找到后台对应的接口。由于这里没有采用网关模式部署因此直接对接的是系统后台。
2、后台应用定位 在后台代码当中我们找到对应的controller类并找到了对应的方法定义接口代码如下
/**
* 震中位置5公里分析
* param lng 经度
* param lat 纬度
* return
*/
PostMapping(/villageinfo)
ResponseBody
public AjaxResult earthinfo(String lng,String lat){ListEarthquakeVillageVo list earthquakeInfoService.findListByLngLat(lng, lat);AjaxResult ar AjaxResult.success();ar.put(data, list);return ar;
} 代码比较简单直接接收参数并将参数传递到service层这里并不会消耗时间我们来看看Service的定义。
Override
public ListEarthquakeVillageVo 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)
ListEarthquakeVillageVo 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空间索引创建完毕后再来看执行计划 再次执行空间查询效果依然很明显。优化后执行时间耗时0.009秒。 从1.8秒到0.009秒速度提升了 200倍。
4、再看前端响应 经过上述的空间索引优化后我们来看一下界面的展示情况。经过数据库优化界面访问速度大幅提升可以实现毫秒级响应。用户体验进一步的提升。 总结 在应用程序的开发过程中一定要重视索引的使用和创建好的索引应用能提升应用的查询性能。不好的索引设计将会使用户体验大大降低。最后提一点最佳实践建议针对于空间索引一定要提前建立因为空间索引的创建耗时太长。在上述的空间索引中既有geometry的空间索引又有geography的索引个人建议如果不是为了精确计算米级查询只需要创建geometry索引即可。这样也减少空间索引的创建成本。 以上就是本文的主要内容本文将详细分析在PostGIS中怎么排查数据查询慢接口如何进行空间索引的构建以及使用空间索引的正确姿势通过空间索引的构建提升空间查询的效率。对致力于空间查询优化的小伙伴们有一定的参考价值。行文仓促定有不当之处如有不当之处欢迎各位专家和朋友批评指正十分感谢。在空间索引优化过程中参考了以下博客资料站在巨人的肩膀上才能看得更高更远。
参考资料地址
1、postgis性能优化实战之-周边搜索查询
