能处理五亿以上行数的数据库

Question

我正在寻找一个能够在合理的时间内为某一列创建索引并在少于3秒的时间内提供选择查询结果的数据库，它需要处理超过5亿行。在低端机器（Core 2 CPU 6600，4GB，64位系统，Windows VISTA）上使用Postgresql或Msql能否处理如此大量的数据？

更新：我问这个问题是为了了解应该在低端机器上使用哪种数据库来为where子句中指定的一两个字段提供选择查询结果，没有联接操作。我需要创建索引——这不能像mysql那样花费很长时间——以实现我的选择查询的足够性能。这台机器是用来执行实验的测试PC。

表模式：

 create table mapper {
        key VARCHAR(1000),
        attr1 VARCHAR (100),
        attr1 INT,
        attr2 INT,
        value VARCHAR (2000),
        PRIMARY KEY (key),
        INDEX (attr1), 
        INDEX (attr2)   
    }

- Skarab

---

再次强调，这取决于许多因素，包括行大小、连接等。除了说“每个DBMS都可以做到这一点，具体取决于你的数据”之外，任何人都无法给出明确的答案。从技术上讲，各种DBMS之间性能差异将归结为实际数据及其处理方式。 - NotMe

在阐述这个问题时，我正在寻找有关在低端机器上应该使用哪种数据库的信息，以便为select查询提供结果，其中在where子句中指定了一个或两个字段。没有joins操作。我需要创建索引（不像在mysql上那样需要花费很长时间），以实现足够高效的选择查询性能。 - Skarab

@Skarab：请注意，获得您问题的确切答案的方法是创建一个数据库，填充它与5×10⁸个随机记录，并进行测试。是的，这需要更长的时间（特别是如果您需要安装SQL服务器），但至少您可以确保做出正确的选择。 - Arseni Mourzenko

PostgreSQL在处理大型数据集方面表现不是很好。可能需要采用更高级的方法，例如聚合表。 - sam yi

@Skarab，如果你已经忘记了并且ChrisLively的回答对你有帮助，你应该真正地接受它。 - Rachael

Answer 1

MSSQL可以轻松处理如此多的行。查询时间完全取决于很多因素，而不仅仅是简单的行数。

例如，它将依赖于：

1. 这些查询要连接多少个表
2. 索引设置得有多好
3. 机器中有多少RAM
4. 处理器速度和数量
5. 硬盘类型和转速
6. 在查询中返回的行大小/数据量
7. 网络接口速度/延迟

很容易遇到一个小表（少于10,000行），执行查询需要几分钟的情况。例如，在Atom处理器、512MB总内存且有很多连接、where子句中有函数，并且没有索引的情况下。

确保所有索引和外键关系都良好，优化查询以消除不必要的函数调用并仅返回实际所需的数据需要更多的工作。此外，您需要快速的硬件。

所有这些都归结为您想花多少钱，开发团队的质量以及您正在处理的数据行的大小。

更新由于问题更改需要进行更新。

这里的信息量仍然不足以给出现实世界的答案。您将不得不测试它并根据需要调整数据库设计和硬件。

例如，我可以很容易地在拥有这些规格的机器上（指问题描述中提到的规格），在一个包含10亿行的表中运行“select top(1) id from tableA (nolock)”查询，并在毫秒级别内得到答案。同样，您可以执行“select * from tablea”查询，虽然查询执行速度很快，但将所有数据传输到另一端需要一段时间。

重点是需要进行测试。这意味着要设置服务器、创建一些表并填充数据。然后，您需要进行性能调整，以使您的查询和索引正确无误。作为性能调整的一部分，您不仅会发现需要重新构建查询，还会根据锁定和等待类型确定可能需要更换的机器部件（例如：磁盘、更多内存、CPU 等）。

我强烈建议您雇用（或承包）一到两名数据库管理员来为您完成此工作。

- NotMe

---

这是对我所有 SQL 焦虑的额外帮助回答。我希望我能够给更多的积分/特别收藏答案！:) - Rachael

它所说的非常有道理，那么为什么要在其他数据库供应商中推广任何一个，特别是因为 MSSQL Server 不在最初的范围内？ - alci

