大MySQL表 [英] Big MySQL Tables

问题描述

我正在处理一个需要缓存分页的搜索"结果的问题:对非常大的数据集进行分页

I'm working on a problem that requires caching paginated "search" results: Paginating very large datasets

搜索如下:给定一个item_id，我找到匹配的item_id及其排名.

The search works as follows: given an item_id, I find the matching item_ids and their rank.

我愿意不向用户显示过去(例如500)的任何结果.500之后，我将假设他们不会找到他们想要的东西...结果已排序还是按匹配顺序排序.因此，我想缓存这500个结果，因此只需要执行一次繁重的查询，用户仍然可以分页结果(最多500个).

I'm willing to concede not showing my users any results past, say, 500. After 500, I'm going to assume they're not going to find what they're looking for... the results are sorted in order of match anyway. So I want to cache these 500 results so I only have to do the heavy lifting of the query once, and users can still page the results (up to 500).

现在，假设我使用一个中间MySQL表作为缓存...也就是说，我将每个项目的前500个结果存储在"matches"表中，例如:"item_id(INTEGER)，matched_item_id(INTEGER) ，match_rank(REAL)".现在搜索变得非常快:

Now, suppose I use an intermediate MySQL table as my cache... that is, I store the top 500 results for each item in a "matches" table, like so: "item_id (INTEGER), matched_item_id (INTEGER), match_rank (REAL)". The search now becomes the extremely fast:

SELECT item.* FROM item, matches 
    WHERE matches.item_id=<item in question>
    AND item.id=matches.matched_item_id 
    ORDER BY match_rank DESC
    LIMIT x,y

如果结果要早于24小时，那么客户就可以将索引项及其匹配项重新索引到此表中，这没有问题.问题是，为N个项目存储500个结果(其中N为〜100,000至1,000,000)，该表变得很大... 50,000,000-500,000,000行.

I'd have no problem reindexing items and their matches into this table as they are requested by clients if the results are older than, say, 24 hours. Problem is, storing 500 results for N items (where N is ~100,000 to 1,000,000) this table becomes rather large... 50,000,000 - 500,000,000 rows.

MySQL可以处理吗?我应该注意什么?

Can MySQL handle this? What should I look out for?

推荐答案

MySQL可以处理这么多行，并且当您开始碰壁时，有几种技术可以扩展. 分区和

MySQL can handle this many rows, and there are several techniques to scale when you are starting to hit the wall. Partioning and replication are the main solutions for this scenario.

您还可以在我之前问过的问题中查看有关MySQL的其他扩展技术.

在stackoverflow上.

You can also check additional scaling techniques for MySQL in a question I previously asked here on stackoverflow.

这篇关于大MySQL表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！