作者简介

李海龙，去哪儿网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中不同的数据类型:

二、安装

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=# \dx ip4r 
     List of installed extensions
 Name | Version | Schema | Description 
------+---------+--------+-------------
 ip4r | 2.4     | public | 
(1 row)
-- 如果已安装低版本(比如ip4r2.2), 则不需要再创建EXTENSION, 尤其是已在使用的情---况下, 不方便先DROP EXTENSION ip4r CASCADE; 再重建, 此时可使用EXTENSION----升级语句
mydb=# \dx ip4r 
     List of installed extensions
 Name | Version | Schema | Description 
------+---------+--------+-------------
 ip4r | 2.2     | public | 
(1 row)
mydb=# alter extension ip4r update TO “2.4” ;
ALTER EXTENSION
mydb=# \dx ip4r 
     List of installed extensions
 Name | Version | Schema | Description 
------+---------+--------+-------------
 ip4r | 2.4     | public | 
(1 row)

简要用法示例:

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的最确切的描述

mydb=# SELECT DISTINCT ON (ip) ip,range,description
mydb-#   FROM access_log, ipranges
mydb-#  WHERE ip <<= range
mydb-#  ORDER BY ip, @ range;
      ip       |        range        |       description       
---------------+---------------------+-------------------------
 10.0.1.15     | 10.0.1.10-10.0.1.20 | my internal network
 24.1.2.3      | 0.0.0.0/1           | classical class A space
 127.0.0.1     | 127.0.0.1           | localhost
 192.168.10.20 | 192.168.0.0/16      | rfc1918 block 3
(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” 用来表述以上三种类型中的任意一种。

支持如下类型转换:

来自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))

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 支持如下类型转换:

如果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支持如下操作符:

[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就可以完美搞定此需求。例如:

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=# \dt+ ipip_data
                      List of relations
 Schema |   Name    | Type  |  Owner   | Size  | Description 
--------+-----------+-------+----------+-------+-------------
 public | ipip_data | table | postgres | 38 MB | 
(1 row)

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=# \d ipip_data
                                      Table “public.ipip_data”
    Column    |         Type          | Collation | Nullable |                Default                
--------------+-----------------------+-----------+----------+---------------------------------------
 id           | integer               |           | not null | nextval('ipip_data_id_seq'::regclass)
 city_code    | character varying(64) |           |          | 
 isp          | character varying(64) |           |          | 
 subnet       | character varying(32) |           |          | 
 ip_int_start | bigint                |           |          | 
 ip_int_end   | bigint                |           |          | 
 ip_range     | ip4r                  |           |          | 
Indexes:
    “ipip_data_pkey” PRIMARY KEY, btree (id)
    “ipip_data_ip_int_start_ip_int_end_idx” btree (ip_int_start, ip_int_end)
    “ipip_data_ip_range_idx” gist (ip_range)

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倍， 数据量越大提升约明显。