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

Question

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

select *from customerwhere 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 definitionCREATE 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. 使用JOIN代替子查询：将子查询转换为JOIN操作，避免每次迭代customer表。

SELECT c.*FROM customer cJOIN (    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);

Answer 2

1.加索引：

CREATE INDEX idx_orders_orderdate_custkey ON orders(o_orderdate, o_custkey);

2.改写查询

SELECT c.*FROM customer cJOIN (  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;