MySQL - 如何有效地对多列进行分页排序

Question

假设我有这四个表:

BRANCH (BRANCH_ID, CITY_ID, OWNER_ID, SPECIALTY_ID, INAUGURATION_DATE)
CITY (CITY_ID, NAME)
OWNER (ONWER_ID, NAME)
SPECIALTY (SPECIALTY_ID, NAME)

我有一个 PrimeFaces 数据表，我将在其中使用 50 (LIMIT X, 50) 分页显示所有分支。今天 BRANCH 有大约 10000 行。我将加入 BRANCH 和其他 3 个表，因为我想显示它们的名字。

我想使用以下默认排序获取结果:

按 INAUGURATION_DATE ASC、C.NAME ASC、O.NAME ASC、S.NAME ASC 排序

现在，用户可以选择单击我的数据表中任何这些列的标题，我将再次查询数据库，将他要求的排序作为优先排序。例如，如果他选择先按专业名称降序排序，我会这样做:

按 S.NAME DESC、INA​​UGURATION_DATE ASC、C.NAME ASC、O.NAME ASC 排序

现在我的问题是:如何始终使用 4 列高效通过这种动态排序查询数据库？许多用户可以同时在我的站点中查看此数据表(例如 1000 个用户)，因此在 SQL 中使用 ORDER BY 非常慢。我正在用 Java 进行排序，但我无法正确进行分页。我怎样才能在 SQL 中有效地做到这一点？为这些列创建索引就足够了吗？

谢谢

Answer 1

10000 行很小，所以 mysql 应该可以很快处理。假设您在 City、Owner 和 Specialty 类上有适当的索引(如果您声明主键就会出现这种情况)，此查询应该会快速返回。另外请务必在查询中使用 LIMIT 50。但是，如果行数变大(例如一百万甚至更多。您应该只是计时查询以找出它开始变慢的位置)，那么您在 City_ID、Owner_ID、Speciality_id 或 inauguration_date 上的单独索引将无济于事。要利用排序，假设您只是在进行连接并且没有 where 子句，那么索引将需要按照您希望排序的顺序位于所有列上。因此，您将需要相当多的索引来涵盖所有情况。如果性能成为问题，您可能需要考虑应用程序是否需要所有这些选项。也许您可以让用户更改任何一列的类型。在这种情况下，个别索引会有所帮助。此外，当行数变大时，性能瓶颈可能不是排序而是您如何执行分页。我喜欢 https://stackoverflow.com/a/19609938/4350148 中的方法.

最后一点。 Mysql 默认缓存查询。因此，如果表没有发生变化，那么查询应该返回，甚至不必进行排序。