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

Answer 1

原因是你對record_global_id這個屬性做篩選，但條件不是等於，所以複合索引後面的部分就用不上了。 record_global_id这个属性做筛选，但条件不是等于，所以复合索引后面的部分就用不上了。

status列的区分度如何？加上索引(status, 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如下：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

如果根據from_uid,to_uid,from_type,to_type,type,status篩選的結果集較少的話，可在union子查詢中不用加AND record_global_id record_global_id DESC LIMIT 0 , 20🎜