MySQL之SQL语句优化

语句优化

即优化器利用自身的优化器来对我们写的SQL进行优化，然后再将其放入InnoDB引擎中执行。

条件简化

移除不必要的括号

select * from x where ((a = 5));

上面的括号很没必要，优化器就会直接去掉。

select * from x where a = 5;

等值传递

select * from x where b = a and a = 5;

同样的，虽然是两列比较，但是a的值只有一个，所以可以优化

select * from x where b = 5 and a = 5;

常量传递

select * from x where a = 5 and b > a;

可以优化为

select * from x where a = 5 and b > 5;

移除没用的条件

select * from x where a < 5 and b > 10 and b > a;

当前两个条件发生时，最后一个条件必然发生，所以可以优化

select * from x where a < 5 and b > 10;

表达式计算

select * from x where -a > -5;

优化器不会对其进行优化，而且这个坏处很多就是不能使用索引了，所以我们尽量让列单独出现，而不是在表达式计算中。

常量表检测

当表中只有一两条数据，或则使用主键或唯一列的索引等值查询的话就会被MySQL优化器视为常量表，直接将SQL语句优化成常量。

select * from table1 join table2 on table1.col1 = table2.col2 where table1 = 'a';

select table1的列都作为常量，table2.* from table2 where table1的常量col1 = table2.col2;

外连接消除

外连接呢，首先连接的顺序是固定的，故驱动表和被驱动表是固定不变的。所以是不能像内连接一样交换驱动表的。

但是呢，有一种情况

select * from table1 left join table2 on table1.col1 = table2.col2 where table2.col2 is not null；

我们设定了table2的列是非空的，这意味着什么，当table1匹配不到时设置table2列为null，但是却不满足搜索条件被过滤掉，所以左连接匹配失败null相当于是失效的。这个语句和内连接是没有区别的，直接将其优化为内连接即可。

所以当在外连接出现时，但是被驱动表拒绝空值时，此时外连接和内连接是可以互相转换的，而内连接可以通过交换驱动表来优化SQL查询成本。

子查询优化

子查询分类

  • 标量子查询
  • 列子查询
  • 行子查询
  • 表子查询

再分

  • 相关子查询
  • 不相关子查询

标量子查询

不相关标量子查询

select * from x where key1 = (select y.key1 from y where y.primarykey = 1);

对于不相关的标量子查询来说，就是先执行子查询，然后在对外部查询进行查询。

相关子查询

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

对于相关的标量子查询

  1. 首先取出外部的每条满足自身搜索条件的行，然后传入子查询对应列的值。
  2. 计算子查询的结果
  3. 在判断外部key1对于这个子查询给的结果是否满足条件，满足加入结果行。
  4. 继续循环回1，直到遍历完所有外层表的行。

其实和连接的流程差不多。

优化器对于标量的子查询并不需要什么优化，因为对于标量的子查询来说，数据量还算很小的了。

IN子查询优化

select * from x where key1 in (select key3 from y);

对于上述不相关的IN查询来说，如果IN子查询的参数少的话，还可以试着加载到内存，然后让外层查询对很多的条件进行比较。

但是如果子查询数据量一旦大了起来，内存无法全部加载完，或导致外层查询需要比较的参数太多，外层记录需要对于过多条件进行比较，导致索引无法使用，因为每一次都要使用索引，每次都要比较，还不如直接全表扫描。最后导致性能很低。

物化表优化

MySQL对这种in参数过多时，不会将子查询在作为外部的参数，而是直接创建一个临时表来存储子查询的结果。

  1. 将临时表的列为子查询结果的列，并对其进行去重。
  2. 临时表经过去重通常不会太大，创建的是Memory的存储引擎的临时表，并对其创建哈希索引。

子查询转物化表materialized_table后，我们还能将物化表和外层查询转换为连接的方式。

select x.* from x inner join materialized_table m on key1 = m.key3;

然后我们就可以用之前计算成本的知识来计算那个作为驱动表更合适了。

只有不相关子查询才能转换为物化表

semi-join优化

像上述结果一样，我们将查询结果转换为物化表，然后我们在把物化表转换为连接的方式。

我们为什么不能直接将子查询转换为连接的方式呢？这就是semi-join优化。

我们可以试试将其转换为如下语句

select x.* from x join y on key1 = key3;

三种情况

  • 被驱动表y的行不满足连接条件的，不能加入结果集。
  • 被驱动表y一个key3满足和驱动表x的key1相等且y表key3有且仅有一条，有一条记录加入结果集。
  • 被驱动表y有key3满足连接条件但是一个key3有很多条记录，就会有多条记录加入结果集。

能满足的条件就是y表的key3是主键或唯一列，不然就会出现多条的情况，这条语句就不等于原语句了。

但是此时semi join半连接概念的出现，在半连接的情况下，对于驱动表x来说，我们只关心被驱动表y是否有记录能够满足连接条件的，而不关心被驱动表y有几条能匹配，最后结果集只保存驱动表x的记录。

实现半连接semi join的方法。PS：semi join半连接只是一个概念。

  • Table pullout （子查询中表上拉）
    • 当子查询的查询列 ( 即select 的列 ) 是主键或唯一列，就是我们上面说的直接join 出来即可，因为不会出现多条的情况
  • DuplicateWeedout execution strategy （重复值消除策略）
    • 我们不是提到上述的我们自己改为join的方法会出现重复的情况吗，因为被驱动表的重复导致驱动表的重复。
    • 我们就直接创建一个临时表，把s1连接的结果记录id (是数据行的id可以这么理解把) 放入临时表中，当该数据行再次被加入时临时表就会抛出主键重复的异常，就不会加入重复行了。
  • LooseScan execution strategy （松散索引扫描）
    • 当子查询列key1有子查询表的索引，这样我们就可以通过索引访问，对于每个值，只访问一行，重复值不再访问，这样来防止出现多条记录。
  • Semi-join Materialization execution strategy (物化表半连接)
    • 将不相关子查询通过物化表的方式物化为临时表，没有重复行的情形，我们可以直接转换为连接。
  • FirstMatch execution strategy （首次匹配）
    • 取外连接的一条记录，然后和子查询进行一条一条的比较。最原始的方法

semi join使用条件：

  • 该子查询必须是和IN语句组成的布尔表达式，并且在外层的Where和on子句中出现。
  • 外层的搜索条件必须是用and 和in子查询连接的。
  • 子查询是单一的查询，不能union
  • 子查询不能包含group by、having、聚集函数
  • ...

EXISTS优化

如果不能使用semi join和物化表，我们还可以将in的语句改造成EXISTS语句。

将上述改造为如下语句。

select * from x where exists (select 1 from y where key3 = x.key1)

如果被驱动表key3有索引，就可以使用索引了啊 o(￣▽￣)ｄ。

这个算是下下策了。

posted @ 2022-05-25 12:32  大队长11  阅读(1192)  评论(0)  编辑  收藏  举报