目录

摘要:

开场白：

一. subquery（子查询)是什么

子查询定义:

为什么需要子查询:

子查询的种类:

二. 为何subquery会成为性能瓶颈

mysql查询处理过程:

发生性能瓶颈的因素:

由资源冲突引发:

由资源耗尽引发:

nested loop引发的

mysql8 子查询优化增强

Join Algorithms

Join Optimizations

主要join算法对比:

stonedb中实际出现的慢子查询:

三. StoneDB对于subquery的性能提升

查询优化方法论

性能分析工具:

以一个慢子查询的例子分析出平静点：

慢子查询explain分析:

性能瓶颈点:

热力图分析：

解决性能瓶颈

优化后的慢SQL的explain分析：

优化后耗时对比:

优化后的热力图分析：

结束语:

摘要:

子查询作为SQL语言中重要的组成部分, 正是子查询使SQL语言真正的成为结构化的查询语言，使SQL的应用范围前所未有的广泛。尤其是OLAP场景，由于业务的复杂性，导致存在大量的子查询。子查询也成为了关系型数据库中执行最为复杂及耗时的部分，如何提升子查询的性能，对于一款OLAP的关系型数据库起到决定性作用。

结合我们stonedb项目对于子查询的实际的优化子查询的经验，开启对于子查询性能提升的讲座系列，希望大家能从我们项目实际的对子查询的优化上，能吸取到经验，也让大家了解到我们公司对于技术的专注以及对于产品精益求精的打磨。

开场白：

我先自我介绍一下吧，我叫悟世，目前在stonedb开发部，然后担任数据库研发工程师岗位。所以一直在做subquery子查询性能提升这个事情。子查询是在整个数据库里边非常重要的，也是涉及到整个数据库性能一个比较核心的地方。所以是专门是针对这个事情，然后以及结合在做的事情以及最应该解决的问题, 开了一系列讲座，目的就在于, 就是我有一个理念: 不但要把事情做正确，更重要的，是要用正确的方式把事情做正确。

把这个事情公开出来。目的就是一方面哦，让大家看到这个事情做的结果一个输出。更重要的啊，更重要就是让大家看到我们是怎么解决这个问题啊，然后我们的方式方法，然后呢，给大家一个参考。

一. subquery（子查询)是什么

在解决子查询他的性能问题之前，首先在定义清楚子查询他究竟是是什么，它的边界在哪？如果根据mysql自己的定义，就是子查询是某个语句中的SELECT语句.

子查询定义:

子查询是某个语句中的SELECT语句

A subquery is a SELECT statement within another statement.

例如:

SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);

为什么需要子查询:

1. 允许结构化查询，以便可以隔离语句的每个部分；
2. 提供了执行操作的替代方法，否则这些操作需要复杂的连接和联合；
3. 子查询比复杂的连接或联合更具可读性。正是子查询使SQL成为“结构化查询语言”。

子查询的种类:

• Subquery as Scalar Operand
• Comparisons Using Subqueries
• Subqueries with ANY, IN, or SOME
• Subqueries with ALL
• Row Subqueries
• Subqueries with EXISTS or NOT EXISTS
• Correlated Subqueries

二. 为何subquery会成为性能瓶颈

mysql查询处理过程:

在阐述为什么子查询会成为性能瓶颈前，有必要大致了解下mysql是如何处理查询的。在理解查询处理的每一步处理的基础上，找到会引发性能问题的所在。

这个图表大家应该都非常清楚。做完词法分析和语法分析以及语法检查，执行预处理， 再到查询优化，之后进入执行器执行。

发生性能瓶颈的因素:

由资源冲突引发:

• 当用户数量增加, 应用要求大量的并发管理
• 增加的锁活动增加的数据一致性压力
• 增加的操作系统压力
• 由于数据量增加, 每个事务要求增加数据访问次数
• 糟糕的查询优化器设计导致返回同样数量的行需要更多的逻辑IO
• 本该并行的处理被串行执行

由资源耗尽引发:

• 硬件资源被耗尽
• 大量的表扫描引起IO短缺
• 内存的不合理分配导致页面被换入换出
• 过多的进程和线程导致操作系统超负荷运转

nested loop引发的

Until MySQL 5.6 nested loop was also the only algorithm available. As the name suggests, it works by nesting loops with one loop for each table in the join.

MySQL :: MySQL 5.6 Reference Manual :: 8.8.2 EXPLAIN Output Format

mysql到5.6版本位置，只有nested loop策略。说直白点其实就是两个for循环，比如我们看下右边这个图。左边是一个右边一个表，左边当做一个offer，一边当做查询的时候，左边每查询银行都要便利，右边这些所有的行，看看是不是符合条件。然后可以看左边的伪代码。就是其实是一个n的平方的复杂度。

这是非常粗暴的一个算法。没有经过任何优化。就类似于冒泡排序那种复杂度。但是在后续版本做了一定程度的优化。

mysql8 子查询优化增强

mysql8 子查询优化增强

MySQL :: MySQL 8.0 Reference Manual :: 8.2.1 Optimizing SELECT Statements

Join Algorithms

• Nested Loop
• Block Nested Loop
• Hash Join

Join Optimizations

• Index Merge
• Multi-Range Read (MRR)
• Batched Key Access (BKA)
• Other Optimizations

主要join算法对比:

stonedb中实际出现的慢子查询:

以下是在我们项目中真实出现的一些慢SQL，基于tpch标准测试，后边跟随的号标是tpch的语句的序列。

大家可以看一下这些类别的慢子查询，思考下mysql中是如何执行的。

三. StoneDB对于subquery的性能提升

当系统被设计好的同时, 系统的性能也就被一起设计好了

——by《Oracle方法论》

在讲之前，然后我觉得对于性能问题要树立一个概念，就是系统。系统性能本身设计的一部分，如果一个系统性能出现问题。一定是在设计这个系统的时候，有些地方没有考虑到或者有的地方设计的不好，导致爆发出来，不是偶然出现的。
所以一开始在设计一个系统时候，就要把其当做整体的一部分去思考。

查询优化方法论

1. 验证问题是什么，包括收集问题的证据，并定义考虑已解决的问题的需求是什么
2. 确定导致性能问题的原因
3. 确定解决方案
4. 验证更改的效果

需要注意:

1. 性能分析必须要以数据说话，所谓的慢，必须要能够被定性, 可以借助一些工具进行
2. 解决问题的过程是一个螺旋上升的过程

性能分析工具:

• explain分析
• phhmyadmin或mysql enterprise monitor
• perf火焰图
• 业务日志埋点

以一个慢子查询的例子分析出平静点：

慢子查询explain分析:

性能瓶颈点:

1. nested loop
2. 全表扫描

热力图分析：

解决性能瓶颈

1. 列式存储以减少磁盘IO
2. 避免Nested loop
3. 增强并行处理能力

优化后的慢SQL的explain分析：

优化后耗时对比:

1. 从3分13秒优化到了14秒

优化后的热力图分析：

结束语:

本讲作为概论引出子查询的性能瓶颈问题，并以一个简单的优化子查询的例子，展示子查询优化可以对sql执行的巨大提升。后续将对其中的技术点做着重展开，敬请关注！