PostgreSQL高级扩展之IP4R__虽然PostgreSQL已有内置数据类型 'inet' 及 'cidr'，但本模块的作者发现一些内置数据类型没有解决的问题。_

PG中文社区 /

mdi-home

首页

社区新闻

中文文档

加入ACE

mdi-home

首页

mdi-chat-processing

社区新闻

mdi-book-open-variant

中文文档

mdi-account-multiple-check

加入ACE

mdi-file-multiple-outline

相关资料

mdi-blank

mdi-exit-to-app

退出账号

PostgreSQL高级扩展之IP4R

原作者：李海龙　　创作时间：2019-01-09 21:32:05+08　　

redraiment 发布于2019-01-09 21:32:05　　

评论: 1　　

顶: 595　

踩: 567　

## 作者简介

**李海龙**，去哪儿网PostgreSQL DBA，PostgreSQL中国社区核心成员，热爱数据库技术, 乐于在公司内外分享使用中的各种经验。

## 一、原理

虽然PostgreSQL已有内置数据类型 `inet` 及 `cidr`， 但本模块的作者发现一些内置数据类型没有解决的问题。

首先且最为重要的是，内置数据类型没有很好的支持(column >>= parameter)这种格式的索引查找，即:当你在一个已有的IP地址范围的表中查找包含指定IP地址的行时。此时就需要配合rtree/gist 索引来搞定， 同时也需要一个IP地址范围的表述(尤其是没有精确落入CIDR块边界的那些范围)。

(虽然PostgreSQL 9.4+ 支持inet数据类型的gist索引，但性能与此模块没法比)

其次， 内置的 inet/cidr有点语义冗余， inet包含2个独立的概念(网络块及其某个指定IP)。 而且，为了同时支持IPv4与IPv6,内置数据类型设计为变长类型，为此付出了不小的代价。 作者的应用case主要是处理大量的单IPv4地址(这也应该是最为常见的case)， 所以想设计一个更轻量级的表述。

因此, IP4R 支持如下6中不同的数据类型:

| 数据类型 | 描述 |
| ---- | ---- |
| ip4 | 单IPv4地址 |
| ip4r | 任意范围的IPv4地址 |
| ip6 | 单IPv6地址 |
| ip6r | 任意范围的IPv6地址 |
| ipaddress | 单IPv4/IPv6地址 |
| iprange | 任意范围的IPv4/IPv6地址 |

## 二、安装

ip4r 可以通过PGXS机制来安装(当前默认)。

```
-- 安装至PostgreSQL
sudo -s
wget https://github.com/RhodiumToad/ip4r/archive/2.4.tar.gz
tar -zxvf 2.4.tar.gz
cd ip4r-2.4
env USE_PGXS=1 make
env USE_PGXS=1 make install

-- DB中创建EXTENSION

lhl@localhost:~$ psql mydb
psql (11.1)
Type “help” for help.
mydb=# create extension ip4r ;
CREATE EXTENSION

简要用法示例:

```
mydb=# CREATE TABLE ipranges (range ip4r primary key, description text not null);
CREATE TABLE
mydb=# CREATE INDEX ipranges_range_idx ON ipranges USING gist (range);
CREATE INDEX
mydb=# INSERT INTO ipranges VALUES ('10.0.0.0/8','rfc1918 block 1');
INSERT 0 1
mydb=# INSERT INTO ipranges VALUES ('172.16.0.0/12','rfc1918 block 2');
INSERT 0 1
mydb=# INSERT INTO ipranges VALUES ('192.168.0.0/16','rfc1918 block 3');
INSERT 0 1
mydb=# INSERT INTO ipranges VALUES ('0.0.0.0/1','classical class A space');
INSERT 0 1
mydb=# INSERT INTO ipranges VALUES ('10.0.1.10-10.0.1.20','my internal network');
INSERT 0 1
mydb=# INSERT INTO ipranges VALUES ('127.0.0.1','localhost');
INSERT 0 1
mydb=# 
mydb=# CREATE TABLE access_log (id serial primary key, ip ip4 not null);
CREATE TABLE
mydb=# CREATE INDEX access_log_ip_idx ON access_log (ip);
CREATE INDEX
mydb=# INSERT INTO access_log(ip) VALUES ('10.0.1.15');
INSERT 0 1
mydb=# INSERT INTO access_log(ip) VALUES ('24.1.2.3');
INSERT 0 1
mydb=# INSERT INTO access_log(ip) VALUES ('192.168.10.20');
INSERT 0 1
mydb=# INSERT INTO access_log(ip) VALUES ('127.0.0.1');
INSERT 0 1
```

-- 查找来自10.0.0.0/8的所有访问

```
mydb=# SELECT * FROM access_log WHERE ip BETWEEN '10.0.0.0' AND '10.255.255.255';
 id |    ip     
