問(wèn)題描述

問(wèn)題解答

原因是你對(duì)record_global_id這個(gè)屬性做篩選，但條件不是等于，所以復(fù)合索引后面的部分就用不上了。

status列的區(qū)分度如何？加上索引(status, record_global_id)試試看。

拆成幾條SQL分開(kāi)查詢。

根據(jù)題主的問(wèn)題，你那條SQL條件那么多，但是只能用到一個(gè)索引，豈不可惜，WHERE條件很明顯的一處：如下的那個(gè)’OR’:

(( ((`from_uid` = 5017446 AND `from_type` = 1 AND `to_uid` = 52494 AND `to_type` = 3)OR (`from_uid` = 52494 AND `from_type` = 3 AND `to_uid` = 5017446 AND `to_type` = 1) ) AND `type` = 2 AND `qa_id` = 0)OR ------------------- 此處這個(gè)OR ----------------------------------(`type` = 3 AND `to_uid` = 52494 AND `to_type` = 3 AND `from_uid` = 5017446 AND `from_type` = 1 AND `module` IN (’community.doctor:appointment:notice’ , ’community.doctor:transfer.treatment’, ’community.doctor:transfer.treatment.pay’, ’community.doctor:weiyi.guahao.to.user’, ’community.doctor:weiyi.prescription.to.patient’, ’community.doctor:user.buy.prescription’)) ) AND `status` = 1 AND `record_global_id` < 5407938

可以將整體的大的WHERE分拆開(kāi)來(lái)，思路就是 UNION，好了，直接貼我改造后的結(jié)果SQL，如果有作用望采納呦^_^

改造后SQL:

(SELECT `record_global_id`, `type`, `mark`, `from_uid`, `from_type`, `to_uid`, `to_type`, `send_method`, `action`, `module`, `send_time`, `content`FROM `im_data_record`WHERE ((`from_uid` = 5017446 AND `from_type` = 1 AND `to_uid` = 52494 AND `to_type` = 3)OR (`from_uid` = 52494 AND `from_type` = 3 AND `to_uid` = 5017446 AND `to_type` = 1) ) AND `type` = 2 AND `qa_id` = 0 AND `status` = 1 AND `record_global_id` < 5407938)UNION(SELECT `record_global_id`, `type`, `mark`, `from_uid`, `from_type`, `to_uid`, `to_type`, `send_method`, `action`, `module`, `send_time`, `content`FROM `im_data_record`WHERE `type` = 3 AND `to_uid` = 52494 AND `to_type` = 3 AND `from_uid` = 5017446 AND `from_type` = 1 AND `module` IN (’community.doctor:appointment:notice’ , ’community.doctor:transfer.treatment’, ’community.doctor:transfer.treatment.pay’, ’community.doctor:weiyi.guahao.to.user’, ’community.doctor:weiyi.prescription.to.patient’, ’community.doctor:user.buy.prescription’) AND `status` = 1 AND `record_global_id` < 5407938)ORDER BY `record_global_id` DESCLIMIT 0 , 20;

如有作用能將執(zhí)行計(jì)劃截圖發(fā)到評(píng)論里嗎？我想驗(yàn)證下我的猜想，謝謝！

創(chuàng)建復(fù)合索引（from_uid,to_uid,from_type,to_type,type,status,record_global_id）修改sql為union如下：

select * from ((SELECT `record_global_id`, `type`, `mark`, `from_uid`, `from_type`, `to_uid`, `to_type`, `send_method`, `action`, `module`, `send_time`, `content`FROM `im_data_record`WHERE`from_uid` = 5017446 AND `from_type` = 1 AND `to_uid` = 52494 AND `to_type` = 3 AND `type` = 2 AND `qa_id` = 0 AND `status` = 1 AND `record_global_id` < 5407938 ORDER BY `record_global_id` DESC LIMIT 0 , 20) union(SELECT `record_global_id`, `type`, `mark`, `from_uid`, `from_type`, `to_uid`, `to_type`, `send_method`, `action`, `module`, `send_time`, `content`FROM `im_data_record`WHERE`from_uid` = 52494 AND `from_type` = 3 AND `to_uid` = 5017446 AND `to_type` = 1 AND `type` = 2 AND `qa_id` = 0 AND `status` = 1 AND `record_global_id` < 5407938 ORDER BY `record_global_id` DESC LIMIT 0 , 20) union(SELECT `record_global_id`, `type`, `mark`, `from_uid`, `from_type`, `to_uid`, `to_type`, `send_method`, `action`, `module`, `send_time`, `content`FROM `im_data_record`WHERE`from_uid` = 5017446 AND `from_type` = 1 AND `to_uid` = 52494 AND `to_type` = 3 AND `type` = 3 AND `module` IN (’community.doctor:appointment:notice’ , ’community.doctor:transfer.treatment’, ’community.doctor:transfer.treatment.pay’, ’community.doctor:weiyi.guahao.to.user’, ’community.doctor:weiyi.prescription.to.patient’, ’community.doctor:user.buy.prescription’)AND `status` = 1 AND `record_global_id` < 5407938 ORDER BY `record_global_id` DESC LIMIT 0 , 20)) aa ORDER BY `record_global_id` DESC LIMIT 0 , 20;

如果根據(jù)from_uid,to_uid,from_type,to_type,type,status篩選的結(jié)果集較少的話，可在union子查詢中不用加AND record_global_id < 5407938 ORDER BY record_global_id DESC LIMIT 0 , 20