c++ - 使用 C++ 的 SQL - 瓶颈在哪里？

Question

我正在尝试逐行读取纯文本文件，构建 SQL INSERT 语句，执行查询，然后继续。目前，我有一个解决方案可以在我大约 4 年的旧桌面上每秒完成大约 200 行。但是，我有大约 1.2 亿行代码要处理，并且希望将其作为日常任务来实现。花几个小时完成它会很好，但花将近一周的时间是不行的。

这些行将包含一个字符串和 5 到 9 个整数，范围从 bool 值(我已将其编码为 TINYINT(1))到自午夜以来的微秒数 (BIGINT)。

一旦从文件中读入(通过 getline())，这些行就被这个函数标记化了:

#define MAX_TOKENS 10
#define MAX_TOKEN_LENGTH 32

char tokens[MAX_TOKENS][MAX_TOKEN_LENGTH];

//...

void split_line(const string &s)
{
  char raw_string[MAX_TOKENS * MAX_TOKEN_LENGTH];
  char *rest;
  char *token_string;

  strcpy(raw_string, s.c_str());

  if(tokens[0][0] != '\0')
  {
    fill(tokens[0], tokens[0]+(MAX_TOKENS*MAX_TOKEN_LENGTH), '\0');
  }

  for(uint32_t token = 0; token < MAX_TOKENS; token++)
  {
    if(token == 0) token_string = strtok_r(raw_string, " ", &rest);
    else token_string = strtok_r(nullptr, " ", &rest);

    if(token_string == nullptr) break;

    if(token >= 1)
    {
      //if it's not a number...
      if(token_string[0] < 48 || token_string[0] > 57)
      {
        if(token_string[0] != 45) //negative numbers are allowed
        {
          clear_tokens();
          break;
        }
      }
    }

    strcpy(tokens[token], token_string);
  }
}

我曾尝试过该分词器的更多 STL 派生版本，但事实证明那太慢了。它在调用图中的排名仍然很高，但不如使用正确的 STL 字符串时排名高。

无论如何，下一步是构建 SQL 查询。为此，我尝试了一些事情。一种选择是字符串流。

string insert_query = "INSERT INTO data_20170222";
stringstream values;
string query;

while(getline(input_stream, input_stream_line))
{
  split_line(input_stream_line);

  if(tokens[5][0] != '\0')  //the smallest line will have six tokens
  {
    try
    {
      query = insert_query;
      uint32_t item_type = stoi(tokens[2]);

      switch(item_type)
      {
        case 0: //one type of item
        case 1: //another type of item
        {
          values << " (valueA, valueB, valueC, valueD, valueE, valueF,"
                    " valueG, valueH) values('"
                 << tokens[0] << "', " << tokens[1] << ", "
                 << tokens[2] << ", "  << tokens[3] << ", "
                 << tokens[4] << ", "  << tokens[5] << ", "
                 << tokens[6] << ", "  << tokens[7] << ")";
          break;
        }
        //...
      }

        query.append(values.str());
        values.str(string());
        values.clear();

        if(mysql_query(conn, query.c_str()))
        {
          string error(mysql_error(conn));
          mysql_close(conn);
          throw runtime_error(error);
        }
      }
      catch(exception &ex)
      {
        cerr << "Error parsing line\n  '" << input_stream_line
             << "'\n" << "  " << ex.what() << endl;
        throw;
      }
    }

当我运行这个版本时，我看到 30% 的 callgrind 样本是在 std::operator<< 在 std::basic_ostream 中测量的。

我最初尝试用字符串来做这一切，唉:

string values;
values = " (valueA, valueB, valueC, valueD, valueE, valueF,"
         " valueG, valueH) values('" +
         string(tokens[0]) + "', " + tokens[1] + ", "
         tokens[2] + ", "  + tokens[3] + ", "
         tokens[4] + ", "  + tokens[5] + ", "
         tokens[6] + ", "  + tokens[7] + ")";

事实证明这实际上是相同的速度，但这次将 30% 的样本从 std::basic_string 分配给 std::operator+。

最后，我直接切换到 sprintf()。

char values[MAX_TOKENS * MAX_TOKEN_LENGTH];
sprintf(values, " (valueA, valueB, valueC, valueD, valueE, valueF,"
        " valueG, valueH) values('%s', %s, %s, %s, %s, %s, %s, %s)",
        tokens[0], tokens[1], tokens[2], tokens[3],
        tokens[4], tokens[5], tokens[6], tokens[7]);

stringstream 比 string 稍快(尽管在合理的误差范围内)。 sprintf() 比两者都快大约 10%，但仍然不够快。

当然有一个完善的方法可以用如此大的数据集完成这项任务。在这一点上，我将不胜感激。

编辑

哇哦。我一时兴起注释掉了对 mysql_query() 的调用。事实证明，不管 valgrind 怎么说，这就是我所有减速的地方。没有那个 block ，它从每秒 200 行跳到每秒 120 万行。这还差不多!太糟糕了，我需要数据库中的数据...

我想这已经成为一个问题，为什么现在 MariaDB 似乎运行得如此缓慢。我在这个系统中有一个很好的 SSD，16GB RAM 等。我觉得我的硬件不太可能阻止它。

越想越好奇。在此先感谢您的帮助!

Answer 1

批处理 INSERTing 每个 INSERT 语句 100 行将以 10 倍的速度运行。

您需要每天插入 1.2 亿行吗？那是每秒 1400 个。您是否计算过多久磁盘空间会用完？

让我们看看SHOW CREATE TABLE。当 INT(4 字节)就足够时，不要使用 BIGINT(8 字节)。当 MEDIUMINT(3 个字节)可以时，不要使用 INT。等等

您将如何处理无数行数据？请记住，针对索引不当的表执行格式不正确的 SELECT 将花费很长时间，即使使用 SSD 也是如此。

one字符串可以归一化吗？

考虑将一堆 bool 值打包成 SET(每 8 个 bool 值 1 个字节)或某个大小的 int。

让我们看看SHOW CREATE TABLE 和主要的SELECTs。

innodb_flush_log_at_trx_commit 的值是多少？使用 2.