针对大型表使用范围 WHERE 子句优化 SQL 查询

Question

我有一个数据库表，它有三列：ID、时间戳和字符串。 每天大约插入 14,000 行，因此表非常大。 它目前有 130 万行。

表定义：

CREATE TABLE readings (
  id int primary key auto_increment,
  ts datetime not null, --the timestamp
  json text not null --the string
);

我正在运行的查询是：

SELECT * FROM readings WHERE ts >= 'TIME_START' AND ts <= 'TIME_END' ORDER BY ts

执行查询大约需要 45 秒。 如何修改表和/或查询以使其更快？

谢谢。

Answer 1

在表ts上添加新索引。

Answer 2

我唯一想到的是假设你把一切都设置正确的分区。您可能还想尝试不同的数据库引擎（例如。InnoDB）。或者在 ts 列中设置索引。

除此之外 - 使用>= <= vs BETWEEN 不会对性能产生任何影响，所以不用担心。

Answer 3

您可以使用自动增量的主要 ID 对结果进行排序。

Answer 4

尝试以下查询.. 如果索引在 id 上，这可能会更快

declare @min_id as int
declare @max_id as int
select @min_id = min(id)
from readings WHERE ts = 'TIME_START' 
select @max_id = max(id)
from readings WHERE ts = 'TIME_END'
SELECT * FROM readings  
id between @min_id and @max_id order by id