SQL语句优化之JOIN和LEFT JOIN 和 RIGHT JOIN语句的优化

在数据库的应用中,我们经常需要对数据库进行多表查询,然而当数据量非常大时多表查询会对执行效率产生非常大的影响,因此我们在使用JOIN和LEFT JOIN 和 RIGHT JOIN语句时要特别注意;

SQL语句的join原理:

数据库中的join操作,实际上是对一个表和另一个表的关联,而很多错误理解为,先把这两个表来一个迪卡尔积,然后扔到内存,用where和having条件来慢慢筛选,其实数据库没那么笨的,那样会占用大量的内存,而且效率不高,比如,我们只需要的一个表的一些行和另一个表的一些行,如果全表都做迪卡尔积,这开销也太大了,真正的做法是,根据在每一个表上的条件,遍历一个表的同时,遍历其他表,找到满足最后的条件后,就发送到客户端,直到最后的数据全部查完,叫做嵌套循环查询。

1、LEFT JOIN 和 RIGHT JOIN优化

在MySQL中,实现如 A LEFT JOIN B join_condition 如下:

1、表B依赖赖与表A及所有A依赖的表

2、表A依赖于所有的表,除了LEFT JOIN 的表(B)

3、join_condition决定了怎样来读取表B,where条件对B是没有用的

4、标准的where会和LEFT JOIN联合优化

5、如果在A中的一行满足where和having条件,B中没有,会被填充null

RIGHT JOIN 与LEFT JOIN类似,这个位置是可以互换的

LEFT JOIN 与 正常JOIN之间的转换原则上当where条件,对于生成的null行总返回false时,可以直接转化为正常的join

如:

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;

注:因为设置了条件t2.column2 =  5,那么对于所有的生成的t2为null的行都是不成立的

这样的优化将非常快速,因为这样相当于把外连接转换为等值连接,少了很多行的扫描和判断。

嵌套循环JOIN算法----Nested-Loop Join

简单的嵌套循环算法就是从一个表开始,通过对表的条件找到一行,然后找下一个表的数据,找完后,又回到第一个表来寻找满足条件的行

例如,有三个表t1, t2, t3,他们的join类型为:

Table  Join Type
t1   range
t2   ref
t3   ALL

最终生成的伪代码为

for each row in t1 matching range {
 for each row in t2 matching reference key {
  for each row in t3 {
   if row satisfies join conditions,
     send to client
  }
 }
} 

即,t1表通过范围扫描,t2关联t1,t3为全表扫描

注:先根据对t1表的条件范围找到一行,和t2匹配,然后寻找t3的满足条件的行

块嵌套循环JOIN算法 ---- Block Nested-Loop Join

这个算法的应用为:由于之前的嵌套算法每读一个表的一行后,就会读下表,这样内部的表会被读很多次,所以,数据库利用了join缓存(join buffer)来存储中间的结果,然后读取内部表的时候,找到一行,都和这个缓存中的数据比较,以此来提高效率。例如:一次从外表读10行,然后读内部表时,都和这10行数据进行比较。

MySQL使用join buffer的条件为:

1、join_buffer_size系统变量决定了每个join使用的buffer大小

2、join类型为index或all时,join buffer才能被使用

3、每一个join都会分配一个join buffer,即一个sql可能使用多个join buffer

4、join buffer 不会分配给第一个非常量表

5、只有需要引用的列会被放到join buffer中,不是整行

最终生成伪代码为:

for each row in t1 matching range {
 for each row in t2 matching reference key {
  store used columns from t1, t2 in join buffer
    这里将t1和t2使用的列存到join buffer中
    if buffer is full {
   for each row in t3 {
    for each t1, t2 combination in join buffer {
     if row satisfies join conditions,
     send to client
    }
   }
   empty buffer
  }
 }
} 

if buffer is not empty {
 for each row in t3 {
  for each t1, t2 combination in join buffer {
   if row satisfies join conditions,
   send to client
  }
 }
} 

