AI 助理
备案控制台
开发者社区 数据库 文章 正文

论IP地址在数据库中应该用何种形式存储

2016-04-26 1747
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
RDS SQL Server Serverless，2-4RCU 50GB 3个月
推荐场景：
云数据库 RDS SQL Server，基础系列 2核4GB
推荐场景：
SQL Server性能诊断案例分析
简介：

在看公司项目代码时，有涉及到ip地址存储，

使用的是varbinary(4)，但没有文档说明

这引发我的思考——缘起

 

当设计一个数据表时，考虑使用何种列的数据类型对性能有比较大的影响，如存储空间、查询开销等。甚至还影响到一些操作，如ip地址以字符串的形式存储在数据库中，就不可以直接比较大小。还有一点需要考虑，那就是可读性！数据虽然是存储在数据库中，但也要考虑到可读性问题。

本文要探讨的是“IP地址在数据库中，应该使用何种形式存储？”，文章将以实验为基础介绍使用何种形式比较适合。

1、感性认识

大 家都知道ip地址分为ipv4、ipv6，这里我以ipv4为例介绍，ipv6原理是一样的。ipv4的小为32bits（或者说是4Bytes），在使 用过程中，我们通常是用点分十进制格式，如192.168.120.65。如何把"192.168.120.65"存储到数据库中呢？

我们考虑下面三个因素：

  • 可读性
  • 存储效率
  • 查询效率

把"192.168.120.65"存储到数据库中有多少中可行方法呢？见下表所示：

数据类型

大小

注释

varchar(15)

占7～15字节

可读性最好（192.168.120.65），但是最费存储空间

bigint

8 字节

可以将ip地址存储为类似192168120065的格式，这种可读性稍差，也比较费存储空间

int

4 字节

这种可读性很差，会存储为1084782657，由192*16777216+168*65536+120*256+65-2147483648计算所得，占用存储空间少。

tinyint

4 字节

用4个字段来分开存储ip地址，可读性稍差（分别为192， 168， 120， 65），存储空间占用少

varbinary(4)

4 字节

可读性差（0xC0A87841），存储空间占用少

从大小来看，依次varchar(15)> bigint> int、tinyint、varbinary(4)。

从可读性来看，依次是varchar(15)> bigint> tinyint> varbinary(4)>int。

从查询效率来看，

综合考虑，似乎tinyint比较好，其次是varbinary(4)。但是tinyint需要占多个表字段，而varbinary只需要占用一个字段即可。正确性还有待下面的实验检查！！！

2、理性认识

本 小节通过创建5张表，分别用上述5中数据类型存储ip地址，每张表插入1,000,000条记录。说明为了方便消除差异，这些表中插入的都是 192.168.120.65。建表和插入数据的sql语句如下（说明：插入1,000,000条记录要花挺长时间的，如果你要自己实验，可以考虑少插入 点数据）：

create database ip_address_test;
go

use ip_address_test
/*****it defines ip address as varchar(15)*****/
create table ip_address_varchar(
	id int identity(1,1) not null primary key,
	ipAddress varchar(15)
);
/*****it defines ip address as bigint*****/
create table ip_address_bigint(
	id int identity(1,1) not null primary key,
	ipAddress bigint
);
/*****it defines ip address as int*****/
create table ip_address_int(
	id int identity(1,1) not null primary key,
	ipAddress int
);
/*****it defines ip address as tinyint*****/
create table ip_address_tinyint(
	id int identity(1,1) not null primary key,
	ip_address1 tinyint,
	ip_address2 tinyint,
	ip_address3 tinyint,	
	ip_address4 tinyint
);
/*****it defines ip address as varbinary(4)*****/
create table ip_address_varbinary(
	id int identity(1,1) not null primary key,
	ipAddress1 varbinary(4)
);


