我想优化我的sql查询.因为响应时间长

Question

首先İ使用wherehas，但后来我决定使用这种方式。这种方法比以前的方法效果好，但我不满意。查询响应时间为873ms。表中有400k+数据

select count(*) as aggregate 
from `orders` 
where (`pickup_address_id` in (
select `id` 
from `addresses` 
where `region_id` = 12) 
or `delivery_address_id` in (
select `id` 
from `addresses` 
where `region_id` = 12)
) and `orders`.`status` = 2

Answer 1

试试这个:

select count(distinct o.`id`) as aggregate 
from `orders` o
inner join `addresses` a ON a.`id` IN (o.`pickup_address_id`, o.`delivery_address_id`)
AND a.`region_id` = 12
where o.`status` = 2

另外:

SELECT count(distinct id) as aggregate
FROM (
select o.`id`
from `orders` o
inner join `addresses` a ON a.`id` = o.`pickup_address_id`
AND a.`region_id` = 12
where o.`status` = 2
UNION
select o.`id` 
from `orders` o
inner join `addresses` a ON a.`id` = o.`delivery_address_id`
AND a.`region_id` = 12
where o.`status` = 2
) t

但是我不知道在不到一秒的时间内查看400K行会有多大的提高。

Answer 2

首先，您可以尝试使用Common Table Expression

消除多个(更准确地说是两次)相同的子查询计算。

WITH CTE(id) AS (
SELECT id 
FROM addresses
WHERE region_id = 12
)

此CTE将计算一次。

其次，在cte中存在pickup_address_id和delivery_address_id的条件下，从orders表中得到与cte联接的行数。

WITH CTE(id) AS (
SELECT id 
FROM addresses
WHERE region_id = 12
)
SELECT COUNT (*)
FROM orders
CROSS JOIN CTE ON CTE.id = orders.delivery_address_id
OR CTE.id = orders.pickup_address_id

最后，通过status = 2添加过滤器，查询将像

WITH CTE(id) AS (
SELECT id 
FROM addresses
WHERE region_id = 12
)
SELECT COUNT (*)
FROM orders
CROSS JOIN CTE ON CTE.id = orders.delivery_address_id
OR CTE.id = orders.pickup_address_id
WHERE orders.status = 2

还应该有以下索引:

addresses:

INDEX (region_id)

orderstable:

INDEX (pickup_address_id),
INDEX (delivery_address_id),
INDEX (status)

试一试。

对于空表，我得到了这个

Schema (MySQL v8.0)

create table addresses (
id int primary key,
region_id int not null,
index(region_id)
);

create table orders (
id int primary key,
pickup_address_id int,
delivery_address_id int,
status int not null,
index (pickup_address_id),
index (delivery_address_id),
index(status),
foreign key (pickup_address_id) references addresses(id),
foreign key (delivery_address_id) references addresses(id)
);

---

查询# 1

explain with cte(id) as (
select id from addresses where region_id = 12)
select count(*) from orders 
cross join cte on cte.id = orders.delivery_address_id
or cte.id = orders.pickup_address_id
where status = 2;

<表类>idselect_type表分区类型possible_keys键key_lenref行过滤额外tbody><<tr>1简单订单refpickup_address_id, delivery_address_id、地位状态4const11001简单地址ref小学,region_idregion_id4const1100使用的地方;使用指数