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

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

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

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

    需求是輸入一個IP，能夠根據IP從一個資料字典表裡查詢IP區段，返回IP對應的區域，這就是一個看起來很簡單的IP地址定位的問題。

   從系統負載方面，CPU的負載較高，而其中很大的一方面代價就是IP地址和數字（IP地址轉換為數字）之間的轉換和對映。

   Buffer Gets指標極高，這個部分其實和整個語句的查取效果有關，如果沒有找到匹配的資料，就會掃描更多的塊。這個部分一個立竿見影的效果就是使用rownum的方式來截斷，在這個基礎上，和Oracle的朋友聊，其實也有一些改進措施的，這個部分對於極限最佳化來說可以參考，所以暫且放一放。

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

   問題就交代到這裡，我今天想再次討論這個問題是想從幾個基礎的問題開始來聊聊MySQL在這方面的優勢，沒錯，是相比於Oracle的優勢的地方。

    首先我們來說說表結構的設計，如果在Oracle裡面，當時設計的地址資訊如下：

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地址轉換後的數字都做了持久化，查詢的邏輯相對就比較彆扭了。

比如下面：B1是傳入的IP地址，即一個字串，會先轉換為數字，然後做Range Scan。

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

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

第一個考慮點還是資料型別，IP地址是一個字串，我們是考慮使用varchar型別還是char呢。

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

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

   那麼問題來了，數值型資料型別其實是很豐富的，這一點和Oracle大大不同，Oracle裡面很多開發，DBA都懶了，或者說Oracle內部已經做好了這種適配，數值精度也不需要更多考慮了，長度也不需要區別對待了，直接一個number型別，想調精度，就直接在這個基礎上改，比如number(10,3),可以定義長度和精度。MySQL在這方面就分得比較輕，有支援0-128以內的tiny int,32767的smallint等，每一個資料型別都摳的很細。

   所以在Oracle裡面的豪氣在這裡就是粗放了，一定需要認真區別對待。

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

> 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結果發現竟然溢位了,SQL_Mode是嚴格模式。

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

> 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的數值型別其實分為有符號和無符號兩種，區間分別是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地址的轉換，MySQL已經提供了這個轉換的方法，可以互相轉換。分別是inet_ntoa（數值轉為IP）,inete_aton(IP轉為數值）

> 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) 有了這些鋪墊，結合索引資訊，實現這個需求問題 不大。