如何使用MySQL位图索引解决用户画像问题

这篇文章给大家分享的是有关如何使用MySQL位图索引解决用户画像问题的内容。小编觉得挺实用的，因此分享给大家做个参考，一起跟随小编过来看看吧。

每个bigint类型包括60个记录的位信息.

但是第0位表示第六十个记录的位

第1位至第59位表示第一至五十九的记录的位信息.

这样记录的位信息保存并不连续,

使用的时候还得把最右边的一位挪到最左边,不好理解还非常麻烦,性能也有损耗.

经过王工的改良,

使用如下sql替换，则保存的位信息就连续了

    SELECT             CEIL(id / 60) g60,            CEIL(id / 1200) g1200,            age grouped,            COUNT(*) total,            BIT_OR(1 << (if(MOD(id, 60)=0,60,MOD(id, 60)))) bitmap        FROM            o_huaxiang_big_0 o        GROUP BY g1200 , g60 , age

创建位图索引的整体SQL如下

truncate table bitmap20_0;insert into bitmap20_0 select   'o_huaxiang_big' table_name,  'umc_sex' column_name,  ((g1200-1)*60)*20 min_id,  ((g1200-1)*60)*20+1200 max_id,  v2.*from (    select     g1200,    grouped,    sum(total) total,     ifnull(max(case when abs((g1200-1)*20-g60)=20 then bitmap else null end),0) c20,    ifnull(max(case when abs((g1200-1)*20-g60)=19 then bitmap else null end),0) c19,    ifnull(max(case when abs((g1200-1)*20-g60)=18 then bitmap else null end),0) c18,    ifnull(max(case when abs((g1200-1)*20-g60)=17 then bitmap else null end),0) c17,    ifnull(max(case when abs((g1200-1)*20-g60)=16 then bitmap else null end),0) c16,    ifnull(max(case when abs((g1200-1)*20-g60)=15 then bitmap else null end),0) c15,    ifnull(max(case when abs((g1200-1)*20-g60)=14 then bitmap else null end),0) c14,    ifnull(max(case when abs((g1200-1)*20-g60)=13 then bitmap else null end),0) c13,    ifnull(max(case when abs((g1200-1)*20-g60)=12 then bitmap else null end),0) c12,    ifnull(max(case when abs((g1200-1)*20-g60)=11 then bitmap else null end),0) c11,    ifnull(max(case when abs((g1200-1)*20-g60)=10 then bitmap else null end),0) c10,    ifnull(max(case when abs((g1200-1)*20-g60)=9 then bitmap else null end),0) c9,    ifnull(max(case when abs((g1200-1)*20-g60)=8 then bitmap else null end),0) c8,    ifnull(max(case when abs((g1200-1)*20-g60)=7 then bitmap else null end),0) c7,    ifnull(max(case when abs((g1200-1)*20-g60)=6 then bitmap else null end),0) c6,    ifnull(max(case when abs((g1200-1)*20-g60)=5 then bitmap else null end),0) c5,    ifnull(max(case when abs((g1200-1)*20-g60)=4 then bitmap else null end),0) c4,    ifnull(max(case when abs((g1200-1)*20-g60)=3 then bitmap else null end),0) c3,    ifnull(max(case when abs((g1200-1)*20-g60)=2 then bitmap else null end),0) c2,    ifnull(max(case when abs((g1200-1)*20-g60)=1 then bitmap else null end),0) c1    from (        SELECT             CEIL(id / 60) g60,            CEIL(id / 1200) g1200,            umc_sex grouped,            COUNT(*) total,            BIT_OR(1 << (if(MOD(id, 60)=0,60,MOD(id, 60)))) bitmap        FROM            o_huaxiang_big_0 o        GROUP BY g1200 , g60 , umc_sex    ) v1 group by  g1200,grouped) v2;  insert into bitmap20_0 select   'o_huaxiang_big' table_name,  'age' column_name,    ((g1200-1)*60)*20 min_id,    ((g1200-1)*60)*20+1200 max_id,  v2.*from (    select     g1200,    grouped,    sum(total) total,     ifnull(max(case when abs((g1200-1)*20-g60)=20 then bitmap else null end),0) c20,    ifnull(max(case when abs((g1200-1)*20-g60)=19 then bitmap else null end),0) c19,    ifnull(max(case when abs((g1200-1)*20-g60)=18 then bitmap else null end),0) c18,    ifnull(max(case when abs((g1200-1)*20-g60)=17 then bitmap else null end),0) c17,    ifnull(max(case when abs((g1200-1)*20-g60)=16 then bitmap else null end),0) c16,    ifnull(max(case when abs((g1200-1)*20-g60)=15 then bitmap else null end),0) c15,    ifnull(max(case when abs((g1200-1)*20-g60)=14 then bitmap else null end),0) c14,    ifnull(max(case when abs((g1200-1)*20-g60)=13 then bitmap else null end),0) c13,    ifnull(max(case when abs((g1200-1)*20-g60)=12 then bitmap else null end),0) c12,    ifnull(max(case when abs((g1200-1)*20-g60)=11 then bitmap else null end),0) c11,    ifnull(max(case when abs((g1200-1)*20-g60)=10 then bitmap else null end),0) c10,    ifnull(max(case when abs((g1200-1)*20-g60)=9 then bitmap else null end),0) c9,    ifnull(max(case when abs((g1200-1)*20-g60)=8 then bitmap else null end),0) c8,    ifnull(max(case when abs((g1200-1)*20-g60)=7 then bitmap else null end),0) c7,    ifnull(max(case when abs((g1200-1)*20-g60)=6 then bitmap else null end),0) c6,    ifnull(max(case when abs((g1200-1)*20-g60)=5 then bitmap else null end),0) c5,    ifnull(max(case when abs((g1200-1)*20-g60)=4 then bitmap else null end),0) c4,    ifnull(max(case when abs((g1200-1)*20-g60)=3 then bitmap else null end),0) c3,    ifnull(max(case when abs((g1200-1)*20-g60)=2 then bitmap else null end),0) c2,    ifnull(max(case when abs((g1200-1)*20-g60)=1 then bitmap else null end),0) c1    from (        SELECT             CEIL(id / 60) g60,            CEIL(id / 1200) g1200,            age grouped,            COUNT(*) total,            BIT_OR(1 << (if(MOD(id, 60)=0,60,MOD(id, 60)))) bitmap        FROM            o_huaxiang_big_0 o        GROUP BY g1200 , g60 , age    ) v1 group by  g1200,grouped) v2;

感谢各位的阅读！关于"如何使用MySQL位图索引解决用户画像问题"这篇文章就分享到这里了，希望以上内容可以对大家有一定的帮助，让大家可以学到更多知识，如果觉得文章不错，可以把它分享出去让更多的人看到吧！