ip地址段查询深度优化案例详解

本文涉及的产品
云原生数据库 PolarDB MySQL 版,通用型 2核4GB 50GB
云原生数据库 PolarDB PostgreSQL 版,标准版 2核4GB 50GB
简介: 如何在庞大的ip地址库中快速找到某个IP地址的归属地?本文比较几种不同优化方法。

问题

某日,研发的小伙伴扔过来一个SQL希望帮忙优化。

select nation,province,city 
from ip_idc 
where ip_start <='113.201.214.203'::inet and 
        ip_end >='113.201.214.203'::inet;

这个SQL需要执行1秒多。而业务需要高并发的频繁执行这条SQL,1秒多的执行时间无法满足业务需求。

这个SQL是想在ip地址库中找到某个IP地址的归属地。ip地址库的表定义如下,每条记录描述了一个地址范围的相关信息,共300多万条记录,600MB。

postgres=# \d ip_idc
                            Table "public.ip_idc"
       Column       |          Type          | Collation | Nullable | Default 
--------------------+------------------------+-----------+----------+---------
 id                 | character varying(255) |           | not null | 
 ip_start           | inet                   |           |          | 
 ip_end             | inet                   |           |          | 
 nation             | character varying(255) |           |          | 
 province           | character varying(255) |           |          | 
 city               | character varying(255) |           |          | 
 ...(略)
Indexes:
    "ip_idc_pkey" PRIMARY KEY, btree (id)
    "ip_idc_ip_start_ip_end_idx" btree (ip_start, ip_end)

分析

通过检查执行计划,可以很明显看出,这个SQL之所以慢,是因为它使用了全表扫描。

postgres=# explain (analyze ,buffers)select nation,province,city from ip_idc where ip_start <='113.201.214.203'::inet and ip_end >='113.201.214.203'::inet;
                                                   QUERY PLAN                                                    
-----------------------------------------------------------------------------------------------------------------
 Seq Scan on ip_idc  (cost=0.00..128204.08 rows=645500 width=29) (actual time=1062.045..1066.116 rows=1 loops=1)
   Filter: ((ip_start <= '113.201.214.203'::inet) AND (ip_end >= '113.201.214.203'::inet))
   Rows Removed by Filter: 3470145
   Buffers: shared hit=4143 read=72010
   I/O Timings: read=321.196
 Planning time: 17.140 ms
 Execution time: 1073.907 ms
(7 rows)

明明表上有索引,为什么还会走全表扫描?

对于btree索引上的范围查询,这其实是一个很正常的现象。

对于联合索引btree(ip_start, ip_end),起决定作用的是开头的ip_start字段。
这个SQL中,ip_start字段上的约束条件是ip_start <= '113.201.214.203'::inet,
即它需要扫描索引中所有小于等于113.201.214.203'::inet的索引项。
这样的索引项的数量越多,执行时间就越长;同时优化器计算出的索引扫描的cost也就越大,大到超过顺序扫描的cost后,优化器就会选择使用顺序扫描的执行计划了。

使用"更小"的ip地址继续查询,可以验证上面的描述。
使用60,10,1开头的IP地址查询,都会走索引扫描,查询时间分别是210毫秒,3毫秒和0.4毫秒。

postgres=# explain (analyze ,buffers)select nation,province,city from ip_idc where ip_start <='60.201.214.203'::inet and ip_end >='60.201.214.203'::inet;
                                                                   QUERY PLAN                                                                    
-------------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using ip_idc_ip_start_ip_end_idx on ip_idc  (cost=0.43..77943.75 rows=640787 width=29) (actual time=104.813..104.815 rows=1 loops=1)
   Index Cond: ((ip_start <= '60.201.214.203'::inet) AND (ip_end >= '60.201.214.203'::inet))
   Buffers: shared hit=3240
 Planning time: 0.082 ms
 Execution time: 104.843 ms
(5 rows)

postgres=# explain (analyze ,buffers)select nation,province,city from ip_idc where ip_start <='10.201.214.203'::inet and ip_end >='10.201.214.203'::inet;
                                                                 QUERY PLAN                                                                 
