文章目录
前言
最近公司项目需要实现附近的门店功能，通过查询资料发现很多方法都可以实现。
包括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/second
1-minute rate = 1740.60 calls/second
5-minute rate = 1740.60 calls/second
15-minute rate = 1740.60 calls/second
min = 1.70 milliseconds
max = 152.20 milliseconds
mean = 9.11 milliseconds
stddev = 6.79 milliseconds
median = 7.61 milliseconds
75% <= 9.06 milliseconds
95% <= 17.78 milliseconds
98% <= 27.31 milliseconds
99% <= 34.27 milliseconds
99.9% <= 62.15 milliseconds
---2km
mean rate = 591.55 calls/second
1-minute rate = 578.13 calls/second
5-minute rate = 575.03 calls/second
15-minute rate = 574.48 calls/second
min = 7.33 milliseconds
max = 167.37 milliseconds
mean = 27.84 milliseconds
stddev = 7.75 milliseconds
median = 26.16 milliseconds
75% <= 29.36 milliseconds
95% <= 42.00 milliseconds
98% <= 45.53 milliseconds
99% <= 49.26 milliseconds
99.9% <= 58.36 milliseconds
-----20km
mean rate = 57.33 calls/second
1-minute rate = 57.47 calls/second
5-minute rate = 55.08 calls/second
15-minute rate = 54.06 calls/second
min = 25.43 milliseconds
max = 608.66 milliseconds
mean = 291.61 milliseconds
stddev = 48.28 milliseconds
median = 286.57 milliseconds
75% <= 310.07 milliseconds
95% <= 370.45 milliseconds
98% <= 414.06 milliseconds
99% <= 433.93 milliseconds
99.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 {
@Autowired
private 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);
}
@Override
public 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: "loc",
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/second
1-minute rate = 615.66 calls/second
5-minute rate = 610.90 calls/second
15-minute rate = 610.23 calls/second
min = 5.19 milliseconds
max = 232.36 milliseconds
mean = 23.86 milliseconds
stddev = 13.49 milliseconds
median = 22.30 milliseconds
75% <= 28.58 milliseconds
95% <= 43.58 milliseconds
98% <= 49.93 milliseconds
99% <= 56.90 milliseconds
99.9% <= 231.33 milliseconds
-----2km
mean rate = 159.02 calls/second
1-minute rate = 158.04 calls/second
5-minute rate = 157.00 calls/second
15-minute rate = 156.74 calls/second
min = 35.76 milliseconds
max = 220.84 milliseconds
mean = 103.05 milliseconds
stddev = 20.75 milliseconds
median = 101.69 milliseconds
75% <= 115.24 milliseconds
95% <= 138.29 milliseconds
98% <= 150.54 milliseconds
99% <= 157.01 milliseconds
99.9% <= 175.21 milliseconds
-----20km
mean rate = 20.14 calls/second
1-minute rate = 20.38 calls/second
5-minute rate = 20.32 calls/second
15-minute rate = 20.53 calls/second
min = 364.77 milliseconds
max = 1170.82 milliseconds
mean = 827.92 milliseconds
stddev = 122.23 milliseconds
median = 836.73 milliseconds
75% <= 901.60 milliseconds
95% <= 1005.59 milliseconds
98% <= 1057.28 milliseconds
99% <= 1078.33 milliseconds
99.9% <= 1170.82 milliseconds
结果同样是扫描的数据越多性能越低
总结
本文通过java使用redis和mongodb分别实现了附近的门店功能，从查询结果来看redis和mongodb的距离计算结果有细微的差别，不过影响不大。
从性能对比来看，本文中的数据测试结果redis的性能优于mongodb。主要影响性能的是单次查询需要扫描的数据.