多表MySQL分页查询技巧

Question

有一个页面包含来自不同MySQL表（新闻，文章，视频，音频等）的各种项目，绑定到某个标签（例如“经济”）。

目前，从每个表中获取与标签绑定的100行数据，然后进行分组和排序。

我需要在页面上引入分页，在这种情况下很痛苦，因为需要将所有项目收集在一起，以便按偏移量和限制长度获取块。

我认为我需要将每个表中的项目聚合到一个数据源中，然后对其进行查询（按标签过滤）和排序（按日期）。

我可以用什么来实现这个目的？我考虑使用Sphinx搜索引擎，但我不确定它在这种情况下是否好用——我只需要查询和排序，而不是全文搜索。

- Vasily

Answer 1

Sphinx对于你的情况是一个非常好的解决方案。你可以为所有类型的内容（新闻、文章、视频、音频）定义一个索引，只需添加一个名为“source_type”的字段，用于显示来源表，例如1-新闻，2-音频，3-视频等，并添加所需用于过滤的所有字段。

如果你想搜索所有带有标签“rock”的音频，只需要通过“tag”和“source_type”字段进行筛选。相比MySQL，Sphinx能够更快地完成这项任务，特别是在处理大量数据时。Sphinx将仅返回找到的几行数据（取决于sphinx配置中的max_results）。

同时，Sphinx可以轻松快速地返回所有匹配项的计数。使用Sphinx查询中的LIMIT和OFFSET，你可以进行分页。

通过这种方式，你可以从Sphinx中获取MySQL数据库对象的ID，然后从MySQL中获取所有所需数据。

我在相同的情况下使用了这个场景，它提供了很高的效率。

- Eugene Soldatov

---

据我了解，Eugene Soldatov，Sphinx可以通过一些字段（例如您的示例中的“标签”和“来源类型”）比MySQL更快地进行过滤。如果这些字段有索引，情况是否也是如此？如果您有任何关于这样的比较的信息，请分享。 - Vasily

根据我的经验，这样做更快。特别是当你需要计算所有匹配项并使用多个过滤器进行聚合（用于分页）时，Sphinx表现最佳。不幸的是，我无法提供任何基准测试数据。我在新闻移动应用程序的后端中使用了这种方案。在一个Sphinx实例和两个MySQL实例上，它大约有1k QPS。数据量大约为500k行。 - Eugene Soldatov

Sphinx没有像数据库一样的“索引”概念，它的设置完全不同。实质上，所有数据都是一个大索引 - 结构化以使回答查询更快。请参见http://en.wikipedia.org/wiki/Inverted_index。 - barryhunter