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

1.问题描述：

MySQL 数据库，单表 `im_data_record`，查询超时(30s)

表结构如下图：

表索引如下图：

查询语句如下:

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

)

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

ORDER BY `record_global_id` DESC

LIMIT 0 , 20;

执行计划如下图：

存储数据特殊说明：

数据现在总共有 5500035 row

其中有一个特殊类型(`module` = ‘特定名称’)的数据就有 5220811 row

数据增长每天也就7-8W的样子，而且每天的增长90%是这个特殊module类型数据增长

查询数据业务说明：

查询业务需要分页

查询业务需要可能会任意组合module，即会出现：

module IN (module_name_1, module_name_2....)

查询业务需要会出现任意接受者和发送者，并不包含module的情况，即会出现：

(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)

查询业务会出现同时查询上面两种的情况的数据，并分页

字段record_global_id唯一索引，并根据此字段排序分页，即：

ORDER BY `record_global_id` DESC

LIMIT 0 , 20;

2.尝试过增加

(record_global_id, module)

复合索引，执行计划不变，问题没有解决

3.这个慢查询怎么破？请大神给予指点，真诚感谢！