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

当设计一个数据表时,考虑使用何种列的数据类型对性能有比较大的影响,如存储空间、查询开销等。甚至还影响到一些操作,如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

可以得到下面的结果:

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

列名 数据类型 说明
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个字节,如下图

如果能够证明数据结果没有错,应该选择bigint,因为他的可读性更好!

3、查询效率

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

省略原文过程。。。。

执行得到的消息如下:

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

作者:吴秦

来源:http://www.cnblogs.com/skynet/archive/2011/01/09/1931044.html

之前看discuz的数据表就发现这个问题了,ip用bigint效率较高。

About 智足者富

http://chenpeng.info

发表评论

电子邮件地址不会被公开。 必填项已用*标注

您可以使用这些HTML标签和属性:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>