假设有一个表。该表用于跟踪书籍的章节。该表具有以下结构:
CREATE TABLE Chapters(
id INT PRIMARY KEY NOT NULL,
storyId INT references Books(id)
title TEXT NOT NULL,
body TEXT NOT NULL
);
现在,在此表中,正文列将包含大量文本。想象一下,平均小说中的平均章节有多大,你会得到一个粗略的想法。它将在其中保存潜在的数百千字节(甚至可能一兆字节)的字符串数据。
现在,当然在某些情况下,您不需要章节的"正文",但需要其他东西,例如它的标题。例如,如果我试图为一本书中的章节构建一个"书籍索引",我会执行如下查询:
SELECT title FROM Chapters WHERE storyId = 1
当然,查询会给我返回故事章节的标题。但是,查询是否会使用大量内存,因为它必须将生成的行(列和所有)加载到内存中,并且表中的每一行都有一个"重"列("正文"列)?
我问这个是因为(据我了解 - 如果我错了,请纠正我)这就是它在文档存储数据库中的工作方式。MongoDB中的每一行(或"文档")都必须首先加载到内存中,即使您只想从中返回一个字段。因此,如果我要在MongoDB中执行类似的查询,它将通过将大型"body"字段加载到内存中来"浪费"内存,即使我唯一想返回的字段是"title"字段。
对于大多数 SQL 实现来说,这些问题是否相同?我特别要求PostgreSQL,但我也有兴趣知道MySQL是否有所不同。
如果您没有选择该列,那么它不应该占用资源。根据您使用的特定类型的 SQL 的工作方式,额外的空间可能会导致页面变大,因此服务器必须遍历更多的磁盘空间才能找到所需的行,但在您的示例中,您是通过 ID(大概是索引)选择的,所以即使这样也不会发生。即使在确实发生的情况下,该额外列也不会放入内存中,只是在服务器搜索所需行时跳过它。
对于SQL的某些变体,像TEXT
这样的东西甚至不与数据行的其余部分一起存储 - 使用指向磁盘上实际保存它的点的指针。在这些情况下,您甚至不会遇到页面较大的问题。
当然,所有这些都将特定于您正在使用的SQL变体的内部结构。我不是MySQL或PostgreSQL的专家,所以如果我的任何解释不适用于SQL的特定实现,任何人都可以纠正我。
即使您没有选择该列,它也会占用资源。如果MyISAM表将使用更多的VFS,而对于Innodb,将使用更多的缓冲池。记录(无论是从索引还是表中检索)都出于缓存目的而完整维护(但在MyISAM的情况下,VFS在提供另一层抽象的页面上运行,但是整个记录是一次性读取的,但可能会分期从缓存中逐出)。
即使立即丢弃它,仍然会对性能产生影响 - 因为这些是可变长度记录,DBMS 必须将流上的句柄推进到下一条记录,除了它使用索引取消引用表的情况外,它不能直接跳转到正确的位置(即使可以, 寻求是昂贵的)。即,对于全表扫描或索引扫描,记录的大小会影响性能。将MyISAM表转换为使用固定长度记录通常会对性能产生显着影响(但不要使用BLOB和CLOB的表尝试此操作)。
对于包含每个记录大小可能发生显著变化的记录的表,另一个问题是记录迁移。记录按定义的顺序累积,但在更新时,特定记录可能会变得太大,无法容纳它以前占用的洞。然后将记录迁移到表的末尾。这也会对性能产生重大影响,这也是固定大小记录速度更快的另一个原因。
在PostgreSQL中,如果大字段大于物理页面大小(默认为8kB),则不会降低性能。行必须适合单个物理页面,因此如果您的章节大小不错,那么您将很容易超过该限制。如此大的行可以被压缩和/或烘烤(是的,就像面包一样)。
TOAST 以行外方式存储大型字段,而其他字段则以常规方式存储。这样可以避免在仅提取其他字段时对 I/O 等进行任何开销。优秀的PG文档更详细地说明了它:
与更多相比,该方案具有许多优点 简单的方法,例如允许行值跨页。 假设查询通常通过比较来限定 相对较小的键值,执行者的大部分工作将是 使用主行条目完成。TOASTed属性的大价值 只会在结果出现时被拉出(如果完全选择的话) set 被发送到客户端。因此,主表要小得多, 与共享缓冲区缓存相比,共享缓冲区缓存中容纳的行更多 没有任何离线存储。排序集也会缩小,排序会 更多时候完全在内存中完成。
答案是
- 是的,它确实会影响性能。
但
- 在大多数情况下,只要
- 数据库运行正常,性能就不会受到足够的影响,以至于您不必担心。
我对健康的定义是a)对表进行了正确的索引,对数据库进行了正确的碎片整理,并对数据库进行了正确的设计。
从文档中:
对于以
ROW_FORMAT=DYNAMIC
或ROW_FORMAT=COMPRESSED
创建的表,BLOB
、TEXT
或VARCHAR
列的值可以完全存储在页外,这取决于它们的长度和整行的长度。对于存储在页外的列,聚集索引记录仅包含指向溢出页的 20 字节指针,每列一个指针。是否有任何列存储在页外取决于页面大小和行的总大小。当行太长而无法完全容纳在聚集索引的页面中时,InnoDB 会选择最长的列进行页外存储,直到该行适合聚集索引页。如上所述,如果一行本身不适合压缩页面,则会发生错误。
这意味着您的大多数TEXT
字段将离开页面,并且不会严重影响不使用它们的查询。
当您执行如下查询时:
SELECT title
FROM Chapters
WHERE storyId = 1
在正确编制索引的数据库上,将发生以下情况:
引擎在二级索引中查找所有具有
storyId = 1
的条目storyId
。索引是索引键和主键上的 B 树,在您的情况下是storyId
和id
。您可以将其视为表的子集,该子集仅由两个字段组成:storyId, Id
,始终对它们进行排序,并在更新原始表时更新。这允许快速定位任何给定storyId
的值。引擎获取在上一步中找到
id
的所有值,并为每个值查找表中的title
。InnoDB
中的表也是 B 树,也就是说它们按主键id
排序。按id
找到每个给定记录也很快,因为当您更新表时,引擎会维护顺序。如果
title
存储在页外,引擎将获取第一个溢出页的地址并遍历单链表,直到获取所有数据。如果title
在页面上,则引擎会立即返回它。
现在,还记得我在第 2 步中说的"快速"吗?
它到底有多快,取决于页面的大小。
B-Tree 的设计方式是,它的深度,即您可以通过id
找到页面的跃点数,取决于它存储的页数(不是记录)和主键的长度。
这意味着如果您的记录很大(即页面上存储了长列),PK 每页获得的记录更少,因此保存相同数量的记录的页面更多,并且 PK 上的索引查找效率降低。
但是,如果title
存储在页面上,则无需执行一次(或多次)额外的页面查找即可获取其值,从而缓解了这种情况。