`

Mysql 多表联合查询效率分析及优化(转)

阅读更多

文章出自:http://blog.csdn.net/hguisu/article/details/5731880

 

1. 多表连接类型

1. 笛卡尔积(交叉连接) 在MySQL中可以为CROSS JOIN或者省略CROSS即JOIN,或者使用','  如:

 

SELECT * FROM table1 CROSS JOIN table2   
SELECT * FROM table1 JOIN table2   
SELECT * FROM table1,table2

      由于其返回的结果为被连接的两个数据表的乘积,因此当有WHERE, ON或USING条件的时候一般不建议使用,因为当数据表项目太多的时候,会非常慢。一般使用LEFT [OUTER] JOIN或者RIGHT [OUTER] JOIN

 

2.   内连接INNER JOIN 在MySQL中把INNER JOIN叫做等值连接,即需要指定等值连接条件在MySQL中CROSS和INNER JOIN被划分在一起。 join_table: table_reference [INNER | CROSS] JOIN table_factor [join_condition]

3. MySQL中的外连接,分为左外连接和右连接,即除了返回符合连接条件的结果之外,还要返回左表(左连接)或者右表(右连接)中不符合连接条件的结果,相对应的使用NULL对应。

 

例子:

user表:

id | name
———
1 | libk
2 | zyfon
3 | daodao

user_action表:

user_id | action
—————
1 | jump
1 | kick
1 | jump
2 | run
4 | swim

 

sql:

 

select id, name, action from user as u  
left join user_action a on u.id = a.user_id  

result:
id | name    | action
——————————–
1  | libk         | jump           ①
1  | libk         | kick             ②
1  | libk         | jump           ③
2  | zyfon      | run               ④
3  | daodao | null              ⑤ 

 

 

分析:
注意到user_action中还有一个user_id=4, action=swim的纪录,但是没有在结果中出现,
而user表中的id=3, name=daodao的用户在user_action中没有相应的纪录,但是却出现在了结果集中
因为现在是left join,所有的工作以left为准.
结果1,2,3,4都是既在左表又在右表的纪录,5是只在左表,不在右表的纪录

 

工作原理:

从左表读出一条,选出所有与on匹配的右表纪录(n条)进行连接,形成n条纪录(包括重复的行,如:结果1和结果3),如果右边没有与on条件匹配的表,那连接的字段都是null.然后继续读下一条。

引申:
我们可以用右表没有on匹配则显示null的规律, 来找出所有在左表,不在右表的纪录, 注意用来判断的那列必须声明为not null的。
如:

sql:

 

select id, name, action from user as u  
left join user_action a on u.id = a.user_id  
where a.user_id is NULL  

 (注意:

 

        1.列值为null应该用is null 而不能用=NULL
         2.这里a.user_id 列必须声明为 NOT NULL 的.

上面sql的result:
id | name | action
————————–
3 | daodao | NULL

——————————————————————————–

一般用法:

a. LEFT [OUTER] JOIN:

除了返回符合连接条件的结果之外,还需要显示左表中不符合连接条件的数据列,相对应使用NULL对应

SELECT column_name FROM table1 LEFT [OUTER] JOIN table2 ON table1.column=table2.column 

b. RIGHT [OUTER] JOIN:

RIGHT与LEFT JOIN相似不同的仅仅是除了显示符合连接条件的结果之外,还需要显示右表中不符合连接条件的数据列,相应使用NULL对应 

SELECT column_name FROM table1 RIGHT [OUTER] JOIN table2 ON table1.column=table2.column 

Tips:

1. on a.c1 = b.c1 等同于 using(c1)
2. INNER JOIN 和 , (逗号) 在语义上是等同的
3. 当 MySQL 在从一个表中检索信息时,你可以提示它选择了哪一个索引。
如果 EXPLAIN 显示 MySQL 使用了可能的索引列表中错误的索引,这个特性将是很有用的。
通过指定 USE INDEX (key_list),你可以告诉 MySQL 使用可能的索引中最合适的一个索引在表中查找记录行。

可选的二选一句法 IGNORE INDEX (key_list) 可被用于告诉 MySQL 不使用特定的索引。如: 

SELECT * FROM table1 USE INDEX (key1,key2)  
WHERE key1=1 AND key2=2 AND key3=3;  
SELECT * FROM table1 IGNORE INDEX (key3)  
WHERE key1=1 AND key2=2 AND key3=3;  

 

2. 表连接的约束条件

添加显示条件WHERE, ON, USING

1. WHERE子句

mysql>

SELECT * FROM table1,table2 WHERE table1.id=table2.id;

2. ON

mysql>

SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id;  
SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id  
LEFT JOIN table3 ON table2.id=table3.id;  

 3. USING子句,如果连接的两个表连接条件的两个列具有相同的名字的话可以使用USING

 例如:

SELECT FROM LEFT JOIN USING ()

 

连接多于两个表的情况举例:

mysql>

SELECT artists.Artist, cds.title, genres.genre   
FROM cds   
LEFT JOIN genres ON cds.genreID = genres.genreID   
LEFT JOIN artists ON cds.artistID = artists.artistID;  

 或者 mysql>

SELECT artists.Artist, cds.title, genres.genre   
FROM cds   
LEFT JOIN genres ON cds.genreID = genres.genreID   
LEFT JOIN artists -> ON cds.artistID = artists.artistID  
WHERE (genres.genre = 'Pop');   

 --------------------------------------------

 另外需要注意的地方 在MySQL中涉及到多表查询的时候,需要根据查询的情况,想好使用哪种连接方式效率更高。

 1. 交叉连接(笛卡尔积)或者内连接 [INNER | CROSS] JOIN

 2. 左外连接LEFT [OUTER] JOIN或者右外连接RIGHT [OUTER] JOIN 注意指定连接条件WHERE, ON,USING.

3. MySQL如何优化LEFT JOIN和RIGHT JOIN

在MySQL中,A LEFT JOIN B join_condition执行过程如下:

1)·  根据表A和A依赖的所有表设置表B。

2)·  根据LEFT JOIN条件中使用的所有表(除了B)设置表A。

3)·   LEFT JOIN条件用于确定如何从表B搜索行。(换句话说,不使用WHERE子句中的任何条件)。

4)·  可以对所有标准联接进行优化,只是只有从它所依赖的所有表读取的表例外。如果出现循环依赖关系,MySQL提示出现一个错误。

5)· 进行所有标准WHERE优化。

6)· 如果A中有一行匹配WHERE子句,但B中没有一行匹配ON条件,则生成另一个B行,其中所有列设置为NULL。

7)· 如果使用LEFT JOIN找出在某些表中不存在的行,并且进行了下面的测试:WHERE部分的col_name IS NULL,其中col_name是一个声明为 NOT NULL的列,MySQL找到匹配LEFT JOIN条件的一个行后停止(为具体的关键字组合)搜索其它行。

RIGHT JOIN的执行类似LEFT JOIN,只是表的角色反过来。

联接优化器计算表应联接的顺序。LEFT JOIN和STRAIGHT_JOIN强制的表读顺序可以帮助联接优化器更快地工作,因为检查的表交换更少。请注意这说明如果执行下面类型的查询,MySQL进行全扫描b,因为LEFT JOIN强制它在d之前读取:

SELECT *  
FROM a,b LEFT JOIN c ON (c.key=a.key) LEFT JOIN d ON (d.key=a.key)  
WHERE b.key=d.key;  

 在这种情况下修复时用a的相反顺序,b列于FROM子句中:

SELECT *  
FROM b,a LEFT JOIN c ON (c.key=a.key) LEFT JOIN d ON (d.key=a.key)  
WHERE b.key=d.key;  

 MySQL可以进行下面的LEFT JOIN优化:如果对于产生的NULL行,WHERE条件总为假,LEFT JOIN变为普通联接。

例如,在下面的查询中如果t2.column1为NULL,WHERE 子句将为false:

SELECT * FROM t1 LEFT JOIN t2 ON (column1) WHERE t2.column2=5;  

 因此,可以安全地将查询转换为普通联接:

SELECT * FROM t1, t2 WHERE t2.column2=5 AND t1.column1=t2.column1;  

 这样可以更快,因为如果可以使查询更佳,MySQL可以在表t1之前使用表t2。为了强制使用表顺序,使用STRAIGHT_JOIN。

 

 

 

 

分享到:
评论

相关推荐

    详解Mysql多表联合查询效率分析及优化

    本文将深入探讨多表联合查询的效率分析以及优化策略。 首先,我们要了解多表连接的几种基本类型: 1. **笛卡尔积(Cross Join)**:这种连接方式返回两个表的全部组合,即每个左表的记录与右表的所有记录相配对。...

    基于MySql多表分页查询优化技术.pdf

    在需要进行频繁的多表联合查询的应用中,优化分页查询性能显得尤为重要。MySql数据库性能的提升,对各类依赖数据库的应用系统具有深远的影响。 作者团队在文章中提出的方法,基于实验和实际应用数据,证实了其对于...

    两种mysql递归tree查询效率-mysql递归tree

    本文通过对比两种MySQL递归树查询方法——使用自连接和递归联合以及利用变量和循环,详细分析了它们各自的实现原理及适用场景。在实际应用中,根据具体需求和数据特点选择合适的方法至关重要。希望本文能为开发人员...

    mysql多表联合查询操作实例分析

    MySQL的多表联合查询是数据库操作中非常重要的一个概念,特别是在处理复杂的数据关系时,能够有效地从多个表中提取所需信息。本篇文章将深入探讨这一主题,通过实例分析来阐述其语法、功能以及操作技巧。 首先,让...

    MySQL索引原理及慢查询优化1

    MySQL索引原理及慢查询优化是数据库管理中的重要主题,尤其是在高并发、大数据量的互联网环境中,优化查询性能对于系统的整体效能至关重要。MySQL作为广泛使用的开源关系型数据库,其索引机制和查询优化技巧是开发者...

    mysql Join使用以及优化

    MySQL数据库中Join操作的使用及优化是一项重要的技能,它涉及到数据库中表与表之间的关联查询。在执行Join操作时,数据库管理系统需要按照某种算法将多个表中的数据记录联合起来,并返回查询结果。Join操作的主要...

    mysql性能的优化

    MySQL性能优化是一个综合性的过程,涉及到SQL查询优化、数据库结构优化以及MySQL服务器配置等多个方面。通过上述方法和技术的应用,可以显著提高MySQL的运行效率,降低资源消耗,最终实现更好的用户体验和服务质量。

    MySQL数据库性能优化分析.pdf

    MySQL数据库性能优化分析是数据库优化的重要环节,对于提高数据库的运行效率和工作能力具有重要的意义。本文将从数据库运行语句、数据库索引以及构造设计等层面对MySQL数据库进行深入的分析,并对数据库的索引进行...

    25.3 MySQL 多表查询

    综上所述,理解和掌握 MySQL 的多表查询是提高数据库管理效率的关键。通过熟练运用各种连接类型、子查询和联合查询,你可以更有效地处理复杂的数据需求,从而在业务场景中发挥出 MySQL 的强大功能。

    04-VIP-Mysql索引优化实战一.pdf

    当在查询中对联合索引的第一个字段使用范围条件时,如`name > 'LiLei'`,MySQL可能会选择不使用该索引,因为它认为范围查询会导致大量行被扫描,导致回表操作效率低下,因此可能选择全表扫描。这可以通过`EXPLAIN`...

    实例讲解MYSQL联合查询

    MySQL是一种广泛使用的开源关系型数据库管理系统,其联合查询(JOIN)功能是数据库操作中的核心部分,用于合并多个数据表中的相关记录,以便进行更复杂的分析和检索。在本实例讲解中,我们将深入探讨MySQL联合查询的...

    MySQL优化 实战视频课程

    - **水平分区**:根据某列值将大表拆分成多个小表,提高查询效率。 - **垂直分区**:将一张大表按照列进行拆分,降低单个表的宽度。 - **组合使用**:结合水平和垂直分区技术,实现更高效的数据库管理。 #### 五、...

    解决Mysql数据库实现多表联合查询

    MySQL,作为一款广泛使用的SQL数据库系统,提供了多种方式进行多表联合查询,以便用户可以有效地从不同表中获取所需信息。本文将详细讨论如何在MySQL中实现多表联合查询,并解决按照特定字段排序展示数据的问题。 ...

    mysql的索引优化

    1. **提高查询速度**:当执行查询时,如果没有索引,MySQL需要从头到尾遍历整个表的所有记录来寻找符合条件的结果。随着表中记录数量的增加,这种操作的时间成本会显著增加。相反,如果有适当的索引,MySQL可以直接...

    查询效率提升10倍!3种优化方案,帮你解决MySQL深分页问题.doc

    本文总结了三种优化方案,解决 MySQL 深分页问题,查询效率直接提升 10 倍。 知识点: 1. 深分页问题:深分页问题是指在分页查询中,当翻页过多的时候,查询效率急剧下降。 解决方案: 2. 准备数据:创建一张...

    MySQL数据库性能优化研究.docx

    3. 优化查询语句:避免使用复杂子查询和联合操作,减少全表扫描。使用 EXPLAIN 分析查询计划,找出潜在的性能问题并进行优化。 四、数据分片 对于超大数据量的场景,可以考虑使用数据分片技术。将数据分散到多个...

    小议ORACLE和MYSQL的联合查询差异.pdf

    通过模拟实验,创建了两个完全相同的表(city和person)并在Oracle和MySQL中执行联合查询,发现随着person表记录数量的增加,MySQL的执行效率逐渐落后于Oracle。这进一步证实了在处理大量数据时,Oracle的优化策略和...

    mysql性能优化综述

    - **分表**:将一张大表分成多个小表,既可以提高查询速度,又便于管理和维护。具体方法包括: - **Merge分表**:将数据分布在多个子表中,通过主表统一管理。 - **纵向分割**:根据字段的更新频率将其分布到不同...

    【表119,,促销计划执行表】 mysql 多表查询 执行计划.doc

    当数据分布在多个相关联的表中时,为了获取全面的信息,我们需要联合这些表进行查询。本篇将重点讨论如何利用MySQL进行多表查询以及执行计划的选择。 一、JOIN操作 在MySQL中,有多种JOIN类型用于连接两个或多个表...

Global site tag (gtag.js) - Google Analytics