前段时间笔者开发某个项目遇到了MySQL性能问题,每张表的数据量都在五千万以上,个别表数据量甚至在一个亿以上,在开发的过程中遇到了非常多的数据库性能优化难点,笔者在开发过程中查询了很多资料,很多查询语句也在优化过程中取得了比较好的效果。笔者也将开发过程中遇到的sql优化问题总结为文章,以便日后回顾。这篇文章主要讲解mysql执行联结运算的原理。为了避免泄露公司业务及数据,在文章中涉及的sql语句都和公司业务无关。
我们知道对于Oracle的表连接,根据SQL连接条件主要支持如下三种连接方法(算法):
1. 笛卡尔积(交叉连接) 在MySQL中可以为CROSS JOIN或者省略CROSS即JOIN,或者使用',' 如:
在前几章节中,我们已经学会了如何在一张表中读取数据,这是相对简单的,但是在真正的应用中经常需要从多个数据表中读取数据。
两个表 t1 和 t2 , 一样的,包括索引信息 a 字段有索引 b字段没有索引。
首先对于熟悉Oracle 的DBA 来说,hash join并不陌生,尤其涉及到多个表join时 执行计划出现 hash join ,一般来说hash join的执行效率是比 Nest Loop 要好。运维MySQL 之后DBA也对MySQL 提出支持hash join的诉求。MySQL 在8.0.18 版本终于支持hash join了。那么什么是hash join呢?
Hash Join作为表连接的基础连接类型,各大关系型数据库(譬如Oracle、sqlserver、Postgres等)很早都支持了Hash Join这种连接类型。作为关系型数据库领域的领袖,Oracle数据库支持三种主流的连接类型:Nested Loop Join、Hash Join 和 Sort Merge Join。而作为最流行的关系型数据库的MySQL 却一直没有支持Hash Join,这点一直为人诟病。千呼万唤始出来,MySQL 8.0.18开始终于支持了Hash Join的连接算法。MySQL 8.0 的所有新特性中,Hash Join 曾经最让我期待的一个新特性。
关于MySQL 的 join,大家一定了解过很多它的“轶事趣闻”,比如两表 join 要小表驱动大表,阿里开发者规范禁止三张表以上的 join 操作,MySQL 的 join 功能弱爆了等等。这些规范或者言论亦真亦假,时对时错,需要大家自己对 join 有深入的了解后才能清楚地理解。
上周在公司做了针对MySQL 8.0新特性相关的分享,提到MySQL 8.0新特性,不得不提到的就是HashJoin,MySQL一直被人诟病没有实现HashJoin,从8.0.18已经带上了这个功能,令人欣喜。有时候在想,MySQL为什么一直不支持HashJoin呢?可能是因为MySQL多用于简单的OLTP场景,并且在互联网应用居多,需求没那么紧急。另一方面在8.0.18之前,MySQL只支持Nest Loop Join算法,MySQL针对这个算法做了若干优化,实现了Block NestLoop Join,Index NestLoop Join等,有了这些优化,在一定程度上能缓解对HashJoin的迫切程度。本文会介绍HashJoin的原理以及在使用和不使用HashJoin的情况下,性能的差异。
SQL中的Join用法 #1 环境 MySQL5.7 Mac #2 创建表及插入数据 新建两个表tableA及tableB mysql> create table tableA ( id int(10), name varchar(100) ); mysql> create table tableB ( id int(10), name varchar(100) ); 插入数据 tableA: mysql> insert into tableA values(1,'布鲁日'); mysql> insert
join 是 MySQL 用来进行联表操作的,用来匹配两个表的数据,筛选并合并出符合我们要求的结果集。
今天遇到一个left join优化的问题,搞了一下午,中间查了不少资料,对MySQL的查询计划还有查询优化有了更进一步的了解,做一个简单的记录: select c.* from hotel_info_original c left join hotel_info_collection h on c.hotel_type=h.hotel_type and c.hotel_id =h.hotel_id where h.hotel_id is null 这个sql是用来查询出c表中有h表中无的记录,所以想到了用left join的特性(返回左边全部记录,右表不满足匹配条件的记录对应行返回null)来满足需求,不料这个查询非常慢。先来看查询计划:
join 方式连接多表,本质就是各个表之间数据的循环匹配。MySQL 5.5 版本之前,MySQL 只支持一种表间关联方式,就是嵌套循环。如果关联表的数据量很大,则 join 关联的执行时间会非常漫长。在 MySQL 5.5 以后的版本中,MySQL 通过引入 BNLJ 算法来优化嵌套执行。
在前几章节中,我们已经学会了如果在一张表中读取数据,这是相对简单的,但是在真正的应用中经常需要从多个数据表中读取数据。
最近,发现业务线程卡死在读取数据库(MySQL 5.7),数据库CPU飙升到了100%,经过定位发现是下面这样一个SQL(由用户触发的查询,可以理解为查看这个群组的所有用户买过哪些商品)导致的:
... FROM table1 INNER|LEFT|RIGHT JOIN table2 ON conditiona
当需要查询两个表的交集、并集等数据时,除了嵌套子查询的方式外,还可以使用join的方式提升性能。对于MySQL的join语句,需要两个最基础的“角色”:主表即驱动表,关联表即驱动表。join描述的就是驱动表与被驱动表的关联关系。MySQL有三种关联逻辑处理策略,分别为:Index Nested-Loop Join、Simple Nested-Loop Join、Block Nested-Loop Join。在编写SQL时,需要配合explain使语句选择性能最优的策略。
在MySQL中,可以使用INNER JOIN语句来内关联两个表。如果要将小表A驱动大表B进行内关联,可以将小表A放在前面,大表B放在后面。具体的SQL语句可以按照以下格式编写:
在数据库处理中,Join操作是最基本且最重要的操作之一,它能将不同的表连接起来,实现对数据集的更深层次分析。
相信许多开发/DBA在使用MySQL的过程中,对于MySQL处理多表关联的方式或者说性能一直不太满意。对于开发提交的含有join的查询,一般比较抗拒,从而建议将join拆分,避免join可能带来的性能问题,同时也增加了程序和DB的网络交互。
在关系型数据库管理系统(RDBMS)中,连接查询是一项重要的数据库操作,它允许我们从多个表中检索和组合数据,以便进行更复杂的查询和分析。
日常工作中,遇到很多left join的SQL,今天对left join的这种语法进行简单讲解。刚开始接触MySQL的时候,我也认为使用left join的时候,是左表驱动右表的,但是随着对MySQL理解的深入,时间长了发现这个理解是错误的。
| 作者 肖泽凡,腾讯TEG研发管理部小小后台攻城狮一枚,负责腾讯敏捷产品研发平台TAPD的基础功能的开发和维护,热爱技术,喜欢分享,文章首次发表于SegmentFault,博客名“X先生”,欢迎与我交流~ ---- 在数据库中join操作被称为连接,作用是能连接多个表的数据(通过连接条件),从多个表中获取数据合并在一起作为结果集返回给客户端。例如: 表A: idnameage1A182B193C20 表B: iduidgender11F22M 通过连接可以获取到合并两个表的数据: select A
这一种在Mysql中是不支持的,可以在SQL Server上测试。这里就不测试了。
MYSQL 的hash join 是从8.018引入的, 众所周知MYSQL的JOIN 的方式一直是不大友好的,nested loop join 在针对数据表join方式中,速度是一个问题。优化的手段很多,驱动表的选择,先去除参与JOIN的数据的等等都是方法。
1)内连接:join, inner join 2)外连接:left join, left outer join, right join, right outer join, union; 3) 交叉连接:cross join
1.2 多表查询 1.2.1 内连接 规则:返回两个表的公共记录 语法: -- 语法一 select * from 表1 inner join 表2 on 表1.公共字段=表2.公共字段 -- 语法二 select * from 表1,表2 where 表1.公共字段=表2.公共字段 例题 -- inner join mysql> select * from stuinfo inner join stumarks on stuinfo.stuno=stumarks.stuno; +--------+--
在MySQL中,join语句想必大家都不陌生,今天我们围绕join语句展开,说一些可能平时不关注的知识点。
MySQL 8.0 相对于 MySQL 5.7,有很多新特性,比如:快速加列、原子 DDL、不可见索引、额外端口、角色管理等。这一节内容,就不讲这些新特性了,只来聊聊最近在工作学习过程中遇到的几处细节上的差异。
同时查询多张表获取到需要的数据 比如:我们想查询到开发部有多少人,需要将部门表和员工表同时进行查询
例 2:查询 tb_course 表中的 id 字段和 tb_students_info 表中的 course_id 字段相等的内容
其实对于上面的观点一定程度上是正确的,但不是完全正确。但之所以流传这么广,主要还是没有搞清楚实际状态,而根据实际使用中总结出来的一些模糊规律。只有了解的MySQL的Join实际执行方式,就会知道上面2种观点是一种模糊的规律,这种规律并不能指导我们实际开发。下面就说说MySQL的实际join执行方式。
At the parser stage, queries with right outer join operations are converted to equivalent queries containing only left join operations. In the general case, the conversion is performed such that this right join:
图片与最后一部分来自:https://blog.csdn.net/plg17/article/details/78758593
MYSQL 的多表联合查询中,只有nest loop 的查询方式,让MYSQL 一致是被“嘲笑”的地方。MYSQL 8.018 后mysql 将拥有HASH JOIN 功能,虽然对比其他数据库来说,这并不新鲜,但对于MYSQL 算是划时代的里程碑。
-- 语法一 select * from 表1 inner join 表2 on 表1.公共字段=表2.公共字段 -- 语法二 select * from 表1,表2 where 表1.公共字段=表2.公共字段
本文若未特意说明使用的数据表,均为 MySQL索引(四)常见的索引优化手段 中的示例表。
MySQL cross join是mysql中的一种连接方式,区别于内连接和外连接,对于cross join连接来说,其实使用的就是笛卡尔连接。在MySQL中,当CROSS JOIN不使用WHERE子句时,CROSS JOIN产生了一个结果集,该结果集是两个关联表的行的乘积。通常,如果每个表分别具有n和m行,则结果集将具有n*m行
正文之前 昨天下午写了篇 Mysql学习小计,结果出乎意料的受欢迎?变相刺激了我多写点 Mysql?好吧,如尔所愿。我晚上反正还不知道学点啥,就把今天看的那个菜鸟教程学完吧,到时候估计一点了,就可以睡了。 正文 ---- Mysql 排序 select field1, field2,...fieldN table_name1, table_name2 order by field1, [field2...] [ASC [DESC]] 你可以使用任何字段来作为排序的条件,从而返回排序后的查询结果。 你
相信这内连接,左连接什么的大家都比较熟悉了,当然还有左外连接什么的,基本用不上我就不贴出来了。这图只是让大家回忆一下,各种连接查询。 然后要告诉大家的是,需要根据查询的情况,想好使用哪种连接方式效率更高。
ps:不按照原文进行翻译,整理两篇博客,根据自己的实践进行验证,本博客可以作为mysql cross join的学习参考手册
用两个表(a_table、b_table),关联字段a_table.a_id和b_table.b_id来演示一下MySQL的内连接、外连接( 左(外)连接、右(外)连接、全(外)连接)。
语句:select * from a_table a inner join b_table bon a.a_id = b.b_id;
对于 MySQL 的 JOIN,不知道大家有没有去想过他的执行流程,亦或有没有怀疑过自己的理解(自信满满的自我认为!);如果大家不知道怎么检验,可以试着回答如下的问题
什么是 SQL语言? 答:SQL指结构化查询语言,全称是 Structured Query Language,是一种 ANSI(American National Standards Institute 美国国家标准化组织)标准的计算机语言。它是用于访问和处理数据库的标准的计算机语言。
领取专属 10元无门槛券
手把手带您无忧上云