使用LATERAL VIEW explode将一个bitmap展开, 然后作为IN子查询的条件进行查询的时候, 性能比较低, 但是单独测试explode 和in查询性能都还可以, 这是什么原因呢
select max(c0) from t_jyOErqQFOu3Xkd2R8h0bqi where `@id` in (select tmp.id_col as id from (SELECT bitmap_from_base64(bitmap) AS bm from test_id ) t LATERAL VIEW explode_bitmap(t.bm) tmp AS id_col )
其中,t_jyOErqQFOu3Xkd2R8h0bqi有8亿行数据, bitmap中有2亿行数据,这个耗时大概要20秒左右
但是展开bitmap和in 2亿条数据其实都不慢
select max(c0) from t_jyOErqQFOu3Xkd2R8h0bqi where `@id` in (select id from xx物理临时表)
将2亿个ID存入物理临时表, 这样查只要2秒左右
select sum(id) from ( select tmp.id_col as id from (SELECT bitmap_from_base64(bitmap) AS bm from test_id ) t LATERAL VIEW explode_bitmap(t.bm) tmp AS id_col)tmp1
测试2亿个bitmap展开也只要2秒左右, 为啥组合写到一个SQL里面,就很慢变成20秒了
