MySQL 优化 基于规则优化

MySQL知识总结

《MySQL是怎样运行的》知识总结

单表访问方法

表的连接原理

优化 基于查询成本的优化

优化需要的统计数据

优化 基于规则的优化

Explain详解

InnoDB缓冲区

事务

redo日志

undo 日志

MVCC原理

MySQL 锁

14 基于规则的优化

​ MySQL会基于一些规则重写我们写的语句,使得查询的执行更加高效

表的结构

# 表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_part (key_part1, key_part2, key_part3)
) ENGINE = INNODB
  CHARSET = utf8;

# 表t1
create table t1
(
    m1 int,
    n1 char(1)
);

# 表t2
create table t2
(
    m2 int,
    n2 char(1)
);

insert into t1
values (1, 'a'),
       (2, 'b'),
       (3, 'c');

insert into t2
values (2, 'b'),
       (3, 'c'),
       (4, 'd');

条件化简

​ 我们编写的查询语的搜索条件本质上是条件表达式,这些表达式可能比较复杂,不能高效地执行查询,MySQL优化器会为我们化简这些表达式

移除不必要的括号

select *
from t1,
     (t2, t3);# 移除不必要的括号
select *
from t1,
     t2,
     t3;

常量传递

​ 某个表达式是某个列与常量的等值匹配

select *
from single_table
where key1 = 'a'
  and key3 > key1;
select *
from single_table
where key1 = 'a'
  and key3 > 'a';# 常量传递

移除没有用的条件

​ 移除明显永远为true、false的表达式

select *
from single_table
where key1 = 'a'
  and 1 = 1;# 移除没有用的条件
select *
from single_table
where key1 = 'a';

表达式计算

​ 表达式包含常量时,它的值会优先计算出来

select *
from single_table
where key2 > 8880 + 8;
select *
from single_table
where key2 > 8888;# 计算表达式

having与where子句的合并

​ 查询语句没有出现聚集函数、group by子句,查询优化器就会把having、where子句进行合并

常量表检测

​ 以下情况的两种类型的查询速度非常快

  1. 查询的表没有或只有一条记录
  2. 使用主键等值匹配唯一索引等值匹配作为查询条件来查询某个表

这两种方式查询的表被成为常量表,优化器在分析一个查询语句时,会首先执行常量表查询,在得到查询结果后,把查询涉及常量表的条件都替换掉

select *
from s1
         join s2 on s1.key2 = s2.key2
where s1.key2 = 8180; 
#执行常量表查询后,只有一条记录
select s1.*,s2.*
from s1
         join s2 on s1.key2的常量值 = s2.key2;

外连接消除

​ 我们知道外连接的驱动表中,如果不能在被驱动表找到on子句匹配的记录,也会将驱动表记录加入到结果集,而对于不符合where子句查询条件的记录不会参与连接,因此如果我们设置了查询条件被驱动表的列不为NULL,那么此时的外连接就跟内连接一样了

select *
from t1
         left join t2 on t1.m1 = t2.m2
where t2.m2 is not null;

+------+------+------+------+
| m1   | n1   | m2   | n2   |
+------+------+------+------+
|    2 | b    |    2 | b    |
|    3 | c    |    3 | c    |
+------+------+------+------+

​ 只要有隐含被驱动表的列不为NULL的条件,就会出现上述情况

空值拒绝:在外连接查询中,指定的where子句包含被驱动表中的列不为null值的条件,在被驱动表的where子句符合空值拒绝的条件后,外连接与内连接就可以相互转换,这种转换带来了好处,优化器可以评估不同表的连接顺序的成本,选择成本较低的那种连接顺序进行查询。

子查询优化

​ 在一个查询语句中的某个位置可以有另一个查询语句,这个查询就是子查询

​ 出现在select子句中:

select (select key2 from single_table limit 1);

​ 出现在from子句中:把子查询的查询结果当作一个表,子查询后面,为这个表设置了别名t,MySQL把这种放在from子句后面的表称为派生表

select k2
from (select key1, key2 k2, key3
      from single_table) t;

子查询分类

按返回的结果集区分

  1. 标量子查询只返回单一值,标量子查询可以作为一个值或表达式的一部分
  2. 行子查询:返回一条记录(记录只有一列,就是标量子查询)
  3. 列子查询:返回一个列的数据(只有一条记录,就是标量子查询)
  4. 表子查询:返回多行、多列的数据

按与外层查询的关系

  • 不相关子查询:子查询可以单独运行出结果,不依赖于外层子查询
select *
from t1
where n1 in (select n2 from t2);
  • 相关子查询:子查询的执行依赖于外层查询
