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

2024-08-20 15:01:27 浏览数 (1)

最近在知乎上发起了一个SQL优化挑战赛，其中题目2用到了多个重写优化算法以及索引创建的策略。本文讲解了详细的优化分析过程，涉及SQL优化的多个方面，包括索引查找、避免回表、驱动表选择、索引避免排序，以及两种重写优化的应用。

注：本文所使用的执行计划可视化工具为PawSQL Explain Visualizer (https://app.pawsql.com/explain), 支持MySQL、PostgreSQL、openGauss等数据库。目前处于β测试阶段, 用户可以免登录使用，如果需要保存历史，需注册PawSQL Cloud账号。

问题描述

题目：下面的SQL如何优化性能最佳（以MySQL数据库8.0.26版本为例）

代码语言：javascript复制
select o_custkey, c_name, sum(o.O_TOTALPRICE)
from customer c, orders o 
where o_custkey = c_custkey 
group by o_custkey, c_name
order by c_name;

表定义如下：

代码语言：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分析

由于关联条件o_custkey上无索引而c_custkey上有索引，从下面的执行计划可以看到，优化器会将orders作为驱动表，执行时间为580.661ms。

2. 优化第一步

  • 目标: 用小表customer驱动大表orders
  • 优化方法：在orders表的关联字段上添加一个索引， create index o_custkey_idx on orders(o_custkey);
  • 优化效果: 从执行计划可以看到，创建索引后表关联顺序改变了，执行时间变为255.78ms，性能提升127.02%

3. 优化第二步

  • 目标：避免customer全表扫描（上图#5）
  • 优化方法：在customer表上创建覆盖索引 create index c_key_name_idx on customer(c_custkey, c_name);
  • 优化效果: 可以看到全表扫描变为全索引扫描，避免了回表，执行时间变为222.879ms，性能提升14.76%。

4. 优化第三步

  • 目标：避免使用临时表进行聚集运算（上图#3节点）
  • 优化方法：将groupby及select列表的o_custkey字段更换为c_custkey字段 select c_custkey, c_name, sum(o.O_TOTALPRICE) from customer c, orders o where o_custkey = c_custkey group by c_custkey, c_name order by c_name;
  • 优化效果: 新的执行计划利用索引的有序性，减少了一个临时表来做分组，执行时间变为168.694ms，性能提升38.13%。

5. 优化第四步

  • 目标：避免对c_name的排序（上图#1节点）
  • 优化方法：

1. 调整索引c_key_name_idx索引字段的顺序，将c_name放在前面；

2. 调整分组列的顺序，将c_name放在c_custkey前面；

代码语言：javascript复制
create index c_name_key_idx on customer(c_name, c_custkey)
代码语言：javascript复制
select c_custkey, c_name, sum(o.O_TOTALPRICE)
from customer c, orders o 
where o_custkey = c_custkey 
group by c_name, c_custkey
order by c_name;
  • 优化效果: 最后一步对c_name的排序消失了，执行时间变为164.180ms，性能提升2.75%.

过程总结

我们通过四步优化，改变了表关联的顺序、消除了全表扫描、避免了回表、避免了临时表聚集运算、避免了排序，执行时间从580.661 ms - > 255.78 ms - > 222.879 ms - >168.694 ms - > 164.180 ms, 性能提升了263.65%，且减少了聚集排序使用的内存。

使用PawSQL优化的惊喜

我们将待优化SQL直接提交到PawSQL，让其给我们做自动优化。从给出的优化详情可以看到，上面的四步优化过程，PawSQL都自动完成了；事实上，它更进一步，发现了人为疏漏的一个优化点：为orders创建了一个覆盖索引，整体的性能提升了766%。

  • PawSQL自动优化后的执行计划如下图：
  • 附录：PawSQL的优化详情

关于PawSQL

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

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

0 人点赞