开发者社区> developerguy> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

MySQL千万级多表关联SQL语句调优

简介: 本文不涉及复杂的底层数据结构,通过explain解释SQL,并根据可能出现的情况,来做具体的优化,使千万级表关联查询第一页结果能在2秒内完成(真实业务告警系统优化结果)。   需要优化的查询:使用explain      出现了Using temporary;      有分页时出现了Using filesort则表示使用不了索引,需要根据下面的技巧来调整语句      rows过多,或者几乎是全表的记录数;      key 是 (NULL);      possible_keys 出现过多(待选)索引。
+关注继续查看
本文不涉及复杂的底层数据结构,通过explain解释SQL,并根据可能出现的情况,来做具体的优化,使千万级表关联查询第一页结果能在2秒内完成(真实业务告警系统优化结果)。
 
需要优化的查询:使用explain
     出现了Using temporary;
     有分页时出现了Using filesort则表示使用不了索引,需要根据下面的技巧来调整语句
     rows过多,或者几乎是全表的记录数;
     key 是 (NULL);
     possible_keys 出现过多(待选)索引。
 
1.使用explain语法,对SQL进行解释,根据其结果进行调优:
     MySQL 表关联的算法是 Nest Loop Join,是通过驱动表的结果集作为循环基础数据,然后一条一条地通过该结果集中的数据作为过滤条件到下一个表中查询数据,然后合并结果:
          a.EXPLAIN 结果中,第一行出现的表就是驱动表
 
          b.对驱动表可以直接排序,对非驱动表(的字段排序)需要对循环查询的合并结果(临时表)进行排序(Important!),即using temporary;
 
          c. [驱动表] 的定义为:1)指定了联接条件时,满足查询条件的记录行数少的表为[驱动表];2)未指定联接条件时,行数少的表为[驱动表](Important!)。
 
          d.优化的目标是尽可能减少JOIN中Nested Loop的循环次数,以此保证:永远用小结果集驱动大结果集(Important!)!:A JOIN B,A为驱动,A中每一行和B进行循环JOIN,看是否满足条件,所以当A为小结果集时,越快。
          
          e.NestedLoopJoin实际上就是通过驱动表的结果集作为循环基础数据,然后一条一条的通过该结果集中的数据作为过滤条件到下一个表中查询数据,然后合并结果。如果还有第三个参与Join,则再通过前两个表的Join结果集作为循环基础数据,再一次通过循环查询条件到第三个表中查询数据,如此往复
 
2.两表JOIN优化:
     a.当无order by条件时,根据实际情况,使用left/right/inner join即可,根据explain优化 ;
     b.当有order by条件时,如select * from a inner join b where 1=1 and other condition order by a.col;使用explain解释语句;
     1)如果第一行的驱动表为a,则效率会非常高,无需优化;
     2)否则,因为只能对驱动表字段直接排序的缘故,会出现using temporary,所以此时需要使用STRAIGHT_JOIN明确a为驱动表,来达到使用a.col上index的优化目的;或者使用left join且Where条件中不含b的过滤条件,此时的结果集为a的全集,而STRAIGHT_JOIN为inner join且使用a作为驱动表
 
3.多表JOIN优化:
     a.无order by条件时,根据实际情况,使用left/right/inner join即可,根据explain优化;
     b.有order by a.col条件时,所有join必须为left join,且每个join字段都创建索引,同时where条件中只能有a表的条件,即将其它表的数据关联到a中形成一张大表,再对a的全集进行过滤;
          如果不能全使用left join,则需灵活使用STRAIGHT_JOIN及其它技巧,以时间排序为例:
               1)数据入库按照平台时间入库,自然a的数据都按时间有序;
SELECT c.*, r.HYPERVISOR_HOST_NAME hostname, r.HOST_IP FROM trust_monitor c STRAIGHT_JOIN res_node r ON c.res_node_id = r.ID STRAIGHT_JOIN am_assets a ON r.ASSET_ID = a.ID AND a.status = 58 STRAIGHT_JOIN se_role s ON a.DEPT_FLAG = s.ROLE_ORG AND s.ROLE_ID IN (32,33,36,41) where c.STATUS = 58 and c.changed_type = 79 limit 1,10;
 
SELECT c.*, r.HYPERVISOR_HOST_NAME hostname, r.HOST_IP FROM trust_monitor c inner JOIN res_node r ON c.res_node_id = r.ID INNER JOIN am_assets a ON r.ASSET_ID = a.ID AND a.status = 58 INNER JOIN se_role s ON a.DEPT_FLAG = s.ROLE_ORG AND s.ROLE_ID IN (32,33,36,41) where c.STATUS = 58 and c.changed_type = 79 order by c.changed_time limit 1,10;
两者结果一致
 
4.误区:
     a.视图只是屏蔽或者高效集合多表数据的一种方法,视图与表JOIN,不会起到任何效果
 
参考:
http://www.cnblogs.com/uttu/p/6384541.html

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
MySQL复习:数据库和数据表管理
MySQL复习:数据库和数据表管理
21 0
MySQL入门实战3:SQL问题诊断与调优
云起冬季实战营第三期:MySQL数据库进阶实战。参加复习一下mysql,教程很好,做笔记记录一下
31 0
MySQL入门实战3:SQL问题诊断与调优
云起冬季实战营第三期:MySQL数据库进阶实战。参加复习一下mysql,教程很好,做笔记记录一下
704 0
MySQL级联复制的同步问题(一)
今天碰到一个有些奇怪的问题,有一套环境,在主从复制的时候有一些问题。 大体的流程设计如下: 三个节点位于三个不同的区域,因为节点1和节点3之间的网络存在问题,所以走了节点2来中转,由此可见延迟是难免的,但是延迟不能太大。
978 0
Mysql创建多列唯一索引Sql
ALTER TABLE `t_city_combo` ADD UNIQUE INDEX ` t_city_combo_index` (`combo_id`, `combo_name`, `city_id`) USING BTREE 语法 ALTER TABLE `表名英文` ADD ...
1367 0
MySQL启用SSD存储
版权声明:本文为博主chszs的原创文章,未经博主允许不得转载。 https://blog.csdn.net/chszs/article/details/48049127 MySQL启用SSD存储 作者:chszs,版权所有,未经同意,不得转载。
613 0
mysql如何跟踪执行的sql语句
在SQL SERVER下跟踪sql采用事件探查器,而在mysql下如何跟踪sql呢? 其实方法很简单,开启mysql的日志log功能,通过查看跟踪日志即可。 开启mysql的日志log方法: windows环境下的配置方法:我使用的版本:Version: 5.
834 0
+关注
developerguy
A code cleanliness code farmers, A programmer concentrate on elegant design, A want to do with the product architect
1663
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
低代码开发师(初级)实战教程
立即下载
阿里巴巴DevOps 最佳实践手册
立即下载
冬季实战营第三期:MySQL数据库进阶实战
立即下载