select *
from t1
where n1 in (select n2 from t2 where m1 = m2);# m1 = m2条件中需要用到外层查询的列

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

​ 我们平时使用子查询最多的地方就是把它作为布尔表达式一部分,用在where子句on子句中查询条件中,布尔表达式形式是:

  • 操作数 操作符 (子查询)

操作符为比较运算符(=、>、<、>=、<=、!=、<=>)

这里的操作数可以是某个列名、常量、更为复杂的表达式、子查询

布尔表达式中的子查询需要为标量子查询行子查询

select *
from t1
where m1 < (select m2 from t2 where n2 = 'd');# 子查询为标量子查询
select *
from t1
where (n1, m1) in (select n2, m2 from t2);# 子查询可以认为是行子查询、表子查询
  • [not] in、any、all子查询

​ 对于列子查询、表子查询,它们的结果集中有多条记录,这些记录相当于一个集合,所有不能单纯的使用 操作数 操作符 (子查询)组成的布尔值表达式了。

select *
from t1
where (n1, m1) in (select n2, m2 from t2);# 子查询可以认为是行子查询、表子查询

in子句的布尔表达式表示某个操作数是否存在于一个集合中

select *
from t1
where n1 < any (select n2 from t2);

# 与上面语句等价
select *
from t1
where n1 < (select max(n2) from t2);

any子句构成的布尔表达式意思是,只要在子查询结果集中存在一个值,某个指定的操作数与该值通过
操作符得到的结果为true,那么表达式的结果就为true。

all子句构成的布尔表达式,则要求某个操作数与子查询结果集中的所有值通过操作符得到结果都为true,那么布尔表达式的结果才为true。

  • exists子查询

我们仅仅判断子查询的结果集中是否有记录,而不关心记录具体是什么,那么可以使用exists子句

select *
from t1
where exists(select 1 from t2);

只要exists子句子查询的结果集中有记录,exists表达式就为true

注意事项

  1. 子查询必须使用括号
  2. select子句的子查询只能是标量子查询
  3. 要获得标量子查询或行子查询,但不能保证查询的结果为1条,那么就应该使用limit 1
  4. inanyall子查询不允许有limit语句
  5. 在子查询中使用order bydistinct、没有聚集函数的having子句的group by语句是毫无意义的(子查询的结果是一个集合),集合是否排序、去重并不重要,对于这些无用的语句,优化器一开始就进行了优化
  6. 不允许在增删改某个表时,同时对它进行子查询

子查询的执行

标量子查询与行子查询的执行过程

explain
select *
from single_table s1
where key1 = (select common_field from single_table s2 where s2.key3 = 'a' limit 1);

执行过程如下:

  1. 单独执行子查询
  2. 将子查询的结果当成外层查询的参数,执行外层查询

相关的标量子查询与行子查询的执行过程

select *
from single_table s1
where key1 = (select common_field from single_table s2 where s2.key3 = s1.key3 limit 1);
  1. 先从外层查询中获取一条记录
  2. 从这个记录中找出与子查询相关的值,再执行子查询
  3. 根据子查询的结果与外层查询的查询条件进行判断,如果成立,就将这条记录加入到结果集,否则丢弃
  4. 返回步骤1,直到外层查询没有记录

In子查询优化

select *
from single_table s1
where key1 in (select common_field from single_table s2 where key3 = 'a');
物化表的提出

​ 不相关In子查询的执行过程并不简单,如果子查询的结果太多,内存可能存放不下。

对于表达式 in这种in子句来说,in子句的若干个参数首先会被排序,如果执行查询时不能利用到索引将in子句划分为若干个扫描区间,那么就会对已排好序的参数进行二分查找,加快计算in表达式的效率。

​ 于是MySQL不直接将不相关子查询的结果集当作外层查询in子句的参数,而是将结果集存放到一个临时表,但需要注意的是临时表的列是子查询结果集中的列写入临时表的记录会被去重

进行去重可以使得临时表的大小变得更小,去重的方式是为相关的列建立主键索引、唯一索引。

一般情况下,子查询的结果集的大小不会很大,为它创建的临时表是基于内存使用的Memory存储引擎创建的,而且还为临时表建立哈希索引

In子句是判断某个操作数是否在集合中,使用哈希索引可以让匹配的过程非常快。

子查询的结果集非常大,超过了系统变量temp_table_sizemax_heap_table_size的值,临时表就会使用基于磁盘存储引擎来保存结果集中的记录,索引的类型也转换为B+树索引

  • 物化:将子查询的结果集的记录存放到临时表中的过程,物化表中有索引(哈希索引或B+树索引),所以判断某个操作数是否在子查询的结果集中的速度非常快
