MySQL是怎样运行的——第十四章

最新推荐文章于 2024-07-15 15:01:56 发布

对酒当歌笑俗人

最新推荐文章于 2024-07-15 15:01:56 发布

阅读量358

点赞数

分类专栏： MySQL 文章标签： mysql 数据库 database

本文链接：https://blog.csdn.net/Ning862217083/article/details/123031545

版权

MySQL 专栏收录该内容

21 篇文章 2 订阅

订阅专栏

这一章主要讲如何通过对sql语句进行变化的方式，来提升查询的性能。
我们把这种方法叫做查询重写。
这种优化应该就是基于关系代数的。当然我的关系代数都忘光了。。

14.1

这一节讲条件化简。

移除括号

在这里插入图片描述

常量传递

a = 5 and b > a
可以被优化为 a = 5 and b > 5

移除没用的条件

(a < 1 and b = b) or (a = 6 or 5 != 5)
可以被化简为
a < 1 or a = 6

表达式计算

a = 5+1 可以被化简为 a = 6
如果有函数调用，比如abs(a) > 5，就不能化简了

HAVING和WHERE合并

如果查询语句里没有出现sum max这类的聚集函数以及group by子句，查询优化器就把having子句和where子句合并起来。

常量表检测

首先说什么是常量表，常量表有两种可能，要么表里只有一条记录，甚至一条记录都没有，要么使用主键等值匹配或者唯一二级索引列等值匹配作为搜索条件去查询的表。

在监测到常量表出现时，首先执行常量表查询，然后把语句中涉及到该表的条件全部替换成常数，最后分析其余表的查询成本。

有个问题是，假如表里确实没有记录，是个常量表。我们在查询之前是怎么知道的呢？毕竟还没访问啊？答案是通过统计数据。而InnoDB中表记录的统计值不准，所以这个只适用于MyISAM等引擎。

14.2

这一节讲外连接消除。
在开始前，先要引出一会要用的表。

CREATE TABLE t1 (
	m1 int,
	n1 char(1)
) Engine=InnoDB, CHARSET=utf-8;

CREATE TABLE t2 (
	m2 int,
	n2 char(1)
) Engine=InnoDB, CHARSET=utf-8;

在这里插入图片描述
内外连接的区别在，如果找不到匹配on子句过滤条件的记录，对于外连接来说，会把驱动表中的记录加入到结果集，被驱动表部分用null填充。对于内连接来说，驱动表的记录会被舍弃。

比如这个例子
在这里插入图片描述
所以，只要我们利用where语句干掉多余的驱动表记录就行了，比如在搜索条件中指定“被驱动表的列不为null”，那么外连接多出来的记录就会被排除掉。外连接就可以转化为内连接了。

在这里插入图片描述
当然，方式有很多种，也可以指定t2.m2 = 2，总之是把外连接多出来的记录排除掉就行了。

14.3

这一节是本章最大头的部分，子查询优化。
在一个查询语句中的某个位置也可以有另一个查询语句，这个出现在某个查询语句的某个位置中的查询就称为子查询。

子查询位置

Select语句中

在这里插入图片描述
这也叫查询列表

在from语句中

在这里插入图片描述
这里把子查询看作一个表。as t意思是给这个表起别名叫t。

在where/on 子句的表达式中

select * from t1 where m1 in (select m2 from t2);

在order/group by子句中

没啥意义

子查询分类

子查询分类方式有两种。
一种是按返回的结果集区分子查询，另一种是按与外层查询的关系来区分子查询。

按返回的结果集区分子查询

标量子查询

在这里插入图片描述
只返回一个单一的值的子查询就是标量子查询

行子查询

只返回一条记录的子查询

列子查询

查询出一列数据，不过这一列要包含多个数据，否则就是标量子查询了

表子查询

返回多行多列结果

按与外层查询的关系来区分子查询

不相关子查询：子查询可以单独运行出结果，不依赖于外层查询的值。
相关子查询：子查询的执行依赖外层结果

举个相关子查询的例子


SELECT • FROM t1 WHERE m1 IN (SELECT m2 FROM t2 WHERE n1 = n2);
n1是外层查询的值

子查询在布尔表达式中的使用

子查询在布尔表达式中使用一般有以下几个场景：

