IP地址定位区间的问题分析

以前写过一篇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)

有了这些铺垫,结合索引信息,实现这个需求问题 不大。

原文发布于微信公众号 - 杨建荣的学习笔记(jianrong-notes)

原文发表时间:2017-06-29

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏企鹅号快讯

利用pandas进行数据分析(二):索引与层次化索引

继上一节的基本数据结构的介绍之后,本节继续介绍中操作和的基本手段。一个最常用的操作就是索引,如何根据分析目的对和进行索引访问得到数据是利用进行数据分析的基本技能...

23690
来自专栏数据和云

常与无常:SQL语句中常量的处理及性能差异解析

杨廷琨,网名 yangtingkun 云和恩墨技术总监,Oracle ACE Director,ACOUG 核心专家 在ITPUB论坛上看到一个有意思的问题:两...

34490
来自专栏维C果糖

史上最简单的 MySQL 教程(三十二)「子查询(下)」

子查询:sub query,查询是在某个查询结果之上进行的,一条select语句内部包含了另外一条select语句。

40260
来自专栏Java架构

前大众点评资深研发专家对Mysql索引的解析与底层数据结构的解刨

mysql索引: 是一种帮助mysql高效的获取数据的数据结构,这些数据结构以某种方式引用数据,这种结构就是索引。可简单理解为排好序的快速查找数据结构。如果要查...

22440
来自专栏腾讯云流计算

Apache Calcite 功能简析及在 Flink 的应用

• Apache Calcite 是一个动态数据的管理框架,可以用来构建数据库系统的语法解析模块

1.6K70
来自专栏YG小书屋

ES中文分词器之精确短语匹配(解决了match_phrase匹配不全的问题)

49040
来自专栏专注研发

单例模式三种模式,饿汉(饥汉)、饱汉(懒汉)、双重锁模式

今天看某培训机构提供面试题中设计模式部分,对饱汉和饿汉比较感兴趣,就搜了一下,然后误人子弟,然后就觉得有必要记录一下

23810
来自专栏JetpropelledSnake

Django学习笔记之ORM多表操作

作者详细模型:把作者的详情放到详情表,包含生日,手机号,家庭住址等信息。作者详情模型和作者模型之间是一对一的关系(one-to-one)

29040
来自专栏mukekeheart的iOS之旅

数据库操作要点知识整理

第一课:客户端         1. Sql Plus(客户端),命令行直接输入:sqlplus,然后按提示输入用户名,密码。         2. 从开始...

24190
来自专栏AhDung

【SQL】分享表值函数FMakeRows,用于生成行

评论中又有一位【笑东风】兄给出改善建议,在此先感谢他。原理是借助行数较多的一个系统视图sys.all_columns与自身做cross join,以得到大量现成...

9630

扫码关注云+社区

领取腾讯云代金券