挑战：这个SQL该如何优化性能最佳？

Question

这个SQL该如何优化性能最佳，

select *
from customer
where customer.c_custkey = (select max(orders.o_custkey)
                      from orders
                      where subdate(orders.o_orderdate, interval '1' DAY) < '2022-12-20')

表结构：

CREATE TABLE `customer` (
`C_CUSTKEY` int NOT NULL,
`C_NAME` varchar(25) NOT NULL,
`C_ADDRESS` varchar(40) NOT NULL,
`C_NATIONKEY` int NOT NULL,
`C_PHONE` char(15) NOT NULL,
`C_ACCTBAL` decimal(15,2) NOT NULL,
`C_MKTSEGMENT` char(10) NOT NULL,
`C_COMMENT` varchar(117) NOT NULL,
 PRIMARY KEY `PK_IDX1614428511` (`C_CUSTKEY`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

  -- tpch.orders definition
CREATE TABLE `orders` (
`O_ORDERKEY` int NOT NULL,
`O_CUSTKEY` int NOT NULL,
`O_ORDERSTATUS` char(1) NOT NULL,
`O_TOTALPRICE` decimal(15,2) NOT NULL,
`O_ORDERDATE` date NOT NULL,
`O_ORDERPRIORITY` char(15) NOT NULL,
`O_CLERK` char(15) NOT NULL,
`O_SHIPPRIORITY` int NOT NULL,
`O_COMMENT` varchar(79) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

Answer 1

1.加索引：

CREATE INDEX idx_orders_orderdate_custkey ON orders(o_orderdate, o_custkey);

2.改写查询

SELECT c.*
FROM customer c
JOIN (
  SELECT MAX(o.o_custkey) AS max_custkey
  FROM orders o
  WHERE o.o_orderdate < DATE_SUB('2022-12-20', INTERVAL 1 DAY)
) o_max ON c.c_custkey = o_max.max_custkey;

Answer 2

1. 使用JOIN代替子查询：将子查询转换为JOIN操作，避免每次迭代customer表。

SELECT c.*
FROM customer c
JOIN (
    SELECT MAX(o.o_custkey) AS max_custkey
    FROM orders o
    WHERE o.o_orderdate < '2022-12-20' - INTERVAL 1 DAY
) sub ON c.c_custkey = sub.max_custkey;

2. 添加索引：为customer表的c_custkey列和orders表的o_custkey和o_orderdate列添加索引，加快查询速度。

ALTER TABLE customer ADD INDEX idx_custkey (c_custkey);
ALTER TABLE orders ADD INDEX idx_custkey (o_custkey);
ALTER TABLE orders ADD INDEX idx_orderdate (o_orderdate);