最初的问题指定了“Msql”。（请注意缺少定义它的第二个字母）我最初将其解读为MSsql而不是MySQL，因为它被标记为“sql-server”；这是微软产品的标签，因此要调用微软的数据库服务器。但在事后评论中，他实际上使用了术语“mysql”。因此，在发布这个答案时，OP使用的实际DB服务器并不清楚。 - NotMe

Answer 2

大多数数据库都能处理这个问题，关键在于您如何处理和利用这些数据。拥有更多的RAM（内存）将会有所帮助。

我建议先尝试使用PostgreSQL数据库，它是免费的，没有RAM限制（不像SQL Server Express），也不存在许可证问题（例如太多处理器等）。但这也是我的工作领域 :)

- Frank Heikens

Answer 3

几乎所有非愚蠢的数据库现在都能轻松处理十亿行数据。即使在32位系统上，也可以处理5亿行数据（尽管64位系统真的有所帮助）。

主要问题是：

• 您需要拥有足够的RAM。需要多少RAM取决于您的查询。
• 您需要拥有良好的磁盘子系统。这基本上意味着如果您想执行大型选择操作，则完全不可能将所有内容都放在单个碟片上。需要许多碟片（或SSD）来处理IO负载。

无论是Postgres还是Mysql，在适当的硬件上都可以轻松处理5亿行数据。

- TomTom

---

根据我的经验，当表格非常长时，Mysql在索引属性方面经常会出现问题。 - Skarab

MySQL在查询计划方面可能会遇到麻烦，它的功能相当有限。但这取决于您的查询类型，和平常一样。 - Frank Heikens

Answer 4

你需要关注的是数据库软件所施加的表大小限制。例如，截至本文撰写时，MySQL InnoDB每个表有64 TB的限制，而PostgreSQL每个表有32 TB的限制；两者都没有对每个表的行数设置限制。如果正确配置，这些数据库系统不应该在处理数十亿行（如果每行足够小）甚至5亿行时出现问题。

为了处理极大量的数据并获得最佳性能，您需要具备足够的磁盘空间和良好的磁盘性能，这可以通过适当的RAID磁盘实现，并且需要大量内存以及快速处理器（理想情况下使用服务器级别的英特尔Xeon或AMD Opteron处理器）。不用说，您还需要确保您的数据库系统已经配置为最佳性能，并且您的表已经正确索引。

- bwDraco

---

似乎可以手动增加PostgreSQL的限制：http://dba.stackexchange.com/questions/28556/manually-increase-postgresqls-table-size-limit-of-32-tb 或通过分区。 - rogerdpack

Answer 5

以下文章讨论了在Microsoft SQL中导入和使用一个包含160亿行的表。 https://www.itprotoday.com/big-data/adventures-big-data-how-import-16-billion-rows-single-table。

来自文章的描述：

以下是我的经验总结： 在一个已定义聚集索引的表中，数据量越大，导入未排序记录的速度就会变得越慢。到某个时候，它变得太慢而不实用。如果您想将表导出为最小可能的文件，请使用本地格式。对于主要包含数字列的表格，这种方法效果最佳，因为它们在二进制字段中的表示比字符数据更紧凑。如果所有数据都是字母数字型的，那么以本地格式导出并没有多大的优势。在数字字段中不允许空值可以进一步压缩数据。如果允许字段为空，则该字段的二进制表示将包含一个1字节的前缀，指示后面有多少字节的数据。由于BCP计数器变量是4字节整数，因此无法对超过2,147,483,647条记录使用BCP。我在MSDN或互联网上找不到任何相关的参考资料。如果您的表格包含超过2,147,483,647条记录，则必须分块导出或编写自己的导出程序。在已填充的表格上定义聚集索引需要大量磁盘空间。在我的测试中，在完成之前，我的日志爆炸增长了10倍的原始表格大小。在使用BULK INSERT语句导入大量记录时，请包括BATCHSIZE参数并指定每次提交多少条记录。如果不包括此参数，则整个文件将作为单个事务导入，这需要大量日志空间。将数据预先排序是将数据最快地导入带有聚集索引的表格的方法。然后，您可以使用BULK INSERT语句和ORDER参数导入它。

即使与多PB的纳斯达克OMX数据库相比，它也很小，该数据库在SQL Server上存储了数万TB和数万亿行。