/*****insert data into tables*****/
declare @i int, @ip varchar(15)
set @i = 0
set @ip = '192.168.120.65'
while @i < 1000000 
begin 
	/**** insert into ip_address_varchar values ****/
    insert ip_address_varchar values(@ip) 

	/**** insert into ip_address_bigint values ****/
	insert ip_address_bigint values(
		convert( bigint,
			right('000'+convert(varchar(3), parsename(@ip, 4)),3)+
			right('000'+convert(varchar(3), parsename(@ip, 3)),3)+
			right('000'+convert(varchar(3), parsename(@ip, 2)),3)+
			right('000'+convert(varchar(3), parsename(@ip, 1)),3)
		)
	)

	/**** insert into ip_address_int values ****/
    insert ip_address_int values(
			cast(
				(cast(parsename(@ip, 4) as bigint)*16777216)+
				(cast(parsename(@ip, 3) as bigint)*65536)+
				(cast(parsename(@ip, 2) as bigint)*256)+
				cast(parsename(@ip, 1) as bigint)
				-2147483648
			as int)
	) 
 
	/**** insert into ip_address_tinyint values ****/
    insert ip_address_tinyint values(
			convert(tinyint, parsename(@ip, 4)),
			convert(tinyint, parsename(@ip, 3)),
			convert(tinyint, parsename(@ip, 2)),
			convert(tinyint, parsename(@ip, 1))
	) 
 
	/**** insert into ip_address_varbinary values ****/
    insert ip_address_varbinary values(
		cast( convert(tinyint, parsename(@ip, 4)) as varbinary)+
		cast( convert(tinyint, parsename(@ip, 3)) as varbinary)+
		cast( convert(tinyint, parsename(@ip, 2)) as varbinary)+
		cast( convert(tinyint, parsename(@ip, 1)) as varbinary)
	)

    set @i = @i + 1 
end

然后我们执行存储过程sp_spaceused查看空间效率，执行下面的sql语句：

exec sp_spaceused ip_address_varchar
exec sp_spaceused ip_address_bigint
exec sp_spaceused ip_address_int
exec sp_spaceused ip_address_tinyint
exec sp_spaceused ip_address_varbinary

可以得到下面的结果：

image

说明：上面各个字段的意思如下表所示

列名

数据类型

说明

reserved

varchar(18)

由数据库中对象分配的空间总量。

data

varchar(18)

数据使用的空间总量。

index_size

varchar(18)

索引使用的空间总量。

unused

varchar(18)

为数据库中的对象保留但尚未使用的空间总量。

可以看出，这5张表中的记录都是1000000，ip_address_varchar占空间最大30792 KB；其次是ip_address_bigint和ip_address_varbinary占用16904 KB；最后是ip_address_int和ip_address_tinyint只占用16904 KB。

所以从可读性和空间效率上来看，最理想的是用tinyint的数据类型存储ip地址。其次应该考虑varbinary(4)和bigint。

理论上bigint肯定要比varbinary占用空间多，可是实验得出来是一样的，为什么呢？我查看帮助信息也没有看出什么异常，varbinary(4)的确是占用4个字节、bigint也的确是占用8个字节，如下图

image

image

如果有知道的，请告诉我一声！不过让我从这两者之间选（信不过数据结果啊），肯定会选择使用varbinary(4)而不是bigint。如果能够证明数据结果没有错，应该选择bigint，因为他的可读性更好！

3、查询效率

本小节比较上述5中存储ip地址的查询效率。为了比较查询效率，这里重新插入数据，消除每张表中的记录都相同（192.168.120.65），下面编写存储过程像数据表中随机插入1000条记录（但是保证每张表的数据是一样的）。存储过程如下：

use ip_address_test
declare @ip1 tinyint, @ip2 tinyint, @ip3 tinyint, @ip4 tinyint, @i int
set @i = 1
while @i <= 1000
begin
	set @ip1 = FLOOR(256*RAND(cast(cast(left(newid(),8) as varbinary  ) as int )) )
	set @ip2 = FLOOR(256*RAND(cast(cast(left(newid(),8) as varbinary  ) as int )) )
	set @ip3 = FLOOR(256*RAND(cast(cast(left(newid(),8) as varbinary  ) as int )) )
	set @ip4 = FLOOR(256*RAND(cast(cast(left(newid(),8) as varbinary  ) as int )) )

	/**** insert into ip_address_varchar ****/ 
	declare @ip_varchar varchar(15)
	set @ip_varchar = cast(@ip1 as varchar)+'.'+
			cast(@ip2 as varchar)+'.'+
			cast(@ip3 as varchar)+'.'+
			cast(@ip4 as varchar)
	insert into ip_address_varchar values(@ip_varchar)

	/**** insert into ip_address_bigint ****/ 
	declare @ip_bigint bigint
	set @ip_bigint = convert( bigint,
				right('000'+convert(varchar(3), @ip1),3)+
				right('000'+convert(varchar(3), @ip2),3)+
				right('000'+convert(varchar(3), @ip3),3)+
				right('000'+convert(varchar(3), @ip4),3)
			)
	insert into ip_address_bigint values(@ip_bigint)

	/**** insert into ip_address_int ****/ 
	declare @ip_int int
	set @ip_int = cast(
				(cast(@ip1 as bigint)*16777216)+
				(cast(@ip2 as bigint)*65536)+
				(cast(@ip3 as bigint)*256)+
				cast(@ip4 as bigint)
				-2147483648
			as int)
	insert into ip_address_int values(@ip_int)

	/**** insert into ip_address_tinyint ****/ 
	insert into ip_address_tinyint values(@ip1,@ip2,@ip3,@ip4)

	/**** insert into ip_address_varbinary ****/ 
	declare @ip_varbinary varbinary(4)
	set @ip_varbinary = cast( convert(tinyint, @ip1) as varbinary)+
		cast( convert(tinyint, @ip2) as varbinary)+
		cast( convert(tinyint, @ip3) as varbinary)+
		cast( convert(tinyint, @ip4) as varbinary)
	insert into ip_address_varbinary values(@ip_varbinary)

	set @i = @i + 1
