未使用空间索引

2022-01-15 00:00:00 indexing spatial mariadb mysql

我在 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
选择类型:简单
表:人
类型:全部
可能的键:NULL
键:NULL
key_len: 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.org 二进制分发
协议版本: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(bound,item) 函数是 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)

当您在 MbrContains() 的第一个参数中使用该对角线的 GeomFromText() 渲染时，它用作边界矩形.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.

相关文章