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

Answer 1

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

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

Answer 2

拆成几条SQL分开查询。

Answer 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` DESC
LIMIT 0 , 20;

---

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

Answer 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