postgres-数据库ip地址存储探索

问题背景

数据库审核过程中发现有存储ip的字段类型为varchar(50)、想到postgresql有专门的存储ip类型。然而存在即合理、所以主要对比varchar和inet存储ip的不同。

网络地址类型

名字	存储空间	描述
cidr	7 或 19 字节	IPv4 或 IPv6 网络
inet	7 或 19 字节	IPv4 或 IPv6 主机和网络
macaddr	6 字节	MAC 地址

cidr默认是存储了子网掩码、而inet可以不存储、macaddr用于存储MAC地址

创建测试表、生成测试数据

postgres=# create table t_ip_test(c_bh char(32),ip_start inet,ip_end inet);
CREATE TABLE
postgres=# insert into t_ip_test values(replace(CAST(uuid_generate_v4()as varchar), '-', ''),'192.168.1.1','192.230.254.254');
INSERT 0 1
--创建测试表
postgres=# create table t_ip(c_bh char(32),inet_ip inet,c_ip varchar (50));
CREATE TABLE
--生成测试数据192.168.1.1-192.230.254.254连续的ip
postgres=# insert into t_ip
postgres-# select
postgres-# replace(CAST(uuid_generate_v4()as varchar), '-', ''),
postgres-# generate_series(0,ip_end-ip_start)+ip_start as inet_ip,
postgres-# (generate_series(0,ip_end-ip_start)+ip_start) as c_ip
postgres-#from t_ip_test;
INSERT 0 4128254
--添加主键
postgres=# alter table public.t_ip ADD PRIMARY KEY ("c_bh");
ALTER TABLE

总共生成400W+数据

创建索引

--inet类型
postgres=# create index idx_t_ip_inet_ip on t_ip(inet_ip);
CREATE INDEX
--varchar类型
postgres=# create index idx_t_ip_c_ip on t_ip(c_ip);
CREATE INDEX

对比表大小、索引大小

--表大小
postgres=# select pg_size_pretty(pg_relation_size('t_ip')); 
 pg_size_pretty 
----------------
 365 MB
(1 row)
--索引大小
postgres=# select indexrelname, pg_size_pretty(pg_relation_size(indexrelname::varchar)) 
postgres-# from pg_stat_user_indexes where schemaname = 'public';
   indexrelname   | pg_size_pretty 
------------------+----------------
 t_ip_pkey        | 233 MB
 idx_t_ip_inet_ip | 88 MB
 idx_t_ip_c_ip    | 159 MB
(3 rows)

可以看到idx_tip_cip比idx_t_ip_inet_ip大了接近一倍

网络地址能自动验证ip正确性

--测试192.168.1.256
postgres=# insert into t_ip(c_bh,inet_ip,c_ip) values(replace(CAST(uuid_generate_v4()as varchar), '-', ''),'192.168.1.256','192.168.1.255');
ERROR:  invalid input syntax for type inet: "192.168.1.256"
LINE 1: ...lace(CAST(uuid_generate_v4()as varchar), '-', ''),'192.168.1...'
​
postgres=# insert into t_ip(c_bh,inet_ip,c_ip) values(replace(CAST(uuid_generate_v4()as varchar), '-', ''),'192.168.1.255','192.168.1.256')
postgres-# ;
INSERT 0 1

可以看到inet类型能够自动识别IP是否合理、而varchar会将错误的ip插入数据库

inet类型查询某个段、某个范围的ip可以走索引

--查询某个段的ip 使用inet类型
postgres=# explain analyze select c_bh,inet_ip,c_ip from t_ip where inet_ip <<= inet '192.168.12/24';
                                                         QUERY PLAN                                                         
----------------------------------------------------------------------------------------------------------------------------
-
 Index Scan using idx_t_ip_inet_ip on t_ip  (cost=0.43..17.98 rows=161 width=58) (actual time=0.054..0.659 rows=256 loops=1)
   Index Cond: ((inet_ip >= '192.168.12.0/24'::inet) AND (inet_ip <= '192.168.12.255'::inet))
   Filter: (inet_ip <<= '192.168.12.0/24'::inet)
 Planning time: 1.531 ms
 Execution time: 1.128 ms
(5 rows)
​
​
--查询某个段的ip 使用varchar类型
postgres=# explain analyze select c_bh,inet_ip,c_ip from t_ip where c_ip like '192.168.12.%'; 
                                                QUERY PLAN                                                
----------------------------------------------------------------------------------------------------------
 Seq Scan on t_ip  (cost=0.00..98381.98 rows=413 width=58) (actual time=0.761..1063.944 rows=256 loops=1)
   Filter: ((c_ip)::text ~~ '192.168.12.%'::text)
   Rows Removed by Filter: 4127998
 Planning time: 0.519 ms
 Execution time: 1064.433 ms
(5 rows)
​
--查询某个范围的ip inet类型
postgres=# explain analyze select c_bh,inet_ip,c_ip from t_ip where inet_ip >= inet '192.230.253.200' and inet_ip <'192.230.254.7';
                                                        QUERY PLAN                                                        
--------------------------------------------------------------------------------------------------------------------------
 Index Scan using idx_t_ip_inet_ip on t_ip  (cost=0.43..9.69 rows=63 width=58) (actual time=0.042..0.181 rows=63 loops=1)
   Index Cond: ((inet_ip >= '192.230.253.200'::inet) AND (inet_ip < '192.230.254.7'::inet))
 Planning time: 0.378 ms
 Execution time: 0.324 ms
(4 rows)
​
--使用varchar类型好像并没有什么好的办法查询一个范围

在inet类型上面创建btree索引、可以用上索引。

inet类型针对网络类型的操作更丰富(<、<=、=、>...)

--对ip做加减
postgres=# select inet'192.168.1.1'+100;
   ?column?    
---------------
 192.168.1.101
(1 row)
​
--查询两个ip之间有多少个ip
postgres=# select inet'192.168.2.1'-inet'192.168.1.1';
 ?column? 
----------
      256
(1 row)
​
--减去100个ip后的ip
postgres=# select inet'192.168.1.254'-100;
   ?column?    
---------------
 192.168.1.154
(1 row)

masklen:获取子网掩码长度

--inet获取子网掩码
postgres=# select masklen('192.168.1.1');
 masklen 
---------
      32
(1 row)
​
postgres=# select masklen('192.168.1.1/24');
 masklen 
---------
      24
(1 row)

网络类型更多操作连接

结语

推荐使用网络地址存储ip、PostgreSql提供的网络数据类型有以下优势:

1.创建索引更省空间

2.能够自动校验ip的正确

3.统计某段或者某个区间可以走索引、而varcahr并不能很好的统计某个区间

4.在做统计的时候网络类型操作更加丰富、对ip做简单的加减。