Mysql join大表优化案例_mysql left join 大表-程序员宅基地
一、准备知识（Mysql join原理及结论）
1、MySQL join分为
inner joinleft (outer) joinright (outer) joinfull join（mysql不支持full join，但是可以利用left join + union + right join实现full join）cross join（笛卡尔积），实际上inner join不指定on即和cross join表现一样straight_join（效果等同于inner join，只是固定了驱动表顺序）
2、驱动表与被驱动表
- inner join：由执行器自行决定谁是驱动表，谁是被驱动表
- left join：左表是驱动表，右表是被驱动表
- right join：右表是驱动表，左表是被驱动表
- straight_join：固定左边为驱动表，右边为被驱动表
3、join执行流程
每取驱动表一行数据，去和被驱动表匹配。可以理解为双层for循环。
所以数据量的时候，循环次数变多，这也是join性能问题的根源。
4、join执行的实现原理
Nest Loop Join (NLJ)，就是单纯双层循环Block Nest Loop Join (BNLJ)，在NLJ基础上，利用join_buffer，一次取出一批驱动表数据，可以减少循环匹配次数Index Nest Loop Join (INLJ)，在NLJ基础上，利用被驱动表连接字段的索引直接找到匹配数据，可以减少循环次数
5、join on
on后跟连接条件，一般必须指定，且只对被驱动表有效(即即使对驱动表加了过滤条件该条件也无效)- 由此可知join on之后，驱动表包含全部数据，被驱动表只包含on条件过滤后的数据
6、on和where
- on在join时就会过滤数据，而where是join完成后再对数据进行过滤，所以on比where先作用
- 所以理论上过滤条件放在on后，比放在where后性能好
- 但是过滤条件放在on后和where后，结果可能不一样(原因就在于on后条件只对被驱动表有效)，所以谨慎在on后加驱动表的过滤条件
- 针对inner join，on和where无啥差别
根据前面的说明，以下属于结论性说明
7、加过滤条件要想清楚
是先对被驱动表进行过滤还是join完再对驱动表和被驱动表进行过滤
8、尽量小表驱动大表
这里针对的是left join和right join，因为inner join会自动选择
9、被驱动表连接字段要加索引
否则Mysql就会使用Block Nest Loop Join，影响性能
10、explain命令分析出的第一行的表即是驱动表
11、优化join的思路
- 顺着减少join时驱动表匹配被驱动表时的循环查找次数进行思考(想一下哪些结论是针对这个思路的？)
- 如果join后的数据量很大，且还要进行相关聚合操作，可以考虑先聚合出临时表，再join（本案例就是这个思路）
二、案例
1、原sql
EXPLAIN
select camf.asset_management_id
from crm_asset_management_friend camf INNER JOIN crm_asset_management cam ON camf.asset_management_id=cam.id
where camf.`status`=1
and cam.wx_status != 0
group by camf.asset_management_id having count(camf.id) > 10;
- 用时：1.5s
- explain结果：
2、分析
- cam数据量4000多，且基本不会再大量增加；camf数据量接近400万，且持续稳定增加ing
- camf在asset_management_id上有索引
- join完之后有320万数据（表明每一条驱动表cam数据匹配被驱动表camf循环次数很多(1条匹配几千条)），如下sql，再进行groupby，性能可想而知
select camf.asset_management_id
from crm_asset_management_friend camf INNER JOIN crm_asset_management cam ON camf.asset_management_id=cam.id
where camf.`status`=1
and cam.wx_status != 0
3、新sql
EXPLAIN
select cam.id from (
select camf.asset_management_id
from
crm_asset_management_friend camf
where camf.`status`=1
group by camf.asset_management_id having count(camf.id) > 10
) tmp INNER JOIN crm_asset_management cam ON tmp.asset_management_id=cam.id
where cam.wx_status!=0;
- 用时：1.0s
- explain结果：
4、结语
- 之所以还是在1s以上，是因为本身如下groupby就耗时0.9s多
select camf.asset_management_id
from
crm_asset_management_friend camf
where camf.`status`=1
group by camf.asset_management_id having count(camf.id) > 10
- 限于业务，表结构，也只能优化到这儿了。
- 要想彻底优化，是需要提取出每个asset_management的count(camf.id)作为一个新字段维护到cam表（维护比较麻烦，这也是没有维护的原因）。这样查询friend数大于10的资产就不需要绕这样一大圈了。sql就会变为类似：
select cam.id from crm_asset_management cam
where cam.wx_status!=0 and friendCount>10
这速度就会杠杆的了！！！
智能推荐
2024全国安全生产合格证危险化学品生产单位主要负责人常考题练习-程序员宅基地
文章浏览阅读926次，点赞17次，收藏27次。参考解析：《生产安全事故应急预案管理办法》第21条矿山、金属冶炼企业和易燃易爆物品、危险化学品的生产、经营（带储存设施的，下同）、储存、运输企业，以及使用危险化学品达到国家规定数量的化工企业、烟花爆竹生产、批发经营企业和中型规模以上的其他生产经营单位，应当对本单位编制的应急预案进行评审，并形成书面评审纪要。23、除矿山、建筑施工单位和易燃易爆物品、危险化学品、放射性物品等危险物品的生产、经营、储存、使用单位和中型规模以上的其他生产经营单位外,其他生产经营单位应当对本单位编制的应急预案进行论证。
zookeeper 链接报错 KeeperErrorCode = NodeExists for_keepererrorcode = nodeexists for /test057-程序员宅基地
文章浏览阅读3.9k次。zookeeper 链接报错 KeeperErrorCode = NodeExists for zookeeper链接报错要将原来的删除掉dataDir路径下的version-2文件夹，再重启本机在C:\JavaEnv\ws-2181_keepererrorcode = nodeexists for /test057
Verilog-实现脉冲边缘检测电路_verilog脉冲检测-程序员宅基地
文章浏览阅读892次，点赞2次，收藏8次。本篇博客主要记录使用Verilog实现对脉冲的上升沿和下降沿检测；下面的链接是博主所写的“基于basys2的按键消抖”，其中状态机实现按键消抖使用了本篇博客所介绍的边缘检测电路。_verilog脉冲检测
前后端分离web项目_semidesign vue-程序员宅基地
文章浏览阅读2.5k次，点赞2次，收藏5次。过程1.构建前端使用到的工具：套件工具-用来安装依赖yarnnpm前端构建工具：webpackvitenuxt.js前端框架VUEreact前端语言typescriptjavascript前端渲染框架semidesign-不支持vueElement ui步骤：1.安装yarn，使用yarn构建web项目的前端部分。在使用yarn时可能会碰到一个问题：无法加载文件 C:\Users\28011\AppData\Roaming\npm\_semidesign vue
iOS 证书管理、验证、打包流程_怎么查看ipa用哪个证书打包的-程序员宅基地
文章浏览阅读1.1k次。背景iOS软件的开发和发布离不开证书和配置文件，如果要想发布app到Apple Store或者使用苹果的推送通知功能，都需要个人开发者证书签名该app，以便通过苹果的认证和审核。由于我们公司的app不是单独一个，而是一个客户对应一个app，在新版本中，需要用到推送通知功能，就需要发布app到Apple Store，通过认证后才能正常使用苹果提供的这个服务，同时，为了满足部分客户要把自己的app发布..._怎么查看ipa用哪个证书打包的
Mac 储存空间“其他”占据这么多？一招带你搞定_其他用户与共享 mac 数据删除-程序员宅基地
文章浏览阅读3.5k次。手动清除这些“其他”文件的方法比较适合专业的、熟悉Mac规则的用户。对于普通Mac用户，如果怕误删系统文件，还是将清理“其他”文件的任务交给专业的第三方清理工具吧，例如CleanMyMac X。2、在“访达”中选中用户名，右侧空白处，右键“查看显示选项”-勾选“显示‘资源库’文件夹”1、打开“访达”-“偏好设置”-“边栏”中，勾选“个人用户名”；1、点击左上角 -“关于本机”-“储存空间”-“管理”；2、点击“文稿”-“文件浏览器”-“资源库”；1）安装应用程序，点击侧边栏中的“系统垃圾”。..._其他用户与共享 mac 数据删除
随便推点
头歌educoder-Python程序设计-第五阶段 类与对象-类的其它特性_头歌类的其它特性-程序员宅基地
文章浏览阅读5.8k次，点赞13次，收藏42次。关卡一：类的内建函数import specialmethodtestsc = specialmethodtest.subClass()# 请在下面填入判断subClass是否为parentClass的子类的代码，并输出结果########## Begin ##########print(issubclass(specialmethodtest.subClass, specialmethodtest.parentClass))########## End ########### ..._头歌类的其它特性
初中学历学前端难不难_计算机前端初中学难不难-程序员宅基地
文章浏览阅读609次。初中学历学前端难不难那肯定难啊。如果年纪不大，而且对IT这方面又比较感兴趣，我建议先去想办法提升一下自己的学历，成人本科也是可以的，该说不说，这东西花点钱还是可以弄到的，毕竟现在IT行业还是很看重学历的，学历是工作的第一块敲门砖，可能有人会说能力更重要，但是我告你你没有学历，别人根本不会去了解你是否有能力。当然花钱买的那个学历也不是处处管用，像大一点的公司需要的学历是需要学信网认证的，但你搞个学历，很多你之前初中文凭进不去的公司可能就会考虑你。如果真的考虑好了的话我建议去报个短期的培训班，毕竟是初中文凭_计算机前端初中学难不难
ThreadX学习（2）——线程_threadx教程-程序员宅基地
文章浏览阅读3.9k次，点赞26次，收藏48次。ThreadX学习（2）——线程学习参考：ThreadX中的线程线程创建堆栈分配互斥锁线程优先级优先级反转优先级继承抢占阈值线程状态数据结构TCB就绪列表API学习参考：《Real-Time Embedded Multithreading: Using ThreadX and ARM》安富莱_STM32-V7开发板ThreadX内核教程（V0.7）ThreadX中的线程在ThreadX中，一般没有进程的概念，统称为线程。关于调度器的实现细节，ThreadX可能是用汇编写的，没看懂。T_threadx教程
苹果手机sim卡无效怎么办_苹果手机存储空间不足怎么办-程序员宅基地
文章浏览阅读127次。 虽然现在的手机存储空越来越大，不过，娱乐的文件已经各种软件的体积也越来越大，而不像其他安卓手机可以通过内存卡对存储空间进行扩充，苹果手机存储空间不足怎么办，下面就为大家介绍一下解决方法。苹果手机存储空间不足怎么办 步骤1：当手机存储空间不足时，我们先得要查清楚，究竟是什么占用了大量的手机存储空间。打开苹果主屏上的“设置”应用。 步骤2：在设置列表中找到“通用”项，点击进入，接下来在通用中找..._萍果7手机sm1卡失效怎么办
中国大学生计算机设计大赛省级赛事管理系统_计算机设计大赛管理信息系统类-程序员宅基地
文章浏览阅读1.1k次，点赞18次，收藏21次。根据提示输入参赛队编号，若查找成功，输出该赛事类别对应的基本信息（参赛作品名称、参赛学校、赛事类别、参赛者和指导老师信息），同时，输出查找成功时的平均查找长度ASL；能够管理各参赛队的基本信息（包含参赛队编号，参赛作品名称，参赛学校，赛事类别，参赛者，指导老师），赛事类别共11项（参见大赛官网。1、请根据任务描述的问题，设计合理的菜单，菜单交互设计要合理，便于用户根据提示使用系统的所有功能。来定义参赛队编号，参赛作品名称，参赛学校，赛事类别，参赛者，指导老师等基本信息。包括增加、删除、修改参赛队伍的信息。_计算机设计大赛管理信息系统类
2024软件测试学习线路图~-程序员宅基地
文章浏览阅读879次，点赞17次，收藏17次。一个好的心态和一个坚持的心很重要，很多冲着高薪的人想学习前端，但是能学到最后的没有几个，遇到困难就放弃了，这种人到处都是，就是因为有的东西难，所以他的回报才很大，我们评判一个前端开发者是什么水平，就是他解决问题的能力有多强。分享一些简单的前端面试题以及学习路线给大家，狂戳这里即可获取一个好的心态和一个坚持的心很重要，很多冲着高薪的人想学习前端，但是能学到最后的没有几个，遇到困难就放弃了，这种人到处都是，就是因为有的东西难，所以他的回报才很大，我们评判一个前端开发者是什么水平，就是他解决问题的能力有多强。