老大告訴我不要用字符串存IP地址，不興~

數據庫中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()函數在這兩種表示方法之間轉換。

1

2

3

4

5

6

7

8

但項目中并未涉及到對IP地址的高頻查詢業務需求；所以嘛，你知道的，我們程序員的三不準則：跟自己沒關系的代碼不要看，自己模塊用不到的技術不要學，遺留代碼只要能跑的就不要動！

直到老大看我們項目數據表時問道：“你們存IP地址都是用字符串嗎？這可不興啊！應該用整數來存啊。”

“老大，我明白你的優化思路，你看咱們這表，就幾十條數據（狗頭）…”

直到上周有位同學問我IP地址在數據庫中該怎么存，他在面試中被問到了，我突然意識到了這玩意兒是時候記錄一下了。

目錄

一、IP地址應該怎么存

二、整數存儲 IP 地址的查詢性能實驗

1、測試范圍查詢：

2、IP精確查詢：

3、整理一下結果發現：

總結

一、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)

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

如果是 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

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

然后將數據庫定義為 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)

1

2

3

IP為字符串，查詢：耗時0.63s

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

1

2

3

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)

1

2

3

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

1

2

3

都是0s出結果。可認為常量索引查詢，性能上無明顯差異。

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精確查詢，或數據量不大，那么使用字符串操作更為簡單。

MySQL 數據庫

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。