输入格式
每个输入文件都包含一次光谱仪运行；每次运行都由一组扫描组成，并且每次扫描都有一个有序的数据点数组。有少量元数据，但文件的大部分由32位或64位整数或浮点数组组成。
主机系统
|----------------+-------------------------------| | OS | Windows 2008 64-bit | | MySQL version | 5.5.24 (x86_64) | | CPU | 2x Xeon E5420 (8 cores total) | | RAM | 8GB | | SSD filesystem | 500 GiB | | HDD RAID | 12 TiB | |----------------+-------------------------------|
使用可忽略的处理器时间，服务器上正在运行其他一些服务。
文件统计信息
|------------------+--------------| | number of files | ~16,000 | | total size | 1.3 TiB | | min size | 0 bytes | | max size | 12 GiB | | mean | 800 MiB | | median | 500 MiB | | total datapoints | ~200 billion | |------------------+--------------|
数据点的总数是一个非常粗略的估计。
提议的架构数据，例如crazy）和
，因此将有一个
runs表，一个带有spectra外键的runs表，和一个带有
datapoints外键的spectra表。2000亿个数据点问题
我将要分析多个光谱甚至可能是多个
运行，导致查询可能涉及数百万行。假设我正确索引了所有内容（这是另一个问题的话题），而又没有尝试在网络上随机播放数百个MiB，那么MySQL处理这个问题在远程上是否合理？
br />
其他信息
扫描数据将来自基于XML的文件
mzML格式。此格式的内容位于存储数据的
<binaryDataArrayList>元素中。每次扫描都会产生> = 2个
<binaryDataArray>元素，这些元素加在一起形成[[123.456, 234.567, ...], ...]形式的二维（或更多）数组。
这些数据是一次写入的，因此更新性能和事务安全性没有问题。
我对数据库架构的天真的计划是：
runs表| column name | type | |-------------+-------------| | id | PRIMARY KEY | | start_time | TIMESTAMP | | name | VARCHAR | |-------------+-------------|
spectra表| column name | type | |----------------+-------------| | id | PRIMARY KEY | | name | VARCHAR | | index | INT | | spectrum_type | INT | | representation | INT | | run_id | FOREIGN KEY | |----------------+-------------|
datapoints表/> | column name | type | |-------------+-------------| | id | PRIMARY KEY | | spectrum_id | FOREIGN KEY | | mz | DOUBLE | | num_counts | DOUBLE | | index | INT | |-------------+-------------|
这合理吗？
所以，正如您可能已经推断出的，我是程序员，而不是生物学家
在实验室中，所以我对科学的了解程度不及实际科学家。
这是我对所用数据类型进行单一频谱（扫描）绘制的图将处理
：
该软件的目标是弄清楚峰的位置和重要性。我们现在使用专有的软件包来解决这个问题，但是我们希望
编写自己的分析程序（在R中），以便我们了解工作原理到底是怎么回事
。如您所见，绝大多数数据都是无趣的，但是我们不想丢掉我们算法遗漏的可能有用的数据。一旦我们有了一个满足的可能峰列表，其余的管道将使用该峰列表，而不是原始的数据点列表。我想将原始数据点存储为一个大blob就足够了，因此可以在需要时对其进行重新分析，但仅将这些峰保留为不同的数据库条目。在这种情况下，每个频谱只有一对
个峰值，因此疯狂的缩放比例问题不应该太大。
#1 楼
我对您的需求不是很熟悉，但是将每个数据点存储在数据库中可能有点过大。听起来几乎就像是通过将每个像素作为单独的记录存储在关系数据库中来采用存储图像库的方法。一般来说，在大多数情况下将二进制数据存储在数据库中是错误的。通常，有解决该问题的更好方法。虽然将二进制数据存储在关系数据库中并不是天生的错误，但通常弊端大于收益。顾名思义，关系数据库最适合存储关系数据。二进制数据不是关系数据。它增加了数据库的大小（通常是很大的），会损害性能，并可能导致有关维护十亿记录的MySQL实例的问题。好消息是，有些数据库特别适合存储二进制数据。其中一个虽然并不总是很明显，但却是文件系统！只需为二进制文件提供一个目录和文件命名结构，然后将它们与其他可能通过查询产生价值的数据一起存储在MySQL DB中。另一种方法是使用基于文档的存储系统来存储您的数据点（可能是光谱）数据，并使用MySQL进行运行（或将运行与其他数据库放置在同一数据库中）。
评论
为什么将二进制数据存储在数据库中被认为是错误的？ （之所以问是因为我很好奇，还是因为我能想到一个用例。）
–kevin628
2012年7月3日在16:44
如果二进制数据没有单独的值，则不应将其存储为唯一行。图像上的像素500x325不相关。
– JustinDanielson
2012年7月3日17:39
这是非常好的一点。我们可能应该保留原始文件，以防日后需要再次拉出东西时使用，但类似于存储图像是一个很好的选择。我们将永远不需要访问每个数据点（除非我们要重新进行峰提取），因此简单地存储提取的统计信息会更好。
–哈克斯尼
2012年7月4日在16:16
#2 楼
我曾经使用过非常大的（Terabyte +）MySQL数据库。我们拥有的最大表实际上超过了十亿行。该版本使用的是MySQL 5.0，因此可能情况有所改善。它起作用了。 MySQL在大多数情况下都能正确处理数据。但是，这非常笨拙。 （如果要获得具有TB级数据的六个sigma级别的可用性，请不要使用MySQL。我们是一家没有DBA和资金有限的初创公司。）
备份和存储数据只是一个挑战。如果需要的话，恢复表将需要几天的时间。
我们有很多表，行数在10到1亿之间。对表的任何重要连接都非常耗时，并且要花很长时间。因此，我们编写了存储过程来“遍历”表，并针对“ id”的范围进行过程联接。这样，我们一次可以处理10-100,000行的数据（加入id的1-100,000，然后加入100,001-200,000，依此类推）。这比连接整个表要快得多。
在不基于主键的大型表上使用索引也要困难得多。 Mysql 5.0将索引存储为两部分-它存储索引（主索引除外）作为主键值的索引。因此，索引查找分为两个部分：首先，MySQL转到索引，并从索引中拉出需要查找的主键值，然后对主键索引进行第二次查找，以找到这些值在哪里。
这样做的净结果是，对于非常大的表（1-2亿行加上表），对表的索引的限制更为严格。您需要更少，更简单的索引。而且即使执行不直接在索引上的简单选择语句也可能永远不会回来。子句必须命中索引或将其遗忘。
但是，话虽如此，事情确实有效。我们能够对这些非常大的表使用MySQL，并进行计算并获得正确的答案。
试图对2000亿行数据进行分析将需要非常高端的硬件以及大量的耐心和耐心。仅以可以恢复的格式备份数据将是一项重要的工作。
我同意srini.venigalla的回答，在这里将数据标准化为疯狂可能不是一个好主意。在具有大量数据的多个表之间进行联接将使您面临文件排序的风险，这可能意味着您的某些查询将永远不会回来。使用简单的整数键进行非正规化将为您提供更大的成功机会。
我们拥有的一切都是InnoDB。关于MyISAM与InnoDB：主要是不要将两者混为一谈。由于MySQL缓存密钥和其他数据的方式，您不能真正针对这两者优化服务器。如果可以，请为服务器中的所有表选择一个或另一个。 MyISAM可能会解决一些速度问题，但可能无法解决需要完成的整个DBA工作-这可能是致命的。
#3 楼
像疯狂一样标准化数据
像疯狂一样标准化数据可能不是正确的策略。通过以标准化形式和高度适合您的应用程序的物化视图形式存储数据，从而保持选择状态不变。这类应用程序的关键是不编写临时查询。查询建模比数据建模更重要。从您的目标查询开始，并朝着最佳数据模型的方向努力。
Is this reasonable?
我将使用此表作为所有查询的主要来源。原因是避免进行任何连接。没有索引的联接将使您的系统非常无法使用，并且在如此大的文件上建立索引同样会很糟糕。
策略是，首先在上表中查询，将结果转储到临时表中并联接临时表以及“运行”和“光谱”查询表，并获取所需的数据。
您是否已分析了写入需求与读取需求？放弃SQL并使用非标准数据存储机制将非常诱人。我认为，这应该是最后的手段。
为了加快写入速度，您可能需要尝试Handler Socket方法。如果我记得，Percona会将Handler Socket打包在其安装包中。 （与Percona无关！）
http://yoshinorimatsunobu.blogspot.com/2010/10/using-mysql-as-nosql-story-for.html
#4 楼
简短的答案是肯定的答案-随着行数的增长，精确的模式，您选择的数据类型和操作的重要性也会增加。在存储的数据上。特别是您的“数据点”表似乎有问题-您是否打算将任何给定光谱的第n个点与其他光谱的第m个点进行比较？如果没有，将它们分开存放可能是一个错误。如果您的数据点不是单独存在的，而是仅在关联光谱的上下文中才有意义，则您不需要主键-光谱的外键和“第n”列（您的“索引”列？）就足够了。定义必须执行的频谱间和频谱内操作，然后找出完成这些操作的最便宜的方法。如果只需要相等性，则可以将它们归一化-可能带有一些有助于您的操作的预先计算的统计元数据。如果您确实需要对单个数据点进行SQL访问，请确保将每行的大小减小到最少的字段数和最小的数据类型。
我亲自管理过的最大的MySQL是大约1亿行。在这种大小下，您希望保持行大小，从而使字段保持固定大小-这使MySQL可以通过乘以每行的固定大小乘以有效地计算表中任何行的位置（请考虑指针算术），尽管具体细节取决于您计划使用的存储引擎。如果可以使用MyISAM，它在速度上弥补了可靠性方面的不足，并且在您的情况下就足够了，请使用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），并确保平衡架构以支持最频繁的操作。
我说的简短吗？哎呀反正祝你好运！
#5 楼
似乎将数据点数据从XML（与运行时间和类型等元数据相对）分解为数据库形式的唯一原因是，当您分析跨阵列的光谱时-即也许找到所有具有一定的签名运行。现在只有您知道您的问题域，但这可能类似于存储以96kHz采样的音乐（每行1个采样）。我不确定大小的问题比数据的使用方式更多。跨数据查询等同于由甲壳虫乐队询问所有歌曲中2分钟的相对振幅。如果您知道可以执行的分析类型，则很有可能对信号执行这些分析并将其存储在有关运行的元数据中会更有意义。我也不确定是否您的源数据稀疏。数据库中的光谱完全可能只包含非零条目，而原始XML确实包含零条目，因此您的总行数可能比源数据中的少得多。
因此，就像许多问题一样，在询问MySQL处理模型之前，退后一步并查看模型以及模型的使用方式可能比担心性能还更合适。
查看完问题的更新后，我认为将二进制数据存储为BLOB或仅将指针存储到文件的模型就足够了，并致力于修改模型以存储有关重要峰的数据。首先读取数据。
#6 楼
我运行了一个Web分析服务，其中包含约50个数据库服务器，每个服务器包含许多表，这些表超过1亿行，而有些表往往超过10亿行，有时每台服务器多达20亿行。这里的表现很好。这是非常标准化的数据。但是，-我主要的阅读目的是，您将远远超过这些表的42亿行标记（可能不是“运行”，可能还有其他两个），这意味着您需要使用BIGINT而不是INT主键/外键。
与INT相比，在索引列中使用BIGINT字段的MySQL性能令人震惊。我犯了一个错误，那就是用一张我认为可能会超出此大小的表来做一次，一旦它达到几亿行，性能就简直是糟糕透顶。我没有原始数字，但是当我说不好的时候，我的意思是Windows ME不好。
此列是主键。我们将其转换回仅为INT并具有魔力，性能再次良好。
我们当时的所有服务器都在Debian 5和MySQL 5.0上运行。从那以后，我们已升级到Debian 6和Percona MySQL 5.5，因此此后情况可能有所改善。但是根据我在这里的经验，不，我认为它不会很好用。
#7 楼
无论它是否有效，您总是会在单个整体存储介质中遇到相同的问题：磁盘速度很慢。以100 MB / s（非常适合旋转媒体）的速度，读取1TB表格需要3个小时；假设没有任何分析，搜索或其他延迟会使您减速。这就是为什么几乎每个“大数据”安装都使用某种分布式数据存储的原因。您可以花费8倍的钱来构建一台超级出色的计算机来运行数据库，但是如果您有大量可以并行扫描的数据，则最好总是在8台便宜的计算机上分配负载。
像hadoop这样的项目是专门为此类目的而构建的。您将由一堆廉价计算机组成一个集群，将数据分布在所有这些计算机中，并进行并行查询。只是基于相同想法的六种解决方案之一，但这是一个非常受欢迎的解决方案。
#8 楼
嗯...我看到了您选择这种数据结构的两个原因：您真的需要执行任何数据点与任何数据点查询
现在，我建议您认真研究一下您的需求，并验证至少上述假设之一是正确的。如果都不是真的，那只会使事情变慢。对于这种数据集，我建议您先找出如何访问数据，需要什么样的精度等等，然后再根据这些数据设计数据库。
PS：保留请记住，每个数据点至少需要36 + 5字节，因此使用200B数据点应至少提供8.2 TB的所需空间。
P.P.S .：您不需要
id表中的datapoints列，一个PRIMARY KEY (spectrum_id, index)可能就足够了（请注意，index可能是保留字）#9 楼
编辑：不要在MYSQL中将数据存储在单个磁盘上进行此操作。仅从单个介质读取该数据量将花费数小时。您需要向外扩展，而不是向上。
如果您想进行有效的数据分析，则需要对数据进行规范化处理。您不是在这里设计在线系统。您想计算数字，进行相应的设计。
下面的行的原始答案。
答案会因您的查询而异，MySQL可能不是此工作的最佳工具。您可能想看看可以“向外扩展”而不是“向上扩展”的解决方案。如果您愿意付出一些努力，也许您应该考虑使用诸如Hadoop之类的Map Reduce解决方案。 。 Google I / O 2012的相关演示：使用BigQuery处理大数据
因此，解决方案将取决于这是否是一次性的事情，以及您是否要合理地支持即席查询。
#10 楼
没有人提到，因此是我的建议。看一下大规模分片的MySQL解决方案。例如，请参阅此备受推崇的tumblr演示。概念是：
而不是一个超大型数据库
使用许多小的数据库部件原始数据
，因此您可以水平缩放，而不必尝试提高垂直性能。 Google的BigTable和GFS也在使用便宜的水平可扩展节点来存储和查询PB的数据。
但是，如果您需要对不同的分片运行查询，则会遇到麻烦。
如果有兴趣的人，我前段时间做了一个hello-world sharding应用程序。在博客文章中对此进行了讨论。我使用了RavenDB和C＃，但细节无关紧要，想法是相同的。
#11 楼
数据将存储在哪种机器上？它是共享存储设备吗？决定您查询时间的最终因素将是硬盘驱动器。数据库及其查询优化器旨在减少磁盘I / O的数量。假设您只有3个表，这将非常可靠地完成。
硬盘的读/写速度将比内存速度慢200-300倍。寻找具有极高延迟和快速读写速度的硬盘驱动器。如果所有这些数据都在一个2 TB驱动器上，则可能要等待很长时间才能完成查询。硬盘驱动器延迟约为10-15毫秒，而内存延迟则小于10纳秒。硬盘驱动器延迟可能比内存延迟慢1000-2000倍。在整个系统中，机械臂在硬盘驱动器上的移动是最慢的事情。
您有多少RAM？ 16 GB？假设您可以保存32条记录。您有16000个文件。如果要线性扫描所有数据点，则很容易仅花费5-10秒的搜索时间即可结束。然后考虑传输速率50mb / s？大约7个小时。此外，任何临时保存的数据都必须存储在硬盘驱动器上，以便为读取新数据腾出空间。
如果您使用的是其他用户正在积极使用的共享存储设备...您最好的选择是晚上运行所有内容。
减少嵌套查询的数量也很有帮助。嵌套查询会生成临时表，这将进一步打击您的硬盘。我希望您的硬盘驱动器上有足够的可用空间。
查询优化一次只能查看1个查询。因此无法优化嵌套的select语句。但是，如果您知道特定的嵌套查询将导致返回小的数据集，请保留该数据集。查询优化使用直方图和粗略的假设，如果您了解有关数据和查询的某些知识，请继续进行操作。
您对数据在磁盘上存储方式的了解越多，编写查询的速度就越快。如果所有内容都按顺序存储在主键上，则对从嵌套查询返回的主键进行排序可能是有益的。另外，如果您可以减少所有需要预先分析的数据集，请执行此操作。根据系统的不同，每个文件的数据传输时间约为1秒。
如果要修改Name值（varchars），我会将其更改为带有最大大小，它将防止碎片，并且权衡只是更多的内存字节。也许NVARCHAR的最大值为100。
关于对表进行非规范化的注释。我认为最好将数据点存储在较大的组中（可能是频谱），然后使用python或与数据库交互的语言进行数据分析。除非您是SQL向导。
评论
您强调了硬盘与内存延迟之间的巨大差异，但是您的数字却相差1000倍。如果硬盘的延迟约为10ms，内存的延迟为10ns，则延迟的相差不会相差1000倍，而应为1,000,000！
–spectre256
2012年7月3日在22:28
#12 楼
对我来说，这听起来像是一个使用场景，您需要像此处所述的“关系列存储”之类的东西。 ，将它们存储在典型的面向行的表中意味着每个元素都类似于一个切片。如果您有兴趣以一种典型的方式查看切片，这是有道理的，但是如果您一次真正查看整个列，则效率可能会降低。检索数组时，不仅也许您不需要将其归一化后再与另一个表连接，但是您可以将序列作为数组而不是哈希进行检索。
我真的可能是误解了这个问题，但我不是甚至建议一个特定的解决方案。
这是另一个可能相关的话题，即使它并不是当前或可部署的解决方案。
#13 楼
我建议您尝试对表进行分区。我们在一个表（股票市场数据）中有超过8000万行，因此可以快速访问它。根据您打算如何搜索数据，应该设计分区。在我们的情况下，按日期工作很好，因为我们查询特定的日期。
http://dev.mysql.com/doc/refman/5.1/en/partitioning-limitations.html
http://www.slideshare.net/datacharmer/mysql-partitions-tutorial
#14 楼
是的，但是...我已经处理了具有20亿行的表。但是，只有使用PK的查询才有望很快。
最重要的是，硬件具有足够的RAM以容纳整个表在内存中。当这成为一个问题时（当时最大为96GB），进行了垂直分区，将每台计算机上设置的表的大小保持足够小，以便仍可容纳在内存中。而且，这些机器通过10Gb光纤连接，因此网络吞吐量并不是什么大问题。
顺便说一句。您的架构看起来像某种适合NoSQL解决方案的东西，使用
run_id作为频谱的哈希键，而spectrum_id作为数据点的哈希键。 #15 楼
我已经在我的博客上写过有关此主题的文章：http://www.tocker.ca/2013/10/24/improving-the-performance-of-large-tables-in-MySQL.html
要重复一些关键点：
B树会随着它们变大而不适合内存而退化（MySQL在这里并不孤单）。
InnoDB确实具有一些功能来维持某些性能（更改缓冲；以前称为“插入缓冲区”）。
分区也可以提供帮助。
在我的帖子中，Tim Callaghan的评论已链接为此：
http://www.tokutek.com/resources/benchmark-results/benchmarks-vs-innodb-hdds/#iiBench
其中显示了使用iibench插入10亿行基准。
评论
Facebook正在使用MySQL进行扩展由于这是原始的A / D轮询质谱仪数据，因此将其存储在数据库中似乎很愚蠢。我将获取原始数据，将其转储，处理并将处理后的结果存储在数据库中。结果将是（a）每行存储一个波形的波形，（b）与这些波形相关的其他数据（如校准曲线），以及（c）数据库中的结果行。这将减少设计中的数十亿行膨胀。当您想重新运行初始分析时，您将有效地编辑一些参数，运行巨大的计算操作，并将新结果存储在数据库中。