空间索引未用于边界框内多边形搜索

2022-01-15 00:00:00 sql mariadb mysql myisam spatial-index

我在 MariaDB 中有一个 MyISAM 表，其中包含两个 datetime 列 begin 和 end，我想在两个与 此处的博客文章类似的方式.

I have a MyISAM table in MariaDB containing two datetime columns begin and end and would like to create and use a spatial index on the two in a similar fashion to the blog post here.

这是我创建表格的方式:

Here is how I create the table:

CREATE TABLE `mytable` (
  `id` int(11) NOT NULL,
  `begin` datetime NOT NULL,
  `end` datetime NOT NULL,
) ENGINE=MyISAM DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4

用数据填充表格后，我然后添加一个 polygon 列:

After filling the table with data, I then add a polygon column:

ALTER TABLE mytable add time_range_int POLYGON NULL;

然后用从 begin 和 end 创建的多边形填充该列:

then fill the column with polygons created from begin and end:

UPDATE mytable
  SET time_range_int=Polygon(
    LineString(
      Point(UNIX_TIMESTAMP(begin), 1),
      Point(UNIX_TIMESTAMP(begin), 0),
      Point(UNIX_TIMESTAMP(end), 0),
      Point(UNIX_TIMESTAMP(end), 1),
      Point(UNIX_TIMESTAMP(begin), 1)
    )
  );

然后我将该列设置为 NOT NULL 并在其上创建空间索引:

I then set the column to NOT NULL and create a spatial index upon it:

ALTER TABLE mytable MODIFY time_range_int POLYGON NOT NULL;
CREATE SPATIAL INDEX index_time_range on mytable(time_range_int);

然后我尝试在表中查询包含 2016-12-19 和 2016-12-20 之间时间跨度的行，我通常在没有空间索引的情况下这样做如下:

Then I try to query the table for rows that contain the timespan between 2016-12-19 and 2016-12-20 which I normally do without the spatial index as follows:

SELECT SQL_NO_CACHE begin, end from mytable WHERE begin<="2016-12-19" and end>="2016-12-20";

尝试通过 using 来利用新创建的空间索引:

Trying to utilize the newly created spatial index by using instead:

SELECT SQL_NO_CACHE begin, end FROM mytable
  WHERE MBRWithin(
    Polygon(
      LineString(
        Point(UNIX_TIMESTAMP("2016-12-19 00:00:00"), 1),
        Point(UNIX_TIMESTAMP("2016-12-19 00:00:00"), 0),
        Point(UNIX_TIMESTAMP("2016-12-20 00:00:00"), 0),
        Point(UNIX_TIMESTAMP("2016-12-20 00:00:00"), 1),
        Point(UNIX_TIMESTAMP("2016-12-19 00:00:00"), 1)
      )
    ),
    time_range_int,
);

(感谢 O. Jones 关于参数顺序的提示)但是，没有使用空间索引，并且两个版本的查询都需要相同的执行时间.即使第二个中的 Explain 表明查询正在使用索引:

(Thanks O. Jones for the hint concerning order of parameters) However, the spatial index is not being used and both versions of the query require the same execution time. Even though Explain on the second shows that the query is using the index:

+------+---------------+---------+--------+------------------+------------------+-----------+--------+--------+-------------+
|   id | select_type   | table   | type   | possible_keys    | key              |   key_len |    ref |   rows | Extra       |
|------+---------------+---------+--------+------------------+------------------+-----------+--------+--------+-------------|
|    1 | SIMPLE        | mytable | range  | index_time_range | index_time_range |        34 | <null> |  67505 | Using where |
+------+---------------+---------+--------+------------------+------------------+-----------+--------+--------+-------------+

当我使用 IGNORE INDEX(index_time_range)

这是 MariaDB 中的错误吗?我的版本是 10.1.21-MariaDB 还是我遗漏了什么?

Is this a bug in MariaDB? My Version is 10.1.21-MariaDB or am I missing something?

推荐答案

尝试将参数的顺序切换为 MBRContains() 之类的东西.

Try switching the order of parameters to MBRContains() something like this.

SELECT begin, end FROM mytable
  WHERE MBRContains(
    Polygon(
      LineString(
        Point(UNIX_TIMESTAMP("2016-12-19 00:00:00"), 1),
        Point(UNIX_TIMESTAMP("2016-12-19 00:00:00"), 0),
        Point(UNIX_TIMESTAMP("2016-12-20 00:00:00"), 0),
        Point(UNIX_TIMESTAMP("2016-12-20 00:00:00"), 1),
        Point(UNIX_TIMESTAMP("2016-12-19 00:00:00"), 1)
      )
    ),
    time_range_int);

我已经成功，使用实际的 x 和 y 坐标，以这种方式使用空间搜索.

I've had success, with actual x and y coordinates, with using the spatial search that way.

相关文章