PostGIS与MySQL空间索引对比

什么是数据库空间索引？

数据库中的索引我们都是知道，最常见的就是用B+数来索引我们存储在数据库中的数据。顾名思义，空间索引就是一种用来索引空间数据（例如2维数据）的索引结构。用户可以像使用传统一维索引一样快捷便利地处理空间数据。常见的空间数据主要包括点、线、多边形。

MySQL空间索引的一个疑似bug

最近工作中需要用到空间索引来索引二维数据，由于之前业务使用的是MySQL 5.7.18，所以为了便捷，优先考虑使用MySQL的Spatial Index来索引二维数据。但是在开发之后，使用一个较为复杂的测试用例进行测试。大致是往MySQL中写入96W个polygon，形成96W条记录。然后需要查找出在特定区域内的所有polygon。SQL语句如下：

select * from my_polygon where ST_Intersects(ST_GEOMFROMTEXT
('POLYGON((1 1,40000 1,40000 40000, 1 40000, 1 1))'), polygon);

这样MySQL会报错error: 3037 Invalid GIS data provided to function st_intersects，但是如果缩小区域搜索则可以得到正确结果。

select * from my_polygon where ST_Intersects(ST_GEOMFROMTEXT
('POLYGON((1 1,10000 1,10000 10000, 1 10000, 1 1))'), polygon);

这个问题真是太奇怪，找了一些资料也没有人遇到类似情况，只能理解可能是MySQL自带的问题。最终无奈只能考虑换成对空间索引支持更好的PostgreSQL/PostGIS。

PostgreSQL 与 MySQL Spatial Index

在引入PostGIS之前，我对比了PostgreSQL 13 和 MySQL 5.7.18在不同查询语句上的消耗时间。

首先在MySQL和PostgreSQL中创建两张表，一张是my_point存储点数据，另一张是my_polygon存储多边形。分别在my_point和my_polygon中各自插入96W条数据。

CREATE TABLE my_point (
id SERIAL PRIMARY KEY,
point point NOT NULL
) ;

CREATE TABLE my_polygon (
id SERIAL PRIMARY KEY
polygon polygon NOT NULL
) ;

然后对比以下4个查询语句上的区别：

1. select * from my_polygon

2. select * from my\_polygon where ST_Intersects(ST_GEOMFROMTEXT
('POLYGON((1 1,10000 1,10000 10000,1 10000, 1 1))'), polygon)

3. select * from my\_polygon where ST_Intersects(ST_GEOMFROMTEXT
('POLYGON((1 1,40000 1,40000 40000, 1 40000, 1 1))'), polygon)

4. select * from my\_point where ST_Intersects(ST_GEOMFROMTEXT
('POLYGON((1 1,40000 1,40000 40000,1 40000, 1 1))'), point)

执行时间结果如下表：

序号	MySQL	PostgreSQL
1	23.076s	8.026s
2	1.285s	1.257s
3	/	3.525s
4	6.431s	0.680s

从表格数据对比中可以看出，PostgreSQL的空间索引都是要比MySQL性能要好的，特别是在数据量更大的情况下，优势更明显。
那么除了时间效率，其他方面二者有哪些区别呢？

功能	MySQL Spatial Index	PostGIS
空间索引	R树	R树和GIST树索引
空间类型	二维	二维、三维甚至四维
空间操作函数	有限	基本实现OGC标准定义的空间操作函数
事务支持	不支持	支持
空间索引	较慢	较快
查询效率	较慢	较快
GIS系统使用	使用较少	使用较多

除了MySQL Spatial 以为，还有一些关系型数据库支持空间索引，例如SQL Server, Oracle，这里有前人做的一些对比。还有一些非关系型数据库如MongoDB, Redis等，但是支持程度有限。

总结

综上所述，PostgreSQL是目前对空间数据索引支持最为优秀的数据库，如果业务中需要应对各类复杂空间查询需求则推荐使用。否则只是一些简单应用则MySQL也足够应付。