报表优化-------根据IP地址对比起始IP和结束IP找到相应的地域（判断两个IP地址）

最新推荐文章于 2023-06-29 14:34:20 发布

Teresa7

最新推荐文章于 2023-06-29 14:34:20 发布

阅读量1.5k

点赞数

分类专栏： SQL Tuning

本文链接：https://blog.csdn.net/Angelina7/article/details/73744443

版权

针对大量数据的IP地址匹配问题，原本的函数和拆分关联方法效率低下，可能导致宕机。通过优化，实现了两种高效方案：一种方式耗时32秒，另一种仅需16秒。优化主要涉及自定义函数的改进和不等值连接策略，显著提升了查询速度。

摘要由CSDN通过智能技术生成

这个报表很慢，通过测试发现慢的原因是在关联IP地址库上表较慢，这里需要通过给定的IP地址，对比IP地址起始IP和结束IP范围找到所在区域。

通过自定义函数直接去关联或者是通过拆分对每个值去关联，这两张方式都是十几分钟，如果查询的数据量大的话直接宕机。但是不关联IP地址库的话5秒就出结果。

我最终返回的数据有将近30万的数据，IP地址库有60多万数据。最终通过两种方式处理，方式一：32秒就出数据。方式二：16秒就出数据。

原sql:

select s.ip_address ip , dp.province || dp.city || dp.county  ip_address
  from data_etl.t_statistics s
left outer join dim_ip_class dp
    on(  to_number(regexp_substr(s.ip_address, '[^.]+', 1, 1)) between
       to_number(regexp_substr(dp.ip_start , '[^.]+', 1, 1)) and
       to_number(regexp_substr(dp.ip_end, '[^.]+', 1, 1))
   and to_number(regexp_substr(s.ip_address, '[^.]+', 1, 2)) between
       to_number(regexp_substr(dp.ip_end, '[^.]+', 1, 2)) and
       to_number(regexp_substr(dp.ip_end, '[^.]+', 1, 2))
   and to_number(regexp_substr(s.ip_address, '[^.]