sql⼤查询leftjoin拆分优化，去掉临时表
本⽂讲解如何把⼀条带有⼀个或多个left join或right join的sql语句拆分成多条sql语句。
MySQL进⾏连表查询效率是很低的，特别是数据很⼤，⽽且并发量很⾼的情况，索引都⽆法解决问题，最好的办法就是把sql语句拆分成多条单表查询的sql。
公司电商⽹站现在要做⽹站服务化，⽤java做中间件，PHP调⽤java接⼝获取数据，数据表也进⾏了拆分，分库，要求不使⽤连表查询，有连表查询的sql语句想办法拆分多条sql语句，然后统⼀使⽤java接⼝。
这样做的⽬的⼀是为了⽹站服务化做调整，数据的增删查改都封装到具体的业务⾥⾯，⼆是为了提⾼性能，减少数据库压⼒，说来说去还是为了提⾼效率。
我们看看⼀个join多张表的sql如何拆分多条sql。
select u1.uid,u1.uname,u2.add from user as u1
left join userinfo as u2 on u1.uid=u2.uid
left join money as u3 on u1.uid=u3.uid
untry='c'
这条sql语句left join三张表，分别是user作为主表，连userinfo，money表。⾸先可以查出所有user的数据，有了第⼀条拆分的sql：
select uid,uname from user where country='c'
由于条件是user.uid=userinfo.uid，所以可以把取得的uid⽤”,”连接，第⼆个拆分的sql如下：
select uid,add from userinfo where uid in(32,34,23,23)
这样得出了符合第⼀个left join条件下的uid，uname和add字段的结果，其实到这⾥就已经实现了以上left join语句的需求，如果还要查询money表的字段，以此类推，把uid作为in的条件查money表。
优化临时表使⽤，SQL语句性能提升100倍
【问题现象】
线上mysql数据库爆出⼀个慢查询，DBA观察发现，查询时服务器IO飙升，IO占⽤率达到100%， 执⾏时间长达7s左右。
SQL语句如下：
SELECT DISTINCT g.*, cp.name AS cp_name, c.name AS category_name, t.name AS type_name FROM gm_game g
LEFT JOIN gm_cp cp ON cp.id = g.cp_id AND cp.deleted = 0
LEFT JOIN gm_category c ON c.id = g.category_id AND c.deleted = 0
LEFT JOIN gm_type t ON t.id = g.type_id AND t.deleted = 0
WHERE g.deleted = 0
ORDER dify_time
DESC LIMIT 20 ;
【问题分析】
使⽤explain查看执⾏计划，结果如下：
这条sql语句的问题其实还是⽐较明显的：
查询了⼤量数据(包括数据条数、以及g.* )，然后使⽤临时表order by，但最终⼜只返回了20条数据。
DBA观察到的IO⾼，是因为sql语句⽣成了⼀个巨⼤的临时表，内存放不下，于是全部拷贝到磁盘，导致IO飙升。
【优化⽅案】
优化的总体思路是拆分sql，将排序操作和查询所有信息的操作分开。
第⼀条语句：查询符合条件的数据，只需要查询g.id即可
SELECT DISTINCT g.id FROM gm_game g
LEFT JOIN gm_cp cp ON cp.id = g.cp_id AND cp.deleted = 0
LEFT JOIN gm_category c ON c.id = g.category_id AND c.deleted = 0
LEFT JOIN gm_type t ON t.id = g.type_id AND t.deleted = 0
WHERE g.deleted = 0
ORDER dify_time
DESC LIMIT 20 ;
第⼆条语句：查询符合条件的详细数据，将第⼀条sql的结果使⽤in操作拼接到第⼆条的sql
SELECT DISTINCT g.*, cp.name AS cp_name,c.name AS category_name,t.name AS type_name FROMgm_game g
LEFT JOIN gm_cp cp ON cp.id = g.cp_id AND cp.deleted = 0
LEFT JOIN gm_category c ON c.id = g.category_id AND c.deleted = 0
LEFT JOIN gm_type t ON t.id = g.type_id AND t.deleted = 0
WHERE g.deleted = 0 and g.id in(…………………)
ORDER dify_time DESC ;
【实测效果】
在SATA机器上测试，优化前⼤约需要50s，优化后第⼀条0.3s，第⼆条0.1s，优化后执⾏速度是原来的100倍以上，IO从100%降到不到1%
在SSD机器上测试，优化前⼤约需要7s，优化后第⼀条0.3s，第⼆条0.1s，优化后执⾏速度是原来的10倍以上，IO从100%降到不到1%可以看出，优化前磁盘io是性能瓶颈，SSD的速度要⽐SATA明显要快，优化后磁盘不再是瓶颈，SSD和SATA性能没有差别。
【理论分析】
MySQL在执⾏SQL查询时可能会⽤到临时表，⼀般情况下，⽤到临时表就意味着性能较低。
临时表存储
MySQL临时表分为“内存临时表”和“磁盘临时表”，其中内存临时表使⽤MySQL的MEMORY存储引擎，磁盘临时表使⽤MySQL的MyISAM存储引擎；
⼀般情况下，MySQL会先创建内存临时表，但内存临时表超过配置指定的值后，MySQL会将内存临时表导出到磁盘临时表；
Linux平台上缺省是/tmp⽬录，/tmp⽬录⼩的系统要注意啦。
使⽤临时表的场景
1）ORDER BY⼦句和GROUP BY⼦句不同， 例如：ORDERY BY price GROUP BY name；
2）在JOIN查询中，ORDER BY或者GROUP BY使⽤了不是第⼀个表的列
例如：SELECT * from TableA, TableB ORDER BY TableA.price GROUP by TableB.name
3）ORDER BY中使⽤了DISTINCT关键字 ORDERY BY DISTINCT(price)
4）SELECT语句中指定了SQL_SMALL_RESULT关键字
SQL_SMALL_RESULT的意思就是告诉MySQL，结果会很⼩，请直接使⽤内存临时表，不需要使⽤索引排序 SQL_SMALL_RESULT 必须和GROUP BY、DISTINCT或DISTINCTROW⼀起使⽤ ⼀般情况下，我们没有必要使⽤这个选项，让MySQL服务器选择即可。
直接使⽤磁盘临时表的场景
1）表包含TEXT或者BLOB列；sql left join 多表连接
2）GROUP BY 或者 DISTINCT ⼦句中包含长度⼤于512字节的列；
3）使⽤UNION或者UNION ALL时，SELECT⼦句中包含⼤于512字节的列；
临时表相关配置
tmp_table_size：指定系统创建的内存临时表最⼤⼤⼩；
max_heap_table_size: 指定⽤户创建的内存表的最⼤⼤⼩；
注意：最终的系统创建的内存临时表⼤⼩是取上述两个配置值的最⼩值。
表的设计原则
使⽤临时表⼀般都意味着性能⽐较低，特别是使⽤磁盘临时表，性能更慢，因此我们在实际应⽤中应该尽量避免临时表的使⽤。 常见的避免临时表的⽅法有：
1）创建索引：在ORDER BY或者GROUP BY的列上创建索引；
2）分拆很长的列：⼀般情况下，TEXT、BLOB，⼤于512字节的字符串，基本上都是为了显⽰信息，⽽不会⽤于查询条件， 因此表设计的时候，应该将这些列独⽴到另外⼀张表。
SQL优化
如果表的设计已经确定，修改⽐较困难，那么也可以通过优化SQL语句来减少临时表的⼤⼩，以提升SQL执⾏效率。
常见的优化SQL语句⽅法如下：
1）拆分SQL语句
临时表主要是⽤于排序和分组，很多业务都是要求排序后再取出详细的分页数据，这种情况下可以将排序和取出详细数据拆分成不同的SQL，以降低排序或分组时临时表的⼤⼩，提升排序和分组的效率，我们的案例就是采⽤这种⽅法。
2）优化业务，去掉排序分组等操作
有时候业务其实并不需要排序或分组，仅仅是为了好看或者阅读⽅便⽽进⾏了排序，例如数据导出、数据查询等操作，这种情况下去掉排序和分组对业务也没有多⼤影响。
如何判断使⽤了临时表？
使⽤explain查看执⾏计划，Extra列看到Using temporary就意味着使⽤了临时表。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。