也许这个问题应该在https://dba.stackexchange.com/上提出,我不确定.请在评论中提出建议或将其移至那里.

对于这个项目,我使用的是在Amazon RDS上托管的MysqL 5.6.19.

摘要

我要在InnoDB表的BLOB列中将照片存储在数据库中,我想知道最佳方法.我正在寻找可以比较不同变体的官方文档或某些方法.

在搜索此主题时,有很多讨论和问题是关于将二进制文件存储在数据库BLOB中还是存储在文件系统中是否更好,数据库只包含文件路径和名称.这种讨论超出了这个问题的范围.对于这个项目,我需要一致性和参照完整性,因此文件将存储在BLOB中,问题在于如何准确地执行它.

数据库架构

这是架构的相关部分(到目前为止).有一个表合同,其中包含有关每个合同和主要ID密钥的一些常规信息.
对于每个合同,可以拍摄几张(~10张)照片,所以我有一张表ContractPhotos：

CREATE TABLE `ContractPhotos` (
  `ID` int(11) NOT NULL,`ContractID` int(11) NOT NULL,`PhotoDateTime` datetime NOT NULL,PRIMARY KEY (`ID`),KEY `IX_ContractID` (`ContractID`),CONSTRAINT `FK_ContractPhotos_Contracts` FOREIGN KEY (`ContractID`) REFERENCES `Contracts` (`ID`),) ENGINE=InnoDB DEFAULT CHARSET=utf8

对于每张照片,我将存储原始的全分辨率图像以及少量缩小版本,因此我有一张表ContractPhotoVersions：

CREATE TABLE `ContractPhotoVersions` (
  `ID` int(11) NOT NULL,`ContractPhotoID` int(11) NOT NULL,`PhotoVersionTypeID` int(11) NOT NULL,`PhotoWidth` int(11) NOT NULL,`PhotoHeight` int(11) NOT NULL,`FileSize` int(11) NOT NULL,`FileMD5` char(32) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,KEY `IX_ContractPhotoID` (`ContractPhotoID`),CONSTRAINT `FK_ContractPhotoVersions_ContractPhotos` FOREIGN KEY (`ContractPhotoID`) REFERENCES `ContractPhotos` (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

最后,有一个表格可以保存所有图像的实际二进制数据.我知道MysqL允许在LONGBLOB列中存储高达4GB,但在我的搜索过程中我遇到了另一个MysqL限制：max_allowed_packet.在我的MysqL实例上,这个变量是4MB.阅读文档后我对这个变量的理解是有效的,单行不能超过4MB.拥有超过4MB的照片是很正常的,所以为了能够INSERT和SELECT这样的文件,我打算将文件拆分成小块：

CREATE TABLE `Photochunks` (
  `ID` int(11) NOT NULL,`ContractPhotoVersionID` int(11) NOT NULL,`ChunkNumber` int(11) NOT NULL,`ChunkSize` int(11) NOT NULL,`ChunkData` blob NOT NULL,UNIQUE KEY `IX_ContractPhotoVersionID_ChunkNumber` (`ContractPhotoVersionID`,`ChunkNumber`),CONSTRAINT `FK_Photochunks_ContractPhotoVersions` FOREIGN KEY (`ContractPhotoVersionID`) REFERENCES `ContractPhotoVersions` (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

此外,我将能够一次将大型照片上传到数据库中几个块,并在连接断开时恢复上传.

数据量

估计的数据量是每张约5MB的40,000张全分辨率照片=> 200GB.按比例缩小的版本很可能是800×600,每个约120KB =>额外5GB.图片不会更新.几年后它们最终将被删除.

题

有很多方法可以将文件拆分成更小的块：您可以将其拆分为4KB,8KB,64KB等.使用InnoDB存储引擎以最大限度地减少浪费的空间和整体性能的最佳方式是什么？

我找到了这些文档：http://dev.mysql.com/doc/refman/5.6/en/innodb-file-space.html,但没有太多关于BLOB的细节.它说页面大小是16KB.

The maximum row length,except for variable-length columns (VARBINARY,
VARCHAR,BLOB and TEXT),is slightly less than half of a database
page. That is,the maximum row length is about 8000 bytes.

我真的希望官方文档比大约8000字节更准确.以下段落最有趣：

If a row is less than half a page long,all of it is stored locally
within the page. If it exceeds half a page,variable-length columns
are chosen for external off-page storage until the row fits within
half a page. For a column chosen for off-page storage,InnoDB stores
the first 768 bytes locally in the row,and the rest externally into
overflow pages. Each such column has its own list of overflow pages.
The 768-byte prefix is accompanied by a 20-byte value that stores the
true length of the column and points into the overflow list where the
rest of the value is stored.

考虑到上述情况,至少可以采取以下策略：

>选择这样的块大小,它将在页面中本地存储,而不涉及页外存储.
>选择整个BLOB存储在页外的块大小.
>我不喜欢将BLOB部分存储在页面中而部分位于页面外的想法.但是,嘿,也许我错了.

我也遇到了这个文档https://dev.mysql.com/doc/refman/5.6/en/innodb-row-format-dynamic.html,此时我意识到我想问这个问题.现在对我来说太压倒了,我希望有一个人对这个话题有实际经验.

我不想因无意中选择差的块大小和行格式而浪费一半的磁盘空间.我担心的是,如果我选择在Photochunks表的同一行中为每个块存储8000个字节加上4个整数的16个字节,那么它将超过页面大小的魔法一半,并且最终每行花费16KB仅为8000字节数据的.

有没有办法检查这种方式实际浪费了多少空间？在Amazon RDS环境中,我担心无法查看InnoDB表所包含的实际文件.否则,我会尝试不同的变体,看看最终的文件大小.

到目前为止,我可以看到有两个参数：行格式和块大小.也许还有其他事情需要考虑.

编辑

为什么我不考虑更改max_allowed_pa​​cket变量.从doc：

Both the client and the server have their own max_allowed_packet
variable,so if you want to handle big packets,you must increase this
variable both in the client and in the server.

我使用MySQL C API来处理这个数据库,同一个C应用程序正在使用相同的libMysqL.dll与200个其他MysqL服务器(完全与此项目无关)进行通信.其中一些服务器仍然是MysqL 3.23.所以我的应用程序必须与所有这些一起工作.坦率地说,我没有查看有关如何在MysqL C API的客户端更改max_allowed_pa​​cket变量的文档.

编辑2

@akostadinov指出有mysql_stmt_send_long_data()将BLOB数据发送到服务器的块和人said,他们已经设法INSERT BLOB大于max_allowed_pa​​cket.尽管如此,即使我设法INSERT,比如20MB BLOB,max_allowed_pa​​cket = 4MB,我如何选择它？我不知道怎么做.

如果你指出我正确的方向,我将不胜感激.