MySQL万万级记录的多表关联SQL语句调优
本文不涉及伟大的底层数据布局,通过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_JOINse_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/zhengyun_ustc/p/slowquery1.html http://huoding.com/2013/06/04/261 来历:http://sunrysoft.iteye.com/blog/2356617 (编辑:厦门网) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |