我在工作中从事的一个软件项目一直让我发疯。这是我们的问题:我们有一系列数据联系人需要每秒记录一次。它需要包括时间、方位(360-1080 字节的数组)、范围和其他一些字段。我们的系统还需要能够将这些数据存储长达 30 天。实际上，最多可以有 100 个不同的联系人，因此在 30 天内最多可以有大约 150,000,000 个点到大约 1,000,000,000 个不同的点。

我正在考虑存储所有这些数据并在以后检索的最佳方法。我的第一个想法是使用像 MySQL 这样的 RDBMS。作为一名嵌入式 C/C++ 程序员，我对使用 MySQL 处理如此大的数据集的经验很少。我在小数据集上涉足过它，但没有那么大。我为将存储一些数据的两个表生成了以下架构:

CREATE TABLE IF NOT EXISTS `HEADER_TABLE` (
  `header_id` tinyint(3) unsigned NOT NULL auto_increment,
  `sensor` varchar(10) NOT NULL,
  `bytes` smallint(5) unsigned NOT NULL,
  PRIMARY KEY  (`header_id`),
  UNIQUE KEY `header_id_UNIQUE` (`header_id`),
  UNIQUE KEY `sensor_UNIQUE` (`sensor`)
) ENGINE=MyISAM AUTO_INCREMENT=0 DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `RAW_DATA_TABLE` (
  `internal_id` bigint(20) NOT NULL auto_increment,
  `time_sec` bigint(20) unsigned NOT NULL,
  `time_nsec` bigint(20) unsigned NOT NULL,
  `transverse` bit(1) NOT NULL default b'0',
  `data` varbinary(1080) NOT NULL,
  PRIMARY KEY  (`internal_id`,`time_sec`,`time_nsec`),
  UNIQUE KEY `internal_id_UNIQUE` (`internal_id`),
  KEY `time` (`time_sec`)
  KEY `internal_id` (`internal_id`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `rel_RASTER_TABLE` (
  `internal_id` bigint(20) NOT NULL auto_increment,
  `raster_id` int(10) unsigned NOT NULL,
  `time_sec` bigint(20) unsigned NOT NULL,
  `time_nsec` bigint(20) unsigned NOT NULL,
  `header_id` tinyint(3) unsigned NOT NULL,
  `data_id` bigint(20) unsigned NOT NULL,
  PRIMARY KEY  (`internal_id`, `raster_id`,`time_sec`,`time_nsec`),
  KEY `raster_id` (`raster_id`),
  KEY `time` (`time_sec`),
  KEY `data` (`data_id`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

表头表只有10行，是静态的。它只是告诉原始数据来自哪个传感器，以及该类型传感器输出的字节数。 RAW_DATA_TABLE 本质上存储原始方位数据(一个 360-1080 字节的数组，它表示每度最多三个样本)。 rel_RASTER_TABLE 保存 RAW_DATA_TABLE 的元数据，可以有多个联系人引用相同的原始数据行。在 rel_RASTER_TABLE 中找到的 data_id 指向 RAW_DATA_TABLE 中某行的 internal_id，我这样做是为了减少所需的写入量。

显然，您可能会说，我在从该数据库中读取和删除时遇到了性能问题。我们软件的运算符(operator)可以看到实时数据，还可以进入重建模式并覆盖过去(例如过去一周)的数据范围。我们的后端日志服务器获取历史行并通过 CORBA 接口(interface)将它们发送到显示器。当所有这一切发生时，我有一个工作线程，它一次删除 1000 行超过 30 天的数据。这是为了防止 session 运行时间超过 30 天，这可能会发生。

我们目前实现的系统适用于较小的数据集，但不适用于大型数据集。我们的 select 和 delete 语句可能需要 2 分钟以上才能返回结果。这完全扼杀了我们实时消费者线程的性能。我怀疑我们没有正确设计我们的模式，选择了错误的键，没有正确优化我们的 SQL 查询，或者每个的某些子集。除非其他操作运行时间过长，否则我们的写入不会受到影响。

这是我们用来获取历史数据的 SQL 查询示例:

SELECT 
  rel_RASTER_TABLE.time_sec, 
  rel_RASTER_TABLE.time_nsec, 
  RAW_DATA_TABLE.transverse, 
  HEADER_TABLE.bytes, 
  RAW_DATA_TABLE.data 
FROM 
  RASTER_DB.HEADER_TABLE, 
  RASTER_DB.RAW_DATA_TABLE, 
  RASTER_DB.rel_RASTER_TABLE 
WHERE 
  rel_RASTER_TABLE.raster_id = 2952704 AND 
  rel_RASTER_TABLE.time_sec >= 1315849228 AND 
  rel_RASTER_TABLE.time_sec <= 1315935628 AND 
  rel_RASTER_TABLE.data_id = RAW_DATA_TABLE.internal_id AND 
  rel_RASTER_TABLE.header_id = HEADER_TABLE.header_id;

对于这么长的问题，我提前表示歉意，但我已经利用了其他资源，这是我最后的选择。我想我会尽可能地描述性强，你们第一眼看到我可以改进我们的设计的任何方式吗？或者，无论如何我们可以针对如此大的数据集优化我们的 select 和 delete 语句？我们目前正在运行 RHEL 作为操作系统，不幸的是无法更改服务器上的硬件配置(4 GB RAM，四核)。我们正在使用 C/C++ 和 MySQL API。任何速度改进都将非常有益。如果您需要我澄清任何事情，请告诉我。谢谢!

编辑:顺便说一句，如果您不能提供具体帮助，也许您可​​以将我链接到您在优化 SQL 查询、模式设计或 MySQL 调优方面遇到的一些优秀教程？

最佳答案

您可以尝试的第一件事是对数据进行反规范化。在这样大小的数据集上，即使您有索引，进行连接也需要非常密集的计算。将这三张 table 变成一张 table 。当然会有重复数据，但如果没有连接，使用起来会容易得多。第二件事，看看你能不能得到一台有足够内存的机器来把整个表放在内存中。对于具有 24GB RAM 的机器来说，它的成本并不高(1000 美元或更少)。我不确定这是否会保存您的整个数据集，但它也会极大地帮助您获得 SSD。对于未存储在内存中的任何内容，SSD 应该可以帮助您高速访问它。第三，研究其他数据存储技术，例如 BigTable旨在处理非常大的数据集。

关于c++ - 大量数据的MySQL性能问题，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/7448785/