文章目录
- 前言
- 一、附近门店功能
- redis实现
- mongodb实现
- 总结
前言
最近公司项目需要实现附近的门店功能，通过查询资料发现很多方法都可以实现。
包括Mysql,Redis,Mongodb,PostgreSQL等
其中分别选择了redis和mongodb进行实现。
一、附近门店功能
redis实现
redis4.0.14版本，使用redis自带的geo命令来实现功能。具体的命令详情可参考官方文档 https://redis.io/commands/geoadd
1、引入要使用的jar包，工程是springboot项目，直接maven引入redis依赖
<dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-data-redis</artifactId></dependency><dependency><groupId>redis.clients</groupId><artifactId>jedis</artifactId></dependency>
2、代码实现redis操作
包括了插入、删除数据，以及查询附近的数据
@Data
public class Geo<T> {private T object;private double distance;
}@Component
public class RedisGeo<T> {@Resource(name = "redisTemplateBusiness")private RedisTemplate redisTemplate;public void setGeo(String key, double longitude, double latitude, T object) {redisTemplate.opsForGeo().add(key, new Point(longitude, latitude), object);}public void removeGeo(String key, T object) {redisTemplate.opsForGeo().remove(key, object);}public List<Geo<T>> getNearbyByGeo(String key, double longitude, double latitude, int distance, int limit) {List<Geo<T>> geos = new ArrayList<>();BoundGeoOperations boundGeoOperations = redisTemplate.boundGeoOps(key);Point point = new Point(longitude, latitude);Circle within = new Circle(point, distance);RedisGeoCommands.GeoRadiusCommandArgs geoRadiusArgs = RedisGeoCommands.GeoRadiusCommandArgs.newGeoRadiusArgs();geoRadiusArgs = geoRadiusArgs.includeDistance();geoRadiusArgs.limit(limit);geoRadiusArgs.sortAscending();GeoResults<RedisGeoCommands.GeoLocation<Object>> geoResults = boundGeoOperations.radius(within, geoRadiusArgs);List<GeoResult<RedisGeoCommands.GeoLocation<Object>>> geoResultList = geoResults.getContent();Geo geo;for (GeoResult<RedisGeoCommands.GeoLocation<Object>> geoResult : geoResultList) {geo = new Geo();geo.setObject(geoResult.getContent());geo.setDistance(geoResult.getDistance().getValue());geos.add(geo);}return geos;}
}
3、初始化数据
选择一个经纬度坐标，插入40万个周边的坐标点数据
@Data
public class StoreBean {private int id;private String name;private double[] loc;private double dist;}
public void insertRedisData(){double longitude=114.068245;double latitude=22.546195;StoreBean storeBean;for(int i=1;i<=100000;i++){storeBean=new StoreBean();storeBean.setId(i);storeBean.setName("门店"+i);double d=Double.parseDouble(i+"")/10000;BigDecimal b1 = new BigDecimal(longitude+d);BigDecimal b2 = new BigDecimal(latitude);redisGeo.setGeo("store",b1.setScale(6, BigDecimal.ROUND_HALF_UP).doubleValue() ,b2.setScale(6, BigDecimal.ROUND_HALF_UP).doubleValue(), storeBean);}for(int i=1;i<=100000;i++){storeBean=new StoreBean();storeBean.setId(i+100000);storeBean.setName("门店"+(i+100000));double d=Double.parseDouble(i+"")/10000;BigDecimal b1 = new BigDecimal(longitude);BigDecimal b2 = new BigDecimal(latitude+d);redisGeo.setGeo("store",b1.setScale(6, BigDecimal.ROUND_HALF_UP).doubleValue() ,b2.setScale(6, BigDecimal.ROUND_HALF_UP).doubleValue(), storeBean);}for(int i=1;i<=100000;i++){storeBean=new StoreBean();storeBean.setId(i+200000);storeBean.setName("门店"+(i+200000));double d=Double.parseDouble(i+"")/10000;BigDecimal b1 = new BigDecimal(longitude-d);BigDecimal b2 = new BigDecimal(latitude);redisGeo.setGeo("store",b1.setScale(6, BigDecimal.ROUND_HALF_UP).doubleValue() ,b2.setScale(6, BigDecimal.ROUND_HALF_UP).doubleValue(), storeBean);}for(int i=1;i<=100000;i++){storeBean=new StoreBean();storeBean.setId(i+300000);storeBean.setName("门店"+(i+300000));double d=Double.parseDouble(i+"")/10000;BigDecimal b1 = new BigDecimal(longitude);BigDecimal b2 = new BigDecimal(latitude-d);redisGeo.setGeo("store",b1.setScale(6, BigDecimal.ROUND_HALF_UP).doubleValue() ,b2.setScale(6, BigDecimal.ROUND_HALF_UP).doubleValue(), storeBean);}}
4、查询数据
查询该坐标附近200米的点，并取前20条
public void testRedis() {double longitude=114.068245;double latitude=22.546195;int distance = 2000;int limit=20;List<Geo<StoreBean>> list = redisGeo.getNearbyByGeo("store", longitude, latitude, distance,limit);if (CollectionUtils.isNotEmpty(list)) {for (Geo<StoreBean> geo : list) {System.out.println(JsonUtil.toJson(geo.getObject()) + "----------" + geo.getDistance());}}}
查询结果会根据距离远近排好序返回
{"name":{"id":1,"name":"门店1","loc":null,"dist":0.0},"point":null}----------10.1641
{"name":{"id":200001,"name":"门店200001","loc":null,"dist":0.0},"point":null}----------10.2266
{"name":{"id":300001,"name":"门店300001","loc":null,"dist":0.0},"point":null}----------11.1206
{"name":{"id":100001,"name":"门店100001","loc":null,"dist":0.0},"point":null}----------11.157
{"name":{"id":2,"name":"门店2","loc":null,"dist":0.0},"point":null}----------20.6339
{"name":{"id":200002,"name":"门店200002","loc":null,"dist":0.0},"point":null}----------20.6964
{"name":{"id":300002,"name":"门店300002","loc":null,"dist":0.0},"point":null}----------22.1145
{"name":{"id":100002,"name":"门店100002","loc":null,"dist":0.0},"point":null}----------22.1509
{"name":{"id":3,"name":"门店3","loc":null,"dist":0.0},"point":null}----------30.5529
{"name":{"id":200003,"name":"门店200003","loc":null,"dist":0.0},"point":null}----------30.6154
{"name":{"id":300003,"name":"门店300003","loc":null,"dist":0.0},"point":null}----------33.3911
{"name":{"id":100003,"name":"门店100003","loc":null,"dist":0.0},"point":null}----------33.4275
{"name":{"id":4,"name":"门店4","loc":null,"dist":0.0},"point":null}----------41.023
{"name":{"id":200004,"name":"门店200004","loc":null,"dist":0.0},"point":null}----------41.0855
{"name":{"id":300004,"name":"门店300004","loc":null,"dist":0.0},"point":null}----------44.3861
{"name":{"id":100004,"name":"门店100004","loc":null,"dist":0.0},"point":null}----------44.4225
{"name":{"id":5,"name":"门店5","loc":null,"dist":0.0},"point":null}----------51.4932
{"name":{"id":200005,"name":"门店200005","loc":null,"dist":0.0},"point":null}----------51.5557
{"name":{"id":300005,"name":"门店300005","loc":null,"dist":0.0},"point":null}----------55.663
{"name":{"id":100005,"name":"门店100005","loc":null,"dist":0.0},"point":null}----------55.6995
5、性能测试
服务器 CPU 2.00GHz 4核
基础数据40W条，本机16线程分别进行1万次附近200m,2000m,20000m查询结果如下:
----200m
mean rate = 1740.76 calls/second1-minute rate = 1740.60 calls/second5-minute rate = 1740.60 calls/second15-minute rate = 1740.60 calls/secondmin = 1.70 millisecondsmax = 152.20 millisecondsmean = 9.11 millisecondsstddev = 6.79 millisecondsmedian = 7.61 milliseconds75% <= 9.06 milliseconds95% <= 17.78 milliseconds98% <= 27.31 milliseconds99% <= 34.27 milliseconds99.9% <= 62.15 milliseconds---2km
mean rate = 591.55 calls/second1-minute rate = 578.13 calls/second5-minute rate = 575.03 calls/second15-minute rate = 574.48 calls/secondmin = 7.33 millisecondsmax = 167.37 millisecondsmean = 27.84 millisecondsstddev = 7.75 millisecondsmedian = 26.16 milliseconds75% <= 29.36 milliseconds95% <= 42.00 milliseconds98% <= 45.53 milliseconds99% <= 49.26 milliseconds99.9% <= 58.36 milliseconds-----20km
mean rate = 57.33 calls/second1-minute rate = 57.47 calls/second5-minute rate = 55.08 calls/second15-minute rate = 54.06 calls/secondmin = 25.43 millisecondsmax = 608.66 millisecondsmean = 291.61 millisecondsstddev = 48.28 millisecondsmedian = 286.57 milliseconds75% <= 310.07 milliseconds95% <= 370.45 milliseconds98% <= 414.06 milliseconds99% <= 433.93 milliseconds99.9% <= 608.53 milliseconds
从结果可以看出查询出的附近的门店越多，性能越慢
mongodb实现
mongodb版本4.4.4，由于从4.0版本起，已经废弃了geoNear命令，所以我们通过$geoNear aggregation 来实现
1、引入要使用的jar包，工程是springboot项目，直接maven引入redis依赖
<dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-data-mongodb</artifactId></dependency>
2、代码实现查询附近的店铺操作
直接使用mongoTemplate操作
@Data
public class StoreBean {private int id;private String name;private double[] loc;private double dist;}
@Service
public class StoreService {@Autowiredprivate MongoTemplate mongoTemplate;public List<StoreBean> findNear(Query query,double lng, double lat, double dist, int page, int pageSize){if(query==null){query=new Query();}List<AggregationOperation> aggregationList = new ArrayList<>();aggregationList.add(new GeoNearDocument(query, new Point(lng,lat), dist));aggregationList.add(Aggregation.skip((long)(page-1) * pageSize));aggregationList.add(Aggregation.limit(pageSize));Aggregation agg = Aggregation.newAggregation(aggregationList);AggregationResults<StoreBean> results = mongoTemplate.aggregate(agg, "store", StoreBean.class);return results.getMappedResults();}
}
public class GeoNearDocument implements AggregationOperation {private Document nearQuery;public GeoNearDocument(Query query, Point point, double maxDistance) {this.nearQuery = new Document("near", new Document("type", "Point").append("coordinates", new double[]{point.getX(), point.getY()})).append("distanceField", "dist").append("query", query.getQueryObject()).append("maxDistance", maxDistance).append("spherical", true);}@Overridepublic Document toDocument(AggregationOperationContext context) {Document command = context.getMappedObject(nearQuery);return new Document("$geoNear", command);}}
对应的mongo查询语句
db.getCollection('store').aggregate([ {$geoNear: {near: { type: "Point", coordinates: [ 114.06821, 22.546212 ] },distanceField: "dist", maxDistance: 200, spherical: true }}, {$skip:0}, {$limit:20}
])
3、初始化数据
选择一个经纬度坐标，插入40万个周边的坐标点数据,使用了批量插入功能，性能挺不错的
public void insertMongoData(){StoreBean storeBean = new StoreBean();double longitude=114.068245;double latitude=22.546195;BulkOperations ops = mongoTemplate.bulkOps(BulkOperations.BulkMode.UNORDERED, "store");for (int i = 1; i <= 100000; i++) {double d = Double.parseDouble(i + "") / 10000;storeBean.setId(i);storeBean.setName("门店" + i);BigDecimal b1 = new BigDecimal(longitude + d);BigDecimal b2 = new BigDecimal(latitude);storeBean.setLoc(new double[]{b1.setScale(6, BigDecimal.ROUND_HALF_UP).doubleValue(), b2.setScale(6, BigDecimal.ROUND_HALF_UP).doubleValue()});ops.insert(storeBean);if (i % 10000 == 0) {ops.execute();ops = mongoTemplate.bulkOps(BulkOperations.BulkMode.UNORDERED, "store");}}for(int i=1;i<=100000;i++){double d=Double.parseDouble(i+"")/10000;storeBean.setId(i+100000);storeBean.setName("门店" + (i+100000));BigDecimal b1 = new BigDecimal(longitude);BigDecimal b2 = new BigDecimal(latitude+d);storeBean.setLoc(new double[]{b1.setScale(6, BigDecimal.ROUND_HALF_UP).doubleValue(), b2.setScale(6, BigDecimal.ROUND_HALF_UP).doubleValue()});ops.insert(storeBean);if(i%10000==0){ops.execute();ops = mongoTemplate.bulkOps(BulkOperations.BulkMode.UNORDERED, "store");}}for(int i=1;i<=100000;i++){double d=Double.parseDouble(i+"")/10000;storeBean.setId(i+200000);storeBean.setName("门店" + (i+200000));BigDecimal b1 = new BigDecimal(longitude-d);BigDecimal b2 = new BigDecimal(latitude);storeBean.setLoc(new double[]{b1.setScale(6, BigDecimal.ROUND_HALF_UP).doubleValue(), b2.setScale(6, BigDecimal.ROUND_HALF_UP).doubleValue()});ops.insert(storeBean);if(i%10000==0){ops.execute();ops = mongoTemplate.bulkOps(BulkOperations.BulkMode.UNORDERED, "store");}}for(int i=1;i<=100000;i++){double d=Double.parseDouble(i+"")/10000;storeBean.setId(i+300000);storeBean.setName("门店" + (i+300000));BigDecimal b1 = new BigDecimal(longitude);BigDecimal b2 = new BigDecimal(latitude-d);storeBean.setLoc(new double[]{b1.setScale(6, BigDecimal.ROUND_HALF_UP).doubleValue(), b2.setScale(6, BigDecimal.ROUND_HALF_UP).doubleValue()});ops.insert(storeBean);if(i%10000==0){ops.execute();ops = mongoTemplate.bulkOps(BulkOperations.BulkMode.UNORDERED, "store");}}}
使用$geoNear需要为loc字段创建2dsphere索引，mongodb控制台创建索引
db.getCollection('store').createIndex({loc:"2dsphere"})
4、查询数据
查询该坐标附近200米的点，并取前20条。
如果有其他的查询条件，可以使用Query来进行数据的过滤
public void testMongodb(){double longitude=114.068245;double latitude=22.546195;double distance = 200;int page=1;int pageSize=20;List<StoreBean> results=storeService.findNear(new Query(),longitude,latitude,distance,page,pageSize);for(StoreBean storeBean:results){System.out.println(JsonUtil.toJson(storeBean));}}
查询结果会根据距离远近排好序返回
{"id":1,"name":"门店1","loc":[114.068345,22.546195],"dist":10.281082270657112}
{"id":200001,"name":"门店200001","loc":[114.068145,22.546195],"dist":10.281082272980736}
{"id":100001,"name":"门店100001","loc":[114.068245,22.546295],"dist":11.131884501985395}
{"id":300001,"name":"门店300001","loc":[114.068245,22.546095],"dist":11.131884502057591}
{"id":2,"name":"门店2","loc":[114.068445,22.546195],"dist":20.56216454244631}
{"id":200002,"name":"门店200002","loc":[114.068045,22.546195],"dist":20.562164544376795}
{"id":100002,"name":"门店100002","loc":[114.068245,22.546395],"dist":22.263769004327976}
{"id":300002,"name":"门店300002","loc":[114.068245,22.545995],"dist":22.26376900447237}
{"id":3,"name":"门店3","loc":[114.068545,22.546195],"dist":30.84324681436141}
{"id":200003,"name":"门店200003","loc":[114.067945,22.546195],"dist":30.84324681666289}
{"id":100003,"name":"门店100003","loc":[114.068245,22.546495],"dist":33.395653506415094}
{"id":300003,"name":"门店300003","loc":[114.068245,22.545895],"dist":33.39565350648729}
{"id":4,"name":"门店4","loc":[114.068645,22.546195],"dist":41.12432908631526}
{"id":200004,"name":"门店200004","loc":[114.067845,22.546195],"dist":41.12432908710427}
{"id":100004,"name":"门店100004","loc":[114.068245,22.546595],"dist":44.5275380079574}
{"id":300004,"name":"门店300004","loc":[114.068245,22.545795],"dist":44.52753800907162}
{"id":5,"name":"门店5","loc":[114.068745,22.546195],"dist":51.40541135767077}
{"id":200005,"name":"门店200005","loc":[114.067745,22.546195],"dist":51.40541135876105}
{"id":100005,"name":"门店100005","loc":[114.068245,22.546695],"dist":55.65942251060517}
{"id":300005,"name":"门店300005","loc":[114.068245,22.545695],"dist":55.65942251100064}
5、性能测试
服务器 CPU 2.00GHz 4核
基础数据40W条，本机16线程分别进行1万次附近200m,2000m,20000m查询:。
查询数据条数:
200m:72条
2km:746条
20km:7482条
性能结果如下
-----200m
mean rate = 636.68 calls/second1-minute rate = 615.66 calls/second5-minute rate = 610.90 calls/second15-minute rate = 610.23 calls/secondmin = 5.19 millisecondsmax = 232.36 millisecondsmean = 23.86 millisecondsstddev = 13.49 millisecondsmedian = 22.30 milliseconds75% <= 28.58 milliseconds95% <= 43.58 milliseconds98% <= 49.93 milliseconds99% <= 56.90 milliseconds99.9% <= 231.33 milliseconds
-----2kmmean rate = 159.02 calls/second1-minute rate = 158.04 calls/second5-minute rate = 157.00 calls/second15-minute rate = 156.74 calls/secondmin = 35.76 millisecondsmax = 220.84 millisecondsmean = 103.05 millisecondsstddev = 20.75 millisecondsmedian = 101.69 milliseconds75% <= 115.24 milliseconds95% <= 138.29 milliseconds98% <= 150.54 milliseconds99% <= 157.01 milliseconds99.9% <= 175.21 milliseconds
-----20km mean rate = 20.14 calls/second1-minute rate = 20.38 calls/second5-minute rate = 20.32 calls/second15-minute rate = 20.53 calls/secondmin = 364.77 millisecondsmax = 1170.82 millisecondsmean = 827.92 millisecondsstddev = 122.23 millisecondsmedian = 836.73 milliseconds75% <= 901.60 milliseconds95% <= 1005.59 milliseconds98% <= 1057.28 milliseconds99% <= 1078.33 milliseconds99.9% <= 1170.82 milliseconds
结果同样是扫描的数据越多性能越低
总结
本文通过java使用redis和mongodb分别实现了附近的门店功能，从查询结果来看redis和mongodb的距离计算结果有细微的差别，不过影响不大。
从性能对比来看，本文中的数据测试结果redis的性能优于mongodb。主要影响性能的是单次查询需要扫描的数据.
