postgresql-数据库网络地址存储探索

最新推荐文章于 2023-09-16 22:10:09 发布

weixin_30675967

最新推荐文章于 2023-09-16 22:10:09 发布

阅读量246

点赞数

CC 4.0 BY-SA版权

文章标签：数据库

原文链接：http://www.cnblogs.com/zhangfx01/p/9506460.html

本文对比了PostgreSQL中使用varchar与inet类型存储IP地址的区别，包括存储空间、索引大小、IP验证、查询效率等方面，并介绍了inet类型的高级功能。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

问题背景

数据库审核过程中发现有存储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)

网络类型更多操作连接