详解MySQL 联合查询优化机制

 更新时间:2021年5月10日 15:00  点击:1846

MySQL 联合查询执行策略。

以一个 UNION 查询为例,MySQL 执行 UNION 查询时,会把他们当做一系列的单个查询语句,然后把对应的结果放入到临时表中,最终再读出来返回。在 MySQL中,每个独立的查询都是一个联合查询,从临时表读取返回结果也一样。

这种情形下,MySQL 的联合查询执行很简单——它将这里的联合查询当做是嵌套循环的联合查询。这意味着 MySQL 会运行一个循环去从数据表读取数据行,然而在运行一个嵌套循环从下一个表读取匹配的数据行。这个过程一直持续,直到找到联合查询中的所有匹配的数据行。然后再根据 SELECT 语句中需要的列去构建返回结果。如下面的查询语句所示:

SELECT tb1.col1, tb2.col2
FROM tb1 INNER JOIN tb2 USING(col3)
WHERE tb1.col1 IN(5,6);

实际转换为 MySQL可能执行的伪代码是下面这样的:

outer_iter = iterator over tb1 where col1 IN(5,6);
outer_row = outer_iter.next;
while outer_row
	inner_iter = iterator over tb2 where col3 = outer_row.col3;
	inner_row = inner_iter.next
    while inner_row
    	output [outer_row.col1, inner_row.col2];
        inner_row = inner_iter.next;
	end
    outer_row = outer.iter.next;
end

转换为伪代码后如下所示

outer_iter = iterator over tb1 where col1 IN(5,6);
outer_row = outer_iter.next;
while outer_row
	inner_iter = iterator over tb2 where col3 = outer_row.col3;
	inner_row = inner_iter.next
    if inner_row
        while inner_row
            output [outer_row.col1, inner_row.col2];
            inner_row = inner_iter.next;
        end
    else
    	output [outer_row.col1, NULL];
	end
    outer_row = outer.iter.next;
end

另一个方式可视化展现查询计划的方式是使用泳道图的形式。下面的图展示了 内连接查询的泳道图。

MySQL 执行的各类查询基本上都是相同的方式。例如,在 FROM 条件里需要先执行的子查询时,也是先将结果放入临时表,然后再把临时表当作普通表后联合来处理。MySQL 执行联合查询时也是使用临时表,然后将右连接查询重写为等价的左连接。简而言之,当前版本的 MySQL 会尽可能把各类查询转成这种方式处理(最新版本 MySQL5.6以后引入了更多的复杂的处理方式)。

当然,并不是所有合法的 SQL 查询语句都可以这么做,有些查询这么做的效果可能很差。

执行计划

MySQL不像其他很多数据库产品,它不会将查询语句产生字节码去执行查询计划。实际上,查询执行计划是一棵指令树,查询执行引擎根据这棵树产生查询结果。最终的查询计划包含了足够多的信息去重构最初的查询。如果在查询语句上执行EXPLAIN EXTENDED(MySQL 8以后不需要加 EXTENDED),然后再执行SHOW WARNINGS,就可以看到重构后的查询。

对于多表查询在概念上可以用树代表。例如,一个4张表的查询可能长得像下面的树一样。这在计算机里称为平衡树,

然而这不是 MySQL 执行查询的方式。如前所述,MySQL 总是从一张数据表开始,然后再从下一张表寻找匹配的数据行。因此,MySQL 的查询计划看起来像下面的左深连接树。

联合查询优化器

MySQL 的查询优化器中最重要的部分是联合查询优化器,由它来决定多表查询执行过程的最优顺序。通常可以通过多种联合查询的次序获取相同的结果。联合查询优化器试图估计这些方案的代价,然后选择最低代价的方案去执行。

下面是一个查询相同结果,但不同次序的联合查询示例。

SELECT film.film_id, film.title, film.release_year, actor.actor_id, actor.first_name, actor.last_name
FROM sakila.film
INNER JOIN sakila.film_actor USING(film_id)
INNER JOIN sakila.actor USING(actor_id);

这里面可能会有一些不同的查询方式。比如,MySQL 可以从 film 表开始,使用 film_actor 的film_id 索引去查找对应的 actor_di 值,然后再从 actor 表使用主键找到对应的 actor 数据行。而 Oracle 用户可能会表述为:“film 表是 film_actor 的驱动表,而 film_actor 是 actor 表的驱动表”。而使用 Explain 解析的结果如下:

