转载

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) 有了这些铺垫，结合索引信息，实现这个需求问题不大。

正文到此结束