SQL优化-慢查询
SQL优化-慢查询
提问于 2013-04-16 18:11:24
给定SQL耗时1.2s：
SELECT DISTINCT contracts.id, jt0.id, jt1.id, jt2.id, jt3.id FROM contracts
LEFT JOIN accounts jt0 ON jt0.id = contracts.account_id AND jt0.deleted=0
LEFT JOIN manufacturers jt1 ON jt1.id = contracts.manufacturer_id AND jt1.deleted=0
LEFT JOIN products jt2 ON jt2.id = contracts.product_id AND jt2.deleted=0
LEFT JOIN users jt3 ON jt3.id = contracts.assigned_user_id AND jt3.deleted=0
WHERE contracts.deleted=0
ORDER BY contracts.application_number ASC
LIMIT 0,21下面是对扩展返回的解释：
id select_type table type possible_keys key key_len ref rows
1 SIMPLE contracts ref idx_contracts_deleted idx_contracts_deleted 2 const 18968 100.00 Using where; Using temporary; Using filesort
1 SIMPLE jt0 eq_ref PRIMARY,idx_accnt_id_del,idx_accnt_assigned_del PRIMARY 108 xxx.contracts.account_id 1 100.00
1 SIMPLE jt1 eq_ref PRIMARY,idx_manufacturers_id_deleted,idx_manufacturers_deleted PRIMARY 108 xxx.contracts.manufacturer_id 1 100.00
1 SIMPLE jt2 eq_ref PRIMARY,idx_products_id_deleted,idx_products_deleted PRIMARY 108 xxx.contracts.product_id 1 100.00
1 SIMPLE jt3 eq_ref PRIMARY,idx_users_id_del,idx_users_id_deleted,idx_users_deleted PRIMARY 108 xxx.contracts.assigned_user_id 1 100.00 我需要distinct，我需要保留所有的连接，我需要order by，我需要limit。
我能以某种方式优化它吗？
回答 4
Stack Overflow用户
发布于 2013-04-16 18:18:07
这些是我得到的唯一建议
- 我希望id被定义为主键和外键与表之间的关系。
- 也许application_number可以被索引(这样排序会更快)
- 也许，如果你正在使用MyISAM，如果你在选择
之前锁定表(以后不要忘记解锁)，sql可能会更快。
Stack Overflow用户
发布于 2013-04-16 18:30:41
首先在以下列上创建必要的索引。
contracts.application_number、manufacturers.deleted、products.deleted、users.deleted
SELECT DISTINCT contracts.id, jt0.id, jt1.id, jt2.id, jt3.id
FROM contracts
LEFT JOIN accounts jt0
ON contracts.deleted=0 AND jt0.id = contracts.account_id
LEFT JOIN manufacturers jt1
ON jt1.deleted=0 AND jt1.id = contracts.manufacturer_id
LEFT JOIN products jt2
ON jt2.deleted=0 AND jt2.id = contracts.product_id
LEFT JOIN users jt3
ON jt3.deleted=0 AND jt3.id = contracts.assigned_user_id
ORDER BY contracts.application_number ASC
LIMIT 0,21正如你所提到的，你已经在contracts.deleted上建立了索引
FROM
(SELECT * FROM contracts WHERE contracts.deleted = 0 USE INDEX(<deletedIndexName>))
LEFT JOIN
accounts jt0
ON
jt0.id = contracts.account_id
LEFT JOIN
...Stack Overflow用户
发布于 2013-04-16 18:54:01
尝试更改subsidiary表的索引，以包括deleted列：
accounts(id, deleted)manufacturers(id, deleted)products(id, deleted)users(id, deleted)
通过包含索引中的所有列，MySQL有更好的机会利用索引。
另一个建议是找出导致值重复的原因，并使用子查询来消除重复，而不是使用distinct。
例如，使用上面的索引：
from contracts c left join
(select id
from accounts
where deleted = 0
group by id
) a
on c.account_id = a.id
. . .子查询应该只使用索引，这可能会加快速度。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/16034307
复制相关文章
相似问题
腾讯云开发者
