作者
digoal
日期
2023-08-28
标签
PostgreSQL , PolarDB , 数据库 , 教学
背景
欢迎数据库应用开发者参与贡献场景, 在此issue回复即可, 共同建设《沉浸式数据库学习教学素材库》, 帮助开发者用好数据库, 提升开发者职业竞争力, 同时为企业降本提效.
- 系列课程的核心目标是教大家怎么用好数据库, 而不是怎么运维管理数据库、怎么开发数据库内核. 所以面向的对象是数据库的用户、应用开发者、应用架构师、数据库厂商的产品经理、售前售后专家等角色.
本文的实验可以使用永久免费的阿里云云起实验室来完成.
如果你本地有docker环境也可以把镜像拉到本地来做实验:
x86_64机器使用以下docker image:
ARM机器使用以下docker image:
业务场景1 介绍: 移动社交、多媒体、内容分发、游戏业务场景, 跨地域多机房的智能加速
在移动社交、多媒体、内容分发业务场景中, 如果用户要交互的内容都在中心网络(假设深圳), 现在用户流动非常频繁, 当用户从深圳出差到北京, 因为网络延迟急剧增加, 他的访问体验就会变得非常差.
网络延迟对游戏业务的影响则更加严重.
为了解决这个问题, 企业会将业务部署在全国各地, 不管用户在哪里出差, 他都可以就近访问最近的中心.
由于标记用户的只有IP地址, 怎么根据用户的接入IP来判断他应该访问哪个中心呢? 通过这个实验, 大家可以了解到在数据库中如何存储IP地址范围和各中心IDC的映射关系, 以及如何根据用户的来源IP(接入IP)来判断他应该去哪个中心IDC访问.
实现和对照
1、设计一张IDC元数据表, 存储IDC的基本信息.
create table tbl_idc ( id int primary key, -- 主键 info text -- 随便输入一些信息, 这个字段和这个实验无关, 不影响这个实验的核心目标 ); -- 写入100个IDC数据 insert into tbl_idc select generate_series(1,100), md5(random()::text);
2、下载(或购买)IP地址库.
参考这篇文章的方法, 得到一张IP地址库表. 《如何获得IP地址对应的地理信息库, 实现智能DNS解析? 就近路由提升全球化部署业务的访问性能》
su - postgres curl http://ftp.apnic.net/apnic/stats/apnic/delegated-apnic-latest -o src.html grep "^apnic|CN|ipv4" src.html > ./cn.csv ./duckdb -c "copy (select column3, (32-log2(column4::int))::int from '/var/lib/postgresql/cn.csv') to '/var/lib/postgresql/ip2region.csv' with (delimiter '/')"
vi c.sh #!/bin/bash for line in $(cat /var/lib/postgresql/ip2region.csv) do # echo $line echo "${line},`curl -s http://whois.pconline.com.cn/ip.jsp?ip=${line}|iconv -f GBK -t UTF8|grep -v "^$"|awk '{print $1","$2}'`" done
. ./c.sh >./c.csv
得到类似这样的信息:
43.226.64.0/22,辽宁省大连市,电信 43.226.68.0/22,辽宁省大连市,电信 43.226.72.0/22,辽宁省大连市,电信 43.226.76.0/22,辽宁省大连市,电信 43.226.80.0/22,辽宁省大连市,电信 43.226.84.0/22,辽宁省大连市,电信 43.226.88.0/22,辽宁省大连市,电信 43.226.92.0/22,辽宁省大连市,电信 43.226.96.0/22,江苏省常州市,电信 43.226.100.0/22,江苏省常州市,电信 43.226.104.0/22,江苏省常州市,电信 43.226.108.0/22,江苏省常州市,电信
导入postgresql|polardb
psql create table tbl_iprange ( id serial primary key, -- 主键 ipr inet, -- ip地址段 loc text, -- 物理地址 provider text -- 运营商 ); copy tbl_iprange(ipr,loc,provider) from '/var/lib/postgresql/c.csv';
3、根据IP地址库的地理位置信息, 整理维护一份属于自己的IP地址范围对应IDC的表.
create table tbl_idc_iprange ( -- IP地址范围对应IDC的表 id int primary key, -- 主键 ipr inet, -- ip地址段 loc text, -- 物理地址 provider text, -- 运营商 idcid int references tbl_idc(id) -- idc id, 随便输入, 不影响测试 ); insert into tbl_idc_iprange select *, ceil(random()*100) from tbl_iprange;
4、根据用户的来源IP(移动端接入地址通常是基站IP)
本实验会使用三种方法来实现.
传统方法 设计和实验
传统数据库没有IP类型, 也没有iprange类型, 所以需要将IP地址段拆成2个int8字段来存储IP地址段的开始和结束地址.
create table tbl_idc_iprange_int8 ( id int primary key, -- 主键 ipstart int8, -- ip地址段开始地址 ipend int8, -- ip地址段结束地址 loc text, -- 物理地址 provider text, -- 运营商 idcid int references tbl_idc(id) -- idc id, 随便输入, 不影响测试 );
创建一个函数, 将inet类型转换为int8类型
create or replace function ip2int8 (inet) returns int8 as $$ select ('b'|| split_part($1::text,'.',1)::int::bit(8)::text|| split_part($1::text,'.',2)::int::bit(8)::text|| split_part($1::text,'.',3)::int::bit(8)::text|| (split_part(split_part($1::text,'.',4),'/',1))::int::bit(8)::text)::bit(32)::int8; $$ language sql strict;
将ip开始和结束值转换为int8 , 写入测试表.
insert into tbl_idc_iprange_int8 select id, ip2int8(network(ipr)), ip2int8(broadcast(ipr)), loc, provider, idcid from tbl_idc_iprange;
创建索引
create index on tbl_idc_iprange_int8 (ipstart, ipend);
根据用户的来源IP(移动端接入地址通常是基站IP). 假设来源来源ip是202.101.172.35 : 3395660835
select * from tbl_idc_iprange_int8 where ipstart < 3395660835 and ipend > 3395660835; id | ipstart | ipend | loc | provider | idcid ------+------------+------------+-----+----------+------- 6924 | 3395649536 | 3395665919 | | | 4 (1 row) postgres=# explain analyze select * from tbl_idc_iprange_int8 where ipstart < 3395660835 and ipend > 3395660835; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------------------- Index Scan using tbl_idc_iprange_int8_ipstart_ipend_idx on tbl_idc_iprange_int8 (cost=0.28..85.71 rows=1 width=88) (actual time=0.385..0.388 rows=1 loops=1) Index Cond: ((ipstart < '3395660835'::bigint) AND (ipend > '3395660835'::bigint)) Planning Time: 0.322 ms Execution Time: 0.426 ms (4 rows)
编写压测脚本进行压力测试
vi t1.sql select * from tbl_idc_iprange_int8 where ipstart < 3395660835 and ipend > 3395660835; pgbench -M prepared -n -r -f ./t1.sql -P 1 -c 10 -j 10 -T 120
压测结果
transaction type: ./t1.sql scaling factor: 1 query mode: prepared number of clients: 10 number of threads: 10 duration: 120 s number of transactions actually processed: 7666686 latency average = 0.156 ms latency stddev = 0.084 ms initial connection time = 19.873 ms tps = 63899.196660 (without initial connection time) statement latencies in milliseconds: 0.156 select * from tbl_idc_iprange_int8 where ipstart < 3395660835 and ipend > 3395660835; TPS: 63899.196660
PolarDB|PG新方法1 设计和实验
PolarDB和PG都支持inet类型, 可以直接使用IP地址进行范围判断.
create table tbl_idc_iprange ( id int primary key, -- 主键 ipr inet, -- ip地址段 loc text, -- 物理地址 provider text, -- 运营商 idcid int references tbl_idc(id) -- idc id, 随便输入, 不影响测试 ); -- 创建inet的spgist索引. create index on tbl_idc_iprange using spgist (ipr);
根据用户的来源IP(移动端接入地址通常是基站IP). 假设来源来源ip是202.101.172.35 : 3395660835
select * from public.tbl_idc_iprange where ipr >>= '202.101.172.35'; id | ipr | loc | provider | idcid ------+------------------+-----+----------+------- 6924 | 202.101.128.0/18 | | | 4 (1 row) postgres=# explain analyze select * from public.tbl_idc_iprange where ipr >>= '202.101.172.35'; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------ Index Scan using tbl_idc_iprange_ipr_idx on tbl_idc_iprange (cost=0.15..2.37 rows=1 width=79) (actual time=0.070..0.072 rows=1 loops=1) Index Cond: (ipr >>= '202.101.172.35'::inet) Planning Time: 0.371 ms Execution Time: 0.117 ms (4 rows)
编写压测脚本进行压力测试
vi t2.sql select * from public.tbl_idc_iprange where ipr >>= '202.101.172.35'; pgbench -M prepared -n -r -f ./t2.sql -P 1 -c 10 -j 10 -T 120
压测结果
transaction type: ./t2.sql scaling factor: 1 query mode: prepared number of clients: 10 number of threads: 10 duration: 120 s number of transactions actually processed: 32939025 latency average = 0.036 ms latency stddev = 0.025 ms initial connection time = 21.646 ms tps = 274540.956059 (without initial connection time) statement latencies in milliseconds: 0.036 select * from public.tbl_idc_iprange where ipr >>= '202.101.172.35'; TPS: 274540.956059
PolarDB|PG新方法2 设计和实验
很显然使用了spgist后性能得到了飙升. 如果用户最初设计的表结构并未使用inet类型, 那么可以使用表达式索引来支持该能力.
使用传统方法的表结构, 将字段转换为int8range类型, 然后使用范围类型索引加速范围检索.
create index on tbl_idc_iprange_int8 using gist (int8range(ipstart,ipend));
select * from tbl_idc_iprange_int8 where int8range(ipstart,ipend) @> 3395660835; id | ipstart | ipend | loc | provider | idcid ------+------------+------------+-----+----------+------- 6924 | 3395649536 | 3395665919 | | | 4 (1 row) postgres=# explain analyze select * from tbl_idc_iprange_int8 where int8range(ipstart,ipend) @> 3395660835; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------- Bitmap Heap Scan on tbl_idc_iprange_int8 (cost=1.58..36.16 rows=43 width=88) (actual time=0.155..0.158 rows=1 loops=1) Recheck Cond: (int8range(ipstart, ipend) @> '3395660835'::bigint) Heap Blocks: exact=1 -> Bitmap Index Scan on tbl_idc_iprange_int8_int8range_idx (cost=0.00..1.57 rows=43 width=0) (actual time=0.144..0.145 rows=1 loops=1) Index Cond: (int8range(ipstart, ipend) @> '3395660835'::bigint) Planning Time: 0.629 ms Execution Time: 0.269 ms (7 rows) postgres=# set enable_bitmapscan =off; SET postgres=# explain analyze select * from tbl_idc_iprange_int8 where int8range(ipstart,ipend) @> 3395660835; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------ Index Scan using tbl_idc_iprange_int8_int8range_idx on tbl_idc_iprange_int8 (cost=0.15..38.30 rows=43 width=88) (actual time=0.127..0.129 rows=1 loops=1) Index Cond: (int8range(ipstart, ipend) @> '3395660835'::bigint) Planning Time: 0.212 ms Execution Time: 0.174 ms (4 rows)
编写压测脚本进行压力测试
vi t3.sql select * from tbl_idc_iprange_int8 where int8range(ipstart,ipend) @> 3395660835; pgbench -M prepared -n -r -f ./t3.sql -P 1 -c 10 -j 10 -T 120
压测结果
transaction type: ./t3.sql scaling factor: 1 query mode: prepared number of clients: 10 number of threads: 10 duration: 120 s number of transactions actually processed: 27106165 latency average = 0.044 ms latency stddev = 0.032 ms initial connection time = 20.660 ms tps = 225923.247074 (without initial connection time) statement latencies in milliseconds: 0.044 select * from tbl_idc_iprange_int8 where int8range(ipstart,ipend) @> 3395660835; TPS: 225923.247074
对照
| 测试模型 | 性能指标(TPS) |
传统方法int8类型 between and |
63899.196660 |
PG|PolarDB inet类型 >>= |
274540.956059 |
PG|PolarDB int8range 类型表达式索引 @> |
225923.247074 |
传统方法没有IP类型也没有range类型, 使用传统的2字段范围检索, 性能较差.
知识点
inet类型: https://www.postgresql.org/docs/16/datatype-net-types.html
- 得到ip地址段的开始IP
network(inet) AS start_address - 得到ip地址段的结束IP
broadcast(inet) AS end_address - 得到ip地址段的IP
split_part(inet::text, '/', 1) - 将ip地址转换为int8
ip2int8(inet)
IP地址段匹配: https://www.postgresql.org/docs/16/functions-net.html
int8range类型: https://www.postgresql.org/docs/16/rangetypes.html
表达式索引: https://www.postgresql.org/docs/16/indexes-expressional.html
范围查找: https://www.postgresql.org/docs/16/functions-range.html
gist索引:
- 《PostgreSQL 9种索引的原理和应用场景》
- 《PostgreSQL GiST 索引原理 - 4》
- 《PostgreSQL GiST 索引原理 - 3》
- 《PostgreSQL GiST 索引原理 - 2》
- 《PostgreSQL GiST 索引原理 - 1》
spgist索引:
- 《PostgreSQL gist, spgist索引的原理、差别、应用场景》
- 《PostgreSQL SP-GiST 索引原理》
- 《PostgreSQL 黑科技 - 空间聚集存储, 使用gevel插件(增强pageinspect)内窥GIN, GiST, SP-GiST索引》
- 《自动选择正确索引访问接口(btree,hash,gin,gist,sp-gist,brin,bitmap...)的方法》
- 《从难缠的模糊查询聊开 - PostgreSQL独门绝招之一 GIN , GiST , SP-GiST , RUM 索引原理与技术背景》
- 《PostgreSQL SP-GiST GiST 索引选择?》
思考
还有什么业务需要通过本实例提供的方法解决网络延迟的问题.
如果是星链网络, 有没有来源IP, 是不是涉及到3维实时计算离哪个基站最近?
参考
《如何获得IP地址对应的地理信息库, 实现智能DNS解析? 就近路由提升全球化部署业务的访问性能》
《PolarDB for PostgreSQL 采用iprange和SPGiST index 实现超光速 - 全球化业务根据来源IP智能DNS路由就近分配本地机房访问, 提升用户体验》
《聊聊between and的坑 和 神奇的解法 - range + gist - 智能DNS解析》
《PostgreSQL 黑科技 range 类型及 gist index 20x+ speedup than Mysql index combine query》