----+-----------
  1 | 10.0.1.15
(1 row)
```

-- 查找access log中所有条目的相应说明， 如果该条目匹配到重叠的范围，则返回多行

```
mydb=# SELECT id,ip,range,description FROM access_log, ipranges WHERE ip <<= range;
 id |      ip       |        range        |       description       
----+---------------+---------------------+-------------------------
  1 | 10.0.1.15     | 10.0.0.0/8          | rfc1918 block 1
  1 | 10.0.1.15     | 0.0.0.0/1           | classical class A space
  1 | 10.0.1.15     | 10.0.1.10-10.0.1.20 | my internal network
  2 | 24.1.2.3      | 0.0.0.0/1           | classical class A space
  3 | 192.168.10.20 | 192.168.0.0/16      | rfc1918 block 3
  4 | 127.0.0.1     | 0.0.0.0/1           | classical class A space
  4 | 127.0.0.1     | 127.0.0.1           | localhost
(7 rows)
```

-- 查找access log中所有IP的最确切的描述

## 三、用法

### ip4, ip6, ipaddress类型

* “ip4”仅接受十进制的合法输入格式: 'nnn.nnn.nnn.nnn'(没有十六机制，八进制等)。 ipv4的值为单IP地址，且以无符号32位整数存储。
* “ip6”接受标准的十六进制输入格式， 例如 '2001:1234:aa55::2323'。混合格式的(最后两组可以接受类似IPv4的点分十进制格式)输入也是接受的。

ip6的值为单IP地址，且方便起见以2组64位存储。输出格式遵循RFC 5952规范(包括用于v4映射地址的混合格式的输出)。

* “ipaddress” 接受任何合法的ip4或ip6的输入。ipaddress的值是单个IPv4/v6地址。 v4和v6的地址被视为不相交--规定所有的v4地址都低于v6的地址， 且'1.2.3.4' 与 '::ffff:1.2.3.4'不等。

下文中”ipX” 用来表述以上三种类型中的任意一种。

支持如下类型转换:

| 原数据类型 | 目标数据类型 | 格式 |
| ---- | ---- | ---- |
| ipX | text | text(ipX)  或  ipX::text  (显式) |
| text | ipX | ipX(text)  或  text::ipX  (显式) |
| ipX | cidr | cidr(ipX)  或  ipX::cidr  (赋值) |
| inet | ipX | ipX(inet)  或  inet::ipX  (赋值) |
| ipX | numeric | to\_numeric(ipX) or  ipX::numeric (显式) |
| numeric | ipX | ipX(numeric)  或  bigint::ipX  (显式) |
| ip4 | bigint | to\_bigint(ip4)  或  ip4::bigint  (显式) |
| bigint | ip4 | ip4(bigint)  或  bigint::ip4  (显式) |
| ip4 | float8 | to\_double(ip4)  或  ip4::float8  (显式) |
| float8 | ip4 | ip4(float8)  或  float8::ip4  (显式) |
| ipX | varbit | to\_bit(ipX)  或  ipX::varbit  (显式) |
| bit(32) | ip4 | ip4(bit)  或  bit::ip4     (显式) |
| bit(128) | ip6 | ip6(bit)  或  bit::ip6     (显式) |
| varbit | ipX | ipX(varbit)  或  varbit::ipX  (显式) |
| ipX | bytea | to\_bytea(ipX)  或  ipX::bytea   (显式) |
| bytea | ipX | ipX(bytea)  或  bytea::ipX   (显式) |
| ipX | ipXr | ipXr(ipX)  或  ipX::ipXr  (隐式) |
| ip4 | ipaddress | ipaddress(ip4)  或  ip4::ipaddress (隐式) |
| ip6 | ipaddress | ipaddress(ip6)  或  ip6::ipaddress (隐式) |
| ipaddress | ip4 | ip4(ipaddress)  或  ipaddress::ip4 (赋值) |
| ipaddress | ip6 | ip6(ipaddress)  或  ipaddress::ip6 (赋值) |

来自bigint及float8的转换仅适用于ip4，且接受的数值范围精确为：0 .. 2^32-1(与其对应IP的显式范围:0.0.0.0 - 255.255.255.255)。其他数据库为了性能经常将IP以数据格式存储，此时这种转换就派上用场了。

'numeric'与ipX 2种类型可显式互转。

转换为cidr会产生/32(对于v4)2或/128(对于v6)。来自inet类型的转换会忽略任何长度的前缀且只处理指定IP。

ipX的值可隐式的转换为相应的范围类型(ip4 -> ip4r, ip6 -> ip6r)，或iprange类型，生成一个仅包含单IP地址的范围。

IpX支持以下通常含义的操作符：`=`、`<>`、`<`、`>`、`<=`、`>=`，且情理之中的支持ORDER BY及btree 索引。但查询规划器无法理解如何转换如下query

```
WHERE ipcolumn <<= value
```

至btree的范围扫描(内置的inet类型使用1个函数来做此种转换， 但插件无法使用这个函数)。

可以使用如下替代解法：

```
WHERE ipcolumn BETWEEN lower(value) AND upper(value)
```

这样就可以使用btree范围扫描了。

ipX 支持如下额外操作符和函数:

```
family(ipX) returns integer 
| 返回4或6, 具体取决于地址族

