mysql 位图_MySQL位图索引解决用户画像问题

在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