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

Answer 1

The reason is that you are filtering the attribute record_global_id, but the condition is not equal, so the part after the composite index is not used. record_global_id这个属性做筛选，但条件不是等于，所以复合索引后面的部分就用不上了。

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

How differentiated is the status column? Add the index (status, record_global_id) and try it. 🎜

Answer 2

Split into several SQLs to query separately.

Answer 3

According to the question, your SQL has so many conditions, but it can only use one index. Isn’t it a pity? The WHERE condition is very obvious: the following '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

The whole large WHERE can be split into two parts. The idea is UNION. Well, I will directly post the SQL result after my transformation. If it is useful, I hope you will adopt it^_^

SQL after transformation:

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

---

If it works, can you post a screenshot of the execution plan in the comments? I want to test my suspicion, thank you!

Answer 4

Create composite index (from_uid,to_uid,from_type,to_type,type, status,record_global_id)
Modify sql to union as follows: 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 rrreee

If the result set filtered based on from_uid, to_uid, from_type, to_type, type, status is small, there is no need to add AND in the union subquery record_global_id < 5407938 ORDER BY record_global_id DESC LIMIT 0, 20🎜