数据库中IP地址存储形式分析

类型：IP工具大小：2.0M语言：中文评分：6.0标签:立即下载。设计数据表时，考虑哪种列数据类型会对性能产生很大影响，例如存储空间和查询开销。甚至会影响一些操作。例如，如果ip地址以字符串的形式存储在数据库中，就不可能直接比较大小。另外要考虑的是可读性！虽然数据存储在数据库中，但应考虑可读性。本文要讨论的是“数据库中的IP地址，应该用哪种形式存储？”本文将根据实验介绍哪种形式更合适。1.感知知识：大家都知道ip地址分为ipv4和ipv6。这里我就以ipv4为例，和ipv6的原理是一样的。Ipv4是32位(或4字节)，我们通常使用点分十进制格式，如192.168.120.65。如何在数据库中存储‘192 . 168 . 120 . 65’？我们考虑以下三个因素：可读性、存储效率和查询效率。在数据库中存储‘192 . 168 . 120 . 65’有多少可行的方法？如下表所示：数据类型大小标注varchar(15)占用7 ~ 15字节，可读性最好(192.168.120.65)，但ip地址可以用类似192168120065的格式存储，存储空间最贵bigint8字节，比内存空间int4字节可读性稍差，将存储为10847825 Calculated by 192 * 16777216 168 * 6555Tinyint4字节使用4个字段分别存储ip地址，可读性稍差(分别为192、168、120、65)。varbinary(4)4字节可读性差(0xC0A87841)，存储空间小。大小方面，Varchar (15) Bigint，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，以便消除差异。

建表和插入数据的结构化查询语言语句如下（说明：插入1,000,000条记录要花挺长时间的，如果你要自己实验，可以考虑少插入点数据):建表和插入数据的结构化查询语言语句创建数据库互联网协议（Internet Protocol的缩写）地址测试；gouse ip _ address _ test/* * * * *它将互联网协议(Internet Protocol)地址定义为varchar(15)* * * */create table IP _ address _ varchar(id int identity(1，1))不为空主键，IP address varchar(15))；/* * * * *它将互联网协议（Internet Protocol的缩写）地址定义为big int * * * * */创建表IP _ address _ big int(id int identity(1，1))不为空主键，ipAddress big int)；/* * * * *它将互联网协议（Internet Protocol的缩写）地址定义为int * * * * */创建表IP _ address _ int(id int identity(1，1))不是空主键，ipAddress int)；/* * * * *它将互联网协议（Internet Protocol的缩写）地址定义为tinyint * * * * */创建表IP _ address _ tinyint(id int identity(1，1))不为空主键，ip_address1 tinyint，ip_address2 tinyint，ip_address3 tinyint，IP _ address 4 tinyint)；/* * * * *它将互联网协议（Internet Protocol的缩写）地址定义为varbinary(4)* * * * * * */创建表IP _ address _ varbinary(id int identity(1，1))不为空主键，IP address 1 varbinary(4))；/* * * * *将数据插入表中* * * * */声明@i int，@ IP varchar(15)set @ I=0 set @ IP=' 192。168 .120 .65 '而@i 1000000 begin /***插入ip_address_varchar值* * * */插入ip_address_varchar值(@ip) /****插入ip_address_bigint值* * * */插入ip_address_bigint值(convert(bigint，right(' 000)convert(varchar 4))、convert(tinyint，parsename(@ip，3))、convert(tinyint，parsename(@ip，2))、convert(tinyint，parsename(@ip，1)))/* * * *插入IP _ address _ varbinary值* * * */插入IP _ address _ varbinary值（转换(tinyint，parsename(@ip，4))为varbinary)转换(convert(tinyint，parsename(@ip，3))为varbinary)转换(convert(tinyint，parsename(@ip，3))然后我们执行存储过程sp_spaceused查看空间效率，执行下面的结构化查询语言语句：exec sp _ space已用IP _ address _ varcharexec sp _ space已用IP _ address _ big intexec sp _ space已用IP _ address _ intexec sp _ space已用IP _ address _ tinyintexc sp _ space已用IP _ address _ varbinary可以得到下面的结果

注：上述字段的含义如下表所示

可以看出，这五个表中的记录都是1000000，ip_address_varchar占用的空间最大，为30792 KB；其次，ip_address_bigint和ip_address_varbinary占用16904kb最后，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中存储互联网协议(互联网协议的缩写)地址的查询效率。为了比较查询效率，这里重新插入数据，消除每张表中的记录都相同(192.168.120.65),下面编写存储过程像数据表中随机插入1000条记录（但是保证每张表的数据是一样的)。存储过程如下：随机插入普通条互联网协议(互联网协议的缩写)地址到5张表中使用IP _ address _ test声明@ ip1 tinyint、@ip2 tinyint、@ip3 tinyint、@ip4 tinyint、@ I int set @ I=1而@ I=1000 begin et @ ip1=FLOOR(256 * RAND(cast(left(newid)，8)为varbinary)为int)))set @ ip2=FLOOR(256 * RAND(cast(left(newid，8)为varbinary)为int)))set @ ip3=FLOOR(256)强制转换(@ip2 as varchar)"。强制转换(@ip3 as varchar)"。强制转换(@ip4 as varchar)插入ip_address_varchar值(@ip_varchar)/****插入ip_address_bigint ****/声明@ IP _ bigint set @ IP _ bigint=convert(bigint，right('000' convert(varchar(3)，@ip1)，3) right('000' convert(varchar(3)，@ip3)，3) right('000' convert(varchar(3)，@ ip3)，3) right('000 '考虑查找在范围192.0.0.0~192.255.255.255之间的互联网协议(互联网协议的缩写)地址的查询效率问题。

说明我忽略了预处理的开销，即将192.0.0.0和192.255.255.255转换为上述的5种类型的时间，代码中我直接使用了这些值，没有给出转换过程，具体代码如下：查询192.0.0.0~192.255.255.255之间的互联网协议(互联网协议的缩写)地址使用ip地址测试集统计信息配置文件开始统计信息超正析象管开始统计信息开始时间开/***从ip_address_varchar ****/select *从IP _ address _ varchar where(cast(parse name(ipAddress，4)为int)介于192和192之间，cast(parsename(ipAddress，3)为int)介于0和255之间，cast(parsename(ipAddress，2)为int)介于0和255之间，强制转换(解析名称(ipAddress，1)作为int)在0和255之间)设置统计配置文件偏移量统计超正析象管偏移量统计时间偏移量统计配置文件开始统计超正析象管开始统计时间开/* * * * *从IP _ address _ bigint * * * * */选择*从IP _ address _ bigint其中(IP地址在19200000000和192255255255之间)设置统计配置文件偏移量统计超正析象管开始统计时间偏移量统计配置文件开始统计超正析象管开始统计时间开/* * * * *从IP _ address _ int * * * * */选择*来自IP _ address _ int其中(IP地址介于1073741824和1090519039之间)设置统计配置文件偏移统计超正析象管偏移统计时间偏移统计配置文件开始统计超正析象管开始统计时间开/* * * * *从IP _ address _ tinyint * * * * */选择*来自IP _ address _ tinyintwhere(IP _ address 1介于192和192之间，ip_address2介于0和255之间，ip_address3介于0和255之间，ip_address4介于0和255之间)从IP _ address _ varbinary其中(ipaddress 1在0xC000000和0xC0FFFFFF之间)设置统计配置文件偏移统计超正析象管偏移统计时间关闭执行得到的消息如下：SQL Server分析和编译时间：中央处理器时间=0 毫秒，占用时间=1 毫秒。(5 行受影响)表' ip_address_varchar '。扫描计数1,逻辑读取6 次，物理读取0 次，预读0 次高球逻辑读取0 次高球物理读取0 次高球预读0 次。(3 行受影响)(1 行受影响)SQL Server执行时间：CPU时间=0 毫秒，占用时间=113 毫秒结构化查询语言服务器执行时间：CPU时间=0 毫秒，占用时间=1 毫秒结构化查询语言服务器执行时间：CPU时间=0 毫秒，占用时间=1 毫秒结构化查询语言服务器分析和编译时间：中央处理器时间=0 毫秒，占用时间=1 毫秒。=============================共115毫秒，ip_address_varchar(5行受影响)表' ip_address_bigint '。扫描计数1,逻辑读取5次，物理读取0 次，预读0 次高球逻辑读取0 次高球物理读取0 次高球预读0 次。(2 行受影响)(1 行受影响)SQL Server执行时间：CPU时间=0 毫秒，占用时间=1 毫秒结构化查询语言服务器执行时间：CPU时间=0 毫秒，占用时间=1 毫秒结构化查询语言服务器执行时间：CPU时间=0 毫秒，占用时间=1 毫秒结构化查询语言服务器分析和编译时间：中央处理器时间=0 毫秒，占用时间=1 毫秒。===================================共四毫秒，ip_address_bigint(5行受影响)表' ip_address_int '。扫描计数1,逻辑读取5次，物理读取0 次，预读0 次高球逻辑读取0 次高球物理读取0 次高球预读0 次。(2 行受影响)(1 行受影响)SQL Server执行时间：CPU时间=0 毫秒，占用时间=146 毫秒结构化查询语言服务器执行时间：CPU时间=0 毫秒，占用时间=1 毫秒结构化查询语言服务器执行时间：CPU时间=0 毫秒，占用时间=1 毫秒结构化查询语言服务器分析和编译时间：中央处理器时间=0 毫秒，占用时间=1 毫秒。===================================共149毫秒，ip_address_int(5行受影响)表' ip_address_tinyint '。扫描计数1,逻辑读取5次，物理读取0 次，预读0 次高球逻辑读取0 次高球物理读取0 次高球预读0 次。

(受影响的2行) (受影响的1行)SQL Server执行时间：CPU时间=0毫秒，运行时间=85毫秒。执行时间SQL Server :CPU时间=0 ms，运行时间=1 ms执行时间SQL Server :CPU时间=0 ms，运行时间=1 ms. SQL Server分析编译时间：CPU时间=0毫秒，运行时间=1毫秒。=====================================总共88毫秒，IP _ address _精致(受影响的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 ms，运行时间=1 ms SQL Server :的执行时间CPU时间=0 ms，运行时间=1 ms============================================总共15毫秒。ip_address_varbinary以上结果只是对效率的初步估计，可能不太准确，但还是有一定的参考价值！我只看IP _ address _ varbinary(15毫秒)、IP _ address _ tinyint(88毫秒)和IP _ address _ big int(4毫秒)。效率差距还是挺大的。综合考虑可读性、存储效率和查询效率，我给它们排名如下：如果考虑存储效率，tinyint最好！Bigint是下一个，然后是varbinary(4)如果更关注查询效率，那么bigint是最好的！然后是varbinary(4)，然后是tinyint。如果我选择，我将使用varbinary(4)。