如果你有一个良好的设计模式和良好的索引，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类型。