ip4_netmask(integer) returns ip4 
| 返回ip4的值,其表示一个指定前缀长度的网络掩码

ip6_netmask(integer) returns ip6
| 返回ip6的值,其表示一个指定前缀长度的网络掩码
 
ipX_net_lower(ipX, integer) returns ipX
| 返回指定前缀长度的CIDR地址块中最低端地址, 包含指定的IP 
| 等价于: network(set_masklen(cidr(ipX),integer))

ipX_net_upper(ipX, integer) returns ipX
| 返回指定前缀长度的CIDR地址块中最高端地址, 包含指定的IP 
| 等价于: broadcast(set_masklen(cidr(ipX),integer))
```

| 操作符 | 描述 |
| ---- | ---- |
| ipX + integer | 将给定的IP加int |
| ipX - integer | 将给定的IP减int |
| ipX + bigint | 将给定的IP加bigint |
| ipX - bigint | 将给定的IP减bigint |
| ipX + numeric | 将给定的IP加numeric |
| ipX - numeric | 将给定的IP减numeric |
| ipX - ipX | 返回两个IP之间的差异(用bigint或numeric来表示) |
| ipX & ipX | 按位做与运算 |
| ipX | ipX | 按位做或运算 |
| ipX # ipX | 按位做异或运算 |
| ~ ipX  | 按位取反 |

ip4的值算术上不会低于0.0.0.0 或高于255.255.255.255，试图超过这个限制都会引发报错。

更为复杂的IP地址的运算， 需要首先将其转换为numeric型； 上述操作符意在覆盖常用的无需转换的实例。

### ip4r, ip6r, iprange类型

* “ip4r”值用来表示单个或多个IPv4地址的单个范围， 例如:'192.0.2.100-192.0.2.200'。  可以是任意范围， 且允许以CIDR地址块的格式输入， 例如：‘192.0.2.0/24’等价于 ‘192.0.2.0-192.0.2.255’。形如‘192.0.2.25’这种单一的值， 表示仅包含单一值的范围。
* “ip6r” 值用来表示单个或多个IPv6地址的单个范围，例如:'2001::1234-2001::2000:0000'。 可以是任意范围， 且允许以CIDR地址块的格式输入， 例如:'2001::/112'等价于'2001::-2001::ffff'。形如'2001::1234'这种单一的值， 表示仅包含单一值的范围。 遵循RFC 5952中指定的输出格式。
* “iprange”值用来表示IPv4或IPv6的范围，以及包含所有IPv4和IPv6空间的特殊值'-'。不支持混合地址族。

对于上述类型, 如果表示的是一个CIDR范围则以CIDR的格式展示， 否则以一个范围格式展示。

目前, 缩略的CIDR格式的IPv4不作为合法的输入，即必须提供所有的字节。 对于IPv6， words仅在符合RFC 5952中的0压缩规则时才可省略。

下文中“ipXr” 用来表述以上三种类型中的任意一种。

可以给函数ipXr(ipX,ipX)2个指定IP值来显式构造ipXr。范围的两端可以无序。

可以给操作符 / (见下文) 指定IP及前缀长度来构造ipXr。为了向后兼容， 操作符 / 仍然接受函数名ipXr\_net\_prefix 及 ipXr\_net\_mask。

ipXr 支持如下类型转换:

| 原类型 | 目标类型 | 格式 |
| ---- | ---- | ---- |
| ipX | ipXr | ipXr(ipX)  或  ipX::ipXr  (隐式) |
| ipXr | text  | text(ipXr) 或  ipXr::text (显式) |
| text | ipXr | ipXr(text) 或  text::ipXr (显式) |
| ipXr | cidr | cidr(ipXr) 或  ipXr::cidr (显式) |
| cidr | ipXr | ipXr(cidr) 或  cidr::ipXr (赋值) |
| ipXr | varbit | to\_bit(ipXr) 或 ipXr::varbit  (显式) |
| varbit | ip4r  | ip4r(varbit) 或 varbit::ip4r  (显式) |
| varbit | ip6r | ip6r(varbit) 或 varbit::ip6r  (显式) |

如果ipXr的值不是合法的CIDR范围，则cidr(ipXr)会返回NULL。

此外，ip4r、ip6r 与 iprange 之间的转换适用于所有的合法组合。

```
ipXr 支持如下函数:
  family(ipXr) returns integer
  |  返回4或6, 具体取决于地址族, 如果是'-'::iprange,则返回NULL

