Redis按范围过滤，排序并先返回10 [英] Redis filter by range, sort and return 10 first

问题描述

假设我们有一个带有字段的简单mysql表(用户):

Assume we have a simple mysql table(user) with fields:

id
rating
salary

我想在指定范围(50-100)中获得10位评分和薪水最高的用户，即在mysql中应该是

I want to get 10 users with highest rating and salary with specified range(50-100), i.e in mysql it would be

SELECT id from user WHERE salary>50 and salary<100 ORDER by rating limit 0, 10

这在100K用户表上运行20ms.

This runs for 20ms on 100K users table.

假设我在redis中具有相同的内容: Zlist分级(rating => user_id) Zlist薪水(salary => user_id)

Assume I have same in redis: Zlist rating (rating=>user_id) Zlist salary (salary=>user_id)

我看到的关于redis的所有解决方案包括复制100k薪水Zlist，删除不需要的条目以及与100k评级列表合并，例如

All solutions I saw with redis include copying 100k salary Zlist, removing unneeded entries, and merging with 100k rating list, like

zinterstore 1 search salary
zremrange search -inf 50
zremrange search 100 +inf
zinterstore 2 search rating weights 0 1
zrange search 0 10

这绝对是慢的(为什么要复制100k元素以删除大多数元素?).

which is absolutely slow(why copy 100k elements to remove most of them?).

有什么方法可以与redis至少实现同等效率吗?

Is there any way to implement this at least comparably efficient with redis?

推荐答案

您描述的用例无法在NoSQL解决方案中完美建模.这不是Redis的限制.

The use case you describe cannot be modeled elegantly in NoSQL solutions. It isn't a Redis limitation.

让我解释一下.您正在一个字段上运行范围查询，而在另一个字段上进行排序.这不是NoSQL解决方案擅长的.例如，Google App Engine禁止此类查询.查看 GAE查询限制，然后阅读属性不等式过滤器中的必须在其他排序顺序之前进行排序"

Let me explain that a bit more. You are running range queries on one field, and sorting on another. This isn't something NoSQL solutions are good at. For example, Google App Engine forbids such queries. Take a look at GAE Query Restrictions and read the section "Properties in Inequality Filters Must Be Sorted before Other Sort Orders"

要获取与不等式过滤器匹配的所有结果，查询会扫描 第一个匹配行的索引表，然后返回所有连续的 结果，直到找到不匹配的行.对于连续 代表完整结果集的行，这些行必须按以下顺序排序 不等式过滤器排在其他排序顺序之前.

To get all results that match an inequality filter, a query scans the index table for the first matching row, then returns all consecutive results until it finds a row that doesn't match. For the consecutive rows to represent the complete result set, the rows must be ordered by the inequality filter before other sort orders.

话虽如此，您仍然可以有效地运行查询，但是解决方案并不完美.

Having said that, you can still efficiently run your queries, but the solution isn't going to be elegant.

1. 创建薪资范围-0-5000、5000-10000、10000-15000等
2. 创建类似users_with_salary:10000-15000的集合.此集合将包含工资在给定范围内的用户ID.
3. 类似地，创建诸如`users_with_rating:1-2之类的集合.该集合将包含评分在给定范围内的用户ID
4. 现在，运行以下伪代码

1. Create salary ranges - 0-5000, 5000-10000, 10000-15000 and so on
2. Create sets like users_with_salary:10000-15000. This set will contain user ids who have salary in the given range.
3. Similarly, create sets like `users_with_rating:1-2". This set will contain user ids who have ratings in the given range
4. Now, run the following pseudo code

String userids[];
for(rating = 10; rating > 0; rating--) {
  for(salary = min_salary; salary < max_salary; salary += 5000) {
      String salary_key = "users_with_salary:" + salary + "-" + (salary+5000);
      String rating_key = "users_with_rating:" + rating + "-" + (rating+1);

      userids.append(redis.sinter(salary_key, rating_key));

      if(userids.length > 10) {
         break;
      }
   }
}

使用redis 2.6和lua脚本，您甚至可以在lua服务器上运行它.

With redis 2.6 and lua scripting, you can even run this on the lua server.

最后，如果要对数据运行复杂的查询，最好在关系数据库中对其进行建模.

In conclusion, if you want to run complex queries on your data, it is best to model it in a relational database.

这篇关于Redis按范围过滤，排序并先返回10的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！