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查询优化之explain的深入解析

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

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

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

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

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配置文件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: 这个参数实际起作用的最大值

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

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

MYSQL 优化常用方法

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

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 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 性能优化的最佳20多条经验分享

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

优化Mysql数据库的8个方法

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

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优化必须调整的10项配置

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

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

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

浅谈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 Join语法以及性能优化

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

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

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

mysql中优化和修复数据库工具mysqlcheck详细介绍

一.mysqlcheck简介 mysqlcheck客户端可以检查和修复MyISAM表.它还可以优化和分析表. mysqlcheck的功能类似myisamchk,但其工作不同.主要差别是当mysqld服务器在运行时必须使用mysqlcheck,而myisamchk应用于服务器没有运行时.使用mysqlcheck的好处是不需要停止服务器来检查或修复表.使用myisamchk修复失败是不可逆的. Mysqlcheck为用户提供了一种方便的使用SQL语句CHECK TABLE.REPAIR TABLE.

phpmyadmin显示MySQL数据表“使用中” 修复后依然无效的解决方法

PHP+MySQL后台,部分程序页面不显示内容,错误提示出错,需要修复. 打开phpmyadmin选择数据库查看,果然有3个表显示为"使用中".勾选之后进行"修复表",出现错误提示不能被修复. 于是接着从"分析表"."优化表"开始,均告失败.失败信息第一条:can't create new temp file google解决方案:强制修复 myisamchk -r -f ****.MYI,问题依旧. 第一步:停掉MYSQL服

MySQL数据库INNODB表损坏修复处理过程分享

突然收到MySQL报警,从库的数据库挂了,一直在不停的重启,打开错误日志,发现有张表坏了.innodb表损坏不能通过repair table 等修复myisam的命令操作.现在记录下解决过程,下次遇到就不会这么手忙脚乱了. 处理过程: 一遇到报警之后,直接打开错误日志,里面的信息: InnoDB: Database page corruption on disk or a failed InnoDB: file read of page 30506. InnoDB: You may have t

Mysql数据库性能优化三(分表、增量备份、还原)

接上篇Mysql数据库性能优化二 对表进行水平划分     如果一个表的记录数太多了,比如上千万条,而且需要经常检索,那么我们就有必要化整为零了.如果我拆成100个表,那么每个表只有10万条记录.当然这需要数据在逻辑上可以划分.一个好的划分依据,有利于程序的简单实现,也可以充分利用水平分表的优势.比如系统界面上只提供按月查询的功能,那么把表按月拆分成12个,每个查询只查询一个表就够了.如果非要按照地域来分,即使把表拆的再小,查询还是要联合所有表来查,还不如不拆了.所以一个好的拆分依据是 最重要的

MySQL查看表和清空表的常用命令总结

查看MySQL数据库表 进入MySQL Command line client下 查看当前使用的数据库: mysql>select database(); mysql>status; mysql>show tables; mysql>show databases;//可以查看有哪些数据库,返回数据库名(databaseName) mysql>use databaseName; //更换当前使用的数据库 mysql>show tables; //返回当前数据库下的所有表的

实现MySQL定时批量检查表repair和优化表optimize table的shell脚本

本文介绍mysql定时批量检查表repair和优化表optimize table的shell脚本,对于MySQL数据库的定期维护相当有用!如下所示: #!/bin/bash host_name=192.168.0.123 user_name=jincon.com user_pwd=jincon.com database=my_db_name need_optmize_table=true tables=$(mysql -h$host_name -u$user_name -p$user_pwd $

mysql下mysqladmin日常管理命令总结(必看篇)

mysqladmin 工具的使用格式: mysqladmin [option] command [command option] command ...... 参数选项: -c number 自动运行次数统计,必须和 -i 一起使用 -i number 间隔多长时间重复执行 0)每个两秒查看一次服务器的状态,总共重复5次. [root@test-huanqiu ~]# mysqladmin -uroot -p -i 2 -c 5 status 1)查看服务器的状况:status [root@te

mysql 导入导出数据库、数据表的方法

Linux下均在控制台下操作.导入数据库:前提:数据库和数据表要存在(已经被创建) (1)将数据表 test_user.sql 导入到test 数据库的test_user 表中[root@test ~]# mysql -uroot -p test < /www/web/test/test_user.sql (2) 将数据库 test.sql 导入到 test 数据库test 中[root@test ~]# mysql -uroot -p test < /www/web/test/test.sq