--------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using ip_idc_ip_start_ip_end_idx on ip_idc  (cost=0.43..17828.34 rows=30263 width=29) (actual time=2.298..2.299 rows=1 loops=1)
   Index Cond: ((ip_start <= '10.201.214.203'::inet) AND (ip_end >= '10.201.214.203'::inet))
   Buffers: shared hit=70
 Planning time: 0.156 ms
 Execution time: 2.327 ms
(5 rows)
postgres=# explain (analyze ,buffers)select nation,province,city from ip_idc where ip_start <='1.201.214.203'::inet and ip_end >='1.201.214.203'::inet;
                                                                QUERY PLAN                                                                
------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using ip_idc_ip_start_ip_end_idx on ip_idc  (cost=0.43..3426.17 rows=5057 width=29) (actual time=0.392..0.393 rows=1 loops=1)
   Index Cond: ((ip_start <= '1.201.214.203'::inet) AND (ip_end >= '1.201.214.203'::inet))
   Buffers: shared hit=15
 Planning time: 0.103 ms
 Execution time: 0.413 ms
(5 rows)

测试结果汇总如下:

目标IP地址 扫描类型 扫描数据块数 执行时间(ms)
113.201.214.203 顺序扫描 76153 1073.907
60.201.214.203 索引扫描 3240 104.843
10.201.214.203 索引扫描 70 2.327
1.201.214.203 索引扫描 15 0.413

查询条件中虽然有ip_end >='1.201.214.203'::inet的约束条件,但由于ip_end是联合索引的第二个字段,难以发挥作用。

因此,本问题的根因在于btree索引不擅长处理范围类型。

优化方案1

熟悉PostgreSQL的人应该都知道,PostgreSQL里有非常丰富的数据类型,其中就包含范围类型。
利用与之配套的gist索引,可以在索引中同时搜索范围的上下边界,达到比较理想的查询效果。
下面实际验证一下效果。

由于inet范围不是内置类型,先创建一个inet的范围类型。

create type inetrange as range(subtype=inet)

为了不修改表结构,创建inet范围的表达式索引

create index on ip_idc using gist(inetrange(ip_start, ip_end, '[]'::text))

执行查询

postgres=# explain (analyze,buffers) select * from ip_idc where inetrange(ip_start,ip_end,'[]') @> '113.21.214.203'::inet;
                                                          QUERY PLAN                                                           
-------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on ip_idc  (cost=1.92..3.44 rows=1 width=136) (actual time=11.071..11.072 rows=1 loops=1)
   Recheck Cond: (inetrange(ip_start, ip_end, '[]'::text) @> '113.21.214.203'::inet)
   Heap Blocks: exact=1
   Buffers: shared hit=521
   ->  Bitmap Index Scan on ip_idc_inetrange_idx  (cost=0.00..1.92 rows=1 width=0) (actual time=11.066..11.066 rows=1 loops=1)
         Index Cond: (inetrange(ip_start, ip_end, '[]'::text) @> '113.21.214.203'::inet)
         Buffers: shared hit=520
 Planning time: 0.098 ms
 Execution time: 11.140 ms
(9 rows)

使用inet范围索引后,执行时间减少到了11毫秒。
仔细检查这个执行计划,发现这个SQL扫描了520个索引块,似乎有点多,有兴趣的同学可以看看从内核源码角度能不能再优化一下。

注:相同的SQL在9.6上执行时间是300毫秒,需要扫描13435个索引块。应该PG 10对gist索引做过优化。

优化方案2

除了自定义的inetrange类型,inet本身就有表示地址范围的能力,并且支持gist索引。另外还有更高效的第三方的ip4r插件可以做同样的事情。
下面比较一下这3种方式在同一个数据集下的性能。

先创建包含3种数据类型的表,并生成300多万不重复的ip范围

create extension ip4r;
create table ip_idc2(id serial,ip_start inet,ip_end inet,iprange inet,iprange2 ip4r);
insert into ip_idc2(ip_start,ip_end,iprange,iprange2)
    select (r1::text ||'.'|| r2::text ||'.'|| r3::text||'.0')::inet, 
        (r1::text ||'.'|| r2::text ||'.'|| r3::text||'.255')::inet,
        (r1::text ||'.'|| r2::text ||'.'|| r3::text||'.0/24')::inet,
        (r1::text ||'.'|| r2::text ||'.'|| r3::text||'.0/24')::ip4r
    from generate_series(1,60) a(r1),generate_series(1,254) b(r2),generate_series(1,254) c(r3) limit 1;

