今天遇到一个left join优化的问题,搞了一下午,中间查了不少资料,对MySQL的查询计划还有查询优化有了更进一步的了解,做一个简单的记录:
select c.* from hotel_info_original c
left join hotel_info_collection h
on c.hotel_type=h.hotel_type and c.hotel_id =h.hotel_id
where h.hotel_id is null
这个sql是用来查询出c表中有h表中无的记录,所以想到了用left join的特性(返回左边全部记录,右表不满足匹配条件的记录对应行返回null)来满足需求,不料这个查询非常慢。先来看查询计划:
rows代表这个步骤相对上一步结果的每一行需要扫描的行数,可以看到这个sql需要扫描的行数为35773*8134,非常大的一个数字。本来c和h表的记录条数分别为40000+和10000+,这几乎是两个表做笛卡尔积的开销了(select * from c,h)。
于是我上网查了下MySQL实现join的原理,原来MySQL内部采用了一种叫做 nested loop join的算法。Nested Loop Join 实际上就是通过驱动表的结果集作为循环基础数据,然后一条一条的通过该结果集中的数据作为过滤条件到下一个表中查询数据,然后合并结果。如果还有第三个参与 Join,则再通过前两个表的 Join 结果集作为循环基础数据,再一次通过循环查询条件到第三个表中查询数据,如此往复,基本上MySQL采用的是最容易理解的算法来实现join。所以驱动表的选择非常重要,驱动表的数据小可以显著降低扫描的行数。
那么为什么一般情况下join的效率要高于left join很多?很多人说不明白原因,只人云亦云,我今天下午感悟出来了一点。一般情况下参与联合查询的两张表都会一大一小,如果是join,在没有其他过滤条件的情况下MySQL会选择小表作为驱动表,但是left join一般用作大表去join小表,而left join本身的特性决定了MySQL会用大表去做驱动表,这样下来效率就差了不少,如果我把上面那个sql改成
select c.* from hotel_info_original c
join hotel_info_collection h
on c.hotel_type=h.hotel_type and c.hotel_id =h.hotel_id
查询计划如下:
很明显,MySQL选择了小表作为驱动表,再配合(hotel_id,hotel_type)上的索引瞬间降低了好多个数量级。。。。。
另外,我今天还明白了一个关于left join 的通用法则,即:如果where条件中含有右表的非空条件(除开is null),则left join语句等同于join语句,可直接改写成join语句。
后记:
随着查看MySQL reference manual对这个问题进行了更进一步的了解。MySQL在执行join时会把join分为system/const/eq_ref/ref/range/index/ALl等好几类,连接的效率从前往后
依次递减,对于我的第一个sql,连接类型是index,所以几乎是全表扫描的效果。但是我很奇怪我在(hotel_id,hotel_type)两列上声明了unique key,根据官方文档连接类型应该是eq_ref才对,
这个问题一直困扰了我两天,在google和stackoverflow上都没有找到能够解释这个问题的文章,莫非我这个问题无解了?抱着解决这个问题的决心今天又翻看了一遍MySQL官方文档
关于优化查询的部分,看到了这样一句:这里的一个问题是MySQL能更高效地在声明具有相同类型和尺寸的列上使用索引。我感觉我找到了问题所在,于是我将original和 collection表的(hotel_type,hotel_id)的encoding和collation(决定字符比较的规则)全部改成统一的utf8_general_ci,然后再次运行第一条sql的查询计划,得到如下结果:
连接类型已经由index优化到了ref,如果将hotel_type申明为not null可以优化到eq_ref,不过这里影响不大了,优化后这条sql能在0.01ms内运行完。
那么如何优化left join:
1、条件中尽量能够过滤一些行将驱动表变得小一点,用小表去驱动大表
2、右表的条件列一定要加上索引(主键、唯一索引、前缀索引等),最好能够使type达到range及以上(ref,eq_ref,const,system)
3、无视以上两点,一般不要用left join~~!
![点击查看原始大小图片](http://dl2.iteye.com/upload/attachment/0091/2276/9e4ff2ab-cbed-3c5f-a3ba-73783943f397-thumb.jpeg)
- 大小: 33.2 KB
![点击查看原始大小图片](http://dl2.iteye.com/upload/attachment/0091/2278/f3feb79b-f460-3e2b-8005-5f396f1760c8-thumb.jpeg)
- 大小: 32.1 KB
![点击查看原始大小图片](http://dl2.iteye.com/upload/attachment/0091/2282/55fd24ff-b161-3b1f-a49b-ab03a3744884-thumb.jpeg)
- 大小: 39 KB
分享到:
相关推荐
在没有索引或者索引设计不合理的情况下,Join操作可能退化为嵌套循环,即每个表中的记录都需要与另一个表中的每条记录进行比较,从而产生大量的I/O操作,增加查询时间。 在设计Join查询时,通常希望驱动表(即在...
本文实例讲述了MySQL 多表关联一对多查询实现取最新一条数据的方法。分享给大家供大家参考,具体如下: MySQL 多表关联一对多查询取最新的一条数据 遇到的问题 多表关联一对多查询取最新的一条数据,数据出现重复 ...
另一个优化策略是使用JOIN操作时要谨慎。JOINs可以合并多个表的数据,但如果不正确使用,可能会导致性能下降。使用INNER JOIN、LEFT JOIN、RIGHT JOIN等不同类型的JOIN取决于你的需求,合理设计表结构和选择合适的...
cross join 将每个表中的每行记录与另一个表中的每行记录组合。 inner join 的另一种写法 inner join 还有一种写法,即使用逗号分隔两个表名,然后在 WHERE 子句中指定连接条件。例如: SELECT * FROM `xs`, `cj`...
SQL语句优化对于大型数据库系统至关重要,特别是在涉及多表查询时,JOIN、LEFT JOIN 和 RIGHT JOIN 的高效运用能显著提升查询速度。以下将详细探讨这些JOIN类型以及相关的优化策略。 JOIN原理: JOIN操作实际上是...
ON 子句与 WHERE 子句的不同 一种更好地理解带有 WHERE … IS NULL 子句的复杂匹配条件的简单方法 Matching-Conditions 与 Where-conditions 的不同 关于 “A LEFT JOIN B ON 条件表达式” 的一点提醒 ON 条件(...
视图是从一个或多个表中创建的虚拟表,它不包含实际的数据,但可以根据需求提供定制的查询结果。 7. 用户权限管理 MySQL提供了一套完善的权限系统,可以控制用户对数据库、表甚至列的操作权限。`GRANT`和`REVOKE`...
本篇文章将深入探讨如何在MySQL中有效地使用多个`LEFT JOIN`进行连接查询,并通过示例分析其性能优化。 首先,我们要理解`LEFT JOIN`的工作原理。`LEFT JOIN`返回左表的所有记录,即使右表没有匹配的记录。如果右表...
优化查询语句是 MySQL 查询性能优化的另一个重要方面。优化查询语句可以减少查询时间、提高查询效率。MySQL 提供了多种查询优化技术,包括使用 UNION 优化、使用子查询优化、使用 JOIN 优化等。 6.3 MySQL 查询优化...
在SQL查询中,JOIN操作是连接不同表的关键技术,它允许我们从多个相关的表中获取数据。本篇文章将深入探讨四种基本的JOIN类型:LEFT JOIN(左连接)、RIGHT JOIN(右连接)、INNER JOIN(内连接)以及OUTER JOIN(外...
为了更好地理解这两种查询方式的特点以及如何优化它们的性能,我们先对它们进行一个简要的介绍。 **子查询(Subquery)**:子查询是指在一个查询语句中嵌套另一个查询语句的方式。子查询可以出现在SELECT、FROM、...
LEFT JOIN查询的效率分析通常涉及到对查询逻辑的理解和优化。例如,如果LEFT JOIN连接的两个表是user和user_action,而我们要获取每个用户的所有动作,即使某些用户没有动作记录。查询可能会像这样: ```sql SELECT...
当执行`LEFT JOIN`时,数据库会生成一个中间的临时表,包含了左表的所有行以及与右表匹配的行。如果右表中没有匹配的行,对应的结果将是`NULL`值。`ON` 子句定义了连接两个表的条件,它在生成临时表时被应用。这意味...
MySQL中的`NOT IN`, `LEFT JOIN`, `IS NULL`, 和 `NOT EXISTS` 是四种不同的SQL查询方式,它们在特定情况下可以实现相似的功能,但实际执行效率可能会有很大差异。本文主要探讨这四种方法在处理大数据量时的性能表现...
为了更好地理解和分析查询优化过程,首先我们需要准备一个测试环境。本例使用的是 MySQL 5.1.49-debug-log 版本,InnoDB 存储引擎。 表结构定义如下: ```sql CREATE TABLE `nkeys` ( `c1` int(11) NOT NULL, `c2...
总之,MySQL的LEFT JOIN是一个强大的工具,但在使用时需要谨慎,合理规划表结构,优化查询语句,避免性能陷阱,以确保高效且准确的数据获取。通过深入了解LEFT JOIN的工作原理和潜在问题,开发者可以更好地应对各种...
结合使用`LEFT JOIN`和`UNION ALL`,我们可以构造一个复杂的查询,首先使用`LEFT JOIN`从文章表中获取数据,并关联到分类表以获取对应的分类名称,然后通过`UNION ALL`将不同条件下的查询结果整合在一起。...
MySQL查询优化器是数据库管理系统(DBMS)的一个核心组件,它负责分析SQL查询语句,并决定执行查询的最佳路径或方式,从而保证查询的效率和性能。优化器的最终目标是找到一种成本最低的方式来执行SQL语句,它会考虑...