end

考虑查找在范围192.0.0.0~192.255.255.255之间的ip地址的查询效率问题。说明我忽略了预处理的开销，即将 192.0.0.0和192.255.255.255转换为上述的5种类型的时间，代码中我直接使用了这些值，没有给出转换过程，具体代码如下：

use ip_address_test
set statistics profile on
set statistics io on
set statistics time on
/**** find from ip_address_varchar ****/
select * from ip_address_varchar
where(
	cast(parsename(ipAddress, 4) as int) between 192 and 192
and cast(parsename(ipAddress, 3) as int) between 0 and 255
and cast(parsename(ipAddress, 2) as int) between 0 and 255
and cast(parsename(ipAddress, 1) as int) between 0 and 255
)
set statistics profile off
set statistics io off
set statistics time off

set statistics profile on
set statistics io on
set statistics time on
/*****find from ip_address_bigint*****/
select * from ip_address_bigint
where(
	ipAddress between 192000000000 and 192255255255
)
set statistics profile off
set statistics io off
set statistics time off

set statistics profile on
set statistics io on
set statistics time on
/*****find from ip_address_int*****/
select * from ip_address_int
where(
	ipAddress between 1073741824 and 1090519039
)
set statistics profile off
set statistics io off
set statistics time off

set statistics profile on
set statistics io on
set statistics time on
/*****find from ip_address_tinyint*****/
select * from ip_address_tinyint
where(
	ip_address1 between 192 and 192
and ip_address2 between 0 and 255
and ip_address3 between 0 and 255
and ip_address4 between 0 and 255
)
set statistics profile off
set statistics io off
set statistics time off

set statistics profile on
set statistics io on
set statistics time on
/*****find from ip_address_varbinary*****/
select * from ip_address_varbinary
where(
	ipAddress1 between 0xC0000000 and 0xC0FFFFFF
)
set statistics profile off
set statistics io off
set statistics time off


执行得到的消息如下：

SQL Server 分析和编译时间:
   CPU 时间 = 0 毫秒，占用时间 = 1 毫秒。

(5 行受影响)
表 'ip_address_varchar'。扫描计数 1，逻辑读取 6 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

(3 行受影响)

(1 行受影响)

SQL Server 执行时间:
   CPU 时间 = 0 毫秒，占用时间 = 113 毫秒。

SQL Server 执行时间:
   CPU 时间 = 0 毫秒，占用时间 = 1 毫秒。

SQL Server 执行时间:
   CPU 时间 = 0 毫秒，占用时间 = 1 毫秒。
SQL Server 分析和编译时间:
   CPU 时间 = 0 毫秒，占用时间 = 1 毫秒。

=============================共115毫秒，ip_address_varchar

(5 行受影响)
表 'ip_address_bigint'。扫描计数 1，逻辑读取 5 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

(2 行受影响)

(1 行受影响)

SQL Server 执行时间:
   CPU 时间 = 0 毫秒，占用时间 = 1 毫秒。

SQL Server 执行时间:
   CPU 时间 = 0 毫秒，占用时间 = 1 毫秒。

SQL Server 执行时间:
   CPU 时间 = 0 毫秒，占用时间 = 1 毫秒。
SQL Server 分析和编译时间:
   CPU 时间 = 0 毫秒，占用时间 = 1 毫秒。

===================================共4毫秒，ip_address_bigint

