MySQL空间索引未使用

本文介绍了MySQL空间索引未使用的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我正在寻找能够找到从点到点近似匹配(假设在20米之内)的行.它可以工作，但不使用索引.

I'm looking for being able to find rows matching approximatively (let's say within 20 meters) given from and to points. It works but it doesn't use index.

我正在尝试利用此表上的Spatial索引，但似乎没有使用(解释命令给我"possible_keys" = null).

I'm trying to take advantage of Spatial index on this table but it doesn't seems to be used (Explain command give me "possible_keys" = null).

具有以下内容:

mysql 5.7.17
表:

mysql 5.7.17
table:

CREATE TABLE `geoDirections` (
   `id` int(11) NOT NULL,
   `from` point NOT NULL,
   `to` point NOT NULL,
) ENGINE=InnoDB;
ALTER TABLE `geoDirections`
    ADD PRIMARY KEY (`id`),
    ADD SPATIAL KEY `from` (`from`),
    ADD SPATIAL KEY `to` (`to`);

已插入约1000000行

arround 1000000 rows inserted

我尝试过的事情:

使用ST_Contains

using ST_Contains

EXPLAIN SELECT
   g.`from`
FROM
    geoDirections g
WHERE
    ST_Contains(ST_Buffer(
          ST_GeomFromText('POINT(-2.00751 48.6547)', 4326), (0.00001*20)), g.`from`) = 1
    AND
        ST_Contains(ST_Buffer(
          ST_GeomFromText('POINT(-2.05757 48.6338)', 4326), (0.00001*20)), g.`to`) = 1

给我

    | id  |  select_type  |  table  |  partitions  |  type  |  possible_keys  |  key  |  key_len  |  ref  |  rows  |  filtered  |  Extra       |
    | 1   |  SIMPLE       |  g      |     null     |  ALL   |     null        |  null |    null   |  null | 994867 |  100.00    |  Using where |

使用计算出的距离

using calculated distance

EXPLAIN SELECT
    X(g.`from`),Y(g.`from`), g.*, (
      6373 * acos (
      cos ( radians( -2.00751 ) )
      * cos( radians( X(g.`from`) ) )
      * cos( radians( Y(g.`from`) ) - radians( 48.6547  ) )
      + sin ( radians( -2.00751 ) )
      * sin( radians( X(g.`from`) ) )
    )
) AS distanceFrom
FROM geoDirections g
HAVING distanceFrom < 0.02

给我

    | id  |  select_type  |  table  |  partitions  |  type  |  possible_keys  |  key  |  key_len  |  ref  |  rows  |  filtered  |  Extra  |
    | 1   |  SIMPLE       |  g      |     null     |  ALL   |     null        |  null |    null   |  null | 994867 |  100.00    |  null   |

甚至很简单

even something as simple as

EXPLAIN SELECT
    X(g.`from`),Y(g.`from`), g.*
FROM geoDirections g
WHERE X(g.`from`) = -2.00751

给我

    | id  |  select_type  |  table  |  partitions  |  type  |  possible_keys  |  key  |  key_len  |  ref  |  rows  |  filtered  |  Extra       |
    | 1   |  SIMPLE       |  g      |     null     |  ALL   |     null        |  null |    null   |  null | 994867 |  100.00    |  Using where |

试图将InnoDb转换为MyIsam(较早的InnoDb版本不支持空间索引)

我想念什么?

推荐答案

错误#76384明确检查返回值时不使用空间索引.

尝试:

mysql> DROP TABLE IF EXISTS `geoDirections`;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE `geoDirections` (
    ->    `id` INT(11) NOT NULL,
    ->    `from` POINT NOT NULL,
    ->    `to` POINT NOT NULL
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.01 sec)

mysql> ALTER TABLE `geoDirections`
    ->     ADD PRIMARY KEY (`id`),
    ->     ADD SPATIAL INDEX (`from`),
    ->     ADD SPATIAL INDEX (`to`);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> EXPLAIN SELECT `g`.`from`
    -> FROM `geoDirections` `g`
    -> WHERE
    ->       ST_Contains(ST_Buffer(
    ->           ST_GeomFromText('POINT(-2.00751 48.6547)', 4326), (0.00001 * 20)), `g`.`from`)
    ->       AND
    ->       ST_Contains(ST_Buffer(
    ->           ST_GeomFromText('POINT(-2.05757 48.6338)', 4326), (0.00001 * 20)), `g`.`to`)\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: g
   partitions: NULL
         type: ALL
possible_keys: from,to
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

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

spatial