在58遇到的第一个有意思的问题.
用户画像的原始表,有一亿记录,100多个维度(100多个列),比如年龄,性别,爱好,是否有车,是否有房什么的.
测试环境800w数据,大概在5G左右
需要解决的问题一 :在100列中任选N列,过滤查询,执行时间小于一秒。实际上N一般在5到10
即类似
select * from 画像表 where 性别=‘男’ and 年龄 between 20 and 30 and 有车='yes' and 有房='yes' and 已婚='no'
问题二:全体数据的随意聚合,执行时间小于5秒
比如
select 年龄,性别,count(*) from 画像表 group by 年龄,性别
在数据库解决这个问题有一些麻烦,传统建索引优化的方式不起作用了。
100多个列随意选择几列查询,索引不可能提前建出这么多.
先看测试数据
CREATE TABLE `o_huaxiang_big` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`user_id` bigint(20) DEFAULT NULL,
`umc_sex` varchar(20) DEFAULT NULL,
`age` varchar(30) DEFAULT NULL,
PRIMARY KEY (`id`)
) ;
处理这个问题,我自然想到模拟一个位图.
一般画像数据有几种类型
1.数值类型
2.日期类型
3.日期时间类型
4.字符串类型
其中 日期和字符串类型可以作为离散值,
日期时间类型也可以转化为日期类型,作为离散值处理。
数值类型比较麻烦,需要人为介入判断是否是离散值,如果不是还需要划分范围。
总之,所有的值都要映射为离散值
然后以上图前5个数据为例,将离散值映射为位图
男 0 0 0 0 1
未知 1 0 0 1 0
女 0 1 1 0 0
一个bigint 是8字节的,为了取整,我存放60个记录的位信息。
然后建位图表如下
CREATE TABLE `bitmap20` (
`table_name` varchar(32) NOT NULL DEFAULT '' comment '位图表记录的原始表名称',
`column_name` varchar(32) NOT NULL DEFAULT '' comment '列名称',
`min_id` int(11) DEFAULT NULL comment '起始ID',
`max_id` int(11) DEFAULT NULL comment '终止ID',
`gid` int(11) NOT NULL DEFAULT '0' comment '分组ID,每组1200记录' ,
`grouped` varchar(32) NOT NULL DEFAULT '' comment '离散值',
`total` bigint(21) NOT NULL DEFAULT '0' comment '总数',
`c20` bigint(20) NOT NULL DEFAULT '0',
`c19` bigint(20) NOT NULL DEFAULT '0',
`c18` bigint(20) NOT NULL DEFAULT '0',
`c17` bigint(20) NOT NULL DEFAULT '0',
`c16` bigint(20) NOT NULL DEFAULT '0',
`c15` bigi