SELECT COUNT(DISTINCT friend.id_) FROM ftsp_kh_khxx khxx JOIN ftsp_kh_pg pg ON pg.kh_khxx_id_ = khxx.id_ JOIN ftsp_infra_user u ON u.id_ = pg.infra_user_id_ AND u.is_delete_ = '0' JOIN ftsp_zj_bmyh bmyh ON bmyh.infra_user_id_ = u.id_ AND bmyh.is_delete_ = '0' JOIN ftsp_zj_bmxx bmxx ON bmyh.zj_bmxx_id_ = bmxx.id_ AND bmxx.is_delete_ = '0' JOIN ftsp_wechat_friend friend ON u.work_weixin_id_ = friend.infra_user_wxid_ AND friend.source_ = '2' AND friend.is_delete_ = '0' LEFT JOIN ftsp_wechat_kh_relation relation ON relation.kh_khxx_id_ = khxx.id_ AND relation.type_ = '1' AND friend.wxid_ = relation.wxid_ WHERE bmyh.is_active_ = '1' AND bmxx.id_ IN ('h0000000000000498698775546511360' , 'h0000000000000498698781397606400', 'h0000000000000498698787076653056', 'h0000000000000498698798514569216', 'h0000000000000498698804499791872', 'h0000000000000498698810493493248') AND bmxx.zj_zjxx_id_ = 'h0000000000000498723219407372288' AND khxx.zj_zjxx_id_ = 'h0000000000000498723219407372288' AND CONVERT( FROM_BASE64(friend.nickname_) , CHAR) LIKE CONCAT('%', '辻弎&', '%') AND EXISTS( SELECT 1 FROM ftsp_wechat_kh_relation re JOIN ftsp_kh_khxx kh ON re.kh_khxx_id_ = kh.id_ WHERE re.type_ = '1' AND re.wxid_ = friend.wxid_ AND kh.name_ LIKE CONCAT('%', '鲁创', '%')) AND khxx.zj_zjxx_id_ = 'h0000000000000498723219407372288' AND khxx.id_ IN (SELECT DISTINCT pg_.kh_khxx_id_ FROM ftsp_kh_pg pg_, ftsp_zj_bmxx bm_ WHERE pg_.zj_bmxx_id_ = bm_.id_ AND bm_.zj_zjxx_id_ = 'h0000000000000498723219407372288' AND bm_.id_ IN ('h0000000000000498698775546511360' , 'h0000000000000498698781397606400', 'h0000000000000498698787076653056', 'h0000000000000498698798514569216', 'h0000000000000498698804499791872', 'h0000000000000498698810493493248', 'h0000000000000512492012482002944', 'h0000000000000498698775546511360', 'h0000000000000498698787076653056', 'h0000000000000498698781397606400')) =========================================================================================================== |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| ----------------------------------------------------------------------------------------------------------- |0 |SCALAR GROUP BY | |1 |8006 | |1 |└─NESTED-LOOP OUTER JOIN | |1 |8006 | |2 | ├─NESTED-LOOP JOIN | |1 |7978 | |3 | │ ├─NESTED-LOOP JOIN | |1 |7959 | |4 | │ │ ├─NESTED-LOOP JOIN | |1 |7930 | |5 | │ │ │ ├─NESTED-LOOP SEMI JOIN | |1 |7910 | |6 | │ │ │ │ ├─NESTED-LOOP JOIN | |1 |7858 | |7 | │ │ │ │ │ ├─NESTED-LOOP JOIN | |16 |495 | |8 | │ │ │ │ │ │ ├─NESTED-LOOP JOIN | |16 |204 | |9 | │ │ │ │ │ │ │ ├─SUBPLAN SCAN |VIEW2 |6 |107 | |10| │ │ │ │ │ │ │ │ └─HASH DISTINCT | |6 |107 | |11| │ │ │ │ │ │ │ │ └─NESTED-LOOP JOIN | |6 |105 | |12| │ │ │ │ │ │ │ │ ├─TABLE GET |bm_ |1 |53 | |13| │ │ │ │ │ │ │ │ └─TABLE RANGE SCAN|pg_(idx_ftsp_kh_pg_2) |638 |36 | |14| │ │ │ │ │ │ │ └─TABLE RANGE SCAN |pg(idx_ftsp_kh_pg_1) |3 |18 | |15| │ │ │ │ │ │ └─TABLE GET |u |1 |19 | |16| │ │ │ │ │ └─TABLE RANGE SCAN |friend(idx_ftsp_wechat_friend_2) |4 |479 | |17| │ │ │ │ └─SUBPLAN SCAN |VIEW1 |1 |52 | |18| │ │ │ │ └─NESTED-LOOP JOIN | |1 |52 | |19| │ │ │ │ ├─TABLE RANGE SCAN |re(idx_ftsp_wechat_kh_relation_3) |1 |30 | |20| │ │ │ │ └─TABLE GET |kh |1 |22 | |21| │ │ │ └─TABLE GET |khxx |1 |20 | |22| │ │ └─TABLE RANGE SCAN |bmyh(idx_ftsp_zj_bmyh_1) |1 |28 | |23| │ └─TABLE GET |bmxx |1 |19 | |24| └─TABLE RANGE SCAN |relation(idx_ftsp_wechat_kh_relation_3)|1 |27 | =========================================================================================================== Outputs & filters: ------------------------------------- 0 - output([T_FUN_COUNT(distinct friend.id_)]), filter(nil), rowset=256 group(nil), agg_func([T_FUN_COUNT(distinct friend.id_)]) 1 - output([friend.id_]), filter(nil), rowset=256 conds(nil), nl_params_([khxx.id_(:27)], [friend.wxid_(:28)]), use_batch=true 2 - output([khxx.id_], [friend.wxid_], [friend.id_]), filter(nil), rowset=256 conds(nil), nl_params_([bmyh.zj_bmxx_id_(:22)]), use_batch=true 3 - output([khxx.id_], [friend.wxid_], [bmyh.zj_bmxx_id_], [friend.id_]), filter(nil), rowset=256 conds(nil), nl_params_([pg.infra_user_id_(:12)]), use_batch=true 4 - output([khxx.id_], [friend.wxid_], [pg.infra_user_id_], [friend.id_]), filter(nil), rowset=256 conds(nil), nl_params_([pg.kh_khxx_id_(:5)]), use_batch=true 5 - output([friend.wxid_], [pg.infra_user_id_], [pg.kh_khxx_id_], [friend.id_]), filter(nil), rowset=256 conds(nil), nl_params_([friend.wxid_(:24)]), use_batch=false 6 - output([friend.wxid_], [pg.infra_user_id_], [pg.kh_khxx_id_], [friend.id_]), filter(nil), rowset=256 conds(nil), nl_params_([u.work_weixin_id_(:20)]), use_batch=true 7 - output([pg.infra_user_id_], [pg.kh_khxx_id_], [u.work_weixin_id_]), filter(nil), rowset=256 conds(nil), nl_params_([pg.infra_user_id_(:10)]), use_batch=true 8 - output([pg.infra_user_id_], [pg.kh_khxx_id_]), filter(nil), rowset=256 conds(nil), nl_params_([VIEW2.kh_khxx_id_(:17)]), use_batch=true 9 - output([VIEW2.kh_khxx_id_]), filter(nil), rowset=256 access([VIEW2.kh_khxx_id_]) 10 - output([pg_.kh_khxx_id_]), filter(nil), rowset=256 distinct([pg_.kh_khxx_id_]) 11 - output([pg_.kh_khxx_id_]), filter(nil), rowset=256 conds(nil), nl_params_([bm_.id_(:1)]), use_batch=true 12 - output([bm_.id_]), filter([bm_.zj_zjxx_id_ = 'h0000000000000498723219407372288']), rowset=256 access([bm_.id_], [bm_.zj_zjxx_id_]), partitions(p0) is_index_back=false, is_global_index=false, filter_before_indexback[false], range_key([bm_.id_]), range[h0000000000000498698775546511360 ; h0000000000000498698775546511360], [h0000000000000498698781397606400 ; h0000000000000498698781397606400], [h0000000000000498698787076653056 ; h0000000000000498698787076653056], [h0000000000000498698798514569216 ; h0000000000000498698798514569216], [h0000000000000498698804499791872 ; h0000000000000498698804499791872], [h0000000000000498698810493493248 ; h0000000000000498698810493493248], [h0000000000000512492012482002944 ; h0000000000000512492012482002944], range_cond([bm_.id_ IN ('h0000000000000498698775546511360', 'h0000000000000498698781397606400', 'h0000000000000498698787076653056', 'h0000000000000498698798514569216', 'h0000000000000498698804499791872', 'h0000000000000498698810493493248', 'h0000000000000512492012482002944', 'h0000000000000498698775546511360', 'h0000000000000498698787076653056', 'h0000000000000498698781397606400')]) 13 - output([pg_.kh_khxx_id_]), filter(nil), rowset=256 access([GROUP_ID], [pg_.kh_khxx_id_]), partitions(p0) is_index_back=false, is_global_index=false, range_key([pg_.zj_bmxx_id_], [pg_.kh_khxx_id_], [pg_.id_]), range(MIN ; MAX), range_cond([pg_.zj_bmxx_id_ = :1]) 14 - output([pg.kh_khxx_id_], [pg.infra_user_id_]), filter(nil), rowset=256 access([GROUP_ID], [pg.kh_khxx_id_], [pg.infra_user_id_]), partitions(p0) is_index_back=false, is_global_index=false, range_key([pg.kh_khxx_id_], [pg.infra_user_id_], [pg.zj_bmxx_id_], [pg.shadow_pk_0]), range(MIN ; MAX), range_cond([:17 = pg.kh_khxx_id_]) 15 - output([u.work_weixin_id_]), filter([cast(u.is_delete_, DECIMAL(1, 0)) = cast('0', DECIMAL(1, -1))]), rowset=256 access([GROUP_ID], [u.is_delete_], [u.work_weixin_id_]), partitions(p0) is_index_back=false, is_global_index=false, filter_before_indexback[false], range_key([u.id_]), range(MIN ; MAX), range_cond([u.id_ = :10]) 16 - output([friend.id_], [friend.wxid_]), filter([cast(friend.source_, DECIMAL(1, 0)) = cast('2', DECIMAL(1, -1))], [cast(friend.is_delete_, DECIMAL(1, 0)) = cast('0', DECIMAL(1, -1))], [(T_OP_LIKE, cast(FROM_BASE64(cast(friend.nickname_, VARCHAR(1048576))), CHAR(1048576)), CONCAT('%', '辻弎&', '%'), '\\')]), rowset=256 access([GROUP_ID], [friend.id_], [friend.source_], [friend.is_delete_], [friend.wxid_], [friend.nickname_]), partitions(p0) is_index_back=true, is_global_index=false, filter_before_indexback[false,false,false], range_key([friend.infra_user_wxid_], [friend.wxid_], [friend.id_]), range(MIN ; MAX), range_cond([:20 = friend.infra_user_wxid_]) 17 - output(nil), filter(nil), rowset=256 access(nil) 18 - output([re.wxid_]), filter(nil), rowset=256 conds(nil), nl_params_([re.kh_khxx_id_(:25)]), use_batch=false 19 - output([re.kh_khxx_id_], [re.wxid_]), filter(nil), rowset=256 access([re.id_], [re.kh_khxx_id_], [re.wxid_]), partitions(p0) is_index_back=true, is_global_index=false, range_key([re.wxid_], [re.type_], [re.id_]), range(MIN,MIN,MIN ; MAX,MAX,MAX)always true, range_cond([cast(re.type_, DECIMAL(1, 0)) = cast('1', DECIMAL(1, -1))], [re.wxid_ = :24]) 20 - output(nil), filter([(T_OP_LIKE, kh.name_, CONCAT('%', '鲁创', '%'), '\\')]), rowset=256 access([kh.name_]), partitions(p0) is_index_back=false, is_global_index=false, filter_before_indexback[false], range_key([kh.id_]), range(MIN ; MAX), range_cond([:25 = kh.id_]) 21 - output([khxx.id_]), filter([khxx.zj_zjxx_id_ = 'h0000000000000498723219407372288']), rowset=256 access([GROUP_ID], [khxx.id_], [khxx.zj_zjxx_id_]), partitions(p0) is_index_back=false, is_global_index=false, filter_before_indexback[false], range_key([khxx.id_]), range(MIN ; MAX), range_cond([:5 = khxx.id_]) 22 - output([bmyh.zj_bmxx_id_]), filter(nil), rowset=256 access([GROUP_ID], [bmyh.zj_bmxx_id_]), partitions(p0) is_index_back=false, is_global_index=false, range_key([bmyh.zj_bmxx_id_], [bmyh.infra_user_id_], [bmyh.is_delete_], [bmyh.is_active_], [bmyh.id_]), range(MIN ; MAX), range_cond([cast(bmyh.is_active_, DECIMAL(1, 0)) = cast('1', DECIMAL(1, -1))], [cast(bmyh.is_delete_, DECIMAL(1, 0)) = cast('0', DECIMAL(1, -1))], [bmyh.zj_bmxx_id_ IN ('h0000000000000498698775546511360', 'h0000000000000498698781397606400', 'h0000000000000498698787076653056', 'h0000000000000498698798514569216', 'h0000000000000498698804499791872', 'h0000000000000498698810493493248')], [bmyh.infra_user_id_ = :12]) 23 - output(nil), filter([bmxx.zj_zjxx_id_ = 'h0000000000000498723219407372288'], [cast(bmxx.is_delete_, DECIMAL(1, 0)) = cast('0', DECIMAL(1, -1))]), rowset=256 access([GROUP_ID], [bmxx.is_delete_], [bmxx.zj_zjxx_id_]), partitions(p0) is_index_back=false, is_global_index=false, filter_before_indexback[false,false], range_key([bmxx.id_]), range(MIN ; MAX), range_cond([:22 = bmxx.id_]) 24 - output(nil), filter([relation.kh_khxx_id_ = :27]), rowset=256 access([GROUP_ID], [relation.id_], [relation.kh_khxx_id_]), partitions(p0) is_index_back=true, is_global_index=false, filter_before_indexback[false], range_key([relation.wxid_], [relation.type_], [relation.id_]), range(MIN ; MAX), range_cond([cast(relation.type_, DECIMAL(1, 0)) = cast('1', DECIMAL(1, -1))], [:28 = relation.wxid_])