数百万条数据的SQLite优化？

Question

数百万条数据的SQLite优化？

3

我正在尝试使用SQLite数据库和Perl模块解决一个问题。最终，我需要记录数千万条目。每个项目的唯一标识符只是URL的文本字符串。我考虑用两种方式来做这件事：

方法1：有好表、坏表和未排序表。（需要检查HTML并决定是否需要它。）假设总共有10亿个页面，每个表中有3.33亿个URL。如果我有一个新的URL需要添加，我需要检查并查看它是否在任何一个表中，在未排序表中添加它（如果它是唯一的）。此外，这个选项需要移动很多行。

方法2：我有两个表，Master和Good。Master包含所有10亿个页面的URL，而Good则包含我想要的3.33亿个页面。需要添加新URL时，需要做同样的事情，但这次只查询一个表，并且永远不会从Master删除行，只将数据添加到Good中。

因此，我需要知道如何最好地设置SQLite数据库，以便快速查询一个巨大的SQLite数据库，查看一个大约20个字符的文本字符串是否唯一，如果不是，则添加。

use BerkeleyDB;

$dbFolder = 'C:\somedirectory';
my $env = BerkeleyDB::Env->new ( -Home => $dbFolder );

my $db  = BerkeleyDB::Hash->new (
-Filename => "fred.db", 
-Env => $env );
my $status = $db->db_put("apple", "red");

当我运行这个程序时，我得到以下结果：

Can't call method "db_put" on an undefined value at C:\Directory\perlfile.pl line 42, <STDIN> line 1.

- VolatileRig

3

我不是数据库专家（所以我可能低估了SQLite），但你确定SQLite是处理那么多数据的最佳解决方案吗？ - Matteo Italia

我从很多人那里听说过，SQLite在处理大量数据方面非常出色。https://dev59.com/RHA75IYBdhLWcg3wrrNS - VolatileRig

3

针对大型数据集，最好的SQLite优化方法是不要使用SQLite。从我的经验来看，随着时间的推移，SQLite在插入方面会明显变慢，尤其是如果您有需要不断重新计算的索引。SQLite 的理论极限远远超出其实际使用极限。 - MPelletier

1

我认为像BerkeleyDB这样的DBM对于这个应用程序来说会更快。使用您的字符串作为键，并使用YAML或Storable序列化所有其他数据。 - daotoad

2

实际上，我一直在尝试让BerkeleyDB正常工作。但是我无法让它创建单个文件。我会发布我所拥有的内容，也许你可以帮助我解决问题。 - VolatileRig

3个回答

2

如果$db未定义，则打开数据库将失败，您需要检查$!和$BerkeleyDB::Error以了解原因。

您已经创建了数据库吗？如果没有，您需要使用-Flags => DB_CREATE。

工作示例：

use strict;
use warnings;
use BerkeleyDB;

my $dbFolder = '/home/ysth/bdbtmp/';

my $db  = BerkeleyDB::Hash->new (
    -Filename => "$dbFolder/fred.db", 
    -Flags => DB_CREATE,
) or die "couldn't create: $!, $BerkeleyDB::Error.\n";

my $status = $db->db_put("apple", "red");

然而，我无法让BerkeleyDB::Env做任何有用的事情；无论我尝试什么，构造函数都会返回undef。

- ysth

我添加了DB_CREATE并在创建环境后检查了$!，但它只显示“没有这样的文件或目录”。您能否给我提供一个可行的示例供我分析？我所需要做的就是在磁盘上启动哈希表，向其中添加项目，并检查现有项目。 - VolatileRig

1

@Sho Minamimoto：添加了一个示例。 - ysth

搞定了。我想问题在于我只写了"fred.db"而不是完整路径，但文档中说无论如何DB都应该建在Env所在的地方。无论怎样，感谢你的帮助！ - VolatileRig

最后一个问题，如果我有数百万条记录，如何按值进行排序？比如，如果我的一些主键URL的值为'0'，另一些为'1'，我如何只获取值为'0'的URL？这样做是否快速？ - VolatileRig

1

@Sho Minamimoto：不，必须经过坏文件肯定会减慢获取好文件的速度。如果您需要尽可能快地完成此操作，则最好使用好的、坏的和未排序的文件（或者也许是好的、全部和未排序的？我不确定您想如何使用它）。 - ysth

我有一个包含1000万个URL的列表，以及解析它的代码，但如果代码在任何时候出现问题会怎么样呢？我无法确定所有页面在任何给定时间都处于相同的状态（有些可能未排序，有些好的，有些坏的，而且总会有新的URL）。我不想从#1重新开始。我认为3个Berkeley DB仍然可以让我保持O(c)，因为每个URL需要对3个哈希进行3个查询。 - VolatileRig

2

我不知道这是否是最优的，但您可以设置SQLite数据库，使“good”表在URL列上具有唯一约束条件。您可能没有足够的RAM来使用Perl进行比较（天真的解决方案是创建一个哈希表，其中URL是键，但如果您有10亿个页面，则需要大量内存）。

当插入数据时，数据库将执行唯一性并在尝试插入重复的URL时抛出某种错误。只要DBI返回不同的错误值以表示不同的错误消息，您就可以捕获此错误并忽略它。

- CanSpice

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Tim · Accepted Answer

我倾向于使用哈希表而不是SQLite来实现你想要做的事情。哈希表被优化为在不需要保持任何排序顺序或冗余数据副本的情况下测试存在性。应用于数据的哈希算法会产生一个位置，如果该数据存在，它将被存储在那里；你可以寻找该位置并查看是否存在。我认为你不需要将哈希表保存在RAM中。

以下是如何采用混合哈希/SQLite方法：

创建一个SQLite表。

STORE
id INTEGER PRIMARY KEY
BUCKET (integer, indexed) 
URL (text, not indexed)
status

如果您想将它们按状态分开，可以拥有三个这样的表：STORE1、STORE2和STORE3。

假设每个存储区将包含250,000,001个不同的桶。（您可以尝试使用此数字；使其成为质数）。

找到一个哈希算法，它需要两个输入，URL字符串和250,000,0001，并返回1到250,000,001之间的数字。

当您获得一个URL时，将其提供给哈希算法，它会告诉您应该查找哪个BUCKET：

选择* from STORE where BUCKET = {哈希函数返回的值}。

您在BUCKET字段上的索引将迅速返回行，然后您可以检查URL。如果当前URL不是其中之一，请添加它：

INSERT STORE(BUCKET, URL) VALUES( {your hash return value}, theURL).

SQLite将为整数值建立索引，这比为URL建立索引更有效率。而且URL只会被存储一次。