时间戳

首页 > 解决方案 > 如何使用经度和纬度搜索最近的用户，同时在 MySQL 中以有效的方式按性别和年龄进行过滤？

mysql - 如何使用经度和纬度搜索最近的用户，同时在 MySQL 中以有效的方式按性别和年龄进行过滤？

问题描述

我习惯了 BTREE 索引，我知道如何应用多列索引来优化普通查询。

我有一个这样的示例数据库：

DROP DATABASE IF EXISTS `gis`;

CREATE DATABASE IF NOT EXISTS `gis`
    DEFAULT CHARACTER SET utf8
    DEFAULT COLLATE utf8_general_ci;

USE `gis`;

DROP TABLE IF EXISTS user;
CREATE TABLE IF NOT EXISTS user (
    id           INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    firstname    VARCHAR(48) NOT NULL,
    sex          ENUM('male', 'female') NOT NULL,
    age          TINYINT UNSIGNED NOT NULL,
    position     POINT NOT NULL
);
ALTER TABLE user ADD INDEX sex__age (sex, age);
ALTER TABLE user ADD SPATIAL INDEX(position);

INSERT INTO user (firstname, sex, age, position) VALUES ('Alexander', 'male', 34, POINT(63.429909, 10.393035));
INSERT INTO user (firstname, sex, age, position) VALUES ('Dina', 'female', 21, POINT(63.426300, 10.392481));
INSERT INTO user (firstname, sex, age, position) VALUES ('Martin', 'male', 32, POINT(63.422304, 10.432027));
INSERT INTO user (firstname, sex, age, position) VALUES ('Tina', 'female', 19, POINT(63.430603, 10.373038));
INSERT INTO user (firstname, sex, age, position) VALUES ('Kristin', 'female', 20, POINT(63.434858, 10.411359));
INSERT INTO user (firstname, sex, age, position) VALUES ('Mette', 'female', 33, POINT(63.420422, 10.403811));
INSERT INTO user (firstname, sex, age, position) VALUES ('Andres', 'male', 34, POINT(63.419488, 10.395722));
INSERT INTO user (firstname, sex, age, position) VALUES ('Sandra', 'female', 25, POINT(63.432053, 10.408738));
INSERT INTO user (firstname, sex, age, position) VALUES ('Kine', 'female', 29, POINT(63.432302, 10.412643));
INSERT INTO user (firstname, sex, age, position) VALUES ('Henrik', 'male', 25, POINT(63.421055, 10.443288));

如果我按性别和年龄进行正常查询和查询，它会使用正确的索引，并且只在该查询中查找 2 行，这是它应该做的，它向我显示查询和索引工作正常。

EXPLAIN SELECT id, firstname, sex, age FROM user WHERE sex = 'male' AND age BETWEEN 25 AND 32;

它使用 key: sex__ageindex 并根据年龄进行范围搜索，检查的行数仅为 2，这很好。

但后来我尝试按位置进行查找，并检查 3.5 公里内的用户，但它检查所有行并且不使用空间索引。

SET @distance = 3.5;
SET @my_place_lng = 63.431592;
SET @my_place_lat = 10.396210;

SELECT
    id,
    firstname,
    sex,
    age,
    ST_Distance_Sphere(Point(@my_place_lng, @my_place_lat), position) AS distance_from_me
FROM user
WHERE
    ST_Contains(ST_MakeEnvelope(
        Point((@my_place_lng+(@distance/111)), (@my_place_lat+(@distance/111))),
        Point((@my_place_lng-(@distance/111)), (@my_place_lat-(@distance/111)))
    ), position)
ORDER BY distance_from_me ASC;

我怎样才能做到这一点并让 MySQL 使用空间索引？

而且我已经知道我不能组合 RTREE 和 BTREE 索引，所以空间和普通索引不能组合。关于如何优化这样的东西有什么建议吗？

标签： mysqlgisgeospatial

解决方案

创建空间索引时是否收到任何警告？MySQL 对 SRID 要求有奇怪的行为，如此处所述

https://mysqlserverteam.com/geographic-indexes-in-innodb/

尝试设置 SRID。

推荐阅读