深入浅出 MySQL SQL 优化指南
先生，在软件世界中 先进的算法并没有完全淘汰糟糕的算法，先进的算法和糟糕的算法总是并存的。
是嘛，我的工程师，现实世界也是这样哦
前言
其实在前面关于MySQL优化的文章，已经讲了很多了, 看了一下之前写的文章自我的感觉是有些偏理论，偏理论的意思就是倾向于理解运作机制，结合实践比较少。我希望本篇能够做到抽象和具体相结合，理论与实践相结合, 给出一套系统优化SQL的思路。只给结论不给推导过程不是我的风格，本篇关注的场景有从数据结构的角度优化，也就是怎么更加有效的利用索引，最大化利用索引。从写法角度的优化，改写一些性能不佳的SQL获得速度的提升。那如果MySQL本身已经竭尽全力了呢，已经到达她的上限了该怎么办？ 那我们就考虑引入其他中间件来进行优化。
这也是我最近的反思，我问我自己如何优化一个慢SQL，脑子里面悬浮的就是索引、最左匹配什么的。一点不系统，因为问题还没有明确，还没有说这个SQL是什么类型，没有提及数据量，也没有提及什么场景。
回忆B+树
我们知道对于关系型数据库一般选取B+树作为自己存储的数据结构，这样的好处是查询快， 同时平衡磁盘和内存的矛盾。原因在于更廉价的存储设备带来了更高的存储容量的同时，同时也带来了更缓慢的读写速度，我们不能一股脑的将数据填入内存，内存容易丢失数据，同时这么大的内存也会带来相对高昂的成本。于是我们就需要选择一种对查询友好且随着数据量上升且查询的时间复杂度相对稳定的数据结构，二叉搜索树也许是个不错的个不错的选择，先天有序的结构，对数级别的查询复杂度。
但坏消息是如果输入的刚好是一个有序的数据列，比如 3、15、12、17、18、....。 在这种情况下我们的树就退化成了链表，换句话说树被拉高了，树的高度会显著增加比较的次数。更高的树就需要更多的查找，更多的磁盘IO。这种场景的解决方案是引入一种自平衡的算法，可以对树的高度进行调整。
引入自平衡算法，树在某些输入下面退化为链表的情况，得到了缓解。但是假设我们的数据就是这么多呢，上百万呢，那如何降低树的高度呢，减少磁盘IO呢，一种可行的方案就是单个结点存储的值多一些，多个结点构成一个页面，我们可以将一个页面多存储几行数据，将树整体变的宽一点，这也就是现代关系型数据库常用的B+树，索引的基础数据结构。
MySQL默认的页面大小是16KB，这是读写页面的基础单位，在页面存储着行数据，行数据里面存储所有列的，我们称之为聚簇索引。在叶子节点中存储的只有索引列和主键列，我们称之为非聚簇索引。MySQL目前只有在主键上的是聚簇索引，按主键列的大小进行排序，在其他列上建立的索引都是非聚簇索引，按选择列进行排序。那如果说选择的索引列比较大，这就意味着单个页面放的数据会相对小一点，这会产生更多的磁盘IO，由此就引出了该如何选择索引列的第一个细则，应当尽可能选择数据类型小的列，这样数据类型越小，索引占用的存储空间越少，一个数据页就可以放下更多的记录减少磁盘IO。
注意到我们上面画的图，经过重平衡之后的树，叶子节点是从左到右是从小到大的，没有重复项。重复项越多，查找的时候就遍历的越多，越无法缩小范围。那么如果我们是对字符串类型的列建立的索引呢？ 我们知道字符串的比较是两个字符数组之间的比较，就是逐个比较字符，远不如比较数值类型直观性能好。所以如果要在字符串类型上建立索引，在满足需求的情况下，尽量选择索引字符的前缀，但这种选择对索引列前缀做排序的方案倒是不常见，原因在于我们在取数据的时候，有时候还需要对数据进行排序，这种对前若干字符进行索引的，如果碰到的数据是前面几个字符相同的比较多，那么后面再排序索引就会失效。
但话说回来，这不是碰上了我们选择索引的第一个原则嘛，尽量选择重复项小的列建立索引。
这也就是需要自增主键的理由
插入点分裂
注意到我们上面的讨论，MySQL主键上面的索引是聚簇索引，按主键进行排序。 如果主键是有序递增的，如果当前节点存满之后，新数据到来之后，InnoDB会首先定位到小于当前插入主键的最大页面，那怎么判断本次插入是递增还是递减呢，MySQL在每个page上记录了上次插入位置(PAGE_LAST_INSERT), 以此来判断是递增还是递减。如下图所示，我们插入的主键是17，小于等于17的页面中选出最大主键值所在的页面，是页面二。然后我们发现页面二已经放满了数据，这个时候只需要再申请一个页面，将新加入的数据放入其中即可。这种策略被称之为插入点分裂:
我们可以看到在主键和索引列递增的情况下，页面的利用率接近百分之百。但是不幸的是，我们不能对主键和普通索引列做出都是递增的这种假设。考虑新加入的是19、18、17。
插入19，小于19的页面中，最大主键所在的页面是页面二, 申请一个新页面将这条记录放入。插入18，小于18的页面中，最大主键所在的页面是页面二，然后再申请一个页面，插入17也会找到页面二，又申请了一个页面。于是最终的存储结构像下面这样:
这也就是参考链接[3] 中描述的MySQL的bug，这种情况下也会导致查询缓慢，愿意在于每个页面的利用率不高，扫描的页面太多。那怎么优化这种插入呢? 一种可行的方案如果插入点在页面的最后，尝试将其插入到下页的开头，具体参考(btr_insert_into_right_sibling函数)，但插入到下一页面，会导致下一页面的上层索引失效，因为最小项发生了变化，例如插入17之后，18这条记录所在的页面的父节点指针还是18，就破坏了B-tree的结构。因此必须更新父节点的索引项，但这有违背了InnoDB加锁的顺序，InnodDB默认是latch coupling模式，也就是先从父节点加锁。在这种情节下走到子节点才发现要加锁，进而需要更新父节点，有可能更新整棵树的情况下，简单粗暴一点做法是对整棵树进行上锁，然后做更新操作。
直接暴力锁掉整棵树无疑会造成性能的退化，不妨引入一种假设，在这种输入的情况下导致整棵树变化的概率比较小，先做最乐观的操作，比较并交换。如果失败再考虑对整棵树进行上锁。但是这种方法增加了分裂的复杂度，更极端的数据分布下没有问题。所以针对这个问题，也可以直接从右边的页里面搬运几条记录过来，提升页面的利用率。或者就搬一半记录过来，由此就引出了中间点分裂。
中间点分裂
如果主键出现是随机的忽大忽小，新进来的数据需要插入之前的页面，这该怎么办呢？图下图所示，我们新插入的节点，主键值是4，这个时候应该如何选择分裂点呢?
这种情况下同样要申请一个新页，但如果我们的新页只放一条记录，这样就对页面的利用率就有些低， 于是我们想到能否从页面一里面移动一部分记录到我们新申请的页面里面:
这种分裂方式被称之为中间点分裂。那如果是持续递减呢，比如在上面的B树中我们插入的是0、1、2。那其实这种我们向左分裂就好了:
小小的总结一下
上面我们简单的回忆一下索引的存储结构，我们可以看到在顺序插入的情况下，页面的利用率最高，页面利用率高的情况下，我们就能在一个页面中匹配尽可能多的记录，减少磁盘IO。这是从插入语句的角度来优化SQL的性能。下面我们将从数据结构的角度讲如何优化SQL，也就是说更好的利用B树。
从数据结构开始优化
回表与聚簇索引
如下面的查询:
select * from test_0 where name = 's'
由于name并非索引列，我们不能像通过主键列查询来快速的定位数据页的位置，在数据页通过二分查找来缩小数据查找范围，快速定位数据。我们只能从B+树的叶子节点开始从头开始遍历，一直遍历到最后一个叶子节点，找到符合条件的数据。
但是如果我们在name这一列上建了索引，那我们等于说基于name建立了一棵B+树，而B+树先天是有顺序的，有了顺序我们就能缩小查找范围，快速定位数据。但在MySQL对非主键列建立的索引还是非聚簇索引，非代表了是聚簇索引的否定，聚簇索引的意思是在叶子节点上存储了一行所有列的数据。而非聚簇索引存储的就是主键列和索引列。这么设计的好处是插入更新的时候成本更低，坏处是如果如果查出来的字段上，在索引列上没有，就要回到主键索引的那棵树上查。这被称之为回表操作。
覆盖索引
那如果我将SQL语句调整为下面这样呢:
select name from test_0 where name = 's'
这个时候由于索引列包含了要查出的这一列，这就不需要回表操作，这也被称之为覆盖索引。
最左匹配
上面我们讲的列都是单索引列，实际情况中我们更多的是组合多个条件去查询, 比如我们根据人去查订单，但通常情况下最新的订单排在最上面:
select * from et_order where userId = '001' order by create_date desc limit 100
如果我们仅针对于userId这一列建立索引，那么拿到结果之后我们还要对结果进行排序，如果我们扫描出来的记录比较大，就有可能需要将结果先放在磁盘上排序，然后再读取进来这无疑是很耗时的。
于是我们想就可以针对多列进行建立索引，这也就是组合索引，按多个字段进行排序，先按第一个字段排序，第一个字段相同再按第二个字段排序。但是遗憾的是在MySQL 8.0之前建立的组合索引默认都是升序，但在MySQL 5.7采取的优化是从后往前扫描。这会消耗一定的成本。
但实际的情况我们往往还会结合多个条件，多个条件组成组合索引就引出了最左匹配:
select * from et_order where b = '001' and a = 'c' and c = 'cc' order by create_date desc limit 100
我们的索引顺序是a、b、c，a在前的话就能够用到B+树的顺序，但这并不意味着我们上面的写法无法用不到索引，原因在于我们上面的条件等价于:
a = '001' and b = 'c' and c = 'cc'
这是查询优化器的功劳，尽量减少开发者的心智负担。最左边的列是第一排序列，条件中如果能够有效的利用到第一排序列就能减少扫描的记录数。
select * from et_order where a = '001' and b = 'c' order by create_date desc limit 100
索引跳跃扫描
上面的SQL同样可以用到(a,b,c)这颗索引树，原因还是来自于这颗B+树以a优先采取排序，a相同再用b，b相同再用c。如果我们只用到最右侧的索引列:
select * from et_order where c = 'c' order by create_date desc limit 100
因为B+树以a为主要顺序，在扫描索引的时候无法利用a的顺序性，于是就不如回到聚簇索引中扫描。 但是要说完全不能用也不至于，考虑极端情况，假设索引(a,b,c) 中的a、b是全部重复的，那么事实上索引(a,b,c) 等价于索引C列的顺，索引(a,b,c)事实上是用列c进行排序的。那这个也能用到索引。
现在让我将情况变得稍微糟糕一些, 前两列的基数为2, 数据分布如下所示:
1 1 2
1 1 3
1 1 4
1 2 4
1 2 5
1 2 6
而我们现在想定位的是c = 4这一列，我们的SQL语句如下所示：
select * from et_order where c = '4'
注意到基数比较小，我们可以先构建一个搜索元组[(1,1,4) , (1,2,4)], 在搜索的时候，在前两列是1的区间里面搜索4，可以有效的利用索引，完成之后直接进入第二个区间，这相当于直接利用了c列进行比较。这也就是MySQL 从8.0.13引入的index skip scan， 该特性由FaceBook贡献。
索引下推
让我们观察这么一个语句:
select * from et_order where a = '001' and b like '%b%' and d like '%c%'
按我的理解是MySQL在扫描索引(a,b,c)这颗B+树的时候，a和b都能用上，先看索引列a是否满足条件，满足之后再看b是否满足条件。最后找到满足条件的记录之后，回表拿出所有行，然后再服务层判断d like '%c%' 。但事实上MySQL之前的某个版本是先定位a = '001'的数据，即使b这一列也在索引列，但是存储引擎这一层在扫描的时候并不会用到b like 这个条件，在MySQL 5.6 才引入索引下推这个功能。 存储引擎层先充分的利用索引列，最后在服务器层过滤无法使用索引列的条件。
模糊匹配的最左匹配
这也就是最左匹配。最左匹配的另一个语义来自模糊匹配, 如下面的SQL:
select * from users where name like '%user_6%' limit 10
这个sql的语义是扫描users中name这一列中包含a的数据，即使我们在name建立了索引列，也无法有效的利用索引，因为这不是一个具体的值，具体的值可以参与比较。但如果我们将SQL改写为下面这样呢:
select * from et_order where create_at_name like 'a%'
我知道这个可以用到索引，但为什么能够用到索引呢？ 我给的第一个解释是原因在于前半部分是确定的，只是后半部分不确定。 但我觉得这个解释是流于表面。那我们看下执行计划里面:
咦，里面的type是range, 也就是将这个模糊疲惫转成了范围查询，我的脑海里面不断的在想如何拿着这个a和B+树作比较，但是感觉没办法做。看到这个range我似乎有点理解了，在比较集合里面我们是否可以认为单个字符的值要小于多个字符的值，基于这种假设我们可以将SQL调整为:
select * from et_order where create_at_name >= 'a'
这就让我有种意料之外的惊喜，我获得了更本质直接的答案，但我又有点懊恼，为什么的思维不断在思考前缀和B+树之间进行比较呢，感觉没办法处理，为什么我没有想到范围查询呢？那这里我就要调整我的思维模型了，参与比较的一定是确定的值，我们需要将查询条件转换为确定的值，前缀匹配确定的是一个范围。因此我们可以考虑从范围入手。当我思考到这里的时候，又觉得很简单。还是那个思路，如果在当前道路找不到答案，不妨考虑变换问题的形式，将问题变换到能够运用到原始条件的形式。我们在实践中不断的强化这一思路的不同表现形式，抽象和具体相结合，理论和实践相结合。
但有时候业务就希望模糊匹配该怎么办，表的数据又很大，SQL就很慢，这个时候我们不妨考虑引入其他中间件来考虑解决这个问题，比如Elasticsearch。君子生非异也，善假于物也。但是引入了ElasticSearch解决了搜索的问题，那么新的问题又来了，该如何维持MySQL和ElasticSearch之间数据的一致性，我们还是和数据库缓存一致性一样的思路，通过CDC来做数据同步，常见的CDC中间件有: Debezium、Canal、Flink CDC等等。
这也就是索引失效的第一个场景模糊匹配，那还有其他索引场景吗？
在列上使用函数
让我们看下面这么一个SQL：
# age_plus_3函数 是将这一列所有的函数都加3
SELECT * FROM users WHERE age_plus_3(age) = '18';
其实这个我还是不理解为什么用不到索引，但是我却没有提出一个很恰当的问题，我并不知道我不理解在哪里。当然你也可以将这个不理解的过程一并送给大模型，让大模型帮你理解你不理解在哪里。但是我还是比较享受这个思考的过程，直到写到这里的时候，我脑袋出现了一幅图, 在B+树进行比较的时候每次比较都对索引列调用age_plus_3函数，不也行吗？ 当我的脑海出现这个图的时候，我大概理解这条SQL为什么用不了索引了。
注意在查找的过程中完全还可以用到B+树，因为age是数字列，我们的age_plus_3是让所有节点列往上增加，并没有改变B+树中结点的顺序。那如果我们的函数无法提供这种自增语义呢，对指定列运用的函数破坏了索引列B+树的结构，那就无法利用原来的顺序了。所以在列上使用函数无法用到索引列，会退化为全表扫描。我们可以调整SQL为:
SELECT * FROM users WHERE age = '15';
也能满足我们的需要，那么我们能够先天的感知，对数字列做加减乘除操作是可逆的，我们的判断来自于等式的性质, 由a + b = c 可以推导出 a = c - b。那让我们看下面这个SQL是否可以转换为等价写法：
SELECT * FROM users WHERE lower(name) = 'jo';
这种是多个结果对一个，name可能是Jo, 还有JO等等的，我们可以将其转换为范围查询。
SELECT * FROM users WHERE in ('jo', 'jO', 'Jo','JO');
但是如果构造的范围太多，其实有遗漏范围的风险，但为了有效的利用索引，在低版本似乎也是一个没办法的事情。到了MySQL 8.0 之后，就引入了函数索引，我们就能心智负担更低的写出性能更佳的SQL，当然代价是多浪费了那么一点点空间。还有一种常见的查询是日期函数:
SELECT * FROM users where YEAR(created_at) = 2025
我们也可以将其转换为等价的范围查询:
SELECT * FROM users where created_at >= '2025-01-01' AND created_at < '2026-01-01'
我们现在来观察我们将函数换成等价的不用到函数列的写法，有哪些规律，普通的加减乘除我们可以做对应的逆运算来解决。如果是复杂一点的映射，我们就需要又输出来倒推输入，如果输入无限个没办法确定的，并且没有办法用范围来框定的，就办法平移。如下SQL:
SELECT * FROM users WHERE TRIM(name) = 'jo';
这样的输入范围是无限的，我们就没办法将其转换成不用函数的写法，我们可以用其他中间件比如ES来解决性能问题。
字符集不一致导致索引失效
但有时候我们的数据量不多，join的时候还是很慢怎么办，这可能原因就是字符集不一致导致的，MySQL在进行数据库比较的时候会对列做隐式转换，隐式转换会有相当高的成本，导致索引失效。所以有时候排查问题可能是逐步的，先看基本的字符集是否对齐。这也是我的经验之谈。
从写法开始优化
相关子查询替换为join
相关子查询写法(一)
本篇的重点在于那些写法不佳的SQL，将其替换为更高效的写法来获得性能的提升。让我们开门见山，日常我们常写的SQL可以简单的分类为join、group by、子查询。一个常见的子查询如下所示:
SELECT
articles.id,
articles.views,
articles.title,
articles.slug,
articles.created_at,
(SELECT count(*) FROM tracking WHERE element_id = articles.id AND tracking_type = 'article_view') AS tracking_views
FROM articles
WHERE articles.company_id = 123
ORDER BY articles.created_at DESC limit 50
artiles表里面有10w数据，tracking只有50w数据, 表结构数据如下所示:
CREATE TABLE `articles` (
`id` int NOT NULL AUTO_INCREMENT,
`views` int NULL DEFAULT 0,
`title` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
`slug` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
`created_at` datetime NOT NULL,
`company_id` int NOT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic;
CREATE TABLE `tracking` (
`id` int NOT NULL AUTO_INCREMENT,
`element_id` int NOT NULL,
`tracking_type` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
`created_at` datetime NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic;
上面的sql在我的4C 8G服务器下面执行时间是接近6s左右，一般惯性思维到这里就要加索引了，但我们分析一下这个sql在select 中嵌套了查询语句，那么就是说在查询articles时候，每碰到满足条件的记录都要执行一下里面的子查询。这是我们的猜想，我的MySQL版本是8.0.42，我们可以用EXPLAIN ANALYZE 看更详细的执行计划，上面的SQL执行计划为:
-> Limit: 50 row(s) (cost=10079 rows=50) (actual time=45.6..45.7 rows=50 loops=1)
-> Sort: articles.created_at DESC, limit input to 50 row(s) per chunk (cost=10079 rows=99585) (actual time=45.6..45.7 rows=50 loops=1)
-> Filter: (articles.company_id = 123) (cost=10079 rows=99585) (actual time=0.045..39.4 rows=50053 loops=1)
-> Table scan on articles (cost=10079 rows=99585) (actual time=0.0436..34.3 rows=100000 loops=1)
-> Select #2 (subquery in projection; dependent)
-> Aggregate: count(0) (cost=46269 rows=1) (actual time=137..137 rows=1 loops=50)
-> Filter: ((tracking.element_id = articles.id) and (tracking.tracking_type = 'article_view')) (cost=45770 rows=4988) (actual time=29.7..137 rows=4.14 loops=50)
-> Table scan on tracking (cost=45770 rows=498830) (actual time=0.0939..106 rows=500000 loops=50)
我们可以看到跟我们的猜测是一致的，也就是外层扫描到一次条件符合的结果，select中就会走一次。 我们尝试用join来改写:
select a.*, counts.count
from articles a
join (
select count(*) as count, element_id
from tracking
where tracking_type = 'article_view'
group by tracking.element_id
) as counts on counts.element_id = a.id
where a.company_id = 123
ORDER BY a.created_at DESC limit 50
这个执行速度只有0.5s左右，如果我们将limit数值往上提升就会发现差距越来越明显，让我们来看下join改写之后的执行计划，还是用 explain analyze看下执行计划:
-> Limit: 50 row(s) (actual time=568..568 rows=50 loops=1)
-> Sort: a.created_at DESC, limit input to 50 row(s) per chunk (actual time=568..568 rows=50 loops=1)
-> Stream results (cost=12473 rows=0) (actual time=361..559 rows=49106 loops=1)
-> Nested loop inner join (cost=12473 rows=0) (actual time=361..535 rows=49106 loops=1)
-> Table scan on counts (cost=2.5..2.5 rows=0) (actual time=361..374 rows=98137 loops=1)
-> Materialize (cost=0..0 rows=0) (actual time=361..361 rows=98137 loops=1)
-> Table scan on <temporary> (actual time=343..351 rows=98137 loops=1)
-> Aggregate using temporary table (actual time=343..343 rows=98137 loops=1)
-> Filter: (tracking.tracking_type = 'article_view') (cost=50260 rows=49883) (actual time=0.172..198 rows=400385 loops=1)
-> Table scan on tracking (cost=50260 rows=498830) (actual time=0.169..129 rows=500000 loops=1)
-> Filter: (a.company_id = 123) (cost=0.25 rows=0.1) (actual time=0.00149..0.00153 rows=0.5 loops=98137)
-> Single-row index lookup on a using PRIMARY (id=counts.element_id) (cost=0.25 rows=1) (actual time=0.00134..0.00136 rows=1 loops=98137)
注意里面的关键词Materialize(物化)、using temporary table (临时表)。这意味着MySQL将子查询结果生成为临时表，通常在内存里面，如果内存放不下将会放到磁盘上, 通过下面的命令可以看到这个阈值是16MB:
SHOW GLOBAL VARIABLES LIKE 'tmp_table_size';
也就是说临时表超过这个大小，临时表将部分数据写入到磁盘上。 我的5.7版本和8.0版本执行效果是一致的，用join改写的时候，MySQL也是采用了物化表的方法去实现查询。注意5.7没有 explain analyze , 我们可以用 explain 来看，我们在执行计划里面看到了MySQL会尝试物化表来进行查询。
相关子查询写法(二)
让我们先开始准备吧，下面是我们本次准备的两张表:
CREATE TABLE orders (
id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
customer_id BIGINT UNSIGNED NOT NULL,
order_amount DECIMAL(10,2) NOT NULL,
order_date DATE NOT NULL,
status CHAR(1) NOT NULL DEFAULT 'N',
KEY idx_cust_date (customer_id, order_date)
) ENGINE=InnoDB;
CREATE TABLE customers (
id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(60) NOT NULL,
signup_date DATE NOT NULL,
KEY idx_signup_date (signup_date)
) ENGINE=InnoDB;
orders里面数据总量为916867,customers的数据总量为10w。
我们想找出同客户单笔订单金额超出历史订单定额平均值的订单, 按订单时间倒序排, 于是我们可以这么写:
SELECT o.*
FROM orders o
WHERE o.order_amount >
( SELECT AVG(order_amount)
FROM orders
WHERE customer_id = o.customer_id )
order by o.order_date desc limit 10
这个SQL花了大概25s，在MySQL 8.0.42中，这其实就是扫描哇orders的全表过程中没扫描到一行数据就要执行下where后面的查询。在 8.0 我们用explain ANALYZE来看下执行计划：
-> Limit: 10 row(s) (cost=99811 rows=10) (actual time=28200..28200 rows=10 loops=1)
-> Sort: o.order_date DESC, limit input to 10 row(s) per chunk (cost=99811 rows=990978) (actual time=28200..28200 rows=10 loops=1)
-> Filter: (o.order_amount > (select #2)) (cost=99811 rows=990978) (actual time=1.13..28042 rows=497008 loops=1)
-> Table scan on o (cost=99811 rows=990978) (actual time=1.04..401 rows=993570 loops=1)
-> Select #2 (subquery in condition; dependent)
-> Aggregate: avg(orders.order_amount) (cost=4.33 rows=1) (actual time=0.0246..0.0246 rows=1 loops=993570)
-> Index lookup on orders using idx_cust_date (customer_id=o.customer_id) (cost=3.37 rows=9.62) (actual time=0.0195..0.0231 rows=11 loops=993570)
基本跟我们上面的分析一致，现在让我们尝试用join重写:
SELECT o.*
FROM orders o
JOIN ( SELECT customer_id, AVG(order_amount) AS avg_amt
FROM orders
GROUP BY customer_id ) a
ON a.customer_id = o.customer_id
WHERE o.order_amount > a.avg_amt
order by o.order_date desc limit 10
这个sql大致在我的服务器上花了3.7s左右，我猜这个执行计划中，MySQL同样将子查询进行物化, 我们用explain ANALYZE 来看下执行计划:
-> Limit: 10 row(s) (actual time=3962..3962 rows=10 loops=1)
-> Sort: o.order_date DESC, limit input to 10 row(s) per chunk (actual time=3962..3962 rows=10 loops=1)
-> Stream results (cost=2.71e+6 rows=332436) (actual time=1698..3887 rows=500212 loops=1)
-> Nested loop inner join (cost=2.71e+6 rows=332436) (actual time=1698..3755 rows=500212 loops=1)
-> Table scan on a (cost=210563..211862 rows=103688) (actual time=1698..1715 rows=99995 loops=1)
-> Materialize (cost=210563..210563 rows=103688) (actual time=1698..1698 rows=99995 loops=1)
-> Group aggregate: avg(orders.order_amount) (cost=200195 rows=103688) (actual time=9.35..1666 rows=99995 loops=1)
-> Index scan on orders using idx_cust_date (cost=100454 rows=997407) (actual time=9.34..1550 rows=1e+6 loops=1)
-> Filter: (o.order_amount > a.avg_amt) (cost=2.4 rows=3.21) (actual time=0.0163..0.02 rows=5 loops=99995)
-> Index lookup on o using idx_cust_date (customer_id=a.customer_id) (cost=2.4 rows=9.62) (actual time=0.0159..0.0186 rows=10 loops=99995)
我们看到了Materialize这意味着我们的优化很有力。但是我在研究SQL优化的时候，偶然尝试将排序移除掉，就发现这个SQL变得很快了，大概只花了大概0.01s左右。
SELECT o.*
FROM orders o
JOIN ( SELECT customer_id, AVG(order_amount) AS avg_amt
FROM orders
GROUP BY customer_id ) a
ON a.customer_id = o.customer_id
WHERE o.order_amount > a.avg_amt
limit 10
于是我开始好奇这个过程，于是我又打开了执行计划:
-> Limit: 10 row(s) (cost=100454 rows=10) (actual time=1.09..1.58 rows=10 loops=1)
-> Filter: (o.order_amount > (select #2)) (cost=100454 rows=997407) (actual time=1.09..1.58 rows=10 loops=1)
-> Table scan on o (cost=100454 rows=997407) (actual time=0.993..0.996 rows=18 loops=1)
-> Select #2 (subquery in condition; dependent)
-> Aggregate: avg(orders.order_amount) (cost=4.33 rows=1) (actual time=0.0281..0.0281 rows=1 loops=18)
-> Index lookup on orders using idx_cust_date (customer_id=o.customer_id) (cost=3.37 rows=9.62) (actual time=0.0217..0.0267 rows=11 loops=18)
这次只扫描了18行就满足了SQL，看起来是数据比较靠前导致的，也就是我们要查询的目标数据刚好在前18行就凑齐了。那如果我们的数据靠后，那么同样扫描行数也很多，这一方面不如join稳定。
总结一下
我们这里总结优化相关子查询的一个手段就是将子查询改写为join，这能启发MySQL将子查询进行物化，避免M×N式扫描。MySQL官方也推荐用join重写子查询。见参考资料[7]。
如果是数据量太大了该怎么办
但如果说B+树的高度已经四五层了，换言之这个单表数量已经超过了当前硬件的瓶颈，我们选择增强单台节点的硬件(一般这被称之为垂直增长)，得到的收益会越来越和成本不成正比。对于这种情况，我们应当优化的是存储策略，开始分库分表。 这也是《关于分库分表，我的一些新思考》讨论的场景，我们做水平扩展，将数据分到几个表里面，这样降低了树的高度，就能从全局提升SQL的查询速度。
那如果是SQL已经很快了，没有优化的空间，SQL慢在等待执行的过程。这就像一家奶茶店一样，奶茶店的店员速度是够快的，只是其他人在排队。那怎么办呢，最直接的就是扩大店面，然后招人。但是垂直扩容到一定地步，收益受到边际效应的制约。于是我们不妨考虑再开几家店，这也就是主从分离了。
update很慢怎么办
那如果是update 语句很慢呢，我们知道MySQL在update的时候默认是加锁的，也就是说同一时刻只能更新某行数据的只能有一个连接，在这个连接更新数据库期间，其他连接都在等。常见的场景就是秒杀，我们扣减库存：
update product set num = num - 下单数量 where skuid = #{skuId} and num - 下单数量 > 0
但是如果你用的是云厂商的数据库，阿里云的RDS数据库引入了Inventory Hint对这种高频update语句进行了优化。但如果还是不够快怎么办，这个时候我们不妨借助Redis。这一点我们在《库存扣减-订单的第一道关卡》已经有了详细的讨论。
写在最后
我们本篇讲的有MySQL是如何将插入的数据中放到页面的，我们可以看到在主键递增的情况下，页面的利用率是最高的。页面利用率越高，我们就能用更少的磁盘IO来查找到我们想要的记录。然后我们讲了B+树是如何加速查询的，有序的数据结构更能加速查询，所以优化SQL的第一个思路就是尝试对查询条件加索引，这非常有效。但是有时候由于业务必须要求我们做模糊匹配等无法有效利用索引的扫描该怎么办，我们可以引入其他中间件来解决这个问题，比如ES。
那有时候是我们写法不佳导致SQL查询缓慢，对于这种情况我们本篇重点讲了相关子查询，相关子查询的实现算法为外层扫描到一个符合条件的记录，子查询就执行一次。对应的优化思路为我们将其转换为join，MySQL会对join做加速，将join的子查询物化。我们还可以看到，在我们研究SQL执行流程的时候，我们看到SQL的执行速度有时候还依赖于数据分布。
但如果是数据量特别大导致的SQL执行速度缓慢呢？ 我们不妨降低数据量，考虑新式的分布式数据库或者分库分表。但如果是慢的原因是因为SQL在等待执行呢，MySQL同时执行的SQL是有限的，这个时候我们可以考虑读写分离。那如果是update语句比较缓慢呢，我们可以考虑引入Redis来解决这个问题。
写这篇文章的时候，有想过其实在LLM普及的今天，你问一个SQL如何优化，其实他能直接给你结果，并能告诉你为什么。但是我时常对大模型抱有疑虑，我担心有时候他给的结果是对的，但是推导过程就存在问题。况且我也更喜欢这个探索的过程，能够带着我拨云见日，跳出我思维的盲区。
参考资料
[1] B-树如何让你的查询更快 juejin.cn/post/734680…
[2] MySQL · 内核特性 · Btree 顺序插入优化及问题 mysql.taobao.org/monthly/202…
[3] bugs.mysql.com/bug.php?id=…
[4] InnoDB B-tree Latch Optimization History baotiao.github.io/2024/06/09/…
[5] 庖丁解InnoDB之B+Tree mysql.taobao.org/monthly//20…
[6] MySQL · 引擎特性 · Skip Scan Range mysql.taobao.org/monthly/201…
[7] Rewriting Subqueries as Joins dev.mysql.com/doc/refman/…
