c# - MySQL 服务器在选择 blob 时变慢，直到所有查询超时

Question

我将个人资料照片存储在我的数据库中，并且我编写了一个小型 C# 控制台应用程序，该应用程序应该只是将这些照片导出到磁盘中的某个位置。我一次循环选择 10 张照片:

const int pageSize = 10;

using (var connection = new MySqlConnection(_options.Connectionstring))
{
    connection.Open();
    var pageCount = pageSize;

    for (var page = 0; pageCount == pageSize; page++)
    {
        using (var command = new MySqlCommand($"SELECT
              p.FirstName, p.LastName, p.Department, ph.Data
          FROM Persons p
          INNER JOIN Photos ph ON ph.PersonId = p.Id
          LIMIT {pageSize} OFFSET {page * pageSize}", connection)
        using (var reader = command.ExecuteReader())
        {
            for (pageCount = 0; reader.Read(); pageCount++)
            {
                var path = GetFilePath(reader, _options.Pattern);
                EnsureDirectoryExists(path);
                File.WriteAllBytes(path, (byte[])reader["Data"]);
            }
        }
    }
}

代码略有修改以使其更简洁，但我主要删除了一些验证和日志记录。

在运行应用程序时在 MySql Workbench 中监视服务器显示“InnoDB 缓冲区使用率”缓慢上升，直到大约 2 分钟或选择 1000 张图像后达到 100%。根据我的阅读，这是完全正常的，但应用程序导出图像的速度也越来越慢，直到 InnoDB 缓冲区接近 100%，此时应用程序开始超时:

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

数据库服务器信息:

• MySQL 服务器 5.6
• 所有表都使用 InnoDB
• innodb_buffer_pool_size = 1GB
• 在具有 8 GB RAM 的 Windows Server 2012 上运行

照片表定义:

CREATE TABLE `Photos` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `PersonId` int(11) NOT NULL,
  `Data` longblob NOT NULL,
  PRIMARY KEY (`Id`),
  KEY `FK_PersonId_IDX` (`photo_person_id`),
  CONSTRAINT `FK_PersonId` FOREIGN KEY (`PersonId`) REFERENCES `Persons` (`Id`)
          ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

应用程序本身的内存使用率非常低。增加 innodb_buffer_pool_size 确实会增加问题发生之前的时间(或导出的照片数量)，与大小的增加成正比，但我不想为了能够导出这些图像而必须向服务器添加更多内存.

在我看来，用大的 blob 填充 InnoDB 缓冲池会导致这个问题，但据我所知，如果我确实想选择所有这些 blob，那真的没有办法阻止这种情况的发生，所以呢我可不可以做？我意识到我可能会增加超时，但出于显而易见的原因，这只是一个糟糕的解决方案，我考虑过将照片表更改为 MyISAM，我认为这可以解决问题，但如果有其他简单的解决方案，我宁愿不这样做.如果实际上没有其他方法可以解决此问题，我也愿意接受完全替代的照片导出解决方案。

我真的不知道还有哪些其他信息是相关的，所以请在评论中询问任何其他详细信息。

Answer 1

不要使用 OFFSET，它必须遍历所有这些行才能到达您真正想要的 10 行。此外，如果要添加/删除行，您可能会遗漏或重复一行。

改为remember where you left off

如果由于某种原因无法实现，则使用“惰性评估”，您只获取 10 个 ID，而不获取其余列。这是在子查询中。然后 JOIN 返回表以获取所需列的其余部分。