关于Mysql使用left join写查询语句执行很慢的问题解决

最新推荐文章于 2024-04-25 23:11:46 发布
最新推荐文章于 2024-04-25 23:11:46 发布
阅读量7.8k 收藏 32
点赞数 8
分类专栏： 日常其他数据运维 文章标签： mysql 数据库 sql SQL性能优化 索引
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/zyypjc/article/details/128029514
版权

目录

（一）前言

（二）正文

1. 表结构/索引展示

（1）表结构

（2）各表索引情况

2. 存在性能问题的SQL语句

3. 解决思路

（1）执行计划思路调优

（2）字符集匹配调优

（三）总结

1. 关于执行计划中TYPE的性能比较

2. 关于left join优化

3. 其他注意点

（一）前言

这几天供应商在测试环境上使用MYSQL数据库做开发时遇到一个SQL性能问题，即在他开发环境本地跑SQL速度很快就一两秒时间，但是同样的SQL放在测试环境上死活跑了很久一直出不了结果。最后求助到我这边，以下正文是我解决这次问题的一个过程浅谈，供大家参考。

（二）正文

本文使用NAVICAT试用版作为基础工具来说明，需要永久激活的可以在网上找到相关介绍走正式途径。

其次附上一篇文章，解释说明如果在NAVICAT中运行一条长时间的SQL想关闭终止它，图形化点击失败时候所应该采取的方式，文章链接如下：

在Navicat上如何停止正在运行的MYSQL语句_zyypjc的博客-CSDN博客

1. 表结构/索引展示

以下将大致描述下本次遇到性能问题涉及的两张表（rep_consultant_first和rep_newcomer_consultant）的表结构和索引。

（1）表结构

a. rep_consultant_first

b. rep_newcomer_consultant

 

（2）各表索引情况

 a. rep_consultant_first

b. rep_newcomer_consultant

2. 存在性能问题的SQL语句

这条SQL语句的意图在于找出rep_newcomer_consultant表中缺失的存在于rep_consultant_first表中的数据，即可求这两张表的差集(rep_consultant_first - rep_newcomer_consultant)，简单说就是下图里的红色填充区域。

select consultantNumber,
       customerId,userName,
			 telephoneNumber,
			 sponsorConsultantNumber,
			 signUpDate 
from rep_consultant_first rcf left join rep_newcomer_consultant rnco
 on rnco.rncoConsultantNumber=rcf.consultantNumber
where rnco.rncoConsultantNumber is null;

 直接运行后我们发现这条语句运行了好几十分钟依旧没有结果，假如只是稍微慢一点那可能勉强说得过去，但是目前这种情况实在是到无法接受的情况了！！！

3. 解决思路

（1）执行计划思路调优

一般SQL慢了，第一个想的一定是查一下执行计划是不是哪个环节没有走索引，走了全表扫描，让我们选中SQL部分，点击“解释已选择的”来看下这条SQL的执行计划详情：

 从执行计划中我们看到别名为rcf(即rep_consultant_first表)的type方式走了ALL，即全表扫描，那自然而然我们会先想从这里去优化。

回到rep_consultant_first表的索引位置，我们看到在select后筛出来的字段里只有consultant number和sponsorConsultantNumber字段上有索引而其他并没有，所以不可避免走了全表扫描

那我们先尝试下给未加索引的字段加上一组索引，大致流程如下：

a. 找到所要加索引字段所在的表（rep_consultant_first），右键点击后选择"设计表"

b. 找到索引选项卡，添加索引TestIndex，最后点击保存。

 

 

让我们重新回到一开始的SQL，看一下加完索引后是否执行计划有优化：

可以看出，执行计划的TYPE从ALL变成了INDEX且EXTRA列明确说明了Using index了，那说明执行计划确实改变了，没有扫全表，不过遗憾的是。。。SQL依旧跑不出来。。。

（2）字符集匹配调优

此时真的黔驴技穷了么？？还真没有 ！足球篮球世界里我们经常看到最后时刻逆袭的致命一击取得胜利，在SQL优化里我们同样有这样的机会！ 仔细回想了下，似乎在MYSQL相关手册资料中的优化TIPS里除了添加相关字段索引之外，那left join中关联两表的字段，字符集是否需要统一？？？

