ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > > > IP地址段查询深度优化案例

IP地址段查询深度优化案例

作者：wangliyun 时间：2020-03-12 17:12:31 0 删除 编辑

作者介绍

陈华军，苏宁IT总部架构专家，PostgreSQL及MySQL产品技术负责人，PostgreSQL中文社区核心成员。

问题

某日，研发的小伙伴扔过来一个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地址）继续查询，可以验证上面的描述。
     
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=3240Planning time: 0.082 msExecution 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=70Planning time: 0.156 msExecution 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=15Planning time: 0.103 msExecution 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=1Buffers: 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=520Planning time: 0.098 msExecution 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')::ip4rfrom 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。



来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/69917105/viewspace-3150559/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： PostgreSQL查询语句优化
下一篇： 【精彩视频】管理、开发规约（上）
请登录后发表评论 登录
全部评论
wangliyun

注册时间：2019-04-19

  • 博文量
    213
  • 访问量
    315

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593