物化表转连接
select *
from single_table s1
where key1 in (select common_field from single_table s2 where key3 = 'a');

​ 子查询物化后,对于这次的查询就有2个角度来看待

  • 从表s1的角度,查询的意思就是,对于s1表的每一条记录,如果该记录的key1在物化表中,那么记录就会被加入到结果集
  • 从物化表的角度,查询的意思是对于物化表的每个值,如果能在s1表的记录中的key1中找到相等的值,就把这个记录加入到结果集

查询的过程就相当于2表的内连接,优化器可以评估不同连接顺序的查询成本,选择成本较低的连接顺序进行查询

将子查询转换为半连接

​ 能否将子查询直接转换为连接?

select *
from single_table s1
where key1 in (select common_field from single_table s2 where key3 = 'a');

对于这个查询语句,可以理解为,对于s1表中的每一条记录,如果能在s2表(满足条件key3 = 'a')中的记录中找到一条或多条记录满足key1 = common_field条件,那就把这条记录加入到结果集

  • 上面的语句和下方的语句很像
select s1.*
from single_table s1
         join single_table s2 on s1.key1 = s2.common_field
where s2.key3 = 'a';

对于s1.key1 = s2.common_field条件有3种情况

  1. s1表的某一条记录不能在s2表中找到满足该条件的记录
  2. s1表的某一条记录能在s2表中找到满足该条件的记录1条
  3. s1表的某一条记录能在s2表中找到满足该条件的记录多条,这个记录会被多次添加到结果集中
实现半连接
1. 子查询表上拉(Table Pullout)
select *
from single_table s1
where key2 in (select key2 from single_table s2 where key3 = 'a');
select s1.*
from single_table s1
         join single_table s2 on s1.key2 = s2.key2
where s2.key3 = 'a';

子查询的查询列表只有主键或唯一索引时可以将子查询表上拉到外层查询from子句中

子查询中查询列表为主键索引、唯一索引时,这些列本身就是不重复的,对于s1中同一条记录能在s2中找到满足in子句条件的记录只有一条

2. 重复消除(Duplicate Weedout)
select *
from single_table s1
where key1 in (select common_field from single_table s2 where key3 = 'a');
# 临时表
create table temp (
    id int primary key
);

s1表的某条记录可能在s2表中有多条匹配记录,该记录会被多次添加到结果集中,为了消除重复,可以建立一个临时表,当某条s1的记录满足in语句条件时,就把这条记录的Id加入这个临时表,如果添加成功,说明这条件记录先前没有被加入到最终的结果集,反之,该记录已经被添加到最终结果集,记录将被丢弃。

3. 松散扫描(LooseScan)
select *
from single_table s1
where key3 in (select key1 from single_table s2 where key1 > 'a' and key1 < 'b');

子查询中,对于s2表的访问可以用到key1的列的索引,而子查询的查询列表恰好就是索引列,在将这个查询语句转换为半连接时,执行的过程是扫描s2表中的索引列,索引列的值在s1表中能找到满足in语句条件的记录,就将记录加入到最终的结果集。

虽然是扫描索引,但它只会取索引值相同的第一条记录(对于普通索引,可能有多条相同值的索引记录)去执行匹配操作。

4. 半连接物化(Semi-Materialization)

前文中,把外层查询的in子句不相关子查询进行物化,然后将外层查询表与物化表进行连接,物化表没有重复的记录,可以将子查询转换为连接查询

5. 首次匹配(FirstMatch)
select *
from single_table s1
where key1 in (select common_field from single_table s2 where s1.key3 = s2.key3);
# 与半连接的方式很像
select s1.*
from single_table s1
         join single_table s2 on s1.key3 = s2.key3
    and s1.key1 = s2.common_field;

原始的半连接的执行方式,过程是:

  1. 取外层查询中的记录,然后到子查询的表中找符合匹配条件的记录
  2. 如果能找到相关的记录,就把外层查询的记录加入到最终结果集并停止该条记录的匹配
  3. 如果找不到符合条件的记录,就把该记录丢弃
  4. 开始下一条外层查询的记录匹配,知道外层查询没有记录为止
半连接适用的条件
  • 查询语句的形式如下的:
select XXX
from outer_tables
where expr in (select XXX inner_tables XXX)
  and XXX;
