数据库 - MySQL 单表500W+数据，查询超时，如何优化呢？
问题描述
问题解答
回答1：原因是你对record_global_id这个属性做筛选，但条件不是等于，所以复合索引后面的部分就用不上了。
status列的区分度如何？加上索引(status, record_global_id)试试看。
回答2：拆成几条SQL分开查询。
回答3：根据题主的问题，你那条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;
如有作用能将执行计划截图发到评论里吗？我想验证下我的猜想，谢谢！
回答4：创建复合索引（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
网公网安备