MySQL百万级、千万级数据多表关联SQL语句调优
本⽂不涉及复杂的底层数据结构，通过explain解释SQL，并根据可能出现的情况，来做具体的优化，使百万级、千万级数据表关联查询第⼀页结果能在2秒内完成(真实业务告警系统优化结果)。
希望读者能够理解SQL的执⾏过程，并根据过程优化，⾛上⾃⼰的"成⾦之路"
需要优化的查询：
使⽤explain
出现了Using temporary；
有分页时出现了Using filesort则表⽰使⽤不了索引，需要根据下⾯的技巧来调整语句
rows过多，或者⼏乎是全表的记录数；
key 是 (NULL)；
possible_keys 出现过多（待选）索引。
1.使⽤explain语法，对SQL进⾏解释，根据其结果进⾏调优：
MySQL 表关联的算法是 Nest Loop Join，是通过驱动表的结果集作为循环基础数据，然后⼀条⼀条地通过该结果集中的数据作为过滤条件到下⼀个表中查询数据，然后合并结果：
EXPLAIN 结果中，第⼀⾏出现的表就是驱动表
对驱动表可以直接排序，对⾮驱动表（的字段排序）需要对循环查询的合并结果（临时表）进⾏排序（Important!），即using temporary;
[驱动表] 的定义为：1）指定了联接条件时，满⾜查询条件的记录⾏数少的表为[驱动表]；2）未指定联接条件时，⾏数少的表为[驱动表]（Important!）。
优化的⽬标是尽可能减少JOIN中Nested Loop的循环次数，以此保证：永远⽤⼩结果集驱动⼤结果集（Important!）！：A JOIN B，A为驱动，A中每⼀⾏和B进⾏循环JOIN，看是否满⾜条件，所以当A为⼩结果集时，越快。
NestedLoopJoin实际上就是通过驱动表的结果集作为循环基础数据，然后⼀条⼀条的通过该结果集中的数据作为过滤条件到下⼀个表中查询数据，然后合并结果。如果还有第三个参与Join，则再通过前两个表的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 l；使⽤explain解释语句；
如果第⼀⾏的驱动表为a，则效率会⾮常⾼，⽆需优化；
reference类型否则，因为只能对驱动表字段直接排序的缘故，会出现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 l条件时，所有join必须为left join，且每个join字段都创建索引，同时where条件中只能有a表的条件，即将其它表的数据关联到a中形成⼀张⼤表，再对a的全集进⾏过滤；
如果不能全使⽤left join，则需灵活使⽤STRAIGHT_JOIN及其它技巧，以时间排序为例：
1)数据⼊库按照平台时间⼊库，⾃然a的数据都按时间有序；sql语句中的 是什么意思
SELECT
c.*, r.HYPERVISOR_HOST_NAME hostname,
r.HOST_IP
FROM
trust_monitor c STRAIGHT_JOIN res_node r s_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
xml文件在哪INNER JOIN res_node r s_node_id = r.ID
INNER JOIN am_assets a ON r.ASSET_ID = a.IDdubbo创始人梁飞去哪了
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
mysql语句多表查询AND c.changed_type = 79
ORDER BY
c.changed_time
LIMIT 1,
10;
两者结果⼀致
4.误区：
a.视图只是屏蔽或者⾼效集合多表数据的⼀种⽅法，视图与表JOIN，不会起到任何效果
如何输入二维数组的数据
参考：
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论