PostgreSQL高级扩展之IP4R

来源: Qunar技术沙龙 作者: 李海龙 | 发布时间: 2018-12-12 09:00:00

IP4R - PostgreSQL中的IPv4/v6 及 IPv4/v6 范围类型 安装 原理 用法 IP4, IP6, IPAddress类型 IP4R, IP6R, IPRange类型 IPXR 索引 应用实践 实例1 实例2


点击箭头处“蓝色字”,关注我们哦!!



2012年加入Qunar,目前在旅游度假事业部担任PostgreSQL DBA。PostgreSQL中国社区成员,热爱数据库技术, 乐于在公司内外分享使用中的各种经验。


安装

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

  1. -- 安装至PostgreSQL


  2. sudo -s

  3. wget https://github.com/RhodiumToad/ip4r/archive/2.4.tar.gz

  4. tar -zxvf 2.4.tar.gz

  5. cd ip4r-2.4

  6. env USE_PGXS=1 make

  7. env USE_PGXS=1 make install

  8. -- DB中创建EXTENSION


  9. lhl@localhost:~$ psql mydb

  10. psql (11.1)

  11. Type "help" for help.

  12. mydb=# create extension ip4r ;

  13. CREATE EXTENSION


  14. mydb=# \dx ip4r

  15.     List of installed extensions

  16. Name | Version | Schema | Description

  17. ------+---------+--------+-------------

  18. ip4r | 2.4     | public |

  19. (1 row)

  20. -- 如果已安装低版本(比如ip4r2.2), 则不需要再创建EXTENSION,

  21. --尤其是已在使用的情况下, 不方便先DROP EXTENSION ip4r CASCADE再重建,

  22. -- 此时可使用EXTENSION升级语句

  23. mydb=# \dx ip4r

  24.     List of installed extensions

  25. Name | Version | Schema | Description

  26. ------+---------+--------+-------------

  27. ip4r | 2.2     | public |

  28. (1 row)

  29. mydb=# alter extension ip4r update TO "2.4" ;

  30. ALTER EXTENSION


  31. mydb=# \dx ip4r

  32.     List of installed extensions

  33. Name | Version | Schema | Description

  34. ------+---------+--------+-------------

  35. ip4r | 2.4     | public |

  36. (1 row)

原理

虽然 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地址

简要用法示例:

  1. mydb=# CREATE TABLE ipranges (range ip4r primary key, description text not null);

  2. CREATE TABLE

  3. mydb=# CREATE INDEX ipranges_range_idx ON ipranges USING gist (range);

  4. CREATE INDEX

  5. mydb=# INSERT INTO ipranges VALUES ('10.0.0.0/8','rfc1918 block 1');

  6. INSERT 0 1

  7. mydb=# INSERT INTO ipranges VALUES ('172.16.0.0/12','rfc1918 block 2');

  8. INSERT 0 1

  9. mydb=# INSERT INTO ipranges VALUES ('192.168.0.0/16','rfc1918 block 3');

  10. INSERT 0 1

  11. mydb=# INSERT INTO ipranges VALUES ('0.0.0.0/1','classical class A space');

  12. INSERT 0 1

  13. mydb=# INSERT INTO ipranges VALUES ('10.0.1.10-10.0.1.20','my internal network');

  14. INSERT 0 1

  15. mydb=# INSERT INTO ipranges VALUES ('127.0.0.1','localhost');

  16. INSERT 0 1

  17. mydb=#

  18. mydb=# CREATE TABLE access_log (id serial primary key, ip ip4 not null);

  19. CREATE TABLE

  20. mydb=# CREATE INDEX access_log_ip_idx ON access_log (ip);

  21. CREATE INDEX

  22. mydb=# INSERT INTO access_log(ip) VALUES ('10.0.1.15');

  23. INSERT 0 1

  24. mydb=# INSERT INTO access_log(ip) VALUES ('24.1.2.3');

  25. INSERT 0 1

  26. mydb=# INSERT INTO access_log(ip) VALUES ('192.168.10.20');

  27. INSERT 0 1

  28. mydb=# INSERT INTO access_log(ip) VALUES ('127.0.0.1');

  29. INSERT 0 1

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

  1. mydb=# SELECT * FROM access_log WHERE ip BETWEEN '10.0.0.0' AND '10.255.255.255';

  2. id |    ip    

  3. ----+-----------

  4.  1 | 10.0.1.15

  5. (1 row)

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

  1. mydb=# SELECT id,ip,range,description FROM access_log, ipranges WHERE ip <<= range;

  2. id |      ip       |        range        |       description      

  3. ----+---------------+---------------------+-------------------------

  4.  1 | 10.0.1.15     | 10.0.0.0/8          | rfc1918 block 1

  5.  1 | 10.0.1.15     | 0.0.0.0/1           | classical class A space

  6.  1 | 10.0.1.15     | 10.0.1.10-10.0.1.20 | my internal network

  7.  2 | 24.1.2.3      | 0.0.0.0/1           | classical class A space

  8.  3 | 192.168.10.20 | 192.168.0.0/16      | rfc1918 block 3

  9.  4 | 127.0.0.1     | 0.0.0.0/1           | classical class A space

  10.  4 | 127.0.0.1     | 127.0.0.1           | localhost

  11. (7 rows)

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

  1. mydb=# SELECT DISTINCT ON (ip) ip,range,description

  2. mydb-#   FROM access_log, ipranges

  3. mydb-#  WHERE ip <<= range

  4. mydb-#  ORDER BY ip, @ range;

  5.      ip       |        range        |       description      

  6. ---------------+---------------------+-------------------------

  7. 10.0.1.15     | 10.0.1.10-10.0.1.20 | my internal network

  8. 24.1.2.3      | 0.0.0.0/1           | classical class A space

  9. 127.0.0.1     | 127.0.0.1           | localhost

  10. 192.168.10.20 | 192.168.0.0/16      | rfc1918 block 3

  11. (4 rows)

