Copyright © 2024 PawSQL

定义​

在日常开发中，有部分开发人员使用关联标量子查询来进行是否存在的判定，譬如下面的SQL查询有订单的用户列表，

select * from customer where (select count(*) from orders where c_custkey=o_custkey) > 0

这类查询有比较严重的性能问题，它需要对外表的每一条记录，进行一次聚集运算。从上面SQL的执行计划可以看到，它的执行时间为4820.015 ms.

Copyright © 2024 PawSQL

问题定义​

Let's say the O_ORDERDATE column in TPCH.ORDERS table is defined as VARCHAR(16), and there is an index ORDDATE_IDX on ORDERS(O_ORDERDATE) to accelerate the queries against this table if there is a condition on O_ORDERDATE. If we want to count the number of orders of today using following SQL query.

Copyright © 2024 PawSQL

Problem​

Let's say we have a b-tree index c_acctbal_idx defined on table tpch.customer to accelarate queries with a condition on column c_acctbal. Let's check a query as follows:

select * from tpch.customer where c_acctbal + 100 = 10000.0

Use explain to get the query plan:

问题定义​

为了获取最近一年内有订单的用户信息，可以使用以下的三种写法去实现，它们在语义上是等价的。那它们的性能如何？如何对它们进行优化？这是本文讨论的问题。

• Query1 - IN子查询(= ANY)

select * from customer where c_custkey in (select o_custkey from orders where O_ORDERDATE>=current_date - interval 1 year)

• Query2 - EXISTS子查询

select * from customer where exists (select * from orders where c_custkey = o_custkey and O_ORDERDATE>=current_date - interval 1 year)

定义​

连接消除（Join Elimination）通过在不影响最终结果的情况下从查询中删除表，来简化SQL以提高查询性能。通常，当查询包含主键-外键连接并且查询中仅引用主表的主键列时，可以使用此优化。

考虑下面的例子，

select o.* from orders o inner join customer c on c.c_custkey=o.o_custkey

订单表（orders）和客户表（customer）关联，且c_custkey是客户表的主键，那么客户表可以被消除掉，重写后的SQL如下：