创建一个用户和设备关系映射表, 用户的设备 ID 存放在数组字段里面:
- CREATE TABLE device.user_devices
- (
- user_id character varying(32) COLLATE pg_catalog."default" NOT NULL,
- device_ids character varying[] COLLATE pg_catalog."default" NOT NULL,
- CONSTRAINT user_devices_pkey PRIMARY KEY (user_id)
- )
将数据导入表:
- insert into device.user_devices
- select device_owner, array_agg(device_id)
- from device.device_info
- where device_owner is not null
- and device_owner != ''
- group by device_owner
比较原查询方式和新查询方式的性能:
原查询方式:
新查询方式:
可以发现新查询方式的性能有了巨大的提升!
来源: https://yq.aliyun.com/articles/679693