当前位置：首页 > 问答 > 正文

用SQL搞定AIS数据导出，数据库里怎么操作才方便快速

要搞定AIS数据导出,并且在数据库里操作起来方便快速，核心思路就一句话：把力气花在前期准备上，而不是临时抱佛脚去处理海量数据。 AIS数据量非常大，一艘船每隔几秒就报告一次位置，日积月累，数据库轻易就能达到TB级别，如果你每次都直接去原始数据表里查询和导出，那速度会慢得让你怀疑人生。

根据数据库管理和数据仓库设计的通用原则,以及处理时序数据的常见做法，最关键的一步是对数据进行分层和处理，你不能把所有的AIS数据都堆在一张表里。

一个非常有效的办法是建立至少两层结构：原始数据层和应用数据层。

第一层，原始数据层： 这张表就叫它 ais_data_raw 好了，它的任务就是来者不拒，把所有接收到的AIS报文解析后的数据都存进去，它的结构可以很简单，包含最核心的字段，

对于这一层,你要做的主要是两件事来保证后续速度：

分区： 这是最重要的提速手段，你可以按照时间范围对表进行分区，比如按天分区或按月分区，假设你的表按天分区，当你需要导出某一天的数据时，数据库就不用扫描整个表，而是直接去找对应那天的分区文件，这叫“分区裁剪”，能极大减少磁盘I/O，速度提升成百上千倍。
索引： 在经常用来查询的字段上建立索引，相当于给书加了目录，对于AIS数据，mmsi 和 timestamp 是最常用的查询条件，所以在这两个字段上建立一个联合索引会非常有效，但要注意，索引不是越多越好，它会降低数据插入的速度并占用额外空间，对于原始数据层，有核心索引就够了。

第二层，应用数据层： 这一层是根据你最常见的导出需求，对原始数据进行清洗、聚合和整理后的表，这才是你平时导出数据时应该主要操作的表，因为原始数据太“碎”了，很多导出需求并不需要每秒一条的精度。

举个例子,如果你经常需要按船舶导出它某段时间内的航迹，你可以在这一层创建一张 ais_vessel_trajectory 表，这张表的数据可以通过定时任务（比如每天凌晨）从 ais_data_raw 表计算得来，计算过程可以包括：

数据清洗： 过滤掉经纬度明显错误（比如在陆地上）、航速异常高的无效数据。
轨迹压缩： 船舶在直线航行时，没必要记录每一个点，可以使用一些算法（比如Douglas-Peucker算法），在保证轨迹形状大体不变的情况下，大幅度减少需要存储的数据点数量，比如把一条有1000个点的轨迹，压缩成100个关键点，数据量直接减少90%，但航迹看起来几乎没区别。
按需聚合： 如果你关心的不是具体轨迹，而是船舶行为，每天进入某个区域的船舶列表”，那你可以在这一层直接预计算好一张 daily_vessel_visit 表，直接存储结果，到时候导出就是秒级响应。

这样一来,当你有导出需求时，操作就变得非常简单快速：

场景1：导出某艘船在过去一周的详细航迹。
- 笨办法： SELECT * FROM ais_data_raw WHERE mmsi = '123456789' AND timestamp BETWEEN '2023-10-01' AND '2023-10-08' ORDER BY timestamp; 如果数据量很大，这个查询会很慢。
- 聪明办法： SELECT * FROM ais_vessel_trajectory WHERE mmsi = '123456789' AND date BETWEEN '2023-10-01' AND '2023-10-08'; 因为数据已经按船舶和日期整理好，并且数据量经过压缩大幅减少，查询速度会飞快。
场景2：导出过去一个月所有经过长江口的船舶名单。
- 笨办法： 在原始表上用一个复杂的空间查询，判断每个点是否在长江口区域，然后对船舶去重，这个操作会扫描整个月的数据，极其耗时。
- 聪明办法： 直接查询预聚合好的 vessel_visit_yangtze_estuary 表，SELECT * FROM vessel_visit_yangtze_estuary WHERE visit_date BETWEEN '2023-09-01' AND '2023-09-30'; 瞬间出结果。

除了表结构设计,导出时的具体操作也有技巧：

使用命令行工具导出： 像 pg_dump（对于PostgreSQL）或 mysqldump（对于MySQL）这样的命令行工具，通常比在图形化界面里执行导出要快得多，尤其是数据量大的时候。
导出为纯文本格式： 如果需要后续处理，导出为CSV或TXT格式通常比导出为Excel（.xlsx）格式更快，文件也更小。
分批导出： 如果必须导出的数据量巨大，可以尝试分批进行，比如按周或按天分批导出，减少单次操作的压力。

想要方便快速地导出AIS数据,诀窍不在于导出那一刻的操作，而在于之前的数据库设计：