处理数百万条记录最有效的技术是什么？

Question

我曾经有一个包含2500万条记录的MySQL数据库表，即使是简单的COUNT(*)查询也需要数分钟才能执行。最后我把它们分成几个表来进行分区。我的问题是，有没有一些模式或设计技术可以处理这种问题（大量记录）？在处理大量记录方面，MSSQL或Oracle哪个更好？

备注： 上述的COUNT(*)问题只是一个例子，在实际应用中，这个应用程序进行crud功能和一些聚合查询（用于报告），但并没有什么复杂的内容。只是由于数据量大，执行某些查询需要相当长的时间（数分钟）。

- lonegunman

---

这是一个很好的问题。但标题不太好。如果有高声望的人能改一下，那就好了？ - Nathan

Answer 1

请参见为什么MySQL在处理大表时会变慢和COUNT(*)与COUNT(col)的区别。确保您对进行计数的列创建了索引。如果您的服务器具有足够的RAM，请考虑增加MySQL的缓冲区大小。确保您的磁盘已正确配置--启用DMA，不与交换分区共享驱动器或电缆等。

- John Millikin

---

我以为在MySQL中，主键会自动创建索引...这不是这样吗？ - Adam Bellaire

在MySQL中，主键或唯一约束隐式地创建了一个索引。您不需要额外声明索引。如果这样做，那就是多余的。 - Bill Karwin

Answer 2

你使用 "SELECT COUNT(*)" 所询问的内容并不容易。在 MySQL 中，MyISAM 非事务引擎通过保持记录计数来进行优化，因此 SELECT COUNT(*) 将非常快速。但是，如果你使用事务引擎，则 SELECT COUNT(*) 基本上是在说：

在我的事务中，这个表里到底有多少条记录？

为了做到这一点，引擎需要扫描整个表；它可能已经大致知道表中存在多少条记录，但是为了获得特定事务的精确答案，它需要进行扫描。使用 MySQL InnoDB 不会很快，使用 Oracle 或其他任何东西也不会很快。整个表必须被读取（除了引擎单独存储的东西，如 BLOB）。

将整个表放入 RAM 中将使其变快一些，但仍然不会很快。

如果你的应用程序依赖于频繁、准确的计数，你可能需要创建一个摘要表，该表由触发器或其他手段更新。

如果你的应用程序依赖于频繁、不太准确的计数，你可以使用定期任务维护摘要数据（这可能会对其他操作的性能产生较小的影响）。

- MarkR

---

将整个表格放入内存中会使其稍微快一些，但仍然不会很快。什么？当然会快得多！你的意思可能是有其他解决问题的方法，而不是使用几GB的RAM... - Christian Davén

Answer 3

许多关于大型表的性能问题与索引问题或完全缺乏索引有关。我建议您确保熟悉索引技术和您计划使用的数据库的具体情况。

关于您在巨大表上进行的缓慢count(*)，我会假设您正在使用MySQL中的InnoDB表类型。我有一些使用MyISAM的MySQL表格，拥有超过1亿条记录，而count(*)非常快。

特别是关于MySQL，即使是最常用的两种表类型InnoDB和MyISAM之间也存在轻微的索引差异。了解它们各自的优缺点及如何使用是值得的。

- Jarod Elliott

---

MyISAM会将计数保持分开，因此count(*)的答案将是即时的；InnoDB则不会这样做，因此必须对记录进行计数。 - Mark Baker

Answer 4

你需要什么样的数据访问方式？我曾经使用过HBase（基于谷歌的BigTable）作为一个应用程序的后端，这个应用程序可以在几秒钟内返回大量数据(~30 million rows)。但是，如果你需要“实时”访问 - 即用来支持网站的话，它并不是很适合。此外，它面向列的本质也是相当激进的变化，如果你习惯行向数据库管理系统的话，需要注意一下。

- mrhahn

Answer 5

在整个表上使用 count(*) 实际上是经常要做的事情吗？

InnoDB 必须执行完整的表扫描以计算行数，如果你确实想要计算所有行，则这显然会影响性能，但这并不意味着表上的其他操作将变慢。

通过正确的索引，MySQL 在检索比表格更大的数据时非常快。索引的问题在于它们可能会损害插入速度，特别是对于大型表格，因为一旦索引所需空间达到某个阈值（可能是内存中保留的大小），插入性能就会急剧下降。但如果你只需要适度的插入速度，MySQL 应该可以满足你的所有需求。

任何其他数据库都会在检索速度和插入速度之间进行类似的权衡；它们可能更适合你的应用程序，也可能不是。但我会首先看看如何正确使用索引并可能重写你的查询，然后再尝试其他数据库。就我们选择 MySQL 最初是因为我们发现它的性能最好。

请注意，MySQL 中的 MyISAM 表格存储总表大小。他们保持这样做是因为在某些情况下对优化器有用，但一个副作用是整个表上的 count(*) 确实非常快。这并不一定意味着它们在任何其他方面都比 InnoDB 更快。

- Mark Baker

Answer 6

我在这个Stackoverflow帖子中详细回答了一个类似的问题，描述了两个系统架构的优点。从数据仓库的角度来看，这样做在一定程度上是正确的，但许多差异也对事务性系统很重要。

然而，2500万行并不是一个超大型数据库（VLDB），如果您遇到性能问题，应该考虑索引和调整。您不需要使用Oracle来支持2500万行的数据库 - 您还需要增加3个数量级才能真正进入VLDB领域。

- Nigel Campbell

Answer 7

您要求的是一本书般的答案，因此我建议您获取有关数据库的好书。有很多种选择。

以下是一些数据库基础知识：

首先，您需要一个出色的数据模型，该模型不仅基于您需要存储的数据，而且还基于使用模式。 良好的数据库性能始于良好的架构设计。

其次，根据预期的查找和更新需求在列上放置索引，因为更新性能经常被忽视。

第三，如果有可能，请勿在where子句中放置函数。

第四，使用质量设计的-咳咳-RDBMS引擎。我恭敬地表示，尽管它在最近的过去取得了很大进展，但mysql并不符合条件（对那些希望争论它终于在最近时代达到标准的人表示歉意）。现在不再需要在高价格和质量之间做出选择；Postgres（又称PostgreSql）是开源的，并且真正出色-并且具有满足您需求的所有插件。

最后，了解您要求数据库引擎执行的操作-获得某些内部见解-以便更好地判断哪些操作是昂贵的以及原因。

- Richard T

Answer 8

我要赞同@Mark Baker的观点，你需要在表上建立索引。

对于除了你选择的查询之外的其他查询，你还应该知道，在查询中使用IN()这样的结构比一系列OR语句更快。有很多小步骤可以加速单个查询。

- warren

Answer 9

索引是处理大量记录的关键，但查询语句的编写方式也会产生很大的影响。具体的性能调优方法因数据库而异，但通常应避免返回比实际需要更多的记录或字段，确保所有连接字段都被索引（以及常见的where子句字段），避免使用游标（尽管我认为这在Oracle中不如SQL Server那么重要，但我不知道mySQL如何）。

硬件也可能成为瓶颈，特别是如果您在同一台机器上运行除数据库服务器之外的其他东西。

性能调优是一个非常技术性的主题，无法在这样的格式中得到很好的回答。我建议您获取一本性能调优书籍并阅读它。这里有一个针对mySQL的链接： http://www.amazon.com/High-Performance-MySQL-Optimization-Replication/dp/0596101716

- HLGEM