在看公司项目代码时,有涉及到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条记录要花挺长时间的,如果你要自己实验,可以考虑少插入点数据):
建表和插入数据的sql语句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个字节,如下图
如果有知道的,请告诉我一声!不过让我从这两者之间选(信不过数据结果啊),肯定会选择使用varbinary(4)而不是bigint。如果能够证明数据结果没有错,应该选择bigint,因为他的可读性更好!
3、查询效率
本小节比较上述5中存储ip地址的查询效率。为了比较查询效率,这里重新插入数据,消除每张表中的记录都相同(192.168.120.65),下面编写存储过程像数据表中随机插入1000条记录(但是保证每张表的数据是一样的)。存储过程如下:
随机插入N条ip地址到5张表中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种类型的时间,代码中我直接使用了这些值,没有给出转换过程,具体代码如下:
查询192.0.0.0~192.255.255.255之间的ip地址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)。
——熬夜搞这问题,设计实验和测试方法,头脑现在有些不清醒了,不知实验数据和结论有没有出问题,有发现的请纠正!
作者:吴秦
出处:http://www.cnblogs.com/skynet/
本文基于署名 2.5 中国大陆许可协议发布,欢迎转载,演绎或用于商业目的,但是必须保留本文的署名吴秦(包含链接).