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

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

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

问题描述

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

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;

表定义如下：

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. 优化第二步

• 目标：避免全表扫描（上图#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放在前面

  create index c_name_key_idx on customer(c_name, c_custkey);
  

  2. 调整分组列的顺序，将c_name放在前面,调整后的SQL如下：

  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等，提供的SQL优化产品包括

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