老大告訴我不要用字符串存IP地址，不興~
數(shù)據(jù)庫(kù)中IP地址數(shù)據(jù)該怎么存?或許你已經(jīng)不止一次遇到過(guò)這類問(wèn)題，怎么存?varchar(255)不就完事兒了?坦白說(shuō)，在我經(jīng)歷的幾個(gè)項(xiàng)目中，幾乎都遇到過(guò)存儲(chǔ)IP地址(V4、V6)的數(shù)據(jù)字段，都用的變長(zhǎng)字符串varchar(15)來(lái)存儲(chǔ)，嗯，感覺(jué)還挺香的...
其實(shí)很早以前我就在《高性能MySQL第三版》中看過(guò)IP地址屬于特殊類型數(shù)據(jù)，應(yīng)轉(zhuǎn)為整數(shù)存儲(chǔ)。
《高性能MySQL第三版》
4.1.7 特殊類型數(shù)據(jù)
某些類型的數(shù)據(jù)并不直接與內(nèi)置類型一致。低于秒級(jí)精度的時(shí)間戳就是一個(gè)例子;本意的前面部分也演示過(guò)存儲(chǔ)此類數(shù)據(jù)的一些選項(xiàng)。
另一個(gè)例子是一個(gè)IPv4地址。人們經(jīng)常使用VARCHAR(15)列來(lái)存儲(chǔ)IP地址。然而，它們實(shí)際上是 32位無(wú)符號(hào)整數(shù)，不是字符串。用小數(shù)點(diǎn)將地址分成四段的表示方法只是為了讓人們閱讀容易。所以應(yīng)該用無(wú)符號(hào)整數(shù)存儲(chǔ)IP地址。MySQL提供INET ATON()和 INET NTOA()函數(shù)在這兩種表示方法之間轉(zhuǎn)換。
??但項(xiàng)目中并未涉及到對(duì)IP地址的高頻查詢業(yè)務(wù)需求;所以嘛，你知道的，我們程序員的三不準(zhǔn)則：跟自己沒(méi)關(guān)系的代碼不要看，自己模塊用不到的技術(shù)不要學(xué)，遺留代碼只要能跑的就不要?jiǎng)?
直到老大看我們項(xiàng)目數(shù)據(jù)表時(shí)問(wèn)道："你們存IP地址都是用字符串嗎?這可不興啊!應(yīng)該用整數(shù)來(lái)存啊。"
"老大，我明白你的優(yōu)化思路，你看咱們這表，就幾十條數(shù)據(jù)(狗頭)...."
直到上周有位同學(xué)問(wèn)我IP地址在數(shù)據(jù)庫(kù)中該怎么存，他在面試中被問(wèn)到了，我突然意識(shí)到了這玩意兒是時(shí)候記錄一下了。
一、IP地址應(yīng)該怎么存
在MySQL中，當(dāng)存儲(chǔ)IPv4地址時(shí)，應(yīng)該使用32位的無(wú)符號(hào)整數(shù)(UNSIGNED INT)來(lái)存儲(chǔ)IP地址，而不是使用字符串，用UNSIGNED INT類型存儲(chǔ)IP 地址是一個(gè)4字節(jié)長(zhǎng)的整數(shù)。
如果是字符串存儲(chǔ)IP 地址，在正常格式下，最小長(zhǎng)度為 7 個(gè)字符 (0.0.0.0)，最大長(zhǎng)度為 15 個(gè) (255.255.255.255)，因此，我們通常會(huì)使用varchar(15)來(lái)存儲(chǔ)。同時(shí)為了讓數(shù)據(jù)庫(kù)準(zhǔn)確跟蹤列中有多少數(shù)據(jù)，數(shù)據(jù)庫(kù)會(huì)添加額外的1字節(jié)來(lái)存儲(chǔ)字符串的長(zhǎng)度。這使得以字符串表示的 IP 的實(shí)際數(shù)據(jù)存儲(chǔ)成本需要16字節(jié)。
這意味著如果將每個(gè) IP 地址存儲(chǔ)為字符串的話，每行需要多耗費(fèi)大約 10 個(gè)字節(jié)的額外資源。
如果你說(shuō)磁盤(pán)夠使不是事兒，那我得告訴你，這個(gè)不僅會(huì)使數(shù)據(jù)文件消耗更多的磁盤(pán)，如果該字段加了索引，也會(huì)同比例擴(kuò)大索引文件的大小，緩存數(shù)據(jù)需要使用更多內(nèi)存來(lái)緩存數(shù)據(jù)或索引，從而可能將其他更有價(jià)值的內(nèi)容推出緩存區(qū)。執(zhí)行SQL對(duì)該字段進(jìn)行CRUD時(shí)，也會(huì)消耗更多的CPU資源。
在早先使用Oracle10g時(shí)，是沒(méi)有相關(guān)函數(shù)來(lái)進(jìn)行IP整數(shù)和字符串的，但在MySQL中有內(nèi)置的函數(shù)，來(lái)對(duì)IP和數(shù)值進(jìn)行相互轉(zhuǎn)換。
- INET_ATON()
將IP轉(zhuǎn)換成整數(shù)。
算法：第一位乘256三次方+第二位乘256二次方+第三位乘256一次方 + 第四位乘256零次方
- INET_NTOA()
將數(shù)字反向轉(zhuǎn)換成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地址的話，可以使用函數(shù) INET6_ATON() 和 INET6_NTOA() 來(lái)轉(zhuǎn)化：
- 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
然后將數(shù)據(jù)庫(kù)定義為 varbinary 類型，分配 128bits 空間(因?yàn)?ipv6采用的是128bits，16個(gè)字節(jié));或者定義為 char 類型，分配 32bits 空間。
二、整數(shù)存儲(chǔ) IP 地址的查詢性能實(shí)驗(yàn)
測(cè)試數(shù)據(jù)，用存儲(chǔ)過(guò)程生成了 100 萬(wàn)個(gè)隨機(jī) IP 地址;
1、測(cè)試范圍查詢：
IP轉(zhuǎn)成Int，查詢：耗時(shí)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為字符串，查詢：耗時(shí)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出結(jié)果。可認(rèn)為常量索引查詢，性能上無(wú)明顯差異。
3、整理一下結(jié)果發(fā)現(xiàn)：
- 范圍查詢和精確查詢：
數(shù)據(jù)量少的情況下的差距不明顯，如果數(shù)據(jù)量擴(kuò)大到約1千萬(wàn)行或1億行，1億行時(shí)預(yù)計(jì)范圍查詢差距能拉開(kāi)到0.5s。
- 存儲(chǔ)空間節(jié)省:
按1億行算，理論上 varchar 最大15字節(jié)存儲(chǔ)，數(shù)值4個(gè)字節(jié)，大約節(jié)省10字節(jié) *1億 約1G空間。
加上索文件引所占的空間，一個(gè)索引也是能節(jié)省1G。約能節(jié)省2G空間。
總結(jié)
??IP地址數(shù)據(jù)采用整數(shù)(UNSIGNED INT)存儲(chǔ)，在存儲(chǔ)和CPU資源使用上都少于字符串存儲(chǔ)形式;在歧義較大的范圍查詢中，存儲(chǔ)整數(shù)方式無(wú)需關(guān)系范圍中的位數(shù)問(wèn)題，查詢更加直觀方便。
??但整數(shù)存儲(chǔ)需要使用INET_ATON、INET_NTOA等特定函數(shù)處理，可讀性查，函數(shù)也會(huì)消耗額外CPU，經(jīng)檢驗(yàn)發(fā)現(xiàn)CPU開(kāi)支微乎其微。
??因此，需要范圍查詢，且數(shù)據(jù)量很大(如億級(jí)以上)，采用數(shù)值存儲(chǔ)IP地址的方式更優(yōu)。如果均是唯一IP精確查詢，或數(shù)據(jù)量不大，那么使用字符串操作更為簡(jiǎn)單。
