問題描述

問題解答

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

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

拆成幾條SQL分開查詢。

根據題主的問題，你那條SQL條件那么多，但是只能用到一個索引，豈不可惜，WHERE條件很明顯的一處：如下的那個’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 ------------------- 此處這個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分拆開來，思路就是 UNION，好了，直接貼我改造后的結果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;

如有作用能將執行計劃截圖發到評論里嗎？我想驗證下我的猜想，謝謝！

創建復合索引（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;

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