有了这个思路，我们立马着手再看下这条问题SQL语句，我们重点关注rep_consultant_first表上的consultantNumber字段以及rep_newcomer_consultant表上的rncoConsultantNumber字段：

对比之下，立马看出了区别！在rep_consultant_first上字段consultantNumber的字符集为utf8mb4，而rep_newcomer_consultant上字段rncoConsultantNumber的字符集为gbk。在官方相关文档中提到过关联字段除了需要有索引外，拥有相同的字符集以及数据类型相当重用，这会极大影响查询速度！

接下来我们来具体操作下，可以将rep_newcomer_consultant上字段rncoConsultantNumber的字符集改从gbk改为utf8mb4，排序方式也改为和rep_consultant_first表一样的utf8mb4_0900_ai_ci试一试，点击保存按钮：

保存成功后，我们立马再运行下慢SQL：

一下子只有0.885秒了！！速度飙升到无法言喻的速度！至此我们基本算优化成功了。

（三）总结

经过这个案例后，我搜罗总结了下本例涉及到一些优化注意点：

1. 关于执行计划中TYPE的性能比较

 

2. 关于left join优化

1、left join选择小表作为驱动表（这部分基本是大家的共识）

2、如果左表比较大，并且业务要求驱动表必须是左表，那么我们可以通过where条件语句，使得左表被过滤的小一些，主要原理和第一条类似

3、关联字段给索引，因为在mysql的嵌套循环算法中，是通过关联字段进行关联，并查询的，所以给关联字段索引很必要

4、如果sql里面有排序，请给排序字段加上索引，不然会造成排序使用全表扫描
        参考：https://www.oschina.net/question/930697_2190172      
5、如果where条件中含有右表的非空条件（除开is null），则left join语句等同于join语句，可直接改写成join语句。 

6、根据文档，MySQL能更高效地在声明具有相同类型和尺寸的列上使用索引。所以把表与表之间的关联字段给上encoding和collation（决定字符比较的规则）全部改成统一的类型

7、右表的条件列一定要加上索引（主键、唯一索引、前缀索引等），最好能够使type达到range及以上（ref,eq_ref,const,system） 

3. 其他注意点

zyypjc
关注
  • 8
    点赞
  • 踩
  • 32
    收藏
    觉得还不错? 一键收藏
  • 2
    评论
