SQL面试题：一个优化案例-Toy模板网

这篇具有很好参考价值的文章主要介绍了SQL面试题：一个优化案例。希望对大家有所帮助。如果存在错误或未考虑完全的地方，请大家不吝赐教，您也可以点击"举报违法"按钮提交疑问。

问题描述

假如存在以下两个表：

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 (`C_CUSTKEY`)
) ENGINE=InnoDB;

  -- 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,
PRIMARY KEY (`O_ORDERKEY`)
) ENGINE=InnoDB;

请问，下面的查询语句应该如何优化？

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

思路分析

首先，customer 表的 c_custkey 字段是主键，查询条件是等值查找，已经最优了。

其次，子查询的条件字段没有索引，需要全表扫描。看一下执行计划：

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

id|select_type|table |partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra                         |
--+-----------+------+----------+----+-------------+---+-------+---+----+--------+------------------------------+
 1|PRIMARY    |      |          |    |             |   |       |   |    |        |no matching row in const table|
 2|SUBQUERY   |orders|          |ALL |             |   |       |   |   1|   100.0|Using where                   |

因此主要优化这个子查询。

第一步，考虑子查询使用 o_orderdate 作为条件，可以基于这个字段创建索引，同时需要把 subdate 函数放到表达式右边，否则会导致索引失效。

CREATE INDEX idx_orders_date ON orders(o_orderdate);

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

id|select_type|table |partitions|type |possible_keys  |key            |key_len|ref|rows|filtered|Extra                         |
--+-----------+------+----------+-----+---------------+---------------+-------+---+----+--------+------------------------------+
 1|PRIMARY    |      |          |     |               |               |       |   |    |        |no matching row in const table|
 2|SUBQUERY   |orders|          |range|idx_orders_date|idx_orders_date|3      |   |   1|   100.0|Using index condition         |

对于 orders 表的访问类型变成了索引范围扫描（range），但是仍然不够理想，因为扫描完索引之后还需要回表查询 max(o_custkey)。

第二步，考虑利用覆盖索引优化，避免回表。可以基于 o_orderdate 和 o_custkey 创建一个复合索引。

CREATE INDEX idx_orders_date_cust ON orders(o_orderdate, o_custkey);

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

id|select_type|table |partitions|type |possible_keys                       |key                 |key_len|ref|rows|filtered|Extra                         |
--+-----------+------+----------+-----+------------------------------------+--------------------+-------+---+----+--------+------------------------------+
 1|PRIMARY    |      |          |     |                                    |                    |       |   |    |        |no matching row in const table|
 2|SUBQUERY   |orders|          |index|idx_orders_date,idx_orders_date_cust|idx_orders_date_cust|7      |   |   1|   100.0|Using where; Using index      |