mysql - 在 MySQL 中使用空间索引时性能不佳

Question

我正在尝试一个小实验来推送一个不是地理空间但非常适合它的数据集，我发现结果有些令人不安。数据集是基因组数据，例如人类基因组，我们有一个 DNA 区域，其中基因等元素占据特定的开始和结束坐标(我们的 X 轴)。我们有多个占据 Y 轴的 DNA(染色体)区域。目标是带回沿单个 Y 坐标与两个 X 坐标相交的所有项目，例如LineString(开始 1，结束 2)。

这个理论似乎很合理，所以我将它推到现有的基于 MySQL 的基因组项目中，并提出了如下表结构:

CREATE TABLE `spatial_feature` (
  `spatial_feature_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `external_id` int(10) unsigned NOT NULL,
  `external_type` int(3) unsigned NOT NULL,
  `location` geometry NOT NULL,
  PRIMARY KEY (`spatial_feature_id`),
  SPATIAL KEY `sf_location_idx` (`location`)
) ENGINE=MyISAM;

external_id 表示我们编码到此表中的实体的标识符 & external_type 编码来源这个。一切看起来都很好，我输入了一些初步数据(30,000 行)，这些数据似乎运行良好。当这增加到超过 300 万行标记时，MySQL 拒绝使用空间索引并且在被迫使用它时速度变慢(40 秒对使用全表扫描的 5 秒)。当添加更多数据时，开始使用索引，但性能损失仍然存在。强制关闭索引使查询时间缩短到 8 秒。我正在使用的查询如下所示:

select count(*)
from spatial_feature
where MBRIntersects(GeomFromText('LineString(7420023 1, 7420023 1)'), location);

进入这里的数据在 Y 维度上非常密集(可以把它想象成你已经记录了一条很长的道路上每座建筑物、电话亭、邮箱和鸽子的位置)。我已经测试了 R-Indexes 如何在 Java 中处理这些数据，并且该领域的其他人已经成功地将它们应用于平面文件格式。然而，没有人将它们应用于数据库 AFAIK，这是本次测试的目标。

有没有人在将大量数据添加到沿特定轴不是非常不同的空间模型时看到类似的行为？如果我反转坐标用法，问题仍然存在。如果这是一个原因，我正在运行以下设置

• 苹果操作系统 10.6.6
• MySQL 5.1.46

帮助!

同时引入解释计划

+----+-------------+-----------------+------+-----------------+------+---------+------+---------+----------+-------------+
| id | select_type | table           | type | possible_keys   | key  | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+-----------------+------+-----------------+------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | spatial_feature | ALL  | sf_location_idx | NULL | NULL    | NULL | 3636060 |    33.33 | Using where |
+----+-------------+-----------------+------+-----------------+------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

重写后的 SQL 如下所示

select count(0) AS `count(*)` from `arabidopsis_thaliana_core_7_60_9`.`spatial_feature` where intersects(geometryfromtext('LineString(7420023 1, 7420023 1)'),`arabidopsis_thaliana_core_7_60_9`.`spatial_feature`.`location`)

仍然没有强调为什么这个查询的性能如此差

阅读@Fraser 从rickonrails 发表的文章后似乎问题与索引不在内存中有关。如果我将类似的技术应用于文章中提到的技术(确实使键缓冲区变得非常大)，然后我强制查询使用索引，查询时间将直线下降。我们仍然看到查询一个区域与然后搜索该区域的一个子集之间存在滞后，但这都指向正确加载索引。

故事的寓意是什么？ MySQL 中的 R-Indexes 在内存中之前性能很差，然后性能非常好。对于我想用它们做的事情来说，这并不是一个很好的解决方案，但它仍然为 MySQL 提供了一个有趣的角度。

感谢大家的帮助。

Answer 1

从 EXPLAIN 计划中我们看到，虽然空间可能用于查询('possible_keys' 列)，但未使用('key' 列中为 NULL)。我不确定为什么没有自动选择它，但您可以通过使用“强制索引”子句在查询中指定它来明确指示 MySql 使用索引:

select count(*)
from spatial_feature 
force index (sf_location_idx) --  <== this is the 'force index' clause
where MBRIntersects(GeomFromText('LineString(7420023 1, 7420023 1)'), location);