没有使用空间索引 [英] Spatial Index not being used

问题描述

我在GEO_LOCATION列上有空间索引，但是当我执行EXPLAIN时，它没有显示索引正在被使用。任何人都可以说出原因吗？

I have a spatial index on GEO_LOCATION column, but when I do EXPLAIN it doesn't show that the index is being used. Can anyone tell why?

EXPLAIN 
SELECT AsText(GEO_LOCATION) 
FROM PERSON 
WHERE ST_Distance(POINT(-94.0724223,38.0234332), GEO_LOCATION) <= 10

id：1

选择类型：SIMPLE

表：PERSON

类型：全部

possible_keys：NULL

键：NULL

key_len：NULL

ref：NULL

行：612602

额外：使用where

id: 1
select type: SIMPLE
table: PERSON
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 612602
Extra: Using where

这是我的环境：

服务器类型：MariaDB

服务器版本：10.1.8-MariaDB - mariadb。组织二进制分发

协议版本：10
服务器字符集：UTF-8 Unicode（utf8）

Apache / 2.4.17（Win32）OpenSSL /1.0.2d PHP / 5.6.14

数据库客户端版本：libmysql - mysqlnd 5.0.11-dev - 20120503

PHP扩展：mysqli文档

PHP版本：5.6.14

This is my environment:
Server type: MariaDB
Server version: 10.1.8-MariaDB - mariadb.org binary distribution
Protocol version: 10
Server charset: UTF-8 Unicode (utf8)
Apache/2.4.17 (Win32) OpenSSL/1.0.2d PHP/5.6.14
Database client version: libmysql - mysqlnd 5.0.11-dev - 20120503
PHP extension: mysqli Documentation
PHP version: 5.6.14

推荐答案

不幸的是 ST_Distance（）<阈值 不是 sargable 搜索条件。为了满足此查询，MySQL必须为表中的每一行计算函数的值，然后将其与阈值进行比较。因此，它必须进行全表扫描（或者可能是完整的索引扫描）。

Unfortunately ST_Distance() < threshold isn't a sargable search criterion. To satisfy this query, MySQL must compute the function's value for every row in the table, and then compare it to the threshold. So it has to do a full table scan (or maybe a full index scan).

要利用索引来加速此查询，您将需要一个边界框标准。查询更精细，但速度也快得多。假设您的几何中的x / y点表示以度为单位的纬度/经度，则该查询可能如下所示：

To exploit an index to speed up this query, you're going to need a bounding-box criterion. The query is a lot more elaborate but also a lot faster. Assuming your x/y points in your geometry represent latitude/longitude in degrees, that query might look like this:

   set @latpoint = 38.0234332;
   set @lngpoint = -94.0724223;
   set @r = 10.0;    /* ten mile radius */
   set @units=69.0;    /* 69 statute miles per degree */
   SELECT AsText(geo) 
     FROM markers
      WHERE MbrContains(GeomFromText( 
       CONCAT('LINESTRING(', @latpoint-(@r/@units),' ',
                             @lngpoint-(@r /(@units* COS(RADIANS(@latpoint)))), 
                          ',', 
                             @latpoint+(@r/@units) ,' ', 
                             @lngpoint+(@r /(@units * COS(RADIANS(@latpoint)))),
                           ')')),
                    geo) 

这是如何工作的？首先， MbrContains（绑定，项目）功能是 sargable 。另一方面，大丑陋的concat项目产生了从边界矩形的西南角到东北角的对角线。使用您的数据点和十英里半径，它看起来像这样。

How does this work? For one thing, the MbrContains(bound,item) function is sargable. For another thing, the big ugly concat item yields a diagonal line from the southwest to the northeast corner of the bounding rectangle. Using your data point and ten mile radius it looks like this.

LINESTRING(37.8785 -94.2564,38.1684 -93.8884)

当您使用 GeomFromText（）渲染该对角线时第一个参数中的行到 MbrContains（）它用作边界矩形。 MbrContains（）然后可以利用漂亮的四叉树几何索引。

When you use the GeomFromText() rendering of that diagonal line in the first argument to MbrContains() it serves as a bounding rectangle. MbrContains() can then exploit the nifty quadtree geometry index.

第三， ST_Distance（在MySQL中，不能处理大圆纬度和经度计算。 （ PostgreSQL 有一个更全面的 GIS扩展。）MySQL就像平地上的烙饼一样愚蠢。它假设几何对象中的点以平面几何体表示。所以 ST_Distance（）<带有lng / lat点的10.0 确实很奇怪。

Thirdly, ST_Distance(), in MySQL, doesn't handle great circle latitude and longitude computations. (PostgreSQL has a more comprehensive GIS extension.) MySQL's is as dumb as a flapjack in flatland. It assumes your points in your geometric objects are represented in planar geometry. So ST_Distance() < 10.0 with lng/lat points does something strange.

这个查询生成的结果有一个缺陷;它返回边界框中的所有点，而不仅仅是在指定的半径范围内。这可以通过单独的距离计算来解决。我已经在这里详细介绍了所有这些。

There's one flaw in the results this query generates; it returns all the points in the bounding box, not just within the specified radius. That's solvable with a separate distance computation. I've written all this up in some detail here.

注意：对于GPS分辨率纬度和经度，32位 FLOAT 数据具有足够的精度。 DOUBLE 是MySQL的地理扩展所使用的。当你在学位工作时，小数点后超过五个位置超出了GPS的精度。 DECIMAL（）不是lat / lng坐标的理想数据类型。

Note: For GPS-resolution latitude and longitude, 32-bit FLOAT data has sufficient precision. DOUBLE is what MySQL's geo extension uses. When you're working in degrees, more than five places after the decimal point is beyond the precision of GPS. DECIMAL() is not an ideal datatype for lat/lng coordinates.

这篇关于没有使用空间索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！