关于ip区间查询sql的写法

最新推荐文章于 2022-11-26 14:37:32 发布

原创最新推荐文章于 2022-11-26 14:37:32 发布 · 837 阅读

3 ·

CC 4.0 BY-SA版权

文章标签：

#SQL #Access #C #C++ #C#

数据库专栏收录该内容

24 篇文章

订阅专栏

针对大量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

实际执行起来速度非常好