【译】MySQL服务博客 - InnoDB中的空间数据索引

本文涉及的产品
云数据库 RDS SQL Server,独享型 2核4GB
云数据库 RDS MySQL Serverless,0.5-2RCU 50GB
简介: MySQL 8.0的一个重要特性是对地理业务的支持。MySQL现在已拥有一类功能称之为空间参考系统(SRS),其中近500个是地理区域相关的。大多数函数还支持地理计算。

MySQL 8.0的一个重要特性是对地理业务的支持。MySQL现在已拥有一类功能称之为空间参考系统(SRS),其中近500个是地理区域相关的。大多数函数还支持地理计算。大家可能会关心索引的功能的增强。

MySQL 8.0附带了用于地理数据的InnoDB空间索引。由于笛卡尔和地理数据的计算方式不同,因此不能在同一个索引中混合使用。实际上,在同一索引中的多个SRS中索引数据是没有意义的。因此,MySQL加强了几何列定义中的SRID限制。

SRID限制

在5.7及更早版本中,对于加索引的几何列的唯一要求是该类型应该是几何类型,并且该列不为空。不幸的是,我们允许将不同SRID中的几何数据插入到同一个索引中。这种做法毫无意义,尤其当某些几何体位于地理SRS中时,情况会变得更糟。

因此,MySQL 8.0增加了限制,几何列只有一个SRID:

mysql>  CREATE TABLE places  (
->  pk INT  PRIMARY KEY,
->  position POINT NOT  NULL  SRID  4326,
->  name VARCHAR(200)
->  );

Query OK,  0  rows affected  (0,00  sec)

如果我们试图在不同的SRID中插入一个几何体,会得到一个错误:

mysql>  INSERT INTO places VALUES  (1,  ST_GeomFromText('POINT(63.4269 10.3958)', 
 0),  'Nidaros Cathedral');

ERROR  3643  (HY000):  The SRID of the geometry does not  match the SRID of the column  'position'.  
The SRID of the geometry is  0,  but the SRID of the column is  4326.  Consider changing the SRID 
of the geometry or  the SRID property  of the column.

如果我们遵守SRID限制,则该点则可以插入:

mysql>  INSERT INTO places VALUES  (1,  ST_GeomFromText('POINT(63.4269 10.3958)', 
 4326),  'Nidaros Cathedral');

Query OK,  1  row affected  (0,00  sec)

有了这个限制,MySQL确保我们不会将同一列中不同SRID中的数据混合在一起,从而使列可以索引。

另一件事是锁定SRID。在SRID限制中它被使用,服务器不允许我们drop掉SRS:

mysql>  DROP SPATIAL REFERENCE SYSTEM  4326;

ERROR  3716  (SR005):  Can't  modify SRID  4326.  There is  at least one column depending on it.

究竟是哪一列呢?

mysql>  SELECT *  FROM INFORMATION_SCHEMA.ST_GEOMETRY_COLUMNS WHERE SRS_ID=4326\G

***************************  1.  row ***************************
               TABLE_CATALOG:  def
                TABLE_SCHEMA:  test
                     TABLE_NAME:  places
                 COLUMN_NAME:  position
                         SRS_NAME:  WGS  84 
                                SRS_ID:  4326
GEOMETRY_TYPE_NAME:  point
1  row in  set  (0,00  sec)

索引

我们可以轻松地在几何列上创建索引。

mysql>  CREATE SPATIAL INDEX position ON places  (position);
Query OK,  0  rows affected  (0,00  sec)
Records:  0  Duplicates:  0  Warnings:  0

由于此列位于SRID 4326中,因此索引也将位于SRID 4326. SRID 4326是地理位置的SRS,因此这将是地理位置索引。查询优化器将自动使用这个索引来优化与空间相关的函数掉执行(ST_Contains,ST_Within等),如果它发现这是最低成本的处理方法。所有的空间关系函数都支持地理计算。

一个可能令人惊讶的事实是,服务器仍然允许我们不必限制在单列上建索引,但是会警告这个索引永远不会被使用:

mysql>  CREATE TABLE dont_do_this  (
->  pk INT  PRIMARY KEY,
->  position POINT NOT  NULL,
->  name VARCHAR(200)
->  );
Query OK,  0  rows affected  (0,00  sec)

mysql>  CREATE SPATIAL INDEX position ON dont_do_this  (position);
Query OK,  0  rows affected,  1  warning  (0,00  sec)
Records:  0  Duplicates:  0  Warnings:  1

Warning  (Code  3674):  The spatial index on column  'position'  will not  be used by the query optimizer since the column does not  have an SRID attribute.  Consider adding an SRID attribute to  the column.

警告说明了一切。该索引将永远不会被使用。服务器允许我们仅仅为了一个原因创建索引:向后兼容mysqldump。我们应该能够从5.7加载一个mysqldump。如果在转储中存在像这样的空间索引,它们将被创建但不会使用。

MyISAM数据

值得注意的是,这只适用于InnoDB。如果我们尝试在MyISAM中的地理SRID上创建一个SRID受限列,我们会得到一个错误:

mysql>  CREATE TABLE places  (
->  pk INT  PRIMARY KEY,
->  position POINT NOT  NULL  SRID  4326,
->  name VARCHAR(200)
->  )  ENGINE=MyISAM;

ERROR  1178  (42000):  The storage engine for  the table doesn't  support geographic spatial reference systems

如果我们尝试使用笛卡尔SRS的SRID,我们可以创建表:

​mysql>  CREATE TABLE places  (
->  pk INT  PRIMARY KEY,
->  position POINT NOT  NULL  SRID  3857,
->  name VARCHAR(200)
->  )  ENGINE=MyISAM;

Query OK,  0  rows affected  (0,00  sec)

原因是MyISAM不支持地理空间索引。创建表时我们已经阻止了它。

我的建议是:将InnoDB用于所有空间数据!

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
22天前
|
监控 安全 关系型数据库
在规划阿里云RDS跨区迁移资源和服务可用性
在规划阿里云RDS跨区迁移资源和服务可用性
206 4
|
26天前
|
存储 关系型数据库 MySQL
MySQL - 索引类型详解
MySQL - 索引类型详解
|
26天前
|
存储 缓存 关系型数据库
MySQL - 存储引擎MyISAM和Innodb
MySQL - 存储引擎MyISAM和Innodb
|
11天前
|
SQL 存储 关系型数据库
MySQL索引(二)索引优化方案有哪些
MySQL索引(二)索引优化方案有哪些
30 0
|
26天前
|
存储 关系型数据库 MySQL
MySQL - 聚簇索引和非聚簇索引
MySQL - 聚簇索引和非聚簇索引
|
26天前
|
存储 SQL 关系型数据库
MySQL - 深入解析MySQL索引数据结构
MySQL - 深入解析MySQL索引数据结构
|
1天前
|
SQL 关系型数据库 MySQL
MySQL索引与事务
MySQL索引与事务
|
1天前
|
存储 SQL 关系型数据库
【MySQL 数据库】6、一篇文章学习【索引知识】,提高大数据量的查询效率【文末送书】
【MySQL 数据库】6、一篇文章学习【索引知识】,提高大数据量的查询效率【文末送书】
18 0
|
1天前
|
监控 关系型数据库 MySQL
MySQL创建索引的注意事项
在索引的世界中,权衡是关键。权衡读写性能,权衡索引的数量和类型,权衡查询的频率和数据分布。通过谨慎的设计、定期的维护和持续的监控,我们能够确保索引在数据库中的角色得到最大的发挥,为应用提供更加高效和可靠的数据访问服务。在数据库优化的旅途中,索引是我们的得力助手,正确使用它将使数据库系统更具竞争力和可维护性。
8 0
|
3天前
|
SQL 关系型数据库 MySQL
京东三面:什么情况会导致 MySQL 索引失效?
为了验证 MySQL 中哪些情况下会导致索引失效,我们可以借助 explain 执行计划来分析索引失效的具体场景。
18 0

推荐镜像

更多