******** 1.row ********
id: 1
select_type: SIMPLE
table: actor
type: ALL
possible_keys: PRIMARY
key: NULL
key_len: NULL
ref: NULL
rows: 200
Extra:
******** 2.row ********
id: 1
select_type: SIMPLE
table: film_actor
type: ref
possible_keys: PRIMARY, idx_fk_film_id
key: PRIMARY
key_len: 2
ref: sakila.film.film_id
rows: 1
Extra: USING index
******** 3.row ********
id: 1
select_type: SIMPLE
table: film
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 2
ref: sakila.film_actor.film_id
rows: 1
Extra: 

这个执行计划与我们猜想的有很大不同。MySQL 首先从 actor 表开始,然后次序是反向的。这是否真的更有效?我们可以在 EXPLAIN 上加上 STRAIGHT_JOIN 来避免优化:

EXPLAIN SELECT STRAIGHT_JOIN film.film_id, film.title, film.release_year, actor.actor_id, actor.first_name, actor.last_name
FROM sakila.film
INNER JOIN sakila.film_actor USING(film_id)
INNER JOIN sakila.actor USING(actor_id);

******** 1.row ********
id: 1
select_type: SIMPLE
table: film
type: ALL
possible_keys: PRIMARY
key: NULL
key_len: NULL
ref: NULL
rows: 951
Extra:
******** 2.row ********
id: 1
select_type: SIMPLE
table: film_actor
type: ref
possible_keys: PRIMARY, idx_fk_film_id
key: idx_fk_film_id
key_len: 2
ref: sakila.film.film_id
rows: 1
Extra: USING index
******** 3.row ********
id: 1
select_type: SIMPLE
table: actor
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 2
ref: sakila.film_actor.actor_id
rows: 1
Extra: 

这解释了为什么MySQL 为什么需要反序执行查询,这会使得检查的数据行更少。

  • 先查询 film 表会需要对 film_actor 和 actor 进行951次查询(最外层循环)
  • 如果将 actor表前置,则只需要对其他表进行200次查询。

从这个例子可以看出,MySQL 的联合查询优化器可以通过调整查询表次序降低查询代价。重新排序后的联合查询通常是很有效的优化,通常是几倍性能的提高。如果没有性能提高的话,也可以使用 STRAIGHT_JOIN 来避免重排序,而使用我们自己认为最好的查询方式。这种情况实际遇到的会很少,大部分情况下,联合查询优化器都会比人做得更出色。

联合查询优化器视图以最低完成代价构建一个查询执行树。如果有可能,它会从全部的单表计划开始,检查所有可能的子树组合。不幸的是,一个 N 张表的联合查询会有 N 个阶乘的组合次序数量。这被称之为所有可能的查询计划的搜索空间,这个数量增长非常快。一个10张表的联合索引会有3628800个不同的方式!一旦搜索空间增长到过大,会导致查询的优化十分久,这时候服务端会停止做全量分析,替代以类似贪婪算法的方式完成优化。这个数量通过 optimizer_search_depth 系统变量控制,可以自己修改该参数。

[!--infotagslink--]

