时间戳

首页 > 解决方案 > ORDER BY 主键超级慢，即使子查询非常快

mysql - ORDER BY 主键超级慢，即使子查询非常快

问题描述

create table account_match
(
    id                 int(11) unsigned auto_increment primary key,
    smaller_account_id int(11) unsigned                    not null,
    bigger_account_id  int(11) unsigned                    not null,
    match_origin       varchar(50)                         null,
    created            timestamp default CURRENT_TIMESTAMP not null,
    constraint account_match_account_id_fk
        foreign key (smaller_account_id) references account (id)
            on delete cascade,
    constraint account_match_account_id_fk_2
        foreign key (bigger_account_id) references account (id)
            on delete cascade

create index account_match_smaller_account_id_bigger_account_id_index
    on account_match (smaller_account_id, bigger_account_id);
);

create table private_message
(
    id                  int(11) unsigned auto_increment primary key,
    sender_account_id   int(11) unsigned                     not null,
    receiver_account_id int(11) unsigned                     null,
    message             text                                 null,
    viewed              tinyint(1) default 0                 not null,
    created             timestamp  default CURRENT_TIMESTAMP not null,
    constraint private_message_account_id_fk
        foreign key (sender_account_id) references account (id)
            on delete cascade,
    constraint private_message_account_id_fk_2
        foreign key (receiver_account_id) references account (id)
            on delete cascade
);

create index private_message_receiver_account_id_sender_account_id_index
    on private_message (receiver_account_id, sender_account_id);

create index private_message_sender_account_id_receiver_account_id_index
    on private_message (sender_account_id, receiver_account_id);

SELECT account_match.*
FROM (
         SELECT account_match.id
         FROM account_match
         WHERE id IN (SELECT id
                      FROM account_match
                      WHERE smaller_account_id = 1
                      UNION ALL
                      SELECT id
                      FROM account_match
                      WHERE bigger_account_id = 1)
           AND NOT EXISTS(SELECT TRUE
                          FROM private_message
                          WHERE sender_account_id = smaller_account_id
                            AND receiver_account_id = bigger_account_id)
           AND NOT EXISTS(SELECT TRUE
                          FROM private_message
                          WHERE sender_account_id = bigger_account_id
                            AND receiver_account_id = smaller_account_id)
     ) match_ids
         JOIN account_match using (id)
ORDER BY id DESC
LIMIT 20

1   PRIMARY account_match       index   PRIMARY PRIMARY 4       20  100 Backward index scan
1   PRIMARY account_match       eq_ref  PRIMARY PRIMARY 4   miliar.account_match.id 1   100 Using where
6   DEPENDENT SUBQUERY  private_message     ref private_message_receiver_account_id_sender_account_id_index,private_message_sender_account_id_receiver_account_id_index private_message_sender_account_id_receiver_account_id_index 9   miliar.account_match.bigger_account_id,miliar.account_match.smaller_account_id  1   100 Using index
5   DEPENDENT SUBQUERY  private_message     ref private_message_receiver_account_id_sender_account_id_index,private_message_sender_account_id_receiver_account_id_index private_message_sender_account_id_receiver_account_id_index 9   miliar.account_match.smaller_account_id,miliar.account_match.bigger_account_id  1   100 Using index
3   DEPENDENT SUBQUERY  account_match       eq_ref  PRIMARY,account_match_smaller_account_id_bigger_account_id_index    PRIMARY 4   func    1   26.57   Using where
4   DEPENDENT UNION account_match       eq_ref  PRIMARY,account_match_smaller_account_id_bigger_account_id_index,account_match_account_id_fk_2  PRIMARY 4   func    1   5   Using where

查询的重点是获取 ID 为 1 的帐户的匹配项（当然其他时候将是其他 ID），其中两个用户都没有互相发送消息。主查询（子查询）在不到 20 毫秒内完成，但是一旦我添加 ORDER BY id DESC，它就会上升到 20 秒。子查询返回了大约 710k 行，但是大小无关紧要，因为我试图按主键排序，对吧？有什么办法可以解决这个速度？

标签： mysqlsqldatabaseperformanceprimary-key

解决方案

试试下面的看看（因为我发现你的大部分代码似乎已经过时了）

以下没有工作

SELECT *
FROM account_match
WHERE (smaller_account_id = 1 or bigger_account_id = 1)
    AND EXISTS(SELECT TRUE
                   FROM private_message
                   WHERE (sender_account_id != bigger_account_id or receiver_account_id != smaller_account_id)
                    AND  (sender_account_id != smaller_account_id or receiver_account_id != bigger_account_id))
    ORDER BY id DESC
    LIMIT 20

尝试这个

-- Create a Temp table and get data from the faster query into that.
CREATE TEMPORARY TABLE a_m_temp
SELECT account_match.id
FROM account_match
WHERE id IN (SELECT id
        FROM account_match
        WHERE smaller_account_id = 1
        UNION ALL
        SELECT id
        FROM account_match
        WHERE bigger_account_id = 1)
    AND NOT EXISTS(SELECT TRUE
            FROM private_message
            WHERE sender_account_id = smaller_account_id
            AND receiver_account_id = bigger_account_id)
    AND NOT EXISTS(SELECT TRUE
            FROM private_message
            WHERE sender_account_id = bigger_account_id
            AND receiver_account_id = smaller_account_id)

-- Inner Join the temp. table to the main

Select a.* from
account_match a
INNER JOIN a_m_temp t
ON a.id = t.id
Order by a.id
Limit 20

推荐阅读