mysql为何500w拆表_MySQL 单表500W+数据，查询超时，如何优化呢？

根据题主的问题，你那条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` DESC

LIMIT 0 , 20;

如有作用能将执行计划截图发到评论里吗？我想验证下我的猜想，谢谢！