用法

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"用来表述以上三种类型中的任意一种。 支持如下类型转换:

原数据类型目标数据类型格式
ipXtexttext(ipX) 或 ipX::text (显式)
textipXipX(text) 或 text::ipX (显式)
ipXcidrcidr(ipX) 或 ipX::cidr (赋值)
inetipXipX(inet) 或 inet::ipX  (赋值)
ipXnumericto_numeric(ipX) 或 ipX::numeric(显式)
numericipXipX(numeric) 或 bigint::ipX (显式)
ip4bigintto_bigint(ip4) 或 ip4::bigint (显式)
bigintip4ip4(bigint) 或 bigint::ip4 (显式)
ip4float8to_double(ip4) 或 ip4::float8  (显式)
float8ip4ip4(float8) 或 float8::ip4 (显式)
ipXvarbitto_bit(ipX) 或 ipX::varbit (显式)
bit(32)ip4ip4(bit) 或 bit::ip4  (显式)
bit(128)ip6ip6(bit) 或 bit::ip6  (显式)
varbitipXipX(varbit) 或varbit::ipX (显式)
ipXbyteato_bytea(ipX) 或 ipX::bytea(显式)
byteaipXipX(bytea) 或 bytea::ipX  (显式)
ipXipXripXr(ipX) 或ipX::ipXr (隐式)
ip4ipaddressipaddress(ip4) 或 ip4::ipaddress (隐式)
ip6ipaddressipaddress(ip6) 或 ip6::ipaddress (隐式)
ipaddress
ip4ip4(ipaddress) 或 ipaddress::ip4 (赋值)
ipaddressip6ip6(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:

  1. WHERE ipcolumn <<= value

至 btree 的范围扫描(内置的 INET 类型使用1个函数来做此种转换,但插件无法使用这个函数)。 可以使用如下替代解法:

  1. WHERE ipcolumn BETWEEN lower(value) AND upper(value)

这样就可以使用 Btree 范围扫描了。 IPX 支持如下额外操作符和函数:

  1. family(ipX) returns integer

  2. | 返回46, 具体取决于地址族


  3. ip4_netmask(integer) returns ip4

  4. | 返回ip4的值,其表示一个指定前缀长度的网络掩码

  5. ip6_netmask(integer) returns ip6

  6. | 返回ip6的值,其表示一个指定前缀长度的网络掩码

  7. ipX_net_lower(ipX, integer) returns ipX

  8. | 返回指定前缀长度的CIDR地址块中最低端地址, 包含指定的IP

  9. | 等价于: network(set_masklen(cidr(ipX),integer))

  10. ipX_net_upper(ipX, integer) returns ipX

  11. | 返回指定前缀长度的CIDR地址块中最高端地址, 包含指定的IP

  12. | 等价于: 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 支持如下类型转换:

原类型目标类型格式
ipXipXripXr(ipX)或ipX::ipXr(隐式)
ipXrtexttext(ipXr)或ipXr::text(显式)
textipXripXr(text) 或 text::ipXr(显式)
ipXrcidrcidr(ipXr)或ipXr::cidr(显式)
cidripXripXr(cidr)或cidr::ipXr(赋值)
ipXrvarbitto_bit(ipXr)或ipXr::varbit(显式)
varbieip4rip4r(varbit) 或 varbit::ip4r(显式)
varbitip6rip6r(varbit) 或 varbit::ip6r(显式)

如果 IPXR 的值不是合法的 CIDR 范围,则 CIDR(IPXR) 会返回 NULL。 此外,IP4R、IP6R 与 IPRange 之间的转换适用于所有的合法组合。 IPXR 支持如下函数:

  1. family(ipXr) returns integer

  2.  |  返回46, 具体取决于地址族, 如果是'-'::iprange,则返回NULL


  3. is_cidr(ipXr) returns boolean

  4.  |  如果此ipXr是一个合法的CIDR地址块,则返回TRUE
     

  5. lower(ipXr) returns ipX

  6.  |  返回指定ipXr范围中最低端地址
     

  7. upper(ipXr) returns ipX

  8.  |  返回指定ipXr范围中最高端地址
     

  9. cidr_split(ipXr) returns setof ipXr

  10.  |  将ipXr的范围拆分成独立的CIDR地址块,每块作为独立的行
     

  11. 例如:

  12. mydb=# select cidr_split('192.168.0.0-192.168.0.254'::ip4r);

  13.    cidr_split  

  14. ------------------

  15. 192.168.0.0/25

  16. 192.168.0.128/26

  17. 192.168.0.192/27

  18. 192.168.0.224/28

  19. 192.168.0.240/29

  20. 192.168.0.248/30

  21. 192.168.0.252/31

  22. 192.168.0.254

  23. (8 rows)

IPXR 支持如下操作符:

操作符描述
a = b等于
a <> b不等于
a < b注释[1]
a <= b注释[1]
a > b注释[1]
a >= b注释[1]
a >>= ba包含b或等于b
a >> ba严格包含b
a <<= ba包含于b或等于b
a && ba和b的交集
@ aa的近似大小(返回double类型)
@@ aa的确切大小(返回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 索引支持如下类型的查找体现出来:

  1. WHERE column >>= value      (或 >>)

  2. WHERE column <<= value      (或 <<)

  3. WHERE column && value

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

  1. CREATE INDEX indexname ON tablename USING gist (column);

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

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

(如果该列被约束为仅包含指定地址族的值,也可以使用 IP4R(column) 或 IP6R(column) )。 接下来上述索引就可以用于如下格式的 query:

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

这种方式的优势在于 IP4R 类型可以删除后重建而不丢数据。这对于设计时没有考虑用到 IP4R 的已有表上的 Query 提速极为有用。 另外一个习惯性用法: 应用程序创建2个整数列来表示 IP 地址范围, 且以如下格式来进行范围:

  1. WHERE value BETWEEN column1 and column2

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

  1. CREATE INDEX indexname ON tablename

  2.   USING gist (ip4r(column1::ip4,column2::ip4));

随后这样进行查找:

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

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

  1. SELECT * FROM tablename

  2. WHERE column >>= value

  3. ORDER BY @ column

  4. LIMIT 1

@column(近似大小)的用法足够应付 IPv4 范围或始终是 CIDR 前缀的值。如果出现的是任意 IPv6 范围,微小差异的范围对比时可能会得到相等的对比结果;这种情况下可使用 ORDER BY @@ column。 当查找多个 IP 时,可用如下格式进行查找:

  1. SELECT DISTINCT ON (ips.ip) ips.ip, ranges.range

  2.  FROM ips, ranges

  3. WHERE ranges.range >>= ips.ip

  4. ORDER BY ips.ip, @ ranges.range

应用实践

实例1

判断2个IP是否在指定的同一网段

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

  1. --封装一个UDF函数

  2. mydb=# CREATE OR REPLACE FUNCTION public.is_same_network(ip1 ip4, ip2 ip4, netmask integer)

  3. RETURNS boolean

  4. LANGUAGE plpgsql

  5. STRICT

  6. AS $function$                                                      

  7. declare                                                  

  8.    sql_string text;                                    

  9.    is_same_network boolean;                            

  10. begin                                                    

  11.    if netmask > 32 or netmask < 0 then                  

  12.        raise exception 'The mask must be between 0 and 32';

  13.    end if;                                              


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

  15.    execute sql_string into is_same_network;            

  16.    return is_same_network;  


  17. exception                                                

  18.    when others then                                    

  19.        raise NOTICE '%', SQLERRM;                      

  20.        return false;                                    

  21. end;                                                    

  22. $function$

  23. ;

  24. CREATE FUNCTION


  25. mydb=# select is_same_network('192.168.233.16','192.168.232.51',23);

  26. is_same_network

  27. -----------------

  28. t

  29. (1 row)

实例2

使用bigint与IP4R查询效率对比

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

  1. postgres=# select * from ipip_data order by random() limit 1;

  2.   id   | city_code | isp |    subnet     | ip_int_start | ip_int_end |   ip_range  

  3. --------+-----------+-----+---------------+--------------+------------+---------------

  4. 272767 | US        | *   | 156.80.0.0/15 |   2622488576 | 2622619647 | 156.80.0.0/15

  5. (1 row)


  6. --随机选取其中一个IP

  7. postgres=# select '156.80.8.88'::ip4::bigint;

  8.    int8  

  9. ------------

  10. 2622490712

  11. (1 row)



  12. postgres=# \d ipip_data

  13.                                      Table "public.ipip_data"

  14.    Column    |         Type          | Collation | Nullable |                Default              

  15. --------------+-----------------------+-----------+----------+---------------------------------------

  16. id           | integer               |           | not null | nextval('ipip_data_id_seq'::regclass)

  17. city_code    | character varying(64) |           |          |

  18. isp          | character varying(64) |           |          |

  19. subnet       | character varying(32) |           |          |

  20. ip_int_start | bigint                |           |          |

  21. ip_int_end   | bigint                |           |          |

  22. ip_range     | ip4r                  |           |          |

  23. Indexes:

  24.    "ipip_data_pkey" PRIMARY KEY, btree (id)

  25.    "ipip_data_ip_int_start_ip_int_end_idx" btree (ip_int_start, ip_int_end)

  26.    "ipip_data_ip_range_idx" gist (ip_range)


  27. postgres=# explain analyze select * from ipip_data where 2622490712 >=ip_int_start and 2622490712 <=ip_int_end;

  28.                                                                     QUERY PLAN                                                                    

  29. -----------------------------------------------------------------------------------------------------------------------------------------------------

  30. 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)

  31.   Recheck Cond: (('2622490712'::bigint >= ip_int_start) AND ('2622490712'::bigint <= ip_int_end))

  32.   Heap Blocks: exact=2

  33.   ->  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)

  34.         Index Cond: (('2622490712'::bigint >= ip_int_start) AND ('2622490712'::bigint <= ip_int_end))

  35. Planning time: 0.139 ms

  36. Execution time: 4.620 ms

  37. (7 rows)




  38. postgres=# explain analyze select * from ipip_data where '156.80.8.88' <<ip_range;

  39.                                                           QUERY PLAN                                                          

  40. ---------------------------------------------------------------------------------------------------------------------------------

  41. 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)

  42.   Recheck Cond: ('156.80.8.88'::ip4r << ip_range)

  43.   Heap Blocks: exact=2

  44.   ->  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)

  45.         Index Cond: ('156.80.8.88'::ip4r << ip_range)

  46. Planning time: 0.054 ms

  47. Execution time: 0.127 ms

  48. (7 rows)



  49. postgres=# select 4.620/0.127;

  50.      ?column?      

  51. ---------------------

  52. 36.3779527559055118

  53. (1 row)

可见,提升36倍,数据量越大提升约明显!

公众号导航