注:在第二个循环才把数据存在join buffer中,这正好印证了上面的第4点

您可能感兴趣的文章:

  • MySQL 性能优化的最佳20多条经验分享
  • MySQL配置文件my.cnf参数优化和中文详解
  • MySQL Order by 语句用法与优化详解
  • MySQL优化必须调整的10项配置
  • 浅谈MySQL中优化sql语句查询常用的30种方法
  • MySQL 百万级分页优化(Mysql千万级快速分页)
  • MySQL性能优化之max_connections配置参数浅析
  • MySQL查询优化之explain的深入解析
  • 优化Mysql数据库的8个方法
  • sql语句优化之用EXISTS替代IN、用NOT EXISTS替代NOT IN的语句
  • mysql下优化表和修复表命令使用说明(REPAIR TABLE和OPTIMIZE TABLE)
  • 浅析Mysql Join语法以及性能优化
  • MYSQL 优化常用方法
  • mysql优化连接数防止访问量过高的方法
  • MYSQL性能优化分享(分库分表)
  • SQLServer 优化SQL语句 in 和not in的替代方案
  • SQL SERVER性能优化综述(很好的总结,不要错过哦)
  • mysql优化配置参数
时间: 2018-03-11

MySQL 性能优化的最佳20多条经验分享

当我们去设计数据库表结构,对操作数据库时(尤其是查表时的SQL语句),我们都需要注意数据操作的性能.这里,我们不会讲过多的SQL语句的优化,而只是针对MySQL这一Web应用最多的数据库.希望下面的这些优化技巧对你有用. 1. 为查询缓存优化你的查询 大多数的MySQL服务器都开启了查询缓存.这是提高性最有效的方法之一,而且这是被MySQL的数据库引擎处理的.当有很多相同的查询被执行了多次的时候,这些查询结果会被放到一个缓存中,这样,后续的相同的查询就不用操作表而直接访问缓存结果了. 这里最主要

SQL SERVER性能优化综述(很好的总结,不要错过哦)第1/3页

一.分析阶段 一般来说,在系统分析阶段往往有太多需要关注的地方,系统各种功能性.可用性.可靠性.安全性需求往往吸引了我们大部分的注意力,但是,我们必须注意,性能是很重要的非功能性需求,必须根据系统的特点确定其实时性需求.响应时间的需求.硬件的配置等.最好能有各种需求的量化的指标. 另一方面,在分析阶段应该根据各种需求区分出系统的类型,大的方面,区分是OLTP(联机事务处理系统)和OLAP(联机分析处理系统). 二.设计阶段 设计阶段可以说是以后系统性能的关键阶段,在这个阶段,有一个关系到以后几乎

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

1.对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引. 2.应尽量避免在 where 子句中使用!=或<>操作符,否则将引擎放弃使用索引而进行全表扫描. 3.应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描,如: select id from t where num is null 可以在num上设置默认值0,确保表中num列没有null值,然后这样查询: select id from

MySQL优化必须调整的10项配置

当我们被人雇来监测MySQL性能时,人们希望我们能够检视一下MySQL配置然后给出一些提高建议.许多人在事后都非常惊讶,因为我们建议他们仅仅改动几个设置,即使是这里有好几百个配置项.这篇文章的目的在于给你一份非常重要的配置项清单. 我们曾在几年前在博客里给出了这样的建议,但是MySQL的世界变化实在太快了!写在开始前-即使是经验老道的人也会犯错,会引起很多麻烦.所以在盲目的运用这些推荐之前,请记住下面的内容: 一次只改变一个设置!这是测试改变是否有益的唯一方法. 大多数配置能在运行时使用SET

浅析Mysql Join语法以及性能优化

一.Join语法概述 join 用于多表中字段之间的联系,语法如下: 复制代码 代码如下: ... FROM table1 INNER|LEFT|RIGHT JOIN table2 ON conditiona table1:左表:table2:右表. JOIN 按照功能大致分为如下三类: INNER JOIN(内连接,或等值连接):取得两个表中存在连接匹配关系的记录. LEFT JOIN(左连接):取得左表(table1)完全记录,即是右表(table2)并无对应匹配记录. RIGHT JOIN

