MySQL如何优化

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

在MySQL中，A LEFT JOIN B join_condition执行过程如下：

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

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

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

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

·         进行所有标准WHERE优化。

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

·         如果使用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。

7.2.10. MySQL如何优化嵌套Join

表示联接的语法允许嵌套联接。下面的讨论引用了13.2.7.1节，“JOIN语法”中描述的联接语法。

同SQL标准比较，table_factor语法已经扩展了。后者只接受table_reference，而不是括号内所列的。

table_reference项列表内的每个逗号等价于内部联接，这是一个保留扩展名。例如：

SELECT * FROM t1 LEFT JOIN (t2, t3, t4)
                 ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c)

等价于：

SELECT * FROM t1 LEFT JOIN (t2 CROSS JOIN t3 CROSS JOIN t4)
                 ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c)

在MySQL中，CROSS JOIN语法上等价于INNER JOIN (它们可以彼此代替。在标准SQL中，它们不等价。INNER JOIN结合ON子句使用；CROSS JOIN 用于其它地方。

总的来说，在只包含内部联接操作的联接表达式中可以忽略括号。删除括号并将操作组合到左侧后，联接表达式：

t1 LEFT JOIN (t2 LEFT JOIN t3 ON t2.b=t3.b OR t2.b IS NULL)
   ON t1.a=t2.a

转换为表达式：

(t1 LEFT JOIN t2 ON t1.a=t2.a) LEFT JOIN t3
    ON t2.b=t3.b OR t2.b IS NULL

但是这两个表达式不等效。要说明这点，假定表t1、t2和t3有下面的状态：

·         表t1包含行{1}、{2}

·         表t2包含行{1,101}

·         表t3包含行{101}

在这种情况下，第1个表达式返回包括行{1,1,101,101}、{2,NULL,NULL,NULL}的结果，第2个表达式返回行{1,1,101,101}、{2,NULL,NULL,101}：

mysql> SELECT *
    -> FROM t1
    ->      LEFT JOIN
    ->      (t2 LEFT JOIN t3 ON t2.b=t3.b OR t2.b IS NULL)
    ->      ON t1.a=t2.a;
+------+------+------+------+
| a    | a    | b    | b    |
+------+------+------+------+
|    1 |    1 |  101 |  101 |
|    2 | NULL | NULL | NULL |
+------+------+------+------+
 
mysql> SELECT *
    -> FROM (t1 LEFT JOIN t2 ON t1.a=t2.a)
    ->      LEFT JOIN t3
    ->      ON t2.b=t3.b OR t2.b IS NULL;
+------+------+------+------+
| a    | a    | b    | b    |
+------+------+------+------+
|    1 |    1 |  101 |  101 |
|    2 | NULL | NULL |  101 |
+------+------+------+------+

在下面的例子中，外面的联接操作结合内部联接操作使用：

t1 LEFT JOIN (t2，t3) ON t1.a=t2.a

该表达式不能转换为下面的表达式：

t1 LEFT JOIN t2 ON t1.a=t2.a，t3.

对于给定的表状态，第1个表达式返回行{1,1,101,101}、{2,NULL,NULL,NULL}，第2个表达式返回行{1,1,101,101}、{2,NULL,NULL,101}：

mysql> SELECT *
    -> FROM t1 LEFT JOIN (t2, t3) ON t1.a=t2.a;
+------+------+------+------+
| a    | a    | b    | b    |
+------+------+------+------+
|    1 |    1 |  101 |  101 |
|    2 | NULL | NULL | NULL |
+------+------+------+------+
 
mysql> SELECT *
    -> FROM t1 LEFT JOIN t2 ON t1.a=t2.a, t3;
+------+------+------+------+
| a    | a    | b    | b    |
+------+------+------+------+
|    1 |    1 |  101 |  101 |
|    2 | NULL | NULL |  101 |
+------+------+------+------+

因此，如果我们忽略联接表达式中的括号连同外面的联接操作符，我们会改变原表达式的结果。

更确切地说，我们不能忽视左外联接操作的右操作数和右联接操作的左操作数中的括号。换句话说，我们不能忽视外联接操作中的内表达式中的括号。可以忽视其它操作数中的括号(外部表的操作数)。

对于任何表t1、t2、t3和属性t2.b和t3.b的任何条件P，下面的表达式：

(t1,t2) LEFT JOIN t3 ON P(t2.b,t3.b)

等价于表达式

t1，t2 LEFT JOIN t3 ON P(t2.b,t3.b)

如果联接表达式(join_table)中的联接操作的执行顺序不是从左到右，我们则应讨论嵌套的联接。这样，下面的查询：

SELECT * FROM t1 LEFT JOIN (t2 LEFT JOIN t3 ON t2.b=t3.b) ON t1.a=t2.a
  WHERE t1.a > 1
 
SELECT * FROM t1 LEFT JOIN (t2, t3) ON t1.a=t2.a
  WHERE (t2.b=t3.b OR t2.b IS NULL) AND t1.a > 1

联接表：

t2 LEFT JOIN t3 ON t2.b=t3.b
t2, t3

认为是嵌套的。第1个查询结合左联接操作则形成嵌套的联接，而在第二个查询中结合内联接操作形成嵌套联接。

在第1个查询中，括号可以忽略：联接表达式的语法结构与联接操作的执行顺序相同。但对于第2个查询，括号不能省略，尽管如果没有括号，这里的联接表达式解释不清楚。(在外部扩展语法中，需要第2个查询的(t2，t3)的括号，尽管从理论上对查询分析时不需要括号：这些查询的语法结构将仍然不清楚，因为LEFT JOIN和ON将充当表达式(t2,t3)的左、右界定符的角色)。

前面的例子说明了这些点：

·         对于只包含内联接(而非外联接)的联接表达式，可以删除括号。你可以移除括号并从左到右评估(或实际上，你可以按任何顺序评估表)。

·         总的来说，对外联接却不是这样。去除括号可能会更改结果。

·         总的来说，对外联接和内联接的结合，也不是这样。去除括号可能会更改结果。

含嵌套外联接的查询按含内联接的查询的相同的管道方式执行。更确切地说，利用了嵌套环联接算法。让我们回忆嵌套环联接执行查询时采用什么算法。

假定我们有一个如下形式的表T1、T2、T3的联接查询：

SELECT * FROM T1 INNER JOIN T2 ON P1(T1,T2)
                 INNER JOIN T3 ON P2(T2,T3)
  WHERE P(T1,T2,T3).

这里，P1(T1,T2)和P2(T3,T3)是一些联接条件(表达式)，其中P(t1,t2,t3)是表T1、T2、T3的列的一个条件。

嵌套环联接算法将按下面的方式执行该查询：

 

FOR each row t1 in T1 {
  FOR each row t2 in T2 such that P1(t1,t2) {
    FOR each row t3 in T3 such that P2(t2,t3) {
      IF P(t1,t2,t3) {
         t:=t1||t2||t3; OUTPUT t;
      }
    }
  }
}

符号t1||t2||t3表示“连接行t1、t2和t3的列组成的行”。在下面的一些例子中，出现行名的NULL表示NULL用于行的每个列。例如，t1||t2||NULL表示“连接行t1和t2的列以及t3的每个列的NULL组成的行”。

现在让我们考虑带嵌套的外联接的查询：

SELECT * FROM T1 LEFT JOIN
              (T2 LEFT JOIN T3 ON P2(T2,T3))
              ON P1(T1,T2)
  WHERE P(T1,T2,T3)。

对于该查询我们修改嵌套环模式可以得到：

FOR each row t1 in T1 {
  BOOL f1:=FALSE;
  FOR each row t2 in T2 such that P1(t1,t2) {
    BOOL f2:=FALSE;
    FOR each row t3 in T3 such that P2(t2,t3) {
      IF P(t1,t2,t3) {
        t:=t1||t2||t3; OUTPUT t;
      }
      f2=TRUE;
      f1=TRUE;
    }
    IF (!f2) {
      IF P(t1,t2,NULL) {
        t:=t1||t2||NULL; OUTPUT t;
      }
      f1=TRUE;
    }
  }
  IF (!f1) {
    IF P(t1,NULL,NULL) {
      t:=t1||NULL||NULL; OUTPUT t;
    }
  }
}
 

总的来说，对于外联接操作中的第一个内表的嵌套环，引入了一个标志，在环之前关闭并且在环之后打开。如果对于外部表的当前行，如果匹配表示内操作数的表，则标志打开。如果在循环结尾处标志仍然关闭，则对于外部表的当前行，没有发现匹配。在这种情况下，对于内表的列，应使用NULL值补充行。结果行被传递到输出进行最终检查或传递到下一个嵌套环，但只能在行满足所有嵌入式外联接的联接条件时。

在我们的例子中，嵌入了下面表达式表示的外联接表：

(T2 LEFT JOIN T3 ON P2(T2,T3))

请注意对于有内联接的查询，优化器可以选择不同的嵌套环顺序，例如：

FOR each row t3 in T3 {
  FOR each row t2 in T2 such that P2(t2,t3) {
    FOR each row t1 in T1 such that P1(t1,t2) {
      IF P(t1,t2,t3) {
         t:=t1||t2||t3; OUTPUT t;
      }
    }
  }
}

对于有外联接的查询，优化器可以只选择这样的顺序：外表的环优先于内表的环。这样，对于有外联接的查询，只可能有一种嵌套顺序。在下面的查询中，优化器将评估两个不同的嵌套：

SELECT * T1 LEFT JOIN (T2,T3) ON P1(T1,T2) AND P2(T1,T3)
  WHERE P(T1,T2,T3)

嵌套为：

FOR each row t1 in T1 {
  BOOL f1:=FALSE;
  FOR each row t2 in T2 such that P1(t1,t2) {
    FOR each row t3 in T3 such that P2(t1,t3) {
      IF P(t1,t2,t3) {
        t:=t1||t2||t3; OUTPUT t;
      }
      f1:=TRUE
    }
  }
  IF (!f1) {
    IF P(t1,NULL,NULL) {
      t:=t1||NULL||NULL; OUTPUT t;
    }
  }
}

和

FOR each row t1 in T1 {
  BOOL f1:=FALSE;
  FOR each row t3 in T3 such that P2(t1,t3) {
    FOR each row t2 in T2 such that P1(t1,t2) {
      IF P(t1,t2,t3) {
        t:=t1||t2||t3; OUTPUT t;
      }
      f1:=TRUE
    }
  }
  IF (!f1) {
    IF P(t1,NULL,NULL) {
      t:=t1||NULL||NULL; OUTPUT t;
    }
  }
}

在两个嵌套中，必须在外环中处理T1，因为它用于外联接中。T2和T3用于内联接中，因此联接必须在内环中处理。但是，因为该联接是一个内联接，T2和T3可以以任何顺序处理。

当讨论内联接嵌套环的算法时，我们忽略了部分详情，可能对查询执行的性能的影响会很大。我们没有提及所谓的“下推”条件。假定可以用连接公式表示我们的WHERE条件P(T1,T2,T3)：

P(T1,T2,T2) = C1(T1) AND C2(T2) AND C3(T3)。

在这种情况下，MySQL实际使用了下面的嵌套环方案来执行带内联接得到查询：

FOR each row t1 in T1 such that C1(t1) {
  FOR each row t2 in T2 such that P1(t1,t2) AND C2(t2)  {
    FOR each row t3 in T3 such that P2(t2,t3) AND C3(t3) {
      IF P(t1,t2,t3) {
         t:=t1||t2||t3; OUTPUT t;
      }
    }
  }
}

你会看见每个连接 C1(T1)，C2(T2)，C3(T3)被从最内部的环内推出到可以对它进行评估的最外的环中。如果C1(T1)是一个限制性很强的条件，下推条件可以大大降低从表T1传递到内环的行数。结果是查询大大加速。

对于有外联接的查询，只有查出外表的当前的行可以匹配内表后，才可以检查WHERE条件。这样，对内嵌套环下推的条件不能直接用于带外联接的查询。这里我们必须引入有条件下推前提，由遇到匹配后打开的标志保护。

对于带下面的外联接的例子

P(T1,T2,T3)=C1(T1) AND C(T2) AND C3(T3)

使用受保护的下推条件的嵌套环方案看起来应为：

FOR each row t1 in T1 such that C1(t1) {
  BOOL f1:=FALSE;
  FOR each row t2 in T2
      such that P1(t1,t2) AND (f1?C2(t2):TRUE) {
    BOOL f2:=FALSE;
    FOR each row t3 in T3
        such that P2(t2,t3) AND (f1&&f2?C3(t3):TRUE) {
      IF (f1&&f2?TRUE:(C2(t2) AND C3(t3))) {
        t:=t1||t2||t3; OUTPUT t;
      }
      f2=TRUE;
      f1=TRUE;
    }
    IF (!f2) {
      IF (f1?TRUE:C2(t2) && P(t1,t2,NULL)) {
        t:=t1||t2||NULL; OUTPUT t;
      }
      f1=TRUE;
    }
  }
  IF (!f1 && P(t1,NULL,NULL)) {
      t:=t1||NULL||NULL; OUTPUT t;
  }
}

总的来说，可以从联接条件（例如P1(T1,T2)和P(T2,T3)）提取下推前提。在这种情况下，下推前提也受一个标志保护，防止检查由相应外联接操作所产生的NULL-补充的行的断言。

请注意如果从判断式的WHERE条件推导出，根据从一个内表到相同嵌套联接的另一个表的关键字进行的访问被禁止。(在这种情况下，我们可以使用有条件关键字访问，但是该技术还未用于MySQL 5.1中）。

以上是小编为您精心准备的的内容，在的博客、问答、公众号、人物、课程等栏目也有的相关内容，欢迎继续使用右上角搜索按钮进行搜索mysql
， 优化
， 表达式
括号
mysql 性能优化、mysql优化、如何优化mysql数据库、拉勾网、mysql 慢查询如何优化，以便于您获取更多的相关知识。

时间： 2025-01-20 20:22:27

MySQL如何优化的相关文章

一些Mysql的优化经验

mysql|优化  一些 Mysql 的优化经验    1. 从数据库结构做起1. 字段类型的定义时遵循以下规则:1. 选用字段长度最小2. 优先使用定长型3. 尽可能的定义 "NOT NULL" 4. 数值型字段中避免使用 "ZEROFILL" 5. 如果要储存的数据为字符串, 且可能值已知且有限, 优先使用 enum 或 set 2. 索引的优化至关重要(以下如果没有特殊说明, 均指查询密集的情况)     1. 被索引的字段的长度越小, 该索引的效率越高2.

从编译到工具：几种mysql的优化方法

mysql|编译|优化 一.在编译时优化mysql 如果你从源代码分发安装mysql,要注意,编译过程对以后的目标程序性能有重要的影响,不同的编译方式可能得到类似的目标文件,但性能可能相差很大,因此,在编译安装mysql适应仔细根据你的应用类型选择最可能好的编译选项.这种定制的mysql可以为你的应用提供最佳性能. 技巧:选用较好的编译器和较好的编译器选项,这样应用可提高性能10-30%.(mysql文档如是说) 1.1.使用pgcc(pentium gcc)编译器 该编译器(http://ww

MySQL的优化（五）

二十一.MySQL表高速缓存工作原理 每个MyISAM表的打开实例(instance)使用一个索引文件和一个数据文件.如果表被两个线程使用或在 同一条查询中使用两次,MyIASM将共享索引文件而是打开数据文件的另一个实例. 如果所有在高速缓存中的表都在使用,缓存将临时增加到比表缓存尺寸大些.如果是这样,下一个被 释放的表将被关闭. 你可以通过检查mysqld的Opened_tables变量以检查表缓存是否太小.如果该值太高,你应该增大表高 速缓存. 二十二.MySQL扩展/优化-提供更快的速度

mysql数据库优化总结

1. 优化你的MySQL查询缓存在MySQL服务器上进行查询,可以启用高速查询缓存.让数据库引擎在后台悄悄的处理是提高性能的最有效方法之一.当同一个查询被执行多次时,如果结果是从缓存中提取,那是相当快的. 但主要的问题是,它是那么容易被隐藏起来以至于我们大多数程序员会忽略它.在有些处理任务中,我们实际上是可以阻止查询缓存工作的. 1. // query cache does NOT work 2. $r = mysql_query("SELECT username FROM user WHERE

MySQL中优化sql语句查询常用的30种方法

本篇文章是对MySQL中优化sql语句查询常用的30种方法进行了详细的分析介绍,需要的朋友参考下   1.对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引. 2.应尽量避免在 where 子句中使用!=或<>操作符,否则将引擎放弃使用索引而进行全表扫描. 3.应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描,如: select id from t where num is null 可以

在CentOS上使用yum安装MySQL+安全优化

0.说明         使用yum安装的好处是,你不用自己去解决软件之间的依赖问题,基本上yum执行完成,也就把软件安装好了,下面介绍使用yum的方法来安装MySQL,同时也会介绍安装完成后的安全优化.         注意:下面的操作都是以新安装的CentOS  6.5来作为演示的. 1.使用yum安装MySQL          查看是否已经安装MySQL: 1 [root@leaf]# yum list installed | grep mysql         如果你在安装CentO

MySQL如何优化LIMIT

MySQL如何优化LIMIT 在一些情况中,当你使用LIMIT row_count而不使用HAVING时,MySQL将以不同方式处理查询. ·         如果你用LIMIT只选择一些行,当MySQL选择做完整的表扫描时,它将在一些情况下使用索引. ·         如果你使用LIMIT row_count与ORDER BY,MySQL一旦找到了排序结果的第一个row_count行,将结束排序而不是排序整个表.如果使用索引,将很快.如果必须进行文件排序(filesort),必须选择所有匹配

解开发者之痛：中国移动MySQL数据库优化最佳实践

 章颖数据研发工程师 现任中国移动杭州研发中心数据研发工程师,擅长MySQL故障诊断,性能调优,MySQL高可用技术,曾任中国电信综合平台开发运营中心DBA   开源数据库MySQL比较容易碰到性能瓶颈,为此经常需要对MySQL数据库进行优化,而MySQL数据库优化需要运维DBA与相关开发共同参与,其中MySQL参数及服务器配置优化主要由运维DBA完成,开发则需要从数据类型优化,索引优化,SQL优化三个角度考虑MySQL数据库优化问题,本次分享将从开发角度,看如何实现MySQL数据库优化. 本次

RDS MySQL空间优化最佳实践

在前三期介绍了RDS for MySQL参数优化,锁问题以及延迟优化最佳实践之后,本期将介绍存储空间相关的最佳实践. 存储空间是RDS很重要的一个指标,在RDS的工单问题中,空间问题的咨询可以排在top 5,当RDS的实际使用空间超过了购买的空间后,实例就会被锁定了,这样就会导致应用无法再写入,更新数据,造成应用的报错.在RDS的控制台中可以设定空间的报警阀值,当实例空间到达报警阀值后用户就会收到报警短信,这个时候用户则需要对判断当前的空间增长是否合理.如果增长合理则需要对实例的进行弹性升级,这