关于ip区间查询sql的写法

针对大量IP地址数据的查询效率问题,本文通过调整SQL查询语句及利用索引优化技术,显著提升了查询速度。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

我们在项目中遇到了这样的一个问题,要查询ip对应的地址,采用的是google的地图库,

其中我们把地图库导入了数据库,构造了一个这样的表

-- Create table
create table T_GGMAP_IP
(
  IPSTART NUMBER not null,
  IPEND   NUMBER not null,
  LOCID   NUMBER,
  COMPANY VARCHAR2(200)
)
tablespace SKYEYE
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate primary, unique and foreign key constraints 
alter table T_GGMAP_IP
  add constraint PK_GGMAP primary key (IPSTART, IPEND)
  using index 
  tablespace SKYEYE
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate indexes 
create unique index IPEND on T_GGMAP_IP (IPEND)
  tablespace SKYEYE
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
create unique index IPSTART on T_GGMAP_IP (IPSTART)
  tablespace SKYEYE
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );

 IPSTART NUMBER ---是ip段开始的数字表示形式,即为a.b.c.d转化为 a*255*255*255+b*255*255+c*255*255+d*255
  IPEND   NUMBER not null, ip段的截止数字表示形式
  LOCID   NUMBER,   ip段对应地址的编号
  COMPANY VARCHAR2(200)

 

如果我们要查询某个ip属于那个地址无非是这样

select * from t_GGMAP_IP t where t.ipstart<=query_ip('202.117.0.20') and t.ipend>=query_ip('202.117.0.20') 

---query_ip是自定义的一个转化ip为数字的函数(a*255*255*255+b*255*255+c*255*255+d*255

 查询执行计划发现

SELECT STATEMENT, GOAL = ALL_ROWS   191 10171 233933
 TABLE ACCESS BY INDEX ROWID YGUO T_GGMAP_IP 191 10171 233933
  INDEX RANGE SCAN YGUO PK_GGMAP 184 330 

 

虽然用到了INDEX RANGE SCAN  PK_GGMAP 但实际执行速度非常慢。T_GGMAP_IP表中大概有400W数据

 所以改进型的sql不应该使用PK_GGMAP的RANGE SCAN ,最好能直接使用INDEX UNIQUE SCAN

 

所以sql可以改造

select t2.* from (select max(t.ipstart) ipst from t_GGMAP_IP t where t.ipstart<=query_ip('202.117.0.20')) t1,t_GGMAP_IP t2 where  t1.ipst=t2.ipstart and t2.ipend>=query_ip('202.117.0.20')

 为

 执行计划为

SELECT STATEMENT, GOAL = ALL_ROWS   5 1 36
 NESTED LOOPS   5 1 36
  VIEW YGUO  3 1 13
   SORT AGGREGATE    1 7
    FIRST ROW   3 203411 1423877
     INDEX RANGE SCAN (MIN/MAX) YGUO IPSTART 3 203411 1423877
  TABLE ACCESS BY INDEX ROWID YGUO T_GGMAP_IP 2 1 23
   INDEX UNIQUE SCAN YGUO IPSTART 1 1 

 

 实际执行起来速度非常好

 

 

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值