(5 行受影响)
表 'ip_address_int'。扫描计数 1，逻辑读取 5 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

(2 行受影响)

(1 行受影响)

SQL Server 执行时间:
   CPU 时间 = 0 毫秒，占用时间 = 146 毫秒。

SQL Server 执行时间:
   CPU 时间 = 0 毫秒，占用时间 = 1 毫秒。

SQL Server 执行时间:
   CPU 时间 = 0 毫秒，占用时间 = 1 毫秒。
SQL Server 分析和编译时间:
   CPU 时间 = 0 毫秒，占用时间 = 1 毫秒。

===================================共149毫秒，ip_address_int

(5 行受影响)
表 'ip_address_tinyint'。扫描计数 1，逻辑读取 5 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

(2 行受影响)

(1 行受影响)

SQL Server 执行时间:
   CPU 时间 = 0 毫秒，占用时间 = 85 毫秒。

SQL Server 执行时间:
   CPU 时间 = 0 毫秒，占用时间 = 1 毫秒。

SQL Server 执行时间:
   CPU 时间 = 0 毫秒，占用时间 = 1 毫秒。
SQL Server 分析和编译时间:
   CPU 时间 = 0 毫秒，占用时间 = 1 毫秒。

=======================================共88毫秒，ip_address_tinyint

(5 行受影响)
表 'ip_address_varbinary'。扫描计数 1，逻辑读取 5 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

(2 行受影响)

(1 行受影响)

SQL Server 执行时间:
   CPU 时间 = 0 毫秒，占用时间 = 13 毫秒。

SQL Server 执行时间:
   CPU 时间 = 0 毫秒，占用时间 = 1 毫秒。

SQL Server 执行时间:
   CPU 时间 = 0 毫秒，占用时间 = 1 毫秒。

===================================共15毫秒，ip_address_varbinary

上述结果只是初略的估计了效率，可能不太精确，但还是具有一定参考价值的！我只看ip_address_varbinary（15毫秒）、ip_address_tinyint（88毫秒）、ip_address_bigint（4毫秒）。

效率差距还是挺大的，综合可读性、存储效率、查询效率，我给这三者排序是：

如果考虑存储效率，tinyint是最好的！其次是bigint，然后是varbinary(4)

如果更多的是考虑查询效率，bigint是最好的！其次是varbinary(4)，然后是tinyint

如果加我选择，我会使用varbinary(4)。

 

——熬夜搞这问题，设计实验和测试方法，头脑现在有些不清醒了，不知实验数据和结论有没有出问题，有发现的请纠正！