create index on ip_idc2 using gist(inetrange(ip_start, ip_end, '[]'::text));
create index on ip_idc2 using gist(iprange inet_ops);
create index on ip_idc2 using gist(iprange2);

执行ip地址查询的SQL,比较3种的类型的索引扫描效率。

postgres=# explain (analyze,buffers) select * from ip_idc2 where inetrange(ip_start,ip_end,'[]') @> '33.21.214.203'::inet;
                                                              QUERY PLAN                                                              
--------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on ip_idc2  (cost=384.42..17950.58 rows=19355 width=33) (actual time=15.131..15.133 rows=1 loops=1)
   Recheck Cond: (inetrange(ip_start, ip_end, '[]'::text) @> '33.21.214.203'::inet)
   Heap Blocks: exact=1
   Buffers: shared hit=668
   ->  Bitmap Index Scan on ip_idc2_inetrange_idx  (cost=0.00..379.58 rows=19355 width=0) (actual time=15.122..15.122 rows=1 loops=1)
         Index Cond: (inetrange(ip_start, ip_end, '[]'::text) @> '33.21.214.203'::inet)
         Buffers: shared hit=667
 Planning time: 0.072 ms
 Execution time: 15.204 ms
(9 rows)

postgres=# explain (analyze,buffers) select * from ip_idc2 where iprange >>= '33.21.214.203'::inet;
                                                          QUERY PLAN                                                          
------------------------------------------------------------------------------------------------------------------------------
 Index Scan using ip_idc2_iprange_idx on ip_idc2  (cost=0.41..3.43 rows=1 width=33) (actual time=2.758..4.940 rows=1 loops=1)
   Index Cond: (iprange >>= '33.21.214.203'::inet)
   Buffers: shared hit=227
 Planning time: 0.082 ms
 Execution time: 4.964 ms
(5 rows)

postgres=# explain (analyze,buffers) select * from ip_idc2 where iprange2 >>= '33.21.214.203'::ip4r;
                                                           QUERY PLAN                                                            
---------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on ip_idc2  (cost=54.42..4966.41 rows=3871 width=33) (actual time=0.032..0.032 rows=1 loops=1)
   Recheck Cond: (iprange2 >>= '33.21.214.203'::ip4r)
   Heap Blocks: exact=1
   Buffers: shared hit=4
   ->  Bitmap Index Scan on ip_idc2_iprange2_idx  (cost=0.00..53.45 rows=3871 width=0) (actual time=0.027..0.027 rows=1 loops=1)
         Index Cond: (iprange2 >>= '33.21.214.203'::ip4r)
         Buffers: shared hit=3
 Planning time: 0.083 ms
 Execution time: 0.065 ms
(9 rows)

测试结果汇总如下

数据类型 Where条件 扫描类型 扫描索引块数 执行时间(ms)
inet范围类型 inetrange(ip_start,ip_end,'[]') @> '33.21.214.203'::inet 索引扫描 667 15.204
inet iprange >>= '33.21.214.203'::inet; 索引扫描 227 4.964
ip4r iprange2 >>= '33.21.214.203'::ip4r 索引扫描 3 0.065

从测试结果可以看出,原生的inet比自定义的inetrange快了3倍。
而第三方的ip4r又比原生的inet快了76倍,执行时间只有0.065毫秒,执行过程中只扫描了3个索引块,可以认为已经优化到头了。

优化方案3

方案2中的ip4r的性能虽然比较理想,但是需要对现有ip地址库的表数据重新定义,而且还需要安装额外的第三方插件,实施代价比较高。

有没有性能和ip4r相当,又不需要修改表数据以及安装第三方插件的方案呢?

经过和业务方沟通,了解到ip地址库中的地址范围没有重叠,并且ip地址库数据齐全,没有ip遗漏。
也就是说,查询ip地址的SQL一定会返回且只返回一条记录。
既然这样,简单思考一下就不难发现:


ip_start小于等于目标IP的最大的地址范围其实就是要找的记录。

这样的查询只需在SQL上简单的添加 order by + limit 1就可以完成优化。效果如下

在ip_start字段上创建btree索引

create index on ip_idc2(ip_start);

当然也可以继续使用现有的btree(ip_start,ip_end)联合索引。

