代码之家 › 专栏 › 技术社区 › Alexey S. Larionov

用于加快查询速度的分隔大字段的SQL

database-design database sql mysql

Alexey S. Larionov · 技术社区 · 6 年前

假设我有一本桌子书:

BOOK_ID INT(6) PK
--------------------
FILE_EXTENSION VARCHAR(5)
TITLE VARCHAR(60)
LANGUAGE VARCHAR(10)
EDITION INT(2)
PUBLISHMENT_OFFICE_ID INT(4)
PUBLISH_YEAR INT(4)
RATING INT(1)
FILE_UPDOAD_DATE DATE
LINK VARCHAR(150)

此表用于搜索书籍(例如,通过扩展、发布办公室、作者(来自其他表)等)和完全可视化(在页面上打印具有所有这些字段的所有书籍)。

所以有一个问题:例如,如果我做了

SELECT BOOK_ID FROM BOOK WHERE FILE_EXTENSION = 'PDF'

这会导致作为中间结果加载所有大字段(链接、标题和可能计划的BLOB)吗,还是会在WHERE子句转换后立即丢弃任何不必要的字段,而不会出现性能问题?

这个问题引出了解决方案:用相同的pk将其他表中的大字段分开,以减慢可视化速度(因为需要一个连接),但要加快搜索速度?它值吗?

另外,这个特定的数据库并不打算保存大量的数据,所以我的查询(我希望)不会那么慢。但这个问题是关于一般数据库的设计(比如10^8个条目)。

P.P.S.请不要将我链接到数据库规范化(我的完整数据库规范化得很好)

2 回复 | 直到 6 年前

MatBailie 6 年前

列作为其行的一部分存储。行作为页面的一部分存储。如果您需要一行中的一列来读取整行,实际上您需要读取该行所在的整页。可能有数千行,包括所有列。希望该页面还有其他您感兴趣的行,并且不会浪费阅读时间。

这就是为什么柱状数据库在分析中变得如此流行的原因。它们分别存储列。它们仍然将值存储在页面中。因此,您从磁盘上读取了该列的数千行,但在分析中,您可能对所有或大部分这些行感兴趣。这样,您可以拥有数百个列,但只能读取正在查询的列。

MySQL没有Columnstore。所以,你需要一个选择。

首先,将大字段放在一个单独的表中,这是您已经提到过的。

第二,可以使用覆盖索引。

如果你索引 (file_extension, book_id) 查询 SELECT book_id FROM book WHERE file_extension = 'pdf' 只要读索引就可以满足。它从不需要读取表本身。 (索引仍然存储为磁盘上的页,但只存储与索引相关的列,并且可能存储为行指针。比桌子窄得多。)

不过,这有点笨拙,因为覆盖索引需要覆盖您知道感兴趣的列。

在实践中,你的领域足够小,在它真正成为问题之前不需要关注它。储存起来比较明智 BLOB 但是在另一张桌子上。

Rick James 6 年前

“列作为行的一部分存储。”——是和否。所有“小”列都存储在行中。但是 TEXT 和 BLOB 如果“大”,则存储在其他位置。(这假设 ENGINE=InnoDB )

SELECT book_id FROM ... WHERE ext = 'PDF' 会从中受益 INDEX(ext, book_id) . 如果没有这样的查询,查询就必须扫描整个表(100M行?).有了这个索引,它将非常有效。

“在页面上打印所有具有所有这些字段的书籍”--这可能排除了大列吗?那样的话 SELECT book_id 对战 SELECT all-these-fields 成本差不多。在网页上这样做是合理的-- 如果你不想在一页纸上展示成千上万本书。这将成为一个“糟糕的用户界面”问题,而不是一个“低效的查询”问题。

title 和 link 在我上面的讨论中,可能会被归入“小”的标题。但是任何 BLOBs 很可能是“大”。

是的,是的 可能的 做“垂直分区”来分割大项目,但这主要是重复InnoDB已经做的事情。别麻烦了。

我们应该在竞技场上讨论这些事情。我的评论到目前为止只触及表面。为了更深入地挖掘,我们需要看到真实的模式和一些重要的查询。我想有些问题会比较慢。对于100M行,改进一个查询有时会损害另一个查询。