MySQL中如何正确存储IP地址
作者:_陈哈哈 发布时间:2024-01-29 13:34:34
数据库中IP地址数据该怎么存?或许你已经不止一次遇到过这类问题,怎么存?varchar(255)不就完事儿了?坦白说,在我经历的几个项目中,几乎都遇到过存储IP地址(V4、V6)的数据字段,都用的变长字符串varchar(15)来存储,嗯,感觉还挺香的…
其实很早以前我就在《高性能MySQL第三版》中看过IP地址属于特殊类型数据,应转为整数存储。
《高性能MySQL第三版》
4.1.7 特殊类型数据
-某些类型的数据并不直接与内置类型一致。低于秒级精度的时间戳就是一个例子;
-本意的前面部分也演示过存储此类数据的一些选项。
-另一个例子是一个IPv4地址。人们经常使用VARCHAR(15)列来存储IP地址。
-然而,它们实际上是 32位无符号整数,不是字符串。用小数点将地址分成四段的表示方法只是为了让人们阅读容易。
-所以应该用无符号整数存储IP地址。MySQL提供INET ATON()和 INET NTOA()函数在这两种表示方法之间转换。
但项目中并未涉及到对IP地址的高频查询业务需求;所以嘛,你知道的,我们程序员的三不准则:跟自己没关系的代码不要看,自己模块用不到的技术不要学,遗留代码只要能跑的就不要动!
直到老大看我们项目数据表时问道:“你们存IP地址都是用字符串吗?这可不兴啊!应该用整数来存啊。”
“老大,我明白你的优化思路,你看咱们这表,就几十条数据(狗头)…”
直到上周有位同学问我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资源。
在早先使用Oracle10g时,是没有相关函数来进行IP整数和字符串的,但在MySQL中有内置的函数,来对IP和数值进行相互转换。
INET_ATON()
将IP转换成整数。
算法:第一位乘256三次方+第二位乘256二次方+第三位乘256一次方 + 第四位乘256零次方
INET_NTOA()
将数字反向转换成IP
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() 来转化:
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
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)
IP为字符串,查询:耗时0.63s
select ip_varchar from T where ip_varchar like '192.%';
1726 row in set, 1 warning (0.63 sec)
2、IP精确查询:
select ip_int from T where ip_int = INET_ATON('192.168.0.0');
1 row in set, 1 warning (0.00 sec)
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空间。
来源:https://chensj.blog.csdn.net/article/details/121073057
猜你喜欢
- 本文实例讲述了Python使用dict.fromkeys()快速生成一个字典。分享给大家供大家参考,具体如下:>>> re
- 在Linux下安装Oracle数据库是一件较为麻烦的事情。由于Linux的安装过程中可能会选择不同的安装包,会导致Oracle安装过程中缺失
- 使用PyQt5应用程序制作PDF转换成图片的小工具,可以导入PDF文档后一键生成对应的PNG图片。PDF图片转换小工具使用的中间件:pyth
- 安装去http://www.mysql.com/downloads/, 选择最下方的MySQL Community Edition,点击My
- 1、凸包检测与凸缺陷定义凸包是将最外层的点连接起来构成的凸多边形,它能包含点击中所有的点。物体的凸包检测常应用在物体识别、手势识别及边界检测
- perl - Practical Extraction and Report Language,Perl有很多命令行参数,通过它可以让你的程
- 题目描述:(1)模拟登陆界面,判别用户名和密码,给出合适的提示,如果超过三次,锁定输入。用代替密码;或者最新输入显示,前面的变成;安全性措施
- 在现在的项目里,不管是电商项目还是别的项目,在管理端都会有导出的功能,比方说订单表导出,用户表导出,业绩表导出。这些都需要提前生成excel
- [Hack] 意为”劈”、”砍”。 [Hacker] 意为”黑客”CSS Hack 是指针对不同的浏览器写不同的CSS code的过程,简单
- ASP里两种常用的生成文件的方式是:利用ADODB.Stream生成文件和利用Scripting.FileSystemObject生成文件1
- Decode函数的语法结构如下:decode (expression, search_1, result_1)decode (express
- Sun Microsystems公司宣布,正式对外提供MySQL 5.1软件——这是全球最受欢迎的开
- 背景最近在写一个echarts数据看板,要在一个页面中展示多张图表,所以留给每张图表的尺寸就很小。这也就使得图表x轴的刻度文字全部挤到一起了
- 上一篇博文,我们已经顺利的从cnodejs.org请求到了数据,但是大家可以注意到我们的/src/api/index.js的第一句就是://
- 写入txt文件def text_save(filename, data):#filename为写入CSV文件的路径,data为要写入数据列表
- 文章背景每隔几年,开放式Web应用程序安全项目就会对最关键的Web应用程序安全风险进行排名。自第一次报告以来,注入风险高居其位!在所有注入类
- 有一个优秀的库可以使用————demjson示范链接http
- 本文记录了mysql 8.0.17 安装配置的图文教程,供大家参考,具体内容如下1.下载安装包下载地址:安装包2.解压安装包到目录这里解压到
- 记录了CentOS7 安装python3.7.0的详细过程,供大家参考,具体内容如下1.下载及解压python3.7的安装包可从官网下载上传
- 很多朋友问过我absolute与relative怎么区分,怎么用?我们都知道absolute是绝对定位,relative是相对定位,但是这个