我總是希望找到一些與眾不同的點(diǎn)來(lái)解讀這一類問(wèn)題，結(jié)果在偶然的一天從IP數(shù)據(jù)云這里找到了一些思路。

首先需求在IP數(shù)據(jù)云里面輸入一個(gè)IP地址【https://www.ip66.net/?utm-source=LJ&utm-keyword=?1146】，能夠根據(jù)IP查返回IP對(duì)應(yīng)的區(qū)域，這就是一個(gè)看起來(lái)很簡(jiǎn)單的IP地址定位的問(wèn)題。

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

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

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

問(wèn)題就交代到這里，我今天想再次討論這個(gè)問(wèn)題是想從幾個(gè)基礎(chǔ)的問(wèn)題開始來(lái)聊聊MySQL在這方面的優(yōu)勢(shì)，沒(méi)錯(cuò)，是相比于Oracle的優(yōu)勢(shì)的地方。

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

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

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

比如下面：B1是傳入的IP地址，即一個(gè)字符串，會(huì)先轉(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，有哪些點(diǎn)需要考慮呢。

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

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

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

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

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

因?yàn)槲覀兇蛩闶褂脭?shù)值類型，最后我們選擇了int(11),沒(méi)有留出很富余的值是因?yàn)槲覀儚脑O(shè)計(jì)的角度來(lái)考慮盡可能按需分配。

> 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)格模式。

好吧，看來(lái)我們太過(guò)于樂(lè)觀了。逐個(gè)擊破。

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

Query OK, 1 row affected (0.00 sec)

原來(lái)是這里的問(wèn)題：

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

ERROR 1264 (22003): Out of range value for column 'ip' at row 1

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

> alter table ip_range modify ip int(11) unsigned;

Query OK, 1 row affected (0.02 sec)

Records: 1 Duplicates: 0 Warnings: 0然后再次插入就沒(méi)有問(wèn)題了。

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

Query OK, 1 row affected (0.00 sec)這里需要提一下，就是對(duì)于IP地址的轉(zhuǎn)換，MySQL已經(jīng)提供了這個(gè)轉(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é)合索引信息，實(shí)現(xiàn)這個(gè)需求問(wèn)題不大。