MYSQL性能优化分享(分库分表)

1.分库分表 很明显,一个主表(也就是很重要的表,例如用户表)无限制的增长势必严重影响性能,分库与分表是一个很不错的解决途径,也就是性能优化途径,现在的案例是我们有一个1000多万条记录的用户表members,查询起来非常之慢,同事的做法是将其散列到100个表中,分别从members0到members99,然后根据mid分发记录到这些表中,牛逼的代码大概是这样子: 复制代码 代码如下: <?php for($i=0;$i< 100; $i++ ){ //echo "CREATE TA

SQLServer 优化SQL语句 in 和not in的替代方案

但是用IN的SQL性能总是比较低的,从SQL执行的步骤来分析用IN的SQL与不用IN的SQL有以下区别: SQL试图将其转换成多个表的连接,如果转换不成功则先执行IN里面的子查询,再查询外层的表记录,如果转换成功则直接采用多个表的连接方式查询.由此可见用IN的SQL至少多了一个转换的过程.一般的SQL都可以转换成功,但对于含有分组统计等方面的SQL就不能转换了. 推荐在业务密集的SQL当中尽量不采用IN操作符 NOT IN 此操作是强列推荐不使用的,因为它不能应用表的索引.推荐用NOT EXIS

mysql优化连接数防止访问量过高的方法

很多开发人员都会遇见"MySQL: ERROR 1040: Too many connections"的异常情况,造成这种情况的一种原因是访问量过高,MySQL服务器抗不住,这个时候就要考虑增加从服务器分散读压力:另一种原因就是MySQL配置文件中max_connections值过小. 首先,我们来查看mysql的最大连接数: mysql> show variables like '%max_connections%'; +-----------------+-------+ |

mysql下优化表和修复表命令使用说明(REPAIR TABLE和OPTIMIZE TABLE)

查询mysql表是否被损坏命令,如下: # CHECK TABLE 表名 mysql的长期使用,肯定会出现一些问题,一般情况下mysql表无法访问,就可以修复表了,优化时减少磁盘占用空间.方便备份. 表修复和优化命令,如下: #REPAIR TABLE `table_name` 修复表 #OPTIMIZE TABLE `table_name` 优化表 REPAIR TABLE 用于修复被破坏的表. OPTIMIZE TABLE 用于回收闲置的数据库空间,当表上的数据行被删除时,所占据的磁盘空间并

sql语句优化之用EXISTS替代IN、用NOT EXISTS替代NOT IN的语句

在许多基于基础表的查询中,为了满足一个条件,往往需要对另一个表进行联接.在这种情况下, 使用EXISTS(或NOT EXISTS)通常将提高查询的效率.在子查询中,NOT IN子句将执行一个内部的排序和合并.无论在哪种情况下,NOT IN都是最低效的 (因为它对子查询中的表执行了一个全表遍历).为了避免使用NOT IN ,我们可以把它改写成外连接(Outer Joins)或NOT EXISTS. 如 我要查询 Sendorder表中的冗余数据(没有和reg_person或worksite相连的数

mysql优化配置参数

第一种 复制代码 代码如下: #This File was made using the WinMySQLAdmin 1.4 Tool #2004-2-23 16:28:14 #Uncomment or Add only the keys that you know how works. #Read the MySQL Manual for instructions [mysqld] basedir=D:/mysql #bind-address=210.5.*.* datadir=D:/mysq

MySQL 百万级分页优化(Mysql千万级快速分页)

以下分享一点我的经验 一般刚开始学SQL的时候,会这样写 复制代码 代码如下: SELECT * FROM table ORDER BY id LIMIT 1000, 10; 但在数据达到百万级的时候,这样写会慢死 复制代码 代码如下: SELECT * FROM table ORDER BY id LIMIT 1000000, 10; 也许耗费几十秒 网上很多优化的方法是这样的 复制代码 代码如下: SELECT * FROM table WHERE id >= (SELECT id FROM

MYSQL 优化常用方法

1.选取最适用的字段属性 MySQL可以很好的支持大数据量的存取,但是一般说来,数据库中的表越小,在它上面执行的查询也就会越快.因此,在创建表的时候,为了获得更好的性能,我们可以将表中字段的宽度设得尽可能小.例如,在定义邮政编码这个字段时,如果将其设置为CHAR(255),显然给数据库增加了不必要的空间,甚至使用VARCHAR这种类型也是多余的,因为CHAR(6)就可以很好的完成任务了.同样的,如果可以的话,我们应该使用MEDIUMINT而不是 BIGIN来定义整型字段. 另外一个提高效率的方法

MySQL配置文件my.cnf参数优化和中文详解

Mysql参数优化对于新手来讲,是比较难懂的东西,其实这个参数优化,是个很复杂的东西,对于不同的网站,及其在线量,访问量,帖子数量,网络情况,以及机器硬件配置都有关系,优化不可能一次性完成,需要不断的观察以及调试,才有可能得到最佳效果. 复制代码 代码如下: [client]port = 3306socket = /tmp/mysql.sock [mysqld]port = 3306socket = /tmp/mysql.sock basedir = /usr/local/mysqldatadi

MySQL性能优化之max_connections配置参数浅析

MySQL的max_connections参数用来设置最大连接(用户)数.每个连接MySQL的用户均算作一个连接,max_connections的默认值为100.本文将讲解此参数的详细作用与性能影响. 与max_connections有关的特性 MySQL无论如何都会保留一个用于管理员(SUPER)登陆的连接,用于管理员连接数据库进行维护操作,即使当前连接数已经达到了max_connections.因此MySQL的实际最大可连接数为max_connections+1: 这个参数实际起作用的最大值

MySQL查询优化之explain的深入解析

在分析查询性能时,考虑EXPLAIN关键字同样很管用.EXPLAIN关键字一般放在SELECT查询语句的前面,用于描述MySQL如何执行查询操作.以及MySQL成功返回结果集需要执行的行数.explain 可以帮助我们分析 select 语句,让我们知道查询效率低下的原因,从而改进我们查询,让查询优化器能够更好的工作. 一.MySQL 查询优化器是如何工作的MySQL 查询优化器有几个目标,但是其中最主要的目标是尽可能地使用索引,并且使用最严格的索引来消除尽可能多的数据行.最终目标是提交 SEL

优化Mysql数据库的8个方法

1.创建索引对于查询占主要的应用来说,索引显得尤为重要.很多时候性能问题很简单的就是因为我们忘了添加索引而造成的,或者说没有添加更为有效的索引导致.如果不加索引的话,那么查找任何哪怕只是一条特定的数据都会进行一次全表扫描,如果一张表的数据量很大而符合条件的结果又很少,那么不加索引会引起致命的性能下降.但是也不是什么情况都非得建索引不可,比如性别可能就只有两个值,建索引不仅没什么优势,还会影响到更新速度,这被称为过度索引.2.复合索引比如有一条语句是这样的:select * from users

MySQL Order by 语句用法与优化详解

MySQL Order By keyword是用来给记录中的数据进行分类的.MySQL Order By Keyword根据关键词分类ORDER BY keyword是用来给记录中的数据进行分类的. 复制代码 代码如下: SELECT column_name(s) FROM table_name ORDER BY column_name 例子 SQL创建代码: 复制代码 代码如下: CREATE TABLE IF NOT EXISTS mysql_order_by_test (  uid int

MySQL中or语句用法示例

1.mysql中or语法的使用,在mysql语法中or使用注意点. 项目遇到坑,遍历发放奖励数据查询错误!!! $sql = 'SELECT * FROM `vvt_spread_doubleegg_exchange_award` AS p WHERE p.`act_type` = 4 or p.`act_type` = 5 AND p.`user_id` = ' .$user_id ; sql中的or语法一般用于多个条件的查询,上面的语法查询的相当于:两个sql查询出来的数据集合. $sql

C++利用MySQL API连接和操作数据库实例详解

1.C++连接和操作MySQL的方式 系列文章: MySQL 设计和命令行模式下建立详解 C++利用MySQL API连接和操作数据库实例详解 在Windows平台,我们可以使用ADO.ODBC或者MySQL API进行连接和操作.ADO (ActiveX Data Objects,ActiveX数据对象)是Microsoft提出的一个用于存取数据源的COM组件.它提供了程序语言和统一数据访问方式OLE DB的一个中间层,也就是Microsoft提出的应用程序接口(API)用以实现访问关系或非关

MySQL数据备份之mysqldump的使用详解

mysqldump常用于MySQL数据库逻辑备份. 1.各种用法说明 A. 最简单的用法: mysqldump -uroot -pPassword [database name] > [dump file] 上述命令将指定数据库备份到某dump文件(转储文件)中,比如: mysqldump -uroot -p123 test > test.dump 生成的test.dump文件中包含建表语句(生成数据库结构哦)和插入数据的insert语句. B. --opt 如果加上--opt参数则生成的du

Java 中This用法的实例详解

 Java 中This用法的实例详解 用类名定义一个变量的时候,定义的只是一个引用,外面可以通过这个引用来访问这个类里面的属性和方法. 那们类里面是够也应该有一个引用来访问自己的属性和方法纳? 呵呵,Java提供了一个很好的东西,就是 this 对象,它可以在类里面来引用这个类的属性和方法.先来个简单的例子: public class ThisDemo { String name="Mick"; public void print(String name){ System.out.pr

Mysql 5.7.9 shutdown 语法实例详解

mysql-5.7.9 终于提供shutdown 语法啦: 之前如果想关闭一个mysql数据库可以通过kill 命令.mysqladmin shutdown .service mysqld stop 等这样的方式. 然而在mysql-5.7.9之后mysql终于提供了SQL接口的shutdown语法啦! SQL接口下的shutdown语法: 语法 shutdown ; -- 这个shutdown要执行成功要有shutdown权限才行. shutdown的一个例子: [root@workstudi

JavaScript中匿名函数的用法及优缺点详解

匿名函数可以有效的保证在页面上写入Javascript,而不会造成全局变量的污染. 这在给一个不是很熟悉的页面增加Javascript时非常有效,也很优美. 一.什么是匿名函数? 在Javascript定义一个函数一般有如下三种方式: 函数关键字(function)语句: function fnMethodName(x){alert(x);} 函数字面量(Function Literals): var fnMethodName = function(x){alert(x);} Function(

Java多线程用法的实例详解

Java多线程用法的实例详解 前言: 最全面的java多线程用法解析,如果你对Java的多线程机制并没有深入的研究,那么本文可以帮助你更透彻地理解Java多线程的原理以及使用方法. 1.创建线程 在Java中创建线程有两种方法:使用Thread类和使用Runnable接口.在使用Runnable接口时需要建立一个Thread实例.因此,无论是通过Thread类还是Runnable接口建立线程,都必须建立Thread类或它的子类的实例.Thread构造函数: public Thread( ); p

C#集合类用法实例代码详解

下面介绍C#的集合类 1ArrayList using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; using System.Collections; namespace 动态数组ArrayList { class Program { static void Main(string[] args) { ArrayList

JDBC连接mysql处理中文时乱码解决办法详解

JDBC连接mysql处理中文时乱码解决办法详解 近日,整合的项目需要跟一个比较老版本的mysql服务器连接,使用navicat查看,发现此mysql服务器貌似没有设置默认编码,而且从操作此mysql的部分php文件看,应该是使用的gb2312的编码,但是,直接使用jdbc操作,从库中读取出来的中文全都是乱码. 一开始,使用类似entity.setDepartName(new String(rs.getString("hg").getBytes("gbk"), &q