我有一个活动,我必须在一年中的每一天设置一个小时的间隔,在哪个时间播放,或者不播放。
例如:将在一年中的每一天播放,除了星期一和星期二,它不会播放从7点到9.30。
如何有效地在SQL数据库中存储结构?我有365天48个半小时的间隔。我不想对不同的表使用外键,因为它效率很低。
谢谢。
最简单的表是这样的:它可能不是最好的结构,因为没有显式地存储半小时时间段的末尾。然而……
create table campaign_times (
campaign_name varchar(35) not null,
time_segment timestamp not null,
play boolean not null default true,
primary key (campaign_name, time_segment)
);
我生成了一堆随机的活动名称(大约175个),并将这些名称与全年的半小时间隔交叉连接:3,083,520行。我知道我需要time_segment上的索引。我还为play添加了一个索引,以防PostgreSQL可以使用它。(PostgreSQL对低选择性列的智能处理在过去不止一次让我感到惊讶。)
create index on campaign_times (time_segment);
create index on campaign_times (play);
确保统计数据是最新的。
analyze campaign_times;
现在让我们看看情况到底有多糟。
explain analyze
select *
from campaign_times
where current_timestamp between time_segment and time_segment + interval '30 minutes'
and play = true;
"Index Scan using campaign_times_time_segment_idx on campaign_times
[snip]
"Total runtime: 498.713 ms"
不到半秒从300万行表中获取当前播放列表。无需考虑诸如删除旧行、尝试更周到的索引、存储更少的行(例如仅在今天日期前一个月)等优化。
我可以接受。
在生产中,我需要外键和检查约束。这些不会影响PostgreSQL中SELECT语句的速度,我也不认为会影响MySQL中的SELECT速度。(好吧,检查约束肯定不会,因为MySQL不会强制执行它们。)