Redis range Query application database Database How to learn Redis Redis range queries

Redis range Query app.

Demand

Find the corresponding city according to IP

The original solution

Oracle Table (Ip_country):

Query the city of IP:

1. Convert IP a.b.c.d in such a format to a number, for example to convert 210.21.224.34 to 3524648994

2. Select city from Ip_country where Ipstartdigital <= 3524648994 and 3524648994 <=ipenddigital

Redis Solutions

Let's start by simplifying the table above:

ID City min Max
1 P1 0 100
2 P2 101 200
3 P3 201 300
4 P4 301 400

(Note: There can be no overlap between range of Min/max)

The main idea is to use Hmset to store each row of the table and establish an ID for each row (as key)

Then the ip_end in order from small to large storage in sorted set, score corresponding to the ID of the row

Query, use the range query feature of Redis sorted set, query the ID from sorted set, and then go to Hmget by ID.

Experiment

Store each row of the table
127.0.0.1:6379> hmset {ip}:1 city P1 min 0 max 100
Ok
127.0.0.1:6379> hmset {ip}:2 city P2 min 101 Max 200
Ok
127.0.0.1:6379> hmset {ip}:3 city P3 min 201 Max 300
Ok
127.0.0.1:6379> hmset {ip}:4 city P4 min 301 Max 400
Ok
Set up sorted set (Member-score)
127.0.0.1:6379> Zadd {IP}:END.ASC 100 1 200 2 300 3 400 4
(integer) 4
127.0.0.1:6379> Zrange {IP}:END.ASC 0-1
1) "1"
2) "2"
3) "3"
4) "4"
Query the corresponding interval (score)
127.0.0.1:6379> Zrangebyscore {ip}:end.asc +inf LIMIT 0 1
1) "1"
127.0.0.1:6379> Zrangebyscore {IP}:END.ASC 123 +inf LIMIT 0 1
1) "2"
127.0.0.1:6379> Zrangebyscore {ip}:end.asc +inf LIMIT 0 1
1) "1"
Explain:
Zrangebyscore {IP}:END.ASC +inf LIMIT 0 1
Represents the first value of a lookup greater than or equal to 90. (+inf in Redis means positive infinity)
The statement returns the value score=1, which corresponds to the ID in Hmset, so you can find the city by Hmget:
Find a city
127.0.0.1:6379> Hmget {ip}:1 City
1) "P1"

Note When you design a Redis key, a uniform prefix is used: {IP}

This is to make these IP-related data all fall into the same Redis server (our Redis is deployed as a cluster and a consistent hash is taken), and it's easier to move data back in the future

Real Exercise

The resulting text derived from the database is like this (select a few behavior examples):

Ipcountry_tab_orderby_end_asc.txt:

"Ipstart" "Ipstartdigital" "Ipend" "Ipenddigital" "Country" "City" "TYPE" "REGISTRY" "ADRESS" "Province"
"1.184.0.0" 28835840 "1.184.127.255" 28868607 "China" "Guangzhou" "" "" "" "" Guangdong Province "
"1.184.128.0" 28868608 "1.184.255.255" 28901375 "China" "Guangzhou" "" "" "" "" Guangdong Province "
"1.185.0.0" 28901376 "1.185.95.255" 28925951 "China" "Nanning" "" "" "" "Guangxi"

1. Generate batch Hmset commands and Zadd commands

Write a small program to generate:

Import Java.io.File;
Import java.io.IOException;
Import java.io.UnsupportedEncodingException;
Import java.util.ArrayList;
Import java.util.List;
Import org.apache.commons.io.FileUtils;
Import org.apache.commons.lang3.StringUtils;
public class Ipcountryredisimport {
public static void Main (string[] args) throws IOException {
File file = NE W File ("E:/doc/ipcountry_tab_orderby_end_asc.txt");
File Hmsetfile = new file ("E:/doc/ip_country_redis_cmd.txt");
File Zaddfile = new file ("E:/doc/ip_country_redis_zadd.txt");
List lines = fileutils.readlines (file);
int i = 0;
StringBuilder rows = new StringBuilder ();
StringBuilder ends = new StringBuilder ();
for (String str:lines) {
if (Stringutils.isempty (str)) {
continue;
}
//skip First line
if (i = = 0) {
i++;
continue;
}
i++;
//"Ipstart" "Ipstartdigital" "Ipend" "Ipenddigital" "Country" "City" "TYPE" "REGISTRY" "ADRESS" "Province"

Redis range Query application database Database How to learn Redis Redis range queries