护航用户反馈RDS实例CPU使用率持续保存100%，影响业务怎么办？

问题描述：
护航用户反馈RDS实例CPU使用率持续保存100%，影响用户业务。如下图：




解决过程：


1、查看当时物理机性能负载较重，CPU使用持续保持 100%，于是对实例进行了迁移。



2、实例迁移后，情况有所好转；但CPU 在14:00 左右又上升到 100%，用户比较焦虑，希望尽快帮助解决。排查用户当时实例运行情况，发现存储过程中的语句执行计划不合理，错误的引入了子查询：
SELECT A.`daysortid`,A.`dayid`,A.`signinscore` INTO vDaysortid,vTemDayID,vLogScore FROM ( SELECT `daysortid`,`dayid`,`signinscore` FROM mbloginscore WHERE `dayid`>=vYesTodayID AND `userid`=IUserID ORDER BY `id` DESC LIMIT 1 ) A




建议用户修改语句为：
SELECT `daysortid`,`dayid`,`signinscore` INTO vDaysortid,vTemDayID,vLogScore FROM mbloginscore WHERE `dayid`>= vYesTodayID AND `userid`= IUserID ORDER BY `id` DESC LIMIT 1
语句修改后，执行计划正确，逻辑读大为减少。




3、同时发现一个针对视图的查询执行耗时长，扫描的行数多。
SELECT COUNT('*') AS `__count` FROM `view_mobile_accounts` WHERE (`view_mobile_accounts`.`LastLoginDate` BETWEEN '2015-07-30 00:00:00' AND '2015-08-06 15:01:02.503120' AND `view_mobile_accounts`.`UnionID` = 101 AND `view_mobile_accounts`.`OldUnionID` = 102)

查看视图 view_mobile_accounts的定义，条件比较简单，而且条件字段的选择性不强，因此该视图定义并不优化。
select `qpaccounts`.`accountslogin`.`UserID` AS
`UserID`,`qpaccounts`.`accountslogin`.`GameID` AS
`GameID`,`qpaccounts`.`accountslogin`.`Account` AS
`Account`,`qpaccounts`.`accountslogin`.`NickName` AS
`NickName`,`qpaccounts`.`accountslogin`.`Enable` AS
`Enable`,`qpaccounts`.`accountslogin`.`UnionID` AS
`UnionID`,`qpaccounts`.`accountslogin`.`OldUnionID` AS
`OldUnionID`,`qpaccounts`.`accountslogin`.`RegPlat` AS
`RegPlat`,`qpaccounts`.`accountsinfo`.`RegisterIP` AS
`RegisterIP`,`qpaccounts`.`accountsinfo`.`RegisterDate` AS
`RegisterDate`,`qpaccounts`.`accountsinfo`.`GameLogonNumber` AS
`GameLogonNumber`,`qpaccounts`.`accountsinfo`.`LastLoginIP` AS
`LastLoginIP`,`qpaccounts`.`accountsinfo`.`LastLoginDate` AS
`LastLoginDate`,`qpmoney`.`gamescoreinfo`.`Score` AS
`Score`,`qpmoney`.`gamescoreinfo`.`Revenue` AS
`Revenue`,`qpmoney`.`gamescoreinfo`.`InsureScore` AS
`InsureScore`,`qpaccounts`.`accountsinfo`.`Experience` AS
`Experience`,`qpaccounts`.`accountslogin`.`MachineSerial` AS
`MachineSerial`,`qpmoney`.`gamescoreinfo`.`PlayTimeCount` AS `PlayTimeCount` from ((`qpaccounts`.`accountslogin` left join `qpaccounts`.`accountsinfo` on((`qpaccounts`.`accountsinfo`.`UserID` = `qpaccounts`.`accountslogin`.`UserID`))) left join `qpmoney`.`gamescoreinfo` on((`qpmoney`.`gamescoreinfo`.`UserID` = `qpaccounts`.`accountslogin`.`UserID`))) where (`qpaccounts`.`accountslogin`.`RegPlat` = 2


建议用户在业务高峰期不要运行这个查询，而且尽量修改为不基于该视图的查询。


4、调整重写查询后，实例的CPU 使用恢复到较低的水平。