- Charles Burns

Answer 6

你有没有尝试过Cassandra？http://cassandra.apache.org/

- adamzwakk

---

那会解决什么问题呢？每个数据库都可以处理这么多的记录。 - Frank Heikens

"Core 2 CPU 6600，4GB，64位系统，Windows VISTA" 不是我建议使用Cassandra的机器类型。 - Michiel Cornille

@rogerdpack 请查看 https://techcrunch.com/2010/09/07/digg-struggles-vp-engineering-door/。 - mtsz

Answer 7

我对于哪个系统是最好的没有太多意见，但也许这个技巧可以帮助你获得一些你想要的速度。

如果你将要进行长varchar字符串的精确匹配，特别是那些超过索引允许长度的字符串，你可以进行一种预先计算的哈希操作：

CREATE TABLE BigStrings (
   BigStringID int identity(1,1) NOT NULL PRIMARY KEY CLUSTERED,
   Value varchar(6000) NOT NULL,
   Chk AS (CHECKSUM(Value))
);
CREATE NONCLUSTERED INDEX IX_BigStrings_Chk ON BigStrings(Chk);

--Load 500 million rows in BigStrings

DECLARE @S varchar(6000);
SET @S = '6000-character-long string here';

-- nasty, slow table scan:
SELECT * FROM BigStrings WHERE Value = @S

-- super fast nonclustered seek followed by very fast clustered index range seek:
SELECT * FROM BigStrings WHERE Value = @S AND Chk = CHECKSUM(@S)

如果您不进行精确匹配，这并不能帮助您，但在这种情况下，您可以考虑全文索引。这将真正改变对包含500万行的表的查找速度。

- ErikE

Answer 8

如今几乎所有的数据库都可以处理这种情况 - 你需要关注的是磁盘 I/O 子系统。你需要配置 RAID 0 或 RAID 0+1，尽可能多地投入问题所需的主轴。此外，为了提高性能，还要将日志/临时/数据逻辑驱动器分开。

例如，假设你有 12 个驱动器 - 在 RAID 控制器中，我会创建 3 个 RAID 0 分区，每个分区有 4 个驱动器。在 Windows 中（假设），将每个组格式化为逻辑驱动器（G、H、I）- 现在在配置 SQLServer（假设）时，将 tempdb 分配给 G，将日志文件分配给 H，将数据文件分配给 I。

- bigtang

---

我在 MSSQL 数据库中有一个包含 16 列的表，其中包含 1.5 亿条记录，我每天平均插入 3.25 Lac 行数据。在插入每个新行之前，我会检查表中的 5 个列值。如果基于这 5 个列值没有找到匹配的行，则只能插入新行。请建议一种高效的方法？提前致谢！ - dilipkumar1007

对于剩余的路人，请勿将您的数据放在RAID0或RAID0+1上。 RAID 0具有数据丢失的风险；如果任何一个驱动器出现故障，所有数据都会丢失。 RAID 0+1没有同样的风险，但比RAID 1+0的风险更高。 请咨询真正的专家。如果无法咨询专家，请使用多个RAID1（镜像）阵列。 SQL Server足够聪明，可以针对多个阵列跨越IO处理大型表。使用更多磁盘可以获得接近线性的性能提升，并降低数据丢失的风险。此外，RAID1阵列通常可以在没有RAID控制器的情况下恢复，如果您的控制器失败。 - Charles Burns

Answer 9

我需要创建索引（不像mysql一样花费很长时间），以实现足够的查询性能。

我不确定你所说的“创建”索引指的是什么。通常，这只需要做一次。当加载大量数据时，通常会删除索引、加载数据，然后再添加索引，以使数据加载非常快速。然后，随着对数据库的更改，索引将得到更新，但并不一定需要在每次查询运行时都重新创建。

尽管如此，数据库确实有查询优化引擎，它们将分析您的查询并确定检索数据的最佳计划，查看如何连接表（在您的场景中不相关），以及可用的索引，显然，您希望避免全表扫描，因此进行性能调整和审核查询计划是很重要的，正如其他人已经指出的那样。

上面提到的校验和看起来很有趣，甚至可以成为同一张表中attr1的索引。

- michaelok