知乎SQL优化挑战赛 - 题目1解析

2024-08-20 15:24:50 浏览数 (2)

最近在知乎上发起了一个SQL优化挑战赛，本文为题目1的解析。其中涉及索引失效，修饰子查询重写等优化知识点，希望对大家在学习优化SQL的过程中有所帮助。

本文所使用的执行计划可视化工具为 PawSQL Explain Visualizer , 支持MySQLPostgreSQLopenGauss等数据库，用户可以免登录使用。

问题描述

题目：下面的SQL如何优化性能最佳

代码语言：javascript复制
select * from customer 
where c_custkey = ( select max(o_custkey) from orders 
                   where subdate(o_orderdate, interval '1' DAY) < '2022-12-20')

表定义如下：

代码语言：javascript复制
create table customer (c_custkey int not null,c_name varchar(25),c_address varchar(40),c_nationkey int,c_phone char(15),c_acctbal decimal(15,2),c_mktsegment char(10),c_comment varchar(117), primary key pk_idx1614428511 (c_custkey)
) engine=innodb default charset=utf8mb4 collate=utf8mb4_bin;

-- tpch.orders definition
create table orders (o_orderkey int,o_custkey int,o_orderstatus char(1),o_totalprice decimal(15,2),o_orderdate date,o_orderpriority char(15),o_clerk char(15),o_shippriority int,o_comment varchar(79), primary key (o_orderkey)
) engine=innodb default charset=utf8mb4 collate=utf8mb4_0900_ai_ci;

优化过程

1. 原SQL分析

  1. 首先这是一个非相关标量子查询，只需要计算一次，且c_cuskey上有唯一性索引，所以对于外表来说，已无优化空间。

2. 优化第一步

  • 目标: 子查询使用 o_orderdate 作为过滤条件，可以基于这个字段创建索引，同时需要把 subdate 函数放到表达式右边，否则会导致索引失效。
  • 优化方法：在orders表的字段上添加一个覆盖索引，并重写SQL CREATE INDEX date_custkey_idx ON public.orders USING btree (o_orderdate, o_custkey); 重写后的SQL: select * from customer where c_custkey = ( select max(o_custkey) from orders where o_orderdate < date('2022-12-20') interval'1 DAY');
  • 优化效果: 全表扫描变成了索引扫描，且避免了回表，执行时间变为0.961ms，性能提升387.02%

3. 优化第二步

  • 目标：利用索引的有序性，只取所需的第一条记录
  • 方法：在orders表上新增不同顺序的一个覆盖索引，同时通过order by limit 1重写max函数 CREATE INDEX date_custkey_idx2 ON public.orders USING btree (o_custkey, o_orderdate); 重写后的SQL: select * from customer where c_custkey = ( select o_custkey from orders where o_orderdate < date('2022-12-20') interval'1 DAY' order by o_custkey desc limit 1);
  • 优化效果: 可以看到全表扫描变为全索引扫描，避免了回表，执行时间变为0.09ms，性能提升1000%。

过程总结

优化第一步选择 (o_orderdate, o_custkey)索引，通过覆盖索引获取数据时，需要找出 o_orderdate 小于 2022-12-21 的所有索引节点，然后遍历其中的 o_custkey，找出最大的值。优化第二部选择 (o_custkey, o_orderdate)，通过覆盖索引获取数据时，需要按照 o_custkey 从大到小查找索引，找出其中 o_orderdate 小于 2022-12-21 的第一个索引节点即可。

PawSQL的自动优化

我们将待优化SQL直接提交到PawSQL，让其给我们做自动优化。从输出的优化详情页面我们可以看到，PawSQL自动帮我们进行了以下两个重写优化

  • MAX/MIN子查询重写
  • 索引列上的运算导致索引失效

并且根据重写后的SQL推荐了对应的索引。使用PawSQL，真正做到了一键优化！

PawSQL Cloud关于题目1的优化详情:

关于PawSQL

PawSQL专注数据库性能优化的自动化和智能化，支持MySQL，PostgreSQL，Opengauss等，提供的SQL优化产品包括

  • PawSQL Cloud，在线自动化SQL优化工具，支持SQL审查，智能查询重写、基于代价的索引推荐，适用于数据库管理员及数据应用开发人员，
  • PawSQL Advisor，IntelliJ 插件, 适用于数据应用开发人员，可以IDEA/DataGrip应用市场通过名称搜索“PawSQL Advisor”安装。
  • PawSQL Engine, 是PawSQL系列产品的后端优化引擎，可以以docker镜像的方式独立安装部署，并通过http/json的接口提供SQL优化服务。
sql 工具 索引 性能 优化

0 人点赞