SQLite 优化——两个数据库与一个数据库
我正在开发一个带有两个 SQLite 数据库的 iPhone 应用程序。我想知道我是否应该只使用一个。
第一个数据库有十几个小表（每个表有四五个 INTEGER 或 TEXT 列）。大多数表只有几十行，但一个表将有几千行“项目”。这些表都是相关的，因此它们之间有很多连接。
第二个数据库仅包含一张表。它有一个 BLOB 列，其中一行与第一个数据库中的一些“项目”（项目的照片）相关。这些 BLOB 仅在程序的一小部分中使用，因此它们很少附加或连接到第一个数据库（无需连接即可轻松获取行）。
第一个数据库文件大小约为一兆字节；包含 BLOB 的数据库将会大得多——大约 50 MB。我分离了 BLOB，因为我希望能够备份较小的数据库，而不必也携带 BLOB。我还认为分离 BLOB 可能会提高较小表的性能，但我不确定。
使用两个数据库（特别是 SQLite 数据库）来分隔 BLOB 与仅使用一个数据库（其中一张表中充满了非常窄的表与 BLOB 表混合在一起）相比，有何优缺点？
I'm developing an iPhone app with two SQLite databases. I'm wondering if I should be using only one.
The first database has a dozen small tables (each having four or five INTEGER or TEXT columns). Most of the tables have only a few dozen rows, but one table will have several thousand rows of "Items." The tables are all related so they have many joins between them.
The second database contains only one table. It has a BLOB column with one row related to some of the "Items" from the first database (photos of the items). These BLOBs are only used in a small part of the program, so they are rarely attached or joined with the first database (rows can be fetched easily without joins).
The first database file size will be about a megabyte; the db with the BLOBs will be much larger -- about 50 megabytes. I separated the BLOBs because I wanted to be able to backup the smaller database without having to carry the BLOBs, too. I also thought separating the BLOBs might improve performance for the smaller tables, but I'm not sure.
What are the pros and cons of having two databases (SQLIte databases in particular ) to separate the BLOBs, vs. having just one database with a table full of very narrow tables mixed in with the table of BLOBs?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
即使数据库大小为 50 MB，这也是一个非常小的数据库大小，并且您不会看到由于数据库本身大小而导致的性能差异。
但是，如果您认为性能是问题所在，请检查您的数据库查询，并确保您没有在不需要该信息的查询中“SELECT”BLOB 行。从数据库返回比您需要的更多的行（可以将其视为从硬盘驱动器读取比您需要的更多的数据），您更有可能看到性能问题。
我认为分离它们没有任何优点，除非您对备份的总大小有非常严格的限制。它只会增加应用程序中奇怪位置的复杂性。
Even at 50 MB, that is a very small database size, and you're not going to see a performance difference due to the size of the database itself.
However, if you think performance is the issue, check your DB queries, and make sure you're not 'SELECT'ing the BLOB rows in queries where you don't need that information. Returning more rows from the database than you need (think of it as reading more data from a hard drive than you need to), is where you're much more likely to see performance issues.
I don't see any pros to separating them, unless you are under a very tight restriction on the total size of the backup. It's just going to add complexity in weird places in your app.