执行SQL

postgres=# explain (analyze ,buffers)select * from ip_idc2 where ip_start <='33.201.214.203'::inet and ip_end >='33.201.214.203'::inet order by ip_start desc limit 1;
                                                                      QUERY PLAN                                                                    
   
----------------------------------------------------------------------------------------------------------------------------------------------------
---
 Limit  (cost=0.43..0.53 rows=1 width=33) (actual time=0.019..0.020 rows=1 loops=1)
   Buffers: shared hit=4
   ->  Index Scan Backward using ip_idc2_ip_start_idx on ip_idc2  (cost=0.43..99888.39 rows=957385 width=33) (actual time=0.018..0.018 rows=1 loops=
1)
         Index Cond: (ip_start <= '33.201.214.203'::inet)
         Filter: (ip_end >= '33.201.214.203'::inet)
         Buffers: shared hit=4
 Planning time: 0.133 ms
 Execution time: 0.044 ms
(8 rows)

优化后,只扫描4个索引块,执行速度比ip4r还快50%,效果符合预期。

小结

对于IP地址段查询的场景,PostgreSQL的ip4r插件是一个性能和通用性都比较不错的一个方案,
但用户不一定方便使用ip4r,比如在未安装ip4r的公有云RDS上或者使用PostgreSQL以外的数据库。

在能满足以下限制条件的情况下,方案3(即order by + limit 1)应该是更好的选择。

  1. ip地址库中的ip地址范围不能重叠,否则原本应该返回多条记录的结果只能查到第1条记录。
  2. ip地址库中的ip地址范围齐全,不能有遗漏,否则可能需要扫描一半的索引,性能很差。

最终,业务采纳了方案3。

相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
相关文章
|
算法 JavaScript Java
使用强大的离线IP地址定位库ip2region获取城市信息
ip2region - 准确率99.9%的离线IP地址定位库,0.0x毫秒级查询,ip2region.db数据库只有数MB,提供了java、php、c、python、nodejs、golang、c#等查询绑定和Binary,B树,内存三种查询算法。
使用强大的离线IP地址定位库ip2region获取城市信息
|
域名解析 SEO 搜索推荐
网络基础知识之————A记录和CNAME记录的区别
1、什么是域名解析? 域名解析就是国际域名或者国内域名以及中文域名等域名申请后做的到IP地址的转换过程。IP地址是网路上标识您站点的数字地址,为了简单好记,采用域名来代替ip地址标识站点地址。域名的解析工作由DNS服务器完成。
9219 1
|
1月前
|
网络安全 Python
Python编程--目标IP地址段主机指定端口状态扫描
Python编程--目标IP地址段主机指定端口状态扫描
|
3月前
这几个IP地址块聚合后,可用地址数是多少?你造吗?
这几个IP地址块聚合后,可用地址数是多少?你造吗?
|
4月前
|
运维 关系型数据库 Serverless
函数计算产品使用问题之如何在日志中打印出你本地机器的IP地址
阿里云Serverless 应用引擎(SAE)提供了完整的微服务应用生命周期管理能力,包括应用部署、服务治理、开发运维、资源管理等功能,并通过扩展功能支持多环境管理、API Gateway、事件驱动等高级应用场景,帮助企业快速构建、部署、运维和扩展微服务架构,实现Serverless化的应用部署与运维模式。以下是对SAE产品使用合集的概述,包括应用管理、服务治理、开发运维、资源管理等方面。
|
6月前
|
Go
信息收集 -- ip段整理
信息收集 -- ip段整理
28 2
|
存储 域名解析 缓存
2023-6-13-IP配置知识补充学习
2023-6-13-IP配置知识补充学习
196 0
|
安全 网络安全 CDN
ip查询显示泛播是什么意思?
好奇openai的请求是如何转发的。
9217 0
|
数据采集 消息中间件 分布式计算
爬虫识别-IP 段统计-需求及思路|学习笔记
快速学习爬虫识别-IP 段统计-需求及思路。
129 0
爬虫识别-IP 段统计-需求及思路|学习笔记
|
数据采集 分布式计算 大数据
爬虫识别-IP 段统计-代码实现及效果|学习笔记
快速学习爬虫识别-IP 段统计-代码实现及效果。
爬虫识别-IP 段统计-代码实现及效果|学习笔记