文章标签：
云数据库 RDS SQL Server 版
存储
SQL
数据库
关键词：
数据库存储
数据库ip
ip地址数据库
ip数据库存储
相关实践学习
使用SQL语句管理索引
本次实验主要介绍如何在RDS-SQLServer数据库中，使用SQL语句管理索引。
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库，该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品：云数据库RDS&nbsp;SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能，强力支撑各种企业应用，同时也包含了微软的License费用，减少额外支出。 了解产品详情:&nbsp;https://www.aliyun.com/product/rds/sqlserver
吴秦tylerzhu
目录
相关文章
。思索
|
3月前
|
存储 关系型数据库 MySQL
MySQL——数据库备份上传到阿里云OSS存储
MySQL——数据库备份上传到阿里云OSS存储
。思索
161 0
小Tomkk
|
24天前
|
存储 关系型数据库 MySQL
PACS系统 中 dicom 文件在mysql 8.0 数据库中的 存储和读取（pydicom 库使用）
PACS系统 中 dicom 文件在mysql 8.0 数据库中的 存储和读取（pydicom 库使用）
小Tomkk
20 2
蓝易云
|
1月前
|
运维 监控 安全
【YashanDB知识库】ycm托管数据库时报错OM host ip：127.0.0.1 is not support join to YCM
总之，解决“OM host ip: 127.0.0.1 is not supported to join to YCM”的关键在于理解集群管理对IP地址的使用要求，并据此做出相应的配置调整，确保集群的稳定性和数据一致性。
蓝易云
16 1
瓴羊Dataphin
|
2月前
|
存储 SQL 专有云
支持配置审计日志的存储数据库
审计日志作为企业监管平台的重要依据，同时也是“等保三级”认证的必要考察项之一。Dataphin V4.3版本支持设置平台日志的存储数据源，帮助用户快速获取审计日志，同时介绍了不同部署模式的Dataphin如何查看审计日志的方法。
瓴羊Dataphin
107 5
yuanzhengme
|
2月前
|
存储 NoSQL 关系型数据库
可以存储文件的数据库有哪些？
可以存储文件的数据库有哪些？
yuanzhengme
68 6
yuanzhengme
|
2月前
|
存储 NoSQL 关系型数据库
可以存储文件的数据库有哪些？
可以存储文件的数据库有哪些？
yuanzhengme
440 0
pbeskoyq7ffm4
|
3月前
|
存储 缓存 负载均衡
带你认识DM 共享存储数据库集群
带你认识DM 共享存储数据库集群
pbeskoyq7ffm4
70 3
土木林森
|
3月前
|
存储 C# 关系型数据库
“云端融合：WPF应用无缝对接Azure与AWS——从Blob存储到RDS数据库，全面解析跨平台云服务集成的最佳实践”
【8月更文挑战第31天】本文探讨了如何将Windows Presentation Foundation（WPF）应用与Microsoft Azure和Amazon Web Services（AWS）两大主流云平台无缝集成。通过具体示例代码展示了如何利用Azure Blob Storage存储非结构化数据、Azure Cosmos DB进行分布式数据库操作；同时介绍了如何借助Amazon S3实现大规模数据存储及通过Amazon RDS简化数据库管理。这不仅提升了WPF应用的可扩展性和可用性，还降低了基础设施成本。
土木林森
77 0
听风de歌
|
1天前
|
SQL 关系型数据库 MySQL
go语言数据库中mysql驱动安装
【11月更文挑战第2天】
听风de歌
13 4
m1a6
|
25天前
|
存储 关系型数据库 MySQL
Mysql(4)—数据库索引
数据库索引是用于提高数据检索效率的数据结构，类似于书籍中的索引。它允许用户快速找到数据，而无需扫描整个表。MySQL中的索引可以显著提升查询速度，使数据库操作更加高效。索引的发展经历了从无索引、简单索引到B-树、哈希索引、位图索引、全文索引等多个阶段。
m1a6
58 3
Mysql(4)—数据库索引

热门文章

最新文章

  • 1
    RDS入门——Excel文件转存到RDS数据库实践
  • 2
    一篇文章彻底理解数据库的各种 JDBC 超时参数 2
  • 3
    数据库改名系列（数据库名，逻辑名，物理文件名）
  • 4
    数据库只读时无法更新的解决方法
  • 5
    干货分享 ｜ 揭秘下一代云数据库引擎MyBase for MySQL
  • 6
    Myeclipse 操作数据库
  • 7
    数据库模型:层次模型、网状模型、关系模型
  • 8
    RDS审计中心--数据库安全监控利器
  • 9
    使用xtrabackup备份innodb引擎的数据库
  • 10
    数据库安全之权限与角色
  • 1
    社招｜成为“云”住民，「阿里云瑶池数据库」多岗位持续热招中！
    166
  • 2
    JAVA数据库的基础知识
    69
  • 3
    启动多个jar包来监听同一个数据库的binlog
    72
  • 4
    OceanBase数据库的主备库参照的配置文件
    158
  • 5
    参与TiDB社区，共筑开源数据库的未来
    115
  • 6
    TiDB与MySQL、PostgreSQL等数据库的比较分析
    1050
  • 7
    sql数据库查询语句大全
    100
  • 8
    面对多种国产数据库，如何选择合适的技术栈和发展方向
    179
  • 9
    宝塔数据库崩溃解决方案详解
    262
  • 10
    视觉智能平台常见问题之创建人脸数据库失败如何解决
    70

    • 相关课程

    更多
  • 数据库的前世今生
  • 数据库核心概念
  • 从传统数据库到云数据库演进
  • 数据库常见问题排查
  • 数据库及SQL/MySQL基础
  • 高校精品课-西安交通大学 -数据库理论与技术

    • 相关电子书

    更多
  • DTCC 2022大会集锦《云原生一站式数据库技术与实践》
  • 阿里云瑶池数据库精要2022版
  • 2022 DTCC-阿里云一站式数据库上云最佳实践

    • 相关实验场景

    更多
  • 如何在云端创建MySQL数据库
  • 数据库实验室挑战任务-高级任务
  • 数据库实验室挑战任务-中级任务
  • 数据库实验室挑战任务-初级任务
  • 阿里云数据库产品实验室
  • MongoDB数据库入门
    • 下一篇
    DataWorks售前咨询