MySQL之SQL语句优化

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

移除不必要的括号

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

上面的圆括号是不必要的,优化器将直接删除它们。

[En]

The parentheses above are unnecessary and the optimizer will remove them directly.

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;

当当前的两个条件发生时,最后一个条件必然会发生,所以可以进行优化。

[En]

When the current two conditions occur, the last condition is bound to occur, so it can be optimized.

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

表达式计算

select * from x where -a > -5;

优化器不会对其进行优化,很多缺点是不能使用索引,因此我们尝试使列单独出现,而不是在表达式计算中出现。

[En]

The optimizer does not optimize it, and a lot of the downside is that indexes cannot be used, so we try to make the columns appear alone rather than in expression evaluation.

常量表检测

当表中只有一两条数据,或则使用主键或唯一列的索引等值查询的话就会被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;

对于外部连接,首先固定连接的顺序,因此从动台和从动台是固定的。因此,您不能像内部联接一样交换驱动程序表。

[En]

As for the external connection, the order of the connection is fixed first, so the driven table and the driven table are fixed. So you can’t exchange driver tables like internal joins.

但是呢,有一种情况

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);

对于不相关的标量量子查询,先执行子查询,再查询外部查询。

[En]

For unrelated scalar quantum queries, the subquery is executed first, and then the external query is queried.

相关子查询

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

对于相关的标量子查询

事实上,这与连接的过程类似。

[En]

In fact, it is similar to the process of connecting.

优化器不需要对标量子查询进行任何优化,因为标量子查询的数据量很小。

[En]

The optimizer does not need any optimization for scalar subqueries because the amount of data is small for scalar subqueries.

IN子查询优化

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

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

但是,如果子查询中的数据量变大,内存不能完全加载,或者外部查询需要比较太多的参数,外部记录需要比较太多的条件,所以无法使用索引,因为每次都会使用索引,最好是直接扫描整个表。最终,性能是非常低的。

[En]

However, if the amount of data in the sub-query becomes large, the memory cannot be fully loaded, or the outer query needs to compare too many parameters, and the outer record needs to compare too many conditions, so that the index cannot be used, because the index is used every time, and it is better to scan the whole table directly. In the end, the performance is very low.

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

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

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

然后,我们可以使用以前的成本计算知识来计算哪个更适合作为动因表。

[En]

Then we can use the previous knowledge of costing to calculate which is more appropriate as the driver table.

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

与上面的结果一样,我们将查询结果转换为物化表,然后将该物化表转换为联接。

[En]

Like the above results, we convert the query results to a materialized table, and then we are converting the materialized table to a join.

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

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

[En]

We can try to convert it to the following statement

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、聚集函数

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

将上述改造为如下语句。

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

如果被驱动表key3有索引,就可以使用索引了啊 o( ̄▽ ̄)d。

这个算是下下策了。

Original: https://www.cnblogs.com/duizhangz/p/16306834.html
Author: 大队长11
Title: MySQL之SQL语句优化

原创文章受到原创版权保护。转载请注明出处:https://www.johngo689.com/505348/

转载文章受原作者版权保护。转载请注明原作者出处!

(0)

大家都在看

亲爱的 Coder【最近整理,可免费获取】👉 最新必读书单  | 👏 面试题下载  | 🌎 免费的AI知识星球