使用=、>、<、>=、<>、!=、<=>作为布尔表达式的操作符

一般语法格式是这样的

操作数 操作符 (子查询)

操作数可以是列名，常量，或者是更复杂的表达式。甚至可以是另一个子查询（子查询只能是标量子查询或行子查询）。
例如：
在这里插入图片描述

[NOT] IN/ANY/ALL/SOME 子查询

对于列子查询和表子查询，都包含很多条记录，所以不能直接用=那些操作符进行连接了。
我们用IN/ANY/ALL/SOME来判断。ANY和SOME表达的意思相同。

操作数 [NOT] IN (子查询)：简单易懂，不解释了
ANY/SOME：操作数操作符 ANY/SOME (子查询) ，例子如下

select * from t1 where m1 > any(select m2 from t2);

ALL：操作数操作符 ALL(子查询)：和ANY基本一样，区别就是语意

EXISTS

[NOT] EXISTS （子查询）

exists用来判断子查询的结果集中是否有记录，不在乎记录具体是什么。

子查询语法注意事项

必须用小括号括起来
在SELECT子句中的子查询必须是标量子查询
需要确保是标量/行子查询时，可以用limit 1来限制记录数量
如果子查询前是 [NOT] IN/ANY/ALL/SOME 时，子查询里不能有limit语句
不能在增删改一个表中的记录的同时查询这个表

子查询的执行方法

还使用以前的single_table表。

CREATE TABLE single_table (
	id INT NOT NULL AUTO_INCREMENT,
	key1 VARCHAR(100),
	key2 INT,
	key3 VARCHAR(100),
	key_part1 VARCHAR(100),
	key_part2 VARCHAR(100),
	key_part3 VARCHAR(100),
	common_field VARCHAR(100),
	PRIMARY KEY (id),
	KEY idx_key1 (key1),
	UNIQUE KEY uk_key2 (key2),
	KEY idx_key3 (key3),
	KEY idx_key_part(key_part1, key_part2, key_part3)
) Engine=InnoDB CHARSET=utf8;

里面有10000个记录，且有两个分身，一个叫s1，一个叫s2。

朴素思路

朴素思路就是最简单直接的思路。
如果是不相关子查询，则先执行子查询，然后把子查询结果当作外层查询的参数。
如果是相关子查询，则先从外层查询的结果集中抽取一条记录，拿着记录去确定子查询的参数，然后执行子查询，不断重复。

看起来也没什么问题。当然，实际上来说有很多优化。

标量子查询、行子查询的执行方式

这种情况下没什么优化，就是朴素思路。

IN子查询优化

物化与物化表

如果按朴素思路做IN子查询（无关子查询情况下），则可能面临以下的问题：

结果集太大，内存爆炸
影响外层查询，比如：
- 无法有效使用索引，只能全表扫描
- 检测IN表达式是否为TRUE花费太多时间

为了优化这种子查询，设计者把子查询的结果写到临时表里。
临时表里的列就是子查询查出来的列。此外，记录会被去重。

去重操作首先就可以减少一定的空间消耗。
如果子查询结果集较小，可以用基于内存的临时表，并建立哈希索引

这样可以非常快的判断一个值是不是在临时表里。O(1)。

如果子查询结果集较大，则用基于磁盘的临时表，并建立B+树索引

把结果集保存到临时表的过程叫做物化。我们把保存结果集的临时表叫做物化表。
因为B+树/哈希的帮助，判断一个值是不是在表里非常快。

物化表转连接

由于结果集变成了表，因此查询语句就相当于原来的表与物化表做内连接。
我们可以利用前面讲过的基于成本的优化来选择合理的驱动、被驱动关系。核心思路是小表驱动大表，尽量让索引在连接时起作用。

子查询转半连接

虽然转物化表也很不错了。但设计者还想着要不要不进行物化操作，直接把子查询转连接呢？
考虑这个sql：

select * from s1 where key1 in (select common_field from s2 where key3 = 'a');
是不是和
select * from s1 inner join s2 on s1.key1 = s2.common_field where s2.key3 = 'a';
很像？

这两个sql确实是很像的。主要问题在子查询的结果集是去重的，common_field值是唯一的。
而内连接的话，同一个commo_field（不考虑key3='a’这件事）值，可能会把s1中的一条记录多次放到结果集里。所以这两个sql是不全等的。