相关文章

  • Mybatis Plus select 实现只查询部分字段

    这篇文章主要介绍了Mybatis Plus select 实现只查询部分字段的操作,具有很好的参考价值,希望对大家有所帮助。如有错误或未考虑完全的地方,望不吝赐教...2021-09-01
  • MySQL性能监控软件Nagios的安装及配置教程

    这篇文章主要介绍了MySQL性能监控软件Nagios的安装及配置教程,这里以CentOS操作系统为环境进行演示,需要的朋友可以参考下...2015-12-14
  • 详解Mysql中的JSON系列操作函数

    新版 Mysql 中加入了对 JSON Document 的支持,可以创建 JSON 类型的字段,并有一套函数支持对JSON的查询、修改等操作,下面就实际体验一下...2016-08-23
  • MyBatisPlus-QueryWrapper多条件查询及修改方式

    这篇文章主要介绍了MyBatisPlus-QueryWrapper多条件查询及修改方式,具有很好的参考价值,希望对大家有所帮助。如有错误或未考虑完全的地方,望不吝赐教...2022-06-27
  • 深入研究mysql中的varchar和limit(容易被忽略的知识)

    为什么标题要起这个名字呢?commen sence指的是那些大家都应该知道的事情,但往往大家又会会略这些东西,或者对这些东西一知半解,今天我总结下自己在mysql中遇到的一些commen sense类型的问题。 ...2015-03-15
  • Oracle使用like查询时对下划线的处理方法

    这篇文章主要介绍了Oracle使用like查询时对下划线的处理方法,本文给大家介绍的非常详细,对大家的学习或工作具有一定的参考借鉴价值,需要的朋友可以参考下...2021-03-16
  • 解决mybatis-plus 查询耗时慢的问题

    这篇文章主要介绍了解决mybatis-plus 查询耗时慢的问题,具有很好的参考价值,希望对大家有所帮助。如有错误或未考虑完全的地方,望不吝赐教...2021-07-04
  • Mysql效率优化定位较低sql的两种方式

    关于mysql效率优化一般通过以下两种方式定位执行效率较低的sql语句。通过慢查询日志定位那些执行效率较低的 SQL 语句,用 --log-slow-queries[=file_name] 选项启动时, mysqld 会 写一个包含所有执行时间超过 long_quer...2015-11-08
  • MySQL 字符串拆分操作(含分隔符的字符串截取)

    这篇文章主要介绍了MySQL 字符串拆分操作(含分隔符的字符串截取),具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧...2021-02-22
  • mysql的3种分表方案

    一、先说一下为什么要分表:当一张的数据达到几百万时,你查询一次所花的时间会变多,如果有联合查询的话,有可能会死在那儿了。分表的目的就在于此,减小数据库的负担,缩短查询时间。根据个人经验,mysql执行一个sql的过程如下:1...2014-05-31
  • Android用MemoryFile文件类读写进行性能优化

    java开发的Android应用,性能一直是一个大问题,,或许是Java语言本身比较消耗内存。本文我们来谈谈Android 性能优化之MemoryFile文件读写。 Android匿名共享内存对外A...2016-09-20
  • MySQL针对Discuz论坛程序的基本优化教程

    过了这么久,discuz论坛的问题还是困扰着很多网友,其实从各论坛里看到的问题总结出来,很关键的一点都是因为没有将数据表引擎转成InnoDB导致的,discuz在并发稍微高一点的环境下就表现的非常糟糕,产生大量的锁等待,这时候如果...2015-11-24
  • 101个MySQL的配置和优化以及备份的经验提示

    MySQL是一个功能强大的开源数据库。随着越来越多的数据库驱动的应用程序,人们一直在推动MySQL发展到它的极限。这里是101条调节和优化 MySQL安装的技巧。一些技巧是针对特定的安装环境的,但这些思路是通用的。我已经把...2013-09-11
  • Windows服务器MySQL中文乱码的解决方法

    我们自己鼓捣mysql时,总免不了会遇到这个问题:插入中文字符出现乱码,虽然这是运维先给配好的环境,但是在自己机子上玩的时候咧,总得知道个一二吧,不然以后如何优雅的吹牛B。...2015-03-15
  • Centos5.5中安装Mysql5.5过程分享

    这几天在centos下装mysql,这里记录一下安装的过程,方便以后查阅Mysql5.5.37安装需要cmake,5.6版本开始都需要cmake来编译,5.5以后的版本应该也要装这个。安装cmake复制代码 代码如下: [root@local ~]# wget http://www.cm...2015-03-15
  • Angular性能优化之第三方组件和懒加载技术

    这篇文章主要介绍了Angular性能优化之第三方组件和懒加载技术,对性能优化感兴趣的同学,可以参考下...2021-05-11
  • 忘记MYSQL密码的6种常用解决方法总结

    首先要声明一点,大部分情况下,修改MySQL密码是需要有mysql里的root权限的...2013-09-11
  • 用VirtualBox构建MySQL测试环境

    宿主机使用网线的时候,客户机在Bridged Adapter模式下,使用Atheros AR8131 PCI-E Gigabit Ethernet Controller上网没问题。 宿主机使用无线的时候,客户机在Bridged Adapter模式下,使用可选项里唯一一个WIFI选项,Microsoft Virtual Wifi Miniport Adapter也无法上网,故弃之。...2013-09-19
  • Node实现搜索框进行模糊查询

    这篇文章主要为大家详细介绍了Node实现搜索框进行模糊查询,文中示例代码介绍的非常详细,具有一定的参考价值,感兴趣的小伙伴们可以参考一下...2021-06-28
  • MySQL中在查询结果集中得到记录行号的方法

    如果需要在查询语句返回的列中包含一列表示该条记录在整个结果集中的行号, ISO SQL:2003 标准提出的方法是提供 ROW_NUMBER() / RANK() 函数。 Oracle 中可以使用标准方法(8i版本以上),也可以使用非标准的 ROWNUM ; MS SQL...2015-03-15