我对您的需求不是很熟悉，但也许将每个数据点存储在数据库中有点矫枉过正。这听起来就像采用通过将每个像素作为单独的记录存储在关系数据库中来存储图像库的方法。

作为一般规则，在数据库中存储二进制数据大多数时候都是错误的。通常有更好的方法来解决问题。虽然将二进制数据存储在关系数据库中并没有本质上的错误，但通常弊大于利。顾名思义，关系数据库最适合存储关系数据。二进制数据不是关系型的。它增加了数据库的大小（通常显着），可能会损害性能，并可能导致有关维护十亿记录 MySQL 实例的问题。好消息是有些数据库特别适合存储二进制数据。其中之一是您的文件系统，虽然并不总是显而易见的！只需为您的二进制文件提供目录和文件命名结构，

另一种方法是使用基于文档的存储系统来存储您的数据点（可能还有光谱）数据，并使用 MySQL 进行运行（或者可能将运行放入与其他数据库相同的数据库中）。

我曾经使用过一个非常大的（Terabyte+）MySQL 数据库。我们拥有的最大的表实际上超过了 10 亿行。这是使用 MySQL 5.0，所以情况可能有所改善。

有效。MySQL 大部分时间都正确处理了数据。不过，它非常笨拙。（如果你想要 6 sigma 级别的可用性和 1 TB 的数据，请不要使用 MySQL。我们是一家没有 DBA 且资金有限的初创公司。）

仅备份和存储数据是一项挑战。如果我们需要，恢复表需要几天时间。

我们有许多在 10-1 亿行范围内的表。对表的任何重要连接都太费时了，而且需要很长时间。因此，我们编写了存储过程来“遍历”表并针对“id”范围处理连接。通过这种方式，我们一次处理 10-100,000 行数据（加入 id 的 1-100,000 然后 100,001-200,000 等）。这比加入整个表要快得多。

在不基于主键的非常大的表上使用索引也更加困难。Mysql 5.0 将索引存储为两部分——它将索引（除了主索引）存储为主键值的索引。因此索引查找分两部分完成：首先 MySQL 转到索引并从中提取它需要查找的主键值，然后对主键索引进行第二次查找以查找这些值在哪里。

这样做的结果是，对于非常大的表（1-2 亿多行），针对表的索引更具限制性。您需要更少、更简单的索引。即使是不直接在索引上的简单选择语句也可能永远不会回来。Where 子句必须命中索引或忘记它。

但话虽如此，事情确实奏效了。我们能够将 MySQL 与这些非常大的表一起使用，并进行计算并得到正确的答案。

试图对 2000 亿行数据进行分析需要非常高端的硬件以及大量的手持和耐心。仅以您可以恢复的格式保存数据备份将是一项重要的工作。

我同意srini.venigalla 的回答，即疯狂地标准化数据在这里可能不是一个好主意。对具有这么多数据的多个表进行连接将使您面临文件排序的风险，这可能意味着您的某些查询永远不会回来。使用简单的整数键进行非规范化会给您更大的成功机会。

我们拥有的一切都是 InnoDB。关于 MyISAM 与 InnoDB：主要是不要将两者混为一谈。由于 MySQL 缓存键和其他数据的方式，您无法真正优化服务器。如果可以的话，为服务器中的所有表选择一个或另一个。MyISAM 可能有助于解决一些速度问题，但它可能无助于需要完成的整体 DBA 工作——这可能是一个杀手。

疯狂地规范化数据

在这种情况下，疯狂地标准化数据可能不是正确的策略。通过以规范化形式和高度适合您的应用程序的物化视图形式存储数据，让您的选择保持开放。这类应用程序的关键不是编写即席查询。查询建模比数据建模更重要。从您的目标查询开始，朝着最佳数据模型努力。

Is this reasonable?

我还将创建一个包含所有数据的附加平面表。

run_id | spectrum_id | data_id | <data table columns..> |

我将使用该表作为所有查询的主要来源。原因是为了避免必须进行任何连接。没有索引的连接将使您的系统非常不可用，并且在如此大的文件上建立索引同样可怕。

策略是，先查询上表，将结果转储到临时表中，并将临时表与 Run 和 Spectrum 的查找表连接起来，得到你想要的数据。

您是否分析过您的写入需求与读取需求？放弃 SQL 并使用非标准数据存储机制将是非常诱人的。在我看来，这应该是最后的手段。

要加快写入速度，您可能需要尝试 Handler Socket 方法。如果我记得的话，Percona 将 Handler Socket 打包在他们的安装包中。（与 Percona 无关！）

http://yoshinorimatsunobu.blogspot.com/2010/10/using-mysql-as-nosql-story-for.html

简短的回答是肯定的——随着行数的增加，您选择的精确模式、数据类型和操作的重要性也会增加。

规范化数据的程度取决于您计划对存储的数据执行的操作。特别是您的“数据点”表似乎有问题——您是否打算将任何给定光谱的第 n 个点与任何其他光谱的第 m 个点进行比较？如果没有，单独存储它们可能是一个错误。如果您的数据点不是独立的，而是仅在其相关光谱的上下文中有意义，则您不需要主键 - 光谱的外键和“第 n”列（您的“索引”列？）就足够了.

定义您必须执行的频谱间和频谱内操作，然后找出最便宜的方式来完成它们。如果只需要相等，它们可能会被非规范化——可能使用一些预先计算的统计元数据来帮助您的操作。如果您确实需要对单个数据点进行 in-SQL 访问，请确保将每行的大小减少到尽可能少的字段数和最小的数据类型。

我个人管理过的最大的 MySQL 大约有 1 亿行。在这种大小下，您希望保持行和字段的大小固定——这允许 MySQL通过乘以每行的固定大小（想想指针算术）来有效地计算表中任何行的位置——尽管确切的细节取决于您计划使用的存储引擎。如果您可以摆脱它，请使用 MyISAM，它在速度上弥补了它在可靠性方面的不足，并且在您的情况下它就足够了。将可变大小字段（例如 VARCHAR）替换为 CHAR(n) 并在读取查询中使用 RTRIM()。

一旦你的表行是固定宽度的，你可以通过仔细评估 MySQL 的整数数据类型（其中一些是非标准的）来减少字节数。通过将 4 字节 INT 转换为 3 字节 MEDIUMINT 可以勉强节省 1 字节，每百万行可以节省约 1MB ——这意味着更少的磁盘 I/O 和更有效的缓存。使用您可以摆脱的最小可能数据类型。仔细评估浮点类型，看看是否可以用 4 字节 FLOAT 甚至 <8 字节定点 NUMERIC替换 8 字节 DOUBLE 。运行测试以确保你选择的任何东西都不会在以后咬你。

根据您的数据集的预期属性和所需的操作，可能会进一步节省您的值的更不寻常的编码（可以编码为一组值的索引的预期模式/重复，原始数据可能只会有意义地有助于元数据并被丢弃等）——尽管只有在尝试了所有其他选项时才值得进行奇异的、不直观的、破坏性的优化。

最重要的是，无论您最终做什么，都不要假设您已经选择了完美的模式，然后盲目地开始倾倒数以千万计的记录。好的设计需要时间来发展。创建大量但易于管理（例如 1-5%）的测试数据集，并验证模式的正确性和性能。查看不同操作的执行方式（http://dev.mysql.com/doc/refman/5.0/en/using-explain.html）并确保平衡架构以支持最频繁的操作。

我说短了吗？哎呀。不管怎样，祝你好运！

似乎将数据点数据从 XML（与运行的时间和类型等元数据相反）切碎并放入数据库表单的唯一原因是当您跨阵列分析光谱时 - 即可能找到所有以特定签名运行。现在只有您知道您的问题域，但这可能类似于存储以 96kHz 采样的音乐，每行 1 个样本。我不确定大小比数据的使用方式更重要。对数据进行查询相当于询问披头士乐队所有歌曲的 2 分钟后的相对振幅。如果您知道可能执行的分析类型，则很有可能对信号执行这些分析并将其存储在有关运行的元数据中可能更有意义。

我也不确定您的源数据是否稀疏。数据库中的频谱完全有可能只包含非零条目，而原始 XML 确实包含零条目，因此您的总行数可能比源数据中的要少得多。

因此，像许多问题一样，在询问 MySQL 处理您的模型之前，退一步查看模型以及将如何使用它可能比担心性能更合适。

在查看您的问题更新后，我认为将二进制数据存储为 BLOB 或只是指向文件的指针的模型就足够了，并且可以修改您的模型以存储有关在数据首次出现时已识别的重要峰值的数据读。

I run a web analytics service with about 50 database servers, each one containing many tables over 100 million rows, and several that tend to be over a billion rows, sometimes up to two billion (on each server).

The performance here is fine. It is very normalized data. However - my main concern with reading this is that you'll be well over the 4.2 billion row mark for these tables (maybe not "runs" but probably the other two), which means you'll need to use BIGINT instead of INT for the primary/foreign keys.

MySQL performance with BIGINT fields in an indexed column is ridiculously horrible compared to INT. I made the mistake of doing this once with a table I thought might grow over this size, and once it hit a few hundred million rows the performance was simply abysmal. I don't have raw numbers but when I say bad, I mean Windows ME bad.

This column was the primary key. We converted it back to be just an INT and presto magico, the performance was good again.

All of our servers at the time were on Debian 5 and with MySQL 5.0. We have since upgraded to Debian 6 and Percona MySQL 5.5, so things may have improved since then. But based on my experience here, no, I don't think it will work very well.

Whether or not it works, you're always going to run into the same problem with a single monolithic storage medium: disks are slow. At 100 MB/s (pretty good for spinning media) it takes 3 hours just to read a 1TB table; that's assuming no analysis or seeking or other delays slow you down.

This is why very nearly every "big data" installation uses some sort of distributed data store. You can spend 8 times as much money building one super amazing computer to run your DB, but if you have a lot of data that can be scanned in parallel, you're almost always better off distributing the load across the 8 cheaper computers.

Projects like hadoop were build specifically for purposes like this. You build a cluster of a whole bunch of inexpensive computers, distribute the data across all of them, and query them in parallel. It's just one of a half a dozen solutions all built around this same idea, but it's a very popular one.

嗯...我认为您选择这种数据结构的原因只有两个：

• 你真的需要做任何数据点与任何数据点查询
• 您打算在 SQL 中执行所有逻辑

现在，我建议仔细研究您的要求，并验证上述假设中至少有一个是正确的。如果两者都不是真的，那么你只是让事情变得更慢。对于这种数据集，我建议首先找出预期如何访问数据、您需要什么样的准确性等 - 然后围绕这些设计您的数据库。

PS：请记住，每个数据点至少需要 36+5 字节，因此对于 200B 数据点，您至少需要 8.2 TB 的空间。

PPS：您不需要表id中的列datapoints，PRIMARY KEY (spectrum_id, index)可能就足够了（请注意index可能是保留字）

编辑：

不要在 MYSQL 中使用存储在单个磁盘上的数据执行此操作。仅从单一介质读取这么多数据将需要数小时。您需要扩大规模，而不是扩大规模。

如果要进行有效的数据分析，则需要对数据进行非规范化。你不是在这里设计一个在线系统。你想处理数字，相应地设计。

下面的原始答案。

答案会因您的查询而异，MySQL 可能不是这项工作的最佳工具。您可能想查看可以“扩大”而不是“扩大”的解决方案。如果您愿意付出一些努力，也许您应该查看 Map Reduce 解决方案，例如 Hadoop。

如果您想进行更多临时查询，Google 的 BigQuery解决方案可能非常适合您。Google I/O 2012 的相关演示：使用 BigQuery 处理大数据

因此，解决方案将取决于这是否是一次性的事情以及您是否想要合理地支持临时查询。

No one has mentioned, thus my suggestion. Take a look at massively sharded MySQL solutions. For example, see this highly regarded tumblr presentation.

The concept is:

• Instead of one extra large database
• Use many small ones holding parts of the original data

Thus you can scale horizontally, instead of trying to improve vertical performance. Google's BigTable and GFS are also using cheap horizontally scalable nodes to store and query petabytes of data.

However, there will be troubles if you need to run queries over different shards.