专栏目录
mysql not in、left join、IS NULL、NOT EXISTS 效率问题记录
12-15
NOT IN、JOIN、IS NULL、NOT EXISTS效率对比 语句一：select count(*) from A where A.a not in (select a from B) 语句二：select count(*) from A left join B on A.a = B.a where B.a is null 语句三：select count(*) from A where not exists (select a from B where A.a = B.a) 知道以上三条语句的实际效果是相同的已经很久了，但是一直没有深究其间的效率对比。一直感觉上语句二是最快的。 今天
leftjoin多了性能下降_MySQL 性能优化总结
weixin_39719585的博客
11-23 5804
作者 |如果耐，请真耐出处 ：https://www.cnblogs.com/joeyJss/p/11096597.html1，Sql优化概要:　　Sql优化就是指语句在执行的时候效率不是那么乐观所以提高Sql优化就是指在数据库的执行速度的，可通过配置搜索引擎，加索引，分库分表等等，对Sql的各列进行精确取其范围值，然后尽量避免全局扫描等等，Sql优化有很多，下面的简单的总结一下吧(上...
MySQL面试：left join我要怎优化？
2401_83412087的博客
03-26 593
这个过程是先遍历表t1，然后根据表t1中取出的每行数据中的a值，去表t2中查询满足条件的记录。扫描表t2，把表t2中的每一行取出来，跟join_buffer中的数据做对比，满足join条件的，作为结果集的一部分返回。把表t1的数据读入线程内存join_buffer中，这里我们是把整个表t1放入内存中。这里，我们两个表都是做的全表扫描，所以不管是哪个表做驱动表都是执行消耗都是一样的。取出表t2中满足条件的行，跟t1的数据组成一行，作为结果集的一部分。，现在起闭关修炼半个月，争取早日上岸！
MySQL left join 查询巨慢 优化
寂寞的搬运工的博客
04-03 9616
MySQL left join 查询巨慢 优化前景提要功能快捷键合理的创建标题，有助于目录的生成如何改变文本的样式插入链接与图片如何插入一段漂亮的代码片生成一个适合你的列表创建一个表格设定内容居中、居左、居右SmartyPants创建一个自定义列表 前景提要 最近工作中遇到一个非常奇怪的问题，mysql中有两张表，test_info和test_do_info需要进行LEFT JOIN关联查询，每张表又都1W+的数据，关联查询需要12s之久。 按照常理来说1W+的数据关联查询应该很快，即使进行全表扫描也不至于
mysql left join查询慢的问题
无名小仙男的博客
08-26 4434
mysql inner join 或者left join 时，假如两个表的关联字段的字符集不一致，会导致查询速度很慢，本人亲身经历过这个问题。。（本问题对应以下内容的第6点） mysql left join 优化方案 1、left join选择小表作为驱动表（这部分基本是大家的共识） 2、如果左表比较大，并且业务要求驱动表必须是左表，那么我们可以通过where条件语句，使得左表被过滤的小一些，主要原理和第一条类似 3、关联字段给索引，因为在mysql的嵌套循环算法中，是通过关联字段进行关联，并查询的，所
mysql多个left join连接查询用法分析
12-16
本文实例讲述了mysql多个left join连接查询用法。分享给大家供大家参考，具体如下： mysql查询时需要连接多个表时，比如查询订单的商品表，需要查询商品的其他信息，其他信息不在订单的商品表，需要连接其他库的表，但是连接的条件基本都是商品ID就可以了，先给一个错误语句（查询之间的嵌套，效率很低）： SELECT A.order_id, A.wid, A.work_name, A.supply_price, A.sell_price, A.total_num, A.sell_profit, A.sell_percent, A.goods_id,
Mysql 优化LEFT JOIN语句
m0_47944994的博客
04-25 1712
Mysql 优化LEFT JOIN语句
Mysql中join连接算法和优化思路
WannaRunning的博客
01-19 677
Index Nested-LoopJoin 这个算法关键在于检索过程中可以使用被驱动表的索引。Index Nested-LoopJoin算法是join连接最优的join算法，如果使用join查询尽量使sql按照此算法执行才能保证性能。 举例说明 如果B表的NO字段是索引字段，那么查询过程可以用上被驱动表的索引，使用Index Nested-LoopJoin算法。 SELECT * FROM A INNER JOIN B ON A.ID=B.NO 1. 从表A中读入一行数据 R； 2. 从数.
MySQL - Left Join和Inner Join的效率对比，以及优化
MinggeQingchun的博客
09-04 5452
这样的多个left join组合，总觉得这种写法是有问题的，直接用inner join会怎样呢？差别在哪里？后续使用inner join发现速度要比left join快一些。
Mysql-相关知识
好记性不如烂笔头
01-11 324
3、Mysql 中空值('')是不占用空间的，Mysql中的NULL其实是占用空间的 不使用NULL可以提高索引效率，因为树形索引结构中将NULL也视作一般数据节点。 4、mysql 索引方法 btree 和 hash btree支持范围查找 如 >,<,order by等场景 hash仅仅能满足 =，！=，in 等场景 5、mysql索引类型normal，unique，f...
mysql left join 查询慢的问题排查
tianjiliuhen的博客
10-21 5068
然后对这个查询查看执行计划，看到 quality_xxx 是全表扫描，虽然比较怀疑，这么小的数据量不会导致这么大的差异，但是还是给 quality_xxx 的 关联字段 vehicle_no 加上了索引。发现 vehicle 的 union 表 和 quality_xxx 使用 left join关联时执行很慢（5s左右），但是两个字查询分别查询时，执行很快（小于1s），两个表的数据量都不是很大，一个7k ， 一个不到5W。整体sql写完之后，发现执行需要5s左右，然后单独对 子查询进行查询，执行都很快。
MySQL表LEFT JOIN左连接与RIGHT JOIN右连接的实例教程
12-15
LEFT JOIN 语法用法与实例 MySQL LEFT JOIN 语法 SQL（MySQL） LEFT JOIN 会取得左表（table1）全部记录，即使右表（table2）并无对应匹配记录。LEFT JOIN 基本语法如下： ... FROM table1 LEFT JOIN table2 ON condition ... MySQL LEFT JOIN 用法实例 下面是两个原始数据表： article 文章表: user 用户表: 我们列出所有的文章及对应的所属用户，即使没有用户的文章也列出。 SELECT … LEFT JOIN … ON 语句如下： SELECT a
在MySQL中使用JOIN语句进行连接操作的详细教程
01-21
到目前，我们已经学习了从一个表中获取数据。这是简单的需要，但在大多数现实MySQL的使用，经常需要将数据从多个表中的一个单一的查询。 可以使用多个表中的单一SQL查询。在MySQL中联接(join)行为是指两个或多个表到一个表中可以使用连接在SELECT，UPDATE和DELETE语句中加入MySQL表。我们将看到一个例子LEFT JOIN简单的MySQL连接。 在命令提示符使用联接： 假设我们两个表的教程tcount_tbl和tutorials_tbl的完整列表如下： 例子: 试试下面的例子： root@host# mysql -u root -p password; Enter pass
MySQL的LEFT JOIN表连接的进阶学习教程
12-15
LEFT JOIN的主表 这里所说的主表是指在连接查询里MySQL以哪个表为主进行查询。比如说在LEFT JOIN查询里，一般来说左表就是主表，但这只是经验之谈，很多时候经验主义是靠不住的，为了说明问题，先来个例子，建两个演示用的表categories和posts： CREATE TABLE IF NOT EXISTS `categories` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(15) NOT NULL, `created` datetime NOT NULL, PRIMARY KEY (`id`)
mysql 开发技巧之JOIN 更新和数据查重/去重
01-19
主要涉及：JOIN 、JOIN 更新、GROUP BY HAVING 数据查重/去重 1 INNER JOIN、LEFT JOIN、RIGHT JOIN、FULL JOIN(MySQL 不支持)、CROSS JOIN 这是在网上找到的非常好的一篇博文，图解 join 语句： CODING HORROR-A Visual Explanation of SQL Joins 下图可以很清楚的明白，join 的数据选取范围 [][1] [1]: http://7xs09x.com1.z0.glb.clouddn.com/160725-imooc-mysql-development-skills-no
MySQL在右表数据不唯一的情况下使用left join的方法
01-19
mysql left join 语句格式 A LEFT JOIN B ON 条件表达式 left join 是以A表为基础，A表即左表，B表即右表。 左表(A)的记录会全部显示，而右表(B)只会显示符合条件表达式的记录，如果在右表(B)中没有符合条件的记录，...
mysql 意向锁
最新发布
csdn问鼎
04-25 204
介绍mysql 意向锁的作用
88张表-Mysql
2301_80151755的博客
04-25 223
【代码】88张表-Mysql。
一文整理完MySQL关系型数据库相关知识
打代码是一天，不打代码也是一天
04-23 1153
在开发中，数据库是专门用来存取数据的软件。数据库的职责就是管理数据的。根据存取数据的类型分为关系型数据库和非关系型数据库数据存储后呈现出来的效果类似上图的，是关系型数据库数据存储后呈现出来的效果类似上图的，是非关系型数据库。
mysql多个left join 查询很慢
08-30
你的问题是关于MySQL中多个LEFT JOIN查询速度慢的情况。有几种可能的原因导致这个问题： 1. 索引不正确或缺失：请确保在连接条件和查询过滤条件上使用了合适的索引。缺少索引可能导致查询需要扫描整个表，从而降低性能。可以使用EXPLAIN语句来分析查询计划，检查是否正确使用了索引。 2. 表关联过多：当进行多个LEFT JOIN操作时，涉及的表越多，查询的复杂度也会增加。尽量减少JOIN操作的数量，或者考虑使用其他优化技术，如子查询、临时表等。 3. 数据量过大：如果参与LEFT JOIN操作的表中数据量非常大，查询速度可能会受到影响。可以考虑通过分区、分表等方式来优化查询性能，以减少操作的数据量。 4. 硬件资源不足：如果数据库服务器的硬件配置不够强大，可能会限制查询的性能。确保服务器有足够的内存、处理器和磁盘空间。 5. 查询语句优化：合理编写查询语句也是提高性能的关键。避免不必要的字段选择和过滤条件，尽量使用更精确的条件来限制结果集大小。 综上所述，你可以通过检查索引、优化查询语句、减少表关联数量和增加硬件资源等方式来提升多个LEFT JOIN查询的性能。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 2
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值