MySQL 中存储序列化对象和性能
我们希望将序列化对象与唯一的内部 ID 一起存储在表中。 我们只想读/写/（很少）更新行。 我们永远不会只与 ID 交互序列化字段。 ，我们使用 InnoDB
首先
；将序列化存储为文本类型字段是否正确？
第二;
如果我们不直接与 r/w 以外的序列化字段交互，是否会影响数据库的性能？
最后;
将序列化对象存储在我们的文件系统中会更好吗？
为了让我们稍微了解一下为什么我们首先要存储它们，我们从供应商那里收到一个对象，用户需要选择几个选项，并且我们需要将精确的对象与修改后的（选择的）组件一起发回。
We want to store a serialized object in a table along with a unique internal ID.
We'd only ever want to read/write/(rarely)update the rows.
We'd never interact with the serialized field just the ID.
We're using InnoDB,
Firstly;
Would it be right to store the serialization as a text type field?
Secondly;
If we do not directly interact with the serialized field other than r/w will it affect the performance of our database at all?
Finally;
Would it be better to store the serialized object in our filesystem instead?
To give a little insight into why we're storing them in the first place, we receive an object from a supplier, a user needs to select several options and we need to send the EXACT object back with the modified (selected) components.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
是的，将其存储为文本字段（类型
TEXT）是正确的，但如果您担心字符编码，您也可以将其存储为二进制（类型BLOB） .不，如果您仅通过作为主键的 ID 进行查找，那么它应该不会对性能产生太大影响。
如果您使用 MySQL 来存储 ID，则最好将序列化对象存储在表中，除非您有大量序列化数据可能会占用大量磁盘空间，但又将其存储在文件系统中也会有同样的问题。
Yes storing it as a text field (type
TEXT) is correct but you can also store it in binary (typeBLOB) if you're worried about character encoding.No, if you are only ever looking up by ID which is a primary key then it shouldn't have too much of a performance effect.
If you're using MySQL to store the ID's, you might as well store the serialized object in the table, unless you have huge amounts of serialized data that could use up lot's of disk space but then storing it in the file system would have the same problem.
是的，我建议你对其进行二进制编码。那么 Blob 可能更合适，即文本 IIRC。只是防止任何类型的编码，只需将其存储为二进制。
不像是任何其他数据。
由于这些大多是较小的数据块，因此我更喜欢数据库，因为它比每个序列化块有一个文件的文件系统可以更好地处理较小的数据块。
Yes, I suggest you take a binary encoding for it. Blob might be better fitting then, which is text IIRC. Just prevent any sort of encoding, just store it binary.
Not more as if it would be any other data.
As those are mostly smaller chunks of data, I would prefer the database as it can handle better smaller chunks of data than a file-system that is having one file per serialized chunk.
这听起来像是一个简单的键->值设置——一个表，两列，第一个主键，没有额外的索引。如果您的对象很大，MySQL 不适合这样做。如果它们很小，你可能就没事。我认为超过 12k 左右就太大了。 InnoDB 页面有 16k，如果每个条目及其开销都占用多个页面，那么您就会开始变得一团糟。
如果您使用适当的文件夹分隔来分解对象，文件系统可以很好地处理此问题。如果一个目录中有一百万个文件，它们通常会出现问题。该临界点因文件系统而异，因此您必须进行一些研究。这意味着一些自定义代码。
MongoDB 非常适合键->值范式，并且比 MySQL 更擅长处理对象存储。
最后，如果我们讨论的记录少于 1,000,000 条且总数少于 10GB 左右，只需将其扔到 MySQL 中即可继续。我认为在重要的时候，无论如何你都会想要进行基准测试。
This sounds like a simple key->value setup -- one table, two columns, primary key on the first and no extra indexes. If your objects are large, MySQL isn't suited to this. If they're small, you're probably just fine. I'd think anything over around 12k is too large. InnoDB pages are 16k and if every entry with its overhead is taking up more than one page, you're starting to develop a mess.
File systems are good at handling this if you break objects up using adequate folder separation. They usually have issues if you have a million files in one directory. Where that tipping point is varies by filesystem, so you'll have to do some research. That means some custom code.
MongoDB is really suited to the key->value paradigm and is better at handling object storage than MySQL.
At the end of it, if we're talking less than 1,000,000 records and less than around 10gb total, just chuck it at MySQL and move on. I think at the point where it matters, you'll want to benchmark anyway.