select XXX
from outer_tables
where (oe1, oe2) in (select ie1, ie2, XXX inner_tables XXX)
  and XXX;
  • 适用的情况:

    • 子查询必须与in子句组成布尔表达式,并且在外层查询的where、on子句出现
    • 外层查询可有其他查询条件,但必须使用and操作符、in子查询的查询条件连接起来
    • 子查询必须是一个单一的查询,不能是union连接起来的若个查询
    • 子查询不能包含group byhaving语句或聚集函数
  • 不适用的情况:

    • 外层查询的where子句,存在其他查询条件使用or操作符与in子查询组成布尔表达式连接
    • 使用not in,而非in
    • 位于select子句的in子查询
    • 子查询包含group byhaving聚集函数
    • 子查询包含了union
  • 对于不能转换为半连接的查询的子查询,MySQL仍然对其进行了优化

    • 对于不相关子查询,可以把它们物化后再参与查询,加快查询执行的速度

    将子查询物化后不能转换为与外层查询表的连接,只能是先扫描外层查询的表的记录,针对这一记录来判断该记录相关的值能否再物化表中查询到

    • 无论子查询是相关还是不相关,都可以将in子查询尝试转换为exists子查询,只要in子查询放在whereon子句中,那么inexists的转换就没问题,进行转换可能才会用到索引
    select *
    from single_table s1
    where key1 in (select key3 from single_table s2 where s1.common_field = s2.common_field)
       or key2 > 1000;
    
    select *
    from single_table s1
    where exists(select 1 from single_table s2 where s1.common_field = s2.common_field and s1.key1 = s2.key3)
       or key2 > 1000;
    

    转换为exists子查询后,便可以使用s2表的key3的索引

    如果in子查询不满足半连接的条件,又不能转换为物化表或转换成本太高,那么它就会转换为exists子查询

总结
  • 如果in子查询满足转换为半连接的条件,优化器会优先把子查询转换为半连接,然后在考虑从下面5种执行半连接策略中选择成本较低的执行策略来执行子查询
    • 子查询表上拉
    • 重复值消除
    • 松散扫描
    • 半连接物化
    • 首次匹配
  • 如果in子查询不满足半连接的条件,那么就会执行下面的策略选择成本较低来执行子查询
    • 将子查询物化,再执行查询
    • in子查询转换为exists子查询

Any与All子查询优化

如果anyall子查询是不相关子查询,它们再很多场合都能转换我们熟悉的方式执行

  • < any (select inner_expr XXX)< (select max(inner_expr) XXX)
  • < all (select inner_expr XXX)< (select min(inner_expr) XXX)

[not] exists子查询优化

​ 如果exists子查询是不相关子查询,就可以先执行子查询,得出[not] exists子查询的结果,再重写原先的查询语句。

select *
from single_table
where exists(select 1 from single_table s2 where key1 = 'a')
   or key2 > 100;
# 重写
select *
from single_table
where true
   or key2 > 100;

# 进一步重写
select *
from single_table
where true;

如果exist子查询的结果为true,那么优化器就会重写查询

如果exist子查询是相关子查询,那么就只能进行外层查询后,再拿结果与子查询进行匹配,不断重复步骤的方式来执行查询

对派生表的优化

​ 把子查询放在from语句后面,这个子查询的结果就相当于一个派生表

select *
from (select key1 k1, key2 k2, key3 k3 from single_table where key1 = 'a') t
where k3 = 'a';

对于派生表的查询,MySQL提供了2种执行策略

​ 将派生表物化,MySQL使用了延迟物化的策略,只有再查询真正使用到派生表时才尝试物化派生表,而不是在执行查询前就物化派生表

select *
from (select * from single_table s1 where key1 = 'a') t
         join single_table s2 on t.key1 = s2.key1
where s2.key2 = -1;

如果采用物化表的方式执行上述查询语句,在执行时首先会到s2表找出满足s2.key2 = -1的记录,如果找不到符合条件的记录,也就没有必要将派生表物化

​ 将派生表和外层查询合并(重写为没有派生表的形式)

select *
from single_table s1
         join single_table s2 on s1.key1 = s2.key1
where s1.key1 = 'a'
  and s2.key2 = -1;

与上方的查询语句一样

通过将外层查询和派生表合并的方式能成功消除派生表,不用再付出成本去创建、访问临时表

有些带派生表的查询不能与外层查询合并,比如包含了一下函数或语句的查询:

  1. 聚集函数
  2. distinct
  3. group by
  4. having
  5. limit
  6. union
  7. union all
  8. 派生表的子查询的select子句包含了子查询

MySQL再执行有派生表的查询时,优先尝试将派生表与外层查询合并,如果不行,则把派生表物化,再查询

  • 0
    点赞
  • 2
    收藏
  • 打赏
    打赏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
©️2022 CSDN 皮肤主题:创作都市 设计师:CSDN官方博客 返回首页
评论

打赏作者

011eH

你的鼓励将是我创作的最大动力

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值