is_cidr(ipXr) returns boolean
  |  如果此ipXr是一个合法的CIDR地址块,则返回TRUE

lower(ipXr) returns ipX
  |  返回指定ipXr范围中最低端地址

upper(ipXr) returns ipX
  |  返回指定ipXr范围中最高端地址

cidr_split(ipXr) returns setof ipXr
  |  将ipXr的范围拆分成独立的CIDR地址块,每块作为独立的行

例如:
mydb=# select cidr_split('192.168.0.0-192.168.0.254'::ip4r);
    cidr_split    
------------------
 192.168.0.0/25
 192.168.0.128/26
 192.168.0.192/27
 192.168.0.224/28
 192.168.0.240/29
 192.168.0.248/30
 192.168.0.252/31
 192.168.0.254
(8 rows)
```

ipXr支持如下操作符:

| 操作符 | 描述 |
| ---- | ---- |
| a = b | 等于 |
| a <> b | 不等于 |
| a < b | 注释 [1] |
| a <= b | 注释 [1] |
| a > b | 注释 [1] |
| a >= b | 注释 [1] |
| a >>= b | a包含b或等于b |
| a >> b | a严格包含b |
| a <<= b | a包含于b或等于b |
| a && b | a和b的交集 |
| @ a | a的近似大小 (返回 double类型) |
| @@ a | a的确切大小 (返回 numeric类型 |
| a / n | 使用地址 a 长度 n 构造CIDR范围 |
| a / b | 使用地址 a 网络掩码 b构造CIDR范围 |

**[1]**: 操作符 <, <=, >, >= 为btree 索引, DISTINCT 及 ORDER BY实现一个排序；这个排序对应用来讲没有意义。这个排序使用的是(lower,upper)的字典顺序。

为了测试ipXr是否包含某个指定IP， 可以使用操作符>>= 即:ipXr >>= ipX(甚是方便!)。 这个case中ipX会隐式转换为ipXr。

### ipXr 索引

ipXr 值可以使用多种索引类型。

ipXr上的传统的btree索引可以用于唯一/主键约束,排序以及等值查找(即: WHERE column = value)。 Btree索引以通用的方式创建且是默认索引类型。

然而， ipXr的强大实用性通过gist索引支持如下类型的查找体现出来:

```
WHERE column >>= value      (或 >>)
WHERE column <<= value      (或 <<)
WHERE column && value
```

这些查找需要GiST索引。 可用如下方式创建:

```
CREATE INDEX indexname ON tablename USING gist (column);
```

也可以在已有的'cidr'类型的列上创建函数型的ip4r索引:

```
CREATE INDEX indexname ON tablename USING gist (iprange(cidrcolumn));
```

(如果该列被约束为仅包含指定地址族的值，也可以使用ip4r(column) 或 ip6r(column) )

接下来上述索引就可以用于如下格式的query:

```
WHERE iprange(cidrcolumn) >>= value    (或 >>, <<=, && 等)
```

这种方式的优势在于ip4r类型可以删除后重建而不丢数据。 这对于设计时没有考虑用到ip4r的已有表上的query提速极为有用。

另外一个习惯性用法: 应用程序创建2个整数列来表示IP地址范围, 且以如下格式来进行范围:

```
WHERE value BETWEEN column1 and column2
```

这种用法试图使用到btree索引，但是大多数情况下效率很低。 此时可以使用ip4r的函数型索引做如下转换:

```
CREATE INDEX indexname ON tablename
   USING gist (ip4r(column1::ip4,column2::ip4));
