mysql - 搜索附近坐标的查询速度较慢

Question

我似乎在搜索附近坐标时遇到了较慢的查询结果(目前查询是针对纬度的)。这是一个mysql查询选择 ABS(propertyCooperativesLat - 3.33234) 作为与表名 order by diff asc limit 0,20 的差异

除了依靠服务器脚本进行排序之外，还有其他方法可以改进这一点吗？

表转储。

CREATE TABLE `property` (
  `propertyID` bigint(20) NOT NULL,
  `propertyName` varchar(100) NOT NULL,
  `propertyCoordinatesLat` varchar(100) NOT NULL,
  `propertyCoordinatesLng` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `property`
--
ALTER TABLE `property`
  ADD PRIMARY KEY (`propertyID`),
  ADD KEY `propertyCoordinatesLat` (`propertyCoordinatesLat`,`propertyCoordinatesLng`),
  ADD KEY `propertyCoordinatesLat_2` (`propertyCoordinatesLat`),
  ADD KEY `propertyCoordinatesLng` (`propertyCoordinatesLng`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `property`
--
ALTER TABLE `property`
  MODIFY `propertyID` bigint(20) NOT NULL AUTO_INCREMENT;
COMMIT;

Answer 1

查询按字符串和 float 之间的差异进行排序。这种奇怪的计算使 MySQL 感到困惑和愤怒，并导致文件排序缓慢。

mysql> explain select ABS(propertyCoordinatesLat - 3.33234) as diff from property order by diff 
+----+-------------+----------+------------+-------+---------------+--------------------------+---------+------+------+----------+-----------------------------+
| id | select_type | table    | partitions | type  | possible_keys | key                      | key_len | ref  | rows | filtered | Extra                       |
+----+-------------+----------+------------+-------+---------------+--------------------------+---------+------+------+----------+-----------------------------+
|  1 | SIMPLE      | property | NULL       | index | NULL          | propertyCoordinatesLat_2 | 302     | NULL |    1 |   100.00 | Using index; Using filesort |
+----+-------------+----------+------------+-------+---------------+--------------------------+---------+------+------+----------+-----------------------------+

将 propertyCooperativesLat 和 propertyCooperativesLng 更改为 a more sensible numeric type让MySQL更好的优化。不再需要进行文件排序。这应该表现得更好。

alter table property change propertyCoordinatesLat propertyCoordinatesLat numeric(10,8) not null;
alter table property change propertyCoordinatesLng propertyCoordinatesLng numeric(11,8) not null;

mysql> explain select ABS(propertyCoordinatesLat - 3.33234) as diff from property order by propertyCoordinatesLat asc limit 0,20;
+----+-------------+----------+------------+-------+---------------+--------------------------+---------+------+------+----------+-------------+
| id | select_type | table    | partitions | type  | possible_keys | key                      | key_len | ref  | rows | filtered | Extra       |
+----+-------------+----------+------------+-------+---------------+--------------------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | property | NULL       | index | NULL          | propertyCoordinatesLat_2 | 5       | NULL |    1 |   100.00 | Using index |
+----+-------------+----------+------------+-------+---------------+--------------------------+---------+------+------+----------+-------------+

如果你想变得更奇特，请查看 MySQL's spatial types 。这些可能会表现得更好，而且肯定会更准确。