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

Question

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.这个慢查询怎么破？请大神给予指点，真诚感谢！

Answer 1

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

---

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

Answer 2

创建复合索引（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

Answer 3

原因是你对record_global_id这个属性做筛选，但条件不是等于，所以复合索引后面的部分就用不上了。

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

Answer 4

拆成几条SQL分开查询。

Answer 5

嗯，看来对于你的问题那个 @wangnan 的答案就可以，分开两拨查询，多次用到索引，不过数据量大的时候可能会　filesort，稍微会影响点性能．哈哈！