如何避免在使用左联接和排序依据时进行文件排序

Question

我有3个表：accounts、contacts和accounts_contacts（映射表）。我每张表上都有大约100万张唱片。此查询使用文件排序，运行需要一分钟以上的时间：

explain SELECT contacts.salutation salutation, contacts.first_name first_name, contacts.last_name last_name, contacts.title title, jt0_accounts.id account_id, jt0_accounts.name account_name
FROM contacts
LEFT JOIN accounts_contacts jt1_accounts_contacts ON (contacts.id = jt1_accounts_contacts.contact_id AND jt1_accounts_contacts.deleted = 0)
LEFT JOIN accounts jt0_accounts ON (jt0_accounts.id = jt1_accounts_contacts.account_id AND jt0_accounts.deleted = 0)
ORDER BY jt0_accounts.name DESC;

这是解释输出：

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1    SIMPLE contacts    ALL NULL    NULL    NULL    NULL    195634  Using temporary; Using filesort
1    SIMPLE jt1_accounts_contacts   ref idx_contid_del_accid    idx_contid_del_accid    113 sugar7.contacts.id,const    1   
1   SIMPLE  jt0_accounts    eq_ref  PRIMARY,idx_accounts_id_del,idx_accounts_date_entered,idx_accnt_assigned_del    PRIMARY 108 sugar7.jt1_accounts_contacts.account_id 1   

正如您所看到的，联系人表正在使用联系人表上的文件排序。

我已经尝试通过在"ORDER by"之前添加"WHERE jt0_accounts.name<>''"来消除文件排序，因此它变成：

explain SELECT contacts.salutation salutation, contacts.first_name first_name, contacts.last_name last_name, contacts.title title, jt0_accounts.id account_id, jt0_accounts.name account_name
FROM contacts
LEFT JOIN accounts_contacts jt1_accounts_contacts ON (contacts.id = jt1_accounts_contacts.contact_id AND jt1_accounts_contacts.deleted = 0)
LEFT JOIN accounts jt0_accounts ON (jt0_accounts.id = jt1_accounts_contacts.account_id AND jt0_accounts.deleted = 0)
WHERE jt0_accounts.name <> ''
ORDER BY jt0_accounts.name DESC;

它确实去掉了contacts表上的filesort，但现在使用映射表上的fileort：

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  jt1_accounts_contacts   ALL idx_account_contact,idx_contid_del_accid    NULL    NULL    NULL    34994   Using where; Using temporary; Using filesort
1   SIMPLE  jt0_accounts    eq_ref  PRIMARY,idx_accounts_id_del,idx_accounts_date_entered,idx_accnt_name_del,idx_accnt_assigned_del PRIMARY 108 sugar7.jt1_accounts_contacts.account_id 1   Using where
1   SIMPLE  contacts    eq_ref  PRIMARY,idx_contacts_id_del,idx_contacts_date_entered   PRIMARY 108 sugar7.jt1_accounts_contacts.contact_id 1   Using where

idx_account_contact索引由account_id和contacts_id组成。我试着将它们添加到WHERE子句中，但似乎没有任何区别。

任何建议都将不胜感激。谢谢

Answer 1

对于特定的查询，您可能无能为力。但是，如果您将查询更改为使用inner join，您可能有机会：

SELECT c.salutation, c.first_name, c.last_name, c.title,
       a.id as account_id, a.name as account_name
FROM accounts a JOIN
     accounts_contacts ac
     ON a.id = ac.account_id AND a.deleted = 0
     contacts c JOIN
     ON c.id = ac.contact_id AND ac.deleted = 0     
ORDER BY a.name DESC;

然后，尝试以下索引：accounts(name, deleted, id)、accounts_contacts(account_id, contact_id)和concats(contact_id, deleted)。