以前寫過一篇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) 有了這些鋪墊，結合索引信息，實現這個需求問題 不大。

當前文章：IP地址定位區間的問題分析(r13筆記第9天)
文章轉載：http://www.js-pz168.com/article24/pojdce.html

成都網站建設公司_創新互聯，為您提供全網營銷推廣、、移動網站建設、定制網站、網站內鏈、域名注冊

廣告

聲明：本網站發布的內容（圖片、視頻和文字）以用戶投稿、用戶轉載內容為主，如果涉及侵權請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網站立場，如需處理請聯系客服。電話：028-86922220；郵箱：631063699@qq.com。內容未經允許不得轉載，或轉載時需注明來源： 創新互聯