首页
学习
活动
专区
圈层
工具
发布

Mysql连接查询时查询条件放在On之后和Where之后的区别

一开始还比较费解,后面回过神来才发现,犯了一个低级的错误,就是在使用left join时过滤条件放到on后面还是where后面是有区别的,如果没有搞清楚他们的区别,连表汇总的结果就会变少或者变多。... 原因 mysql 对于left join的采用类似嵌套循环的方式来进行从处理,以下面的语句为例: SELECT * FROM LT LEFT JOIN RT ON P1(LT,RT)) WHERE...因为对左表无右表匹配行的行而言,遍历右表后b=FALSE,所以会尝试用NULL补齐右表,但是此时我们的P2对右表行进行了限制,NULL若不满足P2(NULL一般都不会满足限制条件,除非IS NULL这种...),则不会加入最终的结果中,导致结果缺失。...on 后跟关联表(从表)的过滤条件,where 后跟主表或临时表的筛选条件(左连接为例,主表的数据都会查询到,所以临时表中必定包含主表所有的字段,需要给主表加什么筛选条件,直接给临时表加效果相同) 总结

3.5K20
  • 您找到你想要的搜索结果了吗?
    是的
    没有找到

    Mysql使用left join连表查询时,因连接条件未加索引导致查询很慢

    背景 最近一个后台功能列表,业务人员反馈查询和导出速度非常慢。 通过定位发现列表查询和数据导出都是使用的同样的一个连表查询SQL。...排查 通过Explain发现,连表查询中的table c没有使用到索引且是全表扫描。另外在Extra中特别说明了Using join buffer (Block Nested Loop)。...知识延伸 MySQL使用嵌套循环算法或其变种来进行表之间的连接。 在5.5版本之前,MySQL只支持一种表间关联方式,也就是嵌套循环(Nested Loop)。...假定要使用以下连接类型执行三个表t1,t2和t3之间的连接: Table Join Type t1 range t2 ref t3 ALL 如果使用一个简单的NLJ算法...由于索引的效率要比逐条循环效率高,所以当使用索引联表时,能大大加快查询速度,但是索引也不是万能的,如果你需要取索引以外的字段,那么依旧需要回到表中查出相应的数据。

    3.8K10

    使用JDBC连接MySQL数据库--典型案例分析(八)----实现员工数据的分页查询

    转载请注明:http://blog.csdn.net/uniquewonderq 问题: 使用JDBC连接Mysql数据库,实现对Emp表数据的分页查询功能。...方案: 对于较大的数据量,通常采用分页查询的方式。不同的数据库产品有不同的数据库级的分页查询策略。例如:Oracle通常使用rownum的方式;而Mysql使用limit的方式。...实现上述功能的MySQL数据库的SQL语句如下: select * from emp order by empno limit 5,5; MYSQL中使用limit关键字实现分页查询。...步骤:添加方法findByPageMySQL方法,实现连接Mysql数据库,实现对Emp表中数据的分页查询,代码如下所示: package dao; import java.sql.Connection...由表可看出:第三条是7499 运行结果: ? 总记录数为11没错,和预期一样。然后输出结果也一致。 本节结束。。。。

    1.6K10

    mysql explain 详解

    使用全文索引时出现 ref_or_null           类似于ref,但是会额外搜索包含null的值(由于mysql null不会存储进索引,而是通过增加null的标识来实现查询)在字段允许null...当估算行数超出总数的30%(默认情况)时,mysql将拒绝使用索引,转为全表查询 (select * from table where id0)  filtered  之前在分析连接查询的成本时提出过一个...MySQL 很有可能寻求通过建立内部的临时表来执行查询。...如果查询中使用到了内部的临时表,在执行计划 的 Extra 列将会显示 Using temporary 提示 Start temporary, End temporary           查询优化器会优先尝试将... IN 子查询转换成 semi-join ,而 semi-join 又 有好多种执行策略,当执行策略为 DuplicateWeedout 时,也就是通过建立临时表来实现为外层查中的记录进行去重操作时,驱动表查询执行计划的

    1.4K20

    仅仅会用MySQL的EXPLAIN还不够,还需要会用EXPLAIN ANALYZE

    查询访问的分区 :列出访问了哪些表分区(如果表已分区)。 所使用的连接类型(如果有):请注意,即使查询中不包含连接,这一列也会填充。 MySQL 可以选择的索引 :列出所有可能的候选索引。...MySQL 实际使用的索引 :显示查询选择的索引,并指定索引的使用长度。 MySQL 选择的索引的长度:当 MySQL 使用复合索引时,length 列是唯一能确定复合索引中的使用了多少列的方法。...将 rows 列的值乘以该百分比,可以看到 MySQL 估计要与查询计划中先前的表连接的行数。 与查询相关的任何额外信息 总之,通过使用 EXPLAIN,可以获得查询预期运行的步骤列表。...它不会展示 MySQL 在查询执行期间的优化过程。 一些统计数据是估算值,可能非常不准确。 它不会区分某些具有相同名称的内容。...temporary table>:表明这里会创建内部表以保存临时结果(例如在连接之前的子查询中)。

    36510

    MySQL联表查询的索引使用

    项目中一般使用的都是单表查询,但是在一些业务场景下,偶尔会选择联表查询,一直对联表查询时如何使用索引一直感到很好奇。...正好近期项目中遇到一个问题,联表查询时,没有建立索引,耗时居然达到了可耻的10分钟,所以趁机了解了一下。...联表查询的算法Nested-Loop Join,MySQL查询的结果集是3张表的笛卡尔积,所以效率特别低。...[(none)]> kill 3468722 结论 关联字段一定要添加索引 where条件的索引建立,一定要查看explain,mysql的工作方式经常跟我们想的不一样 增加慢查询日志(dba呢?)...参考 关于 MySQL LEFT JOIN 你可能需要了解的三点 MySQL JOIN原理 MySQL查询优化——连接以及连接原理 MySQL 性能优化神器 Explain 使用分析 What is the

    13.3K21

    查看Mysql执行计划

    ,数据库软件是怎样扫描表、怎样使用索引的,因此,我们能感知到的就只有 sql语句运行的时间,在数据规模不大时,查询是瞬间的,因此,在写sql语句的时候就很少考虑到性能的问题。...问题: 当我们在查询前能否预先估计查询究竟要涉及多少行、使用哪些索引、运行时间呢?答案是能的,mysql提供了相应的功能和语法来实现该功能。...eq_ref:最多只会有一条匹配结果,一般是通过主键或者唯一键索引来访问;在连接中,MYSQL在查询时,从前面的表中,对每一个记录的联合都从表中读取一个记录,它在查询使用了索引为主键或惟一键的全部时使用...Using temporary :当MySQL 在某些操作中必须使用临时表的时候,在Extra 信息中就会出现Using temporary 。...当然,在大规模数据量时,索引的建立和维护的代价也是很高的,往往需要较长的时间和较大的空间,如果在不同的列组合上建立索引,空间的开销会更大。

    4.3K10

    生产环境mysql用户以及权限规划

    原则:为生产库建立不同的用户管理用户业务用户 管理用户:超级管理用户、dba运维用户、备份用户、监控用户、复制用户 (克隆用户) 业务用户:每个业务模块一个用户,命名可以是user_业务模块名 只读查询用户...:允许开发人员定位问题查询生产库的只读权限 权限分配原则:根据最小原则给每个用户满足需求的最小权限,要是不够再增加 mysql用户和权限牵涉到的表 user : 包含用户账户,全局权限 db: 数据库级别的权限表...连接的主机名不要使用% 使用具体的ip或者域名来限定连接 管理权限:授权方式使用*.* create user event process proxy reload replication clinet...会把用户权限表加载到内存中 账号的匹配 先按照主机名字段排序,然后按用户名排序,用户账号根据排序后信息进行匹配 权限变更后的生效 1.对于表级别和字段级别的权限,修改并加载权限表之后,对于已经建立的客户端连接...,会在对表,字段的下一个请求中生效,对于新建立的连接立即生效 2.对于库级别的权限,修改并加载权限表之后,对于已经建立的客户端连接,会在下一次使用use db_name语句时生效,,对于新建立的连接立即生效

    2.2K51

    mysql之引擎、Explain、权限详解

    该引擎还提供了行级锁和外键约束,它的设计目标是处理大容量数据库系统,它本身其实就是基于MySQL后台的完整数据库系统,MySQL运行时Innodb会在内存中建立缓冲池,用于缓冲数据和索引。...由于锁的粒度更小,写操作不会锁定全表,所以在并发较高时,使用Innodb引擎会提升效率。...所以我们深入了解MySQL的基于开销的优化器,还可以获得很多可能被优化器考虑到的访问策略的细节,以及当运行SQL语句时哪种策略预计会被优化器采用。...,表示mysql服务器将在存储引擎检索行后再进行过滤 Using temporary:表示MySQL需要使用临时表来存储结果集,常见于排序和分组查询 Using filesort:MySQL中无法利用索引完成的排序操作称为...“文件排序” Using join buffer:改值强调了在获取连接条件时没有使用索引,并且需要连接缓冲区来存储中间结果。

    1.6K40

    MySQL 数据库规范--开发篇

    9.避免使用存储过程、触发器、函数等,这些特性会将业务逻辑与数据库耦合在一起,并且MySQL的存储过程,触发器,函数中可能存在bug。 10.尽量避免使用子查询,连接。...尽量将子查询转化为连接查询,mysql 查询优化器会优化连接查询,但连接的表要尽可能的少,如果很多,可以考虑反范式设计。即对设计阶段做一些改造。...const、system:当MySQL对查询的某部分进行优化,并转化为一个常量时,可使用这些类型进行访问。...如果我们在group by 时利用索引分组(其实包含排序的过程)排序,则可以提高性能,因为不会此时查询输出里没有了Using temporary,Using filesort。...再运行3 和 4中的查询语句 结果如下图所示: ?

    2.2K20

    【我在拉勾训练营学技术】Mysql 架构原理

    一、网络连接层 客户端连接器(Client Connectors):提供与MySQL服务器建立的支持。...目前几乎支持所有主流的服务端编程技术,例如常见的 Java、C、Python、.NET等,它们通过各自API技术与MySQL建立连接。...二、服务层(MySQL Server) 服务层是MySQL Server的核心,主要包含系统管理和控制工具、连接池、SQL接口、解析器、查询优化器和缓存六个部分。...MySQL 运行机制 ①建立连接(Connectors&Connection Pool),通过客户端/服务器通信协议与MySQL建立连接。MySQL 客户端与服务端的通信方式是 “ 半双工 ”。...④查询优化器(Optimizer)根据“解析树”生成最优的执行计划。MySQL使用很多优化策略生成最优的执行计划,可以分为两类:静态优化(编译时优化)、动态优化(运行时优化)。 等价变换策略。

    96721

    MySQL 存储过程中使用游标中使用临时表可以替代数组效果

    MySQL不支持数组。...但有时候需要组合几张表的数据,在存储过程中,经过比较复杂的运算获取结果直接输出给调用方,比如符合条件的几张表的某些字段的组合计算,MySQL临时表可以解决这个问题.临时表:只有在当前连接情况下, TEMPORARY...当连接关闭时, TEMPORARY 表被自动取消。必须拥有 create temporary table 权限,才能创建临时表。可以通过指定 engine = memory; 来指定创建内存临时表。...先建立要用的数据表及数据: drop table if exists  person; create table `person` (   `id` int(11)primary key NOT NULL...在连接非临时表查询可以利用指定主键或索引来提升性能。

    2.2K20

    详解Mysql执行计划explain

    eq_ref:最多只会有一条匹配结果,一般是通过主键或者唯一键索引来访问;在连接中,MYSQL在查询时,从前面的表中,对每一个记录的联合都从表中读取一个记录,它在查询使用了索引为主键或惟一键的全部时使用...ref:Join 语句中被驱动表索引引用查询,这个连接类型只有在查询使用了不是惟一或主键的键或者是这些类型的部分(比如,利用最左边前缀)时发生。对于之前的表的每一个行联合,全部记录都将从表中读出。...Using temporary :当MySQL 在某些操作中必须使用临时表的时候,在Extra 信息中就会 出现Using temporary 。...总结 • EXPLAIN不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况 • EXPLAIN不考虑各种Cache • EXPLAIN不能显示MySQL在执行查询时所作的优化工作...当然,在大规模数据量时,索引的建立和维护的代价也是很高的,往往需要较长的时间和较大的空间,如果在不同的列组合上建立索引,空间的开销会更大。

    1.6K20

    mysql临时表的用法

    当处理较复杂大的逻辑时,你可能偶尔需要运行很多查询获得一个大量数据的小的子集,不是对整个表运行这些查询,而是让MySQL每次找出所需的少数记录,将记录存到一个临时表可能更快些,然后多这些表运行查询。...这就是mysql临时表的作用了 一:创建临时表 CREATE TEMPORARY TABLE tmp_table (      name VARCHAR(10) NOT NULL,      value... INTEGER NOT NULL      ) 创建临时表和正常表只是多了个TEMPORARY关键字的区别 该表创建后将会在断开连接之后自动删除,也可以在连接时自己手动删除 DROP TABLE tmp_table...如果你声明Mysql临时表是一个HEAP表,MySQL也允许你指定在内存中创建它 CREATE TEMPORARY TABLE tmp_table (      name VARCHAR(10) NOT... NULL,      value INTEGER NOT NULL      ) TYPE = HEAP 因为HEAP表存储在内存中,你对它运行的查询可能比磁盘上的临时表快些。

    4.3K20

    MySQL权限管理 原

    .* to 'xiaogang'@'%'; 这个时候 xiaogang 就拥有了 所有权限了 权限列表 权限 说明 举例 usage 连接(登陆)权限,建立一个用户,就会自动授予其usage权限(默认授予...权限给它的创建者: create temporary tables (注意这里是tables,不是table) 必须有create  temporary tables的权限,才可以使用create temporary...mysql>  show databases; 对于root@localhost用户来说,没有对mysql数据库的权限,所以以此身份登陆查询时,无法看到mysql数据库: show view 必须拥有show...;//mysql 5之后可以使用,之前需要使用update 更新user表 四、删除 mysql>drop user buzaifengyaosha;   //mysql5之前删除用户时必须先使用revoke...匹配所有主机 localhost    localhost不会被解析成IP地址,直接通过UNIXsocket连接 127.0.0.1      会通过TCP/IP协议连接,并且只能在本机访问; ::1

    2.8K20

    Extra(5)—mysql执行计划(五十一)

    前面说了有type,simple表示普通查询或者连接查询,primary代表union最左边的select,union result代表union查询的临时表去重,所以union all没有去重功能,subquery...代表in的子查询物化表的情况下才会出现,dependent subquery代表相关子查询,dependent union代表相关union查询,还有driverd子查询,from后面的,也需要物化,还有物化后转连接查询...//其中key1>'z'可以使用到索引,但后面的like不可以使用索引, //传统访问应该先索引在索引b+树查询满足z的条件,然后数据回表查询过滤满足like '%a'的数据。...会使用临时表进行去重或者排序,比如我们在distinct,group by,union等子句的查询过程, //这时候如果不能利用索引查询,这时候会建立内部临时表,这时候就会显示 Using temporary...in转为semi-join的时候,有很多执行策略,其实一种也是建立临时表实现为外层查询进去重操作 //这时候驱动表start temporary 被驱动表将会显示end temporary mysql

    94011

    虽然都是并行查询,差别可大了去了

    让流程更加透明 TDSQL-C MySQL版提供多种监控手段对并行查询过程进行监控,保证每个异常状态均可实时掌握,保护集群稳定运行。...为此TDSQL-C MySQL版新增多项监控变量与定制化SQL命令,方便用户在使用并行查询能力时可以精准查看执行流程与工作线程,遇到特殊情况时可以快速处理。...explain语句详细展示了并行查询的执行流程 方法二:线程列表查看 show processlist 命令的输出结果显示了有哪些线程在运行,不仅可以查看当前所有的连接数,还可以查看当前的连接状态帮助识别出有问题的查询语句等...让执行更加灵活 为了适应多种业务情况,以及保证并行查询能力不会对硬件产生过多的负载,TDSQL-C MySQL 版也提供了多种策略对并行查询的执行条件进行设置,方便您对业务进行个性化适配,保证业务稳定运行...设置后,TDSQL-C MySQL 版将会对语句的执行代价、表的行数、单条语句执行并行计划时所使用的内存等条件进行判断,确认每一条 SQL 语句是否允许执行并行查询。

    1.8K20

    MySQL 5.7 内部临时表

    本文研究了在没有写查询的情况下,InnoDB行插入时,因内部临时表的问题而发生性能尖刺的情形。...事情发生在我研究一个客户的案例时,在”InnoDB行插入“指标图上,发现了从1k行每秒激增到6K行的尖刺,但却无法和其他活动或者现象连接起来,PMM监控图形上也有同样的反映。...在最后才发现,行插入飙升一定和DML有关的这种想法是错误的,出乎意料的是,尖刺是由于SELECT查询导致的,但为何SELECT查询会导致大量的InnoDB行插入操作呢?...在MySQL 5.7版本中,内部磁盘临时表的默认引擎是InnoDB引擎,这就意味着当SELECT操作需要在磁盘上创建临时表时(例如GROUP BY操作),就会使用到InnoDB引擎。.../tmp/ibtmp1:12M:autoextend (2)临时表空间和其他的表空间一样都不会自动缩小其占用容量,可能会发生临时表空间容量占满磁盘,MySQL挂掉的情况,可以通过控制其最大的容量来解决:

    7.6K10
    领券