因此出现了半连接这种概念：对于s1表的某条记录，我们只关心s2表中是否存在与之匹配的记录，而不关心到底有多少条匹配的记录。最后的结果集只保留s1部分。

如果看不懂什么是半连接，只需要明白一件事，我们要想办法给s2中的值去重

半连接本身只是个概念，外部是没法用的。MySQL如何实现这种半连接呢？

Table pullout

因为核心思路是给s2中的记录去重，因此当子查询查询的列全都有主键/唯一索引时，可以直接把子查询消除掉，转换为内连接（就是上面的那个例子）。
唯一索引保证了结果是去重的。

Duplicate Weedout

我们建立一个有关s1主键的临时表。临时表里的s1主键也要建立主键索引来保持唯一性。
每次我们想把一条记录加入到结果集之前，都先检查临时表里是否有这个记录对应的主键值。如果临时表中存在这个主键了，就不插入了。否则把主键值插入到临时表中，再把记录加入到结果集里。

一言以蔽之，就是用外层查询查询出来的主键来进行去重。

LooseScan

还是记住核心思路去重。如果我们子查询要查询的列上都有索引，则我们可以走那几列（直接索引覆盖了，肯定快）的索引。因为索引本身可以帮助排序，因此我们对每个值，只需要取第一条记录，用第一条记录去匹配被驱动表的记录即可。

下面举个例子：

select * from s1
	 where key3 in (select key1 from s2 where key1 > 'a' and key1 < 'b');

s2上有key1的索引。
在这里插入图片描述
也就是通过只访问第一条记录的方式来去重。

半连接物化

意思就是如果半连接去重不方便的话，不行就直接物化吧··物化表本身是去重的。

First Match

整体是按照朴素思路（相关子查询那部分）走。每次拿一条记录去匹配子查询的结果集中的记录。关键点在如果匹配成功，则直接加入结果集，这条记录就不继续匹配了。
用两层循环做比喻，就是成功的话，外层循环直接continue。

其实就是Loose Scan反过来。用外层的表当驱动表。

子查询无法转为半连接的情况

有些情况下，子查询是没法变成半连接的。
比如子查询和其他条件之间用OR连上了、子查询外是NOT IN而非IN、子查询包含GROUP BY/ HAVING / 聚集函数、子查询包含union等等等等。

对于无法转换的，可以用以下的办法

物化：注意这里的物化表是不能与外层循环的表连接的。否则就是半连接了······只是说物化了以后判断很快，毕竟可以有哈希索引。
把IN换成EXISTS

这个规则是比较通用的，除了一些特殊情况，比如外层表达式或内层表达式涉及到NULL的时候。因为在不包含IS NULL操作的情况下，如果某个操作数是NULL，表达式的结果也是NULL。而EXISTS的结果一定是TRUE/FALSE，这和NULL不一样。

所幸的是，通常子查询都在where 或 on里，where或on不区分NULL和FALSE。

所以为什么有时候要把in转换成EXISTS呢？因为转换了更有可能用到索引。

比如把
在这里插入图片描述
转换成

在5.5版本以前，其实没有半连接和物化，都是转换成EXISTS，这样可以更好的利用索引。

ANY/ALL子查询优化

不相关子查询可以把ANY或ALL消除掉，变成一个函数调用。
在这里插入图片描述

[NOT] EXISTS子查询的执行

其实就是朴素方法

对于派生表的优化

在这里插入图片描述

这个括号里的子查询就是一个派生表。

对于派生表，也可以优化。最直观的就是物化，不多说了。

注意，物化是惰性的。比如这个sql，先从s2拿一个记录，做匹配的时候才生成派生表。如果s2里没有key2=1的记录，就不生成了。

还有一种可能是直接消除派生表。

select * from (select * from s1 where key1 = 'a') as derived_s1;
select * from s1 where key1 = a

上下两个语句等价，所以派生表真没必要。
再比如上文的
在这里插入图片描述
这个语句，派生表可以和外层查询合并。

当然，不是所有时候都可以把派生表和外层查询合并。比如有各种函数啊、group by啊、having啊、limit啊、union啊、limit啊、distinct啊、子查询又嵌套一个子查询啊等等等等…