postgresql-数据库网络地址存储探索（inet）
问题背景
数据库审核过程中发现有存储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做简单的加减。