```

随后这样进行查找:

```
WHERE ip4r(column1::ip4,column2::ip4) >>= value
```

这种方式通常不适用于IPv6。

一个常见的需求是获得一个存储范围或CIDR前缀的表中指定IP的最长前缀(最具体)。 通常使用ORDER BY @ column就可以完美搞定此需求。例如:

```sql
SELECT * FROM tablename
 WHERE column >>= value
 ORDER BY @ column
 LIMIT 1
```

@ column (近似大小)的用法足够应付IPv4范围或始终是CIDR前缀的值。 如果出现的是任意IPv6范围， 微小差异的范围对比时可能会得到相等的对比结果； 这种情况下可使用ORDER BY @@ column。

当查找多个IP时，可用如下格式进行查找:

```
SELECT DISTINCT ON (ips.ip) ips.ip, ranges.range
  FROM ips, ranges
 WHERE ranges.range >>= ips.ip
 ORDER BY ips.ip, @ ranges.range
```

## 四、应用实践

### 实例1：判断2个IP是否在指定的同一网段

假设要判断如下2个网卡的IP是否在23位掩码的同一网段内

* 192.168.233.16/23
* 192.168.232.51/23

```
--封装一个UDF函数
mydb=# CREATE OR REPLACE FUNCTION public.is_same_network(ip1 ip4, ip2 ip4, netmask integer)
 RETURNS boolean
 LANGUAGE plpgsql
 STRICT
AS $function$ 
declare
   sql_string text; 
    is_same_network boolean;
begin
    if netmask > 32 or netmask < 0 then 
        raise exception 'The mask must be between 0 and 32';
    end if;

sql_string = 'select (~ ('''|| ip1 || '''::ip4 # ''' || ip2 || '''::ip4))::bigint::bit(32)::bit(' || netmask || ')::text  ~ ''^1+$'' as is_same_network;';
    execute sql_string into is_same_network;              
return is_same_network;

exception 
    when others then 
        raise NOTICE '%', SQLERRM;                        
        return false;                                     
end;                                                      
$function$
;
CREATE FUNCTION

mydb=# select is_same_network('192.168.233.16','192.168.232.51',23);
 is_same_network 
-----------------
 t
(1 row)
```

### 实例2：使用bigint与ip4r查询效率对比

使用来自https://www.ipip.net/ 的真实数据进行测试:

postgres=# select count(1) from ipip_data;
 count  
--------
 370885
(1 row)

--随机选取一个IP范围
postgres=# select * from ipip_data order by random() limit 1;
   id   | city_code | isp |    subnet     | ip_int_start | ip_int_end |   ip_range    
--------+-----------+-----+---------------+--------------+------------+---------------
 272767 | US        | *   | 156.80.0.0/15 |   2622488576 | 2622619647 | 156.80.0.0/15
(1 row)

--随机选取其中一个IP
postgres=# select '156.80.8.88'::ip4::bigint;
    int8    
------------
 2622490712
(1 row)

postgres=# explain analyze select * from ipip_data where 2622490712 >=ip_int_start and 2622490712 <=ip_int_end;
                                                                     QUERY PLAN                                                                      
---------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on ipip_data  (cost=2266.78..4855.99 rows=92414 width=57) (actual time=4.592..4.594 rows=2 loops=1)
   Recheck Cond: (('2622490712'::bigint >= ip_int_start) AND ('2622490712'::bigint <= ip_int_end))
   Heap Blocks: exact=2
   ->  Bitmap Index Scan on ipip_data_ip_int_start_ip_int_end_idx  (cost=0.00..2243.68 rows=92414 width=0) (actual time=4.581..4.581 rows=2 loops=1)
         Index Cond: (('2622490712'::bigint >= ip_int_start) AND ('2622490712'::bigint <= ip_int_end))
 Planning time: 0.139 ms
 Execution time: 4.620 ms
(7 rows)

postgres=# explain analyze select * from ipip_data where '156.80.8.88' <<ip_range;
                                                           QUERY PLAN                                                            
---------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on ipip_data  (cost=4.66..409.00 rows=371 width=57) (actual time=0.076..0.079 rows=2 loops=1)
   Recheck Cond: ('156.80.8.88'::ip4r << ip_range)
   Heap Blocks: exact=2
   ->  Bitmap Index Scan on ipip_data_ip_range_idx  (cost=0.00..4.56 rows=371 width=0) (actual time=0.071..0.071 rows=2 loops=1)
         Index Cond: ('156.80.8.88'::ip4r << ip_range)
 Planning time: 0.054 ms
 Execution time: 0.127 ms
(7 rows)

postgres=# select 4.620/0.127;
      ?column?       
---------------------
 36.3779527559055118
(1 row)
```

可见，提升36倍， 数据量越大提升约明显。

![CENTER_PostgreSQL_Community](/images/news/2016/pg_bot_banner.jpg)

评论:1　　

顶: 595　

踩: 567　

请在登录后发表评论，否则无法保存。

1# __ xiaowu 回答于 2024-04-23 14:44:55+08

亲情作文800字:https://www.nanss.com/xuexi/4739.html 大格局有涵养的名句:https://www.nanss.com/xuexi/4544.html 游戏搞笑名字:https://www.nanss.com/mingcheng/4657.html lol游戏名字:https://www.nanss.com/mingcheng/4659.html 美甲店广告语:https://www.nanss.com/shenghuo/4682.html 一个季度几个月:https://www.nanss.com/wenti/4532.html 圣经名言:https://www.nanss.com/xuexi/4208.html 好听名字:https://www.nanss.com/shenghuo/4662.html 海底两万里读后感800字:https://www.nanss.com/xuexi/4620.html 搞笑的游戏名字:https://www.nanss.com/mingcheng/4285.html 岁月是把杀猪刀:https://www.nanss.com/yuedu/4775.html 适合发朋友圈的父爱句子:https://www.nanss.com/yulu/4490.html 晒收到女儿礼物的说说:https://www.nanss.com/wenan/4123.html 明天和意外哪个先来的经典语录:https://www.nanss.com/yulu/4455.html 秋分文案:https://www.nanss.com/wenan/4534.html 家长意见50字:https://www.nanss.com/xuexi/4184.html 服装店名称:https://www.nanss.com/shenghuo/4882.html 朋友圈发泄情绪的句子:https://www.nanss.com/yulu/4598.html 150字日记大全:https://www.nanss.com/xuexi/4133.html 发朋友圈招聘短语:https://www.nanss.com/gongzuo/4400.html 四个字网名大全:https://www.nanss.com/mingcheng/4415.html 爱老公的句子:https://www.nanss.com/yulu/4980.html 对逝者的悼念短句:https://www.nanss.com/yulu/4818.html 回家的暖心短句:https://www.nanss.com/yulu/4226.html 冬日暖阳的唯美句子:https://www.nanss.com/yulu/4918.html 夜晚月色迷人的句子:https://www.nanss.com/yulu/4122.html 三国人物名字:https://www.nanss.com/shenghuo/4638.html 英雄联盟名字:https://www.nanss.com/mingcheng/4060.html 用户名大全:https://www.nanss.com/mingcheng/4392.html 一路同行:https://www.nanss.com/yuedu/4799.html