如何优化此查询，执行时间超过一分钟 [英] How can I optimize this query, takes more than a min to execute

问题描述

我有以下查询需要一分钟多的时间来执行，我该如何优化它.由于 order by o.id desc，它很慢，如果我删除它，查询执行它几毫秒.

<预><代码>选择 o.*, per.email, p.name从订单 o内连接积 po.product_id=p.id内连接人每o.person_id=per.id按 o.id desc 排序限制 100；

以下是解释的结果

<预><代码>1 SIMPLE p index PRIMARY FK2EFC6C1E5DE2FC 8 NULL 6886 使用索引；使用临时；使用文件排序1 简单 o ref FK67E9050121C383DB,FK67E90501FC44A17C FK67E90501FC44A17C 8 dev.p.id 581 SIMPLE per eq_ref PRIMARY PRIMARY 8 dev.o.person_id 1 使用索引

所有表都是 InnoDB 并且连接在主键和外键上.除了索引位于Person的email列和Order

的status列

每张表的记录数

人:1,300,000产品:7,000订单:70,000

解决方案

规划器很可能没有使用 limit 提示在连接之前从订单表中删除行.所以服务器必须对所有行进行连接，然后只返回几行.

试试这个:

select o.* from(select * order order by id desc limit 100) o内连接积 po.product_id=p.id内连接人每o.person_id=per.id按 o.id desc 排序限制 100；

只有当有一个约束保证产品和人员表中存在相应的行时，这才有效.

I have following query which take more than a minute to execute, how can I optimize it. Its slow because of order by o.id desc, if I remove it query executes it few ms.

select o.*, per.email, p.name
from order o 
inner join product p 
on o.product_id=p.id 
inner join person per 
on o.person_id=per.id 
order by o.id desc 
limit 100;

Following is the result of explain

1   SIMPLE  p   index   PRIMARY FK2EFC6C1E5DE2FC    8   NULL    6886    Using index; Using temporary; Using filesort
1   SIMPLE  o   ref FK67E9050121C383DB,FK67E90501FC44A17C   FK67E90501FC44A17C  8   dev.p.id    58  
1   SIMPLE  per eq_ref  PRIMARY PRIMARY 8   dev.o.person_id 1   Using index

All the tables are InnoDB and joins are on Primary and Foreign keys. Other than that indexes are on email column in Person and status column in Order

Number of records in each table

Person : 1,300,000 Product: 7,000 Order : 70,000

解决方案

The planner, most probably, is not using the limit hint to eliminate rows from order table before the join. So the server has to do the join for all rows and then return just a few.

Try this:

select o.* from
(select * order order by id desc limit 100) o
inner join product p 
on o.product_id=p.id 
inner join person per 
on o.person_id=per.id 
order by o.id desc limit 100;

EDIT: This will work only if there is a constraint guaranteeing that corresponding rows are present in Product and Person tables.

这篇关于如何优化此查询，执行时间超过一分钟的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！