RDS最佳实践(四)—如何处理Mysql的子查询

早上值班同事在旺旺群里面贴了一条非常复杂的SQL，用户从本地迁移到RDS Mysql出现严重性能下降，同样的数据和表结构下，在本地的数据库上只要不到1s的时间，但是在rds上好几分钟都没响应。

碰到这类问题需要考虑以下一些因素：

a.数据库的版本不同（不同的版本优化器策略不一样，或者异构数据库间的迁移：oracle–>mysql,sqlserver–>mysql），导致sql执行计划不同，最后导致sql执行时间不同；

b.数据库的配置不同（不同的内存配置，参数设置–query cache是否打开），导致sql执行时间不同；

c.数据库的数据量不同（系统遇到bug，生成了大量的垃圾数据），导致sql执行时间不同；

根据以上线索，用户是刚刚从线下迁移到RDS的，所以数据量和表结构是相同的；

RDS配置为：2400M内存，1200IOPS，本地是笔记本电脑：4000M的内存，5600转的笔记本电脑，所以数据库配置来说区别并不大；

所以就剩下数据库版本了，RDS的版本是Mysql 5.5，而用户使用的数据库版本是5.6，所以问题很可能出现在这里，mysql 5.6和5.5在优化器上最大的改进就是对子查询的优化改进：

a.5.0、5.1、5.5对子查询处理：不会将子查询的结果集计算出来用作与其他表做join，所以很有可能outer 表每扫描一条数据，子查询都会被重新执行一遍，这样就导致性能下降；所以在5.5之前的版本中，处理子查询的问题通常采用sql改写：将子查询改写为join的方式；

b.5.6对子查询处理：将子查询的结果集cache到临时表里，临时表索引的主要目的是用来移除重复记录，并且随后也可能用于在做join时做查询使用，这种技术在5.6中叫做Subquery Materialize.物化的子查询可以看到select_type字段为SUBQUERY，而在MySQL5.5里为DEPENDENT SUBQUERY

5.5的执行计划：

mysql> explain select count(*) from test_pic as bi where bi.time in (select MAX(time) from test_pic where PIC_TYPE=1 GROUP BY BUILDING_ID) GROUP BY bi.BUILDING_ID;
+—-+——————–+————–+——-+—————+—————+———+——+——-+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+——————–+————–+——-+—————+—————+———+——+——-+————-+
| 1 | PRIMARY | bi | index | NULL | IDX_BPIC_0001 | 7 | NULL | 50226 | Using where |
| 2 | DEPENDENT SUBQUERY | test_pic | index | NULL | IDX_BPIC_0001 | 7 | NULL | 43 | Using where |
+—-+——————–+————–+——-+—————+—————+———+——+——-+————-+
2 rows in set (0.00 sec)

explain extended结果可以看到优化器的详细执行步骤采用exists的方式将外表与子查询的表关联起来，这样会大大增加子查询的执行频率:

select count(0) AS `count(*)` from `test_db`.`test_pic` `bi` where <in_optimizer>(`test_db`.`bi`.`time`,<exists>(select max(`test_db`.`test_pic`.`time`) from `test_db`.`test_pic` where (`test_db`.`test_pic`.`PIC_TYPE` = 1) group by `test_db`.`test_pic`.`BUILDING_ID` having (<cache>(`test_db`.`bi`.`time`) = <ref_null_helper>(max(`test_db`.`test_pic`.`time`))))) group by `test_db`.`bi`.`BUILDING_ID`

5.6的执行计划：

mysql> explain select count(*) from test_pic as bi where bi.time in (select MAX(time) from test_pic where PIC_TYPE = 1 GROUP BY BUILDING_ID) GROUP BY bi.BUILDING_ID;
+—-+————-+————–+——-+—————+—————+———+——+——-+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+————–+——-+—————+—————+———+——+——-+————-+
| 1 | PRIMARY | bi | index | IDX_BPIC_0001 | IDX_BPIC_0001 | 7 | NULL | 46595 | Using where |
| 2 | SUBQUERY | test_pic | index | IDX_BPIC_0001 | IDX_BPIC_0001 | 7 | NULL | 46595 | Using where |
+—-+————-+————–+——-+—————+—————+———+——+——-+————-+
2 rows in set (0.00 sec)

explain extended结果可以看到优化器将子查询的结果集计算出来存放到一张临时表中，然后在与表做join：

/* select#1 */
select count(0) AS `count(*)` from `test56`.`test_pic` `bi` where <in_optimizer>(`test56`.`bi`.`time`,`test56`.`bi`.`time` in (
<materialize>
(/* select#2 */ select max(`test56`.`test_pic`.`time`) from `test56`.`test_pic`
where (`test56`.`test_pic`.`PIC_TYPE` = 1) group by `test56`.`test_pic`.`BUILDING_ID` having 1 ),
<primary_index_lookup>(`test56`.`bi`.`time` in <temporary table> on <auto_key>
where ((`test56`.`bi`.`time` = `materialized-subquery`.`MAX(time)`)))))
group by `test56`.`bi`.`BUILDING_ID`

所以针对该问题的解决方案就是将子查询改写为关联：

mysql> explain select count(*) from test_pic as bi , (select MAX(time) as time from test_pic where PIC_TYPE=1 GROUP BY BUILDING_ID) b where bi.time = b.time GROUP BY bi.BUILDING_ID;
+—-+————-+————–+——-+—————+—————+———+——+——-+———————————+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+————–+——-+—————+—————+———+——+——-+———————————+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 737 | Using temporary; Using filesort |
| 1 | PRIMARY | bi | ALL | NULL | NULL | NULL | NULL | 50226 | Using where; Using join buffer |
| 2 | DERIVED | test_pic | index | NULL | IDX_BPIC_0001 | 7 | NULL | 50226 | Using where |
+—-+————-+————–+——-+—————+—————+———+——+——-+———————————+
3 rows in set (0.06 sec)

explain extended的详细执行结果：

select count(0) AS `count(*)` from `test_db`.`test_pic` `bi` join (select max(`test_db`.`test_pic`.`time`) AS `time` from `test_db`.`test_pic` where (`test_db`.`test_pic`.`PIC_TYPE` = 1) group by `test_db`.`test_pic`.`BUILDING_ID`) `b` where (`test_db`.`bi`.`time` = `b`.`time`) group by `test_db`.`bi`.`BUILDING_ID`

SQL很快就执行得到结果；RDS已经推出5.6的版本，届时可以选择购买5.6的实例，同样也可以将5.5,5.1的实例升级到5.6，解决让人诟病的子查询性能问题。

PS.最佳实践：在oracle迁移到mysql的时候，请选用Mysql 5.6的版本，这样就可以避免麻烦的子查询改写了。