SQL高级之子查询优化

in 与 exists

有索引

1. 在有索引的情况下用inner join是最好的，其次是in，exists最糟糕

无索引

• 小表驱动大表，因为join方式需要distinct，没有索引distinct消耗性能较大
  • 所以exists性能最佳，in其次，join性能最差
• 大表驱动小表
  • in和exists的性能应该是接近的，都比较糟糕，exists稍微要好一点，超不过5%
  • 但是inner join由于使用了join buffer所以快很多

总结

in，exists用join替代

假设有一个名为orders的表，其中包含订单信息，以及一个名为customers的表，其中包含客户信息。现在，我们想要查询所有订单的总金额，但只包括那些来自特定客户的订单。

原始查询可能如下所示：

SELECT SUM(order_amount) 
FROM orders 
WHERE customer_id IN (SELECT customer_id FROM customers WHERE customer_name = 'John');

这个查询中包含一个子查询，用于获取名为John的客户的所有订单。由于这个子查询在每一行上都执行一次，查询可能会变得很慢。

优化后的查询可能如下所示：

SELECT SUM(o.order_amount) 
FROM orders o 
JOIN customers c ON o.customer_id = c.customer_id 
WHERE c.customer_name = 'John';

在这个优化后的查询中，我们使用了JOIN操作来连接orders和customers表，并在WHERE子句中直接过滤出名为John的客户。这样可以避免使用子查询，从而提高查询性能。

使用索引优化子查询

假设我们有两个表：orders（订单）和customers（客户）。orders表有order_id（订单ID）、customer_id（客户ID）和order_date（订单日期）等字段，而customers表有customer_id（客户ID）和customer_name（客户名称）等字段。

现在，我们想要找出在2023年下了超过10个订单的客户。没有索引的查询可能如下所示：

SELECT customer_name 
FROM customers 
WHERE customer_id IN (
    SELECT customer_id 
    FROM orders 
    WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31' 
    GROUP BY customer_id 
    HAVING COUNT(*) > 10
);

这个查询首先会找出在2023年下了订单的所有客户，并计算每个客户的订单数量。然后，它会返回那些订单数量超过10的客户的名称。由于子查询需要对orders表进行全表扫描，并且还需要进行分组和计数操作，因此这个查询可能会非常慢。

为了优化这个查询，我们可以在orders表的customer_id和order_date列上创建索引，如下所示：

CREATE INDEX idx_customer_id ON orders (customer_id);
CREATE INDEX idx_order_date ON orders (order_date);

这些索引将允许数据库更快地定位到特定的客户和订单日期，从而提高查询性能。优化后的查询将利用这些索引，如下所示：

SELECT customer_name 
FROM customers 
WHERE customer_id IN (
    SELECT customer_id 
    FROM orders USE INDEX (idx_customer_id, idx_order_date)
    WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31' 
    GROUP BY customer_id 
    HAVING COUNT(*) > 10
);

在这个优化后的查询中，我们使用了USE INDEX语句来指示数据库使用我们创建的索引。这将大大加快查询的执行速度，因为数据库可以更快地定位到满足条件的客户和订单。