MySQL Postgresql / PostGIS

Question

我在一个拥有四亿行分区的MySQL表中拥有纬度/经度坐标。该表每分钟增长2000条记录，旧数据每隔几周会被清除。

我正在探索在数据输入时如何进行空间分析。

大多数分析都需要找到一个点是否在特定的纬度/经度多边形内，或者哪些多边形包含该点。

我看到有以下几种解决点对多边形（PIP）问题的方法:

1. 创建一个MySQL函数，它接受一个点和一个几何对象，并返回布尔值。 这很简单，但不确定如何使用几何对象对纬度/经度坐标执行操作，因为几何对象假设是平面而不是球体。

2. 创建一个MySQL函数，它接受一个点和自定义数据结构的标识符，并返回布尔值。 多边形顶点可以存储在一个表中，函数可以使用球形数学计算PIP。大量的多边形点可能会导致巨大的表和慢查询。

3. 将点数据留在MySQL中，将多边形数据存储在PostGIS中，并使用应用服务器通过提供点作为参数在PostGIS中运行PIP查询。

4. 将应用程序从MySQL迁移到PostgreSQL/PostGIS。 这将需要大量的重写查询和过程。但是PostgreSQL在处理四亿行方面的表现如何呢？ 在Google上快速搜索“MySQL 10亿行”会返回许多结果，而搜索相同的查询“Postgres 10亿行”的结果不相关。

希望听到一些想法和建议。

- Dojo

---

我有运行包含 3 亿多行数据的 Postgres 的个人经验 - 毫不费力。Skype 使用 Pg 跟踪连接、用户、会计等信息，除了通信渠道本身之外的所有内容。这是数十亿条记录。 - dbenhur

我们的PostgreSQL数据库在过去两年中每月处理约6亿条记录，每秒处理高达5000个事务。之前的MySQL服务器无法在相同的硬件上处理这些数据。 - Frank Heikens

@Priyank，这不需要太多的调整。使用默认分布式构建，Pg能够利用大内存、磁盘和处理器资源来处理具有高并发性的大数据集。有一些针对较小系统（例如shared_buffers）的默认配置设置，但是您不需要更改超过少数配置值即可从实质性机器中获得良好的性能。您将需要执行与其他数据库相同的操作来管理大型数据：分区策略、备份和复制、确保您的存储系统可以胜任等。 - dbenhur

我已经成功地使用Postgres数据库存储了超过27亿条记录，并且可以通过使用ST_Contains查询告诉我这些记录中有多少在某个特定的多边形内...根据我的经验，Postgres比Mysql更适合处理数十亿级别的数据...从这个角度来看，我建议选择选项4。只是想问一下...这个地理空间需求纯粹是报告需求吗？离开MYSQL并为应用程序创建一个每晚从MYSQL数据库加载的Postgres数据仓库是否可行？这将为您提供一个与生产环境无关的报告世界。 - Twelfth

我做了一些测试，GIS功能相当不错，但是在非GIS操作上的表现并不令人印象深刻。从一个包含500,000条记录的分区中有5000条满足where子句的"select * from foo"查询在Postgres中需要大约740毫秒，在Mysql上只需要350毫秒。试过一些其他类似的查询，结果也差不多。此外，Postgres数据存储在SSD上，而MYSQL数据存储在常规HDD上。 - Dojo

Answer 1

一些想法。

首先，当涉及到性能调优时，PostgreSQL和MySQL完全是不同的东西。因此，如果你选择移植路线，要准备重新考虑索引策略。PostgreSQL不仅比MySQL具有更灵活的索引，而且表格方法也非常不同，这意味着适当的索引策略与策略截然不同。不幸的是，这意味着你可能会遇到一些困难。如果我能给出建议，我建议首先删除所有非关键索引，然后根据需要逐渐添加它们。

第二点是，由于我们不知道你程序的内部情况，因此在这里没有人可能会给你大量实用的建议。在PostgreSQL中，最好只索引所需内容，但是您可以索引函数的输出（在这种情况下非常有帮助），并且可以仅对表格的一部分进行索引。

我更倾向于PostgreSQL而不是MySQL，因此我认为你应该选择PostgreSQL。然而，不是告诉你为什么等等，让你在这个规模上挣扎，我将告诉你一些我尝试做这件事时会使用的东西。

- 功能性索引 - 为相关分析编写自己的索引函数 - PostGIS非常惊人且非常灵活

最后，切换数据库在这个容量下会有一个学习曲线，你需要为此做好准备。然而，PostgreSQL可以很好地处理这个容量。

- Chris Travers

Answer 2

在这里，行数并不重要。问题是索引可以处理多少点在多边形内。

答案取决于多边形的大小。

PostGIS非常快速地查找多边形边界框内的所有点。然后需要更多的工作来确定点实际上是否在多边形内。

如果您的多边形很小（边界框小），则查询将高效。如果您的多边形很大或形状使边界框变大，则效率将降低。

如果您的多边形基本保持静态，则有解决方法。您可以将多边形分成较小的多边形并重新创建索引。然后索引将更有效。

如果您的多边形实际上是多个多边形，则第一步是使用ST_Dump将多个多边形拆分为单个多边形，并在结果上重新创建和构建索引。

希望对您有所帮助。

Nicklas

- Nicklas Avén

---

这些个体点（约4亿个）无论如何都要存储在数据库中。PIP是另一个问题。如果您指的是第二点，那么它是一个MySQL表，用于存储多边形顶点，并且UDF在表上运行查询以确定PIP结果。 - Dojo