IP地址定位區(qū)間的問題分析(r13筆記第9天)

   以前寫過一篇Oracle中關(guān)于IP地址定位的問題分析，最后引申出了一系列的問題。當(dāng)時問題緊急嚴(yán)峻，抓取了10053事件定位源頭，想出了一個解決妙法，還自鳴得意了下，結(jié)果忙活完之后看看行業(yè)里的解決方案都大體如此，我的心涼了半截。

   我總是希望找到一些與眾不同的點來解讀這一類問題，結(jié)果在偶然的一天從MySQL這里找到了一些思路。

    我先來分析下之前問題和一些收獲。

    需求是輸入一個IP，能夠根據(jù)IP從一個數(shù)據(jù)字典表里查詢IP區(qū)段，返回IP對應(yīng)的區(qū)域，這就是一個看起來很簡單的IP地址定位的問題。

   從系統(tǒng)負(fù)載方面，CPU的負(fù)載較高，而其中很大的一方面代價就是IP地址和數(shù)字（IP地址轉(zhuǎn)換為數(shù)字）之間的轉(zhuǎn)換和映射。

   Buffer Gets指標(biāo)極高，這個部分其實和整個語句的查取效果有關(guān)，如果沒有找到匹配的數(shù)據(jù)，就會掃描更多的塊。這個部分一個立竿見影的效果就是使用rownum的方式來截斷，在這個基礎(chǔ)上，和Oracle的朋友聊，其實也有一些改進(jìn)措施的，這個部分對于極限優(yōu)化來說可以參考，所以暫且放一放。

  從索引的角度來考慮，Range Scan的方式總是會有優(yōu)點和缺點，不可能把它同時結(jié)合起來達(dá)到一個最優(yōu)的效果，換做那一個數(shù)據(jù)庫都是如此，只能說有些回表的數(shù)據(jù)處理Oracle隱式（比如使用rowid)）做好了，而MySQL里面可能需要單獨處理。

   問題就交代到這里，我今天想再次討論這個問題是想從幾個基礎(chǔ)的問題開始來聊聊MySQL在這方面的優(yōu)勢，沒錯，是相比于Oracle的優(yōu)勢的地方。

    首先我們來說說表結(jié)構(gòu)的設(shè)計，如果在Oracle里面，當(dāng)時設(shè)計的地址信息如下：

COLUMN_ID COLUMN_NAME                    DATA_TYPE       DATA_LENGTH NULLABLE  
---------- ------------------------------ --------------- ----------- ----------
         1 IP_ID                          NUMBER(10,0)             22 N
         2 IP_LEFT_LINE                   VARCHAR2(15)             15 N
         3 IP_RIGHT_LINE                  VARCHAR2(15)             15 N
         4 IP2NUM_LEFT_LINE               NUMBER(10,0)             22 N
         5 IP2NUM_RIGHT_LINE              NUMBER(10,0)             22 N
         6 COUNTRY                        VARCHAR2(20)             20 Y
         7 PROVINCE                       VARCHAR2(20)             20 Y
         8 CAPITAL                        VARCHAR2(20)             20 Y

  里面對IP地址和IP地址轉(zhuǎn)換后的數(shù)字都做了持久化，查詢的邏輯相對就比較別扭了。

比如下面：B1是傳入的IP地址，即一個字符串，會先轉(zhuǎn)換為數(shù)字，然后做Range Scan。

SELECT IP_ID,COUNTRY,PROVINCE,CAPITAL
FROM SWD_IP2COUNTY
WHERE STRIPTOINT(:B1 ) BETWEEN IP2NUM_LEFT_LINE AND IP2NUM_RIGHT_LINE

如果換做MySQL，有哪些點需要考慮呢。

第一個考慮點還是數(shù)據(jù)類型，IP地址是一個字符串，我們是考慮使用varchar類型還是char呢。

  假設(shè)一個IP地址為10.127.133.199，字符串的長度就是14位，最高設(shè)置為3*4+3=15位，這是第一點。

  而如果我們存儲了一個IP，則意味著這個工作還沒有完成，我們還需要轉(zhuǎn)換，所以還不如直接轉(zhuǎn)換為數(shù)值，所以綜合起來，其實我們實現(xiàn)這個需求，從簡化的角度來看，其實不需要一個字符型，而是需要一個數(shù)值型即可。

   那么問題來了，數(shù)值型數(shù)據(jù)類型其實是很豐富的，這一點和Oracle大大不同，Oracle里面很多開發(fā)，DBA都懶了，或者說Oracle內(nèi)部已經(jīng)做好了這種適配，數(shù)值精度也不需要更多考慮了，長度也不需要區(qū)別對待了，直接一個number類型，想調(diào)精度，就直接在這個基礎(chǔ)上改，比如number(10,3),可以定義長度和精度。MySQL在這方面就分得比較輕，有支持0-128以內(nèi)的tiny int,32767的smallint等，每一個數(shù)據(jù)類型都摳的很細(xì)。

   所以在Oracle里面的豪氣在這里就是粗放了，一定需要認(rèn)真區(qū)別對待。

   因為我們打算使用數(shù)值類型，最后我們選擇了int(11),沒有留出很富余的值是因為我們從設(shè)計的角度來考慮盡可能按需分配。

> create table ip_range(ip int(11) );
Query OK, 0 rows affected (0.01 sec)

我們插入兩行值：

> insert into ip_range values(inet_aton('127.0.0.1')),(inet_aton('192.168.1.1'));
ERROR 1264 (22003): Out of range value for column 'ip' at row 2結(jié)果發(fā)現(xiàn)竟然溢出了,SQL_Mode是嚴(yán)格模式。

好吧，看來我們太過于樂觀了。逐個擊破。

> insert into ip_range values(inet_aton('127.0.0.1'));               

Query OK, 1 row affected (0.00 sec)

原來是這里的問題：

> insert into ip_range values(inet_aton('192.168.1.1'));
ERROR 1264 (22003): Out of range value for column 'ip' at row 1

這是因為int的數(shù)值類型其實分為有符號和無符號兩種，區(qū)間分別是2147483647和4294967295，所以IP地址的需求我們只需要考慮無符號的情況，修改字段類型。

> alter table ip_range modify ip int(11) unsigned;
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0然后再次插入就沒有問題了。

> insert into ip_range values(inet_aton('192.168.1.1'));
Query OK, 1 row affected (0.00 sec)這里需要提一下，就是對于IP地址的轉(zhuǎn)換，MySQL已經(jīng)提供了這個轉(zhuǎn)換的方法，可以互相轉(zhuǎn)換。分別是inet_ntoa（數(shù)值轉(zhuǎn)為IP）,inete_aton(IP轉(zhuǎn)為數(shù)值）

> select (inet_ntoa(ip)) from ip_range;
+-----------------+
| (inet_ntoa(ip)) |
+-----------------+
| 127.0.0.1       |
| 192.168.1.1     |
+-----------------+
2 rows in set (0.00 sec) 有了這些鋪墊，結(jié)合索引信息，實現(xiàn)這個需求問題 不大。