我有时间序列数据,我想存储在格式的数据库中：
> group：string
>日期：日期
> val1：数字
> val2：数字
> … valN
这个数据库几乎都是读取数据库.搜索将针对属于日期范围内的组的行(例如,组= XXX,日期> = START和日期< = END). 数据集很大.数亿行. sqlite能否轻松处理这类数据？关于sqlite的吸引人之处在于它是无服务器的,如果可以,我想使用它.
解决方法
更新的答案
我尝试将1亿个记录数据库放在RAM磁盘上 – 我不是指内存数据库,我的意思是将RAM格式化为文件系统,其上有数据库,同样的查询需要11秒而不是147秒,如下所示！如果如你所说,你的查询基本上是只读的,那么这可能是你的选择 – 你可以在早上将你的数据库从磁盘复制到RAMdrive并在RAMdrive上整天快速访问它,或直到重启,如果电源断电或机器崩溃,不必担心丢失它.然后,您可以针对基于磁盘的副本运行任何写入,并且它会在您下次将数据库复制到RAM时反映出更改.
原始答案
我根据您的需要创建了三个测试数据库,并在Perl中使用以下代码分别在每个数据库中插入了100万,1000万和1亿条记录.我使用前缀“GROUP-”和记录号合成了组名,并生成了1900到2000之间的随机日期,值数据是随机的.
#!/usr/bin/perl use strict; use DBI; my $dsn = "dbi:sqlite:dbname=test.db"; my $user = ''; my $password = ''; my %attr = ( RaiseError => 1,AutoCommit => 0 ); my $dbh = DBI->connect($dsn,$user,$password,\%attr) or die "Can't connect to database: $DBI::errstr"; $dbh->do("DROP TABLE IF EXISTS TimeSeries;"); $dbh->do("CREATE TABLE TimeSeries (grp TEXT,date TEXT,val1 INTEGER,val2 INTEGER,val3 INTEGER,val4 INTEGER,PRIMARY KEY(grp,date))"); my $sql = qq{ INSERT INTO TimeSeries VALUES ( ?,?,? ) }; my $sth = $dbh->prepare( $sql ); for(my $i=0;$i<100000000;$i++){ # Synthesize a group my $group=sprintf("GROUP-%d",$i); $sth->bind_param(1,$group); # Generate random date between 1900-2000 my $year=int(rand(100))+1900; my $month=int(rand(12))+1; my $day=int(rand(28)+1); my $date=sprintf("%d-%02d-%02d 00:00:00.0",$year,$month,$day); $sth->bind_param(2,$date); $sth->bind_param(3,int(rand(1000000))); $sth->bind_param(4,int(rand(1000000))); $sth->bind_param(5,int(rand(1000000))); $sth->bind_param(6,int(rand(1000000))); $sth->execute(); if(($i % 1000)==0){printf "$i\n";$dbh->commit();} } $dbh->commit(); $sth->finish(); $dbh->disconnect();
对于1m,10m和100m记录,文件大小如下：
-rw-r--r-- 1 mark staff 103M 4 Feb 14:16 1m.db -rw-r--r-- 1 mark staff 1.0G 4 Feb 14:18 10m.db -rw-r--r-- 1 mark staff 11G 4 Feb 15:10 100m.db
一些随机记录如下所示：
GROUP-794|1927-12-14 00:00:00.0|233545|700623|848770|61504 GROUP-797|1927-06-13 00:00:00.0|315357|246334|276825|799325 GROUP-840|1927-09-28 00:00:00.0|682335|5651|879688|247996 GROUP-907|1927-05-19 00:00:00.0|148547|595716|516884|820007 GROUP-1011|1927-06-01 00:00:00.0|793543|479096|433073|786200
然后我尝试查询1927年的所有记录,如下所示：
time sqlite3 1m.db 'select * from timeseries where date between "1927-01-01" and "1927-12-31"'
查询时间如下：
all records in year 1927 from 1m record database => 2.7 seconds all records in year 1927 from 10m record database => 14 seconds all records in year 1927 from 100m record database => 147 seconds
这些数字对我来说看起来非常线性,但这是一个简单的测试,你可能想要接受代码并再玩一遍……
附：我正在运行一个相当不错的规格iMac与SSD.
