mysql - 如何通过标签搜索数十亿个项目(寻找最佳架构)？

Question

我有一个巨大的项目数据库，有数十亿条条目:

t_item

itemId ...

每个项目都标有多个标签:

t_tag

tagId     tagName

t_item_tag

itemTagId [AI]     itemId     tagId

最终用户的用例场景很简单:

Select all items marked with particular tags and sort them by some attribute. E.g.:

• select all laptops (tag) with Core i7 processor (another tag) and sort them by rating (t_item table);
• select all yellow (tag) bags (tag) made of leather (tag) and sort them by price (t_item table).

目前我正在 MySQL 之上运行该系统，但它开始达到极限。主要关心的是最终排序是使用temporary进行的；使用文件排序有点慢。

我还可以采用哪些其他架构来处理我的用例和卷？

Answer 1

“使用 temp；使用 filesort”不是坏人，架构和索引才是。

这对于标签来说是最佳的:

CREATE TABLE Tags (
    item_id ...,
    tag ... -- either a string or a tag_id, it does not matter much,
    PRIMARY KEY(tag, item_id),  -- for clustering and searching
    INDEX(item_id)  -- for maintenance (eg deleting an item_id)
    -- no FOREIGN KEYs; they slow things down
    -- no AUTO_INCREMENT; it is a total waste here
) ENGINE=InnoDB; -- so PK will be clustered.

因此，即使有数十亿行，这样的表也将非常有效地访问 - 所有“黄色”项目都将“聚集”并且需要很少的磁盘命中。

(您建议了 tag_id 和另一个表格。我认为这是一个关于这是否更好的折腾。)

计算磁盘命中率——它们是大型表的主要性能指标。

对于，选择所有配备 Core i7 处理器(另一个标签)的笔记本电脑(标签)，并按评级对它们进行排序(t_item 表)； --

SELECT i.id
    FROM Items AS i
    JOIN Tags  AS t1  ON t1.item_id = i.id
    JOIN Tags  AS t2  ON t2.item_id = i.id
    WHERE t1.tag = 'laptops'
      AND t2.tag = 'Core i7 processor'
    ORDER BY i.rating DESC
    LIMIT 10;

如果有 2000 台“笔记本电脑”，则可能有 20 个磁盘 block (算作 20 个磁盘命中)。 150“酷睿 i7 处理器”可能会增加 2 次磁盘命中。如果这些导致 70 个“项目”，那么您会再获得 70 次磁盘命中，因为它们很可能是随机定位的。是的，将会有一个临时表(在 RAM 中)和一个文件排序(但没有物理"file")来对 70 个 id 进行排序并交付 10 个。

是的，您必须动态构造此 SELECT。

出于几个原因，我突然停止了(仅获取 i.id)...

Items 中还有什么？一些大的 TEXT 列(description 等)？他们很讨厌；他们使工作变得困惑。

我们的愿望是说 SELECT i.* 而不是 SELECT i.id。让我们看看是否可以妥协。

假设您有INDEX(id, rating)。但是，有充分的理由制作一个仅包含 id 和 rating 的表格 - 您经常更改它，并且 我们需要一个“小”表来查找并按评级对 70 个 ID 进行排序。

因此，我们有两个表:Items - id、描述等，以及Ratings，其中:

CREATE TABLE Ratings (
    item_id -- 1:1 with `Items.id`
    rating ...,
    PRIMARY KEY(id)
) ENGINE=InnoDB;

现在，将我的第一个查询更改为 FROM Ratings(而不是 FROM Items)。现在，我们仍然需要从该表中获取 70 行，但由于它更小，缓存的可能性更大，因此它可能涉及不到 70 次磁盘命中。

然后，我们需要从 Items 中仅最终获取 10 个大件东西。

总计:20+2+70+10 = 102 次磁盘命中，或者在普通旧旋转驱动器上大约 1 秒；在 SSD 上速度要快得多。 (另外希望最大的数字 70 会更低。)

回到文件排序...由于只有“小”列(id、评级)并且只有 70 个“行”需要排序，文件排序将成为内存中结构，几乎不需要时间来执行。这就是为什么我说忽略文件排序。通过安排稍后获取庞大的描述，我避免了通过 filsort 拖拽它，这几乎肯定会强制 tmp 表为 MyISAM，而不是 MEMORY。

既然您提到按评级或价格排序，也许这很好:

CREATE TABLE RatingsPrices (
    item_id ..., -- 1:1 with `Items.id`
    rating ...,
    price ...,
    INDEX(item_id, rating),  -- covering index for the main query
    INDEX(item_id, price),  -- covering index for the main query
) ENGINE=InnoDB;

可能还有更多细节和微妙之处，但我希望这些提示能让您朝着好的方向发展。

记住:如果数据集太大，甚至工作集都无法容纳在 RAM 中，请“计算磁盘命中次数”。