<button id="jmirm"><form id="jmirm"></form></button><xmp id="jmirm"><form id="jmirm"></form><ins id="jmirm"><form id="jmirm"><xmp id="jmirm"><ins id="jmirm"><form id="jmirm"></form></ins><form id="jmirm"><button id="jmirm"><button id="jmirm"></button></button></form>
<form id="jmirm"></form>
<form id="jmirm"><button id="jmirm"></button></form><form id="jmirm"><form id="jmirm"><button id="jmirm"></button></form></form><form id="jmirm"><form id="jmirm"><button id="jmirm"></button></form></form>
<xmp id="jmirm">
<xmp id="jmirm"><xmp id="jmirm">
<button id="jmirm"><form id="jmirm"><ins id="jmirm"></ins></form></button>
<xmp id="jmirm"><form id="jmirm"></form>
<xmp id="jmirm"><form id="jmirm"><form id="jmirm"><button id="jmirm"></button></form></form><form id="jmirm"></form><form id="jmirm"></form>
<form id="jmirm"><button id="jmirm"></button></form><xmp id="jmirm"><form id="jmirm"></form><xmp id="jmirm"><form id="jmirm"><button id="jmirm"></button></form><form id="jmirm"></form><form id="jmirm"><form id="jmirm"></form></form>
<ins id="jmirm"><form id="jmirm"><form id="jmirm"></form></form></ins>
<xmp id="jmirm"><form id="jmirm"></form>
<xmp id="jmirm"><form id="jmirm"></form><form id="jmirm"></form>
<xmp id="jmirm">
<button id="jmirm"></button><form id="jmirm"></form><xmp id="jmirm"><form id="jmirm"></form><form id="jmirm"><button id="jmirm"><button id="jmirm"></button></button></form>
<button id="jmirm"></button>
<xmp id="jmirm"><form id="jmirm"><form id="jmirm"></form></form><form id="jmirm"></form><xmp id="jmirm"><xmp id="jmirm"><xmp id="jmirm">
<xmp id="jmirm">
<xmp id="jmirm">
歡迎光臨
一個學習的網站

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出結果??烧J為常量索引查詢,性能上無明顯差異。

3、整理一下結果發現:

  • 范圍查詢和精確查詢:

數據量少的情況下的差距不明顯,如果數據量擴大到約1千萬行或1億行,1億行時預計范圍查詢差距能拉開到0.5s。

 

  • 存儲空間節省:

按1億行算,理論上 varchar 最大15字節存儲,數值4個字節,大約節省10字節 *1億 約1G空間。

加上索文件引所占的空間,一個索引也是能節省1G。約能節省2G空間。

 

總結

IP地址數據采用整數(UNSIGNED INT)存儲,在存儲和CPU資源使用上都少于字符串存儲形式;在歧義較大的范圍查詢中,存儲整數方式無需關系范圍中的位數問題,查詢更加直觀方便。

但整數存儲需要使用INET_ATON、INET_NTOA等特定函數處理,可讀性查,函數也會消耗額外CPU,經檢驗發現CPU開支微乎其微。

因此,需要范圍查詢,且數據量很大(如億級以上),采用數值存儲IP地址的方式更優。如果均是唯一IP精確查詢,或數據量不大,那么使用字符串操作更為簡單。

贊(0) 打賞
版權聲明:本文采用知識共享 署名4.0國際許可協議 [BY-NC-SA] 進行授權
文章名稱:《MySQL中如何正確存儲IP地址》
文章鏈接:http://www.kliknaturalnusantara.com/2981.html
本站資源僅供個人學習交流,請于下載后24小時內刪除,不允許用于商業用途,否則法律問題自行承擔。

評論 搶沙發

  • 昵稱 (必填)
  • 郵箱 (必填)
  • 網址

覺得文章有用就打賞一下文章作者

非常感謝你的打賞,我們將繼續給力更多優質內容,讓我們一起創建更加美好的網絡世界!

支付寶掃一掃打賞

微信掃一掃打賞

国产高清伊人一二三区
<button id="jmirm"><form id="jmirm"></form></button><xmp id="jmirm"><form id="jmirm"></form><ins id="jmirm"><form id="jmirm"><xmp id="jmirm"><ins id="jmirm"><form id="jmirm"></form></ins><form id="jmirm"><button id="jmirm"><button id="jmirm"></button></button></form>
<form id="jmirm"></form>
<form id="jmirm"><button id="jmirm"></button></form><form id="jmirm"><form id="jmirm"><button id="jmirm"></button></form></form><form id="jmirm"><form id="jmirm"><button id="jmirm"></button></form></form>
<xmp id="jmirm">
<xmp id="jmirm"><xmp id="jmirm">
<button id="jmirm"><form id="jmirm"><ins id="jmirm"></ins></form></button>
<xmp id="jmirm"><form id="jmirm"></form>
<xmp id="jmirm"><form id="jmirm"><form id="jmirm"><button id="jmirm"></button></form></form><form id="jmirm"></form><form id="jmirm"></form>
<form id="jmirm"><button id="jmirm"></button></form><xmp id="jmirm"><form id="jmirm"></form><xmp id="jmirm"><form id="jmirm"><button id="jmirm"></button></form><form id="jmirm"></form><form id="jmirm"><form id="jmirm"></form></form>
<ins id="jmirm"><form id="jmirm"><form id="jmirm"></form></form></ins>
<xmp id="jmirm"><form id="jmirm"></form>
<xmp id="jmirm"><form id="jmirm"></form><form id="jmirm"></form>
<xmp id="jmirm">
<button id="jmirm"></button><form id="jmirm"></form><xmp id="jmirm"><form id="jmirm"></form><form id="jmirm"><button id="jmirm"><button id="jmirm"></button></button></form>
<button id="jmirm"></button>
<xmp id="jmirm"><form id="jmirm"><form id="jmirm"></form></form><form id="jmirm"></form><xmp id="jmirm"><xmp id="jmirm"><xmp id="jmirm">
<xmp id="jmirm">
<xmp id="jmirm">