欢迎光临
一个学习的网站

MySQL中如何正确存储IP地址

一、IP地址应该怎么存

MySQL中,当存储IPv4地址时,应该使用32位的无符号整数(UNSIGNED INT)来存储IP地址,而不是使用字符串,用UNSIGNED INT类型存储IP 地址是一个4字节长的整数。

如果是字符串存储IP 地址,在正常格式下,最小长度为 7 个字符 (0.0.0.0),最大长度为 15 个 (255.255.255.255),因此,我们通常会使用varchar(15)来存储。同时为了让数据库准确跟踪列中有多少数据,数据库会添加额外的1字节来存储字符串的长度。这使得以字符串表示的 IP 的实际数据存储成本需要16字节。

这意味着如果将每个 IP 地址存储为字符串的话,每行需要多耗费大约 10 个字节的额外资源。

如果你说磁盘够使不是事儿,那我得告诉你,这个不仅会使数据文件消耗更多的磁盘,如果该字段加了索引,也会同比例扩大索引文件的大小,缓存数据需要使用更多内存来缓存数据或索引,从而可能将其他更有价值的内容推出缓存区。执行SQL对该字段进行CRUD时,也会消耗更多的CPU资源

 

MySQL中有内置的函数,来对IP和数值进行相互转换。

将IP转换成整数。

算法:第一位乘256三次方+第二位乘256二次方+第三位乘256一次方 + 第四位乘256零次方

将数字反向转换成IP

SQL

SELECT INET_ATON('127.0.0.1');
 
+------------------------+
| INET_ATON('127.0.0.1') |
+------------------------+
|             2130706433 | 
+------------------------+
1 row in set (0.00 sec)
 
 
SELECT INET_NTOA('2130706433');
 
+-------------------------+
| INET_NTOA('2130706433') |
+-------------------------+
| 127.0.0.1               | 
+-------------------------+
1 row in set (0.02 sec)

如果是 IPv6地址的话,可以使用函数 INET6_ATON() 和 INET6_NTOA() 来转化:

SQL

mysql> SELECT HEX(INET6_ATON('1030::C9B4:FF12:48AA:1A2B')); 
 
+----------------------------------------------+
| HEX(INET6_ATON('1030::C9B4:FF12:48AA:1A2B')) |
+----------------------------------------------+
| 1030000000000000C9B4FF1248AA1A2B             |
+----------------------------------------------+
1 row in set
 
mysql> SELECT INET6_NTOA(UNHEX('1030000000000000C9B4FF1248AA1A2B')); 
+-------------------------------------------------------+
| INET6_NTOA(UNHEX('1030000000000000C9B4FF1248AA1A2B')) |
+-------------------------------------------------------+
| 1030::c9b4:ff12:48aa:1a2b                             |
+-------------------------------------------------------+
1 row in set

然后将数据库定义为 varbinary类型,分配 128bits空间(因为 ipv6采用的是128bits,16个字节);或者定义为 char 类型,分配 32bits 空间。

二、整数存储 IP 地址的查询性能实验

测试数据,用存储过程生成了 100 万个随机 IP 地址;

1、测试范围查询:

IP转成Int,查询:耗时0.60s

SQL

select ip_int from T where ip_int > INET_ATON('192.0.0.0') and ip_int <=INET_ATON('192.255.255.255');
 
1726 row in set, 1 warning (0.60 sec)

SQL

select ip_varchar from T where ip_varchar like '192.%';
 
1726 row in set, 1 warning (0.63 sec)

2、IP精确查询:

SQL

select ip_int from T where ip_int = INET_ATON('192.168.0.0');
 
1 row in set, 1 warning (0.00 sec)

SQL

select ip_varchar from T where ip_varchar='192.168.0.0';
 
1 row in set, 1 warning (0.00 sec)

都是0s出结果。可认为常量索引查询,性能上无明显差异。

3、整理一下结果发现:

  • 范围查询和精确查询:

数据量少的情况下的差距不明显,如果数据量扩大到约1千万行或1亿行,1亿行时预计范围查询差距能拉开到0.5s。

 

  • 存储空间节省:

按1亿行算,理论上 varchar 最大15字节存储,数值4个字节,大约节省10字节 *1亿 约1G空间。

加上索文件引所占的空间,一个索引也是能节省1G。约能节省2G空间。

 

总结

IP地址数据采用整数(UNSIGNED INT)存储,在存储和CPU资源使用上都少于字符串存储形式;在歧义较大的范围查询中,存储整数方式无需关系范围中的位数问题,查询更加直观方便。

但整数存储需要使用INET_ATONINET_NTOA等特定函数处理,可读性查,函数也会消耗额外CPU,经检验发现CPU开支微乎其微。

因此,需要范围查询,且数据量很大(如亿级以上),采用数值存储IP地址的方式更优。如果均是唯一IP精确查询,或数据量不大,那么使用字符串操作更为简单。

赞(0) 打赏
版权声明:本文采用知识共享 署名4.0国际许可协议 [BY-NC-SA] 进行授权
文章名称:《MySQL中如何正确存储IP地址》
文章链接:https://zixijiaoshi.com/2981.html
本站资源仅供个人学习交流,请于下载后24小时内删除,不允许用于商业用途,否则法律问题自行承担。

评论 抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址

觉得文章有用就打赏一下文章作者

非常感谢你的打赏,我们将继续给力更多优质内容,让我们一起创建更加美好的网络世界!

支付宝扫一扫打赏

微信扫一扫打赏