如果你有一个良好的设计模式和良好的索引,SQL数据库可以轻松处理数百万行数据。
使用适当的类型
将日期和时间存储为单个datetime
或分别为date
和time
类型而不是将它们存储为单独的字符串。有关要使用哪种类型的更多信息,请参见下面的索引。这样可以使存储更紧凑,允许进行索引,排序更快,并且可以使用日期和时间函数而无需进行转换。
同样地,请确保对纬度和经度使用适当的数字类型。您可能想使用numeric
以确保精度。
由于您将要存储数十亿行记录,请确保在主键上使用bigint
。常规的int只能达到约20亿。
将重复数据移到另一个表中
将有关设备的信息存储在单独的表中,而不是在每一行中存储该信息。然后仅在您的日志中存储设备ID。这将减小存储大小,并消除由于数据重复而导致的错误。请确保将设备ID声明为外键,这将提供引用完整性和索引。
添加索引
索引是允许数据库非常高效地搜索数百万或数十亿行数据的方式。请确保在频繁使用的行上创建索引,例如时间戳。
在date
和deviceID
上缺少索引可能是您的查询非常缓慢的原因。没有索引,MySQL必须查看数据库中的每一行,这称为全表扫描。这就是为什么你的查询很慢,因为你缺少索引。
您可以使用explain
来发现您的查询是否使用索引。
datetime
或time
+date
?
通常最好将日期和时间存储在单个列中,通常称为created_at
。然后,您可以使用date
仅获取日期部分,如下所示。
select *
from gps_logs
where date(created_at) = '2018-07-14'
有一个问题。问题出在索引的工作方式...或者说不工作的方式上。由于函数调用,where date(created_at) = '2018-07-14'
不会使用索引。MySQL将在每一行上运行 date(created_at)
,这意味着性能会严重下降,需要进行全表扫描。
您可以通过仅使用 datetime
列来解决这个问题。这将使用索引并提高效率。
select *
from gps_logs
where '2018-07-14 00:00:00' <= created_at and created_at < '2018-07-15 00:00:00'
或者你可以将单个 datetime
列分成 date
和 time
两列,但这会引入新的问题。查询跨越一天边界的范围变得困难,比如说你想要一个不同时区的日期。使用单个列更加简单。
select *
from gps_logs
where '2018-07-12 10:00:00' <= created_at and created_at < '2018-07-13 10:00:00'
但涉及到单独的日期
和时间
会更加复杂。
select *
from gps_logs
where (created_date = '2018-07-12' and created_time >= '10:00:00')
or (created_date = '2018-07-13' and created_time < '10:00:00');
你可以切换到支持部分索引的数据库,如Postgresql。部分索引允许您仅对值的一部分或函数结果进行索引。而且Postgresql在很多方面都比MySQL更好。这是我推荐的。
尽可能多地使用SQL完成工作。
例如,如果您想要知道每天每个设备的日志条目数,而不是将所有行提取出来并自己进行计算,您可以使用group by按设备和日期对它们进行分组。
select gps_device_id, count(id) as num_entries, created_at::date as day
from gps_logs
group by gps_device_id, day;
gps_device_id | num_entries | day
1 | 29310 | 2018-07-12
2 | 23923 | 2018-07-11
2 | 23988 | 2018-07-12
如果你拥有如此庞大的数据,你需要大量依赖 group by
和相关的聚合函数,例如 sum
, count
, max
, min
等等。
避免使用 select *
如果你必须检索 86400 行数据,从数据库中获取所有这些数据的费用可能会很高。你可以通过只获取所需列来显著加快速度,这意味着使用 select only, the, specific, columns, you, need
而不是 select *
。
将所有内容整合在一起
在PostgreSQL中
你在PostgreSQL中的架构应该看起来像这样。
create table gps_devices (
id serial primary key,
name text not null
);
create table gps_logs (
id bigserial primary key,
gps_device_id int references gps_devices(id),
created_at timestamp not null default current_timestamp,
latitude numeric(12,9) not null,
longitude numeric(12,9) not null
);
create index timestamp_and_device on gps_logs(created_at, gps_device_id);
create index date_and_device on gps_logs((created_at::date), gps_device_id);
一个查询通常只能使用一张表的一个索引。由于您经常会一起搜索时间戳和设备ID,timestamp_and_device
结合了时间戳和设备ID的索引。
date_and_device
是同样的道理,但它只是在时间戳的日期部分上建立的部分索引。这将使得 where created_at::date = '2018-07-12' and gps_device_id = 42
非常高效。
在MySQL中
create table gps_devices (
id int primary key auto_increment,
name text not null
);
create table gps_logs (
id bigint primary key auto_increment,
gps_device_id int references gps_devices(id),
foreign key (gps_device_id) references gps_devices(id),
created_at timestamp not null default current_timestamp,
latitude numeric(12,9) not null,
longitude numeric(12,9) not null
);
create index timestamp_and_device on gps_logs(created_at, gps_device_id);
虽然很相似,但没有部分索引。所以你要么总是在你的where
子句中使用裸的created_